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

David_V
Participant I

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?

0 8 465
8 REPLIES 8

David_V
Participant I

would ORDERBY help me with this?

@David_V yes.

David_V
Participant I

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?

@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โ€ฆ

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
Participant I

@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

@David_V hrmmm interestingโ€ฆ

Try making it .9 and tell me what happens.

David_V
Participant I

@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
Participant I

@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

Top Labels in this Space