Use the FORMULA() function in a WHERE clause to filter records based on arithmetic operations on two field values. The FORMULA() function is not supported in the SELECT clause.
Arithmetic formulas in a SOQL WHERE clause is a beta service that is subject to the Beta Services Terms at Agreements - Salesforce.com or a written Unified Pilot Agreement if executed by Customer, and applicable terms in the Product Terms Directory. Use of this beta service is at the Customer’s sole discretion.
Important
Syntax
1WHERE FORMULA('fieldName op fieldName') comparisonOperator literalValue
op — The arithmetic operator. Supported values: + or -.
comparisonOperator — A standard comparison operator such as >, <, =, >=, <=, or !=.
literalValue — The value to compare the result against.
Supported Data Types
Both fields in the FORMULA() expression must use a supported data type. The supported data types are:
Double/Decimal
Integer
DateTime
Date
Currency
Restrictions
The following restrictions apply when using the FORMULA() function in a WHERE clause:
If the left-hand side is not a date type, the right-hand side cannot be a date type.
DATE and DATETIME fields cannot be mixed in the same expression.
Only + and - arithmetic operators are supported.
FORMULA() in WHERE clauses does not support any other formula engine capabilities.
Examples
1SELECT Id FROM Opportunity WHERE FORMULA('Amount - ExpectedRevenue') > 100
This example filters opportunities where the difference between Amount and ExpectedRevenue exceeds 100.
1SELECT Id FROM Opportunity WHERE FORMULA('CloseDate - CreatedDate') > 10
This example filters records where CloseDate is more than 10 days after CreatedDate.
j