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
Derek Davis 7Derek Davis 7 

How do I reference a variable/sting in a Where clause?

Hello,
I have an Apex Controller and a Visualforce page. My controller does several things, amoung them it gets values of a List; and those values are used to populate a Drop Down Field of Departments names on the Visualforce page. The list only shows departments that are related to a facility id. Below is an exerpt from my controller:

This Works: 
List<Department__c> Departments = [select id ,name from Department__c where Facility__c = '00160000010NjFa'] ;


This Doesn't Work: 
List<Department__c> Departments = [select id ,name from Department__c where Facility__c = {!myFacilityid} ] ;

The variable myFacilityid is populated in the controller (code to populate the variable not shown here, but I do know it's working because I reference this variable in a different searchquery as \''+myFacilityid+'\').

I also attempted to reference the variable as:
  • \''+myFacilityid+'\'
  • :myFacilityid
  • {!myFacilityid}
None of these attempts work.

How do I reference the variable in the Where clause shown above?
Best Answer chosen by Derek Davis 7
Mahesh DMahesh D
You can read the parameter value from your Controller:

varFacilityID

in the controller you can do this, so that the value will be set to myFacilityid.

myFacilityid= ApexPages.currentPage().getParameters().get('varFacilityID');

If required you can call search method also in the constructor for the first time otherwise just get the parameter value and assign it to myFacilityId.

Please do let me know if it helps you.

Regards,
Mahesh

All Answers

Deepak GulianDeepak Gulian
String myFacilityid = '00160000010NjFa';
List<Department__c> Departments = [select id ,name from Department__c where Facility__c =: myFacilityid] ;

This will work
Mahesh DMahesh D
Hi Derek,

In the variable is a primitive datat type and you want to compare it with field in a table then you can use below:

String myFacilityId = '00160000010NjFa';
List<Department__c> Departments = [select id ,name from Department__c where Facility__c =: myFacilityId] ;

If the variable is a Collection data type and you want to compare it with the fiend in a table then you can use below

Set<String> myFacilityIdSet = new Set<String>();
.....// Need to add the facility ids into it.
List<Department__c> Departments = [select id ,name from Department__c where Facility__c IN: myFacilityIdSet] ;

Please do let me know if it helps you.

Regards,
Mahesh
Amit Chaudhary 8Amit Chaudhary 8
Option 1
String myFacilityid = '00160000010NjFa';
List<Department__c> Departments = [select id ,name from Department__c where Facility__c = :myFacilityid ] ;

NOTE:- There should not be any space between : and myFacilityid

Let us know if this will help you
Mahesh DMahesh D
Hi Amit,

It doesn't matter whether you have a space or not, I tried both ways and both are working.

Regards,
Mahesh
Derek Davis 7Derek Davis 7
Hi Everyone! Thanks to each of you for your quick responces. Unfortunately, it's still not working. I am definitely still missing something.

Below is my full controller code. Line 31 was initially in question. Please let me know if you have any suggestions on how to resolve. This code works fine, and displays the dynamically populated drop down field on the visualforce page when I reference a specific Id, but it is still not working when I reference a the variable. What am I missing?
Thanks in advance for any additional assistance!
  
public class ServiceRequestDepartmentSearchController {
public list <Service_Request__c> sr {get;set;}  
   public string searchstring {get;set;}
   public string myFacilityid {get;set;}   

   public ServiceRequestDepartmentSearchController(ApexPages.StandardController controller) {
   } 
 
   //perform search when "Search" button is clicked//
   public void search(){
       System.debug('::myFacilityid::'+myFacilityid);
       string searchquery='select Site_Number__r.name,Site_Number__r.Facility__c,Name,SR_Created_Date__c,Status__c,Subject__c,Priority__c,id from service_request__c where (Site_Number__r.Facility__c= \''+myFacilityid+'\') and (Site_Number__r.name like \'%'+searchstring+'%\') order by createddate DESC Limit 20';
     sr= Database.query(searchquery);  
   }  
   public void clear(){  
   sr.clear();  
   } 
    
    //Assign Department Values to the Department Drop Down Field//
    //This variable hold the id of the Department which is selected by end user from the picklist

        public String selecteddepartmentId {set;get;}
   
      

      //This variable holds List of Departments

      public List<SelectOption> getListOfDepartments()

    {
        List<Department__c> Departments = [select id ,name from Department__c where Facility__c =:myFacilityid] ;
               List<SelectOption> DepartmentOptionList = new List<SelectOption>();
               DepartmentOptionList .add(new SelectOption( ' ' ,'---Select---'));
               for(Department__c d : Departments )
               {
                          DepartmentOptionList .add(new SelectOption(d.Id , d.Name));
               }
              return DepartmentOptionList ;
    }
  public ServiceRequestDepartmentSearchController()
  {
                    selecteddepartmentId ='';
  }
}

 
Deepak GulianDeepak Gulian
Your query is right, but it seems that you are not able to pass the value from visualforce page to controller in String variable myFacilityid. Can you please share the VF page code also?
Mahesh DMahesh D
Hi Derek,

Please print the "myFacilityid" and show us the debug logs.

Also make sure that whatever the specific Id you added initially and value from the debug, make sure that both are same.

Regards,
Mahesh
Derek Davis 7Derek Davis 7
Awe... I think I have found problem. The searchquery I mentioned before does work. However, this it because the Search button on the page passes the variable into the controller: 
<apex:commandButton value="Search records" action="{!search}" id="searchButton" reRender="searchBlock">
        <apex:param name="myFacilityid" assignTo="{!myFacilityid}" value="{!$CurrentPage.parameters.varFacilityID}" id="facility" />
</apex:commandButton>

 I need to somehow automatically populate the variable's value in the controller when the page is initially loaded, and then perhaps refresh the page immediately so the values with show in the picklist? Any suggestions?



This is the code to display the drop down field on the visualforce page, but it makes since that it would have no value since the Facility ID doesn't get sent to the controller until the page until the "Search records" button is clicked. 
<apex:selectList value="{!selecteddepartmentId}" size="1" multiselect="false"  >
			<apex:selectOptions value="{!ListOfDepartments}"/>
		</apex:selectList>

Thanks again for any assistance!

 
Mahesh DMahesh D
You can read the parameter value from your Controller:

varFacilityID

in the controller you can do this, so that the value will be set to myFacilityid.

myFacilityid= ApexPages.currentPage().getParameters().get('varFacilityID');

If required you can call search method also in the constructor for the first time otherwise just get the parameter value and assign it to myFacilityId.

Please do let me know if it helps you.

Regards,
Mahesh
This was selected as the best answer
Derek Davis 7Derek Davis 7
Thanks everyone! With your suggestions and a few extra tweaks I was able to get the code behaving as desired.