When working with Dynamics 365 CE/CRM entities, there are special types of fields that may require additional attention while writing to them. An important example from these is the Option type field. An Option or Option Value field is a type of field that defines a set of options or choices to pick from. When such fields are created and displayed in a form, it uses a drop-down list control from where you can choose the value you require. Other common terminologies for these fields are picklist, option set, choice, etc. In this blog post, we shall refer to them as "Option" fields.
Now, while having to write to such fields in your Dynamics 365 CE/CRM instance, you may face a challenge if your data source and destination have a different set of values. For example, let's assume that the acceptable values for an Option field in your Destination instance are "A", "B", and "C"; however, in your Source, the same field has the values "1", "2", and "3", respectively for each record, where 1 corresponds to A, 2 to B, and 3 to C. This would create an issue when the field from the Source is mapped to the Destination, and you would end up getting an error at runtime, stating that the input values do not fall in the range of the options specified. This is expected, as the values that flow in the Source records are not what the Option field in the Destination expects. In such scenarios, you would need to implement a way to perform translations between the Source and the Destination component as part of your ETL transformation. But that would add complexity to the data flow, and would become an extra configuration or flow you would need to maintain and update as required.
This is where KingswaySoft's Option Value Mapping functionality comes in handy. In our latest release (v24.1), we have introduced this feature as a part of our CE/CRM Destination component, where you can directly configure the translations within the component, thereby reducing any overheads in the design. In this blog post, we will be going through an example demonstrating how to work efficiently with this new feature.
Defining an Option/OptionSet field.
In your CRM UI, you will find fields that have a set, or a finite set, of values to choose from. These are not static text fields where you can enter any value, instead, the value needs to be chosen from the available options. This is primarily what Option type fields are. Please find an example in the CRM Account creation form below.
In the above example, the Contact Method field can be set from any of the values that come from the drop-down list. If you wish to create or modify an Option field, you could navigate to Settings > Customization, and choose the entity. As you can see in the image below, this is the customization page for the Contact Method field, which has the name Preferred Method of Contact, and the datatype is set to OptionSet. The Option values are listed at the bottom, and you can see that one among those is also chosen as the default value - "Any".
If you wish to add any values, you could use the "+" icon on the customization page. You can also delete or modify any of the values by clicking on the appropriate ones.
Configuring Option Value Mapping in CRM Destination Component
When you map the input field to Option field in the CRM Destination component, the column that shows "Option Value Mapping" displays an ellipsis button.
When you click it, it opens the option Value Mapping editor window, where you can see that the Target Option Value has the list of available values for that specific field that we have chosen.
Let's assume that our Source data has A, E, F, M, and P, respectively for Any, Email, Fax, Mail, and Phone. We could configure the editor to perform the translations at runtime as shown below.
Now, the above should work seamlessly when all the input values correspond to what we have set in the translation table. However, when there is an unmatched value, this would need to be handled as well. The Option Value Mapping feature provides an option for this too, by using any of the options available in the Handling of non-matched values. Here, you can choose between five options as shown below. For more details on this, please refer to our Online Help Manual.
For our specific use case, we are choosing the "Replace as an existing Option" option and selecting the value "Any", as that is the default type based on the field customization.
With this setup, at runtime, you should see that when the records are being written to the CRM instance, the Preferred Contact Method field will be set according to the translations we have defined for any matching values. For records with non-matched values as input, the default value "Any" would be set for that field. The image below shows the data flow at runtime, and as you can see, the ContactMethod values are flowing from the Source, and the Destination component translates and writes them successfully.
Conclusion
Based on what we have gone through, you can see how easily Option Value translations can be handled within the CRM Destination component. This decreases the complexity of your design, removes the need for adding separate transformations in the flow, and makes it easy to handle non-matched values.
We hope this has helped!