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
Nitin Palmure 5Nitin Palmure 5 

unable to use Set in Where clause of SOQL

I am executing following code as execute anonymous 
Competition__c Member = [SELECT Team_Members__c FROM Competition__c WHERE Id =: 'a4218000000T20V' ];
Id id='a4218000000T20V';
String[] tmpString = Member.Team_Members__c.split('\n');
Map<String, String> memberNames = new Map<String, String>();
for(String s: tmpString){
	//system.debug('s: ' +s);
	memberNames.put(s,s);  
}
system.debug(memberNames);


Set<String>cName = memberNames.keySet();
List<Contact>email = [SELECT Email FROM Contact where Name  LIKE : cName];
system.debug('email ' +email);
system.debug('cName: ' +cName);

Here the output of  system.debug('email ' +email); is the last email id that 'cName' is holding.
for example 'cName' has value as cName: {Member1, Member2, Member3}
However system.debug('email ' +email); gives output as email (Contact:{Email=Member3@AMC.com, Id=0037000001XaXXXXX3, RecordTypeId=012700000009oXXXXX, CurrencyIsoCode=USD})

I am expecting the output as email ids of all the members.
Please help me understand where I am going wrong.
Best Answer chosen by Nitin Palmure 5
Nitin Palmure 5Nitin Palmure 5
I found the answer to it. 
Following is the updated script. 
Competition__c Member = [SELECT Team_Members__c FROM Competition__c WHERE Id = 'a4218000000T20V' ];
System.debug('Member >> '+Member);
String[] tmpString = Member.Team_Members__c.split('\n');
System.debug('tmpString >> '+tmpString);
List<String> listOfNames= new List<String>();

for(string str: tmpString){
     listOfNames.add(str.trim());
}
system.debug('listOfNames >> '+listOfNames);
List<Contact> email = [SELECT Name, Email FROM Contact where Name In : listOfNames];
System.debug('email >> '+email);

 

All Answers

Raj VakatiRaj Vakati
try like this and you no need to use :
  
Competition__c Member = [SELECT Team_Members__c FROM Competition__c WHERE Id ='a4218000000T20V' ];
Id id='a4218000000T20V';
String[] tmpString = Member.Team_Members__c.split('\n');
Map<String, String> memberNames = new Map<String, String>();
for(String s: tmpString){
	//system.debug('s: ' +s);
	memberNames.put(s,s);  
}
system.debug(memberNames);


Set<String>cName = memberNames.keySet();
List<Contact>email = [SELECT Email FROM Contact where Name  LIKE : cName];
system.debug('email ' +email);
system.debug('cName: ' +cName);

 
Nitin Palmure 5Nitin Palmure 5
Hello, 

Thank you for response. 
However, I did not se the difference between both the codes.
Also, the output that I am receiving is the same. 
Any changes that you would like to suggest on this code ?

Thank you
 
Shubham_KumarShubham_Kumar
Hi
Could you try the code below. 
Competition__c Member = [SELECT Team_Members__c FROM Competition__c WHERE Id =  'a4218000000T20V' ];
//Id id='a4218000000T20V';
String[] tmpString = Member.Team_Members__c.split('\n');
Map<String, String> memberNames = new Map<String, String>();// a set or list could have served the purpose too.
for(String s: tmpString){
    //system.debug('s: ' +s);
    memberNames.put(s,s);  
}
system.debug(memberNames);

Set<String>cName = memberNames.keySet();
if(!memberNames.isEmpty())
for(String s : memberNames.keySet()){
cName.add('%'+s+'%');//Modify the WildCards according to your requirement
}


if(cName.size()>0)
List<Contact>email = [SELECT Email FROM Contact where Name  LIKE :cName];
system.debug('email ' +email);
system.debug('cName: ' +cName);


I hope this helps.
 
Ajay K DubediAjay K Dubedi
Hi Nitin,
Try this code: 
Competition__c Member = [SELECT Team_Members__c FROM Competition__c WHERE Id = 'a4218000000T20V' ]; //Remove ':'
Id id='a4218000000T20V';
String[] tmpString = Member.Team_Members__c.split('\n');
Map<String, String> memberNames = new Map<String, String>();
for(String s: tmpString){
    //system.debug('s: ' +s);
    memberNames.put(s,s); 
}
system.debug(memberNames);
Set<String>cName = memberNames.keySet();
List<Contact>email = [SELECT Email FROM Contact where Name  IN: cName]; //Tyr to use IN insted of LIKE.
system.debug('email ' +email);
system.debug('cName: ' +cName);

I hope you find the above solution helpful. If it does, please mark as Best Answer to help others too.
Thanks,
Ajay Dubedi
Nitin Palmure 5Nitin Palmure 5
Hi Shubham, 

The code you suggested is not adding value to cName due to which size is going to 0.
Thanks for the help.

Hi Ajay,

Is giving me the same output that I was getting with the code I wrote. 
It is taking only last value. For some reason all the values are not being stored in cName variable.

Regards
Nitin V Palmure
Shubham_KumarShubham_Kumar
Hi Nitin

Sorry I misunderstood your question earlier.
Salesforce automatically includes id and RecordTypeId(if the object has one) and CurrencyISOCode(if your Org has Multicurrency enabled) in the Query.

Please mark this reply as best answer if it helped you.

Thanks
Shubam Kumar
 

 
Nitin Palmure 5Nitin Palmure 5
Hi Shubham 

Thank you for the response. 
My problem is that the cName has values as 
cName: {Member1, Member2, Member3}
however email holds details about Member3 only, where as it is not storing details regarding Member1 and 2 ?
I am finding a way where I will be able to get email of all the members.
I saw there is a way to query in the for loop to check the email and add it to a set, however its not the best practice, hence looking for some solution.

Regards,
Nitin V Palmure
Shubham_KumarShubham_Kumar
ohk, then you should try this. I have removed size check and it should work now.
  
Competition__c Member = [SELECT Team_Members__c FROM Competition__c WHERE Id =  'a4218000000T20V' ];
//Id id='a4218000000T20V';
String[] tmpString = Member.Team_Members__c.split('\n');
Map<String, String> memberNames = new Map<String, String>();// a set or list could have served the purpose too.
for(String s: tmpString){
    //system.debug('s: ' +s);
    memberNames.put(s,s);  
}
system.debug(memberNames);

Set<String>cName = memberNames.keySet();

for(String s : memberNames.keySet()){
cName.add('%'+s+'%');//Modify the WildCards according to your requirement
}
 system.debug('cName: ' +cName);

List<Contact>email = [SELECT Email FROM Contact where Name  LIKE :cName];
system.debug('email ' +email);

 
Nitin Palmure 5Nitin Palmure 5
Hi Shubham, 

I am trying to get Team Member name from a Text Field which are separated by newline.
Take their name, query in contacts and get the email id.
This is what I want to achieve. I tried it using above script. But it did not help.
Please help if there is some other way.

Thank you.
Nitin Palmure 5Nitin Palmure 5
I found the answer to it. 
Following is the updated script. 
Competition__c Member = [SELECT Team_Members__c FROM Competition__c WHERE Id = 'a4218000000T20V' ];
System.debug('Member >> '+Member);
String[] tmpString = Member.Team_Members__c.split('\n');
System.debug('tmpString >> '+tmpString);
List<String> listOfNames= new List<String>();

for(string str: tmpString){
     listOfNames.add(str.trim());
}
system.debug('listOfNames >> '+listOfNames);
List<Contact> email = [SELECT Name, Email FROM Contact where Name In : listOfNames];
System.debug('email >> '+email);

 
This was selected as the best answer