Formula fields

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.

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, 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 or contacts Lightfield.

Earlier plans can differ. Check your workspace’s available features.

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 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), 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.
”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.

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.