Dataverse Virtual Tables - how to honour existing integer relationships from linked SQL tables?

[cfebvre]

Hi All,

I've set up a virtual connection to our On Premises SQL Server, and have managed to import and link a table in Dataverse.

The connection works brilliantly - for a single table.

The problem is that we have multiple existing tables related via integer fields in SQL, and I can't work out how I can possibly keep that relationship in the virtual tables within Dataverse (since Dataverse requires GUID Lookup columns).

The closest thing I've found is:

SQL to Dataverse Data Migration using Azure Data Factory

https://www.youtube.com/watch?v=Px4-QZt6OoY

(This tutorial covers how to import data and set up Lookup relationships from Integer columns in SQL, but this is a one way, one time import - not a virtual connection where the data is synced).

Has anyone had experience attempting this? Would it even be possible?

Thanks in advance.

[dpoggemann · Accepted solution]

Hi @cfebvre ,

Virtual tables no longer require an associated GUID as a primary key with the virtual connector provider.

Please look at the following portion of the documentation showing how to connect to an AccountID in the SQL source.  https://docs.microsoft.com/en-us/power-apps/developer/data-platform/virtual-entities/create-virtual-... 

https://docs.microsoft.com/en-us/power-apps/developer/data-platform/virtual-entities/create-virtual-... 

[cfebvre]

Thanks a million @dpoggemann . I'll deep dive into this next week but this is exactly the thing - thank you.