Conditionally displaying missing field values in Airtable

To start, we'll be using an applicant tracking base as an example scenario. This simple base is setup to track each stage an applicant goes through during a company hiring process.

The recruiting team needs to make sure a number of tasks are completed for each candidate during each stage of the process. These tasks correspond to the checkboxes in our table:

• The candidate passing the interview (Interviewer Pass)

• Updating the team (Team Update)

• The Recruiter following up with the candidate (Recruiter Follow Up)

• Moving the candidate to the next stage (Move Stage)

• Optionally, if the candidate is applying for a management role, updating the department director (Director Update)

For their workflow, the recruiting team would like a single field to show a list of any tasks that are remaining for each candidate in each stage. They want to use this field to send an automated update every day to their team's Slack channel for more visibility into the recruiting process.

Starting with the logic needed to accomplish this, here's the requirements written out plainly:

• If any of the fields above are blank, show the task for that field as missing.

• If the candidate is applying for a management role, also include the director update field.

• Display the results of checking each field in a single formula field, ideally with each missing task separated by a comma, and separated by a line break.

An IF statement is needed to check each of these fields, and will be structured like this:

IF(
   {Interviewer Pass}=BLANK(), 
   "Interviewer Pass"
)

However, we'll need to string five conditional statements together in the formula field. We'll also need to build in comma separation and line breaks. Here's what that should look like with just two out of the five sets of statements we'll need.

Let's breakdown what's going on in the formula above:

1. This checks if the field is empty (in this case, unchecked)

2. Display this text if the field is empty

3. Adds a comma separator ", "

4. Adds a line break "\n"

5. Allows a separator so the next conditional statement can be added

When you add on the other three sets of conditional statements, the entire formula will look like this:

IF({Interviewer Pass}=BLANK(), "Interviewer Pass" & ", " & "\n") 
&
IF({Team Update}=BLANK(), "Team Update" & ", " & "\n")
&
IF({Recruiter Follow Up}=BLANK(), "Recruiter Follow Up" & ", " & "\n")
&
IF({Move Stage}=BLANK(),"Move Stage" & ", " & "\n")
&
IF(
  AND(
    FIND("Manager", Role),
    {Director Update}=BLANK()
  ),
  "Director Update"
)

Notice that for the last statement there are two additional functions - AND() and FIND() - that allow for a conditional check to see if the role is a management role, and if it is, add on a check to see if the {Director Update} field is checked.

For the final result, we can see that every field that is checked off will be removed from the running list of missing tasks, until they're all complete.

With the added line breaks, each task will appear on its own line. A benefit of the formula string used for this approach is that there aren't an empty lines between tasks shown when the row height is expanded.

This is an alternative approach to use of "\n", and for the sake of example it's helpful to see how the typical approach to line breaks would look in this scenario. As a quick review:

• The typical line break approach uses this string to force line breaks between functions in a formula: & "\n" &. This string would go after each formula function where you want to insert a line break.

• The alternate approach (used in this article) uses this modified string:& "\n"). This string goes within each function, making the line break part of the conditional display of text (in the IF statement).

The result of this alternate approach is that only a line break is added if the condition is met - in this case, if a task is missing.

For reference, here's how the full formula used above would look with the typical line break approach:

IF(
 {Interviewer Pass}=BLANK(), 
 "Interviewer Pass, ")

& "\n" &

IF(
 {Team Update}=BLANK(), 
 "Team Update, ")

& "\n" &

IF(
 {Recruiter Follow Up}=BLANK(), 
 "Recruiter Follow Up, ")

& "\n" &

IF(
 {Move Stage}=BLANK(), 
 "Move Stage, ")

& "\n" &

IF(
 AND(
 FIND("Manager", Role),
 {Director Update}=BLANK()
 ),
 "Director Update"
)

And here's an example of what the result would be; notice the line breaks between tasks, which isn't ideal for this scenario (and takes up unnecessary visual space).