Print this page

Multi-Select Picklist Value Ordering Behavior (UI vs SOQL)

Knowledge Article Number 000231088
Description Multi-Picklist fields allow you to select multiple values on your records for various use cases. You have the ability to define values on creation of the field and after creation you can continue to add and remove other values. These values can also be re-ordered for purposes of displaying the correct perhaps ascending or descending values for a more readable display and better user experience in the UI. 

SOQL, Apex and API access on these fields will however return the values in their created order via Setup or deployment. Similar to when using ORDER BY in SOQL with regards to Picklist fields - "sorting is defined by the picklist sort determined during setup".

To demonstrate the difference refer to these two examples: 
  • We start with Two Multi-Select Picklist fields with values:
    • shanep__Multi_Picklist1__c - option1; option2; option3
User-added image
  • shanep__Multi_Picklist2__c - option3; option2: option1 
User-added image
  • Maintaining that order during initial creation. The order of the values is now set in the UI and for SOQL 

Report (UI)
User-added image

Record (UI)
User-added image

Workbench (SOQL)
User-added image
  • If we now change the order of the values for shanep__Multi_Picklist1__c to option3; option1; option2;
User-added image
  • We can see that the change is reflected in the UI but not in SOQL

Report (UI)
User-added image

Record (UI)
User-added image

Workbench (SOQL)
User-added image


Notes:
It is not recommended to go ahead and delete picklist values in use without first replacing them however replacing them directly without planning may result in the same un-ordered situation.
PLEASE TEST THE BELOW IN A SANDBOX WITH EXAMPLE DATA FIRST AND ENSURE YOU HAVE BACKUPS OF YOUR RECORD DATA AND METADATA BEFORE MAKING ANY LIVE CHANGES
One approach you could take given the example above would be:
  • First add your replacement values, you will need to add the full range of values you want to have in the correct order and not just the ones you want to replace as any new values created will appear at the end of the list. 
  • In this example we'll add NewOption3; NewOption1; NewOption2; , noting that they are now in the order I want to have them in.
User-added image
  • Now, replace the option1; option2; option3; with their corresponding values, and you'll have to wait for each job to complete for each option change and this could take some time, therefore it is recommended to do this in an offline state or when users are not actively using the system.
User-added image
  • Once all 3 values are replaced I use a test record to confirm the values have in fact been changed and I can confirm via SOQL the values are also in the order I want them. 

Workbench (SOQL)
User-added image

Report (UI)
User-added image

Record (UI)
User-added image
  • Please take note that renaming the picklist values will not change the record values but only the options for selection by the users. If you need to rename the values again you will have to use Replace again to override existing values. Adding a dash or some capitalized character as a reference point to what picklist values are new compared to the old values may make this simpler to work with.


 
Resolution
 




promote demote