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?
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()) unselectedValues.add(entry.getValue()); system.debug(JSON.serialize(unselectedValues));
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.
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.