Linking two tables based on one of two conditions

[snowflake123]

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 ID	Recipient	Key
100009	BER1	100009BER
100009	BER2	100009BER
100014	CGN1	100014CGN
100014	CGN2	100014CGN
100014	BON	100014BON

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 😊

[preshetin · Accepted solution]

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.