[SalesForce] Query Multi-Select Picklist in Apex with Visual Force Page

I'm having some difficulty figuring out the correct way to do this. If I have a visual force page with a multi-select picklist and I need to run the selected values through a query. The issue is I do not have those values until they select them. For instance, let's say I have John, Dan, and Nancy as the values I know the query would be:

Select FirstName From Contact Where FirstName Includes ('John;Dan;Nancy')

I know there is a limitation with INCLUDES so this wouldn't work.

String s = 'John;Dan;Nancy';
Select FirstName From Contact Where FirstName Includes (:s)

But what if I need to store those values in a variable in the Visual force page. Let's say the user selected John and Dan. I can store those values in a String or String array, but how would you reference those in query? Can you store the multi-select value in string and referenc it a query using the includes statement or would you need to join string in Dynamic SOQL. I'm also wary on SQL Injection, so I'm looking for the best method.

Thanks,

Andy

Best Answer

Your query is looking for an exact match. ie where the FirstName includes John, Dan and Nancy. If you wanted to search for records that have one or more of those values set then try this:

Select FirstName From Contact Where FirstName Includes ('John', 'Dan', 'Nancy')

You could also use a List, as shown here:

List<String> s = new List<String>{'John', 'Dan', 'Nancy'};
Select FirstName From Contact Where FirstName Includes (:s)

See here for more information

Related Solutions

[SalesForce] Dependent multi-select pick lists

Safe Harbor: this is a really long answer...read at your own risk. Also, none of this sample code has been tested, this is just me stubbing it out in Notepad++.

This is not currently possible with standard Salesforce configuration, as multi-select picklists cannot be selected as the Controlling Field in a Field Dependency. So, you're definitely looking at a VisualForce solution; your approach here really depends on your requirements. Does it need to be easily configurable, or is it feasible to hardcode the dependencies in your controller?

If it's acceptable to hardcode the dependencies in your controller, then you'll probably want to set up maps of the dependencies to simplify your code (I use the word "simplify" loosely here)...

public class someController {

    public String[] parentPicklistVal {public get; public set;}
    public String[] childMultiPicklistVal {public get; public set;}
    public String childSinglePicklistVal {public get; public set;}

    // maps to hold your dependencies between picklists
    private Map<String, List<String>> parentDepMap;
    private Map<String, List<String>> childDepMap;

    private String[] parentOpts = new String[] { 'parent option 1', 'parent option 2' };
    private String[] childMultiOpts = new String[] { 'child multi 1', 'child multi 2', 'child multi 3' };
    private String[] childSingleOpts = new String[] { 'child single 1', 'child single 2', 'child single 3' };

    public someController() {
        // init dependency maps
        parentDepMap = new Map<String, List<String>>();
        childDepMap = new Map<String, List<String>>();

        // pick which child options to display for which parent value
        parentDepMap.put(parentOpts[0], (new String[]{childMultiOpts[0], childMultiOpts[1]}));
        parentDepMap.put(parentOpts[1], (new String[]{childMultiOpts[1], childMultiOpts[2]}));

        // pick which single-select options to display for which child value
        childDepMap.put(childMultiOpts[0], (new String[]{childSingleOpts[0], childSingleOpts[2]}));
        childDepMap.put(childMultiOpts[1], (new String[]{childSingleOpts[1], childSingleOpts[2]}));
        childDepMap.put(childMultiOpts[2], childSingleOpts);    // or if you want to show them all?
    }

    public List<SelectOption> getParentPicklistOptions() {
        List<SelectOption> selectOpts = new List<SelectOption>();
        for ( String s : parentOpts )
            selectOpts.add(new SelectOption(s, s));
        return selectOpts;
    }

    public List<SelectOption> getChildMultiPicklistOptions() {
        List<SelectOption> selectOpts = new List<SelectOption>();
        if ( parentPicklistVal != null && parentPicklistVal.size() > 0 ) {
            // build a set of values to avoid dupes, since there may be overlapping dependencies
            Set<String> possibleOpts = new Set<String>();
            for ( String val : parentPicklistVal )
                possibleOpts.addAll(parentDepMap.get(val));
            for ( String s : possibleOpts )
                selectOpts.add(new SelectOption(s, s));
        }
        return selectOpts;
    }

    public List<SelectOption> getChildSinglePicklistOptions() {
        List<SelectOption> selectOpts = new List<SelectOption>();
        if ( childMultiPicklistVal != null && childMultiPicklistVal.size() > 0 ) {
            // build a set of values to avoid dupes, since there may be overlapping dependencies
            Set<String> possibleOpts = new Set<String>();
            for ( String val : childMultiPicklistVal )
                possibleOpts.addAll(childDepMap.get(val));
            for ( String s : possibleOpts )
                selectOpts.add(new SelectOption(s, s));
        }
        return selectOpts;
    }

    public PageReference actionUpdatePicklistVals() {
        // this doesn't really need to do anything, since the picklists should be updated when their getters call after returning
        return null;
    }

}

And then your VF page...

<apex:page controller="someController">
    <apex:form>
        <apex:outputPanel id="panel1">
            <apex:selectList value="{!parentPicklistVal}" multiselect="true" size="3">
                <apex:selectOptions value="{!parentPicklistOptions}" />
                <apex:actionSupport event="onchange" action="{!actionUpdatePicklistVals}" rerender="panel1" />
            </apex:selectList>
            <apex:selectList value="{!childMultiPicklistVal}" multiselect="true" size="3">
                <apex:selectOptions value="{!childMultiPicklistOptions}" />
                <apex:actionSupport event="onchange" action="{!actionUpdatePicklistVals}" rerender="panel1" />
            </apex:selectList>
            <apex:selectList value="{!childSinglePicklistVal}" multiselect="false" size="1">
                <apex:selectOptions value="{!childSinglePicklistOptions}" />
            </apex:selectList>
        </apex:outputPanel>
    </apex:form>
