Dec 17, 2020 12:27 PM
Hi folks,
I have two tables and am trying to automatically link them with a script.
In this example people apply via an airtable form with a text based “parter code” field. There is a partners table where each partner has a multiple select field that contains all of their partner codes.
Table 1: Applicants
Field: Partner Code (text)
Field: Referring Partner (linked field)
Table 2: Partners
Field: Partner Codes (multiple select)
Field: Applicants using Partner Code (linked field)
Here is what I have so far, based on modifying the scripts of others:
//Define the Partners table and query
let partnersTbl = base.getTable("Partners");
let partnersQuery = await partnersTbl.selectRecordsAsync();
//Define Applicants table and query
let applicantsTbl = base.getTable("Applicants");
let applicantsQuery = await applicantsTbl.selectRecordsAsync();
//Loop through the records and find the Partner Code
for (let record of partnersQuery.records) {
let partnercode = record.getCellValue("Partner Code");
//Loop through linked table and match Partner Code values
for (let applicantRecord of applicantQuery.records) {
if (applicantRecord.getCellValue("Partner Code") === partnercode) {
//Update field
partnersTbl.updateRecordAsync(record, {
'Applicants using Partner Code': [{id: applicantRecord.id}]
});
}
}
}
Also, does this script try and match the entire table every time the script is fired?
Right now this script is timing out. Any help would be greatly appreciated.
Thanks,
Graham
Dec 17, 2020 01:38 PM
@Justin_Barrett I think you’ve done something like this in the past. I based this off of your script; thank you!
Dec 17, 2020 04:02 PM
That does look familiar, but I won’t have time to dig into this until this weekend. If anyone else wants to jump in, feel free.
Dec 28, 2020 10:51 AM
Sorry for the delay. Between multiple migraines and holiday activities, I’ve been a bit busy.
Like I said before, this looks somewhat familiar, but maybe because it’s a fairly generic (not in a bad way) way of finding matches between tables and making links based on the results. There are a couple of issues with how this script is designed, but before getting too far into that, I want to address this question:
Yes. It’s designed to look at all partners and match them against all relevant applicants. Based on your question, it sounds like you’d prefer to only run the script on new applicant records submitted from the form, not all applicant records. Is that correct? If so, this could be set up via an automation.
Apr 27, 2021 01:50 PM
Did this ever get solved? I have a similar use case.
Apr 28, 2021 01:07 PM
@Josh_Cooper Similar use cases often have enough different details that it’s worth starting a new thread. You can link to this thread to indicate that it’s related to your problem, but we’ll need a lot more details about your specific use case to know how to help you.
Apr 28, 2021 02:07 PM
Sorry about that.
I am wanting to link a synced table, Orders, record with another table, Inventory. Both tables have a Part Number field. Basically, I when a new record shows up on the synced table, Orders, I want the automation to link the record with Inventory record with the corresponding Part Number.
Because the table that will trigger the automation, Orders, is the synced table it is possible that there will be multiple new entries at once.
Let me know what other information is needed and I will be happy to provide it.
Thanks!
May 15, 2021 03:20 PM
@Josh_Cooper Sorry for the delayed response. Unfortunately I’ve been—and continue to be for the foreseeable future—pretty swamped, and I can’t look into this any further. Maybe @kuovonne or @Kamille_Parks or @JonathanBowen or @Jeremy_Oglesby can help?
May 16, 2021 03:25 AM
Hi @Josh_Cooper - you don’t need a script to do this - can be done with an Airtable automation on its own. If this is my Orders table:
I’ve got a part number (coming from the table sync) and I’ve added a linked field “Parts” to the Parts table. Then in automations I’ve set up on “new record created” as the trigger and “update” record as the action.
So, in the update action, the Record ID is the ID of the new record. Then I’m setting the “Parts” field (link) to be the value of the Part Number field.
If a part number comes in that is not already in the Parts table, then it creates it in the Parts table.