Jun 20, 2023 12:11 PM
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 1Prep Project 1Cut Project 1Print 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?
Jun 20, 2023 02:45 PM
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
Jun 20, 2023 08:39 PM
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
Nov 22, 2024 02:37 PM
Hi! Did you find a solution? I'm trying to do the same?