Matching bank transactions to a record

[Karl_at_Easy_La]

I am looking into the possibility to migrate a Google Sheets solution, to Airtable.  One piece of functionality I was doing in Google Sheets was the below.

• I have a list of bookings. Each booking has a 'ValueAmount', and PaidOn
• I have an export of BankTransactions. This has information about Date, Description & Total of each transaction

I want to be able to locate the BankTransaction automatically, for each paid booking. How this can be found is by taking the PaidOn, and giving it a time window of 7 days, and looking for a transaction matching the exact Value amount. As a condition this would be like: Booking.MatchingBankTransaction = (where Transaction.Value = Booking.ValueAmount and Transaction.TransactionDate >= Booking.PaidOn and Transaction.TransactionDate <= Booking.PaidOn + 7 days.

The reason for the 7 days, is because the bank can show transactions later. 

Using the FILTER function in Google Sheets, I am able to automatically match this with just a formula.  Airtable has no concept of the FILTER function.  Can one do this functionality easily in Airtable?  I tried looking into Lookup or Rollup fields, however since there is no direct link to the table, these are not very useful. Is it possible to automatically query data from another table, and link it to another table, similar to the FILTER function in Google Sheets?

Thanks, any advice appreciated!

[pressGO_design]

It seems, from your last question and this one, that you’re trying to replicate what you have in Google Sheets in Airtable. The problem with that - well, one of the problems with that - is that you need to structure your data differently in Airtable than it was structured in Google Sheets.

For example, jn a property booking base, one would expect to have a table of Properties, a table of Customers, a table of Bookings that has Customers, Properties, Dates, Amounts, Payments, etc. 

From what you’ve written; I’m not sure you have that. If you do, then I’ve completely misunderstood and I apologize.

If you don’t, I would recommend looking into restructuring your data out of a spreadsheet model and into a database model. 

[fintable]

At Fintable, an official Airtable App, we do have customers who do transaction / invoice reconciliation similar to what you have described. One way to achieve what you are doing here is to use Linked Records and Automations. For example, you can have an Automation run every time a new transaction appears, look for the corresponding booking, and then create a link between the two. Fintable does provide free consulting and setup assistance for such workflows for paying customers.

[Karl_at_Easy_La]

I'm trying to switch to Airtable, from Google Sheets.  I am looking to see what is possible, and what is not in Airtable, compared to Google Sheets. I want to make sure I do not hit any walls later on, when I've already converted most of our information. Hence, I am testing out different functionalities, and trying to anticipate what could be the pain points of such a conversion.. Both of my questions relate to that.  My intention, if working with Airtable, is to convert them to a proper database layout, with the tables you mentioned and more. The issue with this scenario is that there is no direct link from a Booking, to the Bank transaction. There is no primary key/linked relationship which can be set, to match the Booking to its relevant Bank Transaction.  In Google Sheets, I was using a query by using filter, to find and match the transaction without any code. My question is - And you can assume an ideal database structure, can something like this be done in Airtable easily, or would it require say custom Javascript coding, to match such transactions on certain triggers? Any advice would be appreciated on this matter.

For example, this functionality can be done on Google Sheets without any custom coding, which I find extremely flexible.

[fintable]

Yes, there is a concept of Linked Records. A transactions (or multiple) can be linked to a booking.

[TheTimeSavingCo]

Hmm, if you want it fully automated (i.e. automatically link a Booking to a BankTransaction based on those requirements, include duplicates if there are any), then you'd need a script

If you're doing this manually, you could try making the primary field of the BankTransactions table contain the Date and Total.  The workflow would then be to: In the Booking table, add a new link and key in the transaction amount.  This will filter to only display records with that Total, and you could eyeball the dates from there.

You could further streamline this by creating a view in BankTransactions that only displays records that aren't linked to a Booking record

Another option would be for you to write a script extension for this which you could activate via a button click.  It would then search for all the records that matched the criteria and output them as options for you to select.  You could then click on the correct one and the script extension would link it together for you

[Karl_at_Easy_La]

Yeah, that's what I assumed - Was hoping there would be some kind of solution which does not need custom scripting. It seems like anything which you can do in Google Sheets by using the FILTER formula, you'd have to do with a custom script in Airtable - as there is no concept to link data without a Linked Record. 

Regarding the primary key option - that wouldn't work as there is a small possibility that you would have multiple transactions which have the same date & amount - In that case one would need to manually match it.  I'll have to look into having some custom script triggering every X time, and looking to match transactions which are not matched yet.