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?