I have to find out which Opportunities have more than 1 line item. I tried doing this by making a custom report but that did not work. I was able to see which Opportunities had line items, but not how many.

I am not sure if maybe I could run some queries to get this?

  • Add a Roll up Summary field to Opportunity that counts the line items; then you can report on those where count > 1 – cropredy Jun 30 '17 at 17:41
  • welcome to SFSE, please take some time to review How to Ask and take the tour. Restrain from asking for help and adding thanks in your posts in the future. Be sure to include what you have attempted and researched documentation/quotes to avoid having others propose already tried solutions. Thanks! – glls Jun 30 '17 at 17:44
  • check my answer in case it helps – Santanu Boral Jul 1 '17 at 2:55

If you run the following query in developer console query editor. You'll get list of all the opportunities which have opportunitylineitem.

select name, id, (select quantity,  pricebookentry.name, listprice, pricebookentry.unitprice from opportunitylineitems) from opportunity

To find out only those opportunities which have more than one opportunitylineitem. We'll need to loop on the results. And you'll need to run the following snippet in execute anonymous apex window.

List<Opportunity> opps = [select name, id, (select quantity,  pricebookentry.name, listprice, pricebookentry.unitprice from opportunitylineitems) from opportunity];
for(Opportunity o : opps) {
    if(o.opportunitylineitem.size() > 1){

I hope this will work for you. Only if you don't have as many records as the aggregate query limit says, which is 200 or 250.

As @Derek suggested in comments. I'm making it part of my answer.

No worries for aggregate query error now

for( Opportunity opp : [select name, id, (select quantity,  pricebookentry.name, listprice, pricebookentry.unitprice from opportunitylineitems) from opportunity])
   List<OpportunityLineItem> oppLineItems;
      oppLineItems = opp.opportunitylineitem;
   catch( QueryException e )
      oppLineItems = new List<OpportunityLineItem>();
      // Here's the 'FOR loop' the exception message says we should use
      // Within the outer SOQL for loop, this for loop can access the 
      // complete list of child oli records
      for( OpportunityLineItem oli : opp.opportunitylineitem )
         oppLineItems.add( oli );

   // Now we can use the 'oppLineItems' List however we want. 
   System.debug( opp.name + ' : ' + oppLineItems.size() );
| improve this answer | |
  • One thing to be aware of with this solution is that if you query an Opportunity with more than ~200 OpportunityLineItems, you're likely to run into the Aggregate query has too many rows for direct assignment, use for loop error. If you run into this error, you'll need to write an inner loop over the child records (o.OpportunityLineItems in this case) and store them in a temporary list, or a Map<Id, List<OpportunityLineItem>> to get the count. – Derek F Jun 30 '17 at 19:22

You could write a query to do this, but there is an easier way to do this.

You can use a Rollup Summary field to easily (without code, and without the testing that would be required if you were to use code) aggregate data from OpportunityLineItem on Opportunity.

The COUNT operation is what you're looking for here.

| improve this answer | |
  • I created a Rollup Summary field aggregating data from line item on opportunity. i used count also. i made the filter line item greater than 1. i am still not having any luck. any idea what is wrong? – Kate Coppola Jun 30 '17 at 19:07
  • also, line item is under quote and not opportunity. is that normal? – Kate Coppola Jun 30 '17 at 19:08
  • @KateCoppola I think that filter you put in is the issue. The filter is meant to select which detail records (in a Master-Detail relationship, Opportunity being the 'Master' side in this case, and OpportunityLineItem being the 'detail') that you want to count in the rollup (and it sounds like you want to be counting all of them). – Derek F Jun 30 '17 at 19:11
  • @KateCoppola Also, this only counts OpportunityLineItem records, what you have are QuoteLineItem records. You can set up quote syncing and have QuoteLineItems automatically create OpportunityLineItems, but this is quickly heading into the territory where you should ask another question, or edit this question to include more details. – Derek F Jun 30 '17 at 19:14

In my opinion, efficient use of Aggregate query will be as follows:

SELECT OpportunityId, COUNT(Id)
FROM OpportunityLineItem
GROUP BY OpportunityId
LIMIT 2000

Since, Aggregate query does not support queryMore(), take one of the following approach. Refer Aggregate queries and Batch Apex

  • Run the "Aggregate Query" in a schedule class by using @Read annotation.
  • Implementing Iterable interface instead of Query Locator in batch class
| improve this answer | |

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.