Match Payee Scripting Block Demo

Explore

View a more detailed description with photos and video in the Airtable community forums here:

https://community.airtable.com/t/scripting-block-match-ugly-import-transaction-names-to-pretty-single-select-values/27735?u=jeremy_oglesby

​

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 if 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

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.

​

Explore
Updated March 3, 2020 at 5:10 PM
Copied 64 times

Jeremy Oglesby

Just a regular guy.
Explore the base
Main View
Interior Decor
Grid view