Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Airtable Community
- Discussions
- Ask A Question
- Formulas
- If Statement Formula focused on a Lookup Field

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Topic Labels:
Formulas

Solved

Jump to Solution

0
725
2

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Comment Post Options

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jan 22, 2024 10:26 AM

I have the following formula in a field names "Status":

IF({Current Performance}=5,"5: Excellent",

IF({Current Performance}>=4,"4: Good",

IF({Current Performance}>=3,"3: Fair",

IF({Current Performance}>=2,"2: Poor",

IF({Current Performance}<2,"1: Very Poor")))))

The Current Performance field is a lookup field set to show the last (most recent) item from the related table.

If the Current Performance field is blank (no record in the lookup table) the formula will return "Very Poor" even though there is no number in the Current Performance field. I've tried various iterations of the formula, but can't seem to make it behave properly. Essentially, if the Current Performance lookup field is empty there should be no corresponding record in the Status formula field.

Any ideas/suggestions?

Solved! Go to Solution.

1 Solution

Accepted Solutions

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jan 22, 2024 11:03 AM - edited Jan 22, 2024 11:04 AM

Hey @Tom_Glatt!

Here's a version of your formula that will return blank if the *Current Performance* field returns null:

```
IF(
{Current Performance} = 5,
"5: Excellent",
IF(
{Current Performance} >= 4,
"4: Good",
IF(
{Current Performance} >= 3,
"3: Fair",
IF(
{Current Performance} >= 2,
"2: Poor",
IF(
AND({Current Performance}, {Current Performance} < 2),
"1: Very Poor"
)
)
)
)
)
```

Here's an walkthrough on what's happening. To start, here's a formatted version of your formula:

```
IF(
{Current Performance} = 5,
"5: Excellent",
IF(
{Current Performance} >= 4,
"4: Good",
IF(
{Current Performance} >= 3,
"3: Fair",
IF(
{Current Performance} >= 2,
"2: Poor",
IF(
{Current Performance} < 2,
"1: Very Poor"
)
)
)
)
)
```

The problem is caused by the evaluation parameter of the last nested IF function:*{Current Performance} < 2*

When Airtable polls the *Current Performance* field for a value and comes back with a null value, the expression is translated to *null < 2*.

Airtable evaluates null as a falsy, which shares loose equality with a number value of 0.

Knowing that a number data type would satisfy the formula syntax, Airtable treats the null value of your *Current Performance* field as a 0.

With this, we now know that Airtable is evaluating the expression *null < 2* as *0 < 2*. Since this always resolves to true, *1: Very Poor* will always be returned *Current Performance* is null.

We can account for this by implementing an AND function to check that the field is not empty (or zero) but less than a value of 2.

2 Replies 2

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jan 22, 2024 11:03 AM - edited Jan 22, 2024 11:04 AM

Hey @Tom_Glatt!

Here's a version of your formula that will return blank if the *Current Performance* field returns null:

```
IF(
{Current Performance} = 5,
"5: Excellent",
IF(
{Current Performance} >= 4,
"4: Good",
IF(
{Current Performance} >= 3,
"3: Fair",
IF(
{Current Performance} >= 2,
"2: Poor",
IF(
AND({Current Performance}, {Current Performance} < 2),
"1: Very Poor"
)
)
)
)
)
```

Here's an walkthrough on what's happening. To start, here's a formatted version of your formula:

```
IF(
{Current Performance} = 5,
"5: Excellent",
IF(
{Current Performance} >= 4,
"4: Good",
IF(
{Current Performance} >= 3,
"3: Fair",
IF(
{Current Performance} >= 2,
"2: Poor",
IF(
{Current Performance} < 2,
"1: Very Poor"
)
)
)
)
)
```

The problem is caused by the evaluation parameter of the last nested IF function:*{Current Performance} < 2*

When Airtable polls the *Current Performance* field for a value and comes back with a null value, the expression is translated to *null < 2*.

Airtable evaluates null as a falsy, which shares loose equality with a number value of 0.

Knowing that a number data type would satisfy the formula syntax, Airtable treats the null value of your *Current Performance* field as a 0.

With this, we now know that Airtable is evaluating the expression *null < 2* as *0 < 2*. Since this always resolves to true, *1: Very Poor* will always be returned *Current Performance* is null.

We can account for this by implementing an AND function to check that the field is not empty (or zero) but less than a value of 2.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jan 22, 2024 11:06 AM