Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Feb 05, 2021 02:26 PM
Hello!
Hoping I could get some advice on a formula field that accepts my formula but the cells won’t return any data (not even an #error message). Maybe it’s a bug…? Since I’m dealing with multiple formula fields, lookups and a Rollup result that is being fed back to the original table, I’m thinking it’s an issue with Airtable recognizing the data…Any workarounds or syntax advice?
Problem formula is in table 2:
IF({Legal Wraps After}=‘Episode’, {Episode Legal Wrap Date}, IF({Legal Wraps After}=“Season”, {Season Legal Wrap Date}, BLANK()))
Table 1: SEASONS
Table 2: EPISODES
Any help here would be much appreciated! Thanks!
Solved! Go to Solution.
Feb 05, 2021 09:15 PM
You say that the formula is in table 2—the [Episodes]
table—but the first field that the formula references ({Legal Wraps After}
) is in the first table, [Seasons]
. Are you looking up the value of {Legal Wraps After}
based on a link so the formula can use it?
The only syntax advice I can offer doesn’t directly address this problem, but does make the structure a little more streamlined. Instead of using nested IF()
functions, I suggest using the SWITCH()
function. The transformation would look like this:
SWITCH(
{Legal Wraps After},
"Episode", {Episode Legal Wrap Date},
"Season", {Season Legal Wrap Date}
)
Whether you use IF()
or SWITCH()
, you can omit the reference to BLANK()
at the end. The last argument in both functions is optional, and will default to the proper blank-equivalent value based on the type returned by the other options.
Back to your issue, you might check the options that you added to your {Legal Wraps After}
single select field. Specifically, look for spaces at the ends of those words. If the entry is "Episode " (notice the space), then it won’t match when compared against “Episode”.
Feb 05, 2021 09:15 PM
You say that the formula is in table 2—the [Episodes]
table—but the first field that the formula references ({Legal Wraps After}
) is in the first table, [Seasons]
. Are you looking up the value of {Legal Wraps After}
based on a link so the formula can use it?
The only syntax advice I can offer doesn’t directly address this problem, but does make the structure a little more streamlined. Instead of using nested IF()
functions, I suggest using the SWITCH()
function. The transformation would look like this:
SWITCH(
{Legal Wraps After},
"Episode", {Episode Legal Wrap Date},
"Season", {Season Legal Wrap Date}
)
Whether you use IF()
or SWITCH()
, you can omit the reference to BLANK()
at the end. The last argument in both functions is optional, and will default to the proper blank-equivalent value based on the type returned by the other options.
Back to your issue, you might check the options that you added to your {Legal Wraps After}
single select field. Specifically, look for spaces at the ends of those words. If the entry is "Episode " (notice the space), then it won’t match when compared against “Episode”.
Feb 06, 2021 08:10 AM
It looks like your formula in table 2 is referencing a lookup in table 1. A lookup returns an array, not a string.
Use @Justin_Barrett’s formula with a slight addition of forcing the lookup into a string.
SWITCH(
{Legal Wraps After} & "",
"Episode", {Episode Legal Wrap Date},
"Season", {Season Legal Wrap Date}
)
\
Feb 08, 2021 11:59 AM
Thank you @Justin_Barrett and @kuovonne for suggestions! It was totally that I had an extra space in both "Episode " and "Season " in Table 1. Everything is working fine now!