+ Start a Discussion

SOQL Picklists



can't seem to find an answer to this one anywhere !


Can i use select to SOQL picklist values ? If as I suspect the answer is 'no' what is the standard way to do this.


ta in advance


Are you asking can you use picklist values in a select statement? Like in the Where clause?

Or are you asking can you use SOQL to populate a custom picklist on a Visualforce page?




the former




yes, you can. For the standard type picklist field on the Account object issue a query like this:



select name from account where type = 'prospect'



you can also get the type in the select itself:



select name, type from account



you can also get the translated picklist value for the account (if your organization has enabled translation workbench):



select name, toLabel(type) from account



but you can not query the picklist table:



select label, value from picklistvalue where name = 'account.type'



For the last bit you would need to utilize describe information in apex like this:



public class PicklistUtil { public static List<Schema.PicklistEntry> getAccountTypes() { return Account.Type.getDescribe().getPicklistValues(); } }


 Check out the Apex Developer Guide for more info.


select name, toLabel(type) from account



Wow, thanks for that one. Learned something there!


Why isn't this documented?


It is doc'd just not in the apex dev guide. It's here in the webservices API guide which has the full story on SOQL. I believe there are pointers to this doc "for more information see..." in the Apex developer guide.


Wouldn't it make sense to add this piece of documentation to the section on Schema.PicklistEntry (or at least make a reference there)?


I agree it would be helpful to have a consolidated reference for the capabilities and behaviors of each data type and that each place that references the a capability or behavior should back link to it. Back-linking each behavior/feature could produce a lot of "see also" references where a simple description like "this type is dynamically driven based on the definition of values for a picklist field" See <link>Picklist Field Capabilities and Behaviors</link> for more information about picklists and their values would probably be best. I'll review this with our documentation team and the team responsible for data types.


Thanks for the request and sorry the doc wasn't more easily discoverable.   As a token of appreciation I'll share another picklist behavior nugget with you which you may or may not know (likely that someone hitting this thread won't know :) ):


From the API documentation on the SOQL ORDER BY clause:



 sorting is defined by the picklist sort determined during setup."


So this means if my picklist ordering is:






And a SOQL query requests an order by clause on the picklist ascending the resulting order will be rows with values in that order - C->B->A, and NOT A->B->C. And combined with toLabel(), the ordering would thus never change based on lexical order of the given translation.


May be logical and obvious to some, to others (like me) it may not be.


That's a nice addition indeed. Never knew that this is the sorting logic and to be honest not very logical either. Must have something to do with the meta-data structure :)

Amit Rangari 1Amit Rangari 1
So if the picklist is  as below:


and we want all the records with picklist value > C (assuming the sort order is C B E A D, hence elements in order of B, E, A, D) there is no way to express it.

Try example below:

select id, picklist__c from obj__c order by picklist__c //this will show data in sorted by 'C, B ,E, A, D'.
select id, picklist__c from obj__c where picklist__c > 'C'  order by picklist  // this will show data where picklist value is 'E' and 'D' only. , with order as E D. 

the comparision here is happening as string comparison and not the setup order of the value.
Reshma Sahu 1Reshma Sahu 1
You can get the value of the family picklist of Product using WorkBench and Salesforce Inspector. Try this Query
Select Id, Name, Family, IsActive from Product2 WHERE Family<> ' '
You can query multiselect picklists using INCLUDES and EXCLUDES keywords:
SELECT id FROM Opportunity WHERE custom_field__c INCLUDES ('some value')

More info here (https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_querying_multiselect_picklists.htm)