Forum Discussion

ElenaLarrabee1's avatar
ElenaLarrabee1
Qrew Captain
4 years ago

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

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
------------------------------
  • What is the max crew names that someone might select from the multi-select list?

    ------------------------------
    Mike Tamoush
    ------------------------------
    • ElenaLarrabee1's avatar
      ElenaLarrabee1
      Qrew Captain
      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
      ------------------------------
      • MichaelTamoush's avatar
        MichaelTamoush
        Qrew Captain
        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
        ------------------------------
  • 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 .  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  for the trigger Schedule which are not valid for the chosen multi select lookup field.





    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------
    • ElenaLarrabee1's avatar
      ElenaLarrabee1
      Qrew Captain
      Okay, I'll play around with that and see what I can get working! I'll post back here.

      ------------------------------
      Elena Larrabee
      ------------------------------
      • ElenaLarrabee1's avatar
        ElenaLarrabee1
        Qrew Captain
        Got it working! Checkbox in crew assignments searches through the list of selected text options, automation step 1 copies all possible crew from master table to crew assignments, step 2 deletes any without a check in formula checkbox. 

        Thank you!!

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