More context in autocomplete results

[yohanboniface]

Hi again :)

Maybe this one is tricky, at UX level.

I've a ReferenceField pointing to a table of French cities (thousands). There are many cases of homonyms. In such case, when I type some letters to find a city, I cannot know which one is the one I'm looking for:

What would be helpful in this case would be to have extra column displayed in the drop down (the departement name or code in this case).

I've thought about creating a dedicated column in the reference table that would concatenate those two values (city name + departement name), but then I would have this concatenated string as value in the ReferenceField, thus this value would be exported, which is not ideal.

Maybe I'm describing an edge case, and this kind of workaround is the way to go.

One thing I'm thinking is that maybe in case of a ReferenceFIeld we could have two settings:

• which remote column to display
• which remote column to use as storage value (and thus export); that one would be optional and the first would be used if empty

Feel free to tell me if I'm splitting hairs! :)

Thanks for reading and thanks for your feedback on this!

Yohan, loving Grist :)

[anaisconce]

You're not describing an edge case. 🙂 Most users who run into this use the concatenated string method that you described. The simplest solution might be to use the concatenated string method, then create a new column with a reference lookup formula to pull in the referenced record's city. You would then export just the city column.

But there is an itch for a feature here that has come up before. It's typical to have a category (like a governmental department or grocery store department) and subcategories (like cities or specific foods). In those cases, the desire from the UI/UX experience would be to drill into categories when selecting a reference.

So when creating a reference, you would first look at a list of departments, then select one to open a list of cities in that department.

What's less clear to me is how to set categories available for "drilling into" in a reference columns. Perhaps it's all handled in the creator panel on the right in the same place reference columns are configured, but I'm open to other ideas. If you have any ideas for how such a feature would work, I'd love to hear them!

[yohanboniface]

Thanks @anaisconce :)

The simplest solution might be to use the concatenated string method, then create a new column with a reference lookup formula to pull in the referenced record's city. You would then export just the city column.

Just to be sure I'm not missing something: when exporting the document in your screenshot, both columns "Concatenated String" and "Ref Lookup" will be exported, right ? If I'd want to only export the "Ref Lookup", that would need removing or hiding the orther one ? Or is there some option to have a column shown but not exported (other than making another page) ?

So when creating a reference, you would first look at a list of departments, then select one to open a list of cities in that department.

I think this pattern is very usefull when you don't know exactly what you look for, and thus you need to "browse" a list of value to find the right one, and thus this way of "prefiltering" the candidate is very useful.

But when you already know what you are searching, I think nothing can compare to autocompletion in terms of efficiency and UX.

One pattern that can help a lot here, imho, is the "smart search" like in the Sublime Text command palette (and other similar editors), which is roughly letter based and not word based. In case you don't know this tool, I'll try to explain it briefly.

Basically, when doing autocomplete, it will try to best match the candidates that contain the letters your typed in the right order, but wherever in the string (not only at the beginning of the string, and not only in the same word). It will highlight the letters so you understand what you are doing. Put another way, you don't need to finish typing one word to start typing the next one you want to target.

For example, let's say we have those candidates:

… Sparks, Nevada Spokane, Washington Springfield, Alabama Springfield, Arkansas Springfield, Colorado Springfield, Florida …

If I type just sp, the drop down will show something like this:

Sparks, Nevada Spokane, Washington Springfield, Alabama Springfield, Arkansas Springfield, Colorado Springfield, Florida

Now I type spr, I'll have those suggestions:

Springfield, Alabama Springfield, Arkansas Springfield, Colorado Springfield, Florida

At this stage, I understand that I've selected all the Springfield values and so I can start filtering the context directly, so I would continue typing the first letter of the next word I want to target, for example I type spr a I'd see those suggestions:

Springfield, Alabama Springfield, Arkansas

But I want the city of Arkansas, so I'll need one more letter to filter it out. Typing spr ar:

Springfield, Arkansas

This pattern is so powerful that I think it could even replace the idea of drilling by a category then a subcategory, etc. For example, it would work the other way around, with categories before references. Eg.:

Fruits - Apple Fruits - Banana Fruits - Orange Vegetable - Carrot Vegetable - Potato

If I want to search inside the Fruits, I'd type fr to filter out only this category:

Fruits - Apple Fruits - Banana Fruits - Orange

Then I can select one of the values by clicking, or typing again.

Etc.

This would work also with Category > Subcategory > value.

Of course, the real pattern is more subtle than this (it does a bit of fuzzy, it consider spaces, etc.).

Hope I've not been too chaotic in my explanation! :)

[Vladimir-Va]

This query is related to this topic, do you think it will help you in your case? https://github.com/gristlabs/grist-core/issues/208

