FORMULA() in the SOQL WHERE clause: comparing two fields at last

I have built Salesforce formula fields I never wanted.

A checkbox whose only job is to flag rows where Amount beats ExpectedRevenue, so a report filter or a SOQL query can key off it. The field adds nothing to the data model. It exists because SOQL could compare a field to a literal and a field to a bind variable, but never a field to another field on the same record.

Winter ‘27 changes that with a new beta: FORMULA() in the WHERE clause. You write the arithmetic inline, and the query filters on the computed result.

Three things decide whether you reach for it. It is a quality-of-life win for anyone who writes SOQL, it is narrower than the release note makes it sound, and you can’t put it in a production org yet. Learn the shape now, move it into shipping code when it goes GA.

The one shape it supports

The form is a string expression wrapped in FORMULA(), compared against a literal value:

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

Two more that come up in the wild, a date gap and an outstanding balance:

-- Opportunities that took more than 10 days to close
SELECT Id FROM Opportunity
WHERE FORMULA('CloseDate - CreatedDate') > 10

-- Invoices with money still owed
SELECT Id, Name FROM Invoice__c
WHERE FORMULA('Amount_To_Pay__c - Amount_Paid__c') > 0

Read the pattern off those examples. Inside the quotes sits one expression: two field references joined by a single operator. Outside the quotes sits an ordinary SOQL comparison against a literal. The arithmetic does the computing, the comparison does the filtering, and the row never needed a formula field to carry the result.

The operator inside the quotes is where the narrowness bites. It is + or -, and nothing else: no multiplication, no division, no nested formula functions, no IF(). If your rule is Amount * Probability > 10000, FORMULA() can’t express it, and an example online that shows multiplication is wrong against the current beta docs.

Both operands have to be the same type. The types reported so far are number, integer, currency, date, and datetime, and you can’t mix a Date with a DateTime in one expression.

One more boundary: the function works in the WHERE clause only, not in SELECT.

What it retires

Until now, filtering on a cross-field comparison meant one of three workarounds, and each one cost something.

• A dedicated formula field created only so a query could filter on it. Metadata clutter, one more field against the per-object limit, and a field whose name tells the next developer nothing about why it exists.
• A SOQL query that pulls the superset into Apex, then a loop or a stream that throws away the rows that don’t match. More rows over the wire, more heap, and the real filter hidden in procedural code instead of the query.
• Over-fetching on a loose predicate and discarding the rest, the same cost in a thinner disguise.

FORMULA() folds the computed rule back into the query where it belongs. Salesforce Ben’s Winter ‘27 developer roundup framed the win as the end of two habits at once: spinning up a formula field only to filter on it, and dragging too much data into Apex to filter it there instead.

That assembly tax, over-fetch and filter-later, is the same one that makes non-selective SOQL expensive and burns governor limits on heap and query rows. Moving the predicate into the database is the right instinct. The caveats are about when you are allowed to, and how fast it runs.

The catch that keeps it out of production

FORMULA() is beta, and it requires API version 68.0, the version that ships with Winter ‘27. More to the point, it runs in sandboxes, Developer Edition orgs, and scratch orgs only. The function is not available in production.

The lifecycle explains the wording you’ll see across release notes. It arrived as a Summer ‘26 pilot, where you had to ask your account executive to be enrolled. In Winter ‘27 it moves to beta and turns on automatically in those non-production orgs, under Salesforce’s Beta Services terms.

There is no announced GA date for the feature itself. Watch for the date it becomes available in production, and don’t treat the Winter ‘27 release GA date as that signal: the release going GA and this feature going GA are different events.

So the practical status is simple. You can write it, test it, and design around it today in a sandbox or scratch org. You cannot point a production integration, a managed package, or a nightly batch at it until it graduates.

One detail I couldn’t pin down: whether every query surface treats it the same. The sources confirm API 68.0 and the non-production limit, but not whether inline Apex SOQL, the REST Query API, and the Bulk API all accept it identically. If a particular surface matters to your design, test it there rather than assume parity, and treat null operands as another thing to check, because the docs I found don’t say what happens when one side of the subtraction is blank.

The question the docs don’t answer

Here is the thing I wanted to know and couldn’t verify: whether a FORMULA() predicate uses an index, or forces a full scan.

It matters because Salesforce terminates non-selective queries once an object crosses roughly 200,000 rows. A formula field, in general, has no underlying index and makes the optimizer scan, which is one reason filtering on formulas has always been a performance footgun.

Whether the optimizer treats an inline FORMULA() the same way, I couldn’t find Salesforce stating anywhere. Until it does, I’d assume the pessimistic case: that FORMULA('A - B') > n behaves like an unindexed computed filter, fine on a few thousand rows and risky on a few million.

That assumption has a consequence for where you use it. On a small or medium object, reach for it freely once it is in production. On a large object, pair it with a selective, indexed predicate so the optimizer has something to narrow on first, and check the Query Plan tool before you trust it at volume. For the large jobs, the cursor and batch patterns you already use for big data don’t go away just because the filter got cleaner.

One more scope note. This is a core CRM SOQL feature. I have not seen it documented for Data 360’s SOQL, which is a separate dialect with its own rules on currency, relationships, and aggregation, so don’t assume parity there until the docs say so.

What to do with it this season

Treat the beta as a design preview, not a dependency. In a scratch org or sandbox on API 68.0, rewrite a couple of your ugliest over-fetch-and-filter queries with FORMULA() and confirm they return what the old Apex did. Make a list of the throwaway formula fields in your org that exist only to support a filter, because those are the ones you get to delete when the function reaches production. Keep the fields in place until then.

If you maintain an ISV package across many customer schemas, this is worth tracking closely, since it is exactly the case where you can’t assume a given formula field exists on every org. For everyone else, the move is to learn the syntax, respect the +/- ceiling, and leave a comment in the code where a FORMULA() rewrite is waiting on GA. The habit of shipping a field you never wanted ends soon. It just doesn’t end this release.

Understanding the basics

What is the FORMULA() function in SOQL?

FORMULA() is a Winter ‘27 beta function that lets a SOQL WHERE clause filter on a value computed from two fields on the same record, such as WHERE FORMULA('Amount - ExpectedRevenue') > 100. It removes the long-standing need to create a formula field, or to query a superset and filter it in Apex, just to compare one field against another.

Can I use FORMULA() in a production org?

Not yet. In Winter ‘27 it is beta and available in sandboxes, Developer Edition orgs, and scratch orgs only, and it requires API version 68.0. It began as a Summer ‘26 pilot. Salesforce has not announced a date for it to become generally available in production, so design with it now and ship with it later.

What operators does FORMULA() support?

Inside the quoted expression, only + and -, joining exactly two fields of the same type (number, integer, currency, date, or datetime, with no mixing of Date and DateTime). Multiplication, division, and formula functions are not supported. Outside the quotes you use a normal SOQL comparison against a literal, and the function works in the WHERE clause only, not in SELECT.

Does FORMULA() in a WHERE clause slow queries down?

Possibly, on large objects. Salesforce has not published whether a FORMULA() predicate can use an index, and filtering on computed values has historically forced a full scan. Treat it like an unindexed formula filter: safe on small and medium objects, and something to pair with a selective indexed field and verify in the Query Plan tool before running against millions of rows.

---

Planning a Winter ‘27 upgrade and want the query and automation changes triaged before go-live, not discovered the weekend of? Talk to us. Keeping an org fast and cheap to change as the platform moves underneath it is the work we do every release.