Automation to create multiple records based on a multi-select text field

Question

Hi everyone! I have a tricky question for you. I have a parent table (jobs) with a child table (schedule) with its own child table (crew assignments), which in turn has its own parent table (crew) which is just a master list of the staff.

The way I have my app designed, users add schedule items in a grid edit within the main Job form. Then within each schedule item in the grid edit, there's a multi-select text field set up to assign crew members to each schedule item. It's fine, but it's just a multi-select text field that pulls crew names from the master crew table, it's not actually associating crew assignment child records with that schedule item. Having users click a report link from each schedule item and add crew assignments in grid edit mode would be adding too many steps.

What I think I need is to be able to do is keep my multi-select text field, but create an automation that separates that list and then creates a child record in the crew assignments table for each name on the list. I haven't done much with list fields in automations so I'm not sure where to start. Or of course, if I'm missing something blindingly obvious about being able to create multiple grandchild records within a grandparent form let me know.

Any help would be awesome!

------------------------------
Elena Larrabee
------------------------------

michaeltamoush · Answer

What is the max crew names that someone might select from the multi-select list?------------------------------Mike Tamoush------------------------------

elenalarrabee1 · Answer

It could be up to 20 (or potentially even beyond), I saw a couple of solutions that involve 10-step automations, but I was thinking maybe I could use pipelines to create something with a higher step limit?

------------------------------
Elena Larrabee
------------------------------

markshnier__you · Answer

Here is an idea.Create a table of ALL the Crew members (you actually have this already, but maybe flag the Active ones)Lookup the Multi-Select field own to the Crew Assignments.Make a formula checkbox field called [Valid for Schedule?] which will be true if the Assigned Crew Member is in fact part of the multi select field choices.Then create an Automation which will trigger whenever a Scheduled is added or edited and the multi select crew field changes.Step 1 of the Automation will be to purge all the children Crew assignments of the Trigger Schedule record .&nbsp; That way you are always starting from a clean slate.Step 2. Copy ALL the possible Active Crew members off the master list of Staff into Crew Assignments for the trigger Schedule.Step 3. Delete all the crew assignments&nbsp; for the trigger Schedule which are not valid for the chosen multi select lookup field.------------------------------Mark Shnier (YQC)Quick Base Solution ProviderYour Quick Base Coachhttp://QuickBaseCoach.commark.shnier@gmail.com------------------------------

michaeltamoush · Answer

If it's going to go beyond 20, multi select fields only allow 20, so might want to consider that before going down this rabbit hole.

------------------------------
Mike Tamoush
------------------------------

elenalarrabee1 · Answer

Okay, I'll play around with that and see what I can get working! I'll post back here.------------------------------Elena Larrabee------------------------------

Forum Discussion

Automation to create multiple records based on a multi-select text field