How to Find records within a dynamic date range

[BB-Bacuti]

Hi

I have two tables: Table A that has a period_start_date and a period_end_date and Table B has a bunch of rows with a date column

In an automation that's triggered by a particular row on table A (which means I know the period_start_date and period_end_date to use) I want to find all the records from table B where the value in the date column lies between the period start and end.

The "Find records" automation seems to allow only static date values, but I need to use the period start and end dates from table A.

Any suggestions on how to do this without having to fetch all the rows from table B and then loop through in code, which feels very wasteful and slow.

Thanks in advance

[ScottWorld]

Unfortunately, Airtable doesn’t offer this functionality, which seems like a major oversight to me.

I typically search for dynamic date ranges in a no-code way by using Make, but if you know JavaScript code, you can also write a script to do this search.

[Sho · Accepted solution]

Hi @BB-Bacuti ,

I have come up with a solution.
Converting dates to numbers allows for dynamic date "Find Records".

Convert all dates to "X" with a formula like this

IF({Date},
  VALUE(DATETIME_FORMAT({Date},"X"))
)

Automation is set up like this

Why does Airtable make us go through all this trouble?

[ScottWorld]

That's a fantastic workaround, @Sho! Brilliant! I love it! 😎🙌

[BB-Bacuti]

Hi @Sho 

That's brilliant. It worked.