cakeOrders - script for Airtable scripting block

gistfile1.txt

/*******************************************************************************
Title: cakeOrders for Airtable scripting block
Author: Kuovonne Vorderbruggen
Website: kuovonne.com
Date Created: March 14, 2020, revised March 27, 2020
Copyright (c) 2020 by Kuovonne Vorderbruggen
License: MIT License

## Description

This script was created for a scripting block challenge on the Airtable Community
that was announced on Friday, March 13, 2020, and due on Monday, March 16, 2020.
https://community.airtable.com/t/weekend-scripting-block-challenge-and-1-000-prize/28074

This script was inspired by a community request by Sheryl_Baptista on March 6, 2020.
https://community.airtable.com/t/how-to-trigger-creation-of-x-of-new-records-based-on-input-x-from-a-numerical-field/27873

> Hi, I’m trying to figure out if I can create x number of new records
> based on input from a numerical field of another record.
> So for example, if a customer fills in a form for 7 cakes,
> this will trigger the creation of 7 new cake records in another table.

This script creates multiple new linked records in a linked table, based on a
number in a field of the original table.

## Base Setup

Tables:
  Orders: a table with one record for each order
  Cakes: a table with one row for each cake

Important Fields in [Orders] table:
  cakeNumberField: integer, states the number of cakes for the order
  linkedCakeField: linked records, links to cakes in the [Cakes] table

********************************************************************************
*/
/*******************************************************************************
  CUSTOMIZE THESE VALUES TO MATCH YOUR BASE
*******************************************************************************
*/
// Edit the table and view names below:
const ordersTableName = "Orders"; // The table containing the initial orders
const ordersViewName = "Grid View"; // A view for selected orders
const cakesTableName = "Cakes"; // The target table for newly created records

// Edit the field names below. The are fields in the [ordersTableName] table.
const cakeNumberField = "Number of Cakes"; // Field for number of records to create (integer)
const linkedCakeField = "Cakes"; // Field where the newly created records will be linked


/*******************************************************************************
  NO CHANGES REQUIRED BELOW THIS LINE
*******************************************************************************
*/
await createCakeRecords(ordersTableName, cakesTableName, cakeNumberField, linkedCakeField);

async function createCakeRecords(ordersTableName, cakesTableName, cakeNumberField, linkedCakeField) {
  // get the tables
  let ordersTable = base.getTable(ordersTableName);
  let cakesTable = base.getTable(cakesTableName);
  let queryResult;
  if (ordersViewName) {
    let ordersView = ordersTable.getView(ordersViewName);
    queryResult = await ordersView.selectRecordsAsync();
  } else {
    queryResult = await ordersTable.selectRecordsAsync();
  }
  // get the records in the ordersTable
  let orderRecords = queryResult.records;

  let numCakesCreated = 0;
  let numOrdersUpdated = 0;

  // check each order in the ordersTable
  for (let orderRecord of orderRecords) {
    // get data about this orderRecord
    let orderName = orderRecord.name;
    let numberOfCakesOrdered = orderRecord.getCellValue(cakeNumberField);
    // get number of linked cake records
    let cakeRecordIdsAndNames = orderRecord.getCellValue(linkedCakeField);
    let numberOfCakeRecords = 0;
    let existingCakeRecordIds = [];
    if (cakeRecordIdsAndNames !== null) {
      // there are existing linked cakes
      numberOfCakeRecords = cakeRecordIdsAndNames.length;
      existingCakeRecordIds = cakeRecordIdsAndNames.map((cakeRecordIdAndName) => {
        return {id: cakeRecordIdAndName.id};
      });
    }
    // calculate number of cakes needed
    let numberOfCakesNeeded = numberOfCakesOrdered - numberOfCakeRecords;

    if (numberOfCakesNeeded == 0) {
      continue; // no cakes needed, nothing to do
    }

    // notify that the number of linked cakes doesn't match the order number
    output.text(`Order [${orderName}] has ${numberOfCakeRecords} cake(s), but should have ${numberOfCakesOrdered} cake(s).`);

    if (numberOfCakesNeeded > 0) {
      // create more cakes
      output.text(`Creating ${numberOfCakesNeeded} cake(s) for [${orderName}].`);
      let newRecordIds = await createBlankRecords(cakesTable, numberOfCakesNeeded);
      newRecordIds = newRecordIds.map((recordId) => {
        return {id: recordId};
      });
      let answer = await ordersTable.updateRecordAsync(orderRecord.id, {[linkedCakeField]: [...existingCakeRecordIds, ...newRecordIds]});

      // update statistics
      numCakesCreated += numberOfCakesNeeded;
      numOrdersUpdated += 1;
    }
  }

  output.markdown("# Done");
  output.markdown(`Created ${numCakesCreated} cake(s) for ${numOrdersUpdated} order(s)`);
}

async function createBlankRecords(table, numberOfRecords) {
  let fieldValues = {};
  let arrayOfFieldValues = [];
  // build up the proper number of records desired
  for (let i = 0; i < numberOfRecords; i++) {
    arrayOfFieldValues.push({fields: fieldValues});
  }
  let newRecordIds = await table.createRecordsAsync(arrayOfFieldValues);
  return newRecordIds;
}