ReadyXRM

This community is absolutely amazing. Huge thanks to MVP Hardit Bhatia, aka The Power Addict. It would have taken me a lot longer to come up with this solution on my own, but Hardit dove in and quickly figured out the solution and also patiently walked me through it. Hopefully this solution helps you out as well!

Many-to-Many Fields and Canvas Power Apps

I was working with a client on a canvas-based Power App and we needed a way to quickly tag one or more categories to a specific Account record. We are using the Common Data Service as our data store (of course!)

I work pretty much exclusively with Power Apps connected to the Common Data Service. The Common Data Service provides a robust solution for not only storing data but also security, flexibility, automation and extensibility.

While I could have created a multi-item optionset, the list of categories were going to be constantly updated so it was a better approach to use a many-to-many relationship and a custom entity to hold our category options.

The issue is that if you just add a many-to-many field to a canvas Power App form, you will get a litany of error messages as forms and DataCards aren’t fully able to implement the features of the many-to-many attribute in the Common Data Service.

The following is a series of steps and a solution to allow your users to add multiple related records to a record using a form on a canvas Power App.

The Solution

Lets take a step back and walk through the whole solution. Let’s assume that our requirement is the flag the Common Data Model Account entity with one or many different category tags. We also want to be able to edit and add to these tags on a regular basis.

I created a custom entity called “Category” that had a name field.

I created a N:N relationship between Accounts and the custom category entity, so we can “tag” Accounts with many different categories.

I began to add a series of categories (colours) but these could be anything (preferences, industries, etc).

In a model-driven app, we can easily add or remove “categories” from records using the model-driven user interface. This doesn’t require any extra special coding.

Adding Categories on a Canvas App

I created a simple canvas Power App that will perform the following;

• View existing Accounts
• Add a new Account
• View the details of an existing Account

Implementing a many-to-many relationship on a canvas becomes more difficult. I could create a couple of galleries and assign the various options as outlined in this Microsoft posting. However, I would like to keep the interface relatively clean and easy to use.

As I mentioned earlier, simply adding the many-to-many lookup field will result in errors.

I tried to work through the issues and ran into problems. Thankfully a call to the MVP hero brigade resulting in the following solution from Hardit.

First thing is to set the Items to the category entity on the Categories DataCardValue. (Note: You will need to unlock the control).

Then change the Display mode of the Category DataCard from “View” to “Edit”. Since the Form cannot natively handle updating many-to-many fields, by default it will be set to view mode.

Then clear out the Update property on the Category DataCard, as it doesn’t have the ability to properly update a many-to-many relationship between two different Common Data Service records. (We will get to that soon).

Normally I would add a formula to a button to submit the form which would create a new record in the Common Data Service. However, I need to do some extra steps to relate the many-to-many selections;

I still add a button to save my data, but will need to add some extra code beyond the simple form submit.

And then we add the code.

Here is the full formula for the save button:

ClearCollect(
   collectcategories,
   DataCardValue4.SelectedItems
);
SubmitForm(Form1);
Set(
   newAccount,
   Form1.LastSubmit
);
ForAll(
   collectcategories,
   Relate(
      newAccount.Categories,
      ThisRecord
   )
);
NewForm(Form1);

Here is a breakdown of what each line of code is doing:

The ClearCollect gathers the selected items (the many-to-many references) and adds them to a local collection.

The SubmitForm will save the new Account record to the Common Data Service. Since we cleared the Update property, you will not get an error from the Categories field.

The Set command simply assigns the newly created Account record reference to a variable that we will use with the Relate function (next step)

We will then loop through each record in the collection (the many-to-many references) and then call the Relate() function to link them to the newly created Account record.

Finally we will reset the form to add more Accounts.

Testing the App

When I run the app, I see the various selections and can assign them to the account, just using the combobox control on the form.

I then click the save button (the checkbox) and the record saves to the Common Data Service.

If we look at the record in a model-driven app, we see that the categories (colours) were related to the record.

If you add a read-only form and view the results in the canvas app as well. You should be able to work with this solution to add the ability to “unrelate” the corresponding records as well. (Consider that your homework!)

Summary

Many-to-many relationships to reference entities can be extremely helpful and actually I like the implementation on the canvas app better than the model-driven app. However for model-driven, there are PCF controls that replicate this functionality (see the PCF.Gallery for examples).

Again, thanks to Hardit for his help. Definitely check out his blog and follow him on twitter for some great Power Platform tips and techniques!

Cover Photo by John Barkiple on Unsplash

Phone Photo by Fredrik Solli Wandem on Unsplash (modified to add MVP logo)

Nick Doelman is a Microsoft Business Applications MVP and is presenting at DynamicsCon on September 9th as well as UG Summit in October. Follow Nick on twitter at @readyxrm