Calculations troubleshooting

Calculations work well for single-value math, text, dates, and conditional logic against a single record or a related set of records. A handful of shapes and operations behave differently than people expect — especially folks coming from spreadsheets or SQL. This page collects the most common gotchas, the errors you’re likely to see, and the recommended path forward for each. If you’re here because of a specific error, jump to the Error reference.

This page reflects current behavior. Where a section notes that something isn’t supported, it may change in a future release — the section will say so when that’s the case.

Multi-value and array-shaped values

The most common source of confusion. Two related shapes:

Multi-picklist fields — fields whose type is MULTI_PICKLIST. A single cell holds several selected options.
Multi-value text columns surfaced through Data Mine — when a Data Mine trigger fires, multi-value text columns in the payload serialize as a JSON array of objects, not as plain text. The shape looks like this:

[
  { "value": "WD", "type": "TEXT" },
  { "value": "ZIP", "type": "TEXT" }
]

Both shapes are lists of values, and almost every calculation function expects a single value at a time. The two limitations below cover what that means in practice.

If you’re working with a multi-picklist field and want the working patterns for iteration, aggregation, and passing selections into an API body, see Multi-value fields. This section covers what specifically breaks inside a calculation.

Aggregating a multi-picklist into a delimited string

Not currently supported. Aggregations over list-typed fields aren’t supported in calculations. What people try:

STRING_AGG_UNIQUE(CLAI."Claim Edit(s)", ',')

What they get:

Invalid Type Error at line 1, position 18: MULTI_PICKLIST

STRING_AGG and STRING_AGG_UNIQUE only accept single-value text supplied via a related-field aggregation — they cannot operate on a MULTI_PICKLIST field directly. There is no calculation function that aggregates the selections of a multi-picklist into a delimited string. What works instead. Do the aggregation in an Execute Script action and, if the result needs to live on a record, write it back with Update Record Fields. The full pattern (input mapping, iteration, aggregation, and passing the result into a Send API Request body) is on Multi-value fields.

Flattening a Data Mine array payload into plain text

Not currently supported. Calculations cannot convert structures — the [{"value":..., "type":...}, ...] shape that Data Mine produces for multi-value text columns cannot be flattened into plain delimited text inside a calculated column or the field mapping on a Create Record action. What happens if you try: the destination text field stores the literal JSON, for example:

[{"value":"WD","type":"TEXT"},{"value":"ZIP","type":"TEXT"}]

…rather than the friendly WD, ZIP users expect.

SPLIT returns an array you can’t index into

SPLIT(text, delimiter) returns an array of substrings. That’s expected — but selecting a single element from that array isn’t supported in calculations. There’s no array indexing syntax ([0], [1], etc.) and no INDEX/NTH function to pick a piece. Example of the trap:

// Returns the array ['TPFN', '123'] — but you can't extract 'TPFN' from it.
SPLIT('TPFN-123', '-')

Recommended patterns:

If the position is genuinely fixed (for example, every ID is always exactly 4 characters), use LEFT, RIGHT, or MID. These return text you can use directly.
If the split point is variable (for example, “everything before the dash”), combine FIND or SEARCH to locate the delimiter with LEFT/MID to extract the piece you want:

// 'Everything before the first dash' in CONTRACTS."Reference"
LEFT(CONTRACTS."Reference", FIND('-', CONTRACTS."Reference") - 1)

The LEFT/RIGHT/MID pattern only stays correct while the position is truly fixed. If the value’s length can change — for example, IDs growing from 99 to 100 add a character — the calculation will silently start returning the wrong substring without raising an error. For anything length-variable, use the FIND/SEARCH + LEFT/MID pattern instead.

DATEVALUE only accepts year-month-day input

DATEVALUE(text_date) doesn’t auto-detect or parse arbitrary date formats. The input must be in YYYY-MM-DD form — date only, no time component. Other formats won’t parse and the function returns blank, including:

US-style MM/DD/YYYY or European DD/MM/YYYY
Written-out months like Jun 1, 2026
ISO 8601 strings with a time component like 2026-06-01 12:34:56 or 2026-06-01T12:34:56Z

Recommended patterns:

Normalize date text to YYYY-MM-DD in the source system or an ingest step before it reaches the calculation.
If the components are available as separate values, skip DATEVALUE entirely and build the date with DATE(year, month, day).
For text dates with predictable structure, use LEFT/MID/RIGHT to rearrange the pieces into year-month-day before passing to DATEVALUE.

