SOQL Formula Filtering: Formula Fields and the New FORMULA() WHERE Function

There are now two different ideas developers can mean by "formula in a SOQL WHERE clause," and confusing them can produce misleading examples.

1. Filtering an Existing Formula Field

If your object already has a queryable formula field, you can filter it like another field when its return type supports the comparison.

For example, suppose Opportunity has a formula field named Days_Open__c:

SELECT Id, Name, Days_Open__c
FROM Opportunity
WHERE Days_Open__c > 30

This is the established approach: the formula exists in Salesforce metadata and SOQL filters its calculated value.

2. Writing a Formula Expression Directly in WHERE

Historically, SOQL did not generally let you invent an arbitrary Salesforce formula expression directly inside WHERE the way SQL lets you compose many expressions.

That is changing—but with an important qualification.

Salesforce announced a Summer '26 pilot for FORMULA() in SOQL WHERE clauses. The feature allows compiled formula expressions to participate in filtering without creating a one-off formula field.

The current general shape documented by Salesforce is:

WHERE FORMULA('formula expression') <operator> <literal>

As of the Summer '26 pilot, Salesforce documents addition and subtraction in the expression and formula results including numeric, date/datetime, and currency types. FORMULA() is currently supported in WHERE, not HAVING.

Example: Calculate Profit Without a Formula Field

Suppose Order__c stores revenue and fulfillment cost and you want orders whose calculated profit exceeds 250:

SELECT Id, Name, Revenue__c, Cost__c
FROM Order__c
WHERE Status__c = 'Shipped'
  AND FORMULA('Revenue__c - Cost__c') > 250

Without this pilot capability, you would typically create a formula field such as Profit__c or query a broader set and calculate/filter the result elsewhere.

Example: Find Orders That Shipped More Than Three Days Late

If the object stores the order and ship dates, the calculation can be used directly as part of filtering:

SELECT Id, Name, OrderDate__c, ShipDate__c
FROM Order__c
WHERE FORMULA('ShipDate__c - OrderDate__c') > 3

This keeps the computed qualification in the query instead of retrieving every candidate order and calculating the delay in Apex or integration code.

Example: Combine a Normal Filter with a Calculated Filter

The formula condition can participate alongside ordinary SOQL criteria. For example, find high-value orders that shipped within two days:

SELECT Id, Name, Revenue__c, OrderDate__c, ShipDate__c
FROM Order__c
WHERE Revenue__c > 600
  AND FORMULA('ShipDate__c - OrderDate__c') <= 2

These examples follow Salesforce's Summer '26 pilot examples. Test them only in an org where the pilot has been enabled.

However, as of this article's publication, Salesforce describes this capability as a pilot, not a generally available assumption you should build into every production design. Salesforce says customers should contact their Account Executive or Customer Success Manager for pilot access.

Why This Matters

Without direct formula expressions, developers often choose among:

Create a formula field solely to support the query.
Query a broader record set and filter it in Apex/integration code.
Maintain duplicated calculation logic outside Salesforce.

FORMULA() can reduce that friction for eligible pilot users, but it also makes query logic more complex. A reusable business concept may still deserve a real formula field with a clear name and centralized ownership.

Don't Publish Pilot Syntax as Universal SOQL

If you see an example using FORMULA() in WHERE, check the Salesforce release status and your org's feature availability before adopting it.

For broadly deployable code today, filtering an existing formula field remains the safer baseline unless your org is explicitly enrolled in the pilot.

Performance Still Matters

Calculated filters are convenient, but convenience does not make a query selective. For large data volumes, evaluate the query plan and consider whether the filter can use indexed/selective criteria to narrow the candidate set.