Starting November 20, the site will be set to read-only.
On December 4, 2023,
Apex Code Development (90768)
General Development (55146)
Visualforce Development (37251)
Lightning (18265)
APIs and Integration (17146)
Trailhead (11680)
Formulas & Validation Rules Discussion (11337)
Other Salesforce Applications (8116)
Jobs Board (6655)
Force.com Sites & Site.com (4842)
Mobile (2694)
You need to sign in to do that
Don't have an account?
lescclark 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
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?
hi
the former
thanks
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.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:
"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.
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 :)
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.
Select Id, Name, Family, IsActive from Product2 WHERE Family<> ' '
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)
For picklists, there are two main options to search (specifically or generally) for records with data:
*Note: You can replace count() with standard or custom fields if you are looking to see the data within those records.
select count() from Contact where Custom_Picklist__c = 'value1'
select count() from Contact where Custom_Picklist__c <> NULL
For multi-select picklists, there are four main options:
This first query searches for records that have all specified (AND) multi-select picklist values.
select count() from Contact where Custom_Multi_Picklist__c includes ( 'value1','value4','value9' )
This second query searches for records that have one or more of the specified (OR) multi-select picklist values.
select count() from Contact where Custom_Multi_Picklist__c includes ( 'value1;value3;value6' )
This third query searches for records that have only the one specified multi-select picklist values.
select count() from Contact where Custom_Multi_Picklist__c = 'value1'
The fourth query, much like the one for picklists above, searches for records that contain ANY multi-select picklist values.
select count() from Contact where Custom_Multi_Picklist__c <> NULL
There is no way that I am aware of to write a SOQL query for picklists or multi-select picklists that lets you use binding variables like OR or AND. The system will return an error message of "Bind variables only allowed in Apex code" if you attempt it.