Microsoft Dynamics 365 | Integration, Dataverse...

Virtual Entity Virtual Connector Provider with SQL Server & Optionsets

(0) Share

Report

Posted on by kubab

Hi, so I am using the virtual connector provider, as described here:

https://docs.microsoft.com/en-us/power-apps/maker/data-platform/create-virtual-tables-using-connectors?tabs=sql

It's working OK, but I can't get optionsets (choices) to work. In the SQL table, the values are simply stored as integers, as they would in a dynamics table. So I create my external field, set it as a choice field then select the correct global optionset. I assumed it would read and write the integer value to the SQL Server, but it seems it is trying to do something else.

The error I get when trying to update for example is: "Can not convert Object to String"

The documentation isn't very helpful. It could be I need to populate External Type Name and maybe External Value with something, but I don't have a clue as to what.

Anyone got any ideas??

All responses (4)

Answers (0)

KJP838 212 on at

Like (0)

Report

Virtual Entity Virtual Connector Provider with SQL Server & Optionsets

I am getting the same error Exception Message: OptionSet value was not found for Id 1. Please refresh the Virtual Entity if External OptionSet has been updated".

Did you ever find a solution to this? I cannot get option sets to work with virtual entities.
Subash Dekshinamoorthy 5 on at

Like (0)

Report

RE: Virtual Entity Virtual Connector Provider with SQL Server & Optionsets

Hi DAnny3211, Thanks for your suggestion. I have followed your instruction and created Optionset for 'Status' field in Virtual Entity connected with SQL Server Data Source.

I'm getting an "Exception Message: OptionSet value was not found for Id 1. Please refresh the Virtual Entity if External OptionSet has been updated" error when try to create / update the record.

Attached screen shots below for your reference. Kindly let me know your advise to resolve this issue. Thanks!
Suggested answer

DAnny3211 9,274 Moderator on at

Like (1)

Report

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
Suggested answer

miguelbeja on at

Like (0)

Report

RE: Virtual Entity Virtual Connector Provider with SQL Server & Optionsets

Choices are a specific type (name + value) that doesn't have a direct map to SQL

The only workaround that you can use is to have a string field from SQL and use a PCF from the existing gallery that mimics an OptionSet field.