lookupFirstOrLast script for Airtable scripting block

gistfile1.txt

/*******************************************************************************
Title: lookupFirstOrLast script for Airable scripting block
Author: Kuovonne Vorderbruggen
Website: kuovonne.com
Date Created: March 16, 2020
Copyright (c) 2020 by Kuovonne Vorderbruggen
License: MIT License

## DESCRIPTION

While Airtable rollups can display the minimum or maximum value of linked records,
it currently cannot show the first or last record of linked records.

This script seeks to provide that functionality.

## USAGE IN THE DEMO BASE

The demo base is based on the "Individual and corporate donations" template
provided by Airtable.
The demo base adds two additional fields to the [Contacts] table of the original
template: {Last donation date} and {Last donation amount}.

In the demo base, this script looks up all the donations for each contact,
and fills in the information for the date and amount of the last donation.

*******************************************************************************
*/


/*******************************************************************************
  CUSTOMIZE THESE VALUES TO MATCH YOUR BASE
*******************************************************************************
*/
let scriptOptions = {
  parentTableName: "Contacts",
  linkedFieldName: "Donations", // field in parent table
  outputFieldName: "Last donation date", // field in parent table
  sortFieldName: "Date of donation", // field in child table
  valueFieldName: "Date of donation", // field in child table
  firstOrLast: "last", // "first" or "last" for first or last value per sort
  scriptHeading: "Lookup last donation date script" // title displayed when running the script
}
// run the main script with the above options for most recent donation date
await lookupFirstOrLast(scriptOptions);


/*******************************************************************************
This section runs the main script a second time with different options.
Omit this section if you do not want to lookup a second value
*******************************************************************************
*/

// most recent donation amount
scriptOptions = {
  parentTableName: "Contacts",
  linkedFieldName: "Donations",
  outputFieldName: "Last donation amount",
  sortFieldName: "Date of donation",
  valueFieldName: "Amount",
  firstOrLast: "last",
  scriptHeading: "Lookup last donation amount script"
}
await lookupFirstOrLast(scriptOptions);
  output.markdown("## Script Done");


/*******************************************************************************
  NO CHANGES REQUIRED BELOW THIS LINE
*******************************************************************************
*/


/*******************************************************************************
Main function for this script: gather data, show data to user, write updates
*******************************************************************************
*/
async function lookupFirstOrLast(scriptOptions) {
  validateOptions(scriptOptions);
  let userWantsToContinue = await informUserOfScriptPurpose(scriptOptions);
  if (userWantsToContinue) {
    let {parentRecordsList, childRecordsHash} = await getParentAndChildRecords(scriptOptions);
    await updateRecordsWithFirstOrLastLookupValue(scriptOptions, parentRecordsList, childRecordsHash);
  } else {
    output.markdown("## No updates performed");
  }
  output.markdown("**Done updating records for this set of options**");
}


/*******************************************************************************
Function for validating script options.
If any of the tables or field names do not exist, the scripting api will
throw an error when the script attempts to get them.
*******************************************************************************
*/
function validateOptions(scriptOptions) {
  // unpack script options
  let parentTableName = scriptOptions.parentTableName;
  let linkedFieldName = scriptOptions.linkedFieldName;
  let outputFieldName = scriptOptions.outputFieldName;
  let sortFieldName = scriptOptions.sortFieldName;
  let valueFieldName = scriptOptions.valueFieldName;
  let firstOrLast = scriptOptions.firstOrLast;
  // validate script options for parent table
  let parentTable = base.getTable(parentTableName);
  let linkedField = parentTable.getField(linkedFieldName);
  if (linkedField.type != "multipleRecordLinks") {
    throw ('Invalid script option: linkedFieldName must be a field of linked records');
  }
  let outputField = parentTable.getField(outputFieldName);
  if (linkedField.isComputed) {
    throw ('Invalid script option: outputFieldName must be an editable field');
  }
  // validate script options for child table
  let childTable = base.getTable(linkedField.options.linkedTableId);
  let sortField = childTable.getField(sortFieldName);
  let valueField = childTable.getField(valueFieldName);
  // validate firstOrLast option
  if ((firstOrLast != "first") && (firstOrLast != "last")) {
    throw ('Invalid script option: firstOrLast must be "first" or "last"');
  }
}


