Updating A Managed Metadata (MMS) Column Dynamically In A SharePoint List Using MS Flow

INTRODUCTION

 

Unlike SharePoint standard columns, the Managed Metadata (MMS) column expects the value to be in a certain format when we update such a column using code, REST APIs, or through MS Flow. Below is the expected format for setting the value to an MMS column.

 

TERM-LABEL|TERM_GUID

 

An example would be "Lenovo|xxxx-xxxx-xxxx-xxxx" (separated by a pipe symbol).

 

WHY NOT USE THE DEFAULT UPDATE ITEM ACTION?

 

The defaut action supports updating an MMS column and works perfectly. But, we have to set a static (or a chosen) value while designing the flow itself. Consider the below example

 

 

Consider we have an MMS Column called 'Make', which has three terms - Dell, HP, and Lenovo. The value for this column is set while designing the Flow and it will be the same for all the runs. There could be business cases where we have to set a different value for these columns based on other conditions or based on the other column values. Considering the previous example, if we want to set the "Make" value as Lenovo when the title contains Laptop, we cannot just enter text into this column; it will fail. And, we cannot keep adding nested conditions with multiple Update Item actions. The solution won't be scalable when we have 100s of terms for an MMS column.

 

THE SOLUTION

 

When we select a value for the MMS column ("Make" in the example above), the default Update Item action internally queries for the GUID of this term and sets the field's value as 'Dell|xxxx-xxxx-xxxx-xxxx'. Now, if we want to dynamically assign values to an MMS column, we can use the REST API to call the ValidateUpdateListItem endpoint and set the MMS column value in the expected format

 

Given below is a sample POST call to the REST API.

POST
/sites/Internal/_api/web/lists/getByTitle('Products')/items(id)/ValidateUpdateListItem()

PAYLOAD
{
    "formValues":   [
    {"FieldName":"Make","FieldValue":"Lenovo|<<guid-of-the-term>>"},
    ],
    "bNewDocumentUpdate":false,
    "checkInComment":null
}

HOW TO GET THE TERM'S GUID

 

We can use the 'Send an HTTP Request to SharePoint' action to call the below REST API which gives us all the details about the required managed metadata term.

POST
/sites/Internal/_vti_bin/TaxonomyInternalService.json/GetSuggestions

PAYLOAD
{"start":"[NEW VALUE]",
"lcid":1033,
"sspList":"[GUID OF SSPLIST]",
"termSetList":"[GUID OF TERM SET LIST]",
"anchorId":"00000000-0000-0000-0000-000000000000",
"isSpanTermStores":false,
"isSpanTermSets":false,
"isIncludeUnavailable":false,
"isIncludeDeprecated":false,
"isAddTerms":false,
"isIncludePathData":false,
"excludeKeyword":false,
"excludedTermset":"00000000-0000-0000-0000-000000000000"}

The payload for this request expects certain value, and we need to pass the following three dynamic values which are specific for an MMS column within a SharePoint list. Rest of the values for the properties like lcid and others can be set to default static value. We have to provide the values for the following three parameters.

• start  - The term label we are searching for (ex: Lenovo)
• sspList - The GUID of the MMS Service Application (previously known as SSP) of the Term Set
• termSetList - The GUID of the source term set for the MMS column.

FINDING THE GUIDs OF SSP and TERM SET

 

We can query for the fields of the SharePoint list to find these values. Below is the REST API call with a sample response.

GET
_api/web/lists/getByTitle('Products')/fields?$filter=InternalName eq 'Make'

SAMPLE RESPONSE PAYLOAD
{
  "value": [
    {
      "SspId": "9d7dab30-f5f1-4e32-b16e-7d4066af29e8",
      "TermSetId": "f0ca54da-dc64-4120-bf1a-ccd1604aa75c",
    }
  ]
}
//Removed all other properties in the response for readability

The response contains 2 properties SspId and TermSetId which can be used for calling the GetSuggestions endpoint to get the Terms GUID.

 

By calling these SharePoint REST APIs, we can dynamically find the GUID of a term and set its value using the ValidateUpdateListItem endpoint.

 

MS FLOW STEPS

 

Here is how the MS Flow steps should look like.

 

STEP 1

 

Query for the filed to find the MMS Columns metadata using 'Send an HTTP Request to SharePoint' action.

 

 

STEP 2

 

Initialize two variables and assign the sspId and termSetList values from the previous step's response.

 

 

sspId value : first(body('GetMMSMeta')?['value'])?['SspId']

termSetId : first(body('GetMMSMeta')?['value'])?['TermSetId']

 

STEP 3

 

A 'Send an HTTP Request to SharePoint' action calls the GetSuggestions endpoint with the required payload.

 

 

We set the 2 variables for sspId and termSetId previously declared. The newMMSValue will be another variable which will have the term label that we want to set. The value for this variable can be set based on any logical condition or based on the other values of the list columns.

 

STEP 4

 

Format the new value for the MMS field using the previous response.

 

 

The value for this variable should be : first(first(body('Suggestions')?['d']?['Groups'])?['Suggestions'])?['DefaultLabel']|first(first(body('Suggestions')?['d']?['Groups'])?['Suggestions'])?['Id']

 

Here, we are creating a string value with Term Label suffixed with the Term GUID separated by a pipe(|) symbol.

 

STEP 5

 

Set the list items value using 'Send an HTTP Request to SharePoint' action.

 

 

 

CONCLUSION

 

While the default 'Update Item' action of MS Flow supports updating the MMS Column's value with a static value, it is ideal to set it dynamically using this approach. This makes sure our Flow doesn't break when moving the flow to a different environment and supports business cases which require setting the MMS field values dynamically.