How to Implement a Common Data Service Many-to-Many Field on Canvas Power Apps

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.

Account and Category Entity Relationship

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.

Errors adding a many to many field on a form

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.

Category custom entity

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

Many-to-many relationship

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

Colours stored as categories (can be anything)

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 new categories to an Account

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
Canvas app to manage accounts

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.

Errors when adding a many-to-many

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.

Phone a friend…

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

Update Items

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.

Update Display 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).

Clear out the update property

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.

Save button

And then we add the code.

Code to save record and relate many-to-many

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.

Adding many-to-many values

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.

Results of adding Many to Many records

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!)

Many-to-many visible on canvas form

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

7 thoughts on “How to Implement a Common Data Service Many-to-Many Field on Canvas Power Apps

  1. Hi, Just wondering why you didn’t use the out-of-the-box Category Entity? I can see that entity in the CDS but it doesn’t look like I can use it? Do you know anything about this?
    Thanks,
    Ben

    Like

    1. My post was just an example of displaying many to many fields in a canvas app, it was quick and easy way to explain how workaround the combo box limitations and I could have used any many to many entity, but wanted to show that you could use custom entities as well. The OOB category entity has a lot of extra functionality (hierarchy, etc) and relates more to first party apps (like Sales and Service), which were not really relavent to this post. Not sure how usable it is in custom applications. The actual customer project that this was based on had a very proprietary data model that I wasn’t going to use for a public posting. Hope that answers the question. Cheers, Nick

      Like

  2. Thank you for your helpful posts.I could understand what I should do for many-to-many entities on a Canvas app.
    I could realize this when I submit a “new” form like your post, but could not “update” an existing record with those formulas because the column is “read-only”.
    What should I do to update an existing record? Could you help me?

    (I am Japanese and sorry for my poor English.)

    Like

    1. I believe what you need to do is add a form but make sure it is in “edit” mode and then also the Many to Many field; change the display mode from “View” to “Edit”. Very similar to how we built the create form. I hope that helps.

      Like

      1. The problem was not about the display mode. I could resolve my issue by putting the combobox on a “Custom Card”.
        Thank you.

        Like

  3. Hey Nick, great blog post, thanks. Did you have to create a Field (or now called Column) in the Accounts table to store the Categories or does this just appear because you created the relationship? Thanks!

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s