Power Apps: Patch data from the Office 365 Users connector to a SharePoint multi-user field without using collect

Sometimes when building a Power Apps Canvas app we want to use the Patch function instead of Forms to update items in SharePoint for several reasons, such as to have fewer controls in the screen, or to have more control over how the data is updated.

For some complex fields, it can be trickier, as there will be more data manipulation needed and the format varies per field type. Especially when you are updating data using a data source that is not using the Choices function for that field, for example when you use the Office 365 users connector to update a Person field, or when you use data from the source list to update a Lookup field.

Updating multi value fields

To update fields with multiple values we would need to use a table to be the field value instead of a record. For example, if you want to update a user multi field (Person field with Multiple Values enabled), you could use the following syntax (note that only the Claims field is required, this is what SharePoint will use to determine what user to save):

Patch(
    Issues,
   locSelectedRecord,
    {
        'Assigned to': Table(
            {
                Claims: "i:0#.f|membership|user1@tenant.onmicrosoft.com",
                Department: "",
                DisplayName: "",
                Email: "",
                JobTitle: "",
                Picture: ""
            },
            {
                Claims: "i:0#.f|membership|user2@tenant.onmicrosoft.com",
                Department: "",
                DisplayName: "",
                Email: "",
                JobTitle: "",
                Picture: ""
            }
        )
    }
);

Given that we need to use a table to update the field, if we want to generate a table let’s say from the selection of a multi-select Combo Box in which the data source is a search using the Office 365 users connector, some data manipulation will be needed.

Transforming a table/collection using ForAll

Instead of collecting the data and then patching it, it’s possible to use the ForAll function to transform the selected items to the correct format.

This function normally is used to iterate through a table or collection of records and perform an action in each record, but it works not only on this way. It also can be used to transform/calculate values and return a table/collection transforming a source table/collection into a different record structure. When using it and only passing an custom object as parameter, it will get a list of objects and return a different collection in the format you specify.

For example for the following table:

We can use the below formula to return another table with only one column named FullName concatenating the Name and Surname properties:

ForAll(
    sampleTable,
    {FullName: Name & " " & Surname}
)

The output of the formula above using ForAll will be the following table (which can then be used as source for a control, or used in another formula):

Data source formula

Considering the sample formula for the items property in a Combo Box using the Office 365 Users connector to search users:

Office365Users.SearchUser({searchTerm:Self.SearchText,top:10})

This formula will return a table of objects with the results in a forma format which is not what SharePoint expects for the field, as in the sample below:

And by consequence, the values for SelectedItems on this Combo Box will follow this field structure, that’s why we need to transform the data.

Some approaches in the community use collections to generate the correct table and then patch the item.

It works fine but it will create an extra collection in your app’s collection list. Alternatively, we can use the ForAll function to avoid creating an extra collection in the app’s collections list.

Patching without using collect to generate the table

For the case of using the selection in a Combo Box based in the Office 365 Users connector as a data source, you can use the sample below patch statement which uses the ForAll function to transform the selected items table into the format that the SharePoint field requires:

Patch(
    Issues,
   locSelectedRecord,
    {//Replace 'Assigned to' by your field name
        'Assigned to': ForAll(
            cbbOffice365Users.SelectedItems,
            {
                Claims: $"i:0#.f|membership|{UserPrincipalName}",
                Department: "",
                DisplayName: "",
                Email: "",
                JobTitle: "",
                Picture: ""
            }
        )
    }
);

The important points of the formula are that we even though we have to return a collection of objects containing the fields Claims, Department, DisplayName, Email, JobTitle and Picture, SharePoint will use only the Claims value to match the users. So we can transform the SelectedItems into a table of objects containing those fields and only having a value in the Claims field.

Conclusion

Even though there is some data manipulation needed to update a People with Multiple Values field, it’s possible to update the field by transforming the data without using the Collect function and using the ForAll function. And by using the ForAll function you can reduce the number of collections in your apps in some use cases where the values should be used temporarily as in this one.