🚹SharePoint People Column : How to Update and Patch in Power Automate and Power Apps

Last Updated:

In this blog post, we’ll explore the process of updating a SharePoint person column using both Power Automate and Power Apps. By the end of this post, you will have a clear understanding of how to effectively update person columns in SharePoint using these powerful tools. 

CONTENT:

  1. Updating a People column in Power Automate
  2. Patching a People column in Power Apps
  3. Updating a MULTI-SELECT People column in Power Automate
  4. Updating a MULTI-SELECT People column in Power Apps
  5. How to CLEAR a People column using Power Automate
  6. How to CLEAR a People column using Power Apps
  7. Watch Out!

In the examples below I will use a SharePoint List called Acronyms. This list has a Person type columns called “Added By”:

In the examples below I will use a SharePoint List called Acronyms. This list has a Person type columns called “Added By”:

1. Updating a People column in Power Automate

In the example below “Added By” column is a People Only column and multiple people selections are not allowed:

The  “Added By” column is a People Only column and multiple people selections are not allowed.

In this example I will update the “Added By” column using the following property:

USAGE:
   <userPrincipalName>
  
EXAMPLE:
   JohannaL@onmicrosoft.com
  

The userPrincipalName (UPN) of the person is usually the same as the person’s email address.

The user Principal Name will be used with a Claim. A Claim is simply a piece of information (an attribute or property) about the user (the Azure AD user object). 

Create item action using just the UPN for the Added by claims
Runtime output of the SharePoint Create item action using just the UPN for the Added by claims. Using just the UPN the value is automatically converted to a "Claims" object.

Specifying the full Claims property works too

USAGE:
   i:0#.f|membership|<userPrincipalName>
  
EXAMPLE:
    i:0#.f|membership|JohannaL@onmicrosoft.com
  

This is how we use the full Claims property syntax:

Create item action using just the full claims value, not just the UPN for the Added by claims
Runtime output of the SharePoint Create item action using just the full claims value, not just the UPN for the Added by claims

However, using just the UPN is simpler as the value is automatically converted to a “Claims” object.

2. Patching a People column in Power Apps

In the example below the “Added By” column is a People Only column and multiple selections are not allowed:

The  “Added By” column is a People Only column and multiple people selections are not allowed.
USAGE:
<Column-Name>: {
	Claims: "<userPrincipalName>",
	DisplayName: "",
	Email: "",
	Picture: "",
	Department: "",
	JobTitle: ""
}
EXAMPLE:
Patch(
    Acronyms,
    Defaults(Acronyms),
    {
        'Added By': {
            Claims: "JoniS@onmicrosoft.com",
            DisplayName: "",
            Email: "",
            Picture: "",
            Department: "",
            JobTitle: ""
        }
    }
)

The userPrincipalName (UPN) of the person is usually the same as the person’s email address.

The user Principal Name will be used with a Claim. A Claim is simply a piece of information (an attribute or property) about the user (the Azure AD user object). 

The following code example patches a People column called AddedBy where multiple selections are not allowed:

Code that patches a Person column called AddedBy where multiple selections are not allowed

Specifying the full Claims property works too

USAGE:
<Column-Name>: {
	Claims: "i:0#.f|membership|<userPrincipalName>",
	DisplayName: "",
	Email: "",
	Picture: "",
	Department: "",
	JobTitle: ""
}

3. Updating a MULTI-SELCT People column in Power Automate

In the example below the “Added By” column is a People Only column that allows multiple selections:

In the example below the “Added By” column is a People Only column that allows multiple selections.

3.1 Updating a MULTI-SELCT People column with a SINGLE Person in Power Automate

To update a People column where multiple selections are allowed we must use an array. The array must be formatted as an array of JSON objects as the examples below will illustrate.

To update a multi-select People column with a single person the array will contain only a single entry or Claim:

USAGE:
[
  {
    "Claims": "<userPrincipalName>"
  }
]
EXAMPLE:
[
  {
    "Claims": "JohannaL@onmicrosoft.com"
  }
]
   

The userPrincipalName (UPN) of the person is usually the same as the person’s email address.

initialise an array variable
Create item action using the array variable for the Added By Column

Specifying the full Claims property works too