Regex syntax: Java-style

REGEXEXTRACT, REGEXMATCH, and REGEXREPLACE are fully supported. Standard regex operations — character classes, quantifiers, groups, anchors, alternation, backreferences — work as expected.

REGEXEXTRACT returns the first match only — not a list of matches. There is no built-in way to extract every match in one call.

REGEXREPLACE replaces all matches of the pattern in the string, not just the first.

Patterns follow Java-style regex syntax because of the backend implementation. If you’re porting patterns from JavaScript, Python, or PCRE-flavored tools, most expressions work unchanged, but watch for the differences that bite:

Escaping inside string literals — calculation text is enclosed in single quotes, so backslashes in patterns need to be escaped: write '\\d+' (not '\d+') to match one or more digits.
A few advanced constructs — possessive quantifiers (*+, ++) and certain Unicode property classes behave per the Java spec, not PCRE.

The Java Pattern javadoc is the authoritative syntax reference. When in doubt, test the pattern with a small string against REGEXMATCH before building a larger calculation around it.

Adding or subtracting days from a date

Use DATEADD(unit, value, date). Unit tokens are unquoted, and a negative value subtracts:

// Today + 14 days
DATEADD(DAY, 14, NOW())

// A due date minus 3 days
DATEADD(DAY, -3, TASKS."DueDate")

// One month before a due date
DATEADD(MONTH, -1, TASKS."DueDate")

For “is this date yesterday/today/tomorrow” comparisons, use DATEDIF against NOW() with the 'D' unit:

// TRUE when the field's date is exactly yesterday (FALSE for today)
DATEDIF(YOUR_ELEMENT."DateField", NOW(), 'D') = 1

IF branches must return the same type

Both branches of IF(condition, value_if_true, value_if_false) must return the same data type. Mixing types raises a validation error and the calculation won’t save. A common trip-up is using '' (empty string) as a placeholder in the false branch of an otherwise date-typed expression:

// Incorrect — the true branch returns a date, the false branch returns text
IF(ISBLANK(ACQ."Close Date"), '', ACQ."Close Date" + 30)

Use BLANK() instead. It represents the absence of a value without changing the branch’s type, so both sides stay date-typed:

// Correct — both branches are date-typed
IF(ISBLANK(ACQ."Close Date"), BLANK(), ACQ."Close Date" + 30)

The same rule applies for numeric and text branches — use BLANK() (not 0 or '') when one branch has no meaningful value.

Detecting missing values, including the literal text ‘null’

ISBLANK(field) detects when a field has no value at all. Elementum normalizes empty text and true nulls the same way at the field level — ISBLANK returns TRUE for both. In practice you don’t need a separate check for “null vs. blank” on a field. What ISBLANK does not catch is a text field whose content is the four characters n, u, l, l. This shows up when the field was populated by an upstream system that writes the string null as a marker for missing data — API responses, webhook payloads, and some CSV exports do this. From the calculation’s perspective the field holds a non-empty text value, so ISBLANK returns FALSE. To detect that literal string, compare against the quoted text:

// TRUE when the field's text content is the four characters n-u-l-l
SOURCE."StatusAsOfDate" = 'null'

To cover both shapes in one condition, combine the checks with OR:

// TRUE when the field is empty OR contains the text 'null'
OR(ISBLANK(SOURCE."StatusAsOfDate"), SOURCE."StatusAsOfDate" = 'null')

null is not a keyword in the calculation language. Writing null bare (unquoted) raises a syntax error like Invalid Syntax Error at line 1, position N: missing ')' at ','. Use BLANK() to return an empty value from a branch of an IF, and 'null' (quoted) to compare against the literal text.

Worked example: text-to-date, empty when the source is ‘null’

Convert a text field to a date, and return blank — not today’s date — when the source is empty or contains the string null:

IF(
  OR(ISBLANK(SOURCE."StatusAsOfDate"), SOURCE."StatusAsOfDate" = 'null'),
  BLANK(),
  DATEVALUE(SOURCE."StatusAsOfDate")
)

A few details worth calling out:

