Virtual Table does not display a record

(0) Share

Report

Posted on by Vlad_Kv

I created Virtual Table in Dataverse using SQL connection to Serverless SQL Pool in Azure Synapse.

According to the following schema:

Virtual Table has numeric not null ID in and primary text field in Serverless SQL Pool.

GUID is generated from numeric not null ID in Dataverse, like 00000001-0000-0000-0000-000000000000, 00000002-0000-0000-0000-000000000000, ...

Records are displayed in advanced find.

But, when I select a specific record the following error appears:

APIM request was not successful : StatusCode : BadRequest, Reason : Primary key is compound by 0 elements but 1 were provided., Response from APIM: { "status": 400, "message": "Primary key is compound by 0 elements but 1 were provided.\r\nclientRequestId:

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (7)

Answers (0)

TaraAssadipour 79 on at

Like (0)

Report

@Vlad_Kv Could you fix this issue? I'm facing similar problem.

Was this reply helpful? Yes No
Vlad_Kv 11 on at

Like (1)

Report

Hi,
This is a limitation in Dataverse. A virtual table must have a numeric primary key constraint, which you cannot create in a serverless SQL pool. When you create a table, it seems to work, but when you try to access a record, it fails with the above message.
I believe this issue has been fixed in Microsoft Fabric when the virtual table is based on a Delta table in the Fabric Lakehouse.
However, in general, you need to create a table in a SQL database with a numeric primary key constraint.
Hope this helps.

Was this reply helpful? Yes No
TaraAssadipour 79 on at

Like (0)

Report

@Vlad_Kv Thanks for your reply. This is very confusing. Because based on Microsoft documents the primary key for a virtual table does not need to be strictly numeric. It can be a GUID, an integer, or a string that can be parsed as a GUID: https://learn.microsoft.com/en-us/power-apps/maker/data-platform/limits-tshoot-virtual-tables?tabs=sql

Was this reply helpful? Yes No
Vlad_Kv 11 on at

Like (0)

Report

I see. I've tried using GUID, but it didn't work.
It only worked for me if a table had a unique constraint.
Maybe during creation, the system cannot evaluate all the rows in the table, and there is no guarantee that your primary key is unique.

Was this reply helpful? Yes No
TaraAssadipour 79 on at

Like (0)

Report

Thanks @Vlad_Kv . My data resides in Fabric Lakehouse and it seems PKs are not supported in Lakehouse at this point 😞

Was this reply helpful? Yes No
TaraAssadipour 79 on at

Like (0)

Report

Was this reply helpful? Yes No
Vlad_Kv 11 on at

Like (0)

Report

Please check this post: Re: Fabric Virtual Table in Power App - Microsoft Fabric Community

Was this reply helpful? Yes No