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!

Update-Complex-Columns-in-Power-Apps

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: “”
      }
      )
      }
      )

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

Share the knowledge!

Paul Rodrigues

Business Analyst with 20 years of IT experience creating practical solutions. I love to automate business processes through the use of technology while making the end users work easier. My current favorite tools are Power Apps, Power BI and Power Automate. #PowerAddict

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *