Following the article on patching multiple rows, I’ve received a few requests asking how to patch the Person or Group column in a SharePoint list. This question is also frequently asked in the Power Apps forums. Patching complex SharePoint columns with Power Apps is something I spent many years doing, as my Power Platform experience started with using Lists as a data source.
It seems that those who are new to Power Apps are facing similar challenges as I did when I first started. So I thought I’d put this article together and give a quick guide to patching commonly used complex columns in a SharePoint list: Person or group, single choice, multiple choice, lookup and yes/no.
To support the Power Fx code samples in this article, I’ve created a SharePoint list called ComplexColumnsList with the following columns:
I’ve created a Power App and added the above list as a data source.Â
Form control
By far the quickest and easiest way to get the data correct for these column types is to use a Form control. A form control is bound to a data source and builds the right controls for the right column types. The configuration of each column is done for you, which makes it SUPER easy for beginners to work with complex column types.
From the Insert menu, search for form and select Edit form:
Rename the form control. In this example, I’ve renamed mine to frmData. In the properties pane, select the SharePoint list:
To create new records with the form, change the Default mode property to New:
Add a button, then add the following Power Fx to the OnSelect property:
SubmitForm(frmData)
There we go, easy right? You can do this in seconds and have a solid data entry experience to capture new records. Unfortunately, it might not always be so easy to use a Form control. They have their limitations, so what other options do we have?
We can add and configure other controls to be compatible with our patch statements to SharePoint. We can also send through other dynamic or hardcoded data, as long as it’s in the right format that the column will accept. Let’s explore both of these methods for each complex column type.
For the purposes of all examples, I’ll be using the patch function to create a new row of data to showcase the syntax needed for each column type.
Single choice
I’ve added a drop down control to my app and renamed it to dpdChoiceSingle. For the column in SharePoint to recognise the value, I need to bind the Items property to my single choice column in my SharePoint list – ChoiceSingle. I therefore need the following Power Fx for the items property of the drop down:
Choices(ComplexColumnsList.ChoiceSingle)
To patch a value from the drop down control to a SharePoint single choice column, our Power Fx needs to reference the control by name. We then need to add ‘.Selected’ to ensure the record as a whole is included, as this is what SharePoint needs.
Patch(
ComplexColumnsList,
Defaults(ComplexColumnsList),
{
ChoiceSingle: dpdChoiceSingle.Selected
}
);
If we want to pass through hardcoded or dynamic values, we need to build the record object that will be accepted by the column. Sith is one of the choices available in the ChoiceSingle column:
Patch(
ComplexColumnsList,
Defaults(ComplexColumnsList),
{
ChoiceSingle: {Value: "Sith"}
}
)
Though, I’d use this approach with caution as it can bypass the settings of the column. For example, ‘Random text’ isn’t an option I can select for the column in a drop down but I can still submit it as a value. The column will still accept it too:
Multiple choice
As drop down controls in Power Apps do not accept multiple selections, I’ve added a combo box to my app instead. I’ve renamed the control to cmbChoiceMulti. The items property will need to reference the relevant column in the SharePoint list:
Choices(ComplexColumnsList.ChoiceMulti)
The code is very similar to single choice, except we are referring to SelectedItems – plural, as it’s multiple choice:
Patch(
ComplexColumnsList,
Defaults(ComplexColumnsList),
{
ChoiceMulti: cmbChoiceMulti.SelectedItems
}
);
Without using a control to reference, again we can send hardcoded values or other dynamic text. To send multiple values, we can embed them within a Table function like so:
Patch(
ComplexColumnsList,
Defaults(ComplexColumnsList),
{
ChoiceMulti:
Table(
{Value: "Jedi"},
{Value: "Resistance"}
)
}
)
Alternatively, we can simply state the values in an array, like so:
Patch(
ComplexColumnsList,
Defaults(ComplexColumnsList),
{
Title: "ChoiceMulti_Control",
ChoiceMulti:
[
"First order",
"Resistance"
]
}
)
As with single choice, the values may not be the ones configured in the column settings.
Single person or group
I’ve added another combo box to my app and renamed it to cmpPersonSingle. Once again, the items property would need to reflect the list column directly:
Choices(ComplexColumnsList.PersonSingle)
We can reference the control to get the record for the selected person:
Patch(
ComplexColumnsList,
Defaults(ComplexColumnsList),
{
PersonSingle: cmbPersonSingle.Selected
}
);
If you are passing through dynamic values or want to hardcode instead, there’s a few methods that get banded about. The first one I’ll share is the one I always used to use, but it’s rather bloated:
Patch(
ComplexColumnsList,
Defaults(ComplexColumnsList),
{
PersonSingle: {
'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
DisplayName: "Luke Skywalker",
Claims: "i:0#.f|membership|Luke.Skywalker@Jedi.com",
Department: "",
Email: "Luke.Skywalker@Jedi.com",
JobTitle: "",
Picture: ""
}
}
)
A better way is to just add the relevant email into the Claims part of the object. You don’t need the annoying ‘i:0#.f|membership|’ prefix or the ‘@odata’ line either:
Patch(
ComplexColumnsList,
Defaults(ComplexColumnsList),
{
PersonSingle: {
DisplayName: "",
Claims: "Luke.Skywalker@Jedi.com",
Department: "",
Email: "",
JobTitle: "",
Picture: ""
}
}
)
So long as you maintain the same structure (which the SharePoint column needs), you can inject with dynamic values. In the example below, I’m using the Office 365 Users connector to get the logged-on users email address:
Patch(
ComplexColumnsList,
Defaults(ComplexColumnsList),
{
PersonSingle: {
DisplayName: "",
Claims: Office365Users.MyProfileV2().mail,
Department: "",
Email: "",
JobTitle: "",
Picture: ""
}
}
)
Multi person or group
I’ve added a combo box to my app and renamed it to cmbPersonMulti. The items property would need to reflect the list column directly:
Choices(ComplexColumnsList.PersonMulti)
We can reference the control to get the record for the selected person:
Patch(
ComplexColumnsList,
Defaults(ComplexColumnsList),
{
PersonMulti: cmbPersonMulti.SelectedItems
}
);
For using your own, or dynamic values, we can use the same technique as multiple choice and embed values in a Table function. Our streamlined structure and simply adding a value into the Claims field still applies:
Patch(
ComplexColumnsList,
Defaults(ComplexColumnsList),
{
PersonMulti:
Table(
{
DisplayName: "",
Claims: "Luke.Skywalker@Jedi.com",
Department: "",
Email: "",
JobTitle: "",
Picture: ""
},
{
DisplayName: "",
Claims: "Leia.Organa@RebelAlliance.Space",
Department: "",
Email: "",
JobTitle: "",
Picture: ""
}
)
}
)
Lookup
I’ve added a combo box to my app and renamed it to cmbLookup. The items property is bound to the relevant column in my SharePoint list:
Choices(ComplexColumnsList.Lookup)
The lookup column in the SharePoint list is connected to another list in the same site, called Approvers test. There are two records in this list:
The ID field is super important here. This is the generated ID by SharePoint when a record is created. We cannot edit or manipulate this column and there’ll never be any duplicates. We’ll need this column shortly.
The cmbLookup combo box can be referenced to get the record of the selected person. We use ‘Selected’ here without any additional fields on the end, we need the whole record as that’s what the column expects:
Patch(
ComplexColumnsList,
Defaults(ComplexColumnsList),
{
LookupColumn: cmbLookup.Selected
}
);
To update a lookup field without a control to use, we have to find the ID of the related entry. Referring to our Approvers test SharePoint list, let’s say we want to select and save the details for Leia who is ID 3. We need to include that ID in the syntax to target that row for the lookup column to recognise and accept.
As well as ID, you’ll see in the examples below that we also need a ‘Value’ field. This can be blank, but it has to be present in the syntax.
Patch(
ComplexColumnsList,
Defaults(ComplexColumnsList),
{
LookupColumn: {
Id: 3,
Value: ""
}
}
)
If you’re not sure of the List ID, you can use the lookup function to find out. Take care to use a unique reference that will only return a single result:
Patch(
ComplexColumnsList,
Defaults(ComplexColumnsList),
{
LookupColumn: {
Id: LookUp('Approvers test', Email = "Leia.Organa@RebelAlliance.space").ID,
Value: ""
}
}
)
Yes/No
I’ve added a toggle control to my Power App and renamed it to tglYesNo. A toggle in Power Apps shows the text as ‘On’ when selected and ‘Off’ when unselected. However, the output of this control actually returns a boolean value of true (for ‘On’) or false (for ‘Off’). We can prove this by adding a label control to the app and referencing the value property of the toggle control:
Therefore, to patch a Yes/No field in a SharePoint list using a toggle control, we can reference the control and its value property:
Patch(
ComplexColumnsList,
Defaults(ComplexColumnsList),
{
YesNo: tglYesNo.Value
}
);
Without a control to reference, we can simply reference true or false for the input. Remember, a boolean value is expected, not text, so there are no quotation marks present:
Patch(
ComplexColumnsList,
Defaults(ComplexColumnsList),
{
YesNo: true
}
);
Power Apps kindly colours boolean values in Power Fx for us, with a lovely shade of 💩 brown:
Finally, a reminder that even though the SharePoint column is boolean under the hood, visually a value of true is shown as a tick. A value of false will be shown as blank:
Thanks for reading! Hopefully there’s some useful methods & code snippets to help you patch some of the complex SharePoint columns with Power Apps.
If you liked this article and want to receive more helpful tips about Power Platform every week, don’t forget to subscribe 😊
Good and insightful article. I always learn something new from your articles if I’m quite familiar with the subject. In this case, the Lookup patch was quite useful as I haven’t used that until recently as I’m working on a project that requires it. Thanks
Thanks Douglas! The Dataverse version comes out next week, so hopefully that too will be useful for a future project
Absolutely. Will look forward to it. Thanks