Changing and Consolidating Picklist (Multi-Select) fields in Salesforce.com

1. Start by analyzing your current set of values and your future set of values and create a mapping between the values. I was lucky enough to have DBAmp set up in an MS SQL environment where I ran into this issue, so I was able to quickly pull the data through some basic MS SQL queries.

Patrick 1.png

2. Once you have the mapping of your data, you can create a temporary array, which will have an individual field for each new value that your existing values will map to followed by a “;” deliminator. If any of the values in your current state map to a future state value, then the column will list the new value.

Patrick 2.png

3. After you have cleaned up and mapped all your existing values and created your new array, you can concatenate all your new fields into a single future state Picklist (Multi-Select) field.

patrick 3.png

4. Finally, you can load the data back to SFDC via DBAmp, Data Loader, the Data Import Wizard, or whatever your favorite tool is.

Picklist (Multi-Select) and checkbox fields can create havoc when reporting in Salesforce.com or referencing them in other formula fields. There are other options listed below:

  • Most ETL tools, such as Talend, can also remap values with simple point and click.

  • Create several dependent pick-lists and create a data hierarchy. This can also lead to confusion in reporting if the data hierarchy isn’t well defined.

  • Create a custom object with the list of values as a related list to the parent object is . This will provide better flexibility, allow for cleaner reporting, and provide the ability to restructure in the future.

There is a nice feature in the data flow section of Einstein Analytics where you can flag a field a multi-value. This will allow one to report on these field types in Einstein and will separate the values into individual values for the consumers and creators of the dashboards.

patrick 5.png

The MS SQL query that can be used for the data transformation can be found below. Each section contained in the left join below (Education, Government, Healthcare) shows how to convert and map the old values to the new values. The first statement combines and concatenates the values into a single, new Picklist (Multi-Select) field.

Let me know your thoughts, questions, or suggestions info@eigenx.com