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 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!
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.
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.
For example, this functionality can be done on Google Sheets without any custom coding, which I find extremely flexible.
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
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.