How to Import existing SQL data into the Dataverse and create relationships on existing fields

[AndrewMortensen]

Hello,

I am new to the Dataverse and model driven apps and am looking at building a simple portal application that needs to reference existing data from an external system hosted on a SQL server (on premise). This data would be regularly refreshed into the Dataverse.

The SQL server reference tables are:

1. OrderHeader with Primary Key OrderNumber (integer)

2. OrderLineDetail that relates to OrderHeader via field OrderNumber

I have tried two methods to get the data available in the Dataverse:

1. Using the Virtual Connector (preview). This works and I can create the virtual tables, access data and even create relationships between the two tables on field OrderNumber. The problem is that related fields in Views and Table Permissions that use this relationship do not work correctly. I have tested the relationships with dummy (non virtual) tables and the Views and Table Permissions work fine so I assume my issues are due to constraints with the Virtual Connector.

2. Using Dataflows to import the tables. The problem is that the created Dataverse tables have an auto generated Primary Key and I cannot work out a way of creating the relationship between the two tables on the existing OrderNumber field. I believe the cause of my problems is that the relationship is using the GUID primary key which causes an error as OrderNumber is an Integer field.

Does anyone have any guidance on how I can access / import existing SQL data into the Dataverse and get relationships created on the existing OrderNumber field?

Thanks

Andrew

[dpoggemann]

Hi @AndrewMortensen ,

Dataflows would be a good approach here.  You should be able to relate the tables in the following way:

1.  Utilize the Primary Name column on the Order table to be the OrderNumber.  This will be a text field in Dataverse that would contain your Order Numbers.  It is not allows to be a Integer field.  You can add a numeric integer field as well and fill with the Order Number so you can sort correctly in the application based on the integer value vs a text field...

2.  On the Order Line table you would define a Lookup field to the Order table and when you import through the dataflow you would populate with the Order Number from the Order table (the text value one that is the Primary Name column on the table).  This will resolve on the import and will create the relationship to the parent record.  Note, you can not have two Order Numbers the same on the Order table or this will fail to resolve of course but I assume you don't have that case.

Hopefully this helps.  Please accept if answers your question or Like if helps in any way.

Thanks,

Drew

[AndrewMortensen]

Hi @dpoggemann ,

Thanks for the advice. That worked. I thought it stored the Primary Key value in the child table lookup field as this has been my assumption through trial and error working with the Virtual Connector tables i.e. the relationships between them only worked if I made Order Number (integer) the primary key as the lookup field in the Detail table was an integer.

Ideally I would like to have Order Header as a dataflow Dataverse table and the Order Detail as a virtual table as I would like to update data fields in the detail table (and have this flow back to SQL server). I tried the same method you suggested for this combination (virtual table lookup to real table) and it doesn't work. Do you have any suggestions how to get this to work? 

Thanks

Andrew

[dpoggemann]

Hi @AndrewMortensen ,

Sorry, no experience with virtual tables.  Hopefully someone else can jump in here...

Thanks,

Drew

[AlAlonso]

Hi @dpoggemann

When I try to import data from excel, it doesn't ask to choose a column from the source to the lookup field.