Forum Discussion

Sam67548990's avatar
Sam67548990
Regular Visitor
2 months ago
Solved

Fabric virtual table

Can any one help me how to establish a relationship between fabric (source table) and virtual table in data verse, when I used share point i have faced some issues in 1:n relationship. Please help me...
capacities
  • Mahammadaamir's avatar
    Mahammadaamir
    2 months ago

    If you’re using Microsoft Fabric SQL Database or Lakehouse as the source for a Dataverse Virtual Table, you cannot create native Dataverse relationships (1:N, N:1, N:N) in the same way you do with standard Dataverse tables. Virtual tables are read-only metadata representations of external data, and relationship support is limited.

    Option 1: Use Fabric SQL Database with Primary and Foreign Keys (Recommended)

    If your Fabric data is stored in a SQL Database/Warehouse:

    1. Create a Primary Key on the parent table.
    2. Create a Foreign Key in the child table.
    3. Expose both tables through the Dataverse Virtual Table provider.
    4. In Dataverse, create a lookup column that maps to the foreign key.
    5. Refresh the virtual tables.

    This works better than SharePoint because SQL supports relational constraints.

    Option 2: Create Relationships in Fabric First

    Instead of relying on Dataverse:

    • Create SQL views in Fabric that join the parent and child tables.
    • Expose the view as a virtual table.
    • Use the view in your Power Apps or Power BI.

    Common Issues

    If you’re getting errors while creating a 1:N relationship, check that:

    • Both virtual tables use the same data provider.
    • The parent table has a unique primary key.
    • The child table contains the matching foreign key.
    • Data types of the PK and FK are identical.
    • The lookup column is correctly mapped.

    If you’re coming from SharePoint

    SharePoint virtual tables do not fully support relational behavior like SQL/Fabric, which is why 1:N relationships often fail. Fabric SQL or Warehouse is a much better choice for relational scenarios.

Sam67548990's avatar
Sam67548990
Regular Visitor
2 months ago

Hi v-hashadapu 

 

Below is the error log i received. Please have a look. FYI I am using trail org.

 

 

  • Parchitect's avatar
    Parchitect
    Icon for Solution Sage rankSolution Sage
    2 months ago
    Thanks for sharing the error log.

     The important part of the error is: “Primary key is compound by 0 elements but 1 were provided.” That points more to a primary key / virtual table metadata issue than to a normal relationship issue.

     

    Even if your Fabric Lakehouse table has a column that is unique, Dataverse still needs the virtual table provider to recognize a valid primary key/primary identifier for the virtual table. If the connector cannot detect or map the primary key correctly, relationships and record lookups can fail with this kind of APIM/connector error.

     

    A few checks I would make:
    1. Confirm the Fabric table has a single stable key column
       - not null
       - unique
       - preferably GUID or integer
       - not a composite key
       - not generated only in a visual/query layer

     

    2. Recheck the virtual table mapping
       Make sure the key column is mapped as the virtual table primary key / primary identifier when the Fabric virtual table is created.

     

    3. For a standard table → virtual table subgrid scenario
       The relationship should normally be modeled as an N:1 relationship from the virtual child table to the standard Dataverse parent table.
       In other words, the virtual table needs to contain the foreign key column that points back to the Dataverse standard table.

     

    4. Do not try to create a lookup column in the virtual table unless the corresponding foreign key column exists in the external source.  Dataverse cannot simply add a new physical lookup column into the Fabric Lakehouse table, because the virtual table data is not stored in Dataverse.

     

    Microsoft’s virtual table relationship documentation notes that relationships can be created between Dataverse local tables and virtual tables, but the relationship must be defined in Dataverse and backed by the correct source columns. It also notes that a virtual table cannot be on the “1” side of a 1:N relationship.

     

    References:

     

    So in your case, I would first recreate or validate the virtual table with a single recognized primary key column. Then create the relationship from the virtual table side as an N:1 relationship to the standard Dataverse table, using the foreign key column that already exists in the Fabric source.

     

    If the key is already mapped correctly and the same error still occurs, this may be a current limitation/connector issue with Fabric Lakehouse virtual tables exposing key metadata. In that case, the practical workaround would be to stage the data into a source that exposes a proper primary key more reliably, or copy the data into a native Dataverse table if you need full lookup/subgrid behavior.
     

    🔍Parchitect
    Solutions Architect · Microsoft Fabric Specialist

    💡Helpful? Kudos are appreciated.
    ✔️Solved? Mark as Solution so others can find it faster.