CDS data flows How to import data with many to many relationship

[SandraSoniec]

Dear community

I have built a model-driven app and I am in process to import data to CDS using data flows.

The data source is in Excel.

I have been able to map look-up fields based on this article 

https://dynamicscitizendeveloper.com/2020/04/21/how-to-map-a-lookup-field-in-a-power-platform-datafl...

Is it possible to import data to a field that has a many-to-many relationship to my entity?

Thanks in advance

[HSheild · Accepted solution]

hi @SandraSoniec 

This is not possible at the moment using dataflows as the relationship entity (hidden entity that joins entities in a many to many relationship) does not seem to be available to select on the mapping page.  As an example, I just had a look at mapping the Lead - Product many to many relationship and these two entities are related via LeadProduct entity.  This entity doesn't exist as an option to map.

Additionally this article also says that many to many relationships for dataflows are not supported

https://docs.microsoft.com/en-us/powerapps/developer/common-data-service/cds-odata-dataflows-migrati...

You could add an Idea so that the product team know that people want this https://powerusers.microsoft.com/t5/Ideas/ct-p/PA_Comm_Ideas

[jlindstrom · Accepted solution]

Yes. Use power automate and the relate records step. You need to create a flow from a solution and use the connector called cds current environment. Here is a great post about it https://www.google.com/amp/s/tattooedcrmgirl.com/2019/10/30/microsoft-flow-the-relate-records-action...

you can put your excel in onedrive and connect with flow and create or update records in cds

[AndyHowes]

The lack of this feature really frustrated me with Dataflows, so I created a Python library that lets you import Many to Many relationship data easily to Dataverse.

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.

[jontylp]

Hi guys,

Trying to do a similar thing here - I've got a Books table and an Authors table and trying to link the two together (Many to Many relationship). Easy to create the relationship but is there still no "easy" way to import Book data and Author data (from Excel) and link the two without resorting to Python scripts or creating a Flow? The underlying relationship table that gets created to manage the relationships seems completely hidden (even in the Solution Explorer) and I'm not sure how to actually get data into it short of resorting to the API which seems insane to have to do? 🙂 Thanks for the help!

[AndyHowes]

Sorry it took a while to respond. As far as I’m aware is still no out of the box way to import many to many relations. You can use the python library above, but another option is XRM toolbox, it’s pretty easy to use and there’s a tool in there that lets you import many to many relationships.

https://www.xrmtoolbox.com/plugins/Javista.XrmToolBox.ManageNN/

https://www.xrmtoolbox.com/documentation/for-users/first-steps-with-xrmtoolbox/

[jontylp]

Thanks @AndyHowes  I'm arms deep in Python to get this done 🙂 Appreciate the help and have a good Christmas!

[jontylp]

@AndyHowes , I've cloned your Git repo and got it all firing in Python. Thanks so much for your very helpful post and blog write up on the instructions to set it all up (was a lot easier than I thought) though getting Postman up and running with OATH 2.0 was a bit tricky 😕 

One tip I'll add for anybody else trying to go this route is to get to first get to grips with ODATA (rather than doing as I did and trying to modify Python scripts blindly, praying something would work) - specifically "Using collection-valued navigation properties" (Associate and disassociate table rows using the Web API (Microsoft Dataverse) - Power Apps | Microso...) and how Navigation Properties work in general. Probably an obvious thing to say, but I spent hours trying to figure out what Navigation Property name to use to link the actual records was and that you actually don't need to directly poke around with the "hidden" table that links the many-to-many records together. 

Thanks again! 🙂