AirTable module formula error

How To
,
1

What are you trying to achieve?

I have a webhook that receives an email address. I have two Airtable tables: Customers and Accounts. They have a one-to-one relationship and link fields have been setup correctly between the two.

In Accounts, I have the following fields:

Email (from Customers) - a lookup field
Expiry - a date field storing dates in DD/MM/YYYY format
Credits - a number field

I’m trying to Search for a Record using an Airtable module in Make where the email equals the email contained in the webhook, Expiry is less than today’s date and Credits is > 0.

Steps taken so far

I am using the attached formula but it returns an error - please see attached. What am I doing wrong?

Screenshots: scenario setup, module configuration, errors

airtable configuration.png
airtable configuration.png806×1368 116 KB

error.png
error.png762×1066 103 KB

2

Hey Jake

Out on a limb here, so take this advice with a grain of salt.

From what I can read in Airtable’s documentation, you need to create formulas in the table.

I am unsure if your directly able to filter on fields like you do.
Maybe just test the filter with something like:

{email} = “t5@gmail.com”

To check if that works.

Hope it helps.

/Riko

3

Just saw something else, the error returns that email is an unknown field, is the field named differently in Airtables? Do Airtable us two names for coulmns (like a pretyfied human readable one) and a internal/raw one?

4

Thanks for your reply. All I can think of is that Email is a lookup field in my Accounts table. I don’t know if that makes any difference when trying to search for a record based on a lookup field.

5

Try this.

AND(
{Email} = “1. email”,
IS_BEFORE({Expiry}, NOW()),
{Credits} > 0
)

6

:man_facepalming:

A few of things:

  1. I was referencing the field as “{Email}” when I actually needed to reference it as “{Email} (from Customers)” as it’s a lookup field.
  2. There was no need to formatDate. Just using {Expiry} > now(), worked. (I found this surprising as now() does not appear as a function in the Airtable module date functions - but it worked.
  3. Expiry date should be greater than current date. (Wouldn’t explain why the formula wasn’t working but a logic error nonetheless)

So the actual formula should be:

AND(
{Email (from Customers)} = “t5@gmail.com”, //hardcoded for testing
{Expiry} > now(),
{Credits} > 0
)

2 Likes