Answered

Import Two Excel Tables into Dataverse Retaining One-to-Many Relationship

(0) Share

Report

Posted on by rileycooper

I am using the Contact table and a custom table for Reports in a model-driven app.

I have created the lookup one-to-many relationship (contact to reports). So for newly created contacts and reports things are working.

I have existing data on two Excel tables that I need to import and retain the relationship between the two tables.

On the Contact Excel table, for each contact record there is a whole number ContactID.

On the Reports Excel table, for each report record there is a whole number ReportID. I have a ContactID column on the Report Excel table that associates the contact with the report record.

I know I am missing something fundamental here, but I just can't see how to import and retain this relationship on these thousands of existing records. Can someone please help? Thank you.

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (4)

Answers (1)

KoJaPD 597 on at

Like (1)

Report

You could create a unique key (on a custom or existing ID column) in the Contacts table and use a Dataflow to upsert the data from your Excel files. When you import the data in the table that has a lookup to the other table, you use the ContactID column as a reference to the related Contact in the Reports table.
This video might be interesting for you.

Was this reply helpful? Yes No
rileycooper 25 on at

Like (0)

Report

Thank you for your time and your help. I am still having difficulty. I uploaded images of views of Contacts and Content after import. On the Content image, the only contacts associated with reports were the two I entered into the app and did not import. I should be seeing contacts C, D, and E Full Name and Original Contact ID in the Content view -- correct? The Content view allowed me to choose columns from the related Contact table so the relationship seems to be understood. I imported from within my app Import from Excel at the top. Any further insight is truly appreciated.

Content View.JPG

Contact View.JPG

Was this reply helpful? Yes No
Verified answer

KoJaPD 597 on at

Like (1)

Report

Can you share screenshots from the columns in each table?
For Contacts, the "Originals Contact ID" should be added as an Alternate Key;
In Contents, a lookup field to "Contact" should be added
In Contents, the "Original Content ID" should be added as an Alternate Key;

When importing data into Contents, you should be able to choose "Contact.OriginalContactID" as the column to link with the Contact...

Was this reply helpful? Yes No
rileycooper 25 on at

Like (0)

Report

OMG!! Thank you so much! I was missing this part: When importing data into Contents, you should be able to choose "Contact.OriginalContactID" as the column to link with the Contact...

I knew I was missing something fundamental and just couldn't see it. For anyone else who is reading this and is going crazy with this type of issue, the translation is: in the Contents import field mapping, I did not look in the dropdown next to the Original Contact ID to map it to Contact (Lookup). I knew this had to be done, but I just could not see where to do it even though it was right there.

To KoenJanssensPD: Thank you for your time. You have been a tremendous help!

Was this reply helpful? Yes No