Back-references within nested queries ([_THISROW-n])
Consider a complex App formula with “nested” SELECT() expressions (SELECT() expressions within SELECT() expressions):
SELECT( table[column1], ... SELECT( table[column2], ... SELECT( table[column3], ... ) ... ) ... )
Let’s call the row for which this entire expression is evaluated as its App formula the Origin Row.
Within this entire expression, we can refer to column values of the Origin Row by dereferencing
[_THISROW]. For instance, to get the value of the Customer Name column of the Origin Row, we can use
Let’s also name each of the nested queries within the entire expression:
SELECT(table[column1], ...)(outer-most query)
SELECT(table[column3], ...)(inner-most query)
Query 2 is nested inside Query 1. Within Query 2, column values of the row currently being examined by Query 1 can be accessed by dereferencing
[_THISROW-1]. For instance, to get the value of the column1 column of the Query 1 row, we can use
_THISROW-1 refers to the query one step outside the current query.
Query 3 is nested inside Query 2.
[_THISROW-1] refers to the query one step outside the current query, so within Query 3,
[_THISROW-1] refers to the current row of Query 2. Query 2 is itself nested within Query 1, so Query 1 is two steps outside Query 3.
[_THISROW-2] can be dereferenced to access the values of columns in the query two steps otside the current one: Query 1.