Automation: Linking record dynamically based on Date ranges

[Luis_Tamez]

Hi Airtable community!

I need your help... I am working on a personal finance project and have 3 tables: 1) Credit Cards, 2) Statements, 3) Transactions...

On the Credit Cards table, I have a list of the Credit Cards available to make transactions (e.g. Amex Platinum, Chase Sapphire, etc...)

On the Statements table I have a list of all the monthly statements for the each of the cards (e.g. Amex Platinum - Nov 2022 Statement). Each Statement record has a linked record to a Credit Card (from the Credit Cards table) and also a Start date and Close date, which define the range/period for all the transactions pertaining to a Statement.

On the Transactions table, each transaction has a Date, amount, category, vendor, and a Credit Card (linked record to Credit Card table). 

What I need to achieve is to link each Transaction to a corresponding Statement, based on the Credit Card and the Date information on each Transaction. Today I am creating this link manually, but I would like to automate this process. This way, each transaction will be linked to Statement automatically.

I am not sure if a formula, a script or an automation is the right way, but in any case, I don't know how to go about it. Could you please provide advice and feedback?

Thank you!

[Marielle_Gueis1]

Hi @Luis_Tamez , 

What type of plan do you have? If you have a pro account, you could create an automation that runs a script. 

You could choose the trigger type:

• (every time you add a transaction to your transactions table - is that automated?)
• at the end of the month, run a script that looks into your transactions table and links all the right records with each other
• Script manually triggered

Personally, I would suggest to use the first option and do the following: 

1. Create an automation
2. Use "When record matches conditions" 
   1. Date is not empty
   2. Credit Card not empty
3. create add the following script with input (3)

   1. transactionRecId (the record ID from the previous step)

   2. CreditCard (the linked record to the credit card table from the previous step) 

   Spoiler

   when you select the linked record, you need to click on the arrow showing on the variable, then click on Display and select ID (instead of Name) (see screenshot)

   

   3. transaction date - the transaction date from the previous step

let inputConfig = input.config()
const transactionRecId = inputConfig.transactionRecordId
const creditCard = inputConfig.creditCard
const transactionDate = new Date(inputConfig.date)

let statementsTable = base.getTable('Statements');
let statementsQuery = await statementsTable.selectRecordsAsync();


// only get the statements with the corresponding credit card
let filteredRecords = statementsQuery.records.filter(statement => {
    return statement.getCellValue('Credit Card')[0].id === creditCard
})

// check whether the transaction date lies inside the range
filteredRecords.every( function (record) {
    let rangeStart = new Date(record.getCellValue('Start date'))
    let rangeEnd = new Date(record.getCellValue('Close date'))
    if (transactionDate >= rangeStart && transactionDate <= rangeEnd) {
        // transaction date lies inside the range -> link the statement to the transaction
        let transactionsTable = base.getTable('Transactions')
        transactionsTable.updateRecordAsync(transactionRecId, {
            'Statement': [{id: record.id}]
        })
        return false // breaks the loop
    }
    return true
})

I haven't tested the code, because I didn't recreate the tables, but let me know whether that helps!  

[TheTimeSavingCo]

If you don't have a Pro account and can't use scripts, you could look into creating a queued automation system that would link all of your `Statement` records to each new `Transaction` record too.  The idea would be to use a lookup to display the transaction's `Date` value in the `Statements` table, and use a formula field to determine whether that transaction's date falls within the `Statement` record's `Start date` and `Close date` value (as well as matching the card, of course)

You'd then have another automation that would update the original `Transaction` record with the matching `Statement` record