Both branches must be date-typed. BLANK() on the true branch takes the surrounding branch’s type — '' (empty string) is text-typed and would raise a validation error. See IF branches must return the same type.
DATEVALUE only parses YYYY-MM-DD. If the source date is in another format, DATEVALUE returns blank silently rather than the value you expect — see DATEVALUE only accepts year-month-day input.
IF takes exactly three arguments — condition, value if true, value if false. Nested checks belong inside the condition (as with OR above), not as extra positional arguments in the outer IF(...).

Inserting text into the middle of a string

There is no dedicated “insert” function. Split the string at the target position with LEFT and RIGHT, then reassemble it with CONCAT:

// Insert " Sorry" after the first 11 characters of "Hello world this is ME"
CONCAT(
  LEFT(SOURCE."Text", 11),
  ' Sorry',
  RIGHT(SOURCE."Text", LEN(SOURCE."Text") - 11)
)
// Result: "Hello world Sorry this is ME"

When the insertion point isn’t at a fixed offset, use FIND to locate the anchor dynamically:

// Insert " Sorry" immediately after the word "world"
CONCAT(
  LEFT(SOURCE."Text", FIND('world', SOURCE."Text") + 4),
  ' Sorry',
  RIGHT(SOURCE."Text", LEN(SOURCE."Text") - (FIND('world', SOURCE."Text") + 4))
)

SUBSTITUTE(text, old_text, new_text) is a different tool — it replaces every match of old_text. It only produces an “insert” when you replace an anchor with itself plus the new text (for example, SUBSTITUTE(text, 'world', 'world Sorry')), which requires a unique anchor and rewrites every occurrence.

FIND is case-sensitive and returns the position of the first match. If the anchor could appear more than once in the source text, this pattern inserts before the first occurrence only.

Worked example: building an HTML email body from records

A common driver for this pattern is assembling an HTML email body from a list of records — the outer template is fixed, and you need to drop <tr> rows into the <tbody> before sending. The template looks like this:

<table>
  <thead>...</thead>
  <tbody>
  </tbody>
</table>

Given a template value holding that markup and a new_rows value holding the row markup to insert — for example:

<tr><td>Jane Doe</td><td>Paid</td></tr>

split the template around </tbody> and stitch the rows in front of it:

CONCAT(
  LEFT(template, FIND('</tbody>', template) - 1),
  new_rows,
  RIGHT(template, LEN(template) - FIND('</tbody>', template) + 1)
)

What each piece does:

FIND('</tbody>', template) locates the character position where the closing tag starts.
LEFT(template, FIND(...) - 1) returns everything in the template before the closing tag.
RIGHT(template, LEN(template) - FIND(...) + 1) returns the closing tag and everything after it, so the tag itself is preserved.
CONCAT(...) glues the three pieces back together in order — before, rows, after.

Build multi-row markup as its own calculation first. When rows come from several related records (one row per line item, for example), aggregate the row markup in its own calculation and pass the result into the insert formula as new_rows. Nesting the aggregation and the insertion in a single expression is hard to read and hard to debug.

The same pattern in an Execute Script action

If your automation already uses an Execute Script action for other logic, JavaScript’s String.prototype.replace is shorter and avoids off-by-one arithmetic with character positions. Replace the marker with the new content followed by the marker itself:

const { template, newRows } = input.parameters;

const result = template.replace('</tbody>', `${newRows}</tbody>`);

return { emailBody: result };

When rows come from a list of records, build the row markup with .map(...).join('') and insert once:

const { template, records } = input.parameters;

const rowsHtml = records
  .map(r => `<tr><td>${r.name}</td><td>${r.status}</td></tr>`)
  .join('');

const result = template.replace('</tbody>', `${rowsHtml}</tbody>`);

return { emailBody: result };

Two failure modes worth checking if the script raises Cannot read property 'replace' of undefined:

records must be the array of record data, not a single record or an unresolved reference. Access it via input.parameters — parameter names are not injected as standalone variables in the script scope (see Execute Script).
template must be a string at the point you call .replace() on it. A field reference that hasn’t loaded yet resolves to undefined.

For more than one insertion point in the same template (for example, rows inside <tbody> and a summary line before </table>), chain .replace() calls — one per marker.

Pick one tool per insertion and stay there. If the rest of your workflow already lives in calculations, use the LEFT/FIND/CONCAT/RIGHT pattern. If it already lives in an Execute Script action, use .replace(). Bouncing between a calculation, a Run Calculation action, and an Execute Script for the same string-assembly job adds surface area for bugs.

Concatenating strings

