6

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?

3
  • How many records do you have in this custom object?
    – Adrian Larson
    May 1 '17 at 14:06
  • @AdrianLarson Hundreds, if not over a thousand.
    – Chris
    May 1 '17 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 '17 at 14:16
7

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));
2
  • Your first query is the solution I used. Thank you!
    – Chris
    May 1 '17 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 '17 at 16:45
7

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.

6
  • Interesting. I wouldn't have thought of that. Does seem a bit high risk depending on the admin.
    – Adrian Larson
    May 1 '17 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 '17 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?
    – Chris
    May 1 '17 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 '17 at 15:12
  • 1
    @sfdcfox Thank you. This would solve my issue were it not for the dependency.
    – Chris
    May 1 '17 at 16:45
0

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.

1
  • 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 '17 at 0:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.