Accessing columns of the row that triggered a "Execute an action on a set of rows"

[BenBl]

Hey all,

Quick version:

Action 1 is defined: For a record of Table A, “Execute an action on a set of rows”. Referenced table: Table B. Referenced action: Action 2.

Action 2 is defined: For a record of table B, “Add a new row to another table using values from this row”。 Table to add to: Table C.

In action 2, I want to set a column of the new row in Table C equal to the key of the row in Table A where action 1 was used. Is that possible? How do I refer to that row in table A from Action 2?

Long version

If that’s not possible, here’s my use case.
Table A contains orders (columns: order number, pieces ordered, and related worktasks to fabricate pieces ordered).

Table B contains fabrication steps (columns: step, estimated time per piece)

Table C is the worktasks Table A refers to. Columns for table C:fabrication step, duration, start time, end time, employee assigned, associated order.

The idea of the whole flow is to, for a particular order, create one of each kind of worktask (e.g. prep, create, package) listed in table b.

I use table b as an intermediary because the fabrication steps are altered from time to time. It

If there isn’t an easy way to do what’s in the “quick version”, is there a work around to accomplish this?

[perissf · Accepted solution]

It’s doable.
Check out this post, it’s exactly the same situation: table P_S in the post is the equivalent of your table C - Worktasks, and People corresponds to your Order.

How Would You: Create a view to score a large number of people on a large number of skills Questions

Here are the actions to define in order to add records to People Skills stable when a new record to People is added. No need to use counters nor control columns nor control tables. Similarly, you’ll probably need to create actions for handling the creation of new skills. Action 1: Add a row to P_S from People For a record of this table: People Do this: Data: add a new row to another table using values from this row Table to add to: People Skills Set these columns: Person: [_THISROW].[ID] …

[BenBl]

How Would You: Create a view to score a large number of people on a large number of skills

Skills

Jeeze louise. I finally got this to work. Thanks for your help.

I ran into a bug – I’m not sure if this applies to the exact use case you described or not. I’ll explain it here in case anyone else runs into this challenge.

In your solution, the assumption is that the list table (Skills) is being referred to by the other tables. In my case, this is not the behaviour I wanted. I have a list stored as table B. Table C references table A. But neither reference table B and table B references neither. So I had to subtract the list of “skills” for each row in table A from the whole list of “skills” in table B. The bug here is that the action does not properly parse the select statement when you’re selecting text values instead of refs. The real sinister thing about it: it works fine when you run the “test”, but not when you actually run it in an app.

I had to do create a virtual column for table A doing the list subtraction. Then, do an Any() on that virtual column in the action.

The bug was posted here and apparently hasn’t been resolved. To the Appsheet devs: This is really frustrating. First, one needs an elaborate workaround to use a for loop. Then, that workaround requires an additional workaround to avoid a full-on bug. How many users would have been willing to wade through that mess?

[perissf]

I agree on the frustration part , but I am not sure to understand why you aren’t using references between tables that are meant to be logically linked. If you aimed to customize the behavior of the app (i.e. the way the way linked records are displayed, or the ability to edit / add parent / child records when you don’t want them to be edited) these are things that can be easily handled case by case, but I think that generally the pros of using references outweigh the cons.

[BenBl]

I want it to be dumb.

What I mean by that: I’m tracking task times for each task and creating schedules based on them. I’m experimenting a lot with:

• How to break down tasks (e.g. should I schedule steps 1 and 2 together, or each separately?)
• How long to schedule for each task

So I want a record of “That week, we assigned tasks this way with these estimates and 15% were late”. It’s easier to do that if everything is static - e.g. if I update the time a task should take for week 2, it doesn’t go back in history and pretend that I had the same assumption in week 1.

[Steve]

BenBl:

In action 2, I want to set a column of the new row in Table C equal to the key of the row in Table A where action 1 was used. Is that possible? How do I refer to that row in table A from Action 2?

Only possible if the row of the intermediate table (Table B) has a Ref back to the row of the origin (Table A), or enough other information to identify the origin row.