Rules Based Tagging (Multiple Select)

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:

Link to the base here.

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;
}
2 Likes

Hi @VictoriaPlummer - are you able to share the base you show above?

1 Like

Oof! Thanks for the reminder!

1 Like