Power Apps PCF Association Table Control for Joint (Many-to-Many) tables

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.

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.

5 3 votes
Article Rating
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

18 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Jeroen
3 months ago

I did install solution but i do no see the control back on a test field
Can you help me with this please

Fabrizio
Fabrizio
3 months ago

Good job.
I would inform that you leave hard coded the string “crmvet_name” instead replace with associationTable.

kind regards

Lu Zhang
Lu Zhang
3 months ago
Reply to  crmvet

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?

Lu Zhang
Lu Zhang
3 months ago
Reply to  crmvet

Thank you, the new release worked:-)

Mike
Mike
3 months ago

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)

Samuele
Samuele
3 months ago

Good Evening Mr. ,
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

Example.png
jeroen van der pol
jeroen van der pol
2 months ago

Can you help mw with error below

Knipsel.PNG
Jason Mayo
Jason Mayo
21 days ago

I am having the same error, please assist.

Jason Mayo
Jason Mayo
16 days ago
Reply to  crmvet

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

Last edited 16 days ago by Jason Mayo
Bas
Bas
21 days ago

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

Screenshot 2021-09-03 174925.JPG
Bas
Bas
18 days ago
Reply to  crmvet

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!