Re: Formula using lookup & rollup dates returns only blank cells?

Theodora_Hart · ‎Feb 05, 2021

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

{Legal Wraps After} - Single Select Field (episode / season)
{Last Shoot Date} - Rollup Field (MAX {Shoot Date} of all Episodes in a Season)

Table 2: EPISODES

{Shoot Date} - Date Field
{Episode Legal Wrap Date} - Formula Field: ({Shoot Date} + 7 days)
{Season Legal Wrap Date} -Lookup Field: (Lookup the {Last Shoot Date} Rollup Field)

Any help here would be much appreciated! Thanks!

Justin_Barrett · ‎Feb 05, 2021

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

See Solution in Thread

Justin_Barrett · ‎Feb 05, 2021

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

kuovonne · ‎Feb 06, 2021

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

\

Theodora_Hart · ‎Feb 08, 2021

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!