Formula
Filter records on the sum or the difference of two fields with the FORMULA() function.
SOQL.of(Opportunity.SObjectType)
.whereAre(SOQL.Formula('Amount - ExpectedRevenue').greaterThan(100))
.toList();
Beta
FORMULA() in a WHERE clause is a Salesforce Beta feature. It works only in Developer Edition, scratch, and sandbox orgs, from API version 68.0 (Winter '27).
Salesforce accepts:
- two fields joined by
+or-, for example'Amount - ExpectedRevenue'or'Account.AnnualRevenue - Amount'. Literals, functions, and a third field are rejected. - Number, Currency, Percent, Date, and DateTime fields. Date and DateTime fields can't be mixed. Subtracting two dates gives the number of days between them.
WHEREonly. A formula can be used wherever aSOQL.Filtercan: inwhereAre, in aSOQL.FilterGroup, in aSOQL.SubQuery, and in aSOQL.InnerJoin. It's not supported inHAVING,ORDER BY, orSELECT.
FORMULA() doesn't accept bind variables, so the value is written into the query. That's why the comparators take a Decimal or a Date instead of an Object.
Methodsโ
The following are methods for Formula.
equal(Decimal value)equal(Date value)notEqual(Decimal value)notEqual(Date value)lessThan(Decimal value)lessThan(Date value)lessOrEqual(Decimal value)lessOrEqual(Date value)greaterThan(Decimal value)greaterThan(Date value)greaterOrEqual(Decimal value)greaterOrEqual(Date value)
INITโ
Formula stringโ
Construct a FORMULA() condition from two fields joined by + or -.
Signature
Formula Formula(String formula)
Example
SELECT Id
FROM Opportunity
WHERE FORMULA('Account.AnnualRevenue - Amount') < 0
SOQL.of(Opportunity.SObjectType)
.whereAre(SOQL.Formula('Account.AnnualRevenue - Amount').lessThan(0))
.toList();
COMPARATORSโ
equalโ
WHERE FORMULA('Amount - ExpectedRevenue') = 0
Signature
Formula equal(Decimal value)
Formula equal(Date value)
Example
SELECT Id
FROM Opportunity
WHERE FORMULA('Amount - ExpectedRevenue') = 0
SOQL.of(Opportunity.SObjectType)
.whereAre(SOQL.Formula('Amount - ExpectedRevenue').equal(0))
.toList();
notEqualโ
WHERE FORMULA('Amount - ExpectedRevenue') != 0
Signature
Formula notEqual(Decimal value)
Formula notEqual(Date value)
Example
SELECT Id
FROM Opportunity
WHERE FORMULA('Amount - ExpectedRevenue') != 0
SOQL.of(Opportunity.SObjectType)
.whereAre(SOQL.Formula('Amount - ExpectedRevenue').notEqual(0))
.toList();
lessThanโ
WHERE FORMULA('OrderDate__c + DeliveryDays__c') < 2026-11-01
Signature
Formula lessThan(Decimal value)
Formula lessThan(Date value)
Example
SELECT Id
FROM Order__c
WHERE FORMULA('OrderDate__c + DeliveryDays__c') < 2026-11-01
SOQL.of(Order__c.SObjectType)
.whereAre(SOQL.Formula('OrderDate__c + DeliveryDays__c').lessThan(Date.newInstance(2026, 11, 1)))
.toList();
lessOrEqualโ
WHERE FORMULA('ShipDate__c - OrderDate__c') <= 2
Signature
Formula lessOrEqual(Decimal value)
Formula lessOrEqual(Date value)
Example
SELECT Id
FROM Order__c
WHERE FORMULA('ShipDate__c - OrderDate__c') <= 2
SOQL.of(Order__c.SObjectType)
.whereAre(SOQL.Formula('ShipDate__c - OrderDate__c').lessOrEqual(2))
.toList();
greaterThanโ
WHERE FORMULA('Revenue__c - Cost__c') > 250
Signature
Formula greaterThan(Decimal value)
Formula greaterThan(Date value)
Example
SELECT Id
FROM Order__c
WHERE FORMULA('Revenue__c - Cost__c') > 250
SOQL.of(Order__c.SObjectType)
.whereAre(SOQL.Formula('Revenue__c - Cost__c').greaterThan(250))
.toList();
greaterOrEqualโ
WHERE FORMULA('Amount + ExpectedRevenue') >= 1000.50
Signature
Formula greaterOrEqual(Decimal value)
Formula greaterOrEqual(Date value)
Example
SELECT Id
FROM Opportunity
WHERE FORMULA('Amount + ExpectedRevenue') >= 1000.50
SOQL.of(Opportunity.SObjectType)
.whereAre(SOQL.Formula('Amount + ExpectedRevenue').greaterOrEqual(1000.50))
.toList();
ADDITIONALโ
ignoreWhenโ
Condition will be removed when logic expression will evaluate to true.
Signature
Formula ignoreWhen(Boolean logicExpression)
Example
SELECT Id
FROM Opportunity
WHERE StageName = 'Closed Won'
Decimal minimumMargin = null;
SOQL.of(Opportunity.SObjectType)
.whereAre(SOQL.FilterGroup
.add(SOQL.Filter.with(Opportunity.StageName).equal('Closed Won'))
.add(SOQL.Formula('Amount - ExpectedRevenue').greaterThan(minimumMargin)
.ignoreWhen(minimumMargin == null)
)
)
.toList();