Before Trigger & Formula Field Reliability

Question

I have the following code in a before-insert/update trigger:

for(Contact c : newCs) { // newCs is Trigger.new
    // Various "inside the main Contact-trigger-for-loop" actions go here
    //...
    if (c.Coll__c != null) {
        if (
            // Don't bother if nothing of that could feed into an OwnerId change has changed.
            isInsert ||
            (
                isUpdate &&
                (
                    c.Coll__c != oldCsMap.get(c.Id).Coll__c ||
                    (c.Coll__c == 'Four Year College' && c.Formula_4Yr_Couns_Sugg_SFID__c != null && c.Formula_4Yr_Couns_Sugg_SFID__c != oldCsMap.get(c.Id).Formula_4Yr_Couns_Sugg_SFID__c) ||
                    (c.Coll__c == 'Two Year College' && c.Formula_2Yr_Couns_Sugg_SFID__c != null && c.Formula_2Yr_Couns_Sugg_SFID__c != oldCsMap.get(c.Id).Formula_2Yr_Couns_Sugg_SFID__c)
                )
            )
        ) {
            // Move Formula_2Yr_Couns_Sugg_SFID__c/Formula_4Yr_Couns_Sugg_SFID__c to OwnerId as appropriate for Coll__c (OK to do at any time in execution context -- do NOT recursion-proof)
            if (c.Coll__c == 'Four Year College' && c.Formula_4Yr_Couns_Sugg_SFID__c != null && c.OwnerId != c.Formula_4Yr_Couns_Sugg_SFID__c) {
                    c.OwnerId = c.Formula_4Yr_Couns_Sugg_SFID__c;
            } else if (c.Coll__c == 'Two Year College' && c.Formula_2Yr_Couns_Sugg_SFID__c != null && c.OwnerId != c.Formula_2Yr_Couns_Sugg_SFID__c) {
                    c.OwnerId = c.Formula_2Yr_Couns_Sugg_SFID__c;
            }
        }
    }
    //...
}

Basically, there are 3 fields (1 normal, 2 formula) where, if any of them have changed, I want to copy the value of the appropriate formula field into OwnerId (the "normal" field is used to decide which "formula" field is appropriate).

Since this is an inside-the-same-object simple field-value-copy, a "BEFORE" trigger context is very tempting because it'd be so efficient. I was told that people want this to be slipped into an existing trigger handler specifically because they could not manage to fit even one more "Process Builder" onto Contact (big loads are already going "flump" with respect to CPU time).

However, since the value to be copied (and the "should I proceed?" decision) involves formula fields, I can't tell if it's going to do an accurate job.

