I am looking for a way to create an automation that every time a new record has been created, finds records and then creates separate(!) new record for each match.
I am trying to match requests with retailers and there are usually several retailers that match a certain request. Therefore I would like to create another table that has a separate row for each match. So far I only manage to get a table with multiple records in the retailers columns (even though the field properties do not allow multiple matches).
Once a new request has been created, the requests are supposed to be matched with retailers IF those retailers offer the brand at hand AND are in the same postal code area in the Requests x Retailers table in *separate" tables.
Requests x Retailers table:
Settings of automation that blends both matching retailers into one comma-seperated field instead of creating two separate rows:
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:
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
Unfortunately, I am now facing the issue that entries in Retailers x Requests are not linked anymore to their Request ID and their Retailer. The information is still in the Name column, but I would not need to split that information and link it again. Do you have any idea of how this could be done in an automated way?
@Adam_TheTimeSavingCo I am almost there :slightly_smiling_face: - I am just struggling with one tiny issue. I figured out to help myself with LEFT and RIGHT formulas to split the Request ID and the Retailer (as described here).
That works pretty nice when using your trick with the text:
Now my problem is to get rid of the space behind the 2+th /. I adjusted your formula, but obviously do not understand it well enough to do the full magic: