Problems with Virtual Tables Relationships from SQL Server

(0) Share

Report

Posted on by JS-24071625-0

I'm currently facing a complex scenario involving Dataverse Virtual Tables connected to SQL Server, and I'm hoping someone here can share insight or confirm if what I’m trying to achieve is possible (or partially possible).

I have one standard table and several virtual tables reflecting the structure of my ERP database in SQL Server.

A (standard Dataverse table
B (SQL-based Virtual Table)
C (SQL-based Virtual Table)

In SQL Server, all of these tables are related by numeric columns such as COM_Clave (decimal) or ConceptID (decimal).

However, because Dataverse doesn’t allow decimals as Primary Name Columns, I had to create corresponding text fields like sCOM_Clave or sConcepID in the Virtual Tables.

All Virtual Tables are correctly connected to SQL Server, and data loads fine.
I created N:1 relationships between them (for example, C→ B).
But when I open a record in the Model-Driven App, subgrids don’t show the related records, even though those relationships do exist in SQL Server (via COM_Clave, numeric column in SQL Server).
When creating relationships in Dataverse, I’m required to define an External Name — it cannot be left blank
If I reuse the same external column name, I get this error:
```
ExternalName 'COM_Clave' found on attribute 'jfse_comclave' of virtual entity 'C' is already used by another attribute on this entity.
```
I can’t make big changes to the SQL schema, but I can create SQL views.

What I’m Trying to Achieve
Make the relationships between SQL tables automatically “inherit” into Dataverse Virtual Tables.
Be able to open a "B" record and see all related "C" records in a subgrid — without manually setting lookups.
Confirm whether using the sCOM_Clave text column as the External Name in relationships could work reliably.
Has anyone successfully inherited or replicated SQL relationships into Dataverse Virtual Tables — especially when using SQL Views as the data source?
Is it reliable to use a text-based field (sCOM_Clave) as an External Name when the original PK in SQL is numeric (decimal)?
Are there known limitations preventing subgrids from displaying related Virtual Table data when joined by External Name?
🧾 Additional Context
Data Source: SQL Server (on-premises or Azure)
Connector: Virtual Connector Provider
Data Volume: Hundreds of thousands and related records
Environment: Dataverse + Model-Driven App (2025 version)
Official documentation references:

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (9)

Answers (0)

Suggested answer

MParikh 480 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!
Dataverse does not inherit SQL foreign keys for Virtual Tables. You must model lookups yourself. Subgrids show data only when those lookups resolve correctly.

What works

Virtual Tables support N:1 lookups to other Virtual Tables and to standard tables.

Relationships resolve through the Dataverse lookup field whose External Name matches the source FK column.

What blocks you

ExternalName must be unique per attribute in a Virtual Table. Two attributes cannot point to the same source column.

PK for a Virtual Table must be a text field mapped to a unique source column. Numeric PKs from SQL need a text projection.

Dataverse will not auto-join on SQL decimals. If PK is decimal in SQL and the lookup points to a text field with different formatting, the join fails. Subgrids then appear empty.

Pragmatic fix with SQL viewsCreate views that expose stable text keys for both PK and FK. Use identical casting so values match 1:1.

Example for table B and child C

-- Parent B CREATE VIEW v_B AS SELECT CAST(COM_Clave AS nvarchar(50)) AS COM_Clave_Txt, -- unique text PK COM_Clave, -- original numeric NAME, ... FROM dbo.B; -- Child C CREATE VIEW v_C AS SELECT CAST(ConceptID AS nvarchar(50)) AS ConceptID_Txt, -- C PK if needed CAST(COM_Clave AS nvarchar(50)) AS B_COM_Clave_Txt, -- FK to B ConceptID, COM_Clave, ... FROM dbo.C;

Tips

Use the same CAST for PK and FK. Same length, same culture. No trimming.

If decimals exist, standardize format. Example: FORMAT(COM_Clave, 'G', 'en-US') in both views, or CAST after multiplying to remove scale.

Dataverse setup

Create Virtual Table B on v_B.

Primary Key field in Dataverse: text. External Name COM_Clave_Txt. Mark as Primary Key.

Primary Name field: any text column you prefer.

Create Virtual Table C on v_C.

Primary Key field: text. External Name ConceptID_Txt.

Add lookup field B Lookup on C pointing to table B.

External Name on the lookup attribute: B_COM_Clave_Txt.

Create an N:1 relationship C to B using that lookup.

Add a subgrid of C on the B form filtered by the relationship.

Why this works

Both sides of the relationship use the same text representation of the key.

The provider resolves lookups by matching ExternalName values.

Subgrid queries C where B Lookup equals current B row PK.

What not to do

Do not map two attributes in the same table to the same ExternalName. The platform blocks this.

Do not mix numeric PK in parent with text FK in child using different formatting. Joins will not resolve.

Reliability notes

Using a text-based External Name is reliable if the value is unique, stable, and formatted identically on PK and FK.

Views should guarantee uniqueness. If needed, add a unique clustered index on the view with schemabinding over COM_Clave_Txt.

Known limitations that affect UX

No cascade behaviors on delete or assign.

No rollup or calculated columns over Virtual Tables.

Auditing, offline, and advanced business rules are limited.

Large subgrids over Virtual Tables can feel slow. Use indexed views or filtered views to reduce rows.

Validation checklist

In B: open a row, copy PK text from COM_Clave_Txt.

In C: verify B_COM_Clave_Txt has the same text for related rows.

In Dataverse, open maker portal, table C, Columns. Confirm the lookup’s External Name equals B_COM_Clave_Txt.

Test with Advanced Find or modern query experience. Query C by B Lookup equals the target B row.

Thank you!
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!

1 people found this reply helpful.

Was this reply helpful? Yes No
JS-24071625-0 14 on at

Like (0)

Report
Copy link

Link copied!
@MParikh
Follow-up: Linking Virtual Table (B) to Standard Table (A) via COM_Clave (text-based key)

Thank you very much for the detailed explanation — it really helped me fix the relationships between my SQL-based Virtual Tables (for example, C → B).
Now everything works fine when both sides are virtual and their PK/FK values match through text columns exposed in SQL views (using the same CAST).

However, I’ve now reached a new issue when trying to link my Virtual Table B to my Standard Dataverse Table A.

Scenario

Table A → Standard Dataverse table.

Table B → SQL-based Virtual Table.

Both have a common column COM_Clave (numeric in SQL, but I created sCOM_Clave as text in Dataverse in both tables).

In the virtual table B, sCOM_Clave is my Primary Name Column and exposed as text.

In table A, sCOM_Clave exists as a regular text column (not the primary key).

Problem

When I try to create a lookup in Virtual Table B pointing to the standard table A using the External Name sCOM_Clave, Dataverse does not allow the relationship to resolve, because the target field (COM_Clave in A) is not the PK.
I understand that Virtual Table lookups resolve based on External Name → Alternate Key, but since A is a standard table, it doesn’t use the SQL schema and I can’t map its PK directly.

So my question is:

👉 What is the correct way to create this relationship from Virtual Table B → Standard Table A ?
Should I:

Define an Alternate Key in the standard table A using the COM_Clave (text) column?

Then set the lookup in B to use the External Name sCOM_Clave (text), assuming it will match A Alternate Key?

Or is there any limitation that prevents Virtual → Standard lookups when the relationship depends on a text-based key instead of GUID?

Or do I need to use power automate for this? But there are a lot of records for the first time, after it can work only with the New Records.

Additional Notes

COM_Clave is guaranteed to be unique across both tables.

Both sCOM_Clave in B and COM_Clave in A are text-based and formatted identically.

The Virtual Connector Provider is used (SQL Server).

My goal is to have subgrids in A showing related B records (similar to the working C → B case).

Any clarification about whether defining the Alternate Key in the standard table is the right approach for this kind of cross-type relationship (Virtual → Standard) would be very helpful.

Thanks again for the great guidance — your previous answer solved the virtual-to-virtual relationship part perfectly.
Now I’m just trying to make the standard-to-virtual side behave consistently.

Was this reply helpful? Yes No
MParikh 480 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!
You’re correct that Dataverse Virtual Tables can link to standard Dataverse tables, but relationships are resolved via Primary Keys or Alternate Keys so external lookups to standard tables must reference a unique column that’s defined as an Alternate Key in the standard table.

Here’s how to proceed:

In your standard table (A), define an Alternate Key on the sCOM_Clave (text) column. This key must be globally unique for all records.

In your virtual table (B), configure the lookup so its External Name matches this Alternate Key (i.e., sCOM_Clave). This allows Dataverse to correctly resolve relationships between table B (virtual) and table A (standard).

This pattern works well for text-based columns if their values are unique and consistently formatted. Dataverse will match records based on exact values in the Alternate Key, so ensure your SQL views and Dataverse table use the same formatting/casting logic for text columns.

Known Limitations:

Relationships won’t resolve if the Alternate Key isn’t unique, or if there are formatting mismatches between the columns.

Virtual Table → Standard Table lookups do not support GUID as a join unless explicitly mapped; using a text-based Alternate Key is the standard solution for cross-connector relationships.

Subgrids in standard tables will display related virtual table records if this mapping is successful—in large datasets, indexed or filtered views on the SQL side can help performance.

Practical Steps:

Add an Alternate Key for sCOM_Clave in Dataverse Table A.

Point the lookup in Virtual Table B to this key using the same External Name.

Validate that subgrid results are accurate by comparing values on both sides.

Power Automate is a workaround if you need to sync on a schedule or trigger lookups for new records, but defining the Alternate Key is the preferred and scalable approach for direct relationships.

Thank you!
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!

Was this reply helpful? Yes No
JS-24071625-0 14 on at

Like (0)

Report
Copy link

Link copied!

@MParikh

Thanks for your answer, I still have this issue.

I forgot to say that in my table B the PK is ConceptID and COM_Clave is just another column and also in the view sCOM_Clave is another column.

Something happen when creating the relationship and the alternate key, if I relate table B with the External Name sCOM_Clave of table A. Nothing appears in the sub grid, no error but no records, but there is something very weird, when I create the relationships I can't see my virtual tables related to table A. it gives me the error of the image "We weren't able to open your table. Try reloading or reopening." I have another Virtual tables (C) and (D) with no relationships and the same connection and gateway and works fine, but if I delete the relationship between table B and A I can see the table, but with the relationship created I can't see it.

I don't know if the relationship is trying to relate ConceptID with sCOM_Clave or if the external name solves it.

Thanks for your help.

Was this reply helpful? Yes No
MParikh 480 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!
I think there might be a solution, Your failure comes from how the lookup attribute in B is mapped, key uniqueness on A, and External Name collisions on B.

What is going wrong

B uses ConceptID as PK. sCOM_Clave in B is a separate column. The lookup to A must use a dedicated lookup attribute on B whose External Name maps to a single text column in the B view that equals A’s alternate key value.

If B already has a plain text column with External Name sCOM_Clave, then adding a lookup attribute that reuses the same External Name blocks the table or returns empty subgrids. ExternalName must be unique per attribute in a virtual table.

If A’s alternate key is not active, not unique, or the text lengths or formatting differ, the join resolves to nothing.

The maker UI error when the relationship exists points to one of these: ExternalName collision, bad target key, or provider failing to expand the lookup.

Fix recipe

Stabilize keys in SQL views
Parent A (standard Dataverse, but mirror its key format in SQL)

Decide the exact text format for COM_Clave. Example: CAST(COM_Clave AS nvarchar(50)).

Use the same format everywhere.

Virtual B view

CREATE VIEW v_B AS SELECT CAST(ConceptID AS nvarchar(50)) AS ConceptID_Txt, -- PK for B CAST(COM_Clave AS nvarchar(50)) AS sCOM_Clave_Key, -- for lookup to A CAST(COM_Clave AS nvarchar(50)) AS sCOM_Clave_Text, -- plain text column, separate ExternalName ... other columns ... FROM dbo.B;

Notes

Two separate columns in the view, even if values are equal. This prevents ExternalName reuse in Dataverse.

Configure Dataverse table A

Add a text column sCOM_Clave.

Create an Alternate Key on sCOM_Clave. Wait until its status shows Active. Resolve any duplicates before proceeding.

Rebuild virtual table B

Virtual table PK: ConceptID_Txt mapped as the Primary Key.

Add a normal Text column mapped to External Name sCOM_Clave_Text for display.

Add a Lookup column “A Lookup” that targets table A. Set its External Name to sCOM_Clave_Key. Do not reuse sCOM_Clave_Text here.

Save and publish.

Create relationship and subgrid

The N:1 from B to A will use “A Lookup”.

On A’s form, add a subgrid of B filtered by this relationship.

Verify mapping end to end

Pick one A row. Copy its sCOM_Clave value.

In SQL, confirm v_B.sCOM_Clave_Key equals that value for expected B rows.

In Dataverse, open B columns. Confirm the lookup’s External Name equals sCOM_Clave_Key.

Check A’s Keys page. Status must be Active.

Test with Advanced Find. Query B where “A Lookup” equals the target A row.

Common pitfalls checklist

ExternalName collision in B. Avoid mapping two attributes to the same External Name.

Length mismatch. If A.sCOM_Clave is nvarchar(50), expose nvarchar(50) in v_B. No trimming or padding.

Formatting drift. Decimals must be formatted the same. Use the same CAST everywhere.

Alternate Key not unique or still building. Fix duplicates and wait until Active.

Security. Ensure users have read on A and B, and on the connection reference.

Metadata cache. If you changed External Names or keys, delete and recreate the lookup attribute rather than only editing it.

If you still see “We weren’t able to open your table”

Remove the plain text sCOM_Clave attribute that shares the same External Name as the lookup, or remap it to sCOM_Clave_Text in the view.

Drop and recreate the lookup attribute with External Name sCOM_Clave_Key.

Republish the virtual table definition.

Reopen the maker page.

Performance tips

Use indexed views or a narrow view for B to reduce subgrid load.

Avoid large string lengths for keys. nvarchar(50) is plenty.

Keep the subgrid filtered by current record only.

When to use automation

Use Power Automate only if you plan to copy keys into standard lookups or denormalize. For pure virtual-to-standard resolution, Alternate Key on A plus the lookup mapping on B is the correct approach.

Thank you!
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!

1 people found this reply helpful.

Was this reply helpful? Yes No
JS-24071625-0 14 on at

Like (0)

Report
Copy link

Link copied!

@MParikh

Thanks for all your support, Honestly, I’m starting to go a bit crazy with this setup 😅 — I’ve tried exactly what you described step by step, but it still doesn’t work for me.
In my case, table A (standard Dataverse table) has both COM_Clave (decimal) and sCOM_Clave (text).
Table B (SQL-based Virtual Table) also exposes both columns.

I created Alternate Keys on both columns in table A (to test which one would actually resolve the lookup), and both are active.
In the SQL view for B, I also expose both columns (decimal and text).

However, since in my scenario I don’t actually need both columns in table B, I’ve tried deleting one of them after the relationship is created — and that’s where the weird behavior starts.

After creating the relationship, I get no errors, but I also get no related data shown at all.
Even for testing, I only have one record in table A, so it should be a perfect 1:many scenario… yet nothing shows.

What’s even stranger is that once the relationship exists, I can’t even open the Virtual Table B anymore — not in Maker Portal, not in the grid, nothing. It just refuses to load. Same goes for the related views.

Was this reply helpful? Yes No
Suggested answer

MParikh 480 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!
I am sorry to hear that, Here are practical alternatives that I can suggest if you want to implement. Pick based on UX needs, latency tolerance, and governance.

Option 1. Materialize B into a standard table

Use Dataflow Gen2 or ADF to load SQL B into Dataverse on a schedule.

Create a GUID PK in Dataverse, store COM_Clave and sCOM_Clave as text, add indexes.

Create a 1:N from A to the new B_std on sCOM_Clave via an Alternate Key.

Replace the subgrid to point to B_std.
Pros: native relationships, fast subgrids, works with large data.
Cons: storage cost, sync pipeline, near-real-time only.

Option 2. Keep B virtual, replace subgrid with a Canvas component

Add a Canvas App control on A’s form.

OnVisible: Set(varClave, ThisItem.sCOM_Clave).

In the gallery Items: Filter('[dbo].[v_B]', sCOM_Clave_Key = varClave).

Expose only needed columns to keep it snappy.
Pros: no schema fights, no data duplication.
Cons: not an OOB subgrid, fewer OOB commands.

Option 3. Mapping table bridge

Create standard table Map_AB with columns:

sCOM_Clave (text, Alternate Key, indexed)

A_Lookup (lookup to A)

Populate Map_AB once with all distinct sCOM_Clave, link each to A.

In B (virtual), add a lookup to Map_AB using External Name sCOM_Clave.

On A’s form, show a subgrid of B filtered via the Map_AB relationship.
Pros: keeps B virtual, gives an OOB subgrid path.
Cons: one-time and incremental maintenance of Map_AB, extra join hop.

Option 4. Reporting only, skip model-driven subgrid

Use Power BI with DirectQuery to SQL for B and Dataverse connector for A.

Relate on sCOM_Clave in the model, embed the report on the A form.
Pros: rich visuals, no Dataverse schema constraints.
Cons: not a Dataverse subgrid, depends on report refresh/permissions.

Option 5. Make both sides virtual

If A’s data originates in SQL, expose A as a virtual table over a view aligned with B.

Use the virtual-to-virtual pattern you already proved works.
Pros: single pattern, no duplication.
Cons: only feasible if A does not need Dataverse-only features.

What I recommend

Need native subgrid and commands today, and near-real-time is fine: Option 1.

Want to stay fully virtual and keep Dataverse light: Option 2.

Must keep B virtual but want an OOB subgrid with standard relationships: Option 3.

If you want, tell me which route you prefer and I will give you exact build steps, including view SQL, Dataflow settings, column schemas, and the form setup.

1 people found this reply helpful.

Was this reply helpful? Yes No
JS-24071625-0 14 on at

Like (0)

Report
Copy link

Link copied!

@MParikh

Thank you so much for your continued support.

One question? Have you achieved this , I mean is this even possible ? relate standard with virtual tables like I want it do to it and is this just a matter of settings?

Also in table A i have a GUID (database guid not sql) column that is my primary name column and it could have a relationship like COM_Clave with table B, but in table B I dont have that GUID column but in table E that is related in SQL with B i have it. Will that work if in the view i make a join and bring GUID column and make the relationship in dataverse with it? COuld that work better than with COM_Clave but that text column GUID is not PK in ay table in SQL but i have that value in table A.

Option 1. Materialize B into a standard table: I think this will be very expensive table B will be like 10 million records.

Option 2. Keep B virtual, replace subgrid with a Canvas component: Could work but in another standard table (not A) want to relate records in the form from table B filtering in the forms by the table A . and it will not be possible in canvas app ?

Option 3. Mapping table bridge: Will have the same problem when relating standard table with virtual but now in the bridge table , or what will be different.
Option 4 and 5 not viable.

Was this reply helpful? Yes No
Suggested answer

MParikh 480 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!
Yes, Standard → Virtual relationships are supported. The mechanism works through Alternate Keys on the standard table, with the virtual table's lookup External Name matching that key. I have seen this pattern work in production environments. I haven't yet implemented these strategies at this scale, but these are the options I am contemplating.

Your repeated failures suggest either:

ExternalName collision on B

Alternate Key on A not fully indexed

Text formatting mismatch between A.sCOM_Clave and B.sCOM_Clave_Key

The "can't open table" error after adding the relationship indicates an ExternalName conflict or provider metadata issue.

About your GUID approach:

Bringing GUID from SQL table E into B view via JOIN could work better than COM_Clave. Here's why:

If A uses GUID as PK and E contains matching GUIDs, you can:

Create view v_B with CAST(E.GUID AS nvarchar(50)) AS GUID_Key

Map B lookup External Name to GUID_Key

Point that lookup directly to A's PK

This avoids Alternate Keys entirely because lookups to standard tables resolve via PK by default when the External Name matches a GUID format.

Critical: SQL GUIDs must format as xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx. Use LOWER(CONVERT(nvarchar(36), E.GUID)) in your view.

About your options:

Option 1 with 10M rows: expensive, yes. Factor Dataverse database capacity costs before choosing this.

Option 2 Canvas workaround: you asked about filtering B by related A in another form. Canvas components can accept context variables, so you can filter B by A.sCOM_Clave or A.GUID passed from the parent form. Requires custom connectors if B exceeds delegation limits.

Option 3 Mapping bridge: the bridge table Map_AB is standard, not virtual. You populate it once with all distinct sCOM_Clave values and link each to A. Then B (virtual) looks up to Map_AB (standard) using sCOM_Clave. This avoids the Virtual → Standard direct link that keeps failing for you. The relationship chain becomes: A ← Map_AB ← B. Both hops use proven patterns.

My honest recommendation:

Try the GUID approach first. If E.GUID exists and matches A.PK, your view joins B and E, exposes GUID_Key, and B's lookup points to A via that GUID. This removes text formatting risk and Alternate Key dependency.

If GUID works, you solved it without workarounds.

If GUID still fails with the same "can't open table" error, then Option 3 is your safest path. I have not personally deployed 10M row virtual tables with cross-type relationships, but the mapping bridge pattern is documented and supported.

If these approaches still encounter issues: I encourage you to review the entire configuration once more against the steps outlined in my earlier responses. Sometimes small details like column formatting, metadata caching, or security roles can block relationships in ways that are not immediately obvious. Walk through the validation checklist systematically, and if something still feels off, share screenshots of your exact column mappings and relationship definitions. Fresh eyes on the specific configuration often reveal the missing piece.

Was this reply helpful? Yes No