List expression works in Test but fails in Action

Hi there,
I have a list expression, containing SELECT with an inner IN() on another SELECT. This expression works in Test but fails during an Action to “Add a new row to another table using values from this row”.
The goal of the Action is to copy the children of a parent record (after having copied the parent itself). For the time being it only copies one child at a time, and this is enough for catching the problem.

How to reproduce
Create a table Table_A with key column ID and text column Name.
Similarly, create a table Table_B with key column ID and text column Name.
Create a table Table_AB with key column ID and columns Table_A and Table_B referencing the respective tables.
Create the Action Copy Child AB of type “Add a new row to another table using values from this row” with the following fields:

  • For a record of this table: Table_A
  • Table to add to: Table_AB
  • Set these columns:
    • Table_A:
ANY(SELECT(Table_A[ID],[Name]=CONCATENATE("Copy of: ",[_THISROW].[Name])))
  • Table_B:
ANY(SELECT(Table_AB[Table_B],[Table_A]=[_THISROW].[ID])-SELECT(Table_AB[Table_B],IN([Table_A],SELECT(Table_A[ID],[Name]=CONCATENATE("Copy of: ",[_THISROW].[Name])))))

Now, create a row for Table_A with Name = A_1 and another with Name = Copy of: A_1
Create a row for Table_B with Name = B_1 and another with Name = B_2
Add B_1 and B_2 to A_1 (this is equivalent to add 2 rows to Table_AB with column Table_A = the ID of A_1, and column Table_B = the IDs of B_1 and B_2)

Finally, launch twice the Action on A_1. This should add B_1 and B_2 to Copy of: A1. I.e., it should add 2 rows to Table_AB with column Table_A = the ID of Copy of: A_1, and column Table_B = the IDs of B_1 and B_2).

But, it works well only for the first row. The second row added has Table_B column empty.

I get exactly the same result (empty column Table_B on the second run) if I change the expression into:

  • Table_B:
ANY(SELECT(Table_AB[Table_B],[Table_A]=[_THISROW].[ID])-SELECT(Table_AB[Table_B],[Table_A] = ANY(SELECT(Table_A[ID],[Name]=CONCATENATE("Copy of: ",[_THISROW].[Name])))))

Or:

  • Table_B:
ANY(SELECT(Table_AB[Table_B],[Table_A]=[_THISROW].[ID])-SELECT(Table_AB[Table_B],[Table_A]=LOOKUP(CONCATENATE("Copy of: ",[_THISROW].[Name]),Table_A,Name,ID)))

WORKAROUND
The workaround is the well known use of a virtual column in Table_A with one of the above mentioned not-working formulas for a Table_B’s reference pointing towards the first not-yet copied child row.

Please describe in plain language what you think this expression is doing.

2 Likes

Hi Steve
Sure.
The expression you mentioned works. It should populate the Table_A’s reference column of the new row with the key of the parent row to be copied from.
Suppose Table_A represents a person, and Table_B represents a task. A task can be assigned to many persons, and a person can have many tasks, therefore it’s a Many-to-Many relationship.
Suppose that Person A has 2 tasks, B_1 and B_2.
And suppose the person Copy of: A has no tasks, but I want to assign him the same tasks B_1 and B_2. I want to do this by launching the Action on the A person.
Therefore the expression searches for the person whose name is the concatenation of "Copy of: " and A and creates a person-task record for the person found by the expression.

The problem is the second expression, that should search for the tasks assigned to person A and not yet assigned to person Copy of: A.
Hope it’s clearer now, if not please let me know :slightly_smiling_face: