Power Apps Patch Function for SharePoint’s Complex Column Types

Working with Power Apps, you end up having to know a lot of little stuff that is impossible to remember. This article isn’t something entirely new. In fact, I’ve effectively stolen the meat of it from other posts, which I’m listing as sources at the bottom. However. I haven’t seen these formats all collected together in one place before – I could be missing it if there is such a place.

Working with Power Apps and SharePoint, you have to know a special format to use when saving complex column types back to lists and libraries. They are complex enough themselves that I expect no one actually remembers them.

If you have worked with SOAP or REST API endpoints over the years, these structures should look pretty familiar to you. For example, we know that a Lookup column contains the LookupId and the LookupValue. In Power Apps we need to provide those two values, but the format is unique to Power Apps – as far as I can tell.

---

UPDATE – Jan 2, 2021 – Over the 2020 holidays, I saw a tweet go by from @mattbdevaney. In it, he listed his Top 10 Power Apps Tips from 2020. It’s a useful set of tips, so do read the rest. But number 8 pointed out that sometime in mid-2020, the odata.type was longer required, at least for Lookup and Person or Group columns. You still need to know which properties to pass for those column types, but you don’t need the odata.type property. Thus, I have commented out (//) the odata.type properties for the two column types. It doesn’t hurt to pass them, but they are no longer needed.

Top #PowerAppsTip Posts Of 2020 🔥🔥🔥

I've made a list of the top #PowerApps tips from this year. Check out how to use them in the article below.

🔗Link To Articlehttps://t.co/phv31RCEzL pic.twitter.com/cJ6XSct3LY

— Matthew Devaney (@mattbdevaney) December 29, 2020

---

So this post is the post I will return to again and again as I need to Patch data into SharePoint lists and libraries. One might hope that someday Power Apps will be smart enough to make these formats obsolete, but until then, here we are. If I’ve missed a complex column in this first pass, I’ll add it later so I have my own one stop shop. Let me know if you think I’m missing something.

General Instructions

I’m formatting each example as JSON, since that’s the closest analog language. In each case, there is an @odata.type, which tells SharePoint what you are sending it, along with a set of values. I’ve include some instructions along with each example.

The single or double quotes have to be what’s shown in the examples. Watch out, as sometimes when we copy from blog posts, we end up with “curly quotes” which won’t work. I’ve tried to avoid those here, but if you have a problem, check the single and double quotes first.

A Patch function in Power Apps might look something like this. In this example, I’m updating three columns in the SharePoint list called StoreOrderHeader. This Patch reflects the part of the order process where the warehouse ships an order. They click a Save button, and the Patch function below updates the order header.

Column Name	Column Type	Comments
ShipDate	Date/Time	I’m setting the ShipDate to today’s date.
DetailLines	Number	This is the count of detail lines for the order: how many different products were ordered.
ShippedBy	Person or Group	The current user – who has clicked the Save button – is considered the shipper.

Date/Time columns and Number columns are not what we call complex columns: they aren’t made up of separate properties like the examples below. We can simply provide a value, though in the case of Date/Time, it has to be an ISO date string.

Patch(
      StoreOrderHeader,
      thisOrder,
      {
          ShipDate: Today(),
          DetailLines: orderDetailLines,
          ShippedBy: {
              // '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
              Claims: currentUser.Id,
              Email: currentUser.Mail,
              Picture: "",
              JobTitle: "",
              Department: "",
              DisplayName: currentUser.DisplayName
          }
      }
  );

Choice

Let me make my editorial comment here that I do not like Choice columns. It’s too easy to end up with orphaned values in the the column which always seem to bite us down the road. Wherever possible, use a Lookup column instead – unless the limited number of choices will be very unlikely to change.

Format

ChoiceColumnName: {  
    '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",  
    Value: <choice selected value>
}

Example

Severity: {  
    '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",  
    Value: "Red"
}

Lookup

As I mentioned above, Lookup columns have two values: the ID from the lookup list and the value of the item in the lookup list.

Format

LookupColumnName: {  
    // '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",  
    Id: <Lookup ID>,  
    Value: <Lookup Value>
}

Example

LookupColumnName: {  
    // '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",  
    Id: 3,   
    Value: "Arizona"
}

Person or Group

The Person or Group column format looks worse than it is. You have to pass all the properties shown, but you can leave some of them blank. The properties which are required are: Claims, Email, and DisplayName. One might think that Claims or Email alone would be enough (since either is unique), but then one would not be an API designer at Microsoft.

Format

PersonOrGroupColumnName: {  
    // '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
    Claims: <claim for the user>,
    Email: <user's email address>,
    Picture: "",
    JobTitle: "",
    Department: "",
    DisplayName: <user's display name>
}

Example

Here’s an example which looks more like what you’d actually do in your Patch if you are saving the current user in a Person or Group column.

In my Power App, I set a variable for currentUser in the OnVisible for the landing screen.

Set(thisUser, User());
Set(
     currentUser,
     Office365Users.UserProfile(thisUser.Email)
 );

I’ve looked up the user with the Office365Users connector because that tells me more about the user than I get with the User() function.

Then, when I want to set the ShippedBy column to the current user, I can do this:

ShippedBy: {
    // '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
    Claims: "i:0#.f|membership|" & currentUser.Mail,
    Email: currentUser.Mail,
    Picture: "",
    JobTitle: "",
    Department: "",
    DisplayName: currentUser.DisplayName
}

A more complex example I got from one of the sources below shows how you can build the Claims value from scratch based on a selection in a dropdown

ShippedBy: {  
    // '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",  
    Claims: "i:0#.f|membership|" & Lower(  
       LookUp(  
          Office365Users.SearchUser(),  
          DisplayName = Dropdown_ShippedBy.DisplayName   
       ).Mail  
    ),  
    Email: LookUp(  
       Office365Users.SearchUser(),  
          DisplayName = Dropdown_ShippedBy.Selected.DisplayName  
    ).Mail,  
    Picture: "",  
    JobTitle: "",  
    Department: "",  
    DisplayName: Dropdown_ShippedBy.Selected.DisplayName
}

Managed Metadata

You’ll most often use a Managed Metadata column in a dropdown (at least in my experience). If you have a collection of the values available for the column, you’ll have all 5 pieces of information you need.

Format

ManagedMetadataColumnName: {
    '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedTaxonomy",
    'TermGuid': <TermGuid>,
    'WssId': <WssId>,
    'Label': <Label>,
    'Path': <Path>,
    'Value': <Value>
}

Example

Let’s say you have a Managed Metadata column called Store in a list called StoreOrderHeader. You can create a Collection based on the available choices for the Store column.

ClearCollect(
     StoresCollection,
     Choices([@StoreOrderHeader].Store)
 );

If you’ve used the StoresCollection to populate a dropdown called Dropdown_Store by setting Items = StoresCollection, your Patch element will look like this:

Store: {
    '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedTaxonomy",
    'TermGuid': Dropdown_Store.Selected.TermGuid,
    'WssId': Dropdown_Store.Selected.WssId,
    'Label': Dropdown_Store.Selected.Label,
    'Path': Dropdown_Store.Selected.Path,
    'Value': Dropdown_Store.Selected.Value
}

Sources

• PowerApps Patch Function With Complex Columns (c-sharpcorner.com)
• Saving to SharePoint Managed Metadata columns usin… – Power Platform Community (microsoft.com)
• PowerApps: How to update SharePoint choice and lookup type columns with Patch – Giacomo Baizini’s business IT blog (baizini-it.com)
• Top 10 Power Apps Tips Of 2020 – Matthew Devaney