Accounts receivables reconciliation: comparing text strings in two unrelated tables by automation

[0800-grizzly]

Hi,

I'm trying to attempt accounts receivables reconciliation in Airtable. I have managed to get email bank statements parsed with Zapier, which creates short strings like "NN has the paid the bill 499,00" in one table ("statements"). In another table I have orders ("Orders"), some of which are open (and now marked "paid" by hand).

I had thought I easily could make an automation triggered by the creation of a new record in Statements, which would check for any open orders matching on the name. I tried putting two 'Find records' actions after each other, where I thought I could feed the output of the first into the second, thereby filtering down to a result with only those records for which there is a match in both tables. After that I would create a Repeating action to update all those record.

Unfortunately I couldn't find a way to feed the output (list of names) to the second 'Find records' to match a contains -filter. Saw just an error message "Cannot assign list of string to string".

Then I tried seeing if I could use helper columns in either table to sort out if there are matches (like in Excel), but found out that "Find" in a formula cannot do its find in another table. There's no way to connect the Statement records to the Order table when creating them, so any rollups are also out of question I suspect.

I think this would have been easy in Excel, but now I can't think of any other ways to try in Airtable.

Would anybody have some idea how to achieve this?

Rgds,

Björn

[TheTimeSavingCo]

Could you provide screenshots of the two tables with the relevant fields and some example data?  Would love to help but could use more info!

[0800-grizzly]

Thanks for helping!

I'm afraid the table and example data may not help that much, but here goes.

"Orders" table with "Name" and "Paid sum" columns, we have an unpaid record for Helena Anonymised:

"Statements" table with "Content" and "Payer's name" columns. "Content" holds the raw data, there is also a sum on a second row not visible here. "Payer's name" is a REGEX_EXTRACT formula (\w+ \w+) just picking out the two first words in "Content", .i.e. extracting the payer's name.

So, when a record is created in "Statements" where the payer's name can also be found in "Orders" with an empty "Paid sum" column, I want to fill that column with a static string/sum.

TIA,

Björn

[TheTimeSavingCo]

Thanks for the details!  Hmm, I wonder if the Find Record action thing isn't working because of the field types

If you could provide a read-only invite link to a duplicated copy of your base with no data in it I'd love to take a look at it for you.  I'd also need to know which tables and fields you'd want to search by!