Here’s a “Publications” record selector, as it looks immediately after opening:
Enteropens the record selector for it. Note: This behavior is consistent with Google Tables, but different from AirTable. For a cell with existing values, AirTable does not open the record selector on
Enter, presumably to prevent the user from accidentally overwriting values. I prefer the consistency of Google’s behavior here.
Shift+Tabmoves focus between the search inputs.
Downkeys move the selection.
Esckey closes the record selector without producing a record.
Entercloses the record selector, producing the record associated with the currently select option.
The search uses some fuzzy logic when finding and sorting records. Here’s how it works:
Begin with our shared sample data (transformed to the final schema).
Add some more data:
insert into "Authors" ("First Name", "Last Name") values ('Brianna', 'Murphy'), ('Annabelle', 'Smith'), ('Joanna', 'Stevenson'), ('Adrian', 'Richardson'), ('Anna', 'Richardson'), ('Anna', 'Wilson-Rich'), ('Joanna', 'Rich'), ('Hans', 'Ulrich');
We are looking for an author named “Anna Rich”. Let’s search the “Authors” table by entering “Anna” into “First Name” and “Rich” into “Last Name”.
This produces the following results (with the
points column displayed here for reference).
|Id||First Name||Last Name||Website||points|
We don’t get any exact matches, but the records that match most closely are listed first.
The query is run as follows:
WITH anon_cte AS ( SELECT *, CASE WHEN "First Name" ILIKE 'Anna' THEN 4 WHEN "First Name" ILIKE 'Anna%' THEN 3 WHEN "First Name" ILIKE '%Anna%' THEN 2 ELSE 0 END + CASE WHEN "Last Name" ILIKE 'Rich' THEN 4 WHEN "Last Name" ILIKE 'Rich%' THEN 3 WHEN "Last Name" ILIKE '%Rich%' THEN 2 ELSE 0 END AS points FROM "Authors" ) SELECT * FROM anon_cte WHERE points > 0 ORDER BY points DESC LIMIT 10;
The case statements should probably be wrapped into some db-layer function like
search_score(text, text) -> int, and overloading that would enable scoring other types.
When at least one column contains a query, a “ghost row” will appear above the result set, allowing the user to select a record that will be created on-the-fly using all of the data from their search. The ghost row is filled in as the user types.
The user can highlight the ghost row by pressing
Up – and in this state, validation errors will be displayed for cells as needed using the same UX as when adding a new row to a table.
To create a new Publications record, the user needs to supply an Authors record. They use a “nested” record selector to locate or create one Authors record as follows:
Note: There are some layout details here that will need to be worked out during implementation, such as horizontal scrolling.
The nested record selector opens when its target cell is selected (there’s not need to press
Enter), and it closes when a different higher-level cell is selected. There’s no way for it to be closed while its target cell is selected.
Note: UX gets a bit weird with tables that only contain FK columns. In the worst case scenario, a table would have only two columns, both of which are FKs. Selecting a record would be impossible because there would be no way to close the nested selectors. In the slightly-better-but-still-bad case, a table contains a PK plus two FKs. Here, you’d need to select the PK cell to hid the nested selectors. These cases are rare enough that I think it’s fine to discount them for now. With some more thought and experimentation, we can detect cases like that adjust the UI behavior a bit to better suit them.
After selecting an Authors record, the top-level Publications record selector will become active again, with a value for “Author Id” filled in, and with a string representation of that Authors record displayed in its place.
With a little more UX work, I think it would be possible to incorporate an “update” functionality into the record selector.
I’ve acquired a new copy of Moneyball by Michael Lewis, and I’d like to enter it as an item.
Within the item, I begin to locate or create a “Publications” record. My search looks as follows:
|Id||ISBN||Title||Year||Author Id||Publisher Id|
|91821||0393324818||Moneyball: The Art of Winning an Unfair Game||2004||
The first result appears to be a match at first, but then I notice that the book I’m holding is the 2011 edition, with ISBN 0393338398. Upon entering those details I see there is no matching record in the database, so I set out to add one by filling in all the fields I can.
I need to associate my new Publications record, with an Authors record. Here’s what my Authors search looks like:
|Id||First Name||Last Name||Website|
Hmmm. There are two Michael Lewis records! Which do I chose? Are they duplicates? I real life, there are actually as many as twenty published authors named Michael Lewis! While picking an Authors record, I want to see a list of books associated with each author.
This is tricky though. The Michael Lewis I’m after has published a lot of books! How will that list of books be sorted? What text will display for each book? (Because the full title of Moneyball is actually quite long.) Can I filter the list? Can I select an author named Michael Lewis who has published at least one book whose title begins with Moneyball??? Designing a UX which helps answer that sort of question (without lots of clicks and keystrokes) is an interesting challenge!
We could also construct a more common use case if we venture outside our example schema. Consider a CRM-like schema with
person records which each have many
email_address records. When entering an
activity record, I want to select a
person that has at least one
email_address which contains a field that matches a query. And in this case I also want to add a new person with that email address if I don’t find a matching person. Adding is especially tough because we can’t create the
email_address record until we create the
peron record, so the “drill-down-and-back-up-again” UX outlined in the specs above won’t work for the
These scenarios are more tricky to handle and will require more UX thought. But for some schemata handling them well will be pretty important.