/*******************************************************************************
Function informing user of what script does
*******************************************************************************
*/
async function informUserOfScriptPurpose(scriptOptions) {
  // tell the user what the script does
  output.markdown(`
# ${scriptOptions.scriptHeading}

For each record in the *${scriptOptions.parentTableName}* table, this script will
lookup the linked records in the *{${scriptOptions.linkedFieldName}}* field.
The script then determines the *${scriptOptions.firstOrLast}* of those linked records,
when they are sorted according to the *{${scriptOptions.sortFieldName}}* field.
Finally, the script places the value from the *{${scriptOptions.valueFieldName}}*
field of the *${scriptOptions.firstOrLast}* linked record in the
*{${scriptOptions.outputFieldName}}* in the *${scriptOptions.parentTableName}* table.

----
`);
  let userResponse = await input.buttonsAsync('Do you want to continue?', [
    {label: 'Abort', value: false},
    {label: 'Continue', value: true, variant: 'primary'},
  ]);
  return userResponse;
}


/*******************************************************************************
Function for gathering updates for the records
*******************************************************************************
*/
async function getParentAndChildRecords(scriptOptions) {
  // unpack script options
  let parentTableName = scriptOptions.parentTableName;
  let linkedFieldName = scriptOptions.linkedFieldName;
  let sortFieldName = scriptOptions.sortFieldName;
  let valueFieldName = scriptOptions.valueFieldName;

  // get the parent table and records
  let parentTable = base.getTable(parentTableName);
  let parentRecordsList = await getRecordsAsList(parentTable, [linkedFieldName]);
  // get child table and records
  let linkedField = parentTable.getField(linkedFieldName);
  let childTable = base.getTable(linkedField.options.linkedTableId);
  let childRecordsHash = await getRecordsAsHash(childTable, [sortFieldName, valueFieldName]);
  return {parentRecordsList, childRecordsHash};
}


/*******************************************************************************
Function for gathering and performing updates for the records
*******************************************************************************
*/
async function updateRecordsWithFirstOrLastLookupValue(scriptOptions, parentRecordsList, childRecordsHash) {
  // unpack script options
  let parentTableName = scriptOptions.parentTableName;
  let outputFieldName = scriptOptions.outputFieldName;
  // get the parent table
  let parentTable = base.getTable(parentTableName);
  // loop through all the records in the parent table
  for (let parentRecord of parentRecordsList) {
    let updateValue = getFieldValueFromFirstOrLastChild(parentRecord, childRecordsHash, scriptOptions);
    await parentTable.updateRecordAsync(parentRecord.id, {[outputFieldName]: updateValue});
  }
}


/*******************************************************************************
This function gets the records from the table.
This function also gets the cell values and makes them accessible with dot notation.
It also filters out any records for which all returned fields are null.
** Parameters **
table: an Airtable API table object
fieldNames: an array of field names in the table whose values are returned
sortFieldName: optional, a field for sorthing the records
sortDirection: optional, the direction for the sort
** Example usage of resulting array **
let records = await getRecordsAsList(table, fieldNames);
recordId = records[index].id;
recordName = records[index].name;
fieldValue = records[index].fields[fieldName];
*******************************************************************************
*/
async function getRecordsAsList(table, fieldNames, sortFieldName = null, sortDirection = "asc") {
  // set the options for the query
  let queryOptions = { fields: fieldNames };
  if (sortFieldName) {
    queryOptions["sorts"] = [{field: sortFieldName, direction: sortDirection}]
  }
  // run the query
  let queryResult = await table.selectRecordsAsync(queryOptions);
  // get the cell values
  let recordsWithFieldValues = queryResult.records.map((record)=> {
    let newRecord = { id: record.id,
                      name: record.name,
                      fields: {},
                    };
    for (let fieldName of fieldNames) {
      newRecord.fields[fieldName] = record.getCellValue(fieldName);
    }
    return newRecord;
  });
  // filter out records where all fields are null
  let recordsWithFieldValuesNoNulls = recordsWithFieldValues.filter((record)=> {
    let allNulls = fieldNames.every((fieldName) => {
      return (record.fieldName === null);
    });
    return (! allNulls);
  });
  return recordsWithFieldValuesNoNulls;
}


