RE: Virtual Entity Virtual Connector Provider with SQL Server & Optionsets
hi
It sounds like you may need to define a mapping between the option set values in Dynamics 365 and the integer values stored in your SQL table. Here are some steps you can try:
In your external system, create a lookup table that maps each option set value to its corresponding integer value. For example, if you have an option set for gender with values "Male" and "Female" mapped to integer values 0 and 1 respectively, your lookup table would look something like this:
Option Set Value Integer Value
Male 0
Female 1
In your virtual connector, create a new external field for the option set that you want to map. Set the External Type Name to "integer", since that is the data type of the values stored in your SQL table.
Set the External Value field of the external field to the name of the lookup table you created in step 1.
In your Dynamics 365 environment, create a new field on the entity that corresponds to the external table. Set the field type to "Option Set", and select the appropriate option set for the field.
Map the new field to the external field you created in step 2.
When you create or update records in Dynamics 365, select the appropriate option set value for the field. When the data is synced to your SQL table, it will be stored as the corresponding integer value.
When you retrieve data from your SQL table and display it in Dynamics 365, the integer value will be automatically converted to the appropriate option set value using the mapping in your lookup table.
DAniele
