Using formula fields

Formula fields automatically calculate values using data from other fields. Instead of entering a value manually, you define a formula and Lightfield keeps the result up to date when its inputs change.

For a complete list of supported operators and functions, see the 📄 Formula function reference.

What you can do with formula fields

Once calculated, a formula value behaves like other field values. You can:

• Filter and sort by it

• Search it

• Use it in reports and charts

• Use changes to it to trigger automations

Formula fields are read-only. They can't be edited from a record, table, bulk edit, API, the chat assistant, or AI fill. To change a formula's result, update one of the fields it references or edit the formula itself.

Before you begin

Only admins can create or edit formula fields, and formula fields must be enabled for your workspace. If Formula doesn't appear in the field type list, contact your Lightfield representative.

Create a formula field

1. Go to Settings → Data model.

2. Select an object, such as Accounts, Contacts, Opportunities, or a custom object.

3. Open the Fields tab and click Create field.

4. Enter the field details:

   • Name: The name shown on records.

   • Description: An optional explanation of what the field calculates.

   • Type: Select Formula.

   • Output type: Select Number, Currency, Text, Checkbox, or Date. Single-select outputs aren't currently supported.

   • Formula: Enter the calculation in the formula editor.

5. Check the editor for errors before finalizing.

6. Save the field.

A blank input makes the whole result blank. If any field a formula references is empty on a record, that record's result is empty too. Lightfield doesn't treat a blank number as zero. An account missing Q3 gets no annual total at all, so the same formula can look correct on most records and blank on the rest.

Before you save, decide what a blank should mean for each field you reference. Where blank should count as zero, wrap it: coalesce({Q3}, 0). Where blank means "we don't know yet," leaving the result blank is the right answer. See How blank values work.

Important: You can't change a field's type or output type after creating it, and you can't convert an existing field into a formula field. To make either change, create a new formula field.

Sorting records into tiers

Single-select outputs aren't currently supported. Use a Text output with case(...) instead:

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

You can also ask the agent to create or edit a formula field for you. It can write and change the formula.

What happens after you save

After you save, Lightfield recalculates the formula across existing records in the background.

You can't archive or delete a field while a formula depends on it. Lightfield blocks the action and identifies the formula fields that use it.

Reference fields in a formula

You can add a field reference in two ways:

• Type { to open a list of available fields.

• Click Variables to open a searchable list with a description of each field.

The reference appears as a single field token, such as {Discovery score}. Use the arrow keys to move the cursor over a token. Press Backspace next to it to remove the entire reference.

Supported field types

You can reference custom fields with the following types:

• Checkbox

• Number

• Currency

• Text

• Date

• Single select

Single-select fields need .label or .id

A single select stores a hidden option ID separately from the label you see, so a formula has to say which one it means. Each single-select field appears in the picker as two entries:

• {Stage.label} — the text shown in the UI, such as Closed Won

• {Stage.id} — the stored option ID

The accessor sits inside the braces. Use .label to match what you see in the field; use .id when comparing against a specific option, since labels can be renamed later while IDs stay fixed.

Reference another formula field

A formula field can reference another formula field. This can make complex calculations easier to understand — for example, one field can calculate a total and another can assign that total to a range.

A chain can include up to five formula fields. Formula fields can't reference each other in a loop. If a formula creates a loop, Lightfield identifies the cycle and prevents you from saving it.

Reference a related record

A formula can reference a field on a directly related record. For example, an opportunity's total price could multiply its number of seats by the unit price stored on a related product.

A formula can reference the record it's on and a directly related record, but it can't follow another relationship from there.

Review access before referencing sensitive data. The result appears on the record containing the formula field. Anyone who can view that record can see the result, even if they can't access the related record it came from.

Summarize related records with a rollup

A formula can summarize a field across every related record — for example, total opportunity value on an account, or the average deal size for a customer.

Use one of these functions with a single related-record reference:

Insert the reference from the Variables panel: open the relationship, then pick the field you want to summarize. The panel describes these as "The [field] of every [related record] linked to this one."

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

How rollups handle blanks

Rollups behave differently from the rest of the formula language:

• Blank related values are skipped, not propagated. sum and avg ignore related records where the field is empty, rather than returning blank. This is the opposite of how blanks work within a single record.

• count counts only related records that have a value in the referenced field — not every related record.

• A rollup over no related records returns 0 for sum. min, max, and avg return blank.

Rollup restrictions

• No filtering. A rollup covers every active related record. You can't roll up "only closed-won opportunities."

• One relationship only. A rollup can't follow a second relationship from the related record.

• Direction matters. On a one-to-many relationship, only the "one" side can roll up — an account can total a field across its contacts, but a contact can't roll up across that relationship. (From the contact's side there's only one account, so reference it directly instead of rolling it up.) Many-to-many relationships can be rolled up from either side. Opportunities can be rolled up onto an account, but not the reverse.

• One reference per function. The related-record reference must be the only argument, used directly inside the rollup function. You can't nest a calculation inside it, or combine two rollups in one function call. You can use the result of a rollup in a wider formula — for example, sum({Opportunities.Amount}) / {Target}.

• Currency must match. If the related field is a Currency field, the formula field must also be Currency and use the same currency code — including for avg. If the related field is a Number, the output must be Number. count always returns a Number.

• Some relationships aren't eligible. Relationships that can point at more than one kind of record can't be rolled up.

• 10,000 related records per rollup. If a record has more related records than that, the rollup can't calculate and the field is left empty.

How blank values work

Formula fields handle blank values differently from spreadsheets. Review these behaviors before building a complex calculation.

A blank input usually produces a blank result

If a calculation references a blank field, the entire result is blank. Lightfield doesn't treat blank values as zero.

For example, this formula returns blank if any quarter is blank:

sum({Q1}, {Q2}, {Q3}, {Q4})

Use coalesce to replace blank values with zero:

sum(coalesce({Q1}, 0), coalesce({Q2}, 0), coalesce({Q3}, 0), coalesce({Q4}, 0))

A comparison with a blank value returns blank

If {Amount} is blank, {Amount} > 1000 returns blank rather than false. This also applies to == and !=. For example, {Region} != "EMEA" isn't true when Region is blank.

Use isSet or isBlank when you need a true-or-false result:

isSet({Amount}) && {Amount} > 1000

Empty text also counts as blank

isBlank returns true for both an empty field and an empty text value. It returns false for 0 and an unchecked checkbox because those are valid values. coalesce follows the same rule.

concat treats blank as empty text and is the exception to blank propagation; example simplified to concat({First name}, " ", {Last name}), plus the all-blank case returning empty text.

concat(coalesce({First name}, ""), " ", coalesce({Last name}, ""))

Conditions must return true or false

Conditions must evaluate to true or false; text and numbers aren't treated as “truthy.” If a condition is blank, if uses its otherwise branch and case moves to the next condition.

Currency

Currency values carry their currency with them, and Lightfield does not convert between currencies. Combining amounts in different currencies is an error and leaves the field blank. Convert to a single currency upstream if you need to total across regions.

Plain numbers take on whichever currency they're combined with, so {Amount} * 0.15 stays in the amount's currency.

Recalculation

Formula results are calculated in the background and stored on each record. They aren't recalculated each time you view them, so updates may take a short time to appear.

If a new formula field is blank across all records, it may still be backfilling.

When a related record changes, rollups and lookups that depend on it recalculate. If many records reference the same related record, recalculation may take longer than it does for an ordinary field edit.

Limits

Not currently supported

• Filtered rollups: Summarizing only the related records that match a condition

• Multi-hop references: Following more than one relationship

• Date calculations: The current date and time

• Advanced text manipulation: Beyond joining text together, including converting a number to text

• Single-select outputs

Troubleshoot a formula field

The field is blank on some records but not others

Most often this is blank inputs, and it's the rule rather than a failure: if any field the formula references is empty on a record, that record's result is empty. Lightfield doesn't treat a blank number as zero. A formula totaling four quarters produces a value only on records where all four are filled in, so it looks correct on complete records and blank on the rest.

To confirm, open a record where the field is empty and check each field the formula references for a missing value.

To fix it, decide what a blank should mean and say so:

sum(coalesce({Q1}, 0), coalesce({Q2}, 0), coalesce({Q3}, 0), coalesce({Q4}, 0))

Where a blank means "unknown" rather than zero, an empty result is the correct answer — leave it. Editing the formula recalculates the field across existing records.

Three other causes produce the same per-record blank:

• A comparison against a blank value returns blank, not false — {Amount} > 1000 is blank when Amount is. Guard it with isSet({Amount}) && {Amount} > 1000.

• Dividing by zero returns blank instead of infinity:

  if({Total accounts} != 0, {Won accounts} / {Total accounts}, 0)

  Only the branch the condition selects is evaluated.

• A rollup over more than 10,000 related records can't calculate and leaves the field empty.

The field is blank on every record

A new or recently edited formula may still be backfilling, and a large import can delay recalculation. Give it a few minutes and check again.

If it's still blank everywhere, the cause is usually one that applies to every record equally:

• A referenced field is empty on all records — often a custom field that was created but never populated, or one an import didn't map.

• The formula mixes two different currencies, or performs an unsupported operation such as multiplying one currency value by another. Either leaves the result empty on every record.

The value looks wrong

• A rollup total looks low. Rollups skip related records with a blank value in the field being summarized rather than counting them as zero, and count counts only related records that have a value in that field.

• A total looks higher than expected, or a zero appears where you expected no value. Check for a coalesce({Field}, 0) around an input whose blank means "unknown" — that turns every unknown into a real zero, in the field and in any filter or report reading it.

• Currency amounts look off. Lightfield never converts between currencies. Plain numbers take on whichever currency they're combined with, so {Amount} * 0.15 stays in the amount's currency.

The formula can't be saved