function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Lesley BoydLesley Boyd 

SOQL query syntax

How can I write a SOQL query to ID the opportunity contact roles where the contact account doesn't equal the opportunity account?  I'm trying to clean up the opportunity contact roles that were migrated in from another CRM and associated to the wrong opportunity.
SandhyaSandhya (Salesforce Developers) 
Hi Lesley Boyd,

Please refer below link which has SOQL query it may help you.

https://developer.salesforce.com/forums/?id=906F00000008zWVIAY
 
Hope this helps you!

If this helps you please mark it as solved.

Thanks and Regards
Sandhya
SwarnaSankhaSinghSwarnaSankhaSingh
Hi Lesley,

You will not be able to achieve this in a single SOQL statement but will need a code snippet. I have added a few lines of code which will get you the desired result: 
List<Id> oppCRRecMismatchList = new List<Id>();

Map<Id, Opportunity> mapOfOppRecs = new Map<Id, Opportunity>([Select Id, AccountId From Opportunity Where ID IN (SELECT OpportunityId FROM OpportunityContactRole)]);

Map<Id, Contact> mapOfConRecs = new Map<Id, Contact>([Select Id, AccountId From Contact Where ID IN (SELECT ContactId FROM OpportunityContactRole)]);

for(OpportunityContactRole oppCRRec : [SELECT Id, OpportunityId, ContactId FROM OpportunityContactRole])
{
    if(mapOfOppRecs.get(oppCRRec.OpportunityId) != NULL && mapOfConRecs.get(oppCRRec.ContactId) != NULL && mapOfOppRecs.get(oppCRRec.OpportunityId).AccountId != mapOfConRecs.get(oppCRRec.ContactId).AccountId)
    {
    	oppCRRecMismatchList.add(oppCRRec.Id);
    }
}

System.Debug('Value of oppCRRecMismatchList --> ' + oppCRRecMismatchList);
I have deliberately not included the LIMIT clause as I am not sure what is the volume of data for your org. You will need to insert the LIMIT clauses to each of the three Queries being used. In my view if your dataset is a good volume then it is better for you to perform a data export, apply manual cleansing logics and then perform the needed updates/deletions because I am guessing this cleansing will be a one time activity.

I hope this helps; please do let me know what you decided to move ahead with and how it worked out for you. If you feel that your question was answered then do flag the appropriate answer as the solution to your query.

Kind Regards,
Swarna.