Automate Adding Rows to Child Table Based on Parent Table

[Meza1234]

I'm setting up an AppSheet automation to automatically add rows to a child table when specific conditions are met in a parent table. The goal is to insert a record into the child table whenever a part's status in the parent table is changed to "replaced," while preventing duplicates. However, the current AppSheet automation UI makes it difficult to dynamically handle multiple columns for this purpose.

The parent table, Service Saws Checklist, contains service reports identified by a key column like [Report Identifier]. It has over 100 individual Enum columns representing the status of different parts (e.g., [Blade Status], [Motor Status], etc.), with possible values like "good", "fair", "replaced", "damaged", and "not equipped." A virtual column [Parts Used] links to the child table via REF_ROWS("Parts Used", "Report Identifier").

The child table, Parts Used, stores details of replaced parts. It has a key column like [Unique ID of Parts Used], a [Report Identifier] Ref column, a [Part] column (Enum), a [Quantity] column, and optional fields like [From] and [Notes].

What I need is an automation bot that triggers when a row in the Service Saws Checklist is updated. If any part's status changes to "replaced" (and wasn't already marked that way), a new row should be added to Parts Used. Each added row must include a unique ID, the parent [Report Identifier], the part name (matching the Enum), and a quantity of 1. Crucially, the automation must not add duplicate part entries—if a part has already been logged as "replaced" in the child table for that report, it should not be added again.

Due to the wide format of the parent table (each part as a separate column), I need an efficient way to dynamically detect which parts were changed to "replaced" and add only those whose [Report Identifier] isn't already present in the child table. Restructuring the schema isn't an option, so the solution must work within this column-based setup.

I know its a lot to read I just wanted to list out the situation thoroughly. Thank you in advance!

[Meza1234]

For  added context this is the logic I am using for the event trigger of the bot. The logic repeats for all the parts in the google sheet.
"OR(
AND(
ISNOTBLANK([_THISROW_BEFORE].[E-Stop]),
[_THISROW_BEFORE].[E-Stop]<> "Replaced",
[E-Stop] = "Replaced"
),
AND(
ISNOTBLANK([_THISROW_BEFORE].[Upper Door Switch]),
[_THISROW_BEFORE].[Upper Door Switch]<> "Replaced",
[Upper Door Switch] = "Replaced"
),"

[Steve]

There is no "efficient" way given that each part has its own column.

Your trigger logic could be like this:

AND(
  "Replaced" = [E-Stop],
  [E-Stop] <> [_THISROW_BEFORE].[E-Stop],
  NOT(IN("E-Stop", [Parts Used][Part]))
)

[Meza1234]

Thank you for the reply. How would you suggest that I set up the process to handle the adding of the rows to the child table?

[Steve]

I feel there's just no "good" approach here. My initial thought was to create one bot per part. Yes: one. bot. per. part. Each bot would only handle its one part. Adding support for a new part would mean copying an existing bot and replacing the part name in the new bot. The bot trigger condition would be like the AND() expression above. The bot process would consist of a single step, a data change action to add a row to the Parts Used table describing the one part the bot handles.

[Meza1234]

Would having that large number of individual bots have any negative effect on the performance of the app, especially if when multiple parts are replaced at once in a single service report.

[Steve]

I don't know.

Bots exist on the server, not on the user's device, so any impact would not be direct.

The bots could potentially run in parallel (more than one at the same time), which can be an advantage in some cases.

Lemme put some more thought into this. Don't start splitting the bots out yet.

[Steve · Accepted solution]

My thinking now is that you have only one bot that responds to any add or update on Service Saws Checklist. The bot's process would consist of a series of Branch on a condition steps, each of which would target a single part:

AND(
  ("Replaced" = [E-Stop]),
  NOT(IN("E-Stop", [Parts Used][Part]))
)

If the condition succeeds (evaluates to TRUE, or Yes), the part was marked Replaced but does not yet have a Parts Used entry, so run a data action to add a new row to Parts Used describing the replaced part.

You'll need to replicate Was ... replaced? and Add ... to Parts Used for each replaceable part.

The Add ... to Used Parts data action can be implemented as a custom action for each replaceable part, or you could have a single action that accepts inputs. Using this approach, you'd still have one Add ... to Used Parts step per part, but each would use the same data action but would have the part specified as an input. I recommend this approach, but it's a little more advanced.