Custom many to many relationships

(0) Share

Report

Posted on by kylepatterson

Hello,

I'm fairly new to dataverse so apologies if this is a question which has been asked before.

I'm trying to port our old data model to dataverse. We currently have many mapping tables in our old spreadsheet. From my understanding for many to many relationships dataverse creates it's own mapping tables which use UUIDs as primary keys and are more or less invisible to the user. That would be fine except that we want to create a functionality for users to bulk upload CSVs/excel, including mapping tables. I have already created some flows for automatically ingesting tables with one to many relationships and they work fine, but I find the many to many relationships harder to wrap my head around. Moreover we would like to keep track of our own IDs for readability/maintainability and traceability if possible, for example if we reupload a mapping table.

So my question is, are there any disadvantages to creating our own mapping tables instead of using dataverse's many to many relationships? Because I've been reading around and it looks like they would be equally usable in a canvas app.

Thanks,

Kyle.

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (4)

Answers (0)

MayankP 464 on at

Like (0)

Report
Copy link

Link copied!

Hi @kylepatterson

have you looked at this tool which allows manage N:N Relationship for dynamics..

https://github.com/MscrmTools/XrmToolBox/wiki/Manage-NN-relationships

Was this reply helpful? Yes No
Fubar 8,487 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!
The theory....
Native many-to-many (N:N): created by creating an N:N relationship in DataVerse. Behind the scenes an intersect entity is created that has a column to hold the GUID of the record that is one of the N's and another column to hold the GUID from the other record that is the other 'N". There are additional smarts built into things like Subgrids for the native N:N. This intersect entity cannot be customized (in a supported manner) to add additional columns etc (which is often needed).
Manual many-to-many: for this you create the intersect entity yourself and put 2 lookup's, one for each 'N', as you created it you can add additional columns etc.
The native N:N can have records inserted into it via Code (one example of this being implemented is a tool for XRMToolbox link was included in previous post above). And it can be queried to get the GUIDs via oData or using FetchXML but not via the standard user interface (such as using Advanced Find).

The manual N:N is generally easier to data import into (if doing by hand or not using a tool that is DataVerse aware). But when adding a record via a Subgrid in a Model Driven App rather than getting List to select from from Clicking an Add Existing button, you create a record and populate both Lookup fields using Add New.

Was this reply helpful? Yes No
kylepatterson 8 on at

Like (0)

Report
Copy link

Link copied!

Hello Fubar,
Thanks for your kind summary. I think I'll go with manual N:N tables. I just wanted to check if there was an obvious shortcoming that I was missing.
Theoretically I think I could have managed with the native system for the initial ingestion, but I can already see cases down the line where users will want to do x, y and z and it will be too difficult. Also we might want to add manual columns at a later date in some cases.
Cheers,
Kyle.

Was this reply helpful? Yes No
kylepatterson 8 on at

Like (0)

Report
Copy link

Link copied!

Thank you. I've seen xrmtoolbox but the issue is that we will have a front end interface to allow users up trigger flows to ingest CSVs and the like with updated mappings, so it really needs to be manageable without external tools. I think I'll use manual N:N after all.
Thanks,
Kyle.

Was this reply helpful? Yes No