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.
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:
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!)
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
12 thoughts on “How to Implement a Common Data Service Many-to-Many Field on Canvas Power Apps”
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?
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
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.)
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.
The problem was not about the display mode. I could resolve my issue by putting the combobox on a “Custom Card”.
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!
same question here, i cant figure out how to get the field after you create a many-to-many relationship
The lookup exists, but you might have to add it to the solution to see it.
Sorry Phil, not sure how this comment missed my radar . The corresponding column will appear in the Accounts table because the relationship is added. You don’t have to create “both ends”
Hi Phil, You may need to add the Category (or your equivalent) table to the Canvas App via the Data tab then save and restart the Canvas app before the Category entry will appear alongside the other fields under the form properties. Cheers, Jim
Hi Nick, Great alternative to the tutorial provided by Microsoft. However I have one question. How do you show the related field on the details page? Also, do I understand correctly that you do not need to add a lookup field to the accounts table?