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

It wasn’t your decision, you warned everyone against it, but not now you are stuck with the dreaded Picklist (Multi-Select) field type and even worse you must clean up and map the existing data values based on new business needs. Where do you start? What do you do?

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.

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.

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.

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.

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

/**Script for transforming Picklist (Multi-select) values values**/  
SELECT [account].[Id],
       CONVERT(NVARCHAR(MAX),[Industry - Current State]),
       [Education],
       [Government],
       [Healthcare],
       CASE WHEN [Industry - Current State] IS NULL 
            AND [Education] IS NULL 
            AND [Government] IS NULL 
            AND [Healthcare] IS NULL
            THEN [Industry - Current State]
            ELSE CONCAT([Education],[Government],[Healthcare]) 
       END AS [Segment - Future State]
FROM [Account]


--Education
       LEFT JOIN 
       (
       SELECT [Id] AS [account.ID],
              CASE WHEN [Industry - Current State] LIKE '%High School%' 
                   OR [Industry - Current State] LIKE '%Middle School%' 
                   OR [Industry - Current State] LIKE '%Pre School%' 
                   OR [Industry - Current State] LIKE '%College%' 
                   THEN 'Education;'
              END AS [Education]
       FROM [account]
       WHERE [Industry - Current State] LIKE '%High School%' 
             OR [Industry - Current State] LIKE '%Middle School%' 
             OR [Industry - Current State] LIKE '%Pre School%' 
             OR [Industry - Current State] LIKE '%College%' 
       ) AS [Education]
       ON [account].[Id]=[Education].[account.ID]


--Government
       LEFT JOIN 
       (
       SELECT [Id] AS [account.ID],
              CASE WHEN [Industry - Current State] LIKE '%Capitol%' 
                   OR [Industry - Current State] LIKE '%Police Station%' 
                   OR [Industry - Current State] LIKE '%Military Facility%' 
                   OR [Industry - Current State] LIKE '%Prison/Jail%' 
              THEN 'Government;'
              END AS [Government]
       FROM [account]
       WHERE [Industry - Current State] LIKE '%Capitol%' 
             OR [Industry - Current State] LIKE '%Police Station%' 
             OR [Industry - Current State] LIKE '%Military Facility%' 
             OR [Industry - Current State] LIKE '%Prison/Jail%' 
       ) AS [Government]
       ON [account].[Id]=[Government].[account.ID]
                     
--Healthcare
       LEFT JOIN 
       (
       SELECT [Id] AS [account.ID],
              CASE WHEN [Industry - Current State] LIKE '%Acute Care%' 
                   OR [Industry - Current State] LIKE '%Clinics%' 
                   OR [Industry - Current State] LIKE '%GPO%' 
                   OR [Industry - Current State] LIKE '%GPO System%' 
                   OR [Industry - Current State] LIKE '%Independent System%'
              THEN 'Healthcare;'
              END AS [Healthcare]
       FROM [account]
       WHERE [Industry - Current State] LIKE '%Acute Care%' 
             OR [Industry - Current State] LIKE '%Clinics%' 
             OR [Industry - Current State] LIKE '%GPO%' 
             OR [Industry - Current State] LIKE '%GPO System%' 
             OR [Industry - Current State] LIKE '%Independent System%'
       ) AS [Healthcare]
       ON [account].[Id]=[Healthcare].[account.ID]