About Zapier Community
New to our Community? Get to know more about our space and how to make the most of your time here.

Code of Conduct Community FAQ's Zapier Common Questions What to Expect

Get Started With Zapier
New to Zapier? We have got you covered with our Zapier 101 Resources.

Zapier Essentials

Get Support
Have a question? Let's get you an answer!

How Do I...? Troubleshooting Code & Webhooks Developer Zone

Other Help Resources
Wait, there's more! Check out these Community sourced spaces for additional resources.

Zapier Help Center Hire an Expert

Zapier Products
Learn more about Zapier's new products.

Zapier Tables Zapier Interfaces

Zapier Reading
All of our best learning resources can be found here.

Featured Articles Show & Tell AI Hub Zapier Tools Hub

Learning Groups
Excited to meet other builders? Check out our interest areas full of doers, dreamers, and in-betweeners.

User Groups

Other Learning Resources
Some of our favorite additional ways to upskill your Zapier knowledge.

Zapier Learn Zapier Blog Zapier Webinars

Product Updates
Check out what is new and upcoming with Zapier with our regular product updates.

Product Updates

Early Access Program
Want to join Early Access? Learn more here.

Best answer

Possible to Use Look Up Table for Value Between Two Dates?

S
Userlevel 1
Badge

I have a set of values I want to reference (via Look Up Table, but open to other suggestions) in subsequent steps and want to retrieve the value associated to a date range. Any ideas on how to best accomplish this? I saw some advance LUT materials but none indicated anything close to this. I also looked at date manipulations and they didn’t cover this scenario.

 

Intended flow:

  1. Zap runs on schedule (daily, weekly, etc.)
  2. Zap gets current date (pretty format or maybe need to use <date>/<month>/<year> 
  3. Zap looks to see if current date falls between a specified range (need guidance here as LUT seems not an option) and fetches the corresponding value which can be passed/consumed in subsequent steps.
    startDate endDate value
    5/10/22 5/23/22 alpha
    5/24/22 6/6/22

    beta
    6/7/22 6/20/22 charlie
    6/21/22 7/11/22

    delta
icon

Best answer by christina.d 2 June 2022, 09:06

View original
This post has been closed for comments. Please create a new post if you need help or have a question about this topic.

14 replies

Troy Tessalone
Userlevel 7
Badge +14

Hi @salesperson 

Good question.

You can use an Airtable Base Table as a Lookup Table: https://zapier.com/apps/airtable/integrations#triggers-and-actions

Check out this help article: https://support.airtable.com/hc/en-us/articles/218151657-Using-Zapier-s-Multi-Step-Zaps-to-find-and-update-records#advanced

⚡ Troy Tessalone - AutomationAce.com | Premier Certified Zapier Expert | #1 Zapier Community Contributor
S
Userlevel 1
Badge

@Troy Tessalone Appreciate the suggestion. Never used AirTable but just signed up for an account and setup a base and table. Reading through the docs (a bit) I see I can set more complex formulas and that I’d likely need to use the AND() to compare two separate date expressions (below). I get an error that the formula is invalid. Other tips?

 

 

 

Troy Tessalone
Userlevel 7
Badge +14

@salesperson 

Looks like you may be missing a closing ) at the end.

Also, you may need to use this date format for the mapped values: YYYY-MM-DD

Can take a bit of trial and error to get the formula correct.

⚡ Troy Tessalone - AutomationAce.com | Premier Certified Zapier Expert | #1 Zapier Community Contributor
S
Userlevel 1
Badge

Found the missing )… docs seem to show dates as I’m set them, which matches the date syntax in the AirTable. Is that not valid?

And what appears to have the correct parens and quotes in place:

 

Troy Tessalone
Userlevel 7
Badge +14

@salesperson

For each AND condition it expects an evaluation such as IS_AFTER(DATE,DATE)=1

Another way to test is by adding a Formula Field to the Airtable base and trying to configure the formula, then once configured convert it to the Zap.

⚡ Troy Tessalone - AutomationAce.com | Premier Certified Zapier Expert | #1 Zapier Community Contributor
S
Userlevel 1
Badge

