The lower() and upper() functions in Attio: building a match key your CRM can actually compare

lower() and upper() are the two most boring functions in Attio's new text batch. They take a string and change its case. There is no second argument, no edge case in the syntax, nothing to misread.

They are also the functions that decide whether everything else works. Every text comparison in Attio is an exact-string comparison, which means "Acme" and "acme" are two different companies as far as your formula is concerned — and your CRM contains both, because enrichment tools title-case, importers shout, and reps type in whatever case their hands were already in.

So this piece is not really about two functions. It is about the normalisation layer they let you build, what it can fix, and the two things it cannot.

This is part of our function-by-function series on Attio formula attributes — previously: if(), timeSpentIn(), dateDiff(), the ?? operator, count(), hasBeenIn(), valueSetAt(), contains(), sum(), previousValue(), dateAdd(), formatDate(), min()/max(), valueAt(), replace()/replaceAll(), avg()/median(), round()/ceil()/floor(), and()/or()/not(), length(), unique(), abs(), today()/now(), eomonth(), day()/hour(), mod(), power(), exp()/log(), split()/splitPart(), and startsWith()/endsWith().

Table of contents

• What lower() and upper() do
• A formula attribute cannot clean the column it reads
• Lower both sides or neither
• lower() belongs on the inside
• Case is only one of the ways text disagrees
• Building a match key
• upper() as a detector rather than a transformer
• CRM use cases that earn their keep
• Copy-paste formulas
• Gotchas
• Final thoughts

What lower() and upper() do

One argument each, text in and text out.

lower(value)
upper(value)

Attio's own examples:

lower("Hello World")   →   "hello world"
upper("Hello World")   →   "HELLO WORLD"

That is the whole API. Digits, punctuation and spaces pass through untouched, so lower("ENT-100") is "ent-100" and the hyphen and the number survive exactly as they were.

Set the output type to Text. Leaving it on Auto usually works, but an explicit type is what makes the column reliable to group by later, and grouping is most of why you are building it.

A formula attribute cannot clean the column it reads

This is the expectation to get out of the way first, because it is the reason people try lower() and conclude it did nothing.

A formula attribute in Attio is a computed column. It reads other attributes and writes to itself. It cannot write back into {Name}. So lower({Name}) does not turn ACME CORP into acme corp on the record — it creates a *second* column containing acme corp, while {Name} keeps shouting.

Once you stop reading that as a limitation it turns into the right mental model:

• The original attribute is for humans. It goes in emails, on the record header, in a report someone screenshots. Keep it properly cased.
• The normalised formula column is for machines. Nobody should look at it. Its whole job is to be compared, grouped, filtered and matched against another normalised column.

You are not cleaning data. You are building a match key beside the data. That distinction decides how aggressive you should be later — a match key can be mangled beyond readability, because readability was never its job.

Lower both sides or neither

The single most common bug with these functions is lowering one operand and forgetting the other.

lower({Name} ?? "") == {Company > Name} ?? ""

That is 13px] bg-[color:var(--color-bg-muted)] border border-[color:var(--color-border)] px-1.5 py-0.5 rounded">false on every record where the company name contains a capital letter, which is every record. And it fails the way the [startsWith() mistakes fail: quietly. There is no type error, no red squiggle, no empty column. The formula compiles, the checkbox fills in with false everywhere, and it looks exactly like a workspace where nothing matches.

The fix is trivial and the rule is worth making absolute:

lower({Name} ?? "") == lower({Company > Name} ?? "")

Wrapping a value that is already lowercase costs nothing and changes nothing, so there is no judgment call to make record by record. Every text comparison gets lower() on both sides, always. Treat it the way you treat ?? "" — not a fix you apply when you spot a problem, but a default you stop thinking about.

lower() belongs on the inside

Here is the ordering rule that saves the most rework, and it is not obvious from the function signature.

Suppose you want to strip the legal suffix off company names before comparing them. The instinct is to clean, then lowercase:

lower(replaceAll({Name} ?? "", " Ltd", ""))

That catches Acme Ltd. It misses Acme LTD, Acme ltd and ACME LTD, because replaceAll() is matching before the normalisation happens — and like every other text function in this library, it has no case-insensitivity flag to turn on. Nothing in Attio's formula language takes a match mode. There is no regex anywhere.

Put lower() innermost and every downstream operation inherits the normalisation for free:

replaceAll(lower({Name} ?? ""), " ltd", "")

Now the search term only ever has to handle one casing, because by the time replaceAll() sees the value there is only one casing left. The same applies to contains(), startsWith(), endsWith(), split() and splitPart() — wrap the *value* once, at the bottom, and write every literal in lowercase from then on.

