Power App multi-user/person selection backended by Dataverse

[BrokenWookie74]

I have a relatively extensive "solution" of an app, several flows, lots of connectors, and several SharePoint lists associated with it. I've received numerous new requirements which has gotten me thinking it makes sense to cut over to Dataverse as the backend for the need iteration of the app to be released.

My app has a heavy user emphasis with a bunch of people-picker/multi-select user controls, for things like down stream flow access controls of those user lists. Now, SharePoint seems to handle this quite well; Dataverse, not so much. Single user lookup is not really a big deal, just not multiple. I'm quite a way into this backend conversion, but this is a big enough issue that if I cannot find a solution that I may reverse course and stick with SharePoint. I also have a couple of flows that are updating people picker fields in SharePoint.

So, my question is: How do I replicate multi-user lookups and replicate the multi-select people picker from SP, in DV?

[Amik]

Hi @BrokenWookie74 ,

Unfortunately, you cannot replicate the multi-user lookup experience in SharePoint because the Lookup column in Dataverse cannot allow multi user selection.

The way to do it as “designed” is to create a Many-to-Many relationship between your Table and the User table. This type of relationship leverages a special hidden table called an Intersect table (also known as a Bridge table or Joiner table) to enable many rows of one table to be related to many rows of another table.

Example:

1. Select your Table and select Relationships > New Relationship > Many-to-Many

2. In this scenario, we are going to relate the User table to our Table. You can optionally change the intersection table name.

3. In your Canvas app, create a blank screen and add a Gallery (we will call it Gallery1)

4. [Optional] On the TemplateFill property for Gallery1, enter 

If(ThisItem.IsSelected, Color.LightGray, Color.Transparent)

5. Use the Title layout for Gallery1 to display one column for this example:

5. Connect Gallery1 to your Table.

6. For the single Label control inside Gallery1, set the Text property to ThisItem.'Your Record ID'

7. Add a second Gallery (Gallery2) and again, use the Title layout.

8. In the Items property for Gallery2, enter 

Gallery1.Selected.Users

9. Note that because we created a relationship between these two tables, the Users table is a valid selection. If the tables were not related, we would not be able to select the Users table

10. For the Label control in Gallery2, enter ThisItem.'Full Name'

11. Add a ComboBox and name it ComboBox (we will call it ComboBox1)

12. Set the Items property for ComboBox1 to the Users table

13. To relate users to the selected record in Gallery1, add a Button control somewhere in the screen, ideally above the Users table for this example

14. On the OnSelect property for the Button, enter:

ForAll(ComboBox1.SelectedItems,Relate(Gallery1.Selected.Users,ThisRecord))

15. To unrelate users from the selected item in Gallery1, add another Button or Icon (such as the Trash icon) inside Gallery2

16. On the OnSelect property of the Button/Icon, enter: 

Unrelate(Gallery1.Selected.Users,ThisItem)

Microsoft walkthrough: https://powerapps.microsoft.com/en-us/blog/option-sets-and-many-to-many-relationships-for-canvas-app...

Additional note: you cannot export the selected display names as a concatenated field into Excel like with SharePoint. Instead, you will need to leverage other tools such as Power BI, Power Automate, JavaScript Custom Plugins etc for relationship querying on the intersect table. 

------------------------------------------------------------------------------------------------------------------------------

If I have answered your question, please mark your post as Solved. Remember, you can accept more than one post as a solution

If you like my response, please give it a Thumbs Up.

[BrokenWookie74]

@Amik- thank you, detailed response is much appreciated. Though, I'm not sure the way in which this was designed is what I'm looking for. I was able to successfully create a many-to-many relationship between my target table and the Users table. However, that intersect table doesn't seem to be an available option as a lookup when creating a column within the target table (for the purpose of adding multiple users within an Edit Form control), and if I set the User table as the related table, it is automatically set as a many-to-one relationship, which is also how lookup columns treat the aaduser virtual table.

[Amik · Accepted solution]

@BrokenWookie74  - unfortunately, it is not possible to leverage the Intersect/Relationship table. It remains a hidden entity from the list of available tables and is never made available in the user interface.

------------------------------------------------------------------------------------------------------------------------------

If I have answered your question, please mark your post as Solved. Remember, you can accept more than one post as a solution

If you like my response, please give it a Thumbs Up.

[bugescape]

Trust me, stick with SharePoint, I have similar experience, Dataverse is so amature, it's not a good database to use when you already have a working intensive database running.

[Amik]

@bugescape  - there are pros and cons.

SharePoint is perfectly workable for solutions with small data volumes, and it is a significantly superior document management solution. It also has integrations with other parts of the Power Platform which Dataverse does not. Still not being able to Send a HTTP request to Dataverse via Power Automate for example is infuriating.

However, I would not agree that Dataverse (CDS) is immature. It is a platform with a relational database, vastly superior to SharePoint in terms of the management and security of data.

Key issue I find is the cost implications are too high. Moving from SharePoint to Dataverse is effectively jumping from paying nothing per user (SP) to £16 per month per user.

[bugescape]

Dataverse is a shell based on MsSQL services, the relational and security structure is inherited from SQL, I'd still say Dataverse is a completely amateur product, as a shell of another product

[Amik]

cool

[BrokenWookie74 · Accepted solution]

I actually found a workaround that serves the purpose enough for what I need! It utilizes the Office365Users connector search user function and displaying the results from a text box into a gallery, then adding the selected user into a collection, and finally pulling the UPN into a semicolon-delimited concatenated string value. Dataverse accepts that and Power Automate can handle the rest.

[BrokenWookie74]

I was originally using SP, but made sense to make the jump with other reqs, even with its user limitation. Though, I found a way around it and was able to achieve what I wanted (in thread).