Unanswered

Unable to choose Primary Key during Virtual Table refresh

(3) Share

Report

Posted on by 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!

Categories:

Microsoft Dataverse with Power Apps

VirtualTableIssue...

I have the same question (0)

All responses (10)

Answers (1)

Verified answer

EricRegnier 8,720 Most Valuable Professional on at

Like (0)

Report

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#limitations-of-virtual-tables

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!

Was this reply helpful? Yes No
Viliam Juricek 70 on at

Like (2)

Report

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

Was this reply helpful? Yes No
EricRegnier 8,720 Most Valuable Professional on at

Like (0)

Report

@NHelgren

Was this reply helpful? Yes No
matthhias 48 on at

Like (1)

Report
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=sql#for-each-data-source

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/2108647/highlight/true#M29186

Was this reply helpful? Yes No
MMCJF 6 on at

Like (0)

Report

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?

Was this reply helpful? Yes No
Viliam Juricek 70 on at

Like (0)

Report

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

Was this reply helpful? Yes No
EricRegnier 8,720 Most Valuable Professional on at

Like (0)

Report

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

Was this reply helpful? Yes No
Tommy-Upton 119 on at

Like (0)

Report

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

Was this reply helpful? Yes No
frederikbisback 222 on at

Like (0)

Report

Any update on this topic?

Was this reply helpful? Yes No
RonaldSmith 10 on at

Like (0)

Report

Still having this problem. Documentation says integer pkeys should be fine, but after creating the virtual table no data can be displayed. I get the oh so helpful "Something went wrong" error.

To clarify, Power Apps appears to create the SQL-based virtual table successfully, but for whatever reason having the primary key as datatype INT results in the error. The only primary key datatype that I have been able to use is "uniqueidentifier". String GUIDs and any integer datatype fails.

Was this reply helpful? Yes No