This script checks all imported transactions that do not have a “Paid To?” Single Select value (ie, I have not yet matched them to a pretty colored Single Select value yet) to see if I have assigned the import name to a value yet. Those match values that I’ve assigned are stored in another table, so that they persist from one run of the Scripting Block to the next.
If the script finds a match between the import name on the Transaction and the name of a Match record, then it gets the Single Select value for the Match and assigns it to the transaction. It then notifies me, at the end, of how many matches it auto-assigned for me.
If the script does not find a match for the import name of the Transaction, then it assumes that a Match record does not exist for that import name yet – so it creates a Match record for me, and notifies me, at the end, of how many new Match records I need to go make a matching Single Select value for.
Once the script is run, if I have new Match records created, I can go assign them a Single Select value. Often, the Single Select value already exists (a vendor or company I already have matched against with a different import name), so I just assign it the existing Single Select value. If this is an entirely new vendor or company, I assign a new Single Select value, and then add that same Single Select value to the “Paid To?” field in the Transactions table as well.
To see the full written tutorial of how to script works and how to use it click here.
To see the script in action in an Airtable Universe base check this out.
Script Code
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;
}