+ Start a Discussion
Matt FolgerMatt Folger 

How to have a picklist inputted into a user-driven SOQL query?

Hi, I'm building this user-driven input SOQL query (my life's work) and I'm trying to see if it is possible for a picklist to be one of the fields that can be pulled for an input query?  I'm not seeing the apex:inputXXXX command that would do this, but I'm hoping it can be done through some other method.

I have a Inquiry_Status__c field that is a picklist and can only have: "Original", "Inquired" or "Responded" field information, so having the user have to manually type that in the input field is kinda nutty and prone to problems, mistypes, forgetting what the field values CAN be, etc.  Is there a way to do this?

(I presently have the SOQL query working via a <apex:inputText ......> command, but a picklist would be awesome)
Best Answer chosen by Matt Folger
kaustav goswamikaustav goswami
Step 1:

Please insert this code in your controller.

public List<SelectOption> getInquiryStatus(){
List<SelectOption> options = new List<SelectOption>();
options.add(new SelectOption('-- Select Inquiry Status --', ''));
options.add(new SelectOption('Original', 'Original'));
options.add(new SelectOption('Inquired', 'Inquired'));
options.add(new SelectOption('Responded', 'Responded'));
return options;
}

Step 2:

Please replace the line "<apex:inputText value="{!Ven_Inquiry_Stat}" label="Inquiry Status"/>"

with the following code:

<apex:selectList id="inqStatInp" value="{!Ven_Inquiry_Stat}" size="1" label="Inquiry Status">
<apex:selectOptions value="{!InquiryStatus}"/>
</apex:selectList>

This will give you a drop down list on the page. The value that user selects will be present in the variable "Ven_Inquiry_Stat". You can use that value to form the query.

Let me know if this solves your issue or you still have some problems.

Thanks,
Kaustav

All Answers

kaustav goswamikaustav goswami
Let us assume that the field Inquiry_Status__c is inside an object called 'Inquiry__c'. If "inqInst" is an instance of the object in your class then in the visualforce page write something like this -

<apex:inputField value="{!inqInst.Inquiry_Status__c}" id="inqStatInput" />

This will render the field as a picklist in your VF page.

Thanks,
Kaustav
Avidev9Avidev9
Can you post some code ? is there is any reason for using inputText instead of inputField ?
Matt FolgerMatt Folger
<apex:inputField value="{!Vencorr__c.Inquiry_Status__c}" id="inqStatInput" />

Here's the code line I inputed.  

But I get these errors:  
Error: Unknown property 'VencorrCSAQuery.Vencorr__c' 
Quick Fix Create Apex property 'VencorrCSAQuery.Vencorr__c' 

Quick Fix Create Apex method 'VencorrCSAQuery.getVencorr__c'
If I changed the line of code in question to this, (which is how the variable is defined in the controller):  
<apex:inputField value="{!Ven_Inquiry_Stat}" id="inqStatInput" />
Then I get this:  

Error Error: Unknown property 'VencorrCSAQuery.Vencorr__c' 
Quick Fix Create Apex property 'VencorrCSAQuery.Vencorr__c' 

Quick Fix Create Apex method 'VencorrCSAQuery.getVencorr__c'

