Aug 08, 2020 04:51 AM
Hi everyone, I’m new to Airtable and am really struggling migrating a formula. I’ve spent a couple of hours and made little progress…
I am using Integromat to run a query against an Airtable date column. Basically the logic should be this “return all rows that match a specific date and time”. Anything returned (usually just one row per hour) will be then parsed by Integromat.
E.g. If I set 31.08.2020 15:30 in a date field in the column, I want Integromat when it runs (every 15 minutes) to return all the contents from that row to then be processed by Integromat. I’m generally fine working out the logic in Integromat…
Thanks!! :slightly_smiling_face:
Aug 08, 2020 04:55 AM
This is the same query I used to achieve the same thing with Google Sheets through Integromat: {{formatDate(now; “DD/MM/YYYY HH:00”)}}
Aug 08, 2020 07:31 AM
Welcome to the community, @Rich123! :grinning_face_with_big_eyes: There are a few issue that I can see.
First, the values
argument item only works inside a rollup field’s aggregation formula, and represents the values collected by the rollup. It has no meaning outside of a rollup field.
Second, the ARRAYJOIN()
function only works when you have an array of items. Despite how the documentation might be written, you can’t pass in your own comma-separated values to it and combine them.
The biggest issue, though, is that your current formula only attempts to build a string based on the date in the {Scheduled}
field. To actually match against specific records, the formula should return either TRUE or FALSE, but it’s not making any comparisons based on that string, so it’s going to return TRUE for everything because a non-empty string evaluates as TRUE in Airtable. My gut says that you want to compare it against Integromat’s now
date using a similar format. Here’s something that I think will work for your use case.
DATETIME_FORMAT(Scheduled, "DDMMYYYYHH") = "{{formatDate(now; "DDMMYYYYHH")}}"
I built this directly in Integromat, and tested the copy-paste from there to here and back, and everything looks good, so you should be able to copy that directly to your Formula setup field in the trigger module.
Aug 08, 2020 10:10 AM
Hi @Justin_Barrett, thanks you are a genius…!
This would have taken me 100 iterations and 10 hours to work out.
Thanks again, onwards and upwards…!