CONCAT(a, b, c, ...) is the function to join text values. The + operator is for numeric addition only — applying it to text fields does not concatenate them. This is a common surprise for people coming from JavaScript, PHP, or some spreadsheet languages where + doubles as a string-concatenation operator.

// Correct — produces 'Jane Doe'
CONCAT(CUSTOMERS."FirstName", ' ', CUSTOMERS."LastName")

// Incorrect — '+' is numeric addition only; this does not concatenate text
CUSTOMERS."FirstName" + ' ' + CUSTOMERS."LastName"

CONCAT accepts any number of arguments and converts non-text values (numbers, dates) to text automatically before joining.

Changing a column’s field type

Not supported. A column’s field type cannot be changed in place after the column is created. This applies to all type conversions — single-value text ↔ multi-value text, text ↔ multi-picklist, and so on. Recommended pattern — create a new column of the desired type, then delete the old one. If the old column has data you need to preserve, backfill the new column via an automation or import before deleting the old, and update any dependencies (calculations, automations, filters, layouts) to point at the new column.

Error reference

Error you see	What it means	Where to go
`Invalid Type Error at line N, position M: MULTI_PICKLIST`	A calculation function received a `MULTI_PICKLIST` field where it expected a single value.	Aggregating a multi-picklist into a delimited string
Destination field stores literal JSON like `[{"value":"...","type":"TEXT"}, ...]`	A Data Mine multi-value text payload was passed to a plain-text destination without being flattened.	Flattening a Data Mine array payload into plain text
`DATEVALUE` returns blank for a date that “looks valid”	The input isn’t in year-month-day form.	DATEVALUE only accepts year-month-day input
Expected to pull “the first piece” out of a `SPLIT` result and got nothing usable	`SPLIT` returns an array, but array indexing isn’t supported.	SPLIT returns an array you can’t index into
Validation error on an `IF` where one branch is `''` and the other is a date or number	Both branches of `IF` must return the same type; `''` is a text literal and won’t type-match a date or numeric branch.	IF branches must return the same type
`Invalid Syntax Error at line N, position M: missing ')' at ','` on an `IF` that references `null`	`null` isn’t a keyword in the calculation language. Use `BLANK()` to return an empty value, or `'null'` (quoted) to compare against the literal text.	Detecting missing values, including the literal text ‘null’
`ISBLANK(field)` returns FALSE for a field that looks empty	The field contains the four-character text `null` (typically from an upstream system that writes `null` as a marker), not a true empty value.	Detecting missing values, including the literal text ‘null’

Calculations — Full function reference
Tables — Calculated columns on tables
Data mining — Triggers and payload shapes

Data Management

Structuring

Reporting

Multi-value and array-shaped values

Aggregating a multi-picklist into a delimited string

Flattening a Data Mine array payload into plain text

SPLIT returns an array you can’t index into

DATEVALUE only accepts year-month-day input

Regex syntax: Java-style

Adding or subtracting days from a date

IF branches must return the same type

Detecting missing values, including the literal text ‘null’

Worked example: text-to-date, empty when the source is ‘null’

Inserting text into the middle of a string

Worked example: building an HTML email body from records

The same pattern in an Execute Script action

Concatenating strings

Changing a column’s field type

Error reference

​Multi-value and array-shaped values

​Aggregating a multi-picklist into a delimited string

​Flattening a Data Mine array payload into plain text

​SPLIT returns an array you can’t index into

​DATEVALUE only accepts year-month-day input

​Regex syntax: Java-style

​Adding or subtracting days from a date

​IF branches must return the same type

​Detecting missing values, including the literal text ‘null’

​Worked example: text-to-date, empty when the source is ‘null’

​Inserting text into the middle of a string

​Worked example: building an HTML email body from records

​The same pattern in an Execute Script action

​Concatenating strings

​Changing a column’s field type

​Error reference

​Related documentation

Multi-value and array-shaped values

Aggregating a multi-picklist into a delimited string

Flattening a Data Mine array payload into plain text

SPLIT returns an array you can’t index into

DATEVALUE only accepts year-month-day input

Regex syntax: Java-style

Adding or subtracting days from a date

IF branches must return the same type

Detecting missing values, including the literal text ‘null’

Worked example: text-to-date, empty when the source is ‘null’

Inserting text into the middle of a string

Worked example: building an HTML email body from records

The same pattern in an Execute Script action

Concatenating strings

Changing a column’s field type

Error reference

Related documentation