I have a table containing one column of text ...

(David V) #1

I have a table containing one column of text data and a second column of numeric values I want to use to sort the text when I populate a list in another table.

I have no problem creating the list (either via a Ref or a SELECT) but I can’t see how to get the text to appear in the order I want.

Am I missing something or is there a workaround?

(David V) #2

would ORDERBY help me with this?

(Grant Stead) #3

@David_V yes.

(David V) #4

I followed the “Top related items” example so List1 now contains my initial list & List2 has a formula ORDERBY([List1].[NameOfFieldWithSortValues]) but this doesn’t seem to make any difference.

I then noticed the example starts off with its data already nicely sorted by value and when I changed this around it still find the top discounts but they’re displayed in the original (unsorted) order

How can I get List1 sorted in the order I want?

(Grant Stead) #5

@David_V You don’t have to be shy, hook me up with the actual column and table names so I can just pound out the expression… :wink:

ORDERBY() will organize a list of KEYs…

Sorting a List of Keys with ORDERBY

You can sort a list of Keys using the ORDERBY function.

This can be used to sort the values in a Ref dropdown (by modifying the App Formula of a Related References virtual column) or to control the order of rows displayed in a workflow rule Start expression.

ORDERBY(, , , , , …) to sort a list of keys. The first argument must yield a list of references, i.e. a list of the keys of the records to sort. This is followed by one or more pairs indicating a column name to order by, and its ordering direction. The value TRUE indicates “Descending” order. The value FALSE indicates ascending order. If the data should be ordered by just one column (which is the common case), the ordering direction may be omitted and defaults to FALSE (so the rows are sorted in ascending order). For example, OrderBy([Related Orders], [Customer Name], [Order Date], FALSE).

SOOOOO the best thing to jamb in for is a FILTER.

FILTER is a special case of SELECT which is used to construct a list of row references by applying a filter condition to a table.

FILTER(Customers, [State] = “WA”)

— returns a list of customers in WA state FILTER(Customers, [Country] = “USA”)

— returns a list of customers in the USA

(David V) #6

@Grant_Stead Thanks Grant, the App is just a personal one for a language project so I don’t think I can share it but I can explain it using this one: appsheet.com - Top related items - How to find the top four items in a list I copied the App and changed the discount for the first Item from 0.1 to 10. When I open the form for Item1 and and scroll down to see the top four coupons they appear in the original order with 10 at the top even though it’s the biggest value. How can I get it in the right place at the bottom? I tried editing the formula to read ORDERBY([Item Coupons], [Discount], TRUE) but then the results are exactly the same as the original list. Top related items - How to find the top four items in a list appsheet.com

(Grant Stead) #7

@David_V hrmmm interesting…

Try making it .9 and tell me what happens.

(David V) #8

@Grant_Stead: See the snip (the field name is now the App formula); the list is still unsorted. I also tried putting the same formula in the Valid_If qualifier

but that didn’t change anything

(David V) #9

@Grant_Stead - did you take a look at this? I’ve not managed to find a workaround yet and it would be much better to use the right expression