+ Start a Discussion
@anilbathula@@anilbathula@ 

How to query for opportunity from contact

hi guys,

 

Please help me in with the query.

My contact will have only one opportunity  1-1 relation ship:-1 account ,1 contact 1, opportunity.

 

  for(Comments__c cs:trigger.New){
        cms=cs;    
        if(cms.Opportunity__c==null && cms.Contact__c!=null ){
            sobjectSetOfIds.add(cms.Contact__c);           
        }
    }   
    
    Map<Id,Contact>smap1= new Map<Id, Contact>([Select id
                                                        ,Name
                                                        ,Opportunities__r.id // error  
                                                         from Contact 
                                                         where Id in : sobjectSetOfIds]);
                
    for(Comments__c s: trigger.new){
        if(smap1.containsKey(s.Contact__c)){        
            s.Opportunity__c=smap1.get(s.Contact__c).Opportunities.id;    //error
                
        }
    }

 

Thanks

Anil.B

 

cduncombe44cduncombe44

Even though your Contact may only have 1 opportunity, the Opportunities__r returns a list of opportunities, even if that list is only 1 opportunity, you will get an error because you are trying to get the ID of a list, not an Opportunity SObject.  Try something like

 

 Map<Id,Contact>smap1= new Map<Id, Contact>([Select id
                                                        ,Name
                                                        ,Opportunities__r[0].id
                                                         from Contact 
                                                         where Id in : sobjectSetOfIds]);

 

and 

 

s.Opportunity__c=smap1.get(s.Contact__c).Opportunities__r[0].id; 

 

--Chris

 

 

@anilbathula@@anilbathula@

Hi chris

 

Thanks to ur reply .

 

But its giving error in that query.

Error :Unexpected token [

 

Thanks

Anil.B

kerwintangkerwintang

I'm trying to understand your overall requirement.

 

Do you want to get the Opportunity of the Contact from the Comment record? If so, you don't need to create a Map. Try this one:

 

  for(Comments__c cs:trigger.New){
        cms=cs;    
        if(cms.Opportunity__c==null && cms.Contact__c!=null ){
            List<Opportunity__c> opportunities = [select Id, Name from Opportunity where Contact.Id = :cms.Contact__c];

            for(Opportunity opportunity:opportunities){

                cs.Opportunity__c = opportunity;

            }
        }
    }  

@anilbathula@@anilbathula@

Hi Kerwintang

 

Thanks for ur post.

 

I used ur code its giving error.

Error:-Didn't understand relationship 'Contact' in field path. If you are attempting to use a custom relationship, be sure to append the '__r' after the custom relationship name. Please reference your WSDL or the describe call for the appropriate names. at line 26 column 51.

 

Then i modified it as Contact__r.id .

Same error.

 

Thanks

Anil.B


kerwintangkerwintang

Sorry about that. try this one:

 

  for(Comments__c cs:trigger.New){
        cms=cs;    
        if(cms.Opportunity__c==null && cms.Contact__c!=null ){
            List<Opportunity__c> opportunities = [select Id, Name from Opportunity where Account__r.Id = :cms.Contact__r.Account__c];

            for(Opportunity opportunity:opportunities){

                cs.Opportunity__c = opportunity;

            }
        }
    } 

@anilbathula@@anilbathula@

Hi Kerwintang

 

Thanks a lot

 

Ur code helped me a lot actually the query will be like this.

 

for(Comments__c cs:trigger.New){
cms=cs;
if(cms.Opportunity2__c==null && cms.Contact2__c!=null ){
List<Opportunity> opportunities = [select Id, Name from Opportunity where Account.Id = :cms.Contact__r.Account.id];

for(Opportunity opportunity:opportunities){
cs.Opportunity__c = opportunity.id;
}
}
}

But i used the query in a different manner where i had a lookup field on to contact from opportunity .

I used that filed and done my query.

List<Opportunity> opportunities = [select Id, Name from Opportunity where Borrower__r.Id  = :cms.Contact__c];

 

Here i want to know one thing if there are two oppty for that contact which id it will pass to the opportunity__c filed.

 

Thanks

Anil.B

kerwintangkerwintang

Using that given code, the opportunity that will be used will be the last record retrieved in the list. :)

@anilbathula@@anilbathula@

Hi Kerwintang

 

U mean the last modified opportunity record.

 

Thanks man    Thanks a lot  ....

 

 

Thanks

Anil.B

kerwintangkerwintang

No problem, glad to help

cduncombe44cduncombe44

This code may work, but its going to hit governer limits and crash out if you have a batch upload of more than 100.  This is caused by the SOQL in the For loop.  Thats why you need the map, to avoid the SOQL statement inside of the for loop.

 

Like I said, this will work for small batch's, but anything over 100, and it will hit the governer limits and crash out.  

@anilbathula@@anilbathula@

Hi chris,

 

 

Then how to over come this problem.

Do u have any other way to help me.

 

Thanks

Anil.B

kerwintangkerwintang

Move the query outside by placing it inside a Map with AccountId as the key and list of oppportunities as value. Then you can search through that Map in the Comments loop:

 

Map<String, List<Opportunity>> opportunityMap =new Map<String, List<Opportunity>>();

List<Opportunity> opportunities = [select Id, Name, Account.Id from Opportunity];

for(Opportunity opportunity:opportunities){

   List<Opportunity> oppList = opportunityMap.get(opportunity.Account.Id);

    if(oppList==null){

        oppList = new List<Opportunity>();

    }

    oppList.add(opportunity);

    opportunityMap.put(opportunity.Account.Id, oppList);
}
for(Comments__c cs:trigger.New){
cms=cs;
if(cms.Opportunity2__c==null && cms.Contact2__c!=null ){

    List<Opportunity> oppList = opportunityMap.get(cms.Contact__r.Account.Id);

    if(oppList!=null){

        for(Opportunity opportunity:oppList){
            cs.Opportunity__c = opportunity.id;
        }

    }

}
}