Re: Scripting Block: Match Ugly Import Transaction Names to Pretty Single Select Values
EDIT: Updated to incorporate solution for batching more than 50 records
Why this script
I import transaction records into Airtable from my bank on a regular basis via CSV files. Those transaction records have names that are pretty ugly most of the time (like [Amazon.com*6X6AN17G3]) and often there will be multiple transaction names that actually link back to the same company as a payee.
I want to know, for example, how much I paid out to “Fry’s Grocery Store” in total, no matter which Fry’s location I went to. And, I want to view that data in a Chart block, with pretty colors from Single Select options, for example.
So I used to manually match transactions with a colored Single Select value:
Needless to say, it was a tedious manual process, and I very frequently encountered the same Import name over and over – so I knew this was something the Scripting block could help me with, at least to some extent.
What the script does
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.
(Unfortunately, we cannot programmatically add new values to a Single Select field, so this does have to be done manually. If a value is added to the Match Single Select, but that value doesn’t exist yet in the Transactions “Paid To?” Single Select, the script cannot fill it in.)
How to use it
Here’s a video showing the script in action inside a slightly modified version of Airtable’s “Expense Tracking” base template:
The script contains a constant at the beginning holding names of tables and fields specific to the base it is in. Simply change those names to adapt this script to your base’s schema. There are, however, two spots in the script where Field names have been hard-coded as strings – those names must be replaced with hard-coded values as well. Both spots are marked with comments explaining so.
Here’s a Universe link to the modified “Expense Tracking” base seen in the video, and where the scripting block lives:
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;
}
