Apr 22, 2021 04:19 AM
Hi all, I have three tables:
The Projects table has a multiple select field that lists all the people (from linked table) involved in each project record. People are often involved in multiple projects and all projects have more than one person involved. The sub-contracts table lists the contracts that each person (linked from People table) has and the project it relates to (linked to project table). Currently, each sub-contract record is created manually.
I would like to setup an automation that creates a new sub-contract record for each individual involved in each project. The automation trigger seems obvious (new record created in project table) and the action seems obvious (create new record(s) in the sub-contract table). The main problem I’m encountering is how to split the linked multiple select field of members? At the moment, the automation dumps all the individuals into one new sub-contract record.
I’m open to design / restructure tips as well as automation tips. This issue is similar to this previously unanswered question from @Tori_Klein Automation/Formula/Script to Create New Records from a Record with Multiple Linked Records?
Apr 22, 2021 05:27 AM
I feel like this idea from @Kamille_Parks might work but I can’t quite wrap my head around how to apply it to my use case. Updating Linked Records from another Table
Apr 22, 2021 08:00 AM
I don’t see that thread as all that applicable here.
Is there a reason why you don’t link People to Subcontracts first, and then use a Lookup field in the Projects table to find all people linked to a Subcontract? This would avoid some redundancy.
May 06, 2021 12:27 AM
Have the same problem… Anyone figured out how to do this?
Dec 02, 2021 06:42 AM
Same issue, anyone any ideas how to solve this?
Jul 22, 2022 04:40 AM
The sub-contracts are first and primarily project related, and secondarily people related.
Jul 22, 2022 04:56 PM
the question is - why do you using multi-select field in Projects instead of using linked (to People)?
If you have changes in People table - you have to sync all those changes for multi-select field options. It’s not hard, but what for?
With linked, task is easy. When project table updated, and you set links to people involved, second automation will trigger for each change in People table and you can set up new sub-contract record for each person.