Unable to use [_THISROW] with inputs in bot's 'Run a data action task'

[sak]

Has anybody encountered the same bug? To reproduce the bug, create a ‘Run a data action task’ (Set row values) and use inputs with SELECT() expression containing some condition like [invoice_uuid] = [_THISROW]

For example:

SELECT(sale_order_account_invoice_rel[sale_order_uuid], [account_invoice_uuid] = [_THISROW].[uuid])
–The above expression would produce a blank value.

But if I use:

SELECT(sale_order_account_invoice_rel[sale_order_uuid], [account_invoice_uuid] = [uuid])
–This would work fine.

But using the above expression would be a big issue if 2 tables share the same column name.

[Jose_Arteaga]

Hello @sak,

Here’s what I could find:

SELECT(sale_order_account_invoice_rel[sale_order_uuid], [account_invoice_uuid] = [_THISROW].[uuid])

This query scans the sale_order_account_invoice_rel table row by row. So AppSheet will look for uuid within the same table.

SELECT(sale_order_account_invoice_rel[sale_order_uuid], [account_invoice_uuid] = [uuid])

By removing [_THISROW], you allow AppSheet’s parser to look outside the target table and correctly grab the [uuid] from the parent record that is actually calculating the formula.

Now, please note running aSELECT() query can cause your app to sync slowly. If you have properly set up your table references (Ref column types), you can achieve the exact same result instantly by using De-referencing or a Related List virtual column instead:

[account_invoice_uuid].[Related sale_order_uuids].

I hope this helps!

[Steve]

Please provide screenshots of the action configuration, the data action task configuration, and the non-working expression.

[sak]

Hi @Steve ,

Here’s the screenshot of my bot config. The “[_THISROW].” expression caused the result of this task to be a blank value. But if delete the “[_THISROW].”, then the expression would produce a correct value.

[sak]

Thank you for your suggestion @Jose_Arteaga . But I can’t use direct dereference because this is a many-to-many relation between 2 tables. So I need a middle table to accommodate the many-to-many relation.

[Steve]

Consider this example:

(1) is the step (your input file step), (2) is the event that gets triggered, (3) is the table the bot is attached to. In the step, [_THISROW] refers to the row of the bot’s table (3). Does your table have a column named uuid?

[Steve]

You could also be running into some quirky AppSheet behavior. The Automation feature was added much later in AppSheet’s life, and the team working on it were mostly new to the AppSheet team (and departed shortly after Automation was finalized). Automation did and does several things slightly differently than the rest of AppSheet, so we could try adjusting your configuration to be sure it does exactly what you want.

1. If possible, set Blank value comparison mode to Consistent:
   

   

2. If you cannot do (1), try adjusting your expression to confirm [account_invoice_uuid] is not a blank value:

   SELECT(
       sale_order_account_invoice_rel[sale_order_uuid],
       AND(
           ISNOTBLANK([account_invoice_uuid]),
           [account_invoice_uuid] = [_THISROW].[uuid]
       )
   )
   

3. Technically, you want a singular value, but SELECT() returns a list of values (even if it’s a list containing only one value). A more appropriate way to get only one value is to wrap SELECT() with ANY():

   ANY(
       SELECT(
           sale_order_account_invoice_rel[sale_order_uuid],
           AND(
               ISNOTBLANK([account_invoice_uuid]),
               [account_invoice_uuid] = [_THISROW].[uuid]
           )
       )
   )
   

   Or use LOOKUP():

   LOOKUP(
       [_THISROW].[uuid],
       "sale_order_account_invoice_rel",
       "account_invoice_uuid",
       "sale_order_uuid"
   )
   

[sak]

Hi @Steve ,

• The “Blank value comparison mode” is already set to “Consitent”
• The result I want is actually LIST type

[Steve]

@sak

• The result I want is actually LIST type

Not according to (1) and (2):

Inconsistent data typing can definitely cause problems.

[sak]

Hi @Steve ,

Thank you for pointing out. So I change the expression of the input to:

CONCATENATE(
SELECT(
sale_order_account_invoice_rel[sale_order_uuid],
[account_invoice_uuid] = [_THISROW].[uuid]
)
)

And the result is still blank.