1

Is there a way to query for the opportunity line item based on the opportunity team member?

Currently I am doing this :

for {
Select Opportunity.name, name from opportunityteammember where name = 'XYZ'
Variable = opportunity.name;
Select product2.id from opportunitylineitem where opportunity.name = :variable }

Its hitting a DML exception because the first query brings out some opportunities and the second fetches products for all those opportunities. Whereas, all i have to do is to get a product for that oppo team member. Is there any way I can match a opp line item to a opportunity team member?

4
  • Is opportunityteammember a custom object ?
    – SF_user
    May 22 '15 at 14:08
  • @KaustubhLabhe Are you trying to match each Opp line item to the Opp team member that created it? May 22 '15 at 16:18
  • Basically I am trying to fetch a Opp line item and teammember for just that opportunity. So, if I have a teammember name, I can get the respective opportunity but when I try to get the product name from that opportunity, it returns many results as opportunity name is generic. Thanks
    – KLab
    May 25 '15 at 9:17
0

From looking at the relationships, I think you're going to need to go through Opportunity and use a subquery to get what you're looking for in one query. Here's what I came up with:

map<Id,Opportunity>OppMap = new map([SELECT Id, Name (SELECT OpportunityId, UserId FROM 
OpportunityTeamMembers), 
  (SELECT Id, OpportunityId, PricebookEntryId FROM OpportunityLineItems) 
     FROM Opportunity WHERE OpportunityTeamMember.UserId = :variableNameOrList]); 

I put this in a map, but you can put it in a list if you want. If you use a map as above, you can more easily pull out the Line Items using a for loop with the Opp Ids.

Edit in response to comments

If the requirement is to return information related to both the opportunity team member name and the product name when one or the other is selected, it will still need to be done through Opportunity as the two can't be related or directly connected to one another in any other way. That's also the only way you'll be able to get the information all in one query.

BTW, when one selects a Name in the UI, it's usually linked directly to the ID through a look-up, so there shouldn't be any difficulty using ID instead Name in your query if Name is given from the UI.

Just change the WHERE clause above to WHERE (OpportunityTeamMember.UserId = :varNameOrList1 || OpportunityLineItem.Name = : varNameOrList2) and that should work for you. Also add Name to the subquery on OLI. If a list, you want to use IN instead of =. Assuming you're using lists (the query should be bulkified), the revised query would look like this:

map<Id,Opportunity>OppMap = new map([SELECT Id, Name, 
(SELECT Id, TeamMemberRole, User.Id, User.Name FROM OpportunityTeamMembers),
(SELECT Id, CreatedByID, LastModifiedById, OpportunityId, PricebookEntryId, 
 PricebookEntry.Name, PricebookEntry.ProductCode, IsDeleted FROM OpportunityLineItems) 
  FROM Opportunity WHERE 
  (OpportunityTeamMember.UserId IN :varList1 
   || OpportunityLineItem.Name IN : varList2)]); 

You'll notice that I've added the CreatedBy and the LastModifiedBy fields to the subquery on OLI. I think you'll want to run a for loop on what's returned to match up the results to make certain that the UserId returned for the Opp matches either the CreatedBy or the LastModifedBy'and also run a loop to make certain the Product returned matches the product returned for OLI.

I strongly suspect you'll get more returned than you're looking for and will need to sort out the additional results or at least match them up and put them into a map of User to Product.

2
  • Thank you for your reply. The Requirement goes like, the user enters the teammember name or the product name and then I have to fetch out the data related to both.
    – KLab
    May 25 '15 at 9:15
  • See my edited answer
    – crmprogdev
    May 25 '15 at 15:27
0

If you are trying to match the creator of the Opportunity Line Item with an Opportunity Team Member, you could do the following:

  1. Go to workbench.developerforce.com and login to the environment you need to get this information out of. Most likely it will be Production.

  2. Create an SOQL query for the Opportunity Line Item object something like the following:

    SELECT CreatedById,Id,IsDeleted,ListPrice,Name,OpportunityId,PricebookEntryId,Product2Id,TotalPrice FROM OpportunityLineItem WHERE Isdeleted=false
    
  3. Export it to Excel using the Bulk CSV option.

  4. Create an SOQL query for the OpportunityTeamMember object, something like the following:

    SELECT Id,IsDeleted,Name,OpportunityAccessLevel,OpportunityId,UserId FROM OpportunityTeamMember WHERE IsDeleted=False
    
  5. Export it out to Excel using the Bulk CSV option.

  6. Use VLookup to match the UserID on the OpportunityTeamMember spreadsheet to the CreatedByID field of the OpportunityLineItem. Return the value of the Name column on the OpportunityTeamMember spreadsheet to the OpportunityLineItem spreadsheet and you will have the opp team member names and the line items they created.

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.