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!
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!
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.
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.
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.
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.
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.
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
});
}
}