cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cfebvre
Kudo Collector
Kudo Collector

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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
dpoggemann
Super User
Super User

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-... 

 

Hope this helps. Please accept if answers your question or Like if helps in any way.
Thanks,
Drew

View solution in original post

3 REPLIES 3
dpoggemann
Super User
Super User

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-... 

 

Hope this helps. Please accept if answers your question or Like if helps in any way.
Thanks,
Drew

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

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

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Users online (3,662)