Automation Script for parsing a Multi-select field on form submission?

[Michael_Ahrens]

Hello, I’m new to Airtable, (trying to help a friend build an efficient solution) and I’m looking for some advice. We have a form with a rather large multi-select field. Users must enter their name, date and select any number of options from the multiselect field.

The information comes into the table in the way you would expect:

But in order for it to work with my friend’s processes (and existing tables) they need a way to break the record up (ideally on form submission) into multiple records:

I found a way to do it with automations. I created an automation for each Option to check to see if that option has been selected, and then create a new record. Unfortunately, this is time consuming and will not scale well.

Can you recommend how to build an automation script (or other solution) that can automatically parse the multi-select field into multiple records, one for each selection?

Thank you,

[kuovonne · Accepted solution]

Welcome to the Airtable community!

You say that you are open to a scripting solution. What is your experience with writing scripts? While this is would be a straightforward script to write, it might be too much for a someone new to writing scripts, unless you were highly motivate to learn scripting.

If you are not comfortable writing a script (or cannot hire someone to write a script), you could use a system of text fields, formula fields, and automations to create the records.

1. When the form is submitted, have an automation trigger from the form submission and copy the multi-select values to a single line text field.
   2a. Have a formula field that checks the value of the single line text field and extracts the first choice.
   2b. Have a formula field that checks the value of the single line text field and extracts all the remaining choices except the first.
2. Have an automation that watches the formula field in 2a for changes. When there is a change, create a new record with the select choice in 2a, and copy the value from formula field 2b to the single line text field. Optionally, if there is no value in 2b, use a scripting automation to delete the original record.

[Michael_Ahrens]

Thank you for your solution, It’s been a while since I’ve written much code (and never for airtable), so I really appreciate your alternative solution. I was able to implement your solution fairly quickly and it seems to be working really well.

I had a little trouble with step 2. I set up an automation to trigger ‘when record updated’ while watching field 2a, but it would only run one time. Instead, I set up an automation to trigger when ‘record matches conditions’ where the condition is ‘2a is not empty’. Now it runs through each step until there is no value in 2a.

For anyone else looking to do this, here’s how I implemented Kuovonne’s solution:

Initial Form Submission & Formula Cells

Automation #1

• Trigger: When a Form is Submitted
• Action: Update Record > Copy the Options field to Stringify
• Results: Creates a single line text field with the values of the multi select field

Field 2a:

IF( FIND(",", Stringify),
  LEFT(Stringify,
    FIND(",", Stringify)-1
  ),
  {Stringify}
)

Result: Populates Field 2a with the ‘first’ choice in Stringify. -1 removes the comma.
Note: Kuovonne, Thank you for this formula too. I found it in one of your old solutions elsewhere in the community.

Field 2b:

IF(FIND(",", Stringify),
  RIGHT(Stringify,(LEN(Stringify)-FIND(",", Stringify)-1)
  ),
  ""
)

Result: Populates Field 2b with the remaining options. -1 removes the comma from the text. If there are no strings left (indicated by no commas), it sets Field 2b to a null value ("").

Automation #2

• Trigger: When a record matches conditions > Field2a is not empty
• Action: Create Record with all the required fields Name, Date, and Options.
  Make Sure to set Stringify to the contents of Field 2b.
• Result: Field 2a & Field 2b in the new record will be different because of the new values in Stringify. Since Field 2a is not empty, the automation will run again, and again until the Field 2a is empty.

Here are the results: (Checked records were automatically created)

I didn’t end up implementing a scripting automation to delete the original record. My friend wants to hold onto them as ‘receipts’ for the original form submissions, so I will be moving the auto-generated results to a different table.

Thanks again!!

[kuovonne]

Glad you got a working solution! You are welcome for the earlier formula for identifying the first element in the list. Airtable has since released the REG_EX functions which make this even easier than the formula you found, but use what works!

Your system has a few slight differences from what I envisioned, mostly relating to the triggering record and which fields are copied in which records. I was picturing having the original record be the triggering record in all cases, but your method where each newly created record triggers the creation of the next record works too.

[Celebrating]

@Michael_Ahrens @kuovonne I am new to Airtable, and I have a follow up question for your solution. Could this solution be used to assign tasks to clients when a client is added to a program? Each program has a set list of tasks to be completed for each client. 

Below is how I have my table set up. When I select a program for a client, the Program Tasks column has a formula to automatically assign a list of tasks. 

 I tried using your solution above to separate those tasks into separate rows, but the fact that Program tasks is a formula causes the automations to run endlessly because the Stringify column always resets itself to the formulated value from Program tasks instead of being updated by your solution above. Is there a way to modify your solution above to delete the previous row after it runs through the automations that way it doesn't just repeat endlessly?

Thank you!