Unable to choose Primary Key during Virtual Table refresh

[Ashwini_Raghave]

Hi,

While we try to refresh the dataverse virtual table, on the screen where we need to choose the Primary Key and Primary Column, we are unable to see any fields populated under "Primary Key". Only "Primary Column" has most of the fields. Our actual MSSQL table has integer fields which used to appear under "Primary Key" field earlier. 

We need to get this fixed as soon as possible. Please help!

[EricRegnier · Accepted solution]

Hi @Ashwini_Raghave, 

You cannot see anything in the list of primary keys because unfortunately there's a current constraint with Virtual Tables where the primary key needs to map to a GUID column in the external table. For more info, see limitations at: https://learn.microsoft.com/en-us/power-apps/developer/data-platform/virtual-entities/get-started-ve...

You can submit the feedback/idea at https://ideas.powerapps.com/d365community/mycontent/ and I'll vote! I agree it should be possible and a common scenario 🙂

Cheers!

[Wiliam_Juricek]

Hello @Ashwini_Raghave ,

I have found same issue as you have, during last 2 weeks we've been creating Virtual Tables from Synapse Serverless without any issue. Today I needed to add another table and suddenly the wizard says table is missing primary key.

Something must have changed in the wizard because for us there is no option to have primary key since Synapse SQL Serverless External Tables do not support constraints like this.

I have found similar issue while searching: Unable to add on-premise SQL Server tables to Dataverse 

There even is a video from MS showing Virtual Table creation from Synapse Serverless but this is currently not possible. Any table without primary key on datasource will fail the creation for me today.

Build Power Apps from Azure Data Lake with Virtual Tables 

@EricRegnier I see that you're using a different documentation page as reference. Have a look here, according to this documentation not having primary key on the source table is acceptable with some restrictions.

Limitations and troubleshooting virtual tables with Power Apps - Power Apps | Microsoft Learn

Someone from Microsoft should explain what happened here.

BR

Wiliam

[EricRegnier]

@NHelgren 

[matthhias]

Hi Eric, 

there is obviously provided mixed information by Microsoft. I read the same, source you mentioned, but I also found detailed information on SQL connections that say that integer works as well for SQL Server:

(typos are from the original source)

https://learn.microsoft.com/en-us/power-apps/maker/data-platform/limits-tshoot-virtual-tables?tabs=s...

• SQL virtual tables can use a GUID or an Integer field for the Primary Key for funn functionality.
• SQL Server tables without primary keys: Any non-string field can be selected as the primary key. The virtual table should be created successfully. RetrieveMultiple will work, the other operations will fail with the following error message (coming from SQL connector): "APIM request wasn't successful: BadRequest: No primary key exists in table". For funn functionality a GUID or integerfield must be used as the primary key.

I was able to build virtual tables from tables with integer primary keys without any problem, even from SQL views on these tables without any problem until some days ago.
Now the same thing fails unfortunately.

I posted it here:
https://powerusers.microsoft.com/t5/Microsoft-Dataverse/I-can-t-add-a-SQL-virtual-table-view/m-p/210...

[MajaChr]

Hi,

I am experiencing the exact same thing. I was able to create a virtual table from an SQl data source with an integer primary key without any problems a few weeks ago. However, when I tried creating a similar virtual table last week, I was not able to select any of the integer fields as primary key.

Does anyone have an update on whether this is a bug?

[Wiliam_Juricek]

The issue still persists however if anyone needs it really badly as we did (SAP integration) then there is a workaround.

Since every table is just an xml object in customizations.xml and we already had a virtual table without primary keys, we only exported the solution and based on xml of existing virtual table created a definition of new table. It's not the most comfortable process but it works. You do need to create new guid for every form and saved query as well as sections of forms for the new virtual table and add it also into solution.xml as root component but it's manageable and after importing the modified zip of the solution, the new virtual table is fully functional.

Hope if someone needs it as much as we did this will help.

BR

Wiliam

[EricRegnier]

Hey @Wiliam_Juricek, do you try opening a MS support ticket? The MS virtual table product team usually provides great support... 

[Tommy-Upton]

Any update on this?  It's still a problem it seems.

[frederikbisback]

Any update on this topic?