USAGE:
[
  {
    "Claims": "i:0#.f|membership|<userPrincipalName>"
  }
]
EXAMPLE:
[
  {
    "Claims": "i:0#.f|membership|AlexW@dev365.com"
  }
]
   

This is how we use the full Claims property syntax with an array:

initialise an array variable
Create item action using the array variable for the Added By Column

However, using just the UPN is simpler as the value is automatically converted to a “Claims” object.

3.2 Updating a MULTI-SELCT People column with MULTIPLE Persons in Power Automate

To update a multi-select People column with multiple persons we simply add a Claims row for each person to the array:

USAGE:
[
  {
    "Claims": "<userPrincipalName #1>"
  },
  {
    "Claims": "<userPrincipalName #2>"
  },
  {
    "Claims": "<userPrincipalName #3>"
  }
]
EXAMPLE:
[
  {
    "Claims": "JohannaL@onmicrosoft.com"
  },
  {
    "Claims": "HenriettaM@onmicrosoft.com"
  },
  {
    "Claims": "DiegoS@onmicrosoft.com"
  }
]

The userPrincipalName (UPN) of the person is usually the same as the person’s email address.

To update a multi-select column with a multiple persons the array is formatted as an array of JSON objects containing one or more items (or Claims):

To update a multi-select columns with a multiple persons the array is formatted as an array of JSON objects containing one or more items (Claims)
Create item action using an array for the Added By column
Specifying the full Claims property works too

To update a multi-select People column with multiple persons we simply add a Claims row for each person to the array:

USAGE:
[
  {
    "Claims": "i:0#.f|membership|<userPrincipalName #1>"
  },
  {
    "Claims": "i:0#.f|membership|<userPrincipalName #2>"
  },
  {
    "Claims": "i:0#.f|membership|<userPrincipalName #3>"
  }
]
EXAMPLE:
[
  {
    "Claims": "i:0#.f|membership|JohannaL@onmicrosoft.com"
  },
  {
    "Claims": "i:0#.f|membership|HenriettaM@onmicrosoft.com"
  },
  {
    "Claims": "i:0#.f|membership|DiegoS@onmicrosoft.com"
  }
]

The userPrincipalName (UPN) of the person is usually the same as the person’s email address.

To update a multi-select column with a multiple persons the array is formatted as an array of JSON objects containing one or more items (or Claims):

To update a multi-select columns with a multiple persons the array is formatted as an array of JSON objects containing one or more items (Claims)
Create item action using an array for the Added By column

4. Updating a MULTI-SELCT People column in Power Apps

In the example below the “Added By” column is a People Only column that allows multiple selections:

In the example below the "Added By" column is a People Only column that allows multiple selection

4.1 Updating a MULTI-SELCT People column with a SINGLE Person in Power Apps

To update a multi-select People column where multiple selections are allowed, we use the Power Apps Table function.

To update the multi-select People column with a single person or multiple persons we create a table using the properties of the People column:

USAGE:
   
<Column-name>: Table(
      {
         Claims: "<userPrincipalName>",
         DisplayName: "",
         Email: "",
         Picture: "",
         Department: "",
         JobTitle: ""
      }
   )
EXAMPLE:
Patch(
    Acronyms,
    Defaults(Acronyms),
    {
        'Added By': Table({
            Claims: "DiegoS@onmicrosoft.com",
            DisplayName: "",
            Email: "",
            Picture: "",
            Department: "",
            JobTitle: ""
        }
        )
    }
)

Use the userPrincipalName (UPN) of the person is usually the same as the person’s email address, but not always.

To update a multi-select People column we use a table. The table may contain one or more claims.
To update a multi-select People column we use a table. The table may contain one or more claims.
Specifying the full Claims property works too
USAGE:
   
<Column-name>: Table(
      {
         Claims: "i:0#.f|membership|<userPrincipalName>",
         DisplayName: "",
         Email: "",
         Picture: "",
         Department: "",
         JobTitle: ""
      }
   )
EXAMPLE:
Patch(
    Acronyms,
    Defaults(Acronyms),
    {
        'Added By': Table({
            Claims: "i:0#.f|membership|DiegoS@onmicrosoft.com",
            DisplayName: "",
            Email: "",
            Picture: "",
            Department: "",
            JobTitle: ""
        }
        )
    }
)

