REF_ROWS() documentation

[Swoopy]

Refer to this REF_ROWS()

Should they be…? :

FILTER(“Order Details”, ([_THISROW].[Order ID] = [Order ID]))

SELECT(Order Details[Order Details ID], ([_THISROW].[Order ID] = [Order ID]))

[Marc_Dillon]

No, they are correct. [_THISROW] is the key value of the current record, and [Order ID] is the Ref type column in the Order Details Table.

[Swoopy]

OK I got it.

1.The Order table must be using record number as key column.

2.The expressions are used in the Order table itself (as being parent to the Order Details table).

My question came from the thought if the record number is not the key column of the Order table (practically is the order’s serial number of some format). I myself never use record number as key column for any tables.

[Marc_Dillon]

Swoopy:

1.The Order table must be using record number as key column.

There is no reason to think that.

Swoopy:

2.The expressions are used in the Order table itself (as being parent to the Order Details table).

Correct

Swoopy:

My question came from the thought if the record number is not the key column of the Order table (practically is the order’s serial number of some format). I myself never use record number as key column for any tables.

Agreed, using _rownumber as a key is very rarely appropriate. But these expressions have nothing to do with _rownumber.

[Swoopy]

I’ve just seen [_THISROW] being alone without any following dot (.).

Also, ?how are they different from:

FILTER(“Order Details”, ([_THISROW].[Order ID] = [Order ID]))

SELECT(Order Details[Order Details ID], ([_THISROW].[Order ID] = [Order ID]))

[Steve · Accepted solution]

There is no difference. Your approach may offer more clarity to someone reading the expression, and therefore may be preferable.

[Swoopy]

I played with SELECT SQL a lot, still be confused by the samples, let alone the new comers.

[Steve]

Swoopy:

I played with SELECT SQL a lot

AppSheet expressions don’t use SQL at all. Perhaps that’s contributing to your confusion?

[Swoopy]

Since AppSheet uses the name ‘SELECT’ for this function. I believe there’re also others people moving from database world (more or less) they’d pair this function to what they familiar with, the SELECT statement (in all SQL counterparts), just like me because AppSheet’s SELECT function shares some aspects like the SQL SELECT.

Anyhow your sample expressions for REF_ROWS() give clues to what I (we) might not fully understand in AppSheet architecture.

[Marc_Dillon]

Swoopy:

how are they different

Your expressions assume that the parent Table has an [Order ID] column, and that it is set as the key column. The expressions in the document do not make those assumptions, and will still be equivalent to the REF_ROWS expression even if the parent Table has a different key column, because [_THISROW] always returns the key value.