Skip to main content
Version: 6.14.0

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.
  • WHERE only. A formula can be used wherever a SOQL.Filter can: in whereAre, in a SOQL.FilterGroup, in a SOQL.SubQuery, and in a SOQL.InnerJoin. It's not supported in HAVING, ORDER BY, or SELECT.

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.

INIT

COMPARATORS

ADDITIONAL

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();