Is it possible to have a validation rule with a finely nuanced loophole to create and modify detail records?

Question

We have Opportunity with a custom checkbox property "Locked__c". While this value is set to true (never mind how), it should be impossible to change any value on the Opportunity.

The Opportunity has a detail record "Requirement__c" with a checkbox property "Is_Completed__c".

The Opportunity has two roll-up summary fields "Requirement_Count__c" and "Open_Requirement_Count__c", and as you might expect, adding or modifying these cascades into an update of the Opportunity, which therefore executes the Opportunity validation rules.

It must be possible to add and modify "Requirement__c" records even when the Opportunity is locked and we've gotten very close to the requirement with a validation rule that checks:

( 
    (NOT( Locked__c )&&NOT(PRIORVALUE(Locked__c)))&&( 

    && NOT(OR( 
    ISCHANGED(Requirement_Count__c), 
    ISCHANGED(Open_Requirement_Count__c) 
    ))
)

But there is a corner case where this rule blocks changes when we don't want it to.

If the user simultaneously (re)opens and closes the same number of Requirement__c, the validation rule will reject the changes.

The validation has no way to distinguish between which Requirement__c are opening and closing.

I thought about trying to make this into a trigger, but as a trigger on Opportunity, at the time this executes, there would (so far as I know) also be know way of knowing the historic states of the Requirement__c, so we still can't make the distinction.

I also thought about adding a "Was_Completed__c" property to the "Requirement__c", but it seems to me that we'd need to simultaneously update this every time each "Requirement__c" related to the same Opportunity was updated, and we'd need to do this in a bulkified way, and somehow without recursion causing SFDC to throw a tantrum. I'm not sure whether that is possible, but it seems a lot of work to deal with the corner case....

Is there a more elegant/practical way to accomplish this?

Answer 1

I've been handed several projects like this in the past, and I hate it every time it comes to my plate.

I wouldn't call my solution elegant, it is highly dependent on the Trigger Order of Execution, but it has worked for me in the past.

In a nutshell, to do this, I'd create a new checkbox field (let's call it Allow_Lock_Bypass__c, default to unchecked) on Opportunity, and purposely leave the field off of all page layouts (we don't want users to think they can use it to bypass business rules).

Your validation rule would then simply have the one exemption, based on whether or not Allow_Lock_Bypass__c is true/false. Something like

// We want the validation rule to complain in the case where any change is made to 
//   the Opp after the transaction that locked the Opp has finished, and 
//   we aren't explicitly allowing a lock bypass
AND(
    Locked__c,
    NOT(PRIORVALUE(Locked__c)),
    NOT(Allow_Lock_Bypass__c)
)

In the trigger for Requirement__c, you would explicitly update the related Opportunity (after insert/update/delete) and set Allow_Lock_Bypass__c to true.

// This'd work for insert and update
Map<Id, Opportunity> oppsToBypass = new Map<Id, Opportunity>();
for(Requirement__c req :trigger.new){
    if(!oppsToBypass.containsKey(req.Opportunity__c)){
        oppsToBypass.put(req.Opportunity__c, new Opportunity(
            Id = req.Opportunity__c,
            Allow_Lock_Bypass__c = true
        ));
    }
}

update oppsToBypass.values();

That should ensure that any insert/update/delete of a (or many) Requirement__c record should be allowed. The last piece of the puzzle is to automatically disable the bypass after all the work we care about is done.

For that, I use a workflow rule + field update. The evaluation criteria would be "created and any time record is updated", and the filter criteria would simply be Allow_Lock_Bypass__c = true (wouldn't even need the '= true' bit if you were to use a formula instead of the field picklists). You'd then need a field update to update the field back to false.

Like I hinted at before, the reason why this works comes down to the Order of Execution.

The relevant bits are:

• Before triggers are run
• Validation rules are evaluated
• After triggers are run
• Workflow rules are evaluated
• If there is a workflow field update, before and after triggers are run one more time (and only once more), but validation/workflow is skipped

Tracing the expected execution flow...

1. Requirement__c triggers are run
2. You explicitly update related Opportunities (setting Allow_Lock_Bypass__c to true)
3. Opportunity before update triggers are run (no problems at this stage)
4. Opportunity validation rules are evaluated, Allow_Lock_Bypass__c is true, so we aren't given any trouble
5. Opportunity after update triggers are run
6. Opportunity workflow is run, we see that Allow_Lock_Bypass__c is true, so we fire a field update to set it back to false
7. Opportunity before and after triggers run again, validation is bypassed, so we don't run into any issues
8. Control returns to your after trigger(s) on Requirement__c

I think the only gotcha here is that if you have workflow field updates on Requirement__c, you might run into issues (because I think validation and workflow for Opportunity would be run again if your Requirement__c triggers update the Opportunities again on the trigger run after the field update). Having a flow/process builder active might also screw things up (since that happens so late in the order of execution, after workflow is run).

That could be solved with some re-entry control (via a static set<Id> in a helper class) to prevent you from adding records to oppsToBypass that have already gone through that process). I'd imagine you'd probably already have some controls like that in place if you had workflow field updates on Requirement__c though. If you don't, and want help getting that in place, leave me a comment.