4.2 Updating a MULTI-SELCT People column with MULTIPLE Persons in Power Apps

To update a multi-select People column with multiple persons we simply add a Claims record for each person to the array:

USAGE:
   
<Column name>: Table(
      {
         Claims: "<userPrincipalName #1>",
         DisplayName: "",
         Email: "",
         Picture: "",
         Department: "",
         JobTitle: ""
      },
      {
         Claims: "<userPrincipalName #2>",
         DisplayName: "",
         Email: "",
         Picture: "",
         Department: "",
         JobTitle: ""
      },
      {
         Claims: "<userPrincipalName #n>",
         DisplayName: "",
         Email: "",
         Picture: "",
         Department: "",
         JobTitle: ""
      }
   )

To update the multi-select People column with a multiple persons we create a table using the properties of the People column and add a Claims record for each person to the array:

EXAMPLE:
Patch(
    Acronyms,
    Defaults(Acronyms),
    {
        'Added By': Table(
            {
                Claims: "DiegoS@onmicrosoft.com",
                DisplayName: "",
                Email: "",
                Picture: "",
                Department: "",
                JobTitle: ""
            },
            {
                Claims: "IsaiahL@onmicrosoft.com",
                DisplayName: "",
                Email: "",
                Picture: "",
                Department: "",
                JobTitle: ""
            }
        )
    }
)
To update the multi-select Person column with a multiple persons we create a table using the properties of the Person column and add a Claims record for each person to the array:
To update a multi-select People column we use a table. The table may contain one or more claims.
Specifying the full Claims property works too
USAGE:
   
<Column name>: Table(
      {
         Claims: "i:0#.f|membership|<userPrincipalName #1>",
         DisplayName: "",
         Email: "",
         Picture: "",
         Department: "",
         JobTitle: ""
      },
      {
         Claims: "i:0#.f|membership|<userPrincipalName #2>",
         DisplayName: "",
         Email: "",
         Picture: "",
         Department: "",
         JobTitle: ""
      },
      {
         Claims: "i:0#.f|membership|<userPrincipalName #n>",
         DisplayName: "",
         Email: "",
         Picture: "",
         Department: "",
         JobTitle: ""
      }
   )

5. How to CLEAR a People column using Power Automate

To clear or set a People column to empty or blank is not obvious, so this sections will cover how we do this using a HTTP request:

  • 5.1 Clear a SINGLE select People column
  • 5.2 Clear a MULTI-SELCT People column

5.1 Clear a SINGLE select People column in Power Automate

We will need to use a HTTP request to clear a People column. But before we can do this we need get something called the ListItemEntityTypeFullName.

Get the ListItemEntityTypeFullName 

To perform the clear operation, you must know the ListItemEntityTypeFullName property of the list. This property is easy to obtain. We will use the ListItemEntityTypeFullName value in the second step when we clear the People column.

 One way to get the ListItemEntityTypeFullName is to use the following rest API call:

USAGE:
Method: GET
Uri:
_api/web/lists/GetByTitle('<List-Name>')?$select=ListItemEntityTypeFullName
Headers:
{
  "Accept": "application/json;odata=nometadata"
}
EXAMPLE:
Method: GET
Uri:
_api/web/lists/GetByTitle('Acronyms')?$select=ListItemEntityTypeFullName
Headers:
{
  "Accept": "application/json;odata=nometadata"
}
Use the following rest call to get the ListItemEntityTypeFullName for the list Acronyms
Using a HTTP request to get the ListItemEntityTypeFullName

Examine the runtime output of the request to get the text for the ListItemEntityTypeFullName:

The runtime output of the request to get the text for the ListItemEntityTypeFullName
Run time output of the HTTP request to get the ListItemEntityTypeFullName.

In the example above the HTTP request to get the ListItemEntityTypeFullName value is found to be SP.Data.AcronymsListItem. Therefore the ListItemEntityTypeFullName for my list Acronyms is SP.Data.AcronymsListItem. This property value will be used in the next HTTP request to clear the People column.

Using a HTTP request to Clear a SINGLE select People column in Power Automate

