---
title: Formula fields | Lightfield
description: Create fields that calculate their value from other fields and related records, and fix formulas that come out blank or wrong.
---

A formula field calculates its value from other fields and stays up to date. You can filter, sort, search and chart its results, or use changes as automation triggers.

For every operator and function, see the [formula function reference](/help/settings/data-model/formula-functions/index.md).

## Who can use it

Formula fields are on Enterprise. Only owners and admins create or edit them; everyone sees the results.

Every plan can store numbers in **Number** and **Currency** fields, keep them filled with [AI fill](/help/settings/data-model/#let-ai-fill-your-fields/index.md), and ask the agent in chat to work out a total or ratio across records. To add formula fields, an admin changes the plan in [Settings → Billing](https://crm.lightfield.app/crm/settings/billing/) or contacts Lightfield.

Earlier plans can differ. [Check your workspace’s available features](/help/missing-feature/index.md).

## Create a formula field

You can’t change a field’s type or output type after you create it, and you can’t turn an existing field into a formula field. To change either, create a new formula field.

1. Go to [Settings → Data model](https://crm.lightfield.app/crm/settings/data-model/) and open an object.
2. On the **Fields** tab, click **Create field**.
3. Enter a **Name** and, optionally, a **Description** of what it calculates.
4. Set **Type** to **Formula** and write the calculation under **Formula**.
5. Check **Output type**. Lightfield sets it from your formula; the choices are **Number**, **Currency**, **Text**, **Checkbox** and **Date & Time**.
6. Fix anything the editor underlines, then click **Create**.

The field shows on the **Fields** tab with the type **Formula**. Lightfield then calculates it for existing records in the background, so it can look blank for a few minutes.

Admins can also click **Create with agent**. The agent writes the formula and shows it for your approval first.

Formula results are read-only in the app, API, chat and AI fill. Change an input or the formula to change a result. Fields used by a formula cannot be archived or deleted.

### Example: sort records into tiers

Formulas can’t output a Single select. To put records into bands, use a **Text** output with `case`:

```
case(
  {ARR} >= 100000, "Enterprise",
  {ARR} >= 25000,  "Mid-market",
  "SMB"
)
```

## Reference fields

Type `{` to pick a field, or search fields and functions in **Variables**. A reference, such as `{Discovery score}`, is one token; Backspace beside it removes the whole reference.

A formula can read **Checkbox**, **Number**, **Currency**, **Text**, **Date & Time** and **Single select** fields, plus built-in fields such as **Created at**, **Last interaction**, **Next interaction** and **Interaction count**. Multi select fields are not supported as formula inputs. For a one-time calculation instead, ask the agent in chat. The editor identifies unsupported input types.

- **Single select needs `.label` or `.id`.** `{Stage.label}` is the text you see, such as `Closed Won`. `{Stage.id}` is the stored option ID. Use `.id` to match one specific option, because a label can be renamed later.
- **Another formula field.** Break a complex calculation into steps, such as one field for a total and another for its band. Chains are limited (see [Limits](#limits)), and formulas can’t reference each other in a loop.
- **A related record.** A formula can read a field on a directly related record, such as the unit price on an opportunity’s product, but not follow a second relationship from there.

The result shows on the record that holds the formula, so anyone who can see that record sees it, even if they can’t open the related record it came from. Check access before you reference sensitive data.

## Total related records with a rollup

A rollup summarizes a field across every related record, such as the total opportunity amount on an account. To insert one, open the relationship in **Variables** and pick the field.

```
sum({Opportunities.Amount})
count({Contacts})
count({Opportunities.Amount})
```

`sum`, `avg`, `min`, `max`, `count`, `concat`, `join` and `coalesce` can roll up. `count({Contacts})` counts every linked contact; `count({Opportunities.Amount})` counts only opportunities with an amount.

Rollups skip blank related values instead of making the result blank. With no related records, `sum` returns 0 and `min`, `max` and `avg` return blank.

Know these limits before you build one:

- **No filtering.** A rollup covers every active related record, so you can’t total only closed-won opportunities.
- **One relationship, from the “one” side.** An account can total its opportunities; an opportunity reads its account’s field directly instead. Many-to-many relationships roll up from either side. Relationships that can point at more than one kind of record can’t roll up.
- **The reference goes straight inside.** `sum({Opportunities.Amount}) / {Target}` works; a calculation inside the function doesn’t.
- **Types must match.** A Currency field rolls up into a Currency formula in the same currency; `count` always returns a Number.

## How blank values work

- **A blank input gives a blank result.** `sum({Q1}, {Q2}, {Q3}, {Q4})` is blank whenever any quarter is. To count blanks as zero, wrap each: `coalesce({Q1}, 0)`.
- **Comparing with a blank gives a blank.** If `{Amount}` is blank, `{Amount} > 1000` is blank, not false. Check first: `isSet({Amount}) && {Amount} > 1000`.
- **Empty text counts as blank.** `0` and an unchecked checkbox don’t.
- **`concat` treats a blank as empty text**, so `concat({First name}, " ", {Last name})` still works when one name is missing.
- **Conditions must be true or false.** If a condition is blank, `if` uses its otherwise value and `case` moves on.

Decide for each field you reference whether a blank means zero or unknown before you save.

## Currency

A currency value carries its currency, and Lightfield never converts on its own. Combining two currencies is an error and leaves the field blank. Convert first with `convertCurrency(value, "USD", rate)`, at a rate you write into the formula. A plain number takes the currency it’s combined with, so `{Amount} * 0.15` stays in the amount’s currency.

## Limits

| Limit                               | Maximum      |
| ----------------------------------- | ------------ |
| Formula fields per object           | 50           |
| Formula fields in a reference chain | 5            |
| Expression size                     | 200 elements |
| Related records per rollup          | 10,000       |

Formulas can’t use the current date or time (there’s no `today()`) or turn a number into text. Results are stored and recalculated in the background, so a change, especially to a record many others point at, can take a moment to show.

## Troubleshooting

### The field is blank on some records

Check each input on a blank record and decide how the formula should handle missing values. Other causes: a comparison with a blank, dividing by zero (guard it with `if({Total} != 0, {Won} / {Total}, 0)`), or a rollup over more than 10,000 related records.

### The field is blank on every record

A new or edited formula, or a large import, may still be calculating. Wait a few minutes. If it’s still blank, a field it reads may be empty everywhere (often an unmapped import column), or the formula mixes two currencies.

### The value looks wrong

- **A rollup total looks low.** Rollups skip blank related values.
- **A zero appears where you expected nothing.** `coalesce({Field}, 0)` turns every unknown into a real zero, in the field and in every report that reads it.

### The formula can’t be saved

| Message                                                                        | Fix                                                                                                      |
| ------------------------------------------------------------------------------ | -------------------------------------------------------------------------------------------------------- |
| ”… is a single-select field, so a reference to it must say which part to read” | Use `{Field.label}` or `{Field.id}`.                                                                     |
| ”Only single-select fields have `.label` and `.id`.”                           | Reference the field as `{Field}`.                                                                        |
| ”… fields cannot be used in a formula yet.”                                    | Pick a field of a supported type.                                                                        |
| ”… takes … arguments, but got …”                                               | Match the function’s arguments in the [reference](/help/settings/data-model/formula-functions/index.md). |
| ”Expected `)`.”                                                                | Add the missing closing parenthesis.                                                                     |
| ”This type cannot be calculated.”                                              | Choose Number, Currency, Text, Checkbox or Date & Time as the output type.                               |
| ”Formula is required.”                                                         | Enter a formula before saving.                                                                           |

## Common questions

**Why don’t I see Formula as a field type?** Your plan doesn’t include formula fields, or you aren’t an owner or admin. They’re on Enterprise.

**Can I type over a formula result?** No. Formula fields are read-only everywhere. Change a field the formula reads, or edit the formula.

**Can a formula use today’s date?** No. Formulas only read stored values. To count days between two stored dates, use `dateDiff`; see the [function reference](/help/settings/data-model/formula-functions/#dates/index.md).

**Why is my new formula field empty?** Lightfield may still be calculating it for existing records. Wait a few minutes; if it’s still blank, see [The field is blank on every record](#the-field-is-blank-on-every-record).