Managed Metadata Lookups

To add or edit data in Managed Metadata columns you need to find out the internal format SharePoint uses to identify each value, and then return your data in this format.

The internal format SharePoint uses is: TermName|IdForTerm(guid) (e.g. Developer|56e6231f-2493-4c67-995d-fef92818619a) but you are best to check this is the same for your instance.

Check the Format

To check the format that SharePoint is expecting you will need to set TidyLookupData to False on your target SharePoint connection in Data Sync. This ensures that you see the raw SharePoint column value. You can find TidyLookupData in the connection properties window below the columns.

Then preview the data on your SharePoint side by using the preview button in the data source window.

If you do not have any data currently in your list you can manually update a row in SharePoint and then preview the data to see what is expected.

Format the Value

To set the value for Managed Metadata columns you need to provide the Guid (IdForTerm) and name of the term. This value should then be mapped to the TermId column (ManagedMetaData_0) that is returned from your schema.

You can use a Calculated Column with FORMAT and LOOKUP expressions to lookup into the TaxonomyHiddenList to get the IdForTerm guid value for a given term value and then return the value in the format that is expected.

The calculated column expression to Lookup in the TaxonomyHiddenList for IdForTerm where Path == TaxonomyTitle:

FORMAT("{0}|{1}", TaxonomyTitle, LOOKUPB("IdForTerm", "TaxonomyHiddenList"), WHEN("Path", TaxonomyTitle))

To lookup in the TaxonomyHiddenList in a different site for IdForTerm where Title == JobTitle your expression would be similar to:

FORMAT("{0}|{1}", JobTitle, LOOKUPB("IdForTerm", FROM(KV("SharePointUrl", " https://simegoltd.sharepoint.com"), KV("ListName", "TaxonomyHiddenList")), WHEN("Title", JobTitle)))

To take the expression further to check if JobTitle is NULL, and if so return NULL, then Lookup in the TaxonomyHiddenList in a different site for IdForTerm where Title == JobTitle the expression would be similar to:

IF(ISNULLOREMPTY(JobTitle), NULL(), FORMAT("{0}|{1}", JobTitle, LOOKUPB("IdForTerm", FROM(KV("SharePointUrl", " https://simegoltd.sharepoint.com"), KV("ListName", "TaxonomyHiddenList")), WHEN("Title", JobTitle))))

Compare and Preview

Before synchronising you can preview the changes to make sure they are correct. To do this click Compare A -> B and then click onto the results to preview the changes.

Below you can see that the item is being updated to only state Developer rather than having Director and Developer as the options.

Return an Arrary of Data to a Managed Metadata Field

If your column has an array of items e.g. itemA;itemB;itemC separated by a semi-colon and you need to lookup the correct guid value for each, you will need to use Dynamic Columns to loop through each item in the list.

An example of the code you could use in Dynamic Columns is:

public string MyJobTitle 
    { 
        get 
        {
            
            if(string.IsNullOrEmpty(JobTitle)) return null;
            
            var result = new List<string>();
            foreach(var val in JobTitle.Split(';'))
            {
                result.Add(FORMAT("{0}|{1}", val, LOOKUPB("IdForTerm", FROM(KV("SharePointUrl", " https://simegoltd.sharepoint.com"), KV("ListName", "TaxonomyHiddenList")), WHEN("Title", val))));
            }
            return string.Join(";", result.ToArray());            
        }
    }

Note: If the column data type is Array you can omit the split from the code.