Hmm, so I'm not seeing how to do that?  Remember, it's not a direct record that we are referencing with these pull downs, they are placeholders (if that's the right term) for a controller to search based on these criteria.  

Matt FolgerMatt Folger
VF Page:

<apex:page controller="VencorrCSAQuery" tabStyle="Vencorr__c" showHeader="true" sidebar="false" >
    <apex:form >
        <apex:pageMessages />
        <apex:pageBlock title="Search Criteria">
             <apex:pageBlockSection >
                 <apex:inputText value="{!Ven_Inquiry}" label="Inquiry"/>
                 <apex:inputText value="{!Ven_Inquiry_Stat}" label="Inquiry Status"/>
                 <apex:inputText value="{!Ven_Product}" label="Product"/>
                 <apex:inputText value="{!Ven_PO}" label="PO"/>
                 <apex:inputText value="{!Ven_Vendor}" label="Vendor"/>
            </apex:pageBlockSection>
            <apex:pageBlockButtons location="bottom">
                    <apex:commandButton action="{!search}" value="Search"/>
            </apex:pageBlockButtons>
                        </apex:pageBlock>
        <apex:pageBlock title="Search Result">
            <apex:outputLink value="{!URLFOR($Action.Vencorr__c.New)}">Create New</apex:outputLink>
            <apex:pageBlockTable value="{!searchResults}" var="res" rendered="{!searchResults.size > 0}">
            <apex:column title="Inquiry Number">
                    <apex:outputLink value="/{!res.Id}" target="_blank">{!res.name}</apex:outputLink>
            </apex:column>   
                                 
                <apex:column title="Inquiry Status" value="{!res.Inquiry_Status__c}"/>
                <apex:column title="Product" value="{!res.Product__c}"/>
                <apex:column title="Purchase Order" value="{!res.PO__c}"/>
                <apex:column title="Vendor" value="{!res.Vendor__c}"/>
            </apex:pageBlockTable>
            
            <br/>
            SOQL: {!queryString}
            



        </apex:pageblock> 
    </apex:form> 

</apex:page>

Controller:
public class VencorrCSAQuery {
   
    public String Ven_Inquiry {get;set;}                                    
    public String Ven_Inquiry_Stat {get;set;}
    public String Ven_Product {get;set;}
    public String Ven_PO {get;set;}
    public String Ven_Vendor {get;set;}
    public List<Vencorr__c> searchResults {get;set;}
    private boolean firstFilterApplied = false;
    public String queryString {get;set;}
    public VencorrCSAQuery(){
        searchResults = new List<Vencorr__c>();
    }

    public PageReference search(){
        searchResults.clear();
        firstFilterApplied = false;
        try{
            //Create the dynamic SOQL query
            queryString = 'SELECT name, Inquiry__c, Inquiry_Status__c, Product__c, PO__c, Vendor__c '+
                                 'FROM Vencorr__c ';
           
            if (Ven_Inquiry != null && Ven_Inquiry != ''){
                string tempVen_Inquiry = Ven_Inquiry;
                tempVen_Inquiry = String.escapeSingleQuotes(tempVen_Inquiry); //To avoid SOQL injection
                tempVen_Inquiry = tempVen_Inquiry.startsWith('%') ? tempVen_Inquiry : '%' + tempVen_Inquiry;
                tempVen_Inquiry = tempVen_Inquiry.endsWith('%') ? tempVen_Inquiry : tempVen_Inquiry + '%';
                queryString += whereOrAndClause() + ' name like \''+tempVen_Inquiry+'\'';
            }
           
            if (Ven_Inquiry_Stat != null && Ven_Inquiry_Stat != ''){
                string tempVen_Inquiry_Stat = Ven_Inquiry_Stat;
                tempVen_Inquiry_Stat = String.escapeSingleQuotes(tempVen_Inquiry_Stat); //To avoid SOQL injection
                tempVen_Inquiry_Stat = tempVen_Inquiry_Stat.startsWith('%') ? tempVen_Inquiry_Stat : '%' + tempVen_Inquiry_Stat;
                tempVen_Inquiry_Stat = tempVen_Inquiry_Stat.endsWith('%') ? tempVen_Inquiry_Stat : tempVen_Inquiry_Stat + '%';
                queryString += whereOrAndClause() + ' Inquiry_Status__c like \''+tempVen_Inquiry_Stat+'\'';
            }
           
            if (Ven_Product != null && Ven_Product != ''){
                string tempVen_Product = Ven_Product;
                tempVen_Product = String.escapeSingleQuotes(tempVen_Product); //To avoid SOQL injection
                tempVen_Product = tempVen_Product.startsWith('%') ? tempVen_Product : '%' + tempVen_Product;
                tempVen_Product = tempVen_Product.endsWith('%') ? tempVen_Product : tempVen_Product + '%';
                queryString += whereOrAndClause() + ' Product__c like \''+tempVen_Product+'\'';
            }
           
            if (Ven_PO != null && Ven_PO != ''){
                string tempVen_PO = Ven_PO;
                
                tempVen_PO = String.escapeSingleQuotes(tempVen_PO); //To avoid SOQL injection
                //queryString += whereOrAndClause() +' PO__c = \''+tempVen_PO+'\'';
                queryString += whereOrAndClause() +' PO__c ='+ tempVen_PO;
            }
           
            if (Ven_Vendor != null && Ven_Vendor != ''){
                string tempVen_Vendor = Ven_Vendor;
                tempVen_Vendor = String.escapeSingleQuotes(tempVen_Vendor); //To avoid SOQL injection
                queryString += whereOrAndClause() + ' Vendor__c = \''+tempVen_Vendor+'\'';
            }
           
            searchResults = Database.query(queryString);
            }
            catch(QueryException e){
                //If the query returns more than 1000 records, display an error to the user
                ApexPages.addMessage(new ApexPages.message(ApexPages.severity.ERROR,'Too many Reservation records found that match the specified search criteria. Please refine your search criteria and try again'));
                return null;
       
            }
        catch(Exception e1){
             ApexPages.addMessages(e1);
             return null;
        }       

        if (searchResults.size() == 0){
            ApexPages.addMessage(new ApexPages.message(ApexPages.severity.INFO,'The specified search criteria did not match any existing Reservation records. Please try again'));
        }
               
        return null;

    }
   
    private String whereOrAndClause(){
        String queryClause;
        if (firstFilterApplied){
            queryClause = ' AND ';
        }
        else{
            queryClause = ' WHERE ';
        }
        firstFilterApplied = true;
        return queryClause;
    }
}


kaustav goswamikaustav goswami
Step 1:

Please insert this code in your controller.

public List<SelectOption> getInquiryStatus(){
List<SelectOption> options = new List<SelectOption>();
options.add(new SelectOption('-- Select Inquiry Status --', ''));
options.add(new SelectOption('Original', 'Original'));
options.add(new SelectOption('Inquired', 'Inquired'));
options.add(new SelectOption('Responded', 'Responded'));
return options;
}

Step 2:

Please replace the line "<apex:inputText value="{!Ven_Inquiry_Stat}" label="Inquiry Status"/>"

with the following code:

<apex:selectList id="inqStatInp" value="{!Ven_Inquiry_Stat}" size="1" label="Inquiry Status">
<apex:selectOptions value="{!InquiryStatus}"/>
</apex:selectList>

This will give you a drop down list on the page. The value that user selects will be present in the variable "Ven_Inquiry_Stat". You can use that value to form the query.

Let me know if this solves your issue or you still have some problems.

Thanks,
Kaustav
This was selected as the best answer
Vinit_KumarVinit_Kumar
Matt,

You shoould use <apex:actionSupport> with <apex:param>,something like below :-

// Opportunity Stagename is picklist field
<apex:inputField value="{!Opportunity.StageName}" multiselect="false" size="1">
<apex:actionsupport event="onchange" action="{!updateOpportunity}" rerender="oppTable" status="stageStatus">
                                         //The oppStage string will be populated in the controller with the value of Opportunity Stagename"
                    <apex:param name="opportunityID" assignto="{!oppStage}" value="{!Opportunity.StageName}"></apex:param>
                </apex:actionsupport>
				</apex:inputField>


Avidev9Avidev9
Ok I see you are using string variables and inputfield works only with object binding

<apex:inputField value="{!Ven_Inquiry_Stat}" id="inqStatInput" />

Probably you can try to user your object instance instead of String. Lets say the picklist field is on "Vencorr__c"

in controller

public class myController{
public Vencorr__c ven{get;set;}
 public myController(){
    ven = new Vencorr__c();
  }
}

in page you can do

<apex:inputField value="{!ven.Inquiry_Status__c}" id="inqStatInput" />

Now replace Ven_Inquiry_Stat with   ven.Inquiry_Status__c 


Matt FolgerMatt Folger
Wow, thanks everyone for all the great ideas!

I went with kaustav goswami's and it worked without incident, so I'll leave well enough alone.

Thanks everyone.

=)