How to automatically split a record with multiple select values into individual rows in a new table

[Damian_Fuller]

Hi all, I have three tables:

1. People
2. Projects
3. Sub-contracts

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?

[Damian_Fuller]

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

[Kamille_Parks]

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.

[Rahul_Raghavan]

Have the same problem… Anyone figured out how to do this?

[Will_Robards]

Same issue, anyone any ideas how to solve this?

[Damian_Fuller]

The sub-contracts are first and primarily project related, and secondarily people related.

[Alexey_Gusev]

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.