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:
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 addName
to the subquery on OLI. If a list, you want to useIN
instead of=
. Assuming you're using lists (the query should be bulkified), the revised query would look like this:You'll notice that I've added the
CreatedBy
and theLastModifiedBy
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 theCreatedBy
or theLastModifedBy'
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.