Why does the value in my formula field value in the UI differ from value returned from SOQL on the same Opportunity Line Item?

Question

I have two custom fields:

1. IsCurrentFormula__c, a formula field that evaluates whether or not today's date falls between an Opportunity Line Item's effective and end date (eg. within a range of dates).

2. IsCurrent__c, a checkbox field based on the result of the value of IsCurrentFormula__c.

I have two workflows:

2. Set IsCurrent__c to True - if IsCurrentFormula__c==true, IsCurrent__c=true.

3. Set IsCurrent__c to False - if IsCurrentFormula__c==false, IsCurrent__c=false.

I have a batch job that runs every night via scheduled job:

This batch queries all Opportunity Line Items where the value of IsCurrentFormula does not match IsCurrent (this is usually a result of today's date surpassing that of the line item's end date (expired/not-current) or the line item's effective date (active/current). Once I have this list of records, I make a call to Database.update(scope, false) to touch each of these records, causing the workflow to run (or so I believe) and correctly set the IsCurrent field. (I suppose a good secondary question would be to ask if this does happen as a result of the Database.update call.)

Question:

My formula field appears to be working as expected, as I see that my IsCurrentFormula__c field is being set to true. However, I am noticing that my IsCurrent__c field is NOT being set to true. I did some quick debugging via the developer console and anonymous apex by running the same exact query that I have in my batch except using the Id of the problematic Opportunity Line Item instead of comparing IsCurrent__c to IsCurrentFormula__c:

SELECT Id, Name, IsCurrentFormula__c, IsCurrent__c, ContractEffectiveDate__c, ContractEndDate__c
FROM OpportunityLineItem WHERE Id='00kj0000008wWtm'

The value of IsCurrentFormula__c in both of these cases is (incorrectly) false, and when I look at the same Opportunity Line Item's page in Salesforce, it (correctly) shows true.

I can't think of a reason that these would be different. I know that formula values are not stored in the database, but they should still evaluate correctly when the query is run.

Ps. If it helps, here's a high-level screenshot that includes (from top to bottom separated by horizontal red lines) the value shown in the Salesforce UI, the query results from the Query Editor in the SFDev console, and one of the two workflows (the other one is just the opposite as mentioned above).

Answer 1

I believe as a thumb rule (though it has not been documented, but based on my experience) never use Formula field as a evaluation criteria in Workflow and Process Builder.

Workflow and process builder's process may not work properly.

I have faced similar kind of issues in my project where I have tried to evaluated by formula field. It didn't work.

So, you should create a checkbox for IsCurrentFormula__c field instead of formula and use that in workflow criteria.

It should work.

Reason: Formula fields are automatically calculated, they are read-only on record detail pages and do not update last modified date fields. So formula field values are returned during runtime which can be accessed from UI or query.

Also, having similar answer SalesForce workflow based on a formula field