</apex:page>

However, if this needs to be easily configurable, then you're looking at a custom object with a lookup relationship to itself. You can then query the dependencies and build the maps from them instead of hardcoding them. Just throwing some ideas out there, let's say this custom object uses a Name field for its value, a FieldDep__c field as a lookup to it's controlling value, and a WhichPicklist__c field to control which picklist it belongs to (for this I'll just say it's an integer with possible values of 1, 2, and 3).

public class someController {

    public String[] parentPicklistVal {public get; public set;}
    public String[] childMultiPicklistVal {public get; public set;}
    public String childSinglePicklistVal {public get; public set;}

    private Map<String, List<String>> parentDepMap;
    private Map<String, List<String>> childDepMap;

    public someController() {
        FieldDep__c[] deps = [select id, name, fielddep__c, fielddep__r.name, whichpicklist__c from FieldDep__c order name asc];
        // init the same maps that we used in the example above
        parentDepMap = new Map<String, List<String>>();
        childDepMap = new Map<String, List<String>>();
        for ( FieldDep__c dep : deps ) {
            if ( dep.whichpicklist__c == 1 ) {
                // shouldn't really need to do anything here...
            } else if ( dep.whichpicklist__c == 2 ) {
                // add to the dependency map between the parent and child multi-select picklist
                String parentVal = dep.fielddep__r.name;
                if ( parentDepMap.containsKey(parentVal) )
                    parentDepMap.get(parentVal).add(dep.Name);
                else
                    parentDepMap.put(parentVal, new String[]{dep.Name});
            } else if (dep.whichpicklist__c == 3 ) {
                // add to the dependency map between the child multi-select and child single-select picklist
                String parentVal = dep.fielddep__r.name;
                if ( childDepMap.containsKey(parentVal) )
                    childDepMap.get(parentVal).add(dep.Name);
                else
                    childDepMap.put(parentVal, new String[]{dep.Name});
            }
        }
    }

    public List<SelectOption> getParentPicklistOptions() {
        List<SelectOption> selectOpts = new List<SelectOption>();
        String[] parentOpts = parentDepMap.keySet();
        for ( String s : parentOpts )
            selectOpts.add(new SelectOption(s, s));
        return selectOpts;
    }

    public List<SelectOption> getChildMultiPicklistOptions() {
        List<SelectOption> selectOpts = new List<SelectOption>();
        if ( parentPicklistVal != null && parentPicklistVal.size() > 0 ) {
            // build a set of values to avoid dupes, since there may be overlapping dependencies
            Set<String> possibleOpts = new Set<String>();
            for ( String val : parentPicklistVal )
                possibleOpts.addAll(parentDepMap.get(val));
            for ( String s : possibleOpts )
                selectOpts.add(new SelectOption(s, s));
        }
        return selectOpts;
    }

    public List<SelectOption> getChildSinglePicklistOptions() {
        List<SelectOption> selectOpts = new List<SelectOption>();
        if ( childMultiPicklistVal != null && childMultiPicklistVal.size() > 0 ) {
            // build a set of values to avoid dupes, since there may be overlapping dependencies
            Set<String> possibleOpts = new Set<String>();
            for ( String val : childMultiPicklistVal )
                possibleOpts.addAll(childDepMap.get(val));
            for ( String s : possibleOpts )
                selectOpts.add(new SelectOption(s, s));
        }
        return selectOpts;
    }

    public PageReference actionUpdatePicklistVals() {
        // this doesn't really need to do anything, since the picklists should be updated when their getters call after returning
        return null;
    }

}

You would then want to populate FieldDep__c records from the top down so that you can add a lookup to the controlling value for each record.

I hope I didn't go too overboard with this! If I get a chance sometime today I'll put this together in an org to see how valid my code works, but it sounded like you were just looking for a push in the right direction (although this answer may have been slightly more than that...)

[SalesForce] Creating multi-select picklist\selectoption list through query

Visual Force Page

<apex:page standardController="Contract" extensions="newContractController" >
<apex:form >
    <apex:inputfield value="{!myContract.AccountId}">
        <apex:actionSupport event="onchange" action="{!accountChanged}" />
    </apex:inputfield>

    <apex:selectList value="{!selectedSubAccounts}" >
        <apex:selectOptions value="{!subAccountOptions}" />        
    </apex:selectList>

</apex:form>

Controller

public class newContractController {

    public List<SelectOption> subAccountOptions { get; set; }
    public string [] selectedSubAccounts { get; set; }

    public Id accountId { get; set; }
    public Contract myContract { get; set; }

    public newContractController(ApexPages.StandardController controller) {
        myContract =  new Contract();
    }

    public void accountChanged() {

        Account [] accounts = [SELECT Name, Id FROM Account WHERE ParentId = :myContract.AccountId];

        List<SelectOption> options = new List<SelectOption>();

        for (Account a : accounts) {
            options.add(new SelectOption(a.Id,a.Name));        
        }

        subAccountOptions = options;

    }

}

Here's something that may help. Accounts already have a field called Parent Account which will allow for a hierarchy of Accounts. You may want to use that instead of creating a custom Sub Account.

In this page when you pick an Account it will cause an event which will query for all Accounts that whose parent is the one you just picked

Best Answer

Related Solutions

[SalesForce] Dependent multi-select pick lists

[SalesForce] Creating multi-select picklist\selectoption list through query

Related Topic