Script to check if a record exists in another table?

[Devon_Moore]

Hi there,

Hoping someone out there can help me with a weird automation issue. First, some context:

I have 2 tables in my base.

Table #1 is the order table - This is used for tracking individual orders (every line order is quantity 1), and the record name is the serial number of the item.

Table #2 is the shipping table - These records are named as followed (rolled up from the records in Table #1😞 “{Customer Name} + {Site} + {Ship Date}”

So essentially, whether there is 1 order or 20 orders from Table #1 going to a particular customer site on a particular date, I want them all rolled up into a single record in Table #2.

This works perfectly fine manually: I have a column “Shipping Table Name” in Table #1 that follows the same naming convention for the record name in Table #2 ("{Customer Name} + {Site} + {Ship Date}") and I can just copy and paste that into the “Link To Table #2” column. No problem.

But… when I tried to automate it to fill the Link column for me, it only works if the record name already exists in Table #2… (does your head hurt yet? Mine does!)

So! If I haven’t lost you, that’s all the context. Now to my actual question.

I’ve never used scripting but I was thinking maybe there is a script out there that can do something like this:

• Trigger: If a record in Table #1 has “Shipping Table Name” (not empty)…
• Check to see if “Shipping Table Name” exists as a record name in Table #2
• If Yes, update record in Table #1 > 1 field > “Link To Table #2” field updated with “Shipping Table Name” to create the link
• If No, create new record in Table #2 > 1 field > “Link To Table #1” field, which would contain the record name (serial number) from Table #1

Thoughts? Does this exist? Or are there any other ideas out there that will give me the same result?

Thanks friends!

[Kamille_Parks]

Automation “update record” steps should be able to create linked records if the given name doesn’t exist yet. I tested this in one of my automations and it’s working for me. Can you post a screenshot of that step’s configuration?

[Devon_Moore · Accepted solution]

I realized my error just earlier today, thanks for reminding me to come back and update this :slightly_smiling_face: Silly mistake - the name in the 2nd table was a formula. Duh. I changed it to a single line text and now it works fine.

[Ryan_King]

@Devon_Moore I am trying to accomplish something very similar, do you mind providing an example of the automation you have running? Are you combining a script along with the update record action?

[Devon_Moore]

Hi Ryan,

No script was necessary. I can’t share screenshots due to sensitive info in the base, but I will explain it in a way that hopefully makes sense.

Initially, the primary field in Table #2 was a formula of lookup fields from Table #1: ({Customer Name} & {Site} & {Ship Date})

However, you cannot create new records via automation if the primary field of the destination table is a formula (since you can’t type stuff in a formula field, an automation can’t either). So that’s why it was only letting me link to records in Table #2 that already existed.

Now, I have {New Name Formula} in Table #1 instead, which concatenates the name for Table #2 ({Customer Name} & {Site} & {Ship Date}) before the automation even runs.

When a new record is added in Table #1, the automation takes {New Name Formula} from Table #1 and stamps it into the Link field, creating the primary field of Table #2 (which is now a single-line text field as opposed to being a formula itself)

If that primary field name already exists, it adds my order to that existing record. If it does not yet exist, it creates a new record.

Hope that helps!

[Kamille_Parks]

Devon_Moore:

However, you cannot create new records via automation if the primary field of the destination table is a formula

Clarification: While you are correct that Automations cannot fill in a formula field, an Automation “Create Record” step can create a record in any table regardless of whether the primary field is calculated (a formula, autonumber, etc) or not. As long is you fill in at least one field, the record will be created.

^ I know that’s not directly relevant to what you’re doing here, but I’m clarifying so people are aware.

[Devon_Moore]

Very true, thanks for clarifying! Like in this case, if I knew for sure that the shipping record did not yet exist in Table #2, I would just have the automation fill the Link Field only. But since I need it to append itself to a record that already exists (if it exists), I have to use the primary field.