/*******************************************************************************
This function gets the specified field values for all the records in the table
and then stores them in a hash table, with the record id as the key.
This provides very quick lookup of field values based on record id.
** Parameters **
table: a scripting API table object
fieldNames: an array of field names for the given table
** Example usage of resulting hash table **
let recordsHash = await getRecordsAsHash(table, fieldNames);
let fieldValue = recordsHash[recordId][fieldName];
*******************************************************************************
*/
async function getRecordsAsHash(table, fieldNames) {
  // get the records with just the desired fields
  let queryResult = await table.selectRecordsAsync({ fields: fieldNames });
  // create the hash and fill it with the values (including any nulls);
  let recordHash = {};
  queryResult.records.forEach((record)=> {
    let fieldValues = {};
    for (let fieldName of fieldNames) {
      fieldValues[fieldName] = record.getCellValue(fieldName);
    }
    recordHash[record.id] = fieldValues;
  });
  return recordHash;
}


/*******************************************************************************
This function finds the record ids in a linked field of a parent record.
Then it finds the first matching record in the list of childRecords and
returns the value in a specified field of the child record.
** Parameters **
parentRecord: a record in internal format (not Scripting API) where field
  values are accessible via dot notation
linkedFieldName: a linked record field in the parent record
childRecords: an array of records in internal format with record ids
  that are referenced in the parent record. This array should be already sorted.
valueFieldName: the name of the field in the childRecords that contains
  the desired value
*******************************************************************************
*/
function getFieldValueFromFirstOrLastChild (parentRecord, childRecordsHash, scriptOptions) {
  // unpack script options
  let linkedFieldName = scriptOptions.linkedFieldName;
  let sortFieldName = scriptOptions.sortFieldName;
  let valueFieldName = scriptOptions.valueFieldName;
  let firstOrLast = scriptOptions.firstOrLast;

  // for this parent record, get ids of child records in child table
  let linkedRecordIdsAndNamesInParent = parentRecord.fields[linkedFieldName];
//  let linkedRecordIdsAndNamesInParent = parentRecord.getCellValue(fieldName);
  if (linkedRecordIdsAndNamesInParent == null) {
    // no children records, thus no first child, thus no value tu return
    return null;
  };
  // map the linked records to values for the sort field and output value field
  let fieldValues = linkedRecordIdsAndNamesInParent.map((item) => {
    let childRecordID = item.id;
    let sortValue =  childRecordsHash[item.id][sortFieldName];
    let outputValue = childRecordsHash[item.id][valueFieldName];
    return {sortValue, outputValue};
  });
  // filter out null values, and return null if all values are null
  fieldValues = fieldValues.filter((item) => (item.outputValue !== null));
  if (fieldValues.length == 0) { return null; }
  // sort the remainng values
  fieldValues = fieldValues.sort((a,b) => {
    if (a.sortValue < b.sortValue) {
      return -1;
    } else if (a.sortValue > b.sortValue) {
      return 1;
    } else {
      return 0;
    }
  });
  // return value of first or last fieldValue
  let desiredIndex = (firstOrLast == "first") ? 0 : (fieldValues.length - 1);
  return fieldValues[desiredIndex].outputValue;
}


/*******************************************************************************
END OF SCRIPT
*******************************************************************************
*/