15

This is my first post here on the StackExchange and hoping someone can help. I have been tasked with finding a way to prevent users from moving to 70% or greater probability in an opportunity when Opportunity Products with a certain record type exist. We have valid SAP products and what we call Prospect Products (these shouldn't be allowed in opportunity with 70% or greater probability). We are also thinking of adding a field to the product table for prospect products that are "allowed" so those would obviously need to be excluded from the criteria and can be allowed in the forecast.

I know this can be done with Rollup summary fields and validation rules but it's very clunky and not full proof. I am thinking a trigger would be more suitable but I wouldn't even know where to start with my VERY limited code writing capabilities.

Anyone willing to take on this challenge? Thanks in advance!

EDITED TO ADD:

Users have the option of creating prospect products while adding opportunity product line items. An admin might need to flip the approved flag at the product level after the fact allowing the prospect product to be included in the opportunity forecast and bypassing this restriction.

  • Welcome to the StackExchange ! You wouldn't even need a rollup, a validation rule on Opp Product which checks the probability of the Opportunity when ISNEW() should suffice. I'd always recommend using config where possible. (Code means also write tests to deploy) – techtrekker Nov 8 '12 at 23:26
  • what is the issue with validation rules you see!!! I think this is easily doable with a validation rule!!! AND( AND(NOT(ISBLANK($RecordType.Name)),$RecordType.Name='ABC') ,( Probability > 0.7), ISPICKVAL(producttable__c.allowed_field__c,'Allowed')) (assuming allowed_field__c is a picklist) This should prevent the record from saving!!! – Rao Nov 8 '12 at 23:28
  • 1
    The problem is I need to check the record type on the opportunity product, not the opportunity itself. I didn't think I could run the validation rule on the opp to look at the line items. – JustAGirlyGeek Nov 8 '12 at 23:30
  • 1
    I don't think I explained my situation entirely (or I am not thinking this through correctly). That might prevent users from adding additional products but I need users to not be able to move the sales stage with existing prospect products. – JustAGirlyGeek Nov 8 '12 at 23:33
  • 2
    I was playing around with the Rollup and validation rule but I might have been overthinking the entire thing. I was also having trouble with the approved flag and how to properly set this whole thing up without having any unnecessary configuration. If we update the flag on the product table, how will it update the opportunity product record? Or will a simple formula field work with the validation rule? – JustAGirlyGeek Nov 8 '12 at 23:42
3

EDIT: It seems it has to be something more or less like that after all :) You'd best include all these uncovered requirements in the question so it will have some value for people who will search later.

(marked as community wiki so anybody can hack at it if they have better ideas)

trigger OpportunityValidations on Opportunity(before update){

    // Start with building a list of suspicious opportunities to be checked in greater detail
    Set<Id> oppsToBeChecked = new Set<Id>();
    for(Opportunity o : trigger.new){
        if(o.Probability >= 70){
            oppsToBeChecked.add(o.Id);
        }
    }

    /*  Most of the time we probably won't have to query for more info (if opps below 70% are edited for example) but if we have to...
        We'll use a "subquery" for all Products related to each suspect opp.
        - error if there are no products at all
        - error if at least one product we have found is a prospect product that wasn't yet approved by SAP team

        (Because of this "error if there are no products" requirement we had to move out #2 checks from WHERE clause to actual Apex Code. 
        Another route would be to run a separate query to check if any of opps don't have products but it's a waste of a query and in trigger context we have only 20 available.)
    */
    if(!oppsToBeChecked.isEmpty()) {
        for(Opportunity o : [SELECT Id, 
                (SELECT Id, PriceBookEntry.Product2.RecordType.DeveloperName, PriceBookEntry.Product2.Approved__c
                FROM OpportunityLineItems
                ORDER BY PriceBookEntry.Product2.Approved__c) // little trick to get the ones with "false" sorted first
            FROM Opportunity
            WHERE Id IN :oppsToBeChecked]){

            Opportunity originalOpp = trigger.newMap.get(o.Id); // we'll use this reference to original to add error to it if needed
            if(o.OpportunityLineItems.isEmpty()){
                originalOpp.addError('Opportunity shoould have at least one product attached to it before progressing further.');
            } else {
                for(OpportunityLineItem oli : o.OpportunityLineItems){
                    // Note I've modified the check to verify DeveloperName (it's less likely to change in future but change it back if you need)
                    if(oli.PriceBookEntry.Product2.RecordType.DeveloperName == 'Prospect_Product' && oli.PriceBookEntry.Product2.Approved__c == false){
                        originalOpp.addError('At least one product is a "prospect product" that\'s not yet approved, this Opportunity cannot progress. Please double check product ' + URL.getSalesforceBaseUrl().toExternalForm() + '/' + oli.Id);
                        break; // Exit the loop - even if there are more products, we are sure we should prevent "this" Opp from saving
                    }
                }
            }
        }
    }
}

