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 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?

@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.

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:

@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.

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")

@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

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?

@salesperson 

Try removing the IF condition at the start and end

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.

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 

@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.

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 :)

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:

but with unexpected behavior in Zapier.

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

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!