Help

How to Find records within a dynamic date range

Topic Labels: Automations
Solved
Jump to Solution
630 4
cancel
Showing results for 
Search instead for 
Did you mean: 
BB-Bacuti
4 - Data Explorer
4 - Data Explorer

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

1 Solution

Accepted Solutions
Sho
11 - Venus
11 - Venus

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

2023-11-25 100744.png

Why does Airtable make us go through all this trouble?

See Solution in Thread

4 Replies 4

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
11 - Venus
11 - Venus

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

2023-11-25 100744.png

Why does Airtable make us go through all this trouble?

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

Hi @Sho 

That's brilliant. It worked.