Skip to main content
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

​
What counts as blank

A field can hold nothing in several ways. The four checks in the table treat them differently.
  • 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.
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:
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. 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. Notice that empty(a, b) and exists(a, b) are not opposites. When one field is filled and one is blank, both are false. For example, this keeps people who have an email or a phone number:

​
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.
Read it from left to right. MK stops at the first condition that is true. Two shorter options exist, and they behave differently on messy data. 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:
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:

​
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:
Replace lead.opted_in with a yes-or-no field from your own data. 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. 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.