Automation (script?) to create a new record when a linked record is updated - but with new linked record only

[Lara_de_Vries1]

Hello Airtable Community

I’ve been going around in circles on this problem, and am SO close using an automation, but not quite.

Three tables:

1. PLACES - List of places to hire (inventory)
2. BRIEFS - List of briefs (the requirements of the hirer)
3. SELECTION - List of a places linked to a brief i.e. the selection to present to hirer (client portal)

The interface for internal use:
Page 1: Create a new brief (form)
Page 2: Active briefs (record view)

The workflow:
Ideally, the internal users would be able to add multiple places (linked records) to the Active briefs (record view). And when a place is linked to a brief (places can/will be linked to multiple briefs) a new record is automatically created in the Selection table as a unique record linking the location to the brief.

The client portal:
I am using the SELECTION table to create a Softr app to present the selection/response to the hirer - looking up details of the place from PLACES. The hirer can only see selections relevant to them (their email linked to the BRIEF)

The automation so far:
When a new BRIEF is linked to a PLACE (i.e. “when a record is updated”, create a new record in the SELECTION table.
Brief name and place names are automated to pull through - and work.

Where it goes wrong:
When a place is recommended in another brief (this will happen frequently) a new record is created in the SELECTION table. It pulls through ALL the previous briefs linked to that record as well as the new brief.

Seeking your expertise and advice:
How do I create a record in the SELECTION table that links one place with one brief each time?

Many thanks for your kind help. I hope I have presented the problem clearly enough.

[Dan_airtable]

Hey Lara - as you stated the problem is that you need to have the automation run through one record at a time to work. You can achieve this with a combination of rollup, lookup fields and views filtering out one record at a time +MIN(values)within the rollups. This base does exactly that - Airtable Automation: Cycle through one automation at a time

Simply rename the ‘value’ fields in the first and second table. Essentially, places=table 1, briefs=Items to Process, selection=🔗 Value in the base above.

[Lara_de_Vries1]

Hey Dan - thanks so much for responding! I’ll give it a try tonight and let you know how I get on.
I appreciate the help.