It’s a shame that Airtable doesn’t support updating multiple records through a no-code configuration.
Unfortunately, as of today, there is no other choice than writing a script. And, depending on your workflow, it might be as simple as Update Multiple Records with Automation - #2 by CT3 answer, or something far more complicated.
I’m posting the below code for those who would to see how we deal with “complicated”.
It’s much more advanced than the above, and will also require tweaking the code to change the “Configuration” and the “Step 2” which defines what fields are updated.
The script is fairly well documented, and uses best-practices for testing and debugging. (DX)
We use a similar script (the same “base”) for all our automations.
The getRecordFieldValues is a helper (it takes more than half of the script’s size) and isn’t meant to be changed unless you want to add new use cases. It basically converts the fields described in BANK_ORDER_FIELDS_TO_FETCH into a more usable data format, to avoid having to transform each data manually. It’s not so useful here because there is only one field being transformed, but when you have a more fields to transform, it’s quite a lifesaver.
/**
* This script is used by an Airtable automation.
*
* It updates several MoneyOutBankOrder records.
*
* It works in a 3-steps workflow, such as:
* 1) It fetches data from Airtable (using the Automation internal API)
* - MoneyOutBankOrder - Fetches the data of the record that has triggered the automation
* 2) It updates the records contained in the field "bulkTransferSimpleOrders" of the trigger record and changes their "executionStatus" to "planned"
*
* @see https://airtable.com/appDEqCgPvoO63Pmw?automation=XXX
* @see https://www.airscript.dev/
* @see https://airtable.com/marketplace/category/scripts
*/
// ------------------------------------------------ Configuration -----------------------------------------------
/**
* Fields to fetch from the MoneyOutBankOrder table (for the trigger bank order).
*/
const BANK_ORDER_FIELDS_TO_FETCH = [
'ref',
'recordId',
{
name: 'bulkTransferSimpleOrders',
type: 'relationship-to-ids',
},
];
const bankOrderTableName = 'MoneyOutBankOrder';
const relationshipsValues = {};
/**
* For testing purpose, as fallback value when "inputConfig.triggerBankOrderId" is not set.
*
* Must be a valid ID from the "bankOrderTableName" table.
* You can use a formula field with "RECORD_ID()" to know the internal Airtable record ids.
*
* XXX This is to improve DX, because Airtable automation developer experience is shitty, and they don't allow to run automation that are based on Inputs from the UI.
* (or at least, not always, and the current behavior is confusing as of March 2021)
* So, to test an automation from the UI, one needs to remove all configured Inputs to run the script manually.
* And then, when the script works properly, re-configure all Inputs.
* And do this every time.
* The alternative is to run the scripts through automation, but it's harder to debug that way (small editor, not possible to run code directly from editor, etc.)
*/
const testTriggerBankOrderId = 'reclBJNkBppAFx0Xh'; // 'order-165-FROM-compte-de-booster-financier-TO-ambroise-dhenain-27'
// ----------------------------------------------------------------------------------
// -------------------- Script (end of script's configuration) ----------------------
// ----------------------------------------------------------------------------------
/**
* Resolves the values of the record, for all specified fields.
* Stores the resolved values in the destination.
*
* @param fields (array of fields)
* @param record (airtable record)
* @param destination (object)
*/
const getRecordFieldValues = (fields, record, destination) => {
fields.map(async (field) => {
const fieldType = typeof field === 'string' ? 'string' : field.hasOwnProperty('type') ? field.type : 'unknown';
let fieldName;
let fieldValue;
switch (fieldType) {
case 'single-select': // Treats the field as a "Single select" field, returns its "name" as an object and ignores the rest
case 'single-select-to-text': // Treats the field as a "Single select" field, returns its "name" as a string and ignores the rest
case 'multi-select-to-text': // Treats the field as a "Multi select" field, returns all its "name" as a string (comma-separated) and ignores the rest
case 'relationship': // Treats the field as a "Relationship" field and attempts to resolve it. Requires the relationship to have been fetched and stored into relationshipsValues beforehand (or it will crash)
case 'relationship-to-ids': // Treats the field as a "Relationship" field, but doesn't attempt to resolve it, returns the "id" of each element as an array
case 'relationship-to-names': // Treats the field as a "Relationship" field, but doesn't attempt to resolve it, returns the "name" of each element as an array
case 'formula': // Treats the field as a "Formula" field, returns the value (handles all edge cases where the structure might be an scalar value or object, etc.)
case 'lookup': // Treats the field as a "Lookup" field, returns the value (handles all edge cases where the structure might be an scalar value or object, etc.)
case 'number-as-float': // Treats the field as a "Number" field, returns the value as a float (decimal)
case 'number-as-int': // Treats the field as a "Number" field, returns the value as an integer
// case 'date':
case 'number-to-checkbox': // Treats the field as a "Checkbox" field, returns the value as a boolean
fieldName = field.name;
break;
default:
fieldName = field.hasOwnProperty('name') ? field.name : field;
break;
}
let fieldNameDest = typeof field === 'string' ? fieldName : field.hasOwnProperty('as') ? field.as : fieldName;
switch (fieldType) {
case 'single-select':
try {
fieldValue = record.getCellValue(fieldName);
if (fieldValue && Array.isArray(fieldValue)) {
fieldValue = fieldValue[0].name;
}
if (fieldValue && fieldValue.hasOwnProperty('name')) {
fieldValue = fieldValue.name;
}
} catch (e) {
// No value defined, ignored
}
break;
case 'single-select-to-text':
// Airtable stores single select choices as an array, or as an object (depends on their internal rules), which will be null if no choice has been set
fieldValue = record.getCellValue(fieldName);
if (fieldValue && Array.isArray(fieldValue)) {
fieldValue = fieldValue[0].name;
}
if (fieldValue && fieldValue.hasOwnProperty('name')) {
fieldValue = fieldValue.name;
}
break;
case 'multi-select-to-text':
// Convert potential multi choices to string, value will be null if no choice has been set
fieldValue = record.getCellValue(fieldName);
if (fieldValue) {
fieldValue = fieldValue.map((selectedChoice) => selectedChoice.name).join(', ');
}
break;
case 'relationship': {
const relationships = record.getCellValue(fieldName);
if (relationships !== null) {
fieldValue = relationships.map((relationship) => {
const relationshipId = relationship.id;
return relationshipsValues[field.table][relationshipId];
});
} else {
fieldValue = [];
}
break;
}
case 'relationship-to-ids': {
const relationships = record.getCellValue(fieldName);
if (relationships !== null) {
fieldValue = relationships.map((relationship) => {
return relationship.id;
});
} else {
fieldValue = [];
}
break;
}
case 'relationship-to-names': {
const relationships = record.getCellValue(fieldName);
if (relationships !== null) {
fieldValue = relationships.map((relationship) => {
return relationship.name;
});
} else {
fieldValue = [];
}
break;
}
case 'lookup':
case 'formula':
const recordValue = record.getCellValue(fieldName);
if (typeof recordValue === 'object' && recordValue !== null) {
fieldValue = record.getCellValue(fieldName)[0];
} else {
fieldValue = record.getCellValue(fieldName);
}
break;
case 'number-as-float':
fieldValue = parseFloat(record.getCellValue(fieldName));
break;
case 'number-as-int':
fieldValue = parseInt(record.getCellValue(fieldName), 10);
break;
// case 'date': XXX Doesn't work, day is wrong and time is lost, I believe it's just easier to treat dates as text and then use formulas to convert them into actual dates
// const value = record.getCellValue(fieldName);
// console.log('value', value);
// const date = new Date(value);
// console.log('date', date);
// fieldValue = date;
// break;
case 'number-to-checkbox':
fieldValue = parseInt(record.getCellValue(fieldName), 10) > 0;
break;
default:
fieldValue = record.getCellValue(fieldName);
if (fieldValue && typeof fieldValue === 'object' && fieldValue.hasOwnProperty('specialValue')) {
console.error(`[WARNING] The field "${fieldName}" has a "specialValue" property (this SHOULD NOT happen). This usually happens when processing a formula field which resolved to NaN (Not a Number). You probably should adapt your formula to avoid that edge case.`);
}
break;
}
destination[fieldNameDest] = fieldValue;
});
};
// -------------------- Step 1: Fetching Airtable data --------------------
/**
* Resolve the updated record from the Airtable "input.config" API.
*
* "inputConfig.triggerBankOrderId" will always be set when the script is executed through an Airtable automation, but it won't when we execute it manually (during script testing).
* This will show a red warning on Airtable until you configure a proper "Input variable" for the automation.
*/
const inputConfig = input.config();
const triggerBankOrderId = inputConfig.triggerBankOrderId || testTriggerBankOrderId; // Fallback to hardcoded test record id for better DX
// Fetching trigger bank order
const bankOrderValues = {};
const bankOrderTable = base.getTable(bankOrderTableName);
const bankOrderTableRecords = await bankOrderTable.selectRecordsAsync();
console.log('bankOrderTableRecords', bankOrderTableRecords);
const bankOrderRecord = bankOrderTableRecords.getRecord(triggerBankOrderId);
getRecordFieldValues(BANK_ORDER_FIELDS_TO_FETCH, bankOrderRecord, bankOrderValues);
console.log('bankOrderValues', bankOrderValues);
// -------------------- Step 2: Updating the MoneyOutBankOrder records --------------------
const bankOrdersToUpdate = bankOrderValues.bulkTransferSimpleOrders.map((orderId) => {
return {
id: orderId,
fields: {
executionStatus: {
name: 'planned',
},
},
};
});
try {
console.info(`Udpating data for "${bankOrderTableName}" table, where ref: "${bankOrderValues.ref}"`, bankOrdersToUpdate);
await bankOrderTable.updateRecordsAsync(bankOrdersToUpdate);
} catch (e) {
throw new Error(`Error happened while updating the table "${bankOrderTableName}": ${e.message}`);
}
