+ Start a Discussion
ron_reedron_reed 

Opportunity Object - counting number of attached Products that meet a certain criteria

I'm fairly new to apex development and trying to write my first trigger.  I would like to count the number of products that fit a certain criteria and enter that into an Opportunity field.  I have a formula field called Proprietary_Count on the OpportunityLineItem object that returns either a 1 or 0 if the attached Product meets a criteria (Product_Type = "Proprietary") and I'm trying to count those up and insert that in the Opportunity field also called Proprietary_Count.  I can get it to work on a single update, but I know the way I'm doing it won't work on a bulk update.  Any ideas would be much appreciated!

 

 

trigger countProprietaryProjectsTrigger on OpportunityLineItem (after insert) { If (trigger.isinsert) { List<Id> opptyIds = new List<Id>{}; integer prodProps = 0; for(OpportunityLineItem oli: Trigger.new){ String OpportunityId = (String)oli.OpportunityId; if(oli.Approval_Proprietary_Count__C==1){ opptyIds.add(OpportunityId); prodProps = prodProps + 1; } } Opportunity[] opptysToUpdate = [select id, name, Approval_Proprietary_Count__C from Opportunity where Id IN :opptyIds]; for(Opportunity o: opptysToUpdate){ if (o.Approval_Proprietary_Count__C == null) { o.Approval_Proprietary_Count__C = 0; } o.Approval_Proprietary_Count__C = o.Approval_Proprietary_Count__C + prodProps;//just as an example } //Perform the update in a batch for al records update opptysToUpdate; } }

 

 

 

 

Message Edited by ron_reed on 02-02-2009 12:25 PM
SovaneSovane

Hi Ron_Reed,

I would suggest you go for a standard solution as you requirements allow it : RollupSummary fields will do the trick.

Message Edited by Sovane on 02-03-2009 12:21 AM
ron_reedron_reed

Hi Sovane,

I'm using a formula field in the opportunity product object and I'm not able to create a roll-up summary field in the opportunity object based on that formula field.  That's why I'm trying to write a trigger to get to that information.

Ron 

NikiVNikiV

If I understand correctly, you are tagging each OLI record with either a 1 if if the Product is "Proprietary" or 0 if it's not, and then you want a total number of "Proprietary" products on the Opportunity.  If you already have a formula field showing 1/0, could you not simply sum that formula field in a Roll up summary with no conditions and have the correct number of "Proprietary" products?  If that works then no trigger is required.  The help docs say you can build a roll up on a formula field as long as the formula is not using expressions like NOW or TODAY, so perhaps summing the OLI.Proprietary_count calculated field is possible.

 

Some thoughts on the trigger anyways:

  • You don't need the "if (trigger.isinsert)" since your trigger only fires on insert. 
  • You are not triggering an update after deletion so that may cause miscounts if someone deletes an OLI that was "Proprietary". 
  • Also, you may want to query all the OLI's linked to any Opp that is potentially being modified and recount the OLI number, rather than trying to add to the existing value, since if an OLI is deleted you are not updating the Opp value. 
  • To make it more bulk friendly, build a Map with Opp Id and the Opp sobject, then as you cycle through your triggered OLIs you can update that map with the total new Prop. count and then update the entire set of Opps in one shot.  

 

Hope that helps.

Message Edited by NikiV on 02-02-2009 10:04 AM
ron_reedron_reed

Hi NikiV,

Thanks for taking the time to reply.  I did plan on initially using a roll-up summary field, which is why I created the formula field first, but the formula field does not appear as an option in the "field to aggregate" selection box when creating a roll-up summary field.  I did read the help docs, and you are correct that it says it should be able to do it on a formula field, but unfortunately it doesn't allow me (wouldn't be the first time help has been wrong)

 

As far as my trigger goes, I do have a section of code that decrements the count if an opportunity product is deleted - I just didn't include it in my first message because the code is basically the same and if I got the Insert part right, I could use the same concept for the Delete part.

 

I guess the part I'm having a hard time with is your last point about the map.  I get the basic concept of a map and I had a feeling that's what I needed to do in my case - I'm just stuck trying to implement it.  Perhaps I'll take a break from it and have a go at it again later this week.

 

Ron

NikiVNikiV

Strange about the roll up summary issue.  I have several custom formula fields on a child that I'm able to sum on the parent through a lookup.  What does your child formula field look like?  Is it a number or maybe text/boolean?  In the use case you describe, I would think a number formula with a Case statement on the Type=Proprietary picklist field would end with a numerical result that could then be summed on the parent.

 

The maps can be a bit tricky but I found using the APEX help docs very useful.  I would loop through your trigger.new records to build a set of all Opp Ids (using set to avoid duplicate Opp ids) then loop through a query of all OLI's with Opportunity in :setofOppids.  Going through that list of OLI's you can populate a map of OppId:count by finding the OppId in the map and incrementing the count if it exists or adding a new entry in the map with a count of 1 if the OppId is not already in there.  Or something like that!

 

Good luck.

ron_reedron_reed

Hi Niki,

I think I've got what you're saying but I just need clarification on one thing - when I loop through the query of OLIs - that query will include all OLIs that exist in the set of OppIDs, not just the OLIs that are in trigger.new, correct?  If that's the case, I think I've got it.  Thanks, you've been very helpful.

NikiVNikiV

Yes, it will pull all OLI related to the Opps that the modified OLI's link to - that way you get the new total count, rather than trying to maintain the current count by adding/deleting to an existing number.  I think you would be more accurate that way. 

 

Remember to that on a delete trigger, there is no trigger.new, you need to refer to trigger.old to get the OppId value.  Conversely the insert trigger doesn't have trigger.old so you need to use the .new reference.  I usually deal with this by creating a new List variable of OLIs and then use an if statement to assign that variable with either trigger.old if trigger.isdelete = true or with trigger.new otherwise.

 

Have fun!