FORMULA() in the SOQL WHERE Clause: Compare Two Fields Without a Formula Field

A common review comment on data-heavy Apex is “why are you querying everything and filtering in a loop?” The usual answer was that SOQL couldn’t compare two fields on the same record — WHERE Amount > ExpectedRevenue has never been valid. So you either created a formula field just to filter on it, or pulled the rows back and compared them in Apex. Winter ‘27 adds a third option: the FORMULA() function in the SOQL WHERE clause, which does field-to-field arithmetic comparison inside the query itself.

It is Beta in API v68.0, so read the restrictions before you reach for it — the details matter more than the headline.

The syntax

WHERE FORMULA('fieldName op fieldName') comparisonOperator literalValue

• op — the arithmetic operator inside the quoted expression. Supported values are + or - only.
• comparisonOperator — a standard SOQL comparison outside the string: >, <, =, >=, <=, or !=.
• literalValue — the value the arithmetic result is compared against.

The whole formula is a string literal, and the comparison happens against that string’s evaluated result.

Two worked examples

Find opportunities where Amount exceeds ExpectedRevenue by more than 100:

SELECT Id FROM Opportunity WHERE FORMULA('Amount - ExpectedRevenue') > 100

Find records that closed more than 10 days after they were created:

SELECT Id FROM Opportunity WHERE FORMULA('CloseDate - CreatedDate') > 10

The second example is the one that used to be painful — date arithmetic across two fields with no formula field to lean on.

Supported data types

Both fields in the expression must use a supported type, and they must be compatible:

• Double / Decimal
• Integer
• DateTime
• Date
• Currency

Restrictions worth reading before you rely on it

These are the constraints that decide whether FORMULA() fits your case:

• If the left-hand field is not a date type, the right-hand field cannot be a date type either.
• DATE and DATETIME fields cannot be mixed in the same expression.
• Only + and - are supported — no multiplication, division, or function calls.
• FORMULA() is a WHERE-clause function only. It is not supported in the SELECT clause.
• It does not bring the rest of the formula engine with it; this is arithmetic-comparison filtering, not full formula evaluation.

Availability — treat it as sandbox-only

This is where the secondary write-ups disagree, so go by the release documentation: FORMULA() in the WHERE clause is a Beta service governed by the Salesforce Beta Services Terms. It requires API version 68.0 or later and runs only in sandboxes, Developer Edition, and scratch orgs — not production. Prototype and test with it now, but keep a formula-field or Apex-side comparison as the production path until Salesforce confirms GA and production support.

When to use it, when not to

Reach for FORMULA() when the only reason a formula field exists is to support a query filter, or when you are pulling rows back purely to compare two of their own fields in Apex — that pattern wastes heap and query rows on data you immediately discard. If you need the computed value in the result set, this does not help, because it can’t sit in SELECT. And if the comparison needs multiplication, a ratio, or anything past +/-, a formula field is still the tool.

For heavier query-side work, it pairs with the same instincts covered in SOQL for loops and heap management — the goal is always to let the database do the filtering instead of the transaction. And as with any user-supplied filter value, the field names inside FORMULA() are fixed strings you control, but any literal you concatenate in still follows the rules in preventing SOQL injection in Apex.