In this post we describe how to use a hidden taxonomy list to allow us to change the format and store metadata terms, plus their parents in SharePoint Online.
Lists and libraries in SharePoint Online have the ability to show a term or the term plus parents within a managed metadata column by editing said column and changing a neat little option called display format.
However, when you turn on this option your term plus parents displays in the following way:
Parent:Term. What if you wanted to change the way the parent plus child terms are displayed into either a different or user friendly format? Let’s do exactly that:
The taxonomy hidden list
There is a secret list within each SharePoint site called the taxonomy hidden list that, as the name suggests is a hidden list that stores cached managed metadata terms used within site. The taxonomy hidden list is unique to the site and each time terms are used within the site, they get stored within the taxonomy hidden list.
This list contains a lot of repeated stuff that might look confusing, but it does specifically contain the full path of a term including it’s parent, which is what we are looking for.
So in my example I want to replace the colon in the parent plus term with a comma and a space, so it looks like this: Parent, Term. In the below example my managed metadata column is called Department. Here’s how I did it step-by-step:
- In your target list/ library, create a new single line of text column to store you formatted parent plus term (DeptCopy in my example).
Create a new flow in Power Automate based on the when a new item is created SharePoint trigger, making sure your site address and list name are added and configure the actions below:
Get item action
- Set the site address as the target SharePoint site and the list name as TaxonomyHiddenList. Note: you will need to enter a custom value here and type this in.
- Set the Id to equal the managed metadata column (Department) WssId from the when a new item is created trigger.
- Create a new variable to store the full path of the term from the get items action. Give the variable a name set the value to equal Path from the get items action.
- Use the compose data operation to create an expression to replace the colon with a comma and space. You don’t have to do this step, I just use it for troubleshooting to easily see what the outputs are of my input value.
- Update your previously created column to show the newly formatted term plus parent with the output from the compose action. Note: If you don’t use the compose action from the previous step, just write the expression above directly in the column you want to update.
- There is quite a bit online about seeing empty values in your managed metadata column. Although I’ve not personally noticed this, the taxonomy hidden list is set to have unique permissions by default and has the following permissions:
- Anonymous users: view items
- Everyone: read
- There is a good write up of the taxonomy hidden list here.
- Although there are a number of dynamic content options available, none of them seem to contain the parent + term like they do in the hidden taxonomy list. For example the path column that’s available from the ‘create an item’ trigger, outputs ‘null’.