Update cell value from another table based on unique ID (basically left join)

April_Urban · ‎Jul 12, 2023

Hello. I am trying to create an automation or a script that updates a cell in a given table based on a unique identifier that can be matched to a unique identifier in another table. In my mind, I'm basically wanting to do a left join, except that I want to populate missing values in a column instead of just bringing the whole column in. So, more of a if x is null, x = value from column in another table mapped by a lookup value/unique ID.
More detail about table structure:

Table A: File Names
Column A- Name [short text- is essentially file names, as in ThisDocumentName.docx]. Is populated through a Link to another record column from another table.
Column B- Google Drive Link, missing some values [URL]
Column C- Unique ID [short text- is essentially a cleaned version of Column A]

Table B: Google Drive Links
Column A- Name [short text- is essentially file names, as in ThisDocumentName.docx]
Column B- Google Drive Links, complete [URL]
Column C- Unique ID [short text- is essentially a cleaned version of Column A]

I want Table A-Column B empty values to populate with Table B-Column B values. I should be able to join the tables because Table A-Column C should match Table B-Column C. Both Table A and Table B will update, about monthly but it could scale to more frequently, so an automated ore easily repeatable solution is desired.

I tried an automation that kind of worked in a similar scenario, but the automation won't run. The automation is:
Trigger- at a scheduled time
Action 1- Find records (finds records missing values in Table A.B
Action 2- For every item in list (list is populated in Action 1)
Action 3- Find records in Table B based on condition TableB.C = TableA.C. Both dynamic values.
** Testing this step, the testing says it's successfully but it finds no records. I have tested selected records that, when using the filter function for the linking column value in the other table, the matching record is successfully found**
Action 4- Update record
Table- Table A
Record ID- should come from "current item in list of records"
Field- Table A.B = dynamic value Table B.B from Action 3- comes back as list of TableB.B

This runs successfully, but doesn't do anything.

In my mind, I've done something similar with the script in this post, but the differences are that I want to populated certain cells versus create a new column, and I am not trying to create a table link. So I guess it's not that similar. But it does seem like, if I get desperate, I can use this to create a linking field, then manually bring in and update the column where needed.

Thanks for reading!!

April_Urban · ‎Aug 03, 2023

Update: So I could never get this automation to work, but I eventually figured out a script to do what I wanted it to do. Here's the script that eventually worked, apologies I didn't update the script headers yet. Links to posts that led me to the script are also included, there's a nice walk through/breakdown of the script on Medium, linked below.

//Substitute "Orders" for table name which contains values

//on which you want to run the vlookup

let mainTable = base.getTable("File Name Link table");

let mainTableRecords = await mainTable.selectRecordsAsync({fields:["NameLink"]});

//Substitute "Product" for table which contains range to search in

let lookupTable = base.getTable("File Links from Google");

let lookupRangeRecords = await lookupTable.selectRecordsAsync({fields:["NameLink2", "link as text"]});

//Replace "Item.barcode" with column name which has the values you want to look up

for (let record of mainTableRecords.records) {

let lookupValue = record.getCellValue("NameLink");

//Replace "Barcode" with column name which is the range to search in

//Replace "Name" with columnn name which value should be returned

for (let rangeRecord of lookupRangeRecords.records) {

if (rangeRecord.getCellValue("NameLink2") === lookupValue) {

let returnValue = rangeRecord.getCellValue("link as text");

//Replace "Proper Name" with column name from mainTable which should contain the link

await mainTable.updateRecordAsync(record, {

"Google drive link as text": returnValue

});

}

}

Thanks to this: https://community.airtable.com/t5/development-apis/script-lookup-with-multiple-criteria-including-a-...

That led me to this: https://business-automated.medium.com/vlookup-in-airtable-for-tables-without-existing-links-d8ddf379...

See Solution in Thread

CJSmith · ‎Jul 12, 2023

Hi @April_Urban, I literally did something similar today. Essentially you want to:

Target records without a URL in Table A
Find records in Table B with matching unique IDs
Update the records in Table A with the values from Table B

If this is the case, I'll break down the automation as it has worked for me:

Trigger: At a scheduled time
Action 1: Find records in Table A where {URL Field} is empty
Action 2: Repeat for each in list of records from Action 1
- Repeated Action 1: Find records in Table B where {Table B Unique ID} = {Table A Unique ID}
- Repeated Action 2: Update record in Table A where Record ID = list of record IDs from Repeated Action 1; Fields = {URL Field}

This should work - it's usually a matter of getting the "Find Records" components assigned correctly, and also re-testing each step. Let me know if that helps!

April_Urban · ‎Jul 13, 2023

Thanks, it sounds like our automations are the same, I don't see any differences in what you outlined versus what I outlined. Can you share what fields types your unique ID columns are? Mine are short text type, though the text itself is not that short, but let's say 60 characters max, rought estimate. I looked into the length limit of the short text field and they are well within what I found. I've made iterations of my unique ID (stripped out spaces, upcased everything). If this were another programming language I'd be checking whether strings of different lengths could be matched and stripping leading and trailing blanks.
The automation runs, and automation history reports that it runs successfully, it just doesn't find the link and so it doesn't make any updates.

CJSmith · ‎Jul 13, 2023

Sure, we use number fields/serialization which tend to be easy to test given the max we've reached is in the tens of thousands.

This might be a shot in the dark, but have you tried making the record ID in your Action 4 be the List of 'Airtable Record ID' as found in Action 3 instead of the Current Item? That's how I have mine configured.

April_Urban · ‎Jul 18, 2023

Thanks for this, I get a "received invalid inputs" error when I try to enter list of airtable record ID instead of airtable record ID from current item. Changing the list of airtable record ID back to a single airtable record ID resolves that error, but the automation still ultimately doesn't work. FWIW, the automation won't let me put a single value in the fields column in that step of the automation, which is what should be there IMHO since there's an iterator at the top that's supposed to run through each item in a list.
It seems like the error is in the matching step (action 3 in my first post, repeated action 1 in yours). It runs but doesn't find a match. Thanks for trying to help.

April_Urban · ‎Aug 03, 2023