Query date column to match date/time (within one hour) - Integromat

[Rich123]

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:

[Rich123]

This is the same query I used to achieve the same thing with Google Sheets through Integromat: {{formatDate(now; “DD/MM/YYYY HH:00”)}}

[Justin_Barrett]

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.

[Rich123]

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…!