I have a custom object with a picklist field with a very large number of options. In an effort to reduce the number of choices, I want to find (and remove) all the options in this picklist which are not selected on any record across my organization. Is there a way to do this?

  • How many records do you have in this custom object?
    – Adrian Larson
    May 1, 2017 at 14:06
  • @AdrianLarson Hundreds, if not over a thousand. May 1, 2017 at 14:13
  • for starters, I imagine you can iterate over the picklist values and do a soql for each and see which return no records.
    – glls
    May 1, 2017 at 14:16

3 Answers 3


If you have less than 50,000 records, you can just get this information from a simple query:

SELECT MyPicklist__c, count(Id) records FROM MyObject__c GROUP BY MyPicklist__c

If you run this query in the Developer Console, you would see something like:

MyPicklist__c    count(Id)
Value 1          200
Value 2          100
Value 3          75
Value 5          42
...              ...

Any values you do not see under MyPicklist__c are not selected. You could also run a script to just spit out unused values:

Set<String> selectedValues = new Set<String>();
for (AggregateResult aggregate : [
    SELECT MyPicklist__c FROM MyObject__c GROUP BY MyPicklist__c
]) selectedValues.add((String)aggregate.get('MyPicklist__c'));

List<String> unselectedValues = new List<String>();
for (PicklistEntry entry : MyObject__c.MyPicklist__c.getDescribe().getPicklistValues())
    if (!selectedValues.contains(entry.getValue())

  • Your first query is the solution I used. Thank you! May 1, 2017 at 16:44
  • @Chris Glad I could help! If this answer addressed your problem, please consider accepting it by clicking on the check mark/tick to the left of the answer, turning it green. This marks the question as resolved to your satisfaction, and awards reputation both to you and the person who answered. If you have >= 15 reputation points, you may also upvote the answer if you wish. There is no obligation to do either.
    – Adrian Larson
    May 1, 2017 at 16:45

The easiest way to do this is actually just to edit the field twice: change the field type to text, then change it back to picklist. During the text to picklist conversion, all actual values in the field are converted to picklist values. If you were curious what values were there before, make sure you back up the list of options somehow.

  • Interesting. I wouldn't have thought of that. Does seem a bit high risk depending on the admin.
    – Adrian Larson
    May 1, 2017 at 14:22
  • 1
    @AdrianLarson There's always an element of risk when editing a field's type, I guess, but this is one of my favorite tricks for pruning custom picklists, especially if there's a ton of values in the list that are unused.
    – sfdcfox
    May 1, 2017 at 14:25
  • 1
    @sfdcfox The picklist in question is a dependent field (i.e. City field controlled by a County field). Is it still safe to do this? May 1, 2017 at 15:10
  • 1
    @Chris Glad you asked. The dependencies would be lost, so you'll have to rebuild them after conversion. Salesforce should actually warn you if try to do this without removing the dependency first.
    – sfdcfox
    May 1, 2017 at 15:12
  • 1
    @sfdcfox Thank you. This would solve my issue were it not for the dependency. May 1, 2017 at 16:45

Regarding Adrian Larson's answer rather than do the Group, if you don't need the count just do:

SELECT DISTINCT(MyPicklist__c) FROM MyObject__cl;

One reason to use the query you did use (or something like it) would be to order the pick list by frequency in which case:

   SELECT MyPicklist__c, count(Id) AS c FROM MyObject__c 
        GROUP BY MyPicklist__c ORDER BY c DESC

Will give you the output ordered by the frequency of occurences.

  • I am not familiar with that aggregate function, and indeed the Developer Console complains Invalid aggregate function: DISTINCT. Nor does it appear in the Force.com SOQL and SOSL Reference. Have you used it successfully somewhere?
    – Adrian Larson
    May 2, 2017 at 0:03

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .