Skip to main content
Solved

How to Find records within a dynamic date range

  • November 24, 2023
  • 4 replies
  • 340 views
B
Forum|alt.badge.img+3

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

Best answer by Sho

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?

4 replies

ScottWorld
Forum|alt.badge.img+35
  • ScottWorld
  • Genius
  • November 24, 2023

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.

S
Forum|alt.badge.img+21
  • Sho
  • Inspiring
  • Answer
  • November 25, 2023

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
Ken_Szczepanski
B
ScottWorld
Forum|alt.badge.img+35
  • ScottWorld
  • Genius
  • November 25, 2023

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

S
B
Forum|alt.badge.img+3
  • BB-Bacuti
  • Author
  • New Participant
  • November 26, 2023

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?


Hi @Sho 

That's brilliant. It worked.

Terms & ConditionsAccessibility statement