Orderby refs

AppSheet AppSheet Q&A
1

Hey guys,

I have a Row ref in my Plant Information table, a list of related recipes.

I would like these recipes to be in alphabetical order.

I am using this expression but it seems to not be working:

ORDERBY(REF_ROWS(“Recipe Plant Merge Table”, “Plant”), [Recipe Title], TRUE)

Tips welcome!
Best,
Miranda

2

Hmmm, I tried finding some documentation, but wasn’t sure where to look.

You’re close, instead of using the REF_ROWS thing, try this instead:

[Name of the REF_ROWs column][Recipe Plant Merge Table ID Column]

Notice there’s no dot or space between the square brackets.

  1. the first part is the name of the column that has the REF_ROWs formula
  2. the second part is the name of the column in the Recipe Plant Merge Table that’s marked as the key

This will generate a list of records from the related refs list - then, since you’ve wrapped it with ORDERBY, it should come in order.

3

You could also use an appropriate ORDERBY(SELECT(...)) statement as well, dropping the REF_ROWS().

4

If you include the related records list instead of the table portion inside any of those select style formulas, the system will only run the select function over the records inside that list.

So say I’ve got a database of 300,000 records, but the related records for a specific individual record amounts to only five.

If I use the related records formula part, the select function only operates over the five records instead of the 300,000.

It can be tricky to get it to work sometimes, syntax and all of that… But when it works it really speeds up your app and increases the efficiency.

5

You know what guys, thank you for all the help, but it took me FOREVER to get the related refs to show up in the app exactly how I wanted them, and tinkering with all this stuff makes me nervous so I’m just going to order my lists in Drive alphabetically to achieve this end instead of messing with the app.

Thanks for your input though.

1 Like
6

You’re welcome! And don’t stress about it, AppSheet is deep and layered - there’s always more to learn.

7

This:

REF_ROWS(“Recipe Plant Merge Table”, “Plant”)

is equivalent to this:

FILTER(“Recipe Plant Merge Table”, ([_THISROW] = [Plant]))

is equivalent to this:

SELECT(Recipe Plant Merge Table[key-column], ([_THISROW] = [Plant]))

(replacing key-column with the name of the key column of the Recipe Plant Merge Table table)

In order to successfully use any of the above with ORDERBY(), The Plant column of the Recipe Plant Merge Table table must be of type Ref and refer to the Plant Information table.

8

If you’re trying to order row in a view, that can only be done from the view configuration. Ordering the list of rows given by REF_ROWS() will in no way affect the order the rows are presented in a view.

1 Like
9

Steve:

REF_ROWS(“Recipe Plant Merge Table”, “Plant”)

This one will process faster, at least it used to when compared to any of the SELECT() ports.