As to why it can't be a true "no software" solution with rollup summary and a validation rule that would look at "count of unapproved prospect products":

  1. It has to look at Product2 table and 2 values there - RecordType.Name and Approved__c.
  2. None of these are available in the rollup filter criteria (even if I'd have the custom field that is), which makes sense actually, you should filter on fields on OpportunityLineItem only
  3. You can't cheat by making a formula field pointing to Product2 too:
    • because formula field creator on OpportunityLineItem has only the "special $Fields" available and other fields on the product. Can't go through lookup to Pricebook or Product
    • and anyway when at some point admin would tick the "approved" checkbox the formula would not change (ok, it would - but it would not trigger an update that's needed for rollup to recalculate)
  4. It could be an option to have a trigger that would cascade tick checkbox on Opp Line Items when admin ticks the master checkbox on Product... but it will update only existing Opps, what about new ones? Another trigger when Opp Line Items are created? And then what when 5 mins later admin realizes she made a mistake and needs to untick it?
  • 1
    That's because I was too sleepy ;) Probability field is actually a 0-100 range (I thought it behaves like other fields of type percent that display nicely but actually deep in the database are between 0 and 1). Fixed now. Can you update the code to use Product2.RecordType.DeveloperName = 'Prospect_Product' or whatever is appropriate for you (the "API name", that converts spaces to underscores etc). That's because Names can be changed or translated later. DevName is safer to rely on in code. – eyescream Nov 9 '12 at 7:54
  • Brilliant! Works perfectly, thanks! I didn't think to check the .70 in your code because that is what tripped me up in my validation rule. I was using 70 and the correct logic was to use .70. All updated and works great! Now what are the differences between your code and what tecktrekker wrote up above? Also, any tips on test coverage? I really appreciate everyone's help with this!!! – JustAGirlyGeek Nov 9 '12 at 14:24
  • 1
    Glad you like it! Net result should be the same. It's personal pref. really - I run basic checks on the data I have before querying for more (in this case - most of the time opps will be < 70% but @techtrekker's solution ALWAYS uses 1 query). There's limit of max 20 queries when triggers are at play... Re: unit test - sadly opportunity line items are very painful to test. stackoverflow.com/questions/9164986/… I have sample code but can't post it, intelectual property etc etc... Maybe make new question? – eyescream Nov 9 '12 at 23:21
  • In reviewing data we found users have opportunities with 70% or greater probability and NO products :( is there a condition we can add to your trigger to prevent users from progressing the opportunity if they don't have any products listed with perhaps a different error stating this reason? – JustAGirlyGeek Nov 14 '12 at 19:31
  • @JustAGirlyGeek how I love scope creep ;) Check the updated version? Also - we probably should delete some of the comments that have been addressed, it's not a place for lengthy discussions. – eyescream Nov 14 '12 at 20:29
4

I'd advocate the Great Rollup Summary Trick, but if you must have a trigger :

trigger OpportunityBeforeUpdate on Opportunity (Before Update){

    for(Opportunity opp : [Select Id, Name, (Select Id from OpportunityLineItems where PriceBookEntry.Product2.RecordType.Name='OFFENDING RT NAME') from Opportunity where Probability >= 0.7 AND Id in Trigger.New])
    if(opp.OpportunityLineItems != null && opp.OpportunityLineItems.size() > 0)
    opp.addError('You cannot progress this Opportunity');

    }
  • 1
    I'd suggest adding another filter to the where clause to only check Opportunities where ID IN :Trigger.new. – Daniel Ballinger Nov 9 '12 at 0:24
  • Getting the following error with this code: Error:Apex trigger OpportunityBeforeUpdate caused an unexpected exception, contact your administrator: OpportunityBeforeUpdate: execution of BeforeUpdate caused by: System.FinalException: SObject row does not allow errors: Trigger.OpportunityBeforeUpdate: line 7, column 1 – JustAGirlyGeek Nov 9 '12 at 2:50
  • 1
    But of course, cheers Dan. Was too late at night :) – techtrekker Nov 9 '12 at 5:54

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.