Creating new records from a list if a record doesn't already exist

[kzurbrick]

Hi All - new here and I've stumped myself! 

I had an automation that successfully took values from a list field in a projects table (tbl_Projects), and created separate records linked back to the project in an operations table (tbl_Operations). It was triggered on a button.  
Example: 

tbl_Projects.ProjectName (Text)             tbl_Projects.OperationsOptions field (Multiple Select)
Project 1                                                      Prep, Cut, Print

tbl_Operations.OperationName (Text)  tbl_Operations.ProjectLinked  (Linked)                    
Prep                                                           Project 1
Cut                                                             Project 1
Print                                                           Project 1

Problem: when I added/updated the project table list field with additional operations it would create all of them over again in the operations table, plus the new one recently added.  

Example: 

tbl_Projects.ProjectName  (Text)                      tbl_Projects.OperationsOptions field (Multiple Select)
Project 1                                                              Prep, Cut, Print, Quality Check

tbl_Operations.OperationName                        tbl_Operations.ProjectLinked                      
Prep                                                                      Project 1
Cut                                                                        Project 1
Print                                                                      Project 1
Prep                                                                      Project 1
Cut                                                                        Project 1
Print                                                                      Project 1
Quality Check                                                     Project 1

Question: How can I maintain the automation but have it check first to see if there's already a project with that operation?  

[Stephen_Orr1]

Hi @kzurbrick,

Unfortunately, this is currently not possible without scripting as Airtable automations do not allow conditional actions inside of a repeating group.

A potential non-scripting workaround is to setup your base with a junction table approach so that the unique list of operations is stored in a table to be selected as linked records rather than in a multi-select field. This would allow a Find Records automation step to search this table of operations for records not associated with a project in the junction table followed by a repeating group step to create any missing project operations.

A junction table setup would look like this:
Projects (unique) <-one-to-many-> Project Operations (junction) <-many-to-one-> Operations (unique)
In Airtable, setting up the relationships would look like linking three tables as shown above and then within the junction table, disabling the option to link to multiple records for both linked record fields.

Hope that helps!
-Stephen

[TheTimeSavingCo]

You could try a super hacky solution if you're alright with the primary field of the "Operations" table being in the format `[Project Name] - [Operation Name]`:

1. In the "Projects" table, create a formula field that will take the values of the "OperationsOptions" field and format it into `[Project Name] - [Operation Name]`
2. Update your automation so that, instead of creating new records, it'll paste the value of the formula field from step 1 into the linked field to the "Operations" table

This should do what you're looking for I think