The recent release of Dynamics CRM (v9) introduced a new data type, the MultiSelect Option Set. This datatype is similar to picklists, but allows the end user to select and store more than one option from the picklist. While providing a significant user interface convenience, it presents challenges when trying to process data for migrations or integrations. Values selected by an end user are stored in a single comma delimited field, an array, while the numeric identifiers for each value are stored in a different field. Scribe has released an update to the Microsoft Dynamics 365 Sales and Service / CRM connector that supports the manipulation of data for this new data type.
In this blog, I will provide examples of how to utilize Scribe’s cloud-based iPaaS platform and connectors to work with data stored in MultiSelect Option Set fields. These examples also show how to accomplish a basic search of the data using a formula and how to flatten the data to simplify the subsequent processing actions.
Before reviewing these examples, it is important to understand the data storage structure when the MultiSelect Option Set data type is utilized. When defining a field using this data type, both a label and a value are required. The value is a numeric identifier bound to your Dynamics instance and the value is the text displayed for selection by an end user. This can be seen in the Dynamics CRM interface shown below once you select ‘MultiSelect Option Set’ as the data type.
Figure 1. Option of Item, Value of 100,000,000
After creating the field in Dynamics CRM, Scribe will present two source string fields to display the information selected by a user when interacting with a record. The <fieldname> is the display name that was entered when creating the field, the ‘new’ is the default used for any custom field.
- new_<fieldname> (Comma delimited list of labels for the selected row)
- new_<fieldname>_displayname (Comma delimited list of values for the selected row)
Tip: Microsoft does not explicitly prevent a comma or other special characters when defining the labels for these fields but as a best practice this should be avoided. The comma will be preserved when it is stored in the database making the flattening operation significantly more difficult.
Tip: When using these fields in a Map as a target only the new<fieldname> is available to map values into with labels that are defined in Dynamics CRM.
Example #1: Searching the Data
If your use case involves the need to check for a specific value to perform an action, you can use one of the standard Scribe formulas. In an IF or LOOKUP block use the FIND () function as shown in the formula below using your selected search string. Using this formula, if the result is greater than 0, it indicates the presence of your selected search string in the list of values. In this example if the user selected the value of ‘Partner’ in the MultiSelect Option Set, then the formula it will return a value greater than 0 and execute the subsequent blocks in the IF section of the IF/ELSE Block. If the value of Partner is not found, it will execute the subsequent Blocks in the ELSE section of the IF/ELSE Block.
Tip: Scribe formulas are case-sensitive.
Example #2: Flattening the Data
If your use case involves the need to determine the exact number of values in the field or interpret the results differently depending on the potential values, you can flatten the data to simplify subsequent data processing steps.
For this example, I am using a MultiSelect Option Set on the account called AccountType. The example data set is:
To complete this operation, I will use the Scribe Variables Connector and a MySQL DB Connector to process the data from Dynamics CRM. The flow of the Map is to read each row of account data within a loop and then write each individual value to a variable until the variable value is NULL, indicating no additional values exist in the field. After each value is stored within a variable, additional processing steps can be added or, as in this example, the data written to a database table that can then be used as a source in subsequent Maps.
If you are not familiar with the Scribe Variable Connector, information on how to use it can be found on the Scribe Success Community by following the hyperlink.
Here is a sample map with descriptions of the steps to explain the operations:
The data resulting from running this map is now flattened in the database as shown below:
An export of the sample map used in this blog can be downloaded from this Knowledge Base article on the Scribe Success Community.
- Currently filtering in a Query block is not supported for MutiSelect Option Sets. To filter these fields, use a formula in an If/Else or Lookup block.
- This same process can be used for multiple data values stored in a field, such as Salesforce multi select fields, as long as there is a fixed delimiter.
- Additional information can be found in the Scribe Help for the Scribe Connector for Microsoft Dynamics 365 / CRM
Share your experiences with us:
Featured image credit goes to Dynamics Feature a Day. See the original image and article here.