[SalesForce] Opportunities Team members and opportunity line item

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?

Best Answer

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.

Related Topic