Help

Welcome to the Airtable Community! If you're new here, check out our Getting Started area to get the most out of your community experience.

Rules Based Tagging (Multiple Select)

1691 2
cancel
Showing results for 
Search instead for 
Did you mean: 

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:

Screen Recording 2020-05-04 at 08.22 PM

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 Replies 2

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