cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rileycooper
Frequent Visitor

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

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.

1 ACCEPTED SOLUTION

Accepted Solutions

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...

View solution in original post

4 REPLIES 4
KoenJanssensPD
Post Patron
Post Patron

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.

rileycooper
Frequent Visitor

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.

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...

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!

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Users online (1,975)