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

# Calculations troubleshooting

> Common calculation gotchas—multi-value fields, array shapes, regex syntax, date parsing—with the recommended pattern for each.

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](#error-reference).

<Info>
  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.
</Info>

## 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](/data/data-mining) 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:

```json theme={null}
[
  { "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.

<Info>
  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](/data/multi-value-fields). This section covers what specifically breaks inside a calculation.
</Info>

### Aggregating a multi-picklist into a delimited string

**Not currently supported.** Aggregations over list-typed fields aren't supported in calculations.

**What people try:**

```javascript theme={null}
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](/workflows/automation-actions-reference#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](/data/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:**

```javascript theme={null}
// 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:

```javascript theme={null}
// 'Everything before the first dash' in CONTRACTS."Reference"
LEFT(CONTRACTS."Reference", FIND('-', CONTRACTS."Reference") - 1)
```

<Warning>
  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.
</Warning>

## 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)`](/data/calculations#date-and-time-functions).
* 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.

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

<Info>
  **`REGEXREPLACE` replaces all matches** of the pattern in the string, not just the first.
</Info>

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](https://docs.oracle.com/en/java/javase/17/docs/api/java.base/java/util/regex/Pattern.html) 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)`](/data/calculations#dateadd---add-or-subtract-time-from-a-date). Unit tokens are unquoted, and a negative value subtracts:

```javascript theme={null}
// 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`](/data/calculations#date-and-time-functions) against `NOW()` with the `'D'` unit:

```javascript theme={null}
// 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:

```javascript theme={null}
// Incorrect — the true branch returns a date, the false branch returns text
IF(ISBLANK(ACQ."Close Date"), '', ACQ."Close Date" + 30)
```

Use [`BLANK()`](/data/calculations#blank---return-blank-value) instead. It represents the absence of a value without changing the branch's type, so both sides stay date-typed:

```javascript theme={null}
// 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:

```javascript theme={null}
// 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`:

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

<Note>
  `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()`](/data/calculations#blank---return-blank-value) to *return* an empty value from a branch of an `IF`, and `'null'` (quoted) to compare against the literal text.
</Note>

### 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`:

```javascript theme={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](#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](#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`](/data/calculations#concat---join-text-together):

```javascript theme={null}
// 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`](/data/calculations#find---find-text-position-case-sensitive) to locate the anchor dynamically:

```javascript theme={null}
// 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.

<Info>
  `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.
</Info>

### 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:

```html theme={null}
<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:

```html theme={null}
<tr><td>Jane Doe</td><td>Paid</td></tr>
```

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

```javascript theme={null}
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](/workflows/automation-actions-reference#data-actions) 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:

```javascript theme={null}
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:

```javascript theme={null}
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](/workflows/automation-actions-reference#data-actions)).
* `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.

<Tip>
  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.
</Tip>

## 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.

```javascript theme={null}
// 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.

### Adding a newline between concatenated values

Calculations do not expose a `CHAR`, `CHR`, or newline function, and escape sequences like `'\n'` inside a string literal are treated as the two characters `\` and `n`, not as a line break. There is no supported way to insert a raw newline character inside a `CONCAT` expression.

If you need multi-line output — for example, an address block or a message body where each value sits on its own line — use an [Execute Script](/workflows/automation-actions-reference#data-actions) action instead. JavaScript's `\n` escape does produce a newline:

```javascript theme={null}
const { line1, line2, line3 } = input.parameters;

return {
  result: `${line1}\n${line2}\n${line3}`,
};
```

Pass the values as inputs to the action, then reference the `result` output in downstream steps (for example, an Update Record action writing to a multi-line text field, or a Send Email action).

<Note>
  Whether the newline is preserved when the value is displayed depends on the destination. Multi-line text fields, email bodies, and most external systems render `\n` as a line break. Single-line text fields and some UI surfaces collapse whitespace.
</Note>

## 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](#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](#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](#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](#split-returns-an-array-you-cant-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](#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'](#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'](#detecting-missing-values-including-the-literal-text-null) |

## Related documentation

* **[Calculations](/data/calculations)** — Full function reference
* **[Tables](/data/tables)** — Calculated columns on tables
* **[Data mining](/data/data-mining)** — Triggers and payload shapes