[anaisconce]

But when you already know what you are searching, I think nothing can compare to autocompletion in terms of efficiency and UX.

I agree! I was imagining autocomplete with the category > subcategory > value suggestion, too, but wasn't very clear. 🙂 Though you are right that seeing both categories alongside each other in the same menu as a concatenated string may work better.

What's still not clear to me is how to tell Grist what the categories are, in a UI friendly way. It feels tempting to add the ability right in the right-side creator panel's UI for reference column configuration. A link to "add category" similar to how there's a link to "add conditional styles" for conditional formatting. I can think on this more.

Just to be sure I'm not missing something: when exporting the document in your screenshot, both columns "Concatenated String" and "Ref Lookup" will be exported, right ?

Correct. I'm not sure how often you are exporting, and whether or not hiding the column would be an inconvenience on export. If it's a daily export, it might be better to have a filtered view for exporting. I've seen a user have a "To Export" page for this purpose.

Suddenly I feel an itch to make that easier with another feature. 🙂

[dsagal]

I think it does make sense to allow for separate configuration of what Reference cells show vs. what autocomplete options are offered in the dropdown. Here are some related requests:

• https://github.com/gristlabs/grist-core/issues/133 How to filter dropdown choices to show only employees whose role is "Manager". Includes a workaround using a formula as a display column.

• https://community.getgrist.com/t/how-do-you-show-only-certain-rows-from-reference-field/1333 How to filter dropdown choices to show only messages for which Active is "true". Includes some workarounds using formulas and/or access rules.

• https://community.getgrist.com/t/populating-a-reference-drop-down-cell-with-filtered-data-from-another-table/657 How to show only those values in the dropdown that have another field match a value in the current record. The only workaround is to change workflow to avoid using a dropdown.

• https://github.com/gristlabs/grist-core/issues/208 (already mentioned in this thread) For allowing to show a card for each dropdown choice, which may include an image.

• https://github.com/gristlabs/grist-core/issues/453; includes a link to an interesting related Cascader feature of aitable.

• https://community.getgrist.com/t/restrict-users-from-choosing-one-of-the-options-in-a-dropdown-list/4383. Requirement to restrict one option in dropdown only to certain users.

• https://community.getgrist.com/t/custom-widget-automatically-apply-useractions-a-k-a-the-self-clicking-action-button/4388/7: implementation of a workaround with a working example (uses a formula column based on last-update time to find the value to filter on, with last-update time set using a special "automatic action" custom widget).

Plus this issue, and the examples @anaisconce mentioned.

One unifying observation is that the dropdown for a reference is similar to a page widget (like a "Table" or "Card List"): it may be desirable to set filters on it, or linking (to the current record), and to select fields, and maybe to configure a card. Maybe we could come up with a way to configure the Reference dropdown by using the existing widget-configuration functionality, to solve all the various desires?

[yohanboniface]

Answering to myself:

One thing I'm thinking is that maybe in case of a ReferenceFIeld we could have two settings: which remote column to display which remote column to use as storage value (and thus export); that one would be optional and the first would be used if empty

Thinking a bit more, I'd say that's a bad idea. I think it's very important that what's exported is what is displayed.

That's said, what I understand is that we are trying to control two things, related to autocomplete in a ReferenceField:

• which results are going to be shown (filter)
• how those results are going to be shown (display)

About filtering the results, maybe an optional formula could do the trick, being in the same time simple, powerful and consistent with Grist UX ?

One may for example add a formula like this OtherTable.lookupRecord(role=$localRoleColumn).

About controlling the display, I see some options:

• a new optional parameter "Search Column", that defaults to "Show Column"; seems quite powerful (not as using a widget) and still simple (more than using a widget, I'd say); that would be my suggestion for a first move;
• a new optional formula to create the search result; it would take the ref record, and allow whatever one wants to do with such a formula, eg. return ref.category + " / " + ref.title;
• the above "search formula" would optionally return a tuple (record, label) instead of just the record to control in the same time the filter and the display:
  • default formula would be something like: OtherTable.all
  • one may control only the filter doing so: OtherTable.lookupRecords(xxx=$yyy)
  • one may control both filter and display doing so: (ref, ref.category + " / " + ref.title) for ref in OtherTable.lookupRecords(xxx=$yyyy);
• "widget like" settings, where one could choose the "theme" of the displayed dropdown, and maybe which fields are going to be shown and in which order; seems more demanding, both when implementing and when using it imho

The tricky part is that it's important that the user understand that they are controlling the autocomplete behaviour, but not the way the value is going to be displayed in the column, and it's not going to be a validation layer (if I add a filter while the column is already filled, values that do not match the filter will not be changed nor marked as invalid or whatever).

Food for thought… :)