Note the leading space in 13px] bg-[color:var(--color-bg-muted)] border border-[color:var(--color-border)] px-1.5 py-0.5 rounded">" ltd". That is the same boundary discipline as the [suffix trap: without it you will quietly maim Scaltd Systems. Put the separator inside the search term.

Case is only one of the ways text disagrees

lower() is a normaliser for exactly one dimension of difference. There are three, and Attio gives you a tool for one and a half of them.

Whitespace is the painful one, because Attio has no trim() function. A value imported with a trailing space is a different string from the same value without it, and lower() will faithfully preserve that space. Your only tool is replaceAll(value, " ", ""), which removes *every* space rather than just the ones at the ends. For a match key that is fine — you were never going to read it. For anything a human sees, it is not.

Accents are simply out of reach. lower() handles the casing of an accented character, but café and cafe are still two different strings, and folding one to the other would take a replaceAll() per character. If you have a European or Latin American company book, know that this class of near-duplicate survives normalisation.

That is worth saying plainly, because "we added lower() and our dedupe still misses things" is the predictable next complaint, and the reason is not that lower() is broken.

Building a match key

Put the pieces together and you get the column that actually earns its place: a deterministic key that collapses the cosmetic differences between two records of the same company.

Start with the basic version — lowercase, then strip spaces and the punctuation that legal names attract:

replaceAll(replaceAll(replaceAll(lower({Name} ?? ""), " ", ""), ".", ""), ",", "")

Three of four collapse. The fourth is the reason to go one step further and drop the legal suffix — and it is also where the boundary problem bites hardest.

The tempting move is to add replaceAll(..., "inc", "") at the end. Do not. By that point the spaces are gone, so "inc" matches anywhere in the string: Principal Financial becomes prpalfinancial, Vinci becomes vi, and you have invented collisions rather than removed them.

Strip the suffix before you collapse the spaces, with the space inside the search term:

replaceAll(replaceAll(replaceAll(replaceAll(lower({Name} ?? ""), " inc", ""), " ltd", ""), " ", ""), ".", "")

That is safe for Principal Financial and Vinci, and it still folds Acme Inc. and ACME, Inc together. It will also clip Acme Incorporated to acmeorporated, which is harmless as a key — both spellings of the same company land on the same string as long as you apply the identical formula on both objects — but it is the kind of thing that makes a match key unreadable, which is the point about readability from earlier.

If you want the strictly correct version, anchor on the end of the string and cut by length instead:

if(endsWith(lower({Name} ?? ""), " inc"),
   left(lower({Name} ?? ""), length({Name} ?? "") - 4),
   lower({Name} ?? ""))

That only removes inc when the name genuinely ends with it. It costs four extra lines per suffix, so in practice most workspaces run the replaceAll() chain and accept the mangling. Pick based on whether anything downstream ever reads the key.

The important discipline either way: build the same key on both objects with the same formula, then compare the keys. A match key compared against a raw value is not a match key.

upper() as a detector rather than a transformer

upper() gets used far less than lower(), and the reason is the human-readability point — nobody wants ACME CORP in a merge field. But it has one use that lower() cannot do as elegantly, and it is a genuinely good data-quality column:

{Name} == upper({Name} ?? "")

This is true only when the name is *already* entirely uppercase. It is a free detector for the records a legacy import shouted into your workspace, and it takes ten seconds to build. Flip it for the opposite problem:

{Name} == lower({Name} ?? "")

true means the name has no capital letters at all — usually a record someone typed in a hurry on a phone.

Two caveats. A value with no letters in it, like 123 Holdings reduced to digits, satisfies both tests, so do not treat either as a rich signal on its own. And an acronym-only name like IBM will honestly register as all-caps, because it is. Use the column to sort a cleanup queue, not to auto-reject records.

The other legitimate upper() case is codes with a canonical uppercase form — currency codes, country codes, state abbreviations, ticket prefixes. USD is correct and usd looks broken, so when the normalised value will be displayed rather than compared, upper() is the right normaliser:

upper({Currency code} ?? "")

CRM use cases that earn their keep

