Hey Christoph, thanks for all the details!
I’ve put something together for you here that should do what you’re looking for.
You can duplicate the base by clicking the title of the base at the top of the screen, then the three horizontal dots on the right, and then the “Duplicate Base” button. You’ll then be able to see all the formulas, modify the automation as needed, etc
Unfortunately, I don’t know of a way to create multiple records via an automation. (We can do this with a script step in the automation, but I’d rather avoid that unless you’re familiar with scripting)
The idea is to use the automation you’ve created that finds the records that match the conditions and create a record in a table, in my case,
Retailers matching Request.
That table has a formula field with the following formula:
" " &
',' & Requests
Which essentially outputs text in this format for each retailer that matches the conditions:
[Request number] [Retailer name]
e.g. 5 Retailer A,5 Retailer C
We then have another table
Retailers x Requests that’s linked to
Retailers matching Request, and we use an automation to paste the text from the formula field into the link field. Doing this creates a single record for each retailer that matches the request
Let me know what you think!