Hi all,
Had a bit of inspiration from this cool script shared by @Jeremy_Oglesby.
My goal was to have an easy solution to categorize the transactions imported from all of my accounts/financial institutions.
Here’s the scenario:
There will be some frequently occurring transactions like Uber or your preferred supermarket that should have been categorised accordingly so the remaining ones can be categorised manually.
Some personal finance apps might do it for you but I always find them lacking in some other areas hence I always built my own solution - previously in spreadsheets and now Airtable.
This was surprisingly easy to achieve with a small modification of the script.
Here’s how it works.
- The first table contains all the transactions data.
- The second table is a list of categories with a matching “keyword” separated by the pipe symbol ("|") that will be used to categorize the transactions in the first table.
Here’s the script in action:
Finally here’s the actual script:
// BASE SPECIFIC NAMES - TABLES, VIEWS, FIELDS
// ** Change these to match your base schema **
const BaseSpecificNames = {
transactionsTableName: "Transactions",
transactionsPayeeFieldName: "Category",
transactionImportFieldName: "Transaction",
payeeMatchesTableName: "Categories",
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
let catValue = payee.getCellValue(BaseSpecificNames.payeeMatchesNameFieldName)
if (catValue.split("|").find(v => transactionPayee.toUpperCase().includes(v))) {
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: {"Category": newValue}};
transactionsToUpdate.records.push(record);
// Increment this up to show a total of matches found in the output of the script
matchesRecorded++
}
})
}
});
// 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(`* Transactions categorised: ${recordsUpdated}`);
}
/*
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;
}
There would probably other use cases for this script and hope that some of you might find it helpful :rocket:
