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