Adventures with Dataverse: Virtual Tables and Look Up Columns

Recently, I was playing around with Dataverse and Virtual Tables, specifically with the AdventureWorksLT database in Azure SQL.

Note, you can easily import this data by creating a new SQL Database in Azure.

In the AdventureWorksLT database there is a table called SalesLT.SalesOrderHeader that has a foreign key relationship to two other tables, SalesLT.Customer and SalesLT.Address.

Those were the tables I imported as virtual tables.

After creating the virtual tables I noticed that the foreign key columns came over as values, the relationships were lost! 🫨

I thought I might get lucky, and they would come over as Lookup columns, but alas, they didn’t. 😞

From Power Apps Studio I thought might be able to add a relationship and use the existing columns but doesn’t seem to be a way to do that. While I can create a new column, that doesn’t really help me out, especially if I want to persist changes back to SQL.

Maybe there is something I can do in the classic portal? 🤔

Buckle up!

To get to the classic portal, from my Solution, I clicked on Objects, then the Ellipsis (…) and lastly, Switch to classic.

I clicked the SalesLT.SalesOrderHeader entity.

Note, Tables and Entities are the same thing, Table is the modern term, and Entities the legacy term.

I clicked Fields, then adw_customerid field, and then Delete. I did need to confirm the deletion.

Once the column was deleted, I clicked on New.

I set Display Name to CustomerID, which automatically populated Name and External Name.

I then set Field Requirement to Business Required.

Now for the secret sauce! 🪄

I set Data Type to Lookup and then Target Record Type to SalesLT.Customer.

I clicked Save and Close.

I switched back to the Power Apps Portal, selected Tables, and then the SalesLT.SalesOrderHeader table.

My CustomerID now displayed the Primary Name column from the SalesLT.Customer table instead of the actual value. w00t! w00t! 🥳

If I add that field to a Form it will now display as a Lookup control. 👏

Not super revolutionary, but a nice to know should you ever need to do it in the future!