Opportunity Management

Question

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.

Answer 1

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?

Answer 2

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');

    }