I have a requirement to identify all records in one of my tables where a text value in a specific field matches one of several hundred possible text values stored in individual records in a separate table.
For every new record in that first table, I want to check if there is a matching value in Table 2, and if we find a match, I want to update a separate field in Table 1 with ‘true’. If there is no match, then that field is set to ‘false’
Would like to set up an Airtable automation for this, but as with Automation script my javascript is pretty near non-existant!
What will happen with existing records? Do you want to run the same check on them, or has that already been done and you just need to handle new records?
Also, regarding the true/false setting, are you applying these to a checkbox field, or do you want the literal word “true” or “false” in a text field? I’m going with the boolean values for now; if you need strings instead, wrap quotes around the true and false references in the script.
Setting this up will be similar to setting up the automation from the other thread. In this case, pass in the value of the text field that you want to compare from [Table 1]. I’ll assume it comes in from input.config() with the name toMatch in my example below, but feel free to change it if you wish.
Replace “Table 2” with your lookup table name, and “Match Field” with the name of the field in that table containing the text to match against.
let toMatch = input.config().toMatch;
let lookupTable = base.getTable("Table 2");
let query = await lookupTable.selectRecordsAsync();
let result = false;
for (let record of query.records) {
if (record.getCellValue("Match Field").toLowerCase() == toMatch.toLowerCase()) {
result = true;
break;
}
}
output.set("matchFound", result);
Once this script runs, you’ll have the desired value in the matchFound output value. Similar to the other automation setup, add an “Update a record” step after the script to insert this value into your desired field in [Table 1].
What will happen with existing records? Do you want to run the same check on them, or has that already been done and you just need to handle new records?
Also, regarding the true/false setting, are you applying these to a checkbox field, or do you want the literal word “true” or “false” in a text field? I’m going with the boolean values for now; if you need strings instead, wrap quotes around the true and false references in the script.
Setting this up will be similar to setting up the automation from the other thread. In this case, pass in the value of the text field that you want to compare from [Table 1]. I’ll assume it comes in from input.config() with the name toMatch in my example below, but feel free to change it if you wish.
Replace “Table 2” with your lookup table name, and “Match Field” with the name of the field in that table containing the text to match against.
let toMatch = input.config().toMatch;
let lookupTable = base.getTable("Table 2");
let query = await lookupTable.selectRecordsAsync();
let result = false;
for (let record of query.records) {
if (record.getCellValue("Match Field").toLowerCase() == toMatch.toLowerCase()) {
result = true;
break;
}
}
output.set("matchFound", result);
Once this script runs, you’ll have the desired value in the matchFound output value. Similar to the other automation setup, add an “Update a record” step after the script to insert this value into your desired field in [Table 1].
My guess is that whatever is in that field you want to match isn’t pure text. Is it a link field? A lookup? Knowing the field type will let me know how to address the error.
My use case has to do with physical street addresses - this lookup field contains the suburb name for the address, and in a separate part of my process that suburb name field gets populated via a Lookup to a separate table.
What I’m trying to achieve with this script is to automate a process where I can identify and tag records where the suburb matches a list of suburbs held in another table.
Thanks for the clarification. Most of the time lookup fields return arrays, not strings, which makes them a little less intuitive to work with in many cases. It’s also important to note that when using a lookup field in an automation, the only option is to pass an array of linked record IDs, meaning the IDs of the records from which the lookup field is pulling its data. If we brought those into the script, we’d have add more code to poll those records to get the text we need.
Frankly that’s more work that I want to do, and I want to keep this simple for you, so I suggest adding a formula field in the same table as the lookup field, and using that to convert the array into plain text. If your suburb name field is named {Suburb Name}, then the formula would be:
{Suburb Name} & ""
By concatenating the lookup field’s data with an empty string, we get a string containing the same info, which we can then feed into the automation. Modify the automation setup to pass in that formula field’s value via the toMatch input variable for the script, and the script as written above should finally work.
Thanks Justin, really appreciate your patience and input on this.
I think your solution to create a string and then feed it into the automation as an input variable solved my main problem - but now another problem with the .toLowerCase() part has surfaced:
TypeError: Cannot read property ‘toLowerCase’ of null
at main on line 7
Only when I remove .toLowerCase() from if (record.getCellValue("Suburb") will the script pass its test successfully. The toLowerCase() part works fine on the toMatch variable.
So I think this indicates that the value its pulling in from my match table using record.getCellValue("Suburb") is no longer a string? The source ‘Suburb’ field in that table is Single line text.
It is important for this script that it tolerate upper/lower case mis-match, so I do want to follow the approach you originally designed into this script to force the strings to lowercase before looking for a match.
I guess my question is now how can I tell this script that record.getCellValue("Match Field") is a string and can be forced .toLowerCase()
That just means that some of your suburb records are empty in the {Suburb} field you’re reading. An empty field is read as null, and null objects don’t have a “toLowerCase” property. If you’ve got blank records in that table, they should be removed to avoid confusion. If that {Suburb} field is blank for some other reason, that’s fine.
That aside, change getCellValue to getCellValueAsString, which will force it to be a string even if the field is empty.
