Populate child row's parent ID when child row is created via CSV import

[dbaum]

Thanks, @peterdykstra and other staff, for considering my question about CSV during today's office hours. As I mentioned, I've reviewed the various help articles you mentioned, as well as searched the community forum. I don't find an answer to my challenge--which I assume is not an uncommon use case--although I also acknowledge I may be missing or misinterpreting the reference information available.

Anyway, here's the concrete question again since you said you'd follow up with more direct guidance.

I need an "import CSV" action from a parent table row to populate child table rows (child rows should have parent ID column automatically populated). My challenge is figuring out how to populate the child row's parent ID when the child row is created via CSV import.

By way of illustration:

• In my app, a single row in the Lists table is the parent of many rows in the Contacts table.
• A user will create a new entry in the Lists table, at which point the new Lists row's List ID column is assigned a UNIQUEID() by AppSheet.
• The user will have a csv file that they export from another data source they use (and to which my app cannot connect directly). The csv file will have data for the Contacts table, but will not have the parent Lists row's List ID.
• I'm having trouble figuring out how to populate the List ID column as the csv is imported to the Contacts table even though the user is triggering the import from the context of the parent row.

[SkrOYC]

I assume the view that has the CSV Import is an inline view?

[dbaum]

Yes, that's how I've designed it so far. I've tried:

• An "Import CSV" action directly available from the child table's inline view
• An "Execute an action on a set of rows" action (that leads to the child table's "Import CSV" action) available from the parent table's detail view

If there's a different approach that works, I'd be happy to use that instead.

[dbaum]

I figured out an approach via sequential actions, and have a follow-up question.

1. Action in parent table: Set the values of some columns in this row
   1. Populates a column in the current row of the parent table with the current user's User ID
2. Action in parent table: Execute an action on a set of rows
   1. Triggers next action in child table
3. Action in child table: Import a CSV file
   1. Imports CSV file; upon creation of new rows in child table, the parent ID column's initial value populates with the parent row ID, which it finds by selecting the parent table row that includes the current user's User ID

   2. INDEX(SELECT(Lists[List ID], [User ID] = INDEX(Current User[User ID], 1)), 1)

4. Action in parent table: Set the values of some columns in this row
   1. Clears the User ID value

Here's my question: When I invoke each of those actions manually, it works as expected. However, when I create a composite action to invoke them as a series, the CSV import fails, saying that the required parent ID reference is empty. It seems that updating the spreadsheet data source with the user's ID is still pending by the time the CSV import has already begun. Is there a good way to deal with that lag?

[dbaum]

It's convoluted, but I now figured out an approach to deal with the sync lag.

1. I used @Kirk_Masden 's technique based on a spreadsheet formula so the local device knows whether the data source has been updated. See
   Display a "Waiting for sync . . ." message
2. I used @MultiTech 's technique based on looping until a condition is met by including within a "Group" action an "Execute an action on a set of rows" that invokes the same "Group" action. See Looping with Actions.

Here's an outline of the action sequence:

1. Group 1
   1. Clear current user from all rows in parent table (just in case any residual value for current user was lingering from a previously interrupted series of actions)
   2. Set current user in current parent row
   3. Jump to action #2 in Group 2
2. Group 2
   1. Mark current parent row as waiting
   2. "Execute an action on a set of rows" that triggers Group 2 only if current parent row is marked as waiting AND current parent row is not ready per a spreadsheet formula along the lines of NOT(ISBLANK([User ID]))
   3. Mark current parent row as not waiting
   4. "Execute an action on a set of rows" that triggers the CSV import action in the child table
   5. Clear current user from all rows in parent table

[Kirk_Masden]

Thanks @dbaum !  I'm honored and glad you found what I posted to be useful!

[dbaum]

It turns out I rejoiced too soon. It works intermittently. Any of the following can occur:

• It works as expected.
• The csv import fails due to missing parent id value in the import rows.
• It seemingly quits midstream--the parent row is updated in the data source worksheet, but the user isn't even prompted to select a csv file to upload.

I've fiddled with the sequence of steps; which ones occur in group 1 vs. group 2; the conditions to run each action "Only if this condition is true". Nothing works reliably.

So, per the similar techniques that @TeeSee1 and I came to independently (i.e., populate either the parent ID or the CSV import in a temporary location), it's at least possible to use 2 separate series of actions to populate the parent ID in child rows uploaded via CSV. The challenge remains how to connect those 2 series of actions so the user doesn't have to manually invoke each one.

Now, I'm back to hoping @peterdykstra can follow up on our brief exchange in this week's office hours or others in the community can help. I hope there's a more elegant solution than 2 separate series of actions. If not, I hope there's a solution for having the user only invoke the first series and then the second series proceeds once 2-way sync of data updated via the first series has completed.

[MultiTech]

As you have all found out, this is not something that is straight-forward.  The only way I've found to make this work, is to build a system inside the child table that reconstructs the parent ID; but this means you have to use constructed IDs, as well as providing space to enter any relevant parent information inside the child so it's present when importing the CSV.

I put together a video showing how to do this, and intended to make it public... but completely forgot to go back and mark it so.  THANKS FOR REMINDING ME!!! (^_^)

----------------------------------

I've got a protocol guide for for setting something like this up, mainly because of how many moving parts there are in getting things correct.

• You can read more about it on a post on my Patreon page.