Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Jun 14, 2021 07:53 AM
Hi there,
I have a table with a single select field type called PROGRAM TYPE.
It contains 3 different program types, which I’ll just call PROGRAM A, PROGRAM B, PROGRAM C.
I also have a Date Joined field, and I’m creating a new date field called PROGRAM EXPIRES.
PROGRAM A expires 12 months after its ‘Date Joined’ date, and PROGRAM B expires 3 months after its ‘Date Joined’ date. PROGRAM C does not need an expiration date and can be an empty string, or by default be set to 12 months as well.
When I try:
IF({PROGRAM TYPE}="PROGRAM A", DATEADD({Date Joined}, 12,‘months’), DATEADD({Date Joined}, 3,‘months’))
I get an error.
I’ve also tried:
IF({PROGRAM TYPE}="PROGRAM A", DATEADD({Date Joined}, 12,'months'), IF({PROGRAM TYPE}="PROGRAM B",DATEADD({Date Joined}, 3,‘months'), IF({PROGRAM TYPE}="PROGRAM C", DATEADD({Date Joined}, 12,'months')))
And still get an error. I’ve made sure all spaces/quotations are correct based on other suggestions in this forum. Would greatly appreciate any help with this!
Jun 14, 2021 11:51 AM
Its possible the errors come from some of your quote marks being “curly”. Syntax-wise, your formulas are fine. Its also possible you’re getting an error when the {Date Joined}
field is empty.
You can try this formula:
IF(
AND({PROGRAM TYPE}, {Date Joined}),
SWITCH(
{PROGRAM TYPE},
"PROGRAM A", DATEADD({Date Joined}, "12", "months"),
"PROGRAM B", DATEADD({Date Joined}, "3", "months"),
)
)
Jun 14, 2021 07:17 PM
Thanks for the response! Unfortunately, I am getting an “invalid formula” response with this switch statement as well. The {Date Joined}
field is not empty in any of the cells, so that is not the issue.
Jun 14, 2021 07:30 PM
Strange. Can you post a screenshot?
Jun 15, 2021 05:24 AM
@Kamille_Parks, @Willo_Co_Support there is a stray comma in the third line of the SWITCH call. Try this:
IF(
AND({PROGRAM TYPE}, {Date Joined}),
SWITCH(
{PROGRAM TYPE},
"PROGRAM A", DATEADD({Date Joined}, "12", "months"),
"PROGRAM B", DATEADD({Date Joined}, "3", "months")
)
)
PS - I’m working on a tool that catches these kinds of errors in Airtable formulas. Still very early in development but my tool definitely found the error in this formula. Are you guys interested in such a tool?
Jun 15, 2021 11:26 AM
Absolutely. Is it an Airtable app or something that runs outside of Airtable?
Jun 15, 2021 09:28 PM
This could be very useful. Airtable formulas have a unique grammar.
You may also be interested in this tool.
Jun 16, 2021 12:51 AM
Well, how it will be packaged is still undecided as the code is in very early stages of development. I will prefer making the user experience as frictionless as possible such that people don’t have to drastically alter their workflows to use my tool. But how it will turn out remains to be seen.
I am still working on the parts that will do the heavy lifting of formatting/syntax highlighting and other IDE like features we developers have come to expect as standard.
Jun 16, 2021 01:20 AM
Yes, and that is indeed one of the harder parts – gleaning information about the grammar by trying out hundreds of formulas in a table.
Orc is indeed a helpful tool but what I am planning to do is much more than just pretty printing. Some of the planned features are:
And as a bit of a teaser, the syntax highlighting as seen in my terminal: