Here is my requirement.

I have 2 objects

Object 1. Opportunity



2.Primary_Purchase__c(Primary purchase record from related list)

Object 2. Purchase__c


1. Count__c(Field to validate before update on opportunity)

2. Opportunity__c(lookup field to Opportunity)

3. Purchases2(Child Relationship Name)

4. Primary__c(Checkbox)

From Purchase__c have a Lookup to Opportunity and on Opportunity there is a field Primary_Purchase__c which is nothing but the Primary among all related purchases for that Opportunity.

Now when I try to update "Stage" on Opportunity to "Closed-Won" need to validate a field on Count__c on primary Purchase__c record from related list and if count=0 should throw my custom validation.

Apex Trigger(Not working for my functionality/requirement):

trigger countvalidateOnOpp on Opportunity (before insert,before update) {
    Set<Id> oppsToUpdate = new Set<Id>();

    for (Opportunity newOpp : Trigger.new) {
        Opportunity old = Trigger.oldMap.get(newOpp.Id);

        if (newOpp.StageName == 'Closed Won') {

    List<Purchase__c> purchases = [SELECT Id, Status__c, Primary__c ,Count__c
                                   FROM Purchase__c
                                   WHERE Opportunity__c in :oppsToUpdate
                                   AND Primary__c = TRUE];

        for(Opportunity opp: Trigger.new){

        if(purchases != null && purchases.size() > 0)
            for (Purchase__c pur : purchases) {
                if(pur.Primary__c = true && pur.Count__c>0) { 
                  opp.addError('Cannot Update Opp');  


    catch(Exception e)
  • Note you will never throw this validation before insert since the record cannot yet have children. Don'y you just need to change pur.Count__c>0 to pur.Count__c==0? – Adrian Larson Sep 26 '16 at 16:37
  • what if you don't get any record with this condition. Also I don't think you need nested loop here. – Tushar Sharma Sep 26 '16 at 16:42
  • @Andrian: I did try that ==0 but still my trigger not throwing the validation message – Salesforce Learner Sep 26 '16 at 18:18
  • Is Purchase__c master-detail to Opportunity, or just a lookup? – sfdcfox Sep 26 '16 at 18:49

I'm pretty sure this should get you close. Basically, we use AggregateResult to quickly find the values we're interested in. We use a little trick to populate the map with an aliased field to save the extra loops. This code also fixes a number of probable logic errors in your code. This code assumes that a closed won opportunity must have a primary purchase, and that purchase's Count field must not be 0.

trigger countValidateOpp on Opportunity(before insert, before update) {
    // Empty map by default
    Map<Id, AggregateResult> ar = new Map<Id, AggregateResult>();
    if(Trigger.isUpdate) {
        // Check for closed won opps
        Set<Id> opportunityId = new Set<Id>();
        for(Opportunity record: Trigger.new) {
           if(record.StageName == 'Closed Won') {
        // If any are closed, do a query
        if(!opportunityId.isEmpty()) {
            ar = new Map<Id, AggregateResult>(
                [SELECT Opportunity__c Id, SUM(Count__c) sum
                 FROM Purchase__c
                 WHERE Opportunity__c = :opportunityId AND 
                       Primary__c = TRUE
                 GROUP BY Opportunity__c]
    for(Opportunity record: Trigger.new) {
        // if matching condition, report error
        if(record.StageName == 'Closed Won' && 
             (ar.get(record.Id) == null || ((Decimal)ar.get(record.Id).get('sum')) == 0) {
            record.addError('No primary purchase with count more than 0.');
| improve this answer | |
  • This works perfectly I need to throw the validation if there is NULL on this count field how to make this filter. – Salesforce Learner Sep 26 '16 at 19:32
  • @SalesforceLearner Perhaps just add "AND Count__c <> null" in the query? – sfdcfox Sep 26 '16 at 19:39

If Purchase has a master-lookup field to opportunity, just create a new Rollup Summary field, using the Sum operator on Purchase records where Primary is true. You can then reduce this to just a validation rule:

AND(ISPICKVAL(StageName,"Closed Won"), Primary_Purchase_Sum__c=0)
| improve this answer | |
  • Nope, its a Lookup so need to go with your trigger. – Salesforce Learner Sep 26 '16 at 19:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.