Help

Re: Airtable for personal finance [Show and tell]

1921 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Mariusz_S
7 - App Architect
7 - App Architect

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.

  1. The first table contains all the transactions data.
    CleanShot 2021-02-23 at 11.20.22@2x
  2. 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.
    CleanShot 2021-02-23 at 11.21.21@2x

Here’s the script in action:

CleanShot 2021-02-22 at 10.01.53

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:

4 Replies 4
Taylor_Johnston
6 - Interface Innovator
6 - Interface Innovator

Extremely useful - thank you for sharing!

Mariusz_S
7 - App Architect
7 - App Architect

Added a new script: Transaction Splitter!

Scenario:
We all know an amazon purchase can mean different things.
It could be a grocery item, a new gadget or both hence the need to split the transaction and categorize accordingly.

The script below (triggered by a button field) that does exactly that.
Here’s a demo:

Once done the original transaction can be removed.

Full script below:

// Click the "gear" icon in the top right to view settings
let config = input.config({
    title: 'Your script with settings',
    description: 'A script that uses settings',
    items: [
        input.config.table('selectedTable1', {
            label: 'Use the table containing transactions: 👇',
            }),
        input.config.table('selectedTable2', {
            label: 'Use the table containing categories: 👇'
            })
    ]
});

let transactionsTable = config.selectedTable1;
let record = await input.recordAsync('', transactionsTable);
let recordID = record.id;

let transactionAmount = record.getCellValue("Amount");
let transactionDescription = record.getCellValue("Transaction");
let transactionDate = record.getCellValue("Date");
let transactionAccount = record.getCellValue("Account #");
const categoriesTable = config.selectedTable2;
const categoryField = categoriesTable.getField('Match');
const categoryFieldOptions = categoryField !== null ? categoryField.options.choices : [];
let newValue = categoryFieldOptions.find(o => o.name > 0);

output.text(`The transaction to spilt is ${record.getCellValueAsString("Amount")} from ${record.getCellValueAsString("Transaction")}`);

let categoryOptions = [];
for (let option of categoryFieldOptions) { categoryOptions.push({label: option.name.toString(), value: option.name}) };

let recordsData = [];
let totalAmount = 0;
let newTransactionAmount = 0;
while (totalAmount != transactionAmount){
        let newTransactionAmount = await input.textAsync('Please enter the amount')
        let newTransactionAmountCategorySelect = await input.buttonsAsync('Pick a category for the split:', categoryOptions); 
        let toFixedAmount = (x) =>  Number((x).toFixed(2));
        totalAmount += Number(newTransactionAmount);
        let remainingAmount = toFixedAmount(transactionAmount - totalAmount);
        if (Math.abs(remainingAmount) > 0){
            output.text(`There's $${remainingAmount} unallocated from the original transaction`)
        }else{
            output.markdown(`#### **All done ✅**`)
        }        
    await transactionsTable.createRecordAsync({
        "Amount": Number(newTransactionAmount),
        "Category": {name: newTransactionAmountCategorySelect},
        "Transaction": `split from \' ${transactionDescription} \'for $ ${Math.abs(transactionAmount)}` ,
        "Date": transactionDate,
        'Account #':transactionAccount,
    
    });
}

// Ask to delete the record
let deleteRecord = await input.buttonsAsync('Delete the original transaction?', [
    {label: 'Yes', variant: 'danger'},
    {label: 'Cancel'},
]);

// Delete record
if (deleteRecord=== 'Yes'){
    await transactionsTable.deleteRecordAsync(record.id);
    output.markdown(`#### **Deleted the original transaction for $ ${transactionAmount.toFixed(2)}**`)
    }
Brennan_Ward
5 - Automation Enthusiast
5 - Automation Enthusiast

This is wonderful - Thank you for this! It’s exactly what I was searching for.

Is there a way to invert the output numbers, so that your user input can be positive, and it would create the record with a negative number (So that the user does not have to type “-” every time)? It might be a little more intuitive if so.

Mariusz_S
7 - App Architect
7 - App Architect

Glad you like it @Brennan_Ward!

I’ve made a quick fix so the input from the user is absolute value (no need to type “-”) regardless if it’s a debit or credit but would indicate the type of transaction in the workflow i.e. “-” for debit transactions.

// Click the "gear" icon in the top right to view settings
let config = input.config({
    title: 'Your script with settings',
    description: 'A script that uses settings',
    items: [
        input.config.table('selectedTable1', {
            label: 'Use the table containing transactions: 👇',
            }),
        input.config.table('selectedTable2', {
            label: 'Use the table containing categories: 👇'
            })
    ]
});

let transactionsTable = config.selectedTable1;
let record = await input.recordAsync('', transactionsTable);
let recordID = record.id

let transactionAmount = record.getCellValue("Amount");
let transactionDescription = record.getCellValue("Transaction");
let transactionDate = record.getCellValue("Date");
let transactionAccount = record.getCellValue("Account #");
const categoriesTable = config.selectedTable2;
const categoryField = categoriesTable.getField('Match');
const categoryFieldOptions = categoryField !== null ? categoryField.options.choices : [];
let newValue = categoryFieldOptions.find(o => o.name > 0);

output.text(`The transaction to spilt is ${transactionAmount} from ${transactionDescription}`);

let categoryOptions = [];
for (let option of categoryFieldOptions) { categoryOptions.push({label: option.name.toString(), value: option.name}) };

let recordsData = []
let totalAmount = 0
let newTransactionAmount = 0
while (Math.abs(totalAmount) != Math.abs(transactionAmount)){
        let newTransactionAmount = await input.textAsync('Please enter the amount')
        let newTransactionAmountCategorySelect = await input.buttonsAsync('Pick a category for the split:', categoryOptions); 
        let toFixedAmount = (x) =>  Number((x).toFixed(2));
        totalAmount += Number(transactionAmount) < 0 ?  -Number(newTransactionAmount) : Number(newTransactionAmount)
        let remainingAmount = toFixedAmount(transactionAmount - totalAmount)
        if (Math.abs(remainingAmount) !== 0){
            output.text(`There's $${remainingAmount} unallocated from the original transaction`)
        }else{
            output.markdown(`#### **All done ✅**`)
        }        
    await transactionsTable.createRecordAsync({
        "Amount": Number(newTransactionAmount),
        "Category": {name: newTransactionAmountCategorySelect},
        "Transaction": `split from \' ${transactionDescription} \'for $ ${Math.abs(transactionAmount)}` ,
        "Date": transactionDate,
        'Account #':transactionAccount,
    
    });
}

// Ask to delete the record
let deleteRecord = await input.buttonsAsync('Delete the original transaction?', [
    {label: 'Yes', variant: 'danger'},
    {label: 'Cancel'},
]);

// Delete record
if (deleteRecord === 'Yes'){
    await transactionsTable.deleteRecordAsync(record.id);
    output.markdown(`#### **Deleted the original transaction for $ ${transactionAmount.toFixed(2)}**`)
    }