Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Feb 12, 2019 12:15 PM
I’m trying to create a formula that will list multiple fields that are empty, but can only get it to list a single field. It is meant to be a quick reference field to see what info is not filled out in a row.
The goal: IF {Field 1}, {Field 2}, {Field 3} are all empty, but {Field 4} is filled out, then list “Field 1, Field 2, Field 3”
Below is my attempt. It only ever lists a single field, seemingly overwriting other values that would be returned. If I introduce OR IF it breaks the formula.
IF({Film Year}=BLANK(), “Film Year”, IF({Print Source}=BLANK(), “Print Source”,IF({RT (min)}=BLANK(), “RT (min)”)))
Solved! Go to Solution.
Feb 12, 2019 05:37 PM
In that case, you want chained IF()
statements rather than nested.
For instance
IF(
NOT(
{Field 1}
),
'Field 1,'
)&IF(
NOT(
{Field 2}
),
'Field 2,'
)&IF(
NOT(
{Field 3}
),
'Field 3,'
)&IF(
NOT(
{Field 4}
),
'Field 4'
)
That’s a little sloppy, in that it will leave a dangling comma on any test where {Field 4}
is present, but with a bit moire conditional logic you can prettify it, if needed.
Feb 12, 2019 12:27 PM
You have to group your checks with AND() because you need to get all of them true. And then in the if true
part output the 3 fields like: Field1 & ', ' & Field2
and so on.
Feb 12, 2019 02:11 PM
For the record, I’m a real novice at this. Per your instruction (as I understood it) I entered the following:
IF(AND({Film Year}=BLANK(), AND({Print Source}=BLANK(), AND({RT (min)}=BLANK()))), “FilmYear & ', ’ & PrintSource & ', ’ & Runtime”)
If all fields (Film Year, Print Source, RT) are blank, then it literally returns “FilmYear ', ’ PrintSource ', ’ Runtime”
I actually need it to return a value for every instance that BLANK() is true, and omit returning a value for every instance that BLANK() is not true.
Apologies if I misunderstood your instruction, or if I’m not communicating my question properly.
Feb 12, 2019 02:28 PM
I recommend you to read the guides: https://guide.airtable.com/
The syntax for AND() is: AND(Condition1, Condition2,…)
. You just need 1 AND()
.
Feb 12, 2019 02:30 PM
I don’t understand this party, because you’d get nothing.
What you actually want to do? :thinking:
Feb 12, 2019 03:03 PM
I will definitely use the reference link. Thank you.
I thought a visual aid would do a better job than me describing the goal. Below is an example image of what the finished product would be. I am trying to create the “What’s Missing” field. Whenever the other fields are not filled out, they are listed in the “What’s Missing” field.
Feb 12, 2019 05:37 PM
In that case, you want chained IF()
statements rather than nested.
For instance
IF(
NOT(
{Field 1}
),
'Field 1,'
)&IF(
NOT(
{Field 2}
),
'Field 2,'
)&IF(
NOT(
{Field 3}
),
'Field 3,'
)&IF(
NOT(
{Field 4}
),
'Field 4'
)
That’s a little sloppy, in that it will leave a dangling comma on any test where {Field 4}
is present, but with a bit moire conditional logic you can prettify it, if needed.
Feb 13, 2019 01:57 AM
The tree formatting makes me to think that the IFs are childs, and they are not.
Anyway, what you want this for? The fields are right there and you see what is missing. Maybe there is a easy way to do what in the end you’d want to do.
Feb 13, 2019 09:29 AM
Perfect! Thanks so much!
Feb 13, 2019 09:38 AM
The image was just an example. It will actually be going through 350 fields to check if info is missing for just 5 specific fields. The purpose is to have a quick reference so a person won’t need to comb through everything to see what’s missing.
@W_Vann_Hall’s formula works except for the “dangling comma”, so we can consider this solved. Thanks to you both for the help!