SharePoint Consulting Blog

Introduction:

In this blog post, we will learn how to dynamically create Terms in a TermSet of a Termstore and set them in a managed metadata column using Microsoft Power Automate.

Requirement:

To obtain information about Skills and Past Projects from the Office 365 user profile and add it to a SharePoint list, we will enable Out-of-the-Box (OOTB) filtering and sorting operations on these fields.

We can use Single line of text OR Multiline of text site column and store information with delimiters, but limitation was, can’t perform OOTB filtering and sorting operation.

To overcome this issue, we are going to use a Managed Metadata site column to store the terms values dynamically. This approach will allow us to perform Out-of-the-Box (OOTB) filtering and sorting operations on the information, providing a more effective solution compared to using Single line of text or Multiline of text site columns with delimiters.

Approach:

Create “Managed Metadata field” with “Allow multiple values” checkbox enabled and “Customize your term set” option selected as shown in below screenshot.

Below are the steps in Power Automate to dynamically create Terms in a TermSet of a Termstore and then create an item in a SharePoint list with those terms in a Managed Metadata field.

Step 1: Add an action “Initialize Variable”, Name it as “Initialize variable Skills” as shown in below screenshot. This will be used to store JSON for use in SharePoint list Item creation.

Step 2: Add “Get user profile (V2)” action with user principal name or Email ID in “User (UPN)” as shown below.

Step 3: Append value to “Skills” variable as shown in below screenshot.

Step 4: Add below set of actions to process each element in “Skills” array (which we retrieved in Step-2).

Step 5: Then we need to add an action called “Send an HTTP request to SharePoint” to retrieve terms from TermSet.

Uri - _api/v2.1/termStore/groups/97ac8ae5-1608-4047-8951-585b3a2640c5/sets/e0af947c-6cef-4766-8c79-9c454cc5a323/children.

In Uri, “97ac8ae5-1608-4047-8951-585b3a2640c5” is Termstore ID and “e0af947c-6cef-4766-8c79-9c454cc5a323” is TermSet ID in which we need to store or create terms.

Step 6: To compare the current item in the loop with the term name, we need to filter the values retrieved in Step-5 to check if they are "equal to" the term name.

string (item ()? ['labels'][0] ['name']) expression is used in comparison as shown in below screenshot.

Step 7: Add the following set of actions to form JSON or create JSON data for the "Skills" variable. The condition, as shown in the below screenshot, checks whether the term is present in the "Skills" TermSet or not, using the expression length(body('Filter_skills_array')). If the term is not present, we are adding a POST call to create a new term and then appending the values to the "Skills" variable. On the other hand, if the term is already present, we simply append the values to the "Skills" variable.

Step 8: If the condition is "Yes," indicating that the term is present in the "Skills" TermSet of the Termstore, then we simply append the values to the "Skills" variable.

Note: Value will be appended in JSON format as -

{

“value”: “Sharepoint|3198df72-311b-4757-9246-052296af2”

}

Where, the expression in below screenshot consists of “{current term name in loop}|body('Filter_skills_array')[0]['id']”

Step 9: If the condition is "No," meaning that the term is not present in the "Skills" TermSet of the Termstore, in such a case, we need to create the term using a POST call with the action called "Send an HTTP request to SharePoint," as shown in the below screenshot.

Uri - _api/v2.1/termStore/groups/97ac8ae5-1608-4047-8951-585b3a2640c5/sets/e0af947c-6cef-4766-8c79-9c454cc5a323/children.

In Uri, “97ac8ae5-1608-4047-8951-585b3a2640c5” is Termstore ID and “e0af947c-6cef-4766-8c79-9c454cc5a323” is TermSet ID in which we need to store or create terms.