Related object, non-lookup validation rule?

Question

our sales team is using a custom object Exception_Tracking__c to keep track of various exceptions on the Opportunity before it becomes a deal. There's a lookup to Opportunity in the Exception_Tracking__c object, but not an Exception_Tracking__c lookup on the Opportunity (just a related list).

A really pointless field that Sales just has to have is Opportunity.Exception_NA__c (boolean).

If there aren't any Exceptions for a specific Opportunity, they'll mark that as TRUE. However, they also want validation in place to prevent a user from marking the field as TRUE if there are any related Exception_Tracking__c records. Trying to figure out how to do this.

My understanding is that standard validation won't work and I would need to use Apex. I'm thinking I would need to do a Map of some kind, but just don't know how to tie it all together. My failed attempt:

trigger OpportunityExceptionTrigger on Opportunity (before insert, before update) {
    
    
    
    Map<Id,Exception_Tracking__c> exceptionRelatedFields=new Map<Id,Exception_Tracking__c>(
                [Select Opportunity__r.Id 
                  from Exception_Tracking__c 
                  where Opportunity__c IN:Trigger.New]);
    
    For(Opportunity opp : trigger.new){
        If(opp.Exception_NA__c == TRUE && exceptionRelatedFields.get(opp.Id) != null){
            opp.addError('Because there are Exception Tracking records submitted for this Opportunity, the Exception NA field cannot be marked as true');
        }
    }
}

Answer 1

What you have right now seems pretty close to me.

The only things of note are:

• <something> == true can always be reduced to simply <something> (mostly a code style thing, shouldn't cause any problems)
• Records do not have Ids in a before insert context, and it's thus impossible to have related records before insert

So remove the before insert from the contexts that your trigger runs in, and things should work. You'll probably want to have a way to automatically uncheck Exception_NA__c if any exception tracking records are related to a given opportunity, but that's out of scope for this question. A trigger on Exception_Tracking__c should do the trick, and honestly, you should consider making Exception_NA__c read-only and have it be automatically managed though code (so that you can do away with your trigger on Opportunity and prevent Sales from trying to game the system). Using the DLRS (Declarative Lookup Rollup Summary) tool from Andrew Fawcett is an easy way to handle simple things like this (count the child records, make a checkbox formula field that is checked only when the count is 0).

If you do decide to continue on your own, you should be using a trigger framework instead of having logic inside of your triggers (and don't forget, best practice is 1 trigger for any given SObject).

Answer 2

Got this figured out by utilizing the size of the map:

trigger OpportunityExceptionTrigger on Opportunity (before update) {
     
    Map<Id,Exception_Tracking__c> exceptionRelatedFields=new Map<Id,Exception_Tracking__c>([Select Id, Opportunity__c from Exception_Tracking__c where Opportunity__c IN:Trigger.New]);
    system.debug(exceptionrelatedfields.size());
    
    For(Opportunity opp : trigger.new){
        If(opp.Exception_NA__c == TRUE && exceptionRelatedFields.size() > 0){
            opp.addError('Because there are Exception Tracking records submitted for this Opportunity, the Exception NA field cannot be marked as true');
        }
    }
}