My gut tells me that, particularly since it's not recursion-proofed (upon request), it'll be pretty darned good for situations where a real live data-load / user-hitting-"save" change one of the 3 values (the formula field's value would be changed by changing where the prospective student lives, where they go to school, by changing the value of Coll__c, etc.) because there are so many Processes & Workflows on Contact that some pass across this code should probably pick up on enough to get the right value into OwnerId.

And my quick-and-dirty unit test passes:

private static testMethod void testAssignedCounselorCopyToOwner() {

    Id counsProfileId= [select id, name from profile where name='Counselor'][0].Id;
    User testUser = new User(alias = 'u1', email='[email protected]', emailencodingkey='UTF-8', lastname='Test', languagelocalekey='en_US', localesidkey='en_US', profileid = counsProfileId, timezonesidkey='America/Los Angeles', username='[email protected]');
    INSERT testUser;

    ContactTriggerHandler.skipAssignedCounselorCopyToOwner = TRUE;
    Contact c1 = new Contact(LastName='c1Last', Coll__c='Two Year College');
    System.runAs(testUser) { INSERT new List<Contact>{c1}; {} }
    ContactTriggerHandler.skipAssignedCounselorCopyToOwner = FALSE;

    Id c1OwnerIdBeforeTest = [SELECT OwnerId FROM Contact WHERE Id = :c1.Id][0].OwnerId;
    System.assertEquals(testUser.Id, c1OwnerIdBeforeTest); // Make sure "OwnerId" is simply the "test user" who INSERT-ed c1

    Test.startTest();
    c1.Coll__c='Four Year College';
    Contact c2 = new Contact(LastName='c2Last', Coll__c='Two Year College');
    System.runAs(testUser) {
        UPDATE new List<Contact>{c1}; {}
        INSERT new List<Contact>{c2}; {}
    }
    Test.stopTest();

    System.assertNotEquals(c1OwnerIdBeforeTest,[SELECT OwnerId FROM Contact WHERE Id = :c1.Id][0].OwnerId); // Make sure the c1 owner was changed from the "test user" upon UPDATE
    System.assertNotEquals(testUser.Id,[SELECT OwnerId FROM Contact WHERE Id = :c2.Id][0].OwnerId);  // Make sure the c2 owner was set to something besides the "test user" upon INSERT
    System.assertNotEquals([SELECT OwnerId FROM Contact WHERE Id = :c1.Id][0].OwnerId,[SELECT OwnerId FROM Contact WHERE Id = :c2.Id][0].OwnerId); // Note that this presumes two-year & four-year counselors for low-info recruits don't overlap.  May need to remove this test if such presumptions become untrue due to staff assignment changes.  It's a quick-and-dirty way to examine #2 for having a "2-year" counselor rather than knowing exactly who that should be ("not the 4-year counselor" is the test).

}

I also threw a couple of System.assert(FALSE,...)s into my code at various points -- the Formula_4Yr_Couns_Sugg_SFID__c & Formula_2Yr_Couns_Sugg_SFID__c values inside the loop, before my big "if," have the same values for "c1" as they do when SOQL-queried after "c1" has finished INSERT-ing in the test class -- they're not blank inside the loop or anything as the INSERT first gets BEFORE-processed. Similarly, c1's Formula_2Yr_Couns_Sugg_SFID__c goes NULL in both places (inside the BEFORE handler loop and after the UPDATE is done) as it's UPDATE'd -- without it mattering whether I suppressed trigger-post-processing on C1's initial INSERT. So things look promising with respect to the data integrity of formula-field values in BEFORE loops.

Still, I have my doubts. A nagging part of my brain suspects that whatever values of Formula_4Yr_Couns_Sugg_SFID__c & Formula_2Yr_Couns_Sugg_SFID__c exist on "c" in a "Trigger.new" loop may not be what I think they are (perhaps old values from previous recursions, perhaps not-actually-yet-recalculated, etc).

Does anyone know details about typical values found in (Trigger.new)[#].Formula_Field__c references?

(I'd love it if somehow, my existing code could be sufficient, since it's meant to be fired upon every Contact Update/Insert, and anything more "foolproof" seems like it would involve at least 1 SOQL query.)

Answer 1

If none of the formulas reference anything cross-object, you should be good.

If you have any trouble referencing it directly, you can call recalculateFormulas() to ensure you have the correct value.

Answer 2

As an addendum to @adrianLarson comment:

Cross-object formula fields may be available in the before trigger context if the parent relationship is based on a required field or on a lookup field with non-null value. Looking at the Trigger Order of Execution

1 On the server, Salesforce: Loads the original record from the database or initializes the record for an upsert statement.

2 Loads the new record field values from the request and overwrites the old values. If the request came from a standard UI edit page, Salesforce runs system validation to check the record for:

...Compliance with layout-specific rules

...Required values at the layout level and field-definition level

...Valid field formats

...Maximum field length

When the request comes from other sources, such as an Apex application or a SOAP API call, Salesforce validates only the foreign keys. Prior to executing a trigger, Salesforce verifies that any custom foreign keys do not refer to the object itself. Salesforce runs user-defined validation rules if multiline items were created, such as quote line items and opportunity line items.

3. Executes all before triggers.

4 Runs most system validation steps again, such as verifying that all required fields have a non-null value

So, let's consider some examples:

---

Example 1 - OpportunityLineItem - cross object formula field to Opportunity.xxx

OLIs inserted from the standard UI must, by definition, have a parent Opportunity so any formula fields that are like Opportunity.xxx will be available in the OpportunityLineItem "before insert" trigger. If your business logic ensures that all Opportunities must have Accounts, then formula fields like Opportunity.Account.xxx will also be available to the "before" trigger. Thus, no SOQL is required to access these fields.

OpportunityId is a required field in the schema.

However, OLIs inserted via DML or via the API may not have the required parent field (OpportunityID) set. In this use case, the formula field will resolve to null and your "before" trigger must accommodate. The record won't save because the required field check isn't run until the before trigger is completed.

---

Example 2 - OpportunityLineItem - cross object formula field to PricebookEntry.Product2.xxx

OLIs inserted from the standard UI must, by definition, have a PricebookEntry so any formula fields that are like PricebookEntry.Product2.xxx will be available in the OpportunityLineItem "before insert" trigger.

PricebookEntryId is a required field in the schema.

However, OLIs inserted via DML or via the API may not have the required parent field (PricebookEntryID) set. In this use case, the formula field will resolve to null and your "before insert" trigger must accommodate. Note the record won't save because the required field check is run after the before triggers execute..

---

Example 3 - Opportunity with cross object formula field to Account.xxx

The OOTB page layout has AccountId as a required field but you could have page layouts where this is not required. Furthermore, AccountId is not a required field in the schema for Opportunity.

Thus, as with the other examples, the "before" trigger may find the cross-object formula fields resolving to null depending on how whether the Opportunity record was CRUD with the required fields.

---

Lastly, since you can use DML or the API to update SObjects, the update might (errantly) null out required fields and your before update trigger's references to cross-object formula fields may resolve to null. A truly robust before update trigger that is doing logic against a parent object's values must allow for the use case where the lookup referenceId to the parent object is null or otherwise bogus. This is one of the reasons why trigger-based validations are recommended to be done in "after triggers" (once validation and required field checks have been completed)