Virtual Tables - Setting Primary Field when there are no Text Fields

[Gary_Eden]

Hi,

I've read through the other posts on Virtual Tables (VT) and can't find what I'm looking for, hence this new post.

Historically I've just connected Canvas apps direct to the data source e.g. Azure SQL, Oracle, etc. However, I now have a couple of scenarios where it looks like the use of Virtual Tables is the way to go and have started to experiment with them.

To keep the experiments simple I've set up 3 SharePoint Lists and 3 Azure SQL tables, same basic structure for each data point.

I have them set up and connected to a Canvas app and the standard read/write/delete functions work as expected. However, there are a few oddities that I can't figure out and hope that someone here can help.

SharePoint (SP) Lists

The VT Primary key field maps to the SP ID field which is what you would expect.

The VT Primary field maps to the SP Title field. You can't change the mapping even though this field has been made optional and there are other 'text' fields that could be used. 

Additionally, when adding a row to the list you must include a value for the Title field as this is deemed required by the VT, even though it isn't in the SP List.

Annoying but at least you can work with it - is there any way to select a Primary field other than Title for SP Lists?

Azure SQL (Probably most SQL databases)

The VT Primary key maps to the primary key in the SQL table. This tends to be the first field in the table but I suspect that Dataverse is looking for a field flagged as Primary (and for Azure SQL, Identity Specification = Yes), so it shouldn't matter where the field sits in the column order (I need to check this).

The problem is with the Primary field, It appears that VTs insist on the Primary field being Text (and only text).

If there are multiple text fields to select from you can use any of them but these may not contain unique data. I've always considered that the Primary field should be unique. To be fair when working with SQL & MySQL, in the past, the Primary field was also the Primary key and was a PROPER AUTONUMBER not this string prefixed annoyance in Dataverse.

Problem 1.

If using a text field that doesn't contain unique data what will be the impact across Dataverse and any apps connected to the VT e.g. Canvas, Model Driven or Power Pages?

Problem 2 (a much bigger problem).

For legacy Relational Database it's standard to use foreign keys to provide relationships. A typical example of this is an orders table that contains a column with a key to the product and other columns to hold order quantity, price, key to the customer, etc. All of these are numeric fields. 

When trying to create a VT from a source table that doesn't have a text field Dataverse throws an error stating no Primary Field and refuses to move forward. Even if it were possible to go back and add a text field to the SQL tables (it's not), backfilling data to this new text field to accommodate VTs is not practical.

We have several legacy databases that we wish to join up without moving them and VTs offer a great way to do this. However, if we are crippled by needing to add text fields to legacy tables just to use as Primary fields the project is dead in the water.

Ideally, we would want to use the same field for the Primary key and Primary field, this is the unique field in the tables and in most cases this would be the numeric (auto-number) id/uid/guid field.

I'm guessing others have encountered the same problem.

Is there a workaround to resolve this?

Thanks in advance.