SOQL query returns different value than is on UI for formula fields
Last updated 2022-06-30 ·Reference W-2995113 ·Reported By 22 users
SOQL query returns different value than is on UI for formula fields when the field used in the formula is blank.
1. Log-in to your org.
2. Go to setup--> Account --> Fields
3. Create a new custom object field of type "text" and name it as "TestField" (TestField__c) and save it.
4. Create another custom field of type "Formula", choose return type as Number, name it as "FormulaTestField" (FormulaTestField__c) , include Formula like.
and save it.
5. Now, go to Accounts tab and open any account record and you will see a field value of 10 for FormulaTestField.
6. Now go to Developer console, and run the following query in Query Editor,
Select Id, FormulaTestField__c from Account where id ='<account-id>'
You will notice a value of 0 for the same FormulaTestField__c contradictory to the value of 10 which we have noticed it on UI.
Use 'CASE' in formula rather than 'IF' like "CASE(TestField__c, '5', 0, 'Cloud', 0,10)"
Another workaround for this will be to check for blank values using the ISBLANK operator and handling it.
Reported By (22)
Is it Fixed?
Any unreleased services, features, statuses, or dates referenced in this or other public statements are not currently available and may not be delivered on time or at all. Customers who purchase our services should make their purchase decisions based upon features that are currently available.