LIST Deference

[jaichith]

Dear friends,

                   I have a doubt in list deference expression

Working Expression : 

                          ANY(SELECT(Courier[VLW DIV], AND([COURIER NAME] = [_THISROW].[CONSIGNMENTS SL NO].[COURIER NAME], [COURIER TARIFF] = [_THISROW].[CONSIGNMENTS SL NO].[COURIER TARIFF])))

Expression which did not work :

                        ANY(SELECT(Courier[VLW DIV], AND([COURIER NAME] =[CONSIGNMENTS SL NO].[COURIER NAME], [COURIER TARIFF] = [CONSIGNMENTS SL NO].[COURIER TARIFF])))

Error returned by expression assistant : [CONSIGNMENTS SL NO].[COURIER TARIFF] not found

Why this expression does not works without [_this]. ? Is it due to the wrapping of select () function? 

but other places I have used many list deference expression like [pincode].[state] , it works fine. 

please explain the reason behind it.

[WillowMobileSys · Accepted solution]

When using table based functions like SELECT(), column references are first assumed to be from the table operating on.  If that column is not found then AppSheet uses context, as much as possible, to derive which column you mean.  There are cases where context-based derivation doesn't succeed OR there is ambiguity (i.e. more than one possible answer).  Sometimes it's not clear why the intended column can't be found.

As a rule of thumb, I always use the qualifying [_THISROW] inside of SELECT() and any other table-based expressions.

I hope this helps!

[WillowMobileSys · Accepted solution]

When using table based functions like SELECT(), column references are first assumed to be from the table operating on.  If that column is not found then AppSheet uses context, as much as possible, to derive which column you mean.  There are cases where context-based derivation doesn't succeed OR there is ambiguity (i.e. more than one possible answer).  Sometimes it's not clear why the intended column can't be found.

As a rule of thumb, I always use the qualifying [_THISROW] inside of SELECT() and any other table-based expressions.

I hope this helps!

[TeeSee1 · Accepted solution]

https://support.google.com/appsheet/answer/10108207?hl=en

As @WillowMobileSys says you should always use it to indicate precisely which column you mean.