Find records and create multiple, separated records

[Christoph_Baumeister]

Hi there,

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).

Any ideas? Thank you!

[Adam_C]

Hm, I’d love to help but I don’t have enough information to do so I’m afraid.

Could I get screenshots of the Requests and Retailers table with the relevant fields? For clarity, could I also what you call the table that you took this initial screenshot of?

@Christoph_Baumeister

I am looking for a way to create an automation that every time a new record has been created

Which table is this record being created in?

@Christoph_Baumeister

finds records and then

I understand that “finds records” here is finding retailers that match the request. What is the criteria for this?

@Christoph_Baumeister

creates separate(!) new record for each match.

Which table will the new records be created in?

[Christoph_Baumeister]

Sorry for the lack of information @Adam_C.

There are four tables in total:

• Requests
• Brands
• Retailers
• Requests x Retailers

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.

Request table:

Brands table:

Retailers table:

Requests x Retailers table:

Settings of automation that blends both matching retailers into one comma-seperated field instead of creating two separate rows:

[Adam_C]

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 &
" " & 
SUBSTITUTE(
  Retailers,
  ',',
  ',' & 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!

[Christoph_Baumeister]

Hi @Adam_C, thank you so much for the effort you put into this! It works really nicely!

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?

[Christoph_Baumeister]

@Adam_C I am almost there - 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:

Requests &
"/" & 
SUBSTITUTE(
  Retailers,
  ',',
  ','& Requests &"/"
)

Do you know what is still wrong?

[Adam_C]

@Christoph_Baumeister

Requests &
"/" & 
SUBSTITUTE(
  Retailers,
  ',',
  ','& Requests &"/"
)

Ah, try replacing this line:
','& Requests &"/"
with this:
', '& Requests &"/"

Ha, I see what you’re doing; you’re going to paste the extracted request number and retailer name into the link field with an automation, nice.

Man, I feel like we’re missing something here. This seems like it should be so easy to do, but here we are with this convoluted 5 step process or whatever

This is going to burn through your automations really quickly though right?

[Christoph_Baumeister]

Thank you!

Yeah, I totally agree that this is a pretty complex solution for a trivial problem. However, I have done a lot of research on this forum and did not find any proper solution…

[Adam_C]

Yeah, same; I can’t think of any other way to deal with this either. Let me know if this becomes too automation task intensive and I’ll write up an automation script for you or something

[Adam_C]

Hey, I put together two more automations that use scripts instead found here, just in case the task usage ever becomes an issue for you.

Search using automation does the same thing we’ve accomplished previously in two tasks, whilst Search using script does it in one.

I separated them into two in case you didn’t want to bother figuring out how to customize the finding code; then you could just customize the search by using Search using automation instead