function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
kcharubinkcharubin 

SOQL - WHERE IN Clause

I am working on a Lead Request system.

Every user has a Lead Center record from which they can manage all their leads and submit Lead Requests.

Each user is only responsible for some set of states.

 

I am trying to make the process of finding the leads for a given request automatic

In the code below I pass in the Lead Request ID as a parameter using a button.

Using the Lead Request ID I then run a query to find the Lead Request Record.

 

On the Lead Request Record there is a text field which lists all the states that a given user is responsible for.

In the findLeads() function below I am trying to run a query on Consumer_Leads__c to find the requested number of leads 

that are in the states listed on the Lead Request.

 

My code work if there is only one state listed.

The IN Clause works in such a way that the parameters need to be single quoted and comma separated

 

If there is only one state listed then the string looks like 'FL' which is in the proper syntax

The problem occurs when there are multiple states listed, the string the looks like 'FL,CT,TX' which is incorrect format

For the IN Clause to accep the input it would have to be 'FL','CT','TX'

 

I would really appriciate if someone could help me figure out how to convert the text field listing the states into the proper format that the IN Clause would accept.

 

This is the code I have so far, Currently it only works if there is only one state listed in the Territory__c text field on the Lead Request

 

public class MyLeadsController {

    public List<Consumer_Leads__c> allLeads {get; set;}
    public String leadRequestID {get; set;}
    public Integer numLeads {get; set;}
    
    public MyLeadsController(ApexPages.StandardController controller) {
    
        leadRequestID = ApexPages.currentPage().getParameters().get('lc');
        
    }
    
    public void findLeads(){
        Lead_Request__c leadRequest = [select id, Number_of_Leads_Requested__c, Territory__c, Lead_Center__c
                                    from Lead_Request__c
                                    where id =: leadRequestID];
        
        allLeads = [select Lead_Center__c, Lead_Request_NEW__c, First_Name__c, Last_Name__c, State__c, City__c, ZIP_Code__C, Lead_Source__c, CreatedDate
                    from Consumer_Leads__c
                    where Lead_Center__c = 'a19Q0000001OaRM' AND State__c IN (:leadRequest.Territory__c)
                    limit :(Integer)leadRequest.Number_of_Leads_Requested__c];
                    
       numLeads = allLeads.size();
    }
}

 

Best Answer chosen by Admin (Salesforce Developers) 
PriyasoftPriyasoft

 Try this....

 

public class MyLeadsController {

    public List<Consumer_Leads__c> allLeads {get; set;}
    public String leadRequestID {get; set;}
    public Integer numLeads {get; set;}
    
    public MyLeadsController(ApexPages.StandardController controller) {
    
        leadRequestID = ApexPages.currentPage().getParameters().get('lc');
        
    }
    
    public void findLeads(){
        Lead_Request__c leadRequest = [select id, Number_of_Leads_Requested__c, Territory__c, Lead_Center__c
                                    from Lead_Request__c
                                    where id =: leadRequestID];
        
List<string> lstStates = new List<string>();
if(leadRequest.Territory__c!=null)
{
string[] arrTerr = leadRequest.Territory__c.split(',');
for(string state:arrTerr)
{
lstStates.add(state.trim());
}
} allLeads = [select Lead_Center__c, Lead_Request_NEW__c, First_Name__c, Last_Name__c, State__c, City__c, ZIP_Code__C, Lead_Source__c, CreatedDate from Consumer_Leads__c where Lead_Center__c = 'a19Q0000001OaRM' AND State__c IN :lstStates limit :(Integer)leadRequest.Number_of_Leads_Requested__c]; numLeads = allLeads.size(); } }

All Answers

PriyasoftPriyasoft

 Try this....

 

public class MyLeadsController {

    public List<Consumer_Leads__c> allLeads {get; set;}
    public String leadRequestID {get; set;}
    public Integer numLeads {get; set;}
    
    public MyLeadsController(ApexPages.StandardController controller) {
    
        leadRequestID = ApexPages.currentPage().getParameters().get('lc');
        
    }
    
    public void findLeads(){
        Lead_Request__c leadRequest = [select id, Number_of_Leads_Requested__c, Territory__c, Lead_Center__c
                                    from Lead_Request__c
                                    where id =: leadRequestID];
        
List<string> lstStates = new List<string>();
if(leadRequest.Territory__c!=null)
{
string[] arrTerr = leadRequest.Territory__c.split(',');
for(string state:arrTerr)
{
lstStates.add(state.trim());
}
} allLeads = [select Lead_Center__c, Lead_Request_NEW__c, First_Name__c, Last_Name__c, State__c, City__c, ZIP_Code__C, Lead_Source__c, CreatedDate from Consumer_Leads__c where Lead_Center__c = 'a19Q0000001OaRM' AND State__c IN :lstStates limit :(Integer)leadRequest.Number_of_Leads_Requested__c]; numLeads = allLeads.size(); } }
This was selected as the best answer
kcharubinkcharubin

Thank You!

I am a beginer developer and I have spent a few days trying to figure this out.

Your code works great!