For keeping self-links in sync in AirTable

syncSelfLinks.js

/*
 * Title: Sync Self-Links
 * License: Public Domain
 * Author: Nick Crews
 * 
 * Description: 
 * You would want to self-link in certain recursive situations.
 * For example, modeling a table of Employees:
 * an employee should have both "manager" and "manages" fields,
 * each of which link back to the same Employees table.
 * 
 * When you link table A to table B, everything works fine:
 * AirTable automatically creates a field in table B that links back to table A,
 * and it keeps these two links in sync whenever you change either one.
 * 
 * However, when you link a table to itself, AirTable doesn't help you out:
 * - it doesn't create the twin field for you.
 * - it doesn't keep the link up to date.
 * 
 * This script is intended to be used in automations to fix this situation.
 * 
 * Assume we have the employee example as above:
 * - Table is named "Employees"
 * - has a field "Manager" which links to the one employee that manages this person.
 * - has a field "Manages" which links to the 0-N employees that this person manages.
 * 
 * Unfortunately, due to some limitations of the AirTable API
 * (you can only search for a record based on its recordID,
 * you can't search for "all records that hold recordId XYZ in field ABC"),
 * we have to do a few shenanigans to deal with the case of *removing* a link.
 * Since we only receive the updated record AFTER the update,
 * we have no way of knowing if a link was ADDED to the record, or removed.
 * So, we need to add two internal fields to the Employees table to remember
 * the state before the update.
 * Let's call them "_managerBefore" and "_managesAfter". I'm using those funky names
 * so that non-technical users of your airtable get some heads up that something funky
 * is going on, and they shouldn't mess with those columns directly.
 * 
 * OK, after those two bookkeeping columns are added, we are ready to use this script:
 * - Add an automation
 * - Add a trigger:
 *   - set the type as "When record updated" 
 *   - watch table "Employees"
 *   - watch the fields "Manager" and "Manages"
 * - Add a "Run Script" step
 *   - Copy past this script
 *   - Add 6 "Input Variables":
 *     - "Table Name": for us, the string literal "Employees"
 *     - "Changed Record ID": hit the `+` button, and select the airtable record ID from the
 *        trigger step. This will be dynamically filled depending on which record was updated.
 *     - "Field A": for us, the string literal "Manager"
 *     - "Field A Previous": for us, the string literal "_managerBefore"
 *     - "Field B": for us, the string literal "Manages"
 *     - "Field B Previous": for us, the string literal "_managesBefore"
 * 
 * Note: this method can be a little delicate:
 * - if one of the bookkeeping fields is edited by some other process,
 *   the state is corrupted and who knows what will happen on the next run.
 * - If some random error
 */

async function sync(tableName, changedRecordId, fieldChanged, fieldChangedPrevious, fieldToSync) {
  checkVal("tableName", tableName);
  checkVal("changedRecordId", changedRecordId);
  checkVal("fieldChanged", fieldChanged);
  checkVal("fieldToSync", fieldToSync);
  const table = base.getTable(tableName);
  const changedRecord = await table.selectRecordAsync(changedRecordId);
  if (!changedRecord) {
    throw Error(`Record ${changedRecordId} doesn't exist in ${tableName}`);
  }
  
  // @ts-ignore
  const linkedRecords = changedRecord.getCellValue(fieldChanged) || [];
   // @ts-ignore
  const linkedRecordsPrevious = changedRecord.getCellValue(fieldChangedPrevious) || [];
  console.debug(`record ${JSON.stringify(changedRecord)} previously had field "${fieldChanged}"=${JSON.stringify(linkedRecordsPrevious)}`);
  console.debug(`record ${JSON.stringify(changedRecord)} now has field "${fieldChanged}"=${JSON.stringify(linkedRecords)}`);
  
  const linked_rids = linkedRecords.map(record => record["id"])
  const linked_rids_previous = linkedRecordsPrevious.map(record => record["id"])
  
  const links_to_append = linkedRecords.filter(record => !linked_rids_previous.includes(record["id"]));
  const links_to_remove = linkedRecordsPrevious.filter(record => !linked_rids.includes(record["id"]));

  forEach(table, links_to_append.map(r => r["id"]), record => appendLink(table, record, fieldToSync, changedRecord))
  forEach(table, links_to_remove.map(r => r["id"]), record => removeLink(table, record, fieldToSync, changedRecord))
  // now update the bookkeeping column to the new value
  await table.updateRecordAsync(changedRecordId, {[fieldChangedPrevious]: linkedRecords});
}

function checkVal(name, val) {
  console.debug(`${name}=${val}`);
  if (!val) {
    throw Error(`parameter ${name} is null`)
  }
  if (!val.length) {
    throw Error(`parameter ${name} is empty`)
  }
}

async function appendLink(table, record, field, linkRecord) {
  console.log(`adding ${JSON.stringify(linkRecord)} to the "${field}" field of ${JSON.stringify(record)}`);
  const old_val = record.getCellValue(field) || []
  const new_val = [{ id: linkRecord.id }, ...old_val]
  await table.updateRecordAsync(record.id, {[field]: new_val})
}

async function removeLink(table, record, field, linkRecord) {
  console.log(`removing ${JSON.stringify(linkRecord)} from the "${field}" field of ${JSON.stringify(record)}`);
  const old_val = record.getCellValue(field) || []
  const new_val = old_val.filter(record => record["id"] != linkRecord.id)
  await table.updateRecordAsync(record.id, {[field]: new_val})
}

async function forEach(table, recordIds, func, fields = null) {
  // you can only request 100 records at a time
  const chunkSize = 100;
  for (let i = 0; i < recordIds.length; i += chunkSize) {
      const recordIdChunk = recordIds.slice(i, i + chunkSize);
      let options = {recordIds: recordIdChunk}
      if (fields) {
        options["fields"] = fields;
      }
      const batch = await table.selectRecordsAsync(options)
      for (let record of batch.records) {
        func(record);
      }
  }
}

async function main(){
  const cfg = input.config();
  sync(
    cfg["Table Name"],
    cfg["Changed Record ID"],
    cfg["Field A"],
    cfg["Field A Previous"],
    cfg["Field B"],
  );
  sync(
    cfg["Table Name"],
    cfg["Changed Record ID"],
    cfg["Field B"],
    cfg["Field B Previous"],
    cfg["Field A"],
  );
}

await main();