+ Start a Discussion

SOQL query to find and email team member who have no quotes on opportunity ?

I need a query to fetch Team Members who have no quotes on Opportunity and send email to those..

Plz give the solution
if it is for one Opportunity
SELECT UserId, user.name, user.email,opportunityid FROM OpportunityTeamMember where UserId not in (select createdbyid from quote where opportunityid=:id) and opportunityid=:id

to do for Bulk, you have to use two soql and some custom logic.

1) run the below Aggregate SOQL and put the count in a map with key as <opportunityid>_<createdbyid>
select opportunityid, createdbyid, count(id) from Quote group by opportunityid,createdbyid

2) Get all Opportunity team members.
SELECT id,(select userid, user.name, user.email from OpportunityTeamMembers) from Opportunity where id in : idList

Iterated the result and find the value exist in the Map for <opportunityid>_<userid>, if not then send email.