0

In order to isolate and re-produce a more complex issue, I've created a formula field Expiry_Formula__c on object Opportunity with the formula defined as:

ADDMONTHS(Expiry_Date__c, -3)

where Expiry_Date__c is a field of type Date. I've then added a minimal trigger to the Opportunity object which simply shows the value of these fields when a record is created:

trigger OpportunityTrigger on Opportunity (before insert) {

    if (Trigger.IsInsert) {
        Opportunity opp = Trigger.new[0];
        System.debug('opp.Expiry_Date__c =' + opp.Expiry_Date__c);
        System.debug('opp.Expiry_Formula__c =' + opp.Expiry_Formula__c);
    }

}

When I create an Opportunity record with Expiry_Date__c = 2022-07-30, the trigger shows value 2022-04-29 for the formula, but when I then query the newly created record the value of Expiry_Formula__c is 2022-04-30 (the Expiry_Date__c value is still 2022-07-30), i.e. the value of the formula appears to be different in the 'before insert' trigger than it is when retrieving the record using SOQL.

Questions:

  • is this correct and documented behavior by the salesforce.com system?
  • is there any workaround or fix which will make the formula give the same result in the 'before insert' trigger as when querying the record afterwards?

1 Answer 1

1

According to Salesforce documentation, no formula field outcomes should be available in a before trigger, even after querying it: https://help.salesforce.com/s/articleView?id=000320999&type=1

As you have proven, the behavior in reality is slightly different. Particularly for formula fields that only refer other fields on the same object, there might be some database optimization that makes the formula field calculate already in the before trigger.

I suspect the date difference is caused by the fact that dates are really stored as datetime with a time of 00:00:00Z. So that could possibly cause unexpected differences. Since Salesforce doesn't officially support this, it might not really classify as a bug, although it's definitely not correct.

As a workaround or fix, you could try a couple of things:

  • running 'recalculateFormulas()' in the trigger and see if it resolves to the right date.
  • using a before insert record-triggerd Flow and see if that behaves differently
  • an option that will always work is to move your logic to an After trigger. But it might be less efficient, depending on what you want to achieve in your code.
4
  • thanks! Is it possible that the salesforce.com statement on Jan 24, 2019 was accurate then but has since been supplanted by changes in one of the later releases? Certainly it appears that our code should NOT be making reference to formula fields in the 'before insert' trigger, but I also find that code running in the 'after insert' trigger receives the same (wrong?) value.
    – John Lewis
    Jun 1, 2022 at 4:10
  • Unfortunately, I've found that the 'recalculateFormulas()' approach gives the same result - I had to run it on an object cloned from the original Trigger.new object because otherwise it gives an error "UNKNOWN_EXCEPTION, Cannot set the value of a calculated field: []"
    – John Lewis
    Jun 1, 2022 at 4:10
  • As far as I can tell, this problem ONLY seems to occur when the date passed to the ADDMONTHS() function ends with 29 or 30 and is NOT the last day of the month, i.e. 2022-05-30 gives a problem, but 2022-04-30 does not. What's actually happening here is that certain Apex test methods which run successfully most of the time fail because a date calculated as (e.g. System.today() + 90) falls on one of the problematic dates (in this case 2022-07-30).
    – John Lewis
    Jun 1, 2022 at 4:11
  • 1
    if this also happens on After trigger, then it's a bug in Salesforce, indeed. Jun 1, 2022 at 8:10

You must log in to answer this question.

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