Update SharePoint Complex Columns in Power Apps
Updating SharePoint complex columns such as choice and person types takes a little more effort than most column types. In this post I will show you step by step how to update SharePoint complex columns in Power Apps. Also see the bonus content at the end of this post on patching new records. Continue reading and you too will become an Office Power User!
Setting Up The Gallery for The Demo!
For the demo I am using a SharePoint list named “FinancialSampleDemo” with three columns. A choice column named “PowerPlatformTool” a person column named “AssignToSingle” and a person column named “AssignTo”. Note that for this demo I am only displaying two images when multiple people are assigned. If you need more than I would suggest to insert a sub gallery to handle this.
- Vertical Gallery:
- Name: galleryComplexData
- X: 0
- Y: 206
- Width: 640
- Height: 444
- Items: FinancialSampleDemo
- Fill: RGBA(0, 0, 0, 0)
- TemplateFill: White
- Template padding: 10
- Template size: 188
- Label: To display the power tool of choice.
- X: 30
- Y: 90
- Width: 316
- Height: 33
- Text: ThisItem.PowerPlatformTool.Value
- Label: Used to display the single person value.
- X: 10
- Y: 73
- Width: 316
- Height: 43
- Text: ThisItem.AssignedToSingle.Email
- ListBox: Used to display the multi person values.
- X: 10
- Y: 73
- Width: 316
- Height: 75
- Items: ThisItem.AssignedTo.Email
- Visible: CountRows(ThisItem.AssignedTo)>1
- Image: This shows the first image.
- X: 369
- Y: 55
- Width: 96
- Height: 96
- Border radius: 50
- Image:
If(CountRows(ThisItem.AssignedTo) > 1, First(ThisItem.AssignedTo).Picture, ThisItem.AssignedToSingle.Picture
)
- Image: This shows the second image.
- X: 491
- Y: 55
- Width: 96
- Height: 96
- Border radius: 50
- Visible: CountRows(ThisItem.AssignedTo)>1
- Image: Last(ThisItem.AssignedTo).Picture
Update a SharePoint Choice Column
For the choice column I am using a column named “PowerPlatformTool”. This is pretty straight forward however, if the column property “Allow ‘Fill-in’ choices” is set to no than you need to pass in one of the choice values set in SharePoint.
- Text Input:
- Name: txtChoice
- X: 40
- Y: 725
- Width: 560
- Height: 70
- Button: Used to update the complex choice column. Select your gallery item first.
- X: 33
- Y: 815
- Width: 573
- Height: 83
- Text: “Update Choice Column”
- OnSelect:
UpdateIf(
FinancialSampleDemo,
ID = galleryComplexData.Selected.ID,
{
PowerPlatformTool: {
‘@odata.type’: “#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference”,
Value: txtChoice.Text
}
}
);
Refresh(FinancialSampleDemo);
Reset(txtChoice)
Update a SharePoint Single Person Column
For the single person SharePoint column I am using a person column named “AssignedToSingle” with the “Allow multiple selections” property set to no. Person columns with this property set to no are the easiest to work with.
- Button: Used to update the complex single person column. Select your gallery item first.
- X: 33
- Y: 921
- Width: 573
- Height: 83
- Text: “Update Single Person Column”
- OnSelect:
UpdateIf(
FinancialSampleDemo,
ID = galleryComplexData.Selected.ID,
{
AssignedToSingle: {
‘@odata.type’: “#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference”,
Claims: “rodriguesconsulting@ABC.com”,
Department: “”,
DisplayName: “”,
Email: “”,
JobTitle: “”,
Picture: “”
}
}
);
Refresh(FinancialSampleDemo)
Update a SharePoint Multiple Person Column
For the multiple person SharePoint column I am using a person column named “AssignedTo” with the “Allow multiple selections” property set to yes. Person columns with this property set to yes require a bit more effort to update as you will see below.
- Button: Used to update the complex single person column. Select your gallery item first.
- X: 33
- Y: 1027
- Width: 573
- Height: 83
- Text: “Update Multi Person Column”
- OnSelect:
UpdateIf(
FinancialSampleDemo,
ID = galleryComplexData.Selected.ID,
{
AssignedTo: Table(
{
‘@odata.type’: “#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference”,
Claims: “paul@abc.com”,
Department: “”,
DisplayName: “”,
Email: “”,
JobTitle: “”,
Picture: “”
},
{
‘@odata.type’: “#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference”,
Claims: “rodriguesconsulting@abc.com”,
Department: “”,
DisplayName: “”,
Email: “”,
JobTitle: “”,
Picture: “”
}
)
}
);
Refresh(FinancialSampleDemo)
Bonus: Patch a New Record to SharePoint Multiple Person, Single Person and Choice Columns
Add the following code to a button on select to patch a new record to SharePoint populating a choice column, single person column and a multiple person column.
- Button: Used to patch a new record with a choice, single person and a multiple person columns.
- OnSelect:
Patch(
FinancialSampleDemo,
Defaults(FinancialSampleDemo),
{
PowerPlatformTool: {
‘@odata.type’: “#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference”,
Value: “Power Apps”
},
AssignedToSingle: {
‘@odata.type’: “#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference”,
Claims: “rodriguesconsulting@abc.com”,
Department: “”,
DisplayName: “”,
Email: “”,
JobTitle: “”,
Picture: “”
},
AssignedTo: Table(
{
‘@odata.type’: “#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference”,
Claims: “paul@abc.com“,
Department: “”,
DisplayName: “”,
Email: “”,
JobTitle: “”,
Picture: “”
},
{
‘@odata.type’: “#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference”,
Claims: “rodriguesconsulting@abc.com”,
Department: “”,
DisplayName: “”,
Email: “”,
JobTitle: “”,
Picture: “”
}
)
}
)
- OnSelect:
Now you know how to patch and update complex SharePoint columns in Power Apps! Please leave your feedback. I hope this article has been helpful for you so bookmark this blog as new articles are posted regularly.
Additional Resources
SharePoint Date Delegation in Power Apps
Defining Default Values for Complex SharePoint Columns