In the example below the “Added By” column is a People Only column and multiple selections are not allowed:

In the example below “Added By” column is a People Only and multiple selections are not allowed :
The SharePoint list showing the  “Added By” column sample data. List ID 80 is shown

Here is an example of clearing the Added By column from a SharePoint List called Acronyms where the list item ID is 80. The “type” property for the list Acronyms is SP.Data.AcronymsListItem which we obtained in the previous step.

Here is an example of clearing the Added By column from a SharePoint List called Acronyms where the list item ID is 80.

➡️ Use the SharePoint internal name for the People column in the Body of the HTTP request and not the display name. For example, in the Acronyms list I have a column named “Added By” and this is also known as the display name of the column “Added By”. But the “Added By” column also has an internal SharePoint nameAddedBy” and I need to use this internal name in the HTTP requests.

USAGE:
Method: POST
Uri:
_api/web/lists/GetByTitle('<List-Name>')/items(<Item-ID>) 
Headers:
{
  "IF-MATCH": "*",
  "X-HTTP-Method": "MERGE",
  "Content-Type": "application/json;odata=verbose",
  "Accept": "application/json;odata=verbose"
}
Body:
{
  "__metadata": {
    "type": "<ListItemEntityTypeFullName>"
  },
  "<Column-Name>StringId": "",
  "<Column-Name>Id": -1 
}
EXAMPLE:
Method: POST
Uri:
_api/web/lists/GetByTitle('Acronyms')/items(80) 
Headers:
{
  "IF-MATCH": "*",
  "X-HTTP-Method": "MERGE",
  "Content-Type": "application/json;odata=verbose",
  "Accept": "application/json;odata=verbose"
}
Body:
{
  "__metadata": {
    "type": "SP.Data.AcronymsListItem"
  },
  "AddedByStringId": "",
  "AddedById": -1 
}

“-1” is the “magic” value that clears the People column

5.2 Clear a MULTI-SELCT People column in Power Automate

In the example below the “Added By” column is a People Only column and multiple selections are allowed:

In the example below “Added By” column is a People Only and multiple selections are allowed:
The SharePoint list showing the  “Added By” column sample data. List ID 80 is shown. It has 3 persons assigned to the Added By column
USAGE
Method: POST
Uri:
_api/web/lists/GetByTitle('<List-Name>')/items(<item-ID>)/validateUpdateListItem
Body:
{
    "formValues":[
	{
	    "FieldName": "<column-name>",
	    "FieldValue": "[]"
	}
    ]
}

➡️ Use the SharePoint internal name for the People column in the Body of the HTTP request and not the display name. For example, in the Acronyms list the column display name is “Added By” but its internal name is “AddedBy”:

EXAMPLE:
Method: POST
Uri:
_api/web/lists/GetByTitle('Acronyms')/items(80)/validateUpdateListItem
Body:
{
    "formValues":[
	{
	    "FieldName": "AddedBy",
	    "FieldValue": "[]"
	}
    ]
}
The HTTP request to clear the person column

📝 SIDE NOTE: One parameters we could add to THE HTTP action is the 'bNewDocumentUpdate' parameter. By adding this parameter to the request HTTP Body we can tell the update to not create another item version. In other words when bNewDocumentUpdate is set to true the list item will be updated without creating a new version.  
 {
  "formValues": [
    {
      "FieldName": "AddedBy",
      "FieldValue": "[]"
    }
  ],
  "bNewDocumentUpdate": true
 } 
See: Update SharePoint column without new item version in Power Automate

6. How to CLEAR a People column using Power Apps

The SharePoint list showing the  “Added By” column sample data. List ID 80 is shown

To clear a People column in Power Apps we can simply use the Blank() function. Blank is a placeholder for “no value” or “unknown value.”  We can use blank for both single or multi-select People columns.

Patch(
    Acronyms,
    LookUp(Acronyms, ID = 80),
    {
        'Added By': Blank()
    }
)

7. Watch Out!

7.1 No Spaces in the Claim Property

No spaces in the Claims value

Make sure that there are no spaces in the Claims value.

7.2 How to find a user’s UPN

Flow for How to find a user's UPN

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Built with WordPress.com.

Up ↑

%d bloggers like this: