Is there a way to automatically sort the Enum...

Is there a way to automatically sort the Enum values in a dropdown when that list is populated by looking up another table?

I have a column called Vendor in a table, which Refs a different table called Vendors. The Vendor column looks up the Vendors table and displays the Vendor names as an Enum dropdown. (A very cool feature).

The Vendors table is writable by the user, since they need to be able to add a new vendor in the field as needed. Using the lookup feature means that the next time they encounter that vendor he/she will be on the dropdown list and they wonโ€™t have to enter it by hand.

Iโ€™d like the dropdown list to be sorted alphabetically. I can sort the Vendors table in the spreadsheet, in which case the names will appear in the right order in the dropdown, but when the user adds a new name that name is appended at the end of the table (and in the dropdown). Not surprising, but I was just wondering if there was a way to sort the list on the fly instead of me sorting the table in the spreadsheet every time a new name is added.

The Vendors lookup table is keyed by the Vendor name.

Thanks!

1 18 3,129
18 REPLIES 18

Hi +Charlie Wells, You may want to look at latest post by @praveen under Feature of The Week.You may want to try the Sort function mentioned therein.

Request you to put expression such as =SORT(Vendors[VendorReference])

in valid_if of column Vendor where you wish to have a drop down list.

Vendors and VendorReference are respectively lookup table and lookup column in the lookup table where vendor list is stored.

Hope this is what you are looking at and helps.

Can anyone point me to @praveenโ€™s post? I canโ€™t find it on his profile.

Thanks in advance!

That post is 3 years old when the community was on another community platform. Similarly blog post being older, I could not search it in blog section.

However , since the post was about SORT() , you may wish to take a look at help article on SORT() and ORDERBY()

Thanks mate

Thank you @Suvrutt_Gurjar, It works 

 

@Suvrutt_Gurjar funny I just saw that too! Very good timing :=). Iโ€™ll give it a try. Thank you!

@Suvrutt_Gurjar โ€ฆ and it worked beautifully. Thank you and thanks to @praveen and AppSheet!

Best tool ever.

Hi +Charlie Wells, nice to know the solution worked for you. You are right-AppSheet platform,AppSheet team and AppSheet community are a great combination to work with.

Quick question, probably a dumb one: how is Sort() different to Orderby()?

Hi @David_Ackling-Jones,As per my understanding, the SORT will work on list composed of any column in a table and has a simpler format, Just the list to sort out with possible option of ascending or descending with False/True parameter.

ORDERBY will work only on REF fields. Also ORDERBY has more options like sorting a key value by another column value such as say Date etc.

The List Expression documentation explains it well help.appsheet.com - List Expressions and Aggregates List Expressions and Aggregates help.appsheet.com

Ty

ten4info
Participant V

@David_Ackling-Jones, @Suvrutt_Gurjar, SORT with valid_if worked great! Thank you.

@Suvrutt_Gurjar, question, how can you use this expression while also letting the user add a value to the lookup data? I found the add value option and toggle it on, but to no availโ€ฆ

Is there a way to do this?
I am in need of this too...

Wally_Young
Participant II

Iโ€™m trying to do something similar with an Enum drop down list to display the list sorted by the most frequently selected items to the least selected. The Enum is of Vendors in my Expenses table, and has Valid If set to Expenses[Vendor]. How can I display the list of Vendors sorted in this way? Thanks in advance!

Any ideas or suggestions?

Youโ€™ll need to track the frequency-of-selection for each item; AppSheet doesnโ€™t do this itself. Once youโ€™ve got that, the values can be used to sort the list.

Thanks, I thought this should have been easier to just do a count of the number of occurrences of a Vendor in my Expenses table and then use this to display the vendors sorted descending orderโ€ฆ Ended up creating a Vendors table with the list of vendors, along with a RelatedExpenses virtual REF column with the โ€œSELECT(Expenses[Vendor], [Vendor] = [_THISROW].[Vendor Name])โ€ formula, and another ExpensesCount virtual column with COUNT([RelatedExpenses]) and I can then use that when displaying my list of vendors to pick from.

Top Labels in this Space