I’ve been experimenting with different syntax in an Airtable formula field and had this mostly working, but I’d expect this to place the crewNumber value in row 3. The syntax passes the formula field as valid. Any hints?

IF(AND((DATETIME_FORMAT('5/11/22', 'I'))>dateStart,(DATETIME_FORMAT('5/11/22', 'I'))<dateEnd),crewNumber,"noBetween")

 

Troy Tessalone
Userlevel 7
Badge +14

@salesperson 

Best to decouple the Formula logic to see what the returned values/results are so you can make sense of why the formula is or is not returned the expected values.

⚡ Troy Tessalone - AutomationAce.com | Premier Certified Zapier Expert | #1 Zapier Community Contributor
S
Userlevel 1
Badge

@Troy Tessalone

After breaking this down a bit more…

This formula in Airtable, works as expected.

IF((AND(DATESTR('5/11/2022')>DATESTR(dateStart),DATESTR('5/11/2022')<DATESTR(dateEnd))),crewNumber,"nope")

And we can see that using todays date, correctly set the value in the column.

Transferring this same formula to Zapier results in different behavior.

I’d expect the Zapier run of the formula to retrieve match to row 3. Or am I missing some other Airtable function that is needed?

 

Troy Tessalone
Userlevel 7
Badge +14

@salesperson 

Try removing the IF condition at the start and end

⚡ Troy Tessalone - AutomationAce.com | Premier Certified Zapier Expert | #1 Zapier Community Contributor
S
Userlevel 1
Badge

And trying a different formula logic (that also works in Airtable) seems to fail from Zapier.

IF(AND(IS_AFTER(DATESTR("5/11/2022"),{dateStart}),(IS_BEFORE(DATESTR("5/11/2022"),{dateEnd}))),{crewNumber},"notBetween")

Added as formula2:

This works in Airtable, but from Zapier, same formula (inserting {} around the column names):

I’m beginning to think this is less of a ‘me’ issue… Support ticket filed with Zapier also.

S
Userlevel 1
Badge

@salesperson

Try removing the IF condition at the start and end

Wow! First test is as expected… now I gotta get my head around why dropping the IF makes the difference. A good explanation will be useful to future me and others that find this thread… :)

Really appreciate your guidance @Troy Tessalone 

Troy Tessalone
Userlevel 7
Badge +14

@salesperson 

The IF condition is returning either a value for TRUE or FALSE.

Whereas by removing the IF condition, it evaluates the AND condition for the specified fields and will return a matching record.

⚡ Troy Tessalone - AutomationAce.com | Premier Certified Zapier Expert | #1 Zapier Community Contributor
nicksimard
Userlevel 7
Badge +11

Hi @salesperson!

Great to see that you and Troy worked through this by using Airtable. I wanted to offer an alternate solution that uses Formatter by Zapier and our spreadsheet-style formulas:

A little sneak peek: 

There’s a Shared Zap in that article that you can use as a starting point for your own workflow :)

christina.d
Userlevel 7
Badge +9

Hi friends! There’s a ton of useful here in this thread and I wanted to pop in and consolidate some of it. 

For this scenario, Troy recommended using Airtable (specifically a Lookup Table) as a solution. He also linked this helpful resource from Airtable on advanced features in Zapier.

After fiddling with the formula a bit the OP mentions:

This formula in Airtable, works as expected.

IF((AND(DATESTR('5/11/2022')>DATESTR(dateStart),DATESTR('5/11/2022')<DATESTR(dateEnd))),crewNumber,"nope"​​​​​​)

 

but with unexpected behavior in Zapier.

 

Troy recommended removing the IF condition, which appeared to do the trick.

@salesperson

The IF condition is returning either a value for TRUE or FALSE.

Whereas by removing the IF condition, it evaluates the AND condition for the specified fields and will return a matching record.

 

We also had an alternative recommendation from Nick!

I wanted to offer an alternate solution that uses Formatter by Zapier and our spreadsheet-style formulas:

A little sneak peek: 

There’s a Shared Zap in that article that you can use as a starting point for your own workflow :)

I hope this helps!

Terms & Conditions