Passing parent row value to child action ([_THISROW-1] not working)

AppSheet AppSheet Q&A
, ,
1

Hello,

I’m trying to generate checklist records automatically when a new checklist is created.

Tables

  • CHECKLISTS

  • CHECKLISTS_ITEMS

  • CHECKLIST_RESPUESTAS

Goal

When a new row is added to CHECKLISTS, I want to automatically create all checklist items in CHECKLIST_RESPUESTAS based on the master table CHECKLISTS_ITEMS.

Automation

A Bot triggers when a row is added to CHECKLISTS.

Event:

Adds only
Table: CHECKLISTS

Actions involved

:one: CHECKLIST__CrearRespuestas
Table: CHECKLISTS
Type: Execute action on a set of rows

Referenced rows:

SELECT(
CHECKLISTS_ITEMS[ID_ITEM],
[ACTIVO] = TRUE
)

Referenced action:

ITEM__CrearRespuesta

:two: ITEM__CrearRespuesta
Table: CHECKLISTS_ITEMS
Type: Add row to another table

Destination table:

CHECKLIST_RESPUESTAS

Values being assigned:

ID_ITEM = [ID_ITEM]
ID_CHECKLIST = [_THISROW-1].[ID_CHECKLIST]

Process flow

CHECKLISTS
   → CHECKLIST__CrearRespuestas
      → iterate CHECKLISTS_ITEMS
         → ITEM__CrearRespuesta
            → insert row in CHECKLIST_RESPUESTAS

Problem

When using:

[_THISROW-1].[ID_CHECKLIST]

AppSheet shows the error:

Unable to find column '_THISROW-1'

I also tried:

CONTEXT("HostRowKey")
CONTEXT("RowKey")

but they return empty values when the action runs.

Expected behavior

Each generated row in CHECKLIST_RESPUESTAS should contain the ID_CHECKLIST from the parent row in CHECKLISTS.

Example result:

CHECKLIST_RESPUESTAS
ID_CHECKLIST | ID_ITEM
CHK-001      | ITEM1
CHK-001      | ITEM2
CHK-001      | ITEM3

Question

What is the correct way to pass the parent row value (ID_CHECKLIST) into the action that creates rows in another table when using Execute action on a set of rows?

Thanks in advance.

1 Like
2

You cannot use the [_THISROW-n] property in this way when there is a set of actions. When the “add row to another table” is called, it is passed only the single row as context. The action has no other knowledge of any other context outside that scope.

To paraphrase the issue you have….

after inserting a Checklist parent row, you want to assign that parent row ID to the child list of Checklist items which are retrieved from a “template” set of items to be assigned. But because you are iterating over that “template” list of items to control which child item rows to add, the parent row has fallen out of context and the ADD action does not have access to that parent row to grab it’s ID.

I have encountered this scenario MANY times, have tried many different approaches and have not found an elegant simple solution. To work around this I use a “Variables” table. I then introduce a Grouped action that:

  1. Adds the Parent row.
  2. Grabs the ID of the Parent row and inserts it into the “Variables” table. This assumes it is the most recent row added.
  3. Perform the “Execute action on set of rows” and the the “Add row to another table”
    a) In the “Add row to another table”, I pull the Parent row ID from the “Variables” table. This is repeated on each “template” row for which a child row is added.
  4. Delete the “Variables” row - cleanup.

NOTE: To prevent the “Variables” row entries from stepping on other users I assign to the row the User ID an well as give the variable I’m tracking a Name.

NOTE #2: I typically implement the Grouped action above on the Form Save Behavior. I am not certain if performing these steps in a Bot might behave differently due to datasource context problems. Once implemented, it is easy to use the Grouped action either way.

************************************************

The proper system fix for this would be to extend the usage of the INPUT() function for this exact use case. It is a fairly common use case.

I hope this helps!!!

2 Likes
3

If you have Core, one simple option is to use AppSheet API/webhook. When the Bot is triggered with Adds_Only, you read the new parent ID from the row itself and then find out child records that belongs to the latest parent and copy them.. or if you have them in a separate table.

3 Likes