+ Start a Discussion
lescclarklescclark 

SOQL Picklists

Hi

 

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

mikefmikef

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?

lescclarklescclark

hi

 

the former

 

thanks

mtbclimbermtbclimber

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.

 

HarmpieHarmpie
select name, toLabel(type) from account

 

 

Wow, thanks for that one. Learned something there!

 

Why isn't this documented?

mtbclimbermtbclimber

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.

HarmpieHarmpie

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

mtbclimbermtbclimber

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:

 

"picklist

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

 

So this means if my picklist ordering is:

 

C

B

A

 

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.

HarmpieHarmpie

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:

C
B
E
A
D

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.