Skip to main content

Hi all 🙂

I'm trying to link two tables based on one of two conditions, but am not sure if it works with simple automations or if it requires a script (I'm also new to scripting). I have the following two tables:

TABLE 1 contains (amongst other information) vendor IDs that are either the simple IDs or a combination of ID and city code, example:

ID
100009
100014CGN
100014BON

 

TABLE 2 contains each vendor and the recipient, for example:

Vendor IDRecipientKey
100009BER1100009BER
100009BER2100009BER
100014CGN1100014CGN
100014CGN2100014CGN
100014BON100014BON

Now, I would like to link the tables under the following conditions:

1. If the Key in TABLE2 is available as an ID in TABLE1, the records should be linked

2. If the Key in TABLE2 is not available as an ID in TABLE1, but the vendor ID in TABLE2 is available as an ID in TABLE1, the records should be linked via the vendor ID

3. If none is available, there should not be any link

I tried linking it with two different automations, but the automations would just create new records (e.g., 100009BER is not available in TABLE1, so the automation would create that record there).

 

Any help would be much appreciated! Thank you 😊

Hi @snowflake123  - looks like this is possible to accomplish with a script. This script would loop through the records of table2 and add links based on your logic. This script can be run periodically and fill the missing links or it can be run once a new record in one of the tables is created.

You may try using the script below which I got by asking Chat GPT. I've briefly looked through it and it feels like it does what you want.

Before running this script make sure to make a backup to ensure no data is lost.

 

 

// Fetch records from TABLE1
let table1 = base.getTable('TABLE1');
let result1 = await table1.selectRecordsAsync();

// Fetch records from TABLE2
let table2 = base.getTable('TABLE2');
let result2 = await table2.selectRecordsAsync();

// Loop through TABLE2 records
for (let record2 of result2.records) {
let vendorId = record2.getCellValueAsString('Vendor ID');
let key = record2.getCellValueAsString('Key');

// Check if the key is available in TABLE1
let keyRecord = result1.records.find(record1 => record1.getCellValueAsString('ID') === key);

// Check if the vendor ID is available in TABLE1
let vendorRecord = result1.records.find(record1 => record1.getCellValueAsString('ID') === vendorId);

// If keyRecord is available, link the records
if (keyRecord) {
table2.updateRecordAsync(record2, {
'Link to TABLE1': [{ id: keyRecord.id }]
});
}
// If vendorRecord is available, link the records
else if (vendorRecord) {
table2.updateRecordAsync(record2, {
'Link to TABLE1': [{ id: vendorRecord.id }]
});
}
// If none is available, remove the link
else {
table2.updateRecordAsync(record2, {
'Link to TABLE1': []
});
}
}

 

Hope this helps!

P.S. If you have budget feel free to send me a private message.

 

 

Reply


Terms & Conditions