Automation: Run a script: Automatically link record based on plain text or formula result

[Ricardo]

I copied @Justin_Barrett’s script from the below post and modified it for my purpose:

Automatically link record based on plain text or formula result Scripting app

I would like to create a workaround for the issue that Linked Records are not available in Automations. I am collecting lots of URL inputs from colleagues via the web clipper. For a number of reasons I want to link the records to another table automatically, using a script. The idea would be to trigger the script using an Automation. In short: I want the title of the Calculated site to be copied into the linked record field Sites [how to link record] I feel like this should be fairly simpl…

I’m inputting productName (formula field, text formatted) and recordID from the trigger. My tables are Departures with a linked field Product towards the table Products, where the primary field Product matches the productName input.

let DepartureTable = base.getTable("Departures");
let ProductTable = base.getTable("Products");
let ProductsQuery = await ProductTable.selectRecordsAsync();
let config = input.config();

// Find the matching record
let matched = ProductsQuery.records.filter(product => {return product.product === config.productName});
let productRecord;
// If a matching record exists, use it; otherwise make a new record
if (matched.length)
    productRecord = matched[0].id;
await DepartureTable.updateRecordAsync(config.recordID, {"Product": [{id: productRecord.id}]});

It seems the query comes up empty with an error “TypeError: Cannot read property ‘id’ of undefined
at main on line 12”. When I enter the following line it will create a new record in products wit the correct primary field name, a duplicate of the existing one.

else
    productRecord = await ProductTable.createRecordAsync({"Product": config.productName});

Could some point me towards the error in the query?

[kuovonne]

Ricardo:

if (matched.length)
    productRecord = matched[0].id;
await DepartureTable.updateRecordAsync(config.recordID, {"Product": [{id: productRecord.id}]});

I think the error is here. The way the script is written, the productRecord variable is only sent if a match is found. However, the updateRecordAsync request is submitted even if there was no match. Try throwing curly braces around the two lines so that the update only happens if there was a match.

if (matched.length) {
    productRecord = matched[0].id;
    await DepartureTable.updateRecordAsync(config.recordID, {"Product": [{id: productRecord.id}]});
}

This should get rid of the current error. However, I suspect that the script still will not find the match you want. I suspect that the following line will filter out all records.

Ricardo:

let matched = ProductsQuery.records.filter(product => {return product.product === config.productName});

[Ricardo]

Thanks! You’re right in both case. The original script included bellow’s if/else to create a new record if no match was found. I updated the script with curly brackets.

else
    productRecord = await ProductTable.createRecordAsync({"Product": config.productName});

I don’t under the syntax of the JavaScript filter. I’m trying to search the Products table for a record where the primary text field “Product” matches the input variable’s supplied by a text-formatted formula field. Evidently I didn’t get this right…

Ricardo:

> let matched = ProductsQuery.records.filter(product => {return product.product === config.productName});
> ```

[Justin_Barrett]

Ricardo:

I don’t under the syntax of the JavaScript filter. I’m trying to search the Products table for a record where the primary text field “Product” matches the input variable’s supplied by a text-formatted formula field. Evidently I didn’t get this right…

Ricardo:

> > let matched = ProductsQuery.records.filter(product => {return product.product === config.productName});
> ```

The issue isn’t with the .filter() syntax, though if you want to learn more about how the .filter() method works (among many other built-in array methods), check out this page. You might also review this outline of arrow functions.

The specific problem that you’re hitting is based on an assumption that you’re making about how to retrieve field values. Your use of product.product combined with your description of what you want to achieve tells me that you think that using the pattern variable.fieldname is the way to go, which is incorrect. What you’re actually doing with that syntax is retrieving the value of the “.product” property on the record assigned to the product variable as the array is iterated. Because no such property exists on Airtable records, it’s returning undefined, which will never match the string that’s coming into the script via config.productName.

Field values are mostly retrieved using either the.getCellValue() or .getCellValueAsString() methods, depending on how you wish to process the data. However, there is a built-in .name property on all records which retrieves the value of the primary field regardless of what that field’s actual name may be. That’s the technique that my original script used. While you could use product.getCellValue("Product") to retrieve the same value using the field’s literal name, you can also use the .name shortcut:

let matched = ProductsQuery.records.filter(product => {return product.name === config.productName});

[kuovonne]

It looks like you may be new to the world of code. If you are interested in learning to code, I recommend learning the fundamentals of JavaScript, and then gradually layering on the Airtable API. Otherwise troubleshooting issues like these will constantly plague you when you seek to adapt scripts written by other people.

If you aren’t interested in learning to code, that’s find too. However, if you do not know how to code, keep in mind that you may spend more time and effort trying to adapt a script (that was not designed to be adapted by a non-coder) than in hiring someone to provide you with working code.

[Ricardo]

Thanks for both your replies, an insightful reply from @Justin_Barrett and very true comment by @kuovonne. I’m coming off Google Sheets, where I felt like a fairly proficient user, but have no understanding of JavaScript - at least for now. :winking_face:

Do you have recommendations how to go about hiring a consultant for the base setup and scripting? I usually gravitate towards posting on UpWork for specialized tasks.

[Justin_Barrett]

@Ricardo This forum has a #developers:work-offered category. Post there with a description of what you need and others users who feel they can help will reply. (Normally I’d offer my own services, but my plate is quite full at the moment.)