I’m not particularly eager to rely on Dataverse’s OOTB Many-to-Many (N:N) relationship due to its limitations and, in most cases, do opt for custom association (joint) tables.
Yes, it is a bit of extra work; however, with a custom table, it is possible to get full control over that many to many relationships and have more control and functionality with no significant downsides on the performance of database size.
Pros
The common reasoning to opt out from OOTB N:N relationship to a custom joint table:
- Ability to work with records in bulks (OOTB feature to export/import MS Excel)
- Ability to present available choices/records in UI (aka toggle/checkbox UX)
- Have complete control over taxonomies, configuration and templates, and simple maintenance by business/end users
- Allow multiple records with additional info to preserve historical information and have extensive audit logs
- e.g., when we had and removed an attribute or had to for specific periods of time
- Extensibility and futureproofing. Available in the Power Apps Maker Portal, thus can be extended and customized with
- additional fields
- business logic (anything, business rule, automation, custom code)
- security
Data Model Difference
Here is how it differs from the data model perspective at Dataverse level.
Note: At MS SQL level, it might be the same; tables-wise, there is a table at DB level. It is just not accessible at Dataverse for us, but since we do not work with it directly, who cares.
Cons
One of the caveats to this approach is a change in UX. Instead of relating records, users now have to create records in that association table manually, or automation is needed for each of such tables.
I’ve used to resolve that caveat on a need basis with such automation (real-time D365 workflows) or for advanced scenarios with UI programming (on form JS code), and that has to be applied on D365 (Model-Driven Apps) forms.
Solution
With PCFs introduction, we got an easy option to have something reusable I’ve got a chance to introduce a PCF for this, which just needs to get added to the form you need it on.
The control technically is very similar to the existing solutions for N:N visualization, or tags. Here are some similar PCFs available at the moment.
- https://pcf.gallery/n-to-n-multiselect
- https://pcf.gallery/checkbox-list
- https://pcf.gallery/nn-checkboxes
- https://pcf.gallery/tag-picker-grid-component
- https://pcf.gallery/nndropdown
- https://pcf.gallery/nntree-control
However, there were no PCF to support visualization and provide simple UX for custom association tables vs Dataverse OOTB N:N relationship.
How it looks in the User Interface
Records in the associated table are just being created and deleted by this control when user in UI selects values. Visual representation and filtering can be tuned per specific scenario.
How to get it
The PCF is free to use or contribute by anyone available as a managed solution and a source code at GitHub.
Do not forget to check other amazing PCF controls at pcf.gallery
Feedback and Enhancements
Please feel free to contact me via LinkedIn to provide feedback or get support or suggest ideas for enhancements.
I do have other versions I’m playing with for specific scenarios to support filtering and scoring models.
I did install solution but i do no see the control back on a test field
Can you help me with this please
Sure. Please ping me in LinkedIn if you want to jump on a call. Just to clarify, solution does not have any test fields, only the control. So, you have to work with your own test fields of string or memo types. Are you saying that there is no Association Table Control available to add for a string/memo field in controls or something else?
Good job.
I would inform that you leave hard coded the string “crmvet_name” instead replace with associationTable.
kind regards
Thanks, Fabrizio!
I’ve released a fixed version to GitHub.
It looks like the version 1.0.0.1 still has the hardcoded name in the managed solution. It fails when doing the post request.
Would you mind to help build a new managed solution file?
Lmao. Yes it had missing brackets. Please check new release.
Thank you, the new release worked:-)
Great control! FYI – could only get the control to appear if the field was a memo or multiline text (single line text doesn’t work)
Good Evening Mr. @crmvet,
I’d like to know if filter the results by a lookup column is possibile.
I upload here an example. I’d like to filter “Seleziona Argomenti”‘s results using “Categoria”‘s value (in our case “Economia”).
Thanks in advance,
Samuele
Hi Samuele, it is not included in this PCF. The intention is to keep this one as simple as possible. But for specific needs, it can be easily extended to filter per classification/category of such table and visualization how many of options has been selected to show scoring as an example. I might be releasing such functionality as separate PCF in future. You can ping me at Linkedin if you want to discuss details on this.
Im looking for something like this or the n-to-n-multiselect control.
Will this be able to filter based on views?
Can you help mw with error below
I am having the same error, please assist.
Hi Jason, Could you please give any details? Steps to repro and screen for the configuration/mapping to tables, etc.
created an N:N between two custom tables with lookups to each, default casing on column schema names; everything displays correctly, but clicking on an item throws “checkboxChanged” error created associated record
Hi Jeroen, Could you please give any details? Steps to repro and screen for the configuration/mapping to tables, etc.
Hi Alex,
Did you get bottom to this? I’m experiencing the same issue.. I’ve tried pretty much everything, but still no luck to get this one working. Would you be okay if I message you on LinkedIn?
Many thanks!
Vaclav
Could you please check the newer version 1.0.0.3, it has a fix for the label as well. And sure contact me on LinkedIn will figure out what is happening.
Today I’ve seen same error in my environment and solved it creating a new column in the intermediate entity (aka custom association table), with the same logical name as the field specified in Selector Label. Hope it helps.
@crmvet very useful add-in!
Could you please check the newer version 1.0.0.3, it has a fix for the label as well. And sure contact me on LinkedIn will figure out what is happening.
Hi, I’m getting an error when selecting a tag in the control on the form. Do you have any idea how to solve it? The entities work and if I manually add a record it will show up in the interface. But tagging itself isn’t working….
Hi Bas, I would check configuration of the PCF. Could you send a screen of the config or maybe would it be possible give access to your sandbox env? Hard to RCA it w/o hands on.
Hi, I was able to resolve it myself. I need to match the upper & lowercase in the entity name. After I have done that, the PCF control works as expected! Thanks for the great control!
Hi, I am still getting that same error, I have checked all the field schema names and there is no issues. Can you please help how to proceed?
Awesome control! meets my requirements perfectly. To others trying to use the control, please make sure that all the input parameters are completely distinct and are entered correctly and the tool will work. One more thing for using Default filter I used the CRM rest builder to build the query and applied on the control. Works like charm!