Dataverse Virtual Table to SQL Server - How to map choice column in Dataverse to column in SQL

[cfebvre]

Hi All,

I'm trying to create a new choice column in my Virtual Dataverse table. When creating other column types all I've needed to do is ensure there is an equivalent column in SQL and provide the 'external name' of that column.

However, the choice column type requires an additional property: 'External type name'. This is defined when setting up the global choices in the solution.

The documentation surrounding this is fairly brief, all it says is:

'If the column type you create is OptionSet: This property maps to the external name of the set of values in the external service for the choice . Typically, this can be an enum or name of a string value class. The External Type Name can be used when a fully qualified name is required. For example, as the Type Name with OData where parameters in a query need the fully qualified name, such as [Type Name].[Value].'

https://docs.microsoft.com/en-gb/power-apps/maker/data-platform/create-edit-virtual-entities#create-...

I'm just struggling to understand what this means in the context of SQL. SQL Server doesn't not have an enum data type for a column, and most other info I can find about setting up choices in SQL recommend creating an additional table with a key to the primary table - but I don't *think* that's what I should be doing in this instance. I don't even think I need to list the full set of values in SQL. I only need to store the reference to the value from Dataverse - but I don't know for certain.

Has anyone had any experience with this? I haven't been able to find any examples or blog posts from anyone.

Cheers in advance.

[IsaacViray]

Hi Kudo,

Did you get any chance to make this work?  I am in a similar situation and the choice field is causing an error when I am trying to save the form.

[SamahBentaybi]

I'm in the similar situation I would like I there are a solution ?

Thank's

[IsaacViray]

I raised a ticket with MS, I will provide an update if they have a solution.

[remkotsfc]

We struggle with the same issue. Did you get your issue resolved?

[IsaacViray]

MS came back to with the reply below about dropdown in power pages for SQL/external table:

"SQL service does not currently have a standardized Option Set type that aligns with the Dataverse Choices. In Dataverse, an Enum value set is used, which is linked to labels for display. A column for an option set will only be created in Dataverse if the source is an option set value. This is the current design limitation."

"However, there is a workaround available. You can use a string column and implement a PowerApps Component Framework (PCF) control that mimics an Option Set. This will allow you to establish a virtual table connection between SQL and Dataverse. I recommend checking out the CSV Dropdown Control | PCF Gallery, which provides an example of creating a dropdown control in CRM."

I will try my luck with their suggested solution.

[cfebvre]

Hi @IsaacViray  - good that there's a potential workaround to this problem. We ended up not pursuing this further for a few reasons, so I never chased this further, sorry. The PCF sounds like a superficial shell over the top of what is ostensibly a string column type, so this *should* work as far as the link between SQL and Dataverse is concerned.

[remkotsfc]

Hi @IsaacViray , thank you for getting back on this. Appreciated.

It would have been nice if Microsoft would be more vocal about this limitation in their documentation....
So basically, a Dataverse virtual table with an SQL Connector does not support OptionSets (Enums).  

Fortunately for us, the CSV Dropdown control could work for us.