Creating parent table record utilizing a bulk action with several child table records

AppSheet AppSheet Q&A
,
1

Hi,

I’m banging my head against the wall, and it hurts. haha. I’m trying to do the following:

  1. I have a list of daily logs

  2. I want to bulk select several daily logs, and then create one record in a parent table that will have a reverse reference to these child records as a list

  3. I don’t want to have to create the parent record first

I’ve gotten as far as linking the child records together with a common ID, but when I try to create a row in the parent table as part of the bulk action process, it tries to write more than one record to the parent table with the same ID, even though I put an expression in the behavior field of the action to look to see if a parent record with a given ID was created. The action fails because Appsheet THINKS a record was created in the parent table with a given ID, but it doesn’t actually write it to the table.

Maybe I’m not understanding the order of operations for Appsheet actions. Below are my grouped action that I’m exectuting as part of a bulk action (selecting several child rows)

image.png
image.png1010×456 27.8 KB

Create ID 2 - sets a field in the child rows with a common identifier

Create actual Trip ID - looks for the child rows with the common identifier in the previous action and sets them to another common identifier that I generate with another action and using the input() function, I can give them the same ID that I generate randomly

Select one row to utilize UniqueID to create a row in Trips - In theory this should select one child row to take the “trip ID” field set in the previous action. This “Trip ID” value would then be used to create one row in a parent table called “trips” and would utilize the “Trip ID” value as the unique ID for the row.

I’m finding that Appsheet seems to execute the bulk actions in parallel (based on the row create time that I see in the data table), and it doesn’t allow me to, say write one record to the parent table, and check that a given ID exists in order to use the behavior true/false field on an action to prevent the action from running.

Any ideas how I can go in reverse to create one parent row from a bulk action executed on several child rows? I need this to work in offline situations, so a webhook isn’t an option… I’m confident I could do this with a webhook, but I need it to work in offline situations…

2

Hello @SmartsheetGuru

I was not able to do what you wanted with actions only.

I was able to do it using a Bot though. You may not like what I have done. But I hope it will give you some idea to resolve your issue.

  1. Create a temp table
  • USER - To make sure the user triggering the process affects his/her own trip ID
  • Trip ID - To hold the trip ID temporarily

  1. When you update your common id with a real trip ID, update the temp row also

  2. Create action tied to temp to add a new row to Trips with the trip id updated in 1)

  3. Let a bot run 3) when the trip id col is updated.

This seems to work.

Cheers

P.S. If you need to be off line and see the results immediately, a bot wouldn’t work either…

3

@TeeSee1

Thanks for the reply! I think conceptually what you suggest would work, and I considered a bot, but it’s the offline functionality I’m having trouble with… So, I’m working on an alternate solution that I think will be ok with the end users. This app will be used in airplane cockpits when service generally isn’t available, but sync can happen when the user is back online.

I’m going to do the following:

  1. When creating a daily log, the user indicates it is for a single of multi day trip

  2. If it’s a single day → create a trip as well with the daily log referenced to the trips table through a Trip ID

  3. If it’s a multi, ask for the date range, create a trip ID and Trip table entry on the first day, then I check if subsequent daily logs are in this range and attach it to the appropriate trip using the trip’s ID.

This takes extra actions to ensure the trip start/end dates stay in sync with the daily logs attached, but it will be slicker than the users having to create a trip before creating daily logs. If a trip was created before creating daily logs, it would be simpler, but the end users don’t want this extra step, as the primary entry view is the daily log.

The trip grouping is more for submitting reports to the appropriate personnel when all logs are completed for the trip.