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.

[NimbiMac]

Hi, did you ever get around your issue here? I have a similar one where I have created two virtual tables from SQL which are related in SQL in the form of a bridge table, like this:

Subscription Table
.SubscriptionID

.Name

Programme Table

.ProgrammeID

.Name

ProgrammeSubscription Table

.ProgrammeSubscriptionID

.ProgrammeSubscriptionIDText (this was added to act as the Dataverse primary field)

.SubscriptionID

.ProgrammeID

Here, the ProgrammeSubscription table is the bridge table with foreign keys to both the Programme and Subscription tables. The thing is, after creating these three virtual tables, the relationships were not brought over. This means, in any model driven app, I am not able to display the related data since the Dataverse relationship is not there. If I were to add a custom relationship to these table by means of a new lookup column, I would then be unable to update/sync the the data between Dataverse and SQL as the lookup column wouldn't exist in SQL tables. Not sure if there is any caveat around adding in a custom relationship to two virtual tables.

I have been unable to find much on this topic - maintaining relationships from SQL when creating a Dataverse virtual table. 

Any advice or comments would be appreciated! 

Thanks, Mac