Is there anyway to sort a referenced drop down in the order that I want? (NO ASC NOR DESC)

[Jon_Capria]

Hi,

Starting with the two tables:

• Customer Table
• Order Header Table

In the Order Header form, I have referenced the customer table so I can select which customer is purchasing from me… So far I have found out how to sort by name asc or desc but I was wondering if there is any way to sort the dropdown in the order that I want.

The whole purpose is convenience, I have a customer who purchases way more often than the rest but his name starts with an M so I always have to scroll down or type his name in order to select him and it would be so much easier if I had him on the top and the rest asc order.

If there is any way to do that I would appreciate it your thoughts and ideas.

Thanks.

[LeventK]

@Jon_Capria
This is what you want I assume

help.appsheet.com

ORDERBY()

Sort row references

[Suvrutt_Gurjar]

Jon_Capria:

I have a customer who purchases way more often than the rest but his name starts with an M so I always have to scroll down or type his name in order to select him and it would be so much easier if I had him on the top and the rest asc order.

As guided by @LeventK, you could use ORDERBY() expression in valid_if of ref column to sort the customers alphabeticaally in ascending order.

For the customer whose name starts with an M, you could have his name ( key value of that customer record in Customer table) in initial value of the reference column. For any new record being added, through the form of the “Order Header Table”, the default customer choice will be the “M” customer, which you can change, if you are adding record for some other customer.

help.appsheet.com

App Formulas and Initial Values

How you can set app formulas and initial values to dynamically assign values.

[Jon_Capria]

Thank you all for your answers.

This is what I ended up doing just for mere preference…

• I created a new column on google sheets called “order” type integer,
• On AppSheet, I regenerated the Structure of the columns so I can see it on my app.

• I set the number 1 to my “M” customer.
• I used ORDER BY expression in valid_if of ref column to sort the customers by my order column first and then alphabetically in ascending order.

It may not be the smartest solution but it works for me and I can also add numbers like 2, 3… and on in case there are more favorite customers.

I wouldn’t have been able to figure it out without your suggestions.

Thanks a lot!

[Steve]

Jon_Capria:

It may not be the smartest solution

That’s pretty much how I would have done it.

[Pratyay_Rakshit]

If you wish to order the customers according to fondness, meaning the ones who purchase more would end up on top, you can use the count function and incorporate it in the orderby function.

Assuming the purchase table has reference to the customer table (it most certainly should), you should be able to count the number of times a customer has made a purchase.

1. Add a virtual column, [Purchase Count], with type ‘number’, to the customer table, with formula:

Count(Select[Purchase table Name], [Customer Name column in purchase table]=[_THISROW].[Customer Name column in customer table]))

This outputs a number which is the count of purchases made by a customer.

2. Add a virtual column named Customers by Count in the Purchase table, with type enumlist and base type ‘ref’, as reference to the customer table. It’s formula would be:

orderby(filter(“Customer Table Name”, TRUE), [Purchase Count], TRUE)

This column stores all the values from the customer table sorted according to the purchase count. If you want to sort ascending, omit the TRUE after the [Purchase Count].

3. In the column to choose the customer in the purchase table, go to the valid_if field and input the following bulk dereference formula:

[Customers by Count][Customer Name column in customer table]

This produces a sorted list of customers according to the number of purchases they have made. Don’t use a " . " in the bulk dereference, as the “.” is only meant for single dereference, not bulk.

This was all taught to me once by @Steve, and I owe it completely to him.