[_THISROW] in Template Still an Issue Or Again?

AppSheet AppSheet Q&A
,
1

Just came across this issue in an Excel template and seemingly there was reports about this same issue in early 2023 and late 2023 - early 2024. I am using the following expression in my template and it appears that [_THISROW] is not properly applied:

<<SUM(SELECT(WaterUseData[Water_Use_Acre_Feet], AND([MeterInformation_fk] = [_THISROW].[MeterInformation_fk], [Year] = [_THISROW].[Report_Year])))>>

Note that [MeterInformation_fk] is a column in both tables in this case, which is why [_THISROW] is an important factor in the expression. If I add the same expression into a VC in my table and just use the VC in my template then this works out fine. But, I really only need this calculation in the template, so using a VC adds unnecessary calculation time in the app.

Anybody else know the status? @devingu @Koichi_Tsuji @takuya_miyai @Rifad

2

Should work fine.

3

It appears that in the context I am using it does not apply any of the filters in the AND() expression, however I am using the same expression in the VC where it is correctly evaluated. See screenshot below of my template:

Markus_Malessa_0-1725044499321.png
Markus_Malessa_0-1725044499321.png1352Ă—166 12.2 KB

So I’m just wondering

4

Here is a screenshot of the result. The green is correct based on the VC the red is incorrect and I am unsure what filter it actually applies if any.

Markus_Malessa_0-1725044826514.png

I should add that the above result is with the expression slightly modified to:

<<SUM(SELECT(WaterUseData[Water_Use_Acre_Feet], AND([MeterInformation_fk] = [_THISROW].[MeterInformation_fk], [Year] = [Report_Year])))>>

Note that I removed one of the [_THISROW] references in the AND(). If I use the expression in my initial post the result is 39,000,000. I am very certain that it is because of the _THISROW factor and the 39 million result probably is the sum total of that table column for all 300,000+ rows.

5

[_THISROW] isn’t referencing the row you think it is, I’d bet. Try [_THISROW-1] instead. See also Back-references within nested queries ([_THISROW-n])

1 Like
6

Seemingly this is the solution, although I am not certain why. I presume because my <> expression is essentially my main query, therefore the SELECT() expression becomes a nested query. I think what I am missing is why is it treated as a nested query in the template, but not in the VC expression?

7

The “main query” is actually above/outside the <>…<> tags. If the bot is running against a row (triggered by an add, update, or delete), [_THISROW] refers to that row. If the bot is scheduled, I’m not sure it refers to anything; it may be undefined and silently ignored.

2 Likes