Formula Functions — COALESCE, IFNULL, NVL & NULLIF

HARi formulas include four null-handling functions for building a value out of a first choice and a fallback: COALESCE, IFNULL, NVL and NULLIF. Use them when a field might be empty and you want a sensible default instead of a blank — for example a display name that falls back to “Unknown” when there is no last name. The one thing to know up front: in HARi, “empty” means both a missing value and an empty text field (""), so a fallback fires on a blank field, not only on a never-set one. That single rule is what makes these functions feel obvious to a business user — and it is a deliberate difference from how a database would behave.

The four functions at a glance

Function	Arguments	Returns
COALESCE(a, b, …)	two or more	the first argument that is neither empty nor missing; if every argument is empty, it returns nothing
IFNULL(a, b)	exactly two	a when a has a value, otherwise b
NVL(a, b)	exactly two	identical to IFNULL — NVL is just the name Oracle users know
NULLIF(a, b)	exactly two	nothing when a equals b, otherwise a — useful for turning a placeholder like "N/A" back into a blank

IFNULL and NVL are the same function under two names — pick whichever reads better to you. COALESCE is the general form: give it as many fallbacks as you like and it walks left to right until one isn’t blank.

What counts as “empty”: the one rule that trips people up

HARi treats an empty text field as empty — not just a missing one

In a database, COALESCE skips only a truly missing value (SQL NULL). HARi also skips an empty string (""). So COALESCE(last_name, "Unknown") returns “Unknown” both when last_name was never set and when it is blank text. This is intentional: to a non-technical user, a blank field and a missing field are the same thing, and both should fall through to the fallback. If you have ever been surprised that a “not null” database column still looked empty on screen, this is exactly the gap HARi closes.

This matters because empty and missing values are one of the most common sources of confusion in spreadsheet and formula work — a cell that looks empty can still hold an empty string that a blank-check misses. HARi removes that trap by treating both the same: if the field shows nothing, the fallback fires.

There is a matching rule on the other side, and it is just as important:

Zero is a value, not empty. COALESCE(discount, 100) where discount is 0 returns 0, not 100. The functions only skip a missing value or empty text — never a real 0, and never false. If you want zero to fall through to a fallback, that is a different question (test discount == 0 with a SWITCH or an IF), not what these functions do.

Worked example: a display name that never shows a stray space

The most common use is building a person’s full name when the last name might be missing. Here is the exact formula HARi uses:

TRIM(CONCAT(first_name, " ", COALESCE(last_name, "")))

Reading it inside-out:

• COALESCE(last_name, "") — use the last name if there is one; otherwise fall back to an empty string.
• CONCAT(first_name, " ", …) — join the first name, a space, and that result.
• TRIM(…) — strip the leftover space when the last name was blank.

The result:

first_name	last_name	Formula result
John	Doe	John Doe
John	(blank or missing)	John

Without the ''-as-empty rule, a blank last_name would have produced "John " with a trailing space — which TRIM then cleans. Together, COALESCE + TRIM give you a tidy name in every case, whether the last name is missing, blank, or present.

When to reach for each one

• A default for a possibly-blank field — COALESCE(field, "fallback") or the two-argument IFNULL(field, "fallback"). Both do the same job for one fallback; COALESCE is the one to use when you have several fallbacks to try in order: COALESCE(mobile, work_phone, home_phone, "no phone on file").
• The Oracle spelling — NVL(field, "fallback") if that is the name in your muscle memory. It behaves exactly like IFNULL.
• Blank out a placeholder — NULLIF(status, "N/A") returns nothing when the status is literally "N/A", so a placeholder value stops showing up as real data. NULLIF compares loosely, so treat it as “does this look the same as the value I want to hide?” rather than a strict, type-exact match.

Related functions and the full reference

These four are the null-handling family. HARi formulas include many more building blocks — CONCAT, TRIM, SWITCH, IF, SUM, DISPLAY and others — for text, numbers and conditionals. A complete formula-function reference covering the full set is planned as a follow-up (tracked internally as FORMULA-DOCS-1); this page documents the null-handling family specifically, because its empty-string behavior is the one place a formula author is most likely to be caught out.

To put these to work, open any formula field in the form or field designer and type the function name — HARi validates the formula as you go and shows a clear error if an argument count is wrong.