This article provides examples of how to use TIBCO Scribe® Online 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 used.  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, TIBCO Scribe® Online presents two source string fields to display the information selected by a user when interacting with a record.  The <fieldname> is the display nameentered when creating the field, the ‘new’ is the default used for any custom field.

1. new_<fieldname> (Comma delimited list of labels for the selected row)
2. 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 is 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 TIBCO Scribe® Online 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 returns a value greater than 0 and executes the subsequent blocks in the IF section of the IF/ELSE Block. If the value of Partner is not found, it executes the subsequent Blocks in the ELSE section of the IF/ELSE Block.

Tip: TIBCO Scribe® Online 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.

This example uses a MultiSelect Option Set on the account called AccountType.  The example data set is:

To complete this operation, 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 TIBCO Community in the following article: Using The TIBCO Scribe Labs Variables Connector

Here is a sample map with descriptions of the steps to explain the operations. A copy of this Map is attached to the article.
 

The data resulting from running this map is now flattened in the database as shown below:


Additional Tips

• 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 TIBCO Scribe® Online Help for the TIBCO Scribe® Online Connector for Microsoft Dynamics 365 / CRM

Disclaimer