> ## Documentation Index
> Fetch the complete documentation index at: https://docs.allgoodhq.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Handle blank and messy values in formulas

> Check for blank fields, fall back to another value, and join text or do math safely, so formulas give the right answer on records with missing data.

Real data has gaps. Some people have no phone number. Some names arrive with extra spaces. A form field a visitor left empty can arrive as empty text. Formulas treat each of these a little differently, and the differences cause most surprises. This article shows how to check for blank values, pick a fallback, and keep joins and math from giving odd results.

## Prerequisites

* A list open in MK, with the formula box ready. [Filter a list with a formula](https://docs.allgoodhq.com/mk/formulas/filter-a-list.md) shows where it is.
* The exact names of the fields you want to check. [How formulas find your data](https://docs.allgoodhq.com/mk/formulas/how-formulas-find-your-data.md) shows how to look them up.

## What counts as blank

A field can hold nothing in several ways. The four checks in the table treat them differently.

| Value in the field | `empty()` is | `exists()` is | Counts as true | `flag()` is |
| - | - | - | - | - |
| No value | true | false | no | false |
| Empty text `''` | true | false | no | false |
| Spaces only `'  '` | true | false | yes | false |
| Text `'a'` | false | true | yes | false |
| The number `0` | false | true | no | false |
| Text `'no'` | false | true | yes | false |

* **`empty()`** is true for no value, empty text, and spaces only. It is false for `0`.
* **`exists()`** is the opposite of `empty()`. It is true when the field is filled.
* **Counts as true** is how a formula reads a value in a yes-or-no spot, such as after `!` or around `&&`. Spaces-only text counts as true, which is rarely what you want.
* **`flag()`** reads a field that spells out yes or no in words. See [Read a yes or no field](#read-a-yes-or-no-field).

Use `empty()` and `exists()` when you ask "is there anything real here?" They are the safest choice for data that came from forms, imports, or connected systems.

## Steps

**1. Decide what a blank record should do**

Pick one answer for records where the field is blank. Should they stay in the list, or leave it?

**2. Choose the check**

Use `exists()` to keep only records that have a value. Use `empty()` to find records that lack one.

**3. Put the check first**

Write the check first, then `&&`, then your real question. A record that fails the check never reaches your question. For example:

```
exists($`person.title`) && !$`person.title`.toLowerCase().includes('intern')
```

**4. Apply the formula**

Type the formula in the **ƒx** box, then press **Enter** or select outside the box.

**5. Test a blank record**

Find a record where the field is blank. Confirm it behaves the way you chose in the first step.

You will see a clear answer for records with data and for records without it. The check goes first because text and number tests give odd answers on no value, as the next table shows.

| Without a check | What goes wrong | With a check |
| - | - | - |
| ``!$`person.title`.toLowerCase().includes('intern')`` | With no title, `.includes()` has no answer, and `!` turns that into true. People with no title pass the filter. | ``exists($`person.title`) && !$`person.title`.toLowerCase().includes('intern')`` |
| ``$`company.employeeCount` < 500`` | No value counts as 0, so the answer is true. | ``exists($`company.employeeCount`) && $`company.employeeCount` < 500`` |

`exists()` treats `0` as filled, so a company with an employee count of `0` stays in the list.

## Check several fields at once

`empty()` and `exists()` accept a list of fields, separated by commas. The answer depends on **every** field in the list.

| Situation for fields `a` and `b` | `empty(a, b)` | `exists(a, b)` |
| - | - | - |
| Both filled | false | true |
| `a` filled, `b` blank | false | false |
| `a` blank, `b` filled | false | false |
| Both blank | true | false |

Notice that `empty(a, b)` and `exists(a, b)` are not opposites. When one field is filled and one is blank, both are false.

| You want to ask | Write |
| - | - |
| Are all of these blank? | `empty(a, b)` |
| Are all of these filled? | `exists(a, b)` |
| Is at least one of these filled? | `!empty(a, b)` |
| Is at least one of these blank? | `!exists(a, b)` |

For example, this keeps people who have an email or a phone number:

```
!empty($`person.email`, $`person.phone`)
```

| Email | Phone | After |
| - | - | - |
| `'a@b.co'` | `'555-0100'` | `true` |
| `'a@b.co'` | `''` | `true` |
| No value | `'  '` (spaces only) | `false` |

## Pick the first filled value

A **fallback** tries one field, then another, then a default. This is useful in a column, such as a greeting name.

```
!empty($`person.fullName`) ? $`person.fullName` : !empty($`person.firstName`) ? $`person.firstName` : 'friend'
```

Read it from left to right. MK stops at the first condition that is true.

| Full name | First name | After |
| - | - | - |
| `'Jordan Rivera'` | `'Jordan'` | `'Jordan Rivera'` |
| No value | `'Jordan'` | `'Jordan'` |
| `'  '` (spaces only) | `'Jordan'` | `'Jordan'` |
| No value | No value | `'friend'` |

Two shorter options exist, and they behave differently on messy data.

| Formula | Skips no value | Skips empty text | Skips spaces only |
| - | - | - | - |
| `a ?? b ?? 'friend'` | Yes | No | No |
| `a \|\| b \|\| 'friend'` | Yes | Yes | No |
| The `empty()` version above | Yes | Yes | Yes |

Use `??` when `0` is a real answer, such as a score. Use the `empty()` version for text from forms and imports.

## Join text when a piece might be missing

Joining two fields with `+` gives no value when either piece has no value. A person with a first name and no last name would show an empty cell. Give each field a fallback of empty text, then trim the extra space:

```
(($`person.firstName` ?? '') + ' ' + ($`person.lastName` ?? '')).trim()
```

| First name | Last name | After |
| - | - | - |
| `'Ann'` | `'Lee'` | `'Ann Lee'` |
| `'Ann'` | No value | `'Ann'` |
| No value | `'Lee'` | `'Lee'` |
| No value | No value | `''` (empty text) |

Always put some typed text between the fields, even if it is only a space. Writing `` $`person.firstName` + $`person.lastName` `` tries to add the two fields as numbers, and MK rejects it.

## Do math when a number might be missing

In math, no value counts as `0`. The formula ``$`company.annualRevenue` / 12`` shows `0` for a company with no revenue, which looks like real data. To leave the cell empty, check first and return no value:

```
exists($`company.annualRevenue`) ? $`company.annualRevenue` / 12 : null
```

| Annual revenue | After |
| - | - |
| `1200000` | `100000` |
| `0` | `0` |
| No value | No value (empty cell) |

## Read a yes or no field

Some fields hold words such as `Yes`, `TRUE`, or `y` instead of a true or false value. Without help, the text `'no'` counts as true, because it is not empty. Use `flag()` to read these fields as yes or no:

```
flag($`lead.opted_in`)
```

Replace `lead.opted_in` with a yes-or-no field from your own data.

| Value in the field | After |
| - | - |
| `'Yes'`, `'TRUE'`, `'y'`, `'on'`, `'1'` | `true` |
| The number `1` | `true` |
| `'no'`, `'maybe'`, `''`, no value, `0` | `false` |
| `' yes '` (spaces around it) | `false` |

`flag()` does not remove spaces. If your data has stray spaces, trim first: ``flag($`lead.opted_in`.trim())``. When the field has no value, `.trim()` gives no value, and `flag()` reads that as false.

## If a formula still gives the wrong answer

Check [Formula gives a blank, wrong, or error result](https://docs.allgoodhq.com/mk/formulas/not-working.md). It lists the most common causes. If the answer is still wrong, contact allGood support. Include the formula and one record where the result is wrong.

## Related articles

* [What formulas are and when to use one](https://docs.allgoodhq.com/mk/formulas/what-formulas-are.md)
* [Filter a list with a formula](https://docs.allgoodhq.com/mk/formulas/filter-a-list.md)
* [Add a calculated column to a list](https://docs.allgoodhq.com/mk/formulas/add-a-calculated-column.md)
* [Formula reference: concepts, functions, and operators](https://docs.allgoodhq.com/mk/formulas/reference.md)


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.