[dsagal]

Collecting the various scenarios that have come up, and the various desires, I propose a first step that addresses the majority of cases: to add filtering for values in the dropdown.

Specifically, something like this:

How it works:

1. When a condition formula is set, only matching values would be shown in the dropdown. The match is between a special ref variable, which stands for the referenced record that's being considered for inclusion in the dropdown list, and the current record (into which the new value would be saved).
2. The conditions are limited to the same subset of Python as Access Rule conditions. This allows them to be evaluated in the browser. All the examples so far have only needed to compare values (==, !=), and sometimes to combine with and and or; that would be supported.
3. If it turns out easy to expose user as a variable, including UserAttribute values, it would also be useful for some cases.

Examples, based on the needs in issues linked earlier:

• ref.Role == "manager" -- only show managers rather than all employees
• ref.Active -- only show projects whose toggle column Active is on.
• $Group in ref.All_Groups -- only show projects associated with the group that the current user belongs to.
• ref.Country == $Country -- only show cities whose country matches the country of the current record.
• ref.Team == user.TeamMember.Team or user.TeamMember.Role == "Manager" -- only allow selecting a team member from the same team as the current user, or allow selecting anyone when the current user is a manager (relying on a custom UserAttribute 'TeamMember' configured in access rules)

What's not addressed by this first step is:

• Display of values in dropdown (e.g. to show multiple fields, or customized cards)
• Ordering of values (i.e. the dropdown still has autocomplete behavior, filtering and ordering based on what's typed in)
• Filtering Choice dropdowns

The reason I think this is still a good path forward is that it seems quite doable, and addresses many of the needs that are currently either impossible or require difficult workarounds. And I don't think it makes it harder to work on other related wishes listed above.

[paulfitz]

That looks like a great addition @dsagal. Achieves very sought after functionality, and is relatively easy to implement - a good combination.

[emanuelegissi]

Yes, from a user perspective, It would be great!

[dsagal]

Filtering for dropdown values is now released and available in production!

Look for the option "Set dropdown condition" in the creator panel when configuring a Reference or Reference List column (also Choice and Choice List). For clarity, the syntax is a bit different from proposed: the records in dropdown choices are represented by the special variable choice. Here are some examples:

• choice.Role == "manager" -- only show managers rather than all employees
• choice.Active -- only show projects whose toggle column Active is on.
• $Group in choice.All_Groups -- only show projects associated with the group that the current user belongs to.
• choice.Country == $Country -- only show cities whose country matches the country of the current record.

Feedback welcome! In particular, we'd particularly appreciate suggestions for a UI that makes this feature easier to use.

[emanuelegissi]

Hi Dimitry @dsagal , this is indeed great news! Thank you for your hard work.

A couple of days ago I've started using the feature in the 1.1.13 version. I was wondering how to filter the dropdown depending on the current user using the UI, it seems to me that the user variable is not available.

Do I miss anything? Thank you

[dsagal]

Hi @emanuelegissi. Yeah, this first iteration doesn't include support for the user variable. Hopefully we can add that soon.

[riesjart]

@dsagal Thank you for adding this feature! 👍

Trying various formula I often get Error in dropdown condition without any details. It looks like this happens when accessing relations on the current record and/or choice. Is possible to output the encountered error so it can be debugged? Thanks!

[dsagal]

Yes, I see the issue. When there is an error in the condition, it is (usually) reported immediately below the condition formula. But in the dropdown, all one sees is "Error in dropdown condition".

I notice that error handling is a little strange. E.g. a syntax error (for a meaningless condition like !) disappears if you select another column and return; and the bad condition seems completely not saved if you reload the page. For other errors, the condition does get saved. Some errors never get shown: e.g. using $Foo.Bar.Baz in a condition formula isn't supported, but it produces no error explanation at all, just the "Error in dropdown condition" message when trying to use it.

Thanks for drawing our attention to it. Hopefully these issues can be polished up in the next iteration.

[emanuelegissi]

this first iteration doesn't include support for the user variable. Hopefully we can add that soon.

Great news! Thank you.

[gwhthompson]

This is great - thank you! One thing I've noticed is that "add new" is disabled if dropdown conditions are set.

It would be very helpful these weren't mutually exclusive. E.g. in a big table it would be good to be able to 'archive' older records so they don't appear in the dropdown while still being able to easily create new ones.

Perhaps this could be implemented along similar lines to 'newRec' in the ACL? I.e. if the condition would evaluate to true on the new record then it may be added.

[PhilRW]

This is great - thank you! One thing I've noticed is that "add new" is disabled if dropdown conditions are set.

I agree, I'd like to see this implemented somehow for smoother UI Flow.