I use tags to highlight some characteristics of records in my table. In this dataset I want to highlight whenever I have a “Stale Deal” and/or a “New Customer.” I want to use multi-select options because they’re easy to filter and chart. However, I don’t want to manually tag each select option, because there’s a formulaic way I can assess these characteristics:
- ‘New Customer’ = Deal Count is one
- ‘Stale Deal’ = Time since last inquiry is > 30 days.
I’ve tried to do this via a formula and isn’t pretty to write, read, filter, or chart by. So, why not replace that formula with a script? Works like a charm:
Happy Scripting!
/*
Calculations:
'New Customer' = Deal Count == 1
'Stale Deal' = Times Since Last Inquiry > 30
*/
// Load Records
let table = base.getTable('Data');
let query = await table.selectRecordsAsync();
let records = query.records
// Create an Array of tags when a record matches the criteria
let check = records.map( c => ({id:c.id,tags:[
c.getCellValue('Deal Count') == 1 ? 'New Customer' : null,
c.getCellValue('Times Since Last Inquiry') > 30 ? 'Stale Deal' : null,
]
}));
// Write tags to 'Tags' field
let update = check.map(c => ({id:c.id,fields:{'Tags':c.tags.filter(x => x).map(x => ({name:x}))}}))
// Update more than 50 records at a time using my favorite script from @Jeremy_Oglesby 😇
await batchAnd('Update',table,update)
/*
Use this function to perform 'Update', 'Create', or 'Delete'
async actions on batches of records that could potentially
more than 50 records.
::PARAMETERS::
action = string; one of 3 values:
- 'Update' to call table.updateRecordsAsync()
- 'Create' to call table.createRecordsAsync()
- 'Delete' to call table.deleteRecordsAsync()
table = Table; the table the action will be performed in
records = Array; the records to perform the action on
- Ensure the record objects inside the array are
formatted properly for the action you wish to
perform
::RETURNS::
recordsActedOn = integer, array of recordId's, or null;
- Update Success: integer; the number of records processed by the function
- Delete Success: integer; the number of records processed by the function
- Create Success: array; the id strings of records created by the function
- Failure: null;
*/
async function batchAnd(action, table, records) {
let recordsActedOn;
switch (action) {
case 'Update':
recordsActedOn = records.length;
while (records.length > 0) {
await table.updateRecordsAsync(records.slice(0, 50));
records = records.slice(50);
};
break;
case 'Create':
recordsActedOn = [];
while (records.length > 0) {
let recordIds = await table.createRecordsAsync(records.slice(0, 50));
recordsActedOn.push(...recordIds)
records = records.slice(50);
};
break;
case 'Delete':
recordsActedOn = records.length;
while (records.length > 0) {
await table.deleteRecordsAsync(records.slice(0, 50));
records = records.slice(50);
}
break;
default:
output.markdown(`**Please use either 'Update', 'Create', or 'Delete' as the "action" parameter for the "batchAnd()" function.**`);
recordsActedOn = null;
}
return recordsActedOn;
}
