Import from excel - how do I import/maintain N:N (many to many) relationships?

(0) Share

Report

Posted on by Madumi

171

Hi,

I'm new to Powerapps & the Common Data Service--hence the noob question... I'm hoping someone can direct me. I am doing a table import (from Excel spreadsheets) into the database, so I began by exporting to *.csv the blank tables out of CDS. I'll use them as templates to fill in with data & do the import... and I *think* I can figure out how to import/maintain tables related by 1:N relationships (lookup fields from other tables).

However, that said, I have two tables (entities), with one field in each joined by a many-to-many relationship. Given that these fields were not exported from my blank CDS database & I can't see a way to populate the intermediary join table inherent in the many-to-many relationship, how can I import the fields related with a many-to-many-relationship?

Any hints would be hugely appreciated. I haven't played around with N:N relationships before, so I may have this all backwards (apologies in advance), thx!

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (3)

Answers (0)

Madumi 171 on at

Like (0)

Report
Copy link

Link copied!

Hmmm, After reading around, I decided to go a different route & create my own custom intermediary join table/entity to hold all the records for the N:N many to many relationship. That way I can maintain control over the data import process (I have near 30,000 records to import, so manually entering those relationships definitely isn't a good option).

I thought I knew how to import entity data that contained lookup fields. As it turns out, I am still feeling my way forward. I found this link, the answer to which (3rd post down) uses the old Dynamics 365 interface, but I'm wondering if there's a better way... When I tried doing it in the Powerapps interface, I set Alternate Keys in the entities referenced by the lookup fields to help map the records, but the import dialogue keeps throwing a "Couldn't resolve the guid for the lookup field: xxxxx_xxxxx" error.

Should I just use Dynamics 365 for importing entities with lookup fields, or is there a way to do it in Powerapps?

Was this reply helpful? Yes No
v-siky-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi @Madumi ,

You need some external tools to help you manage NN relationship. I recommend you to use XrmToolBox plugin for Importing N:N relationships.
XrmToolBox : https://www.xrmtoolbox.com/
Manage NN relationships: https://github.com/MscrmTools/XrmToolBox/wiki/Manage-NN-relationships
Best regards,
Sik

Was this reply helpful? Yes No
AndyHowes 15 on at

Like (1)

Report
Copy link

Link copied!

Hi @Madumi,

I know this is a bit old but for people finding this question who want a different way to import M:M data, I developed a Python library that enables you to use the built in many to many relationship table, without needing to create a custom table. If you like Python this is a super easy and super fast way to import many to many data.

The blog explains how to set up the Azure app to get access to Dataverse, and it's set up to let you access the data using your own credentials so you don't need to set up a user just for the API.

Blog: https://andyhowes.co/easily-connect-to-dataverse-microsoft-dynamics-with-python/
Code: https://github.com/YesWeCandrew/PyConnectDataverse

Use the pcd_add_m2n_data.py file. You just require a table with the GUIDs of the two records you want to link into together in the many to many relationship.

Was this reply helpful? Yes No