4

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:

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

  2. 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).

notice how DB and UI values of the same fields are different.

1
  • Welcome to SFSE! No need to greet or sign your posts. We have a pretty terse format. Happy exchanging.
    – Adrian Larson
    May 16, 2017 at 2:00

1 Answer 1

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

8
  • Does not explain the difference in the query and the UI
    – Eric
    May 16, 2017 at 3:54
  • Even if I didn't find any documentation on that, but faced similar issues May 16, 2017 at 3:55
  • Nah your good. Just curious if you had thoughts on that
    – Eric
    May 16, 2017 at 3:56
  • 2
    Still does not explain it for me or I am being thick. If I am looking at the UI and use dev console to query the fields with nothing else happening I would expect the query to return what I am seeing on the screen. I thought that what what the op was showing. But I could be missing it all together.
    – Eric
    May 16, 2017 at 4:20
  • 1
    This answer looks like great advice, but doesn't seem to address the core issue that a formula field is giving a different value in the UI than in APEX. I am aware that formulas are evaluated at run-time but that doesn't explain at all why APEX would get a different result. This seems like it should not be the case if the platform is reliable.
    – dsharrison
    May 16, 2017 at 18:43

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .