I’ve been going around in circles on this problem, and am SO close using an automation, but not quite.
PLACES - List of places to hire (inventory)
BRIEFS - List of briefs (the requirements of the hirer)
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)
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.
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.