Re: List expression works in Test but fails in Act...

perissf · 06-08-2021 05:01 AM

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.

Steve

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

perissf

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

perissf

Yes, I want to copy parent record together with child records. But the situation is slightly different from the one referenced in this post, because the relationship between tables is of type Many-To-Many.

The action giving a problem is the one copying the child record(s). The idea is that this action should be repeated several times in a loop.

Steve

I’m completely confused. Your problem description is completely disorganized and I just can’t follow it. I need a simple, straight-forward description of the problem you need solved. Do not refer me to other posts or relate the problem to some other problem.

perissf

Ok
Let’s start from scratch.
I want to represent a situation with some persons who are assigned some tasks. The same person can have many tasks, and the same task can be assigned to many persons.
I have a person and 2 tasks. These 2 tasks are assigned to this person.
I want to create an action, acting on this person, that creates a copy of the person and then adds the same tasks to the target person just created.

In order to accomplish this, I did the following.

Create a sheet named Person with two columns, ID and Name
Create a sheet named Task with two columns, ID and Name
Create a sheet named Person_Task with columns ID, Person and Task.

Create a new AppSheet app. Create the 3 tables. AppSheet will automatically create the views and relationships.

Using the App, create a Person with name Person1 and 2 tasks with name Task1 and Task2.
Edit the Person in order to add the two tasks to him.

I need an action (or a set of actions) that create a copy of Person1, assigning to him the name Copy of: Person1. I did the action, it’s straightforward.
Then I need another action to assign to Copy of: Person1 the same tasks assigned to Person1. The action should be launched from Person1 but act on Copy of: Person1.

I created this action, without using additional tables nor workflows, nor API. The action seems 100% correct, because I tested the results, but when launched it creates rows in Person_Task table with empty Task column.

perissf

@Adam or somebody else of AppSheet team,
have you had the chance to have a look at this issue?

List expression works in Test but fails in Action