+ Start a Discussion
Vipin  PulinholyVipin Pulinholy 

SoQL Query Help - OpportunityContactRole



I am trying to write a query to get information from OpportunityContactRole. But this need to be joined with Opportunity & Account. Here is what I want.


Get all the OpportunityContactRole Id from OpportunityContactRole
Where Opportunity.Account.PersonContactId=OpportunityContactRole.ContactId and Opportunity.Id=OpportunityContactRole.OpportunityId


I am sure whether this is possible in soql. Can any one please help/guide me to write a query to get the required result?


Thank You.


You can do that, but you have to reference all of it from the OpportunityContactRole, like


Select o.Contact.AccountId, o.ContactId,o.Id,o.Contact.Account.Name From OpportunityContactRole o where o.Opportunity='xxx'


I think you can go as many as 5 levels deep with those parent relationships.  Use the query builder in Force.com Explorer or in the Force.com IDE to help you.

Vipin  PulinholyVipin Pulinholy

Thanks for the input.


I tried that, but I need a some thing like this.


Select o.Contact.AccountId, o.ContactId,o.Id,o.Contact.Account.Name,o.Contact.Account.PersonContactId From OpportunityContactRole o where o.OpportunityId=o.Opportunity.Id and o.ContactId=o.Contact.Account.PersonContactId


How we can achive this join (where o.OpportunityId=o.Opportunity.Id and o.ContactId=o.Contact.Account.PersonContactId)


Can we do it any way?


That makes no sense.  Are you just trying to identify OpportunityContactRoles where the contact is a person account?  Then just say


where o.Contact.IsPersonAccount=true


Whatever fields you need from the corresponding Account part of the person account, you can get to via o.Contact.Account.

Vipin  PulinholyVipin Pulinholy

I am trying to get all OpportunityContactRoles Id's (a report) where the contact added on Opportunity Contact Roles is same as the Account (Perosn Account) on that Opportunity. For example, Say I have Person Account Called ' Das' and this has an oppportunty created called 'Das -Opportunity' and user added a contact role to this opportunty with the same 'Das' (Person Account). Then I want to get the id of this OpportunityContactRole.


Our requirment is to delete all such contact role (we have lot) from OpportunityContactRoles . So I was thinking to get a report and use the dataloader to mass delete all this OpportunityContactRoles .


Please let me know if my explanation is not clear.

Well, I'm not sure you'll be able to do that in a single query then -- you'll have to do it in 2 steps, because you can't presently compare a field value to another field value in SOQL.