• Email domain matches the company domain. The check that catches a person attached to the wrong company after an import: lower(splitPart({Email addresses > Email address}[0] ?? "", "@", 1)) == lower({Company > Domains}[0] ?? "").
• A dedupe match key on Companies. The replaceAll() chain above, forced to Text, then group the view by it. Any group with more than one record is a duplicate cluster, and you did not export anything.
• Normalised domain for cross-source matching. Enrichment providers disagree about www. and protocol prefixes constantly: replaceAll(replaceAll(replaceAll(lower({Domains}[0] ?? ""), "https://", ""), "http://", ""), "www.", "").
• Case-insensitive tag or category checks. contains(lower({Tags} ?? ""), "saas") still works when the importer wrote SaaS, SAAS and Saas in three different rows.
• All-caps cleanup queue. {Name} == upper({Name} ?? ""), forced to Checkbox, filtered to true.
• Person name against company name for naming-convention QA: lower({Name} ?? "") == lower({Company > Name} ?? "") catches the records where someone pasted the company into the person's name field.

Copy-paste formulas

Case-insensitive comparison of two text attributes:

lower({Name} ?? "") == lower({Company > Name} ?? "")

Company match key, punctuation and spaces collapsed:

replaceAll(replaceAll(replaceAll(lower({Name} ?? ""), " ", ""), ".", ""), ",", "")

Company match key with the common legal suffixes stripped first:

replaceAll(replaceAll(replaceAll(replaceAll(lower({Name} ?? ""), " inc", ""), " ltd", ""), " ", ""), ".", "")

Normalised domain, protocol and www. removed:

replaceAll(replaceAll(replaceAll(lower({Domains}[0] ?? ""), "https://", ""), "http://", ""), "www.", "")

Person's email domain versus their company's domain:

lower(splitPart({Email addresses > Email address}[0] ?? "", "@", 1)) == lower({Company > Domains}[0] ?? "")

All-caps record detector:

{Name} == upper({Name} ?? "")

All-lowercase record detector:

{Name} == lower({Name} ?? "")

Canonical uppercase code for display:

upper({Currency code} ?? "")

Case-insensitive category check:

contains(lower({Categories} ?? ""), "saas")

Case-insensitive domain classification, combining today's function with yesterday's:

endsWith(lower({Domains}[0] ?? ""), ".edu")

Gotchas

• A formula attribute cannot rewrite the attribute it reads. lower({Name}) produces a new column; {Name} is untouched. If you need the source cleaned, that is an import or an API job, not a formula.
• Lower both sides of every comparison. One-sided lowering returns false everywhere and looks like real data, not a bug.
• Put lower() innermost. replaceAll(), contains(), startsWith() and endsWith() are all matching on the value as they receive it, and none of them takes a case-insensitivity option.
• Write every literal in lowercase once lower() is on the inside. A stray capital in a search term undoes the whole chain.
• There is no trim(). Leading and trailing whitespace survives lower() intact. replaceAll(value, " ", "") is the only whitespace tool, and it removes every space, not just the outer ones.
• Accents are not folded. café and cafe remain different strings after lower().
• Multi-value attributes are text[]. Email addresses, domains and phone numbers reject the text functions with *"Argument of type text[] attribute is not compatible with expected type (text)"*. Append [0] to the attribute reference, which targets the primary value.
• Blanks propagate. Guard at the attribute, not at the end: lower({Name} ?? ""), never lower({Name}) ?? "".
• Watch the boundary when stripping suffixes. Strip inc with its leading space and do it before you collapse spaces, or Principal Financial and Vinci get shredded.
• Build the identical key on both sides. A normalised value compared against a raw one matches nothing, and it is a hard bug to see because both columns look plausible.
• Referencing another formula attribute is unreliable. If you want to compare two match keys, re-derive the expression inline rather than pointing at the formula column you already built. Attio's documented ceiling is three formula attributes deep, but in practice chained references return wrong values with no error at all.

Final thoughts

lower() and upper() have no depth to them as functions. What they have is leverage: they are the layer that makes every other text comparison in Attio behave the way people already assumed it behaved.

The mental shift worth taking away is that you are not cleaning your CRM. You are building a second, ugly, machine-readable column beside the pretty one, and the ugly column is where matching, grouping and dedupe actually happen. Once that is the frame, the aggressive replaceAll() chains stop feeling like vandalism and start feeling like what they are — a key.

And be honest with yourself about the ceiling. Case is one of three ways the same company appears twice in your workspace, and it is the only one Attio normalises cleanly. If your duplicates are mostly punctuation and legal suffixes, a match key will find them. If they are mostly accents, abbreviations and genuinely different trading names, no formula is going to save you, and you need a proper dedupe pass instead.

Tomorrow: left() and right(), the fixed-length slicers — and the honest way to cut a suffix off a string when you know exactly how long it is.

Want this kind of layer built into your workspace rather than assembled a column at a time? Our AI-native Attio sprint covers the schema, the formulas and the dedupe pass together.