Patch SharePoint multi-select person column: append & remove individual users

[LaurensM]

When creating Power Apps based on SharePoint you may have had the requirement to save multiple people to a SharePoint Person column. While the Form control and underlying combobox offer a straightforward solution, this may not always align with your specific needs.

Imagine a scenario in which your users should be able to individually add & remove themselves from a record without affecting the existing people selection. In this case, the standard Combobox / Form approach may fall short.

In this blog post we will briefly refresh our knowledge on the default Form approach but we will more importantly explore ways to add & remove a user from a multi-select SharePoint Person column without leveraging a combobox.

SETTING THE SCENE

Let’s imagine a scenario in which we are building a simple Service Desk Tool where multiple agents can work on the same ticket. The screen should allow the SD agents to scroll through open tickets, see additional information & assign a ticket to themselves.

We can use a Gallery control to display the tickets and a Form control to display additional ticket information. In this case, the Form is displaying the currently assigned agents via a Combobox:

A service desk agent can search via the combobox searchbar & add themselves as one of the assigned agents. In order to save the data, we could leverage the SubmitForm() function which handles most of the logic for us.

//Example of the code within a ‘Submit’ button
SubmitForm(frmTicket)

The user is now able to add themselves to & remove themselves from a ticket, yet this approach requires each agent to manually search and add themselves. Additionally, nothing is stopping the user from accidentally removing other assigned agents or adding the wrong person to the ticket.

APPEND THE CURRENT USER

As we’ve established above, the previous approach ‘works’ but surely is not the most secure & user-friendly method. As an adjustment we will set the DisplayMode of our person field to view and add an ‘Assign to me’ button instead.

Simply patching the current user to our Person field would overwrite our existing links to Agent 1 and Agent 2. In other words, our Patch statement will need to append the current user to our existing selection:

Patch(
    Datasource,
    //Within our example Record_Reference would be Gallery.Selected
    Record_Reference,
    {
        PersonColumn: Ungroup(
            Table(
                {
                    Items: [
                        {
                            DisplayName: "",
                            Claims: "i:0#.f|membership|" & User().Email,
                            Department: "",
                            Email: User().Email,
                            JobTitle: "",
                            Picture: ""
                        }
                    ]
                },
                {
                    //Within our example this would be Gallery.Selected.’Assigned Service Desk Agent(s)’
                    Items: Record_Reference.PersonColumn
                }
            ),
            "Items"
        )
    }
)

Append code deepdive

In order to append the current user to our existing selection, we have to create a nested table structure.

Table(
    {
        Items: [
            {
                DisplayName: "",
                Claims: "i:0#.f|membership|" & User().Email,
                Department: "",
                Email: User().Email,
                JobTitle: "",
                Picture: ""
            }
        ]
    },
    {
        //Within our example this would be Gallery.Selected.’Assigned Service Desk Agent(s)’
        Items: Record_Reference.PersonColumn
    }
)

Our new table with a column named ‘Items’ now contains our nested Person arrays:

Items

(New user) [     {LaurensM Person Object} ]

(Existing selection) [     {Agent 1 Person Object},     {Agent 2 Person Object} ]

The Ungroup function allows us to merge both nested tables together by ungrouping the items column and thus appending our current user to the existing selection.

Ungroup(
    TableName,
    ColumnToUngroup
)

Note: Interested in learning more about patching SharePoint complex columns? Be sure to give this blog post by Craig White (@cdwhite) a read.

REMOVE THE CURRENT USER

Once a ticket is assigned the ‘Withdraw’ button will be displayed, allowing the agent to remove themselves from the ticket assignment.

In this scenario we want to filter out the current user from the existing selection. This can be done by supplying our person column with a Filter function that returns all people currently saved to this person column except the current user.

//Replace Datasource, Record_Reference & PersonColumn accordingly
Patch(
    Datasource,
    Record_Reference,
    {
        PersonColumn: Filter(
            Record_Reference.PersonColumn,
            !(User().Email in Email)
        )
    }
)

FINAL RESULT

Note: Appending & removing individual users is not limited to the current app user. In order to add or remove a different user, replace all mentions of User().Email in the examples above with the corresponding email value.

If you liked this blog post, feel free to give it a like. 👍 | For more tips and tricks check out my blog (LaurensM). 📘