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.

10 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Jeroen
24 days 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
22 days 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
18 days 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
18 days ago
Reply to  crmvet

Thank you, the new release worked:-)

Mike
Mike
19 days 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
14 days 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