Matching Transaction Names to Single Select Values

							
							And here’s the full text of the script code as it exists in that example base:

/*
Script: Match imported transaction names to pretty Single Select values
Author: Jeremy Oglesby
License: MIT

I import transaction records into a log in Airtable, and those transaction records
have consistent names for transactions from a particular location or store. However,
those imported transaction names are also ugly! I like to use colored Single Select
values for the payee names to use in things like the Chart Block.

Instead of manually selecting the Single Select value for each payee (which I had to
do up until now), this script will check to see if I have established a match between
the import name for each transaction and a Single Select payee name. If so, it auto-
populates that Single Select field for me; if not, it creates a new match record for
me, so I can establish a match for the next time that same import name comes up!

The script utilizes a table of "Match Records" as a database to check new imports
against, and if a new import name is recognized, then a new "Match Record" is created
so that future runs of the script can match against that new import name.

Most Base Specific information is stored in the constant at the beginning of the script
(just below), but there are two spots near the end of the script where the name of a
field must be supplied manually as a string value.
*/

// BASE SPECIFIC NAMES - TABLES, VIEWS, FIELDS
// ** Change these to match your base schema **
// There are 2 spots in the script where the string name of the field must be hard-coded
const BaseSpecificNames = {
    transactionsTableName: "Transaction Log",
    transactionsPayeeFieldName: "Paid To?",
    transactionImportFieldName: "Import Name",
    payeeMatchesTableName: "Paid To Matches",
    payeeMatchesNameFieldName: "Name",
    payeeMatchesMatchFieldName: "Match"
};

const transactionsTable = base.getTable(BaseSpecificNames.transactionsTableName);
const transactionsPayeeField = transactionsTable.getField(BaseSpecificNames.transactionsPayeeFieldName);
const transactionsPayeeFieldOptions = transactionsPayeeField !== null ? transactionsPayeeField.options.choices : [];
const transactionsQuery = await transactionsTable.selectRecordsAsync();
const transactions = transactionsQuery.records;

const payeeMatchTable = base.getTable(BaseSpecificNames.payeeMatchesTableName);
const payeeQuery = await payeeMatchTable.selectRecordsAsync();
const payees = payeeQuery.records;

// Keep track of how many new match records we create
let newPayeeMatchRecords = 0;

// Keep track of how many matches were established and recorded
let matchesRecorded = 0;

// Batch up all the transaction records that we can update with a match
let transactionsToUpdate = {records: []}

// Batch up all the new payee match records we need to create
let payeeMatchRecordsToCreate = {records: []}

// Loop over each transaction...
transactions.forEach(transaction => {
    let payeeField = transaction.getCellValue(BaseSpecificNames.transactionsPayeeFieldName);

    // If this transaction already has the Payee name match made, find the name there; if not, this is blank
    let payeeName = payeeField ? payeeField.name : '';

    // If the payee Single Select field is empty (this transaction has not been matched yet)
    // then check for import name matches in the matches table
    if (payeeName === '') {
        
        // matchFound defaults to false until/unless a match is found
        let matchFound = false;
        let transactionPayee = transaction.getCellValue(BaseSpecificNames.transactionImportFieldName);
        
        // Loop over each record in the matches table to check them against the import name of the transaction
        payees.forEach(payee => {
            
            // If a match is found between import name and an existing match record, set matchFound to true
            // and add the transaction record to the list of records to be updated with the payee name found
            if (transactionPayee === payee.getCellValue(BaseSpecificNames.payeeMatchesNameFieldName)) {
                matchFound = true;
                let matchPayeeField = payee.getCellValue(BaseSpecificNames.payeeMatchesMatchFieldName);
                let matchPayee = matchPayeeField ? matchPayeeField.name : '';

                let newValue = transactionsPayeeFieldOptions.find(o => o.name === matchPayee);
                
                // Field name must be manually changed here since only a string value can be supplied
                let record = {id: transaction.id, fields: {"Paid To?": newValue}};
                transactionsToUpdate.records.push(record);
                
                // Increment this up to show a total of matches found in the output of the script
                matchesRecorded++
            }
        })

        // If no match was found after looping through match records, and this transaction has not
        // been matched previously, then add a record to be created in the matches table so that
        // this import name can be matched in the future
        if (matchFound === false && transactionPayee !== '') {
            
            // Field name must be manually changed here since only a string value can be supplied
            let record = {fields: {"Name": transactionPayee}};
            payeeMatchRecordsToCreate.records.push(record);

            // Increment this up to show a total of new payee match records created in the output of the script
            newPayeeMatchRecords++;
        }
    }
});

// Update transactions that were matched
// await transactionsTable.updateRecordsAsync(transactionsToUpdate.records);
let recordsUpdated = await batchAnd('Update', transactionsTable, transactionsToUpdate.records);

// Create new match records for unmatched import names
// await payeeMatchTable.createRecordsAsync(payeeMatchRecordsToCreate.records);
let recordsCreated = await batchAnd('Create', payeeMatchTable, payeeMatchRecordsToCreate.records);

if (recordsUpdated !== null && recordsCreated !== null) {
    output.markdown(`## Done!`);
    output.markdown(`* Matches recorded: ${recordsUpdated}`);
    output.markdown(`* New Payee names added to match list: ${recordsCreated}`)
}

/*
    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 or null; 
                   - Success: integer; the number of records processed by the function
                   - Failure: null;
*/
async function batchAnd(action, table, records) {
    let recordsActedOn = records.length;

    switch (action) {
        case 'Update':
            while (records.length > 0) {
                await table.updateRecordsAsync(records.slice(0, 50));
                records = records.slice(50);
            };
            break;
        
        case 'Create':
            while (records.length > 0) {
                await table.createRecordsAsync(records.slice(0, 50));
                records = records.slice(50);
            };
            break;

        case 'Delete':
            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;
}