Virtual Tables and Relationships

[sasrsc]

Novice alert!
I have an extensive on-prem SQL database with relationships, etc.. (I am not a DBA). We have just created an on-prem data gateway and I can connect using the SQL Connector. Great. I know in my Power App I want to show galleries with data from the related tables for example.
Raw Data:
Hotel Table: PropertyName, ChainID, CountryID
Chain Table: ID, ChainName
Country Table: ID, CountryName
etc...
A normal gallery may show Property Name, Chain Name and Country Name.
This means I have to build my collection or items property joining across the tables.
I don't have any views across the tables created already in SQL. I have 2 choices as I see it. 
1) Ask the SQL DBA to build the views 
2) Build the "joins" in the Power App (Lookups, etc..)
But then I noticed a 3rd option...
3) Use Virtual Tables and build the views myself in dataverse. What a good idea...I thought.

So I created all my virtual tables and it adds the guids for each table but it didn't recognize the existing relationships. OK, I thought, I will build them manually. There are 200K rows in some of the tables so I didn't want to rebuild any "joins" manually. But when I attempt to create a new relationship in dataverse on these tables it always wants to reference the GUID field and not the existing integer columns that contain the join relationship in SQL. 

My question is can I build a relationship between columns and NOT use the GUID's in Dataverse. How? It's not intuitive as to how that is done.

Note: I am building a Canvas App and have no interest in a Model Driven App in this case.

[ThomasMV1]

Ideally you would recreate the lookup in dataverse between the virtual tables. If you created a SQL view, this would appear as a separate table, and for the dataverse view you would need lookups to be configured anyway.

The issue you might be seeing when trying to create a new relationship is due to your configuration in SQL. Has the integer column been assigned as primary key in the table? If it is not assigned the primary key, then the virtual table config wizard will just choose the guid column. Also check the integer column is an INT column and not any other type. Primary keys for VT's can only be INT or GUID. If your foreign key is an int, then the primary key in SQL will need be an INT, or vice versa.

To recreate the relationships, you would delete the old foreign key column and re-add it into the old-style CRM advanced settings page, but re-add it as a relationship. Keep the external name the same as it is in SQL, then point it to your other virtual table.

Inside your canvas app you can then just build out collections with related records or use the . notation to get related record info inside a gallery.

[sasrsc]

This is helpful, so it looks like in the case of these 2 tables specifically ... 
I'm choosing 2 different tables as these are the cleanest...
dbo.DictContinents 
id = PK, smallint
dbo.DictCountries
id = PK, int
continentID = FK, smallint.

I assume Dataverse recognizes smallint as well as int?

So therefore it sounds like I can use the old-style CRM advanced settings page to get this correct. You agree this is likely acceptable to do this without touching SQL. Yes, the thought of using .notation is really appealing.

[sasrsc]

also where do I access the "old style" CRM? it's not intuitive?

[ThomasMV1]

If the primary key is set to small INT, then it will probably not recognise that. Ideally the primary key would just be an INT. What you could do is create a SQL view, just SELECT columns, cast (PKcolumn AS INT) FROM tablename.

Casting the primary key as INT should help Power Platform recognise it as a possible primary key. 

I don't think you'd actually need the advanced settings (Settings cog in Power Platform -> Advanced Settings). In dataverse you should be able to just create a new relationship, as long as the external name is how it appears in sql.

Please mark this as solved if this works for you 🙂

[Khonzi]

This worked for me. Great solution.
It is a shame that we can't build relationships in the new view yet but the fact that I could maintain my SQL relationships in the old view makes up for it. Little pain for massive gains.

[Craig1]

Good evening folks,

I am also having problems trying to create a relationship between 2 virtual tables (linked to SQL Server). In SQL Server I have a party (person) table and a party type table linked by a party type ID in the Party Table. 

I am puzzled by the comments above that say there isn't a way to add relationships in the "new view" as this does exist. I have set up my 1-to-N relationship between the Party Type virtual table and the Party Table using the new view and it saves the relationship without any issues and creates a lookup column in the Party Table.  I then select the Party Table (from Tables) and view the current records which look fine until I add the new lookup column to the view. At which point I get an error "Something went wrong, We weren't able to open your table. Try reloading or reopening." 

I would be very grateful if someone can help 🙂

Kind Regards, Craig

[WheresWally]

Hi All, 

I'm really struggling with this as well. Any help would be greatly appreciated. 

Here are my tables:

TableName: PeoplesFavouriteColours

ID [int] Identity (1,1) NOT NULL	Primary Key
Favourite colour [int] NULL	Foreign Key (the MANY of the 1-*)

CONSTRAINT [FK_PeoplesFavouriteColours_Colours] FOREIGN KEY([Favourite colour])

TableName = Colours

ID [int] Identity (1,1) NOT NULL	Primary Key (The ONE of the 1-*)
Colour Name	This is the column I'd like to display in a Powerapp form for selection or displaying what's already selected.

Both are on prem sql servers which I add as Virtual Tables. Both say they are using the ID column as the Primary Key when I add the tables. Both do still get an automatic GUID column - I don't think that matters, just noting it.

I then go into PeoplesFavouriteColours table and delete the Favourite colour column.  

I add a relationship to that same table and select the Colours table as the related table. 

I put Favourite colour as the external name and save it. I try to view the table and get the same error as craig above - something went wrong. 

Has something changed? Is it the case maybe that now (May 2024) I must use GUIDs in SQL? Thanks

[WheresWally]

Hi All, 

A very clever colleague of mine managed to solve it last night. The situation to recap is that we have 2 x virtual tables based on the same on prem SQL server source (via gateway). We were trying to create a relationship and kept getting errors when viewing the column in the table. 

The solution was that the external name is not the name 'just as it is in SQL'. It's the way that Powerapps names the column when first creating the virtual table. And if you have a space in the name, it adds x0020. Now you will see those x0020 additions in the column name when viewing the table and clicking Columns. However! this is not the name you want. Select the ... menu on the column and choose Edit, then go to Advanced options and scroll down to 'External name' (note this is all fresh after table creation and before you delete the original column that you're replacing with the relationship/lookup). 

The underscores added were surprising and once we'd added those it worked perfectly. Note that when viewing the table, the column will display the primary name column you chose when setting the table up. Guessing that once in app, you can choose other columns from the lookup but not certain as I haven't tried yet.