Managed Metadata columns fail to sync between SharePoint and client applications

This issue seems to be cropping up a lot at the moment, one possible fix is below.

Symptoms:

When you set a Managed Metadata Service (MMS) column in SharePoint these values are pushed down to the office document and will be visible on the Document Information Panel (DIP). When these values are changed in an office document however these MMS column changes are not updated in the SharePoint item. Non MMS fields (i.e. Single Line of Text, Choice, Number etc.) are correctly synced. If you close and re-open the office document, even from another computer, any changes made in office to the MMS values will still remain as you set them in the DIP. However as normal any changes to the values in SharePoint will be pushed down to the office document overwriting any values in the DIP.

In summary: SharePoint can write to the office document but MMS values in the document cannot be written to SharePoint by office.

Note: If text, choice or other non MMS fields are not being synced when you save the document then this is probably unrelated to your issue.

Where has this been seen:

We’ve seen it in at least two SharePoint 2010 SP1¬†environments in the last week, with farms using varying CUs. No obvious cause has been identified.
The main example is in office, at least word and Excel. This has also been seen with Harmon.ie where it is impossible to set the MMS value, it is probable other systems may be effected.

Solution:

Add and remove a MMS column from each list. You can confirm that this fixes your issue by performing a manual update of a single list and then run a bulk correction using PowerShell. Note that you will need to test and re-create any faulty Site Templates.

Cause

Not known at this time, it appears to be related to the document parser. It appears that in some cases the document Parser process fails on MMS values. The value in Word is maintained in the document’s xml fields but is not correctly udpated (at least in our tests) with the correct namespace for the term or termID.
It seems that by adding a new MMS column the issues with the other columns is corrected, we believe this might be due to some version or synchronisation process but have not tracked down the root cause.

Manual steps

In your list or library, open the list settings.

Image of library ribbon with Library Settings highlighted

Library Settings

Click on ‘Create Column’

Image of Create Column highlighted within Library Settings

Create Column

Enter a name, here we will use ‘DummyColumn’ and select ‘Managed Metadata’

Column creation process with Name and Type highlighted

Create Column (specify type and name)

Select a value in the MMS

Image of Managed metadata value selected in column creation

Select Managed Metadata Value

Click OK.

At this point you should be able to confirm that the MMS field is now synchronised between Office and SharePoint. You can then delete the column.

Note: If the process fails then delete the column anyway, unless you’re selling childrens accessories then it will probably be of little use.

Programatic

This can be scritped in several ways but the primary method will be on server PowerShell. An example script is shown below:

<#
Author: Alex Brassington (Trinity Expert Systems)
Date: 26/04/2013
Description:
Adds and removes an MMS colummn to every library in the white list for all sites in a web application. This is to
fix the office => SharePoint managed metadata service sync field issues.
This can be run with either a white list of lists/libraries to update or without, in which case all document libraries will be updated. It is possible that this only needs to run on one document library per site but i have not yet been able to confirm or refute that.
#>

Add-PSSnapin Microsoft.SharePoint.PowerShell -ea SilentlyContinue

#Reference to the content type hub to be used for the MMS Column    
$CTHubURL= "http://sharepoint/sites/cthub"

#Site Collection to modify
$SCURL = "http://sharepoint/sites/cthub"

#Name of the MMS instance to use
$MMSInstance = "Managed Metadata Service"


#A 'white list' of libraries to process. Note that this currently contains 'DOcuments' which should be handled as a special case.
$librariesToCheck =
(
    "Documents",
    "Entertainment",
    "Project Documents",
    "Management Information"
)

    #Setup the termstore object
    $contentTypeHub = Get-SPSite $contentTypeHubURL
    $session = New-Object Microsoft.SharePoint.Taxonomy.TaxonomySession($contentTypeHub)
    $termStore = $session.TermStores | ? {$_.Name -eq $MMSInstance}
    $group = $termStore.Groups["Demo Terms"]
    $termSet = $group.Termsets["Condiments"]


Function Update-LibrariesInSiteCollection ()
{
[CmdletBinding()]
    Param (
    [Parameter(Position=0,Mandatory=$true,ValueFromPipeLine=$true)][string]$siteURL, 
    [Parameter(Position=1,Mandatory=$true)][Microsoft.SharePoint.Taxonomy.TermSet]$termSet,
    [Parameter(Position=2,Mandatory=$true)][Microsoft.SharePoint.Taxonomy.TermStore]$termStore,
    [Parameter(Position=3,Mandatory=$false)][string]$errorFile,
    [Parameter(Position=4,Mandatory=$false)][string[]]$librariesToCheck
    )
    
    
    #No change required, only used internally
    $columnName = "TempColumn"
    
    #Get the SharePoint Site Collection to process
    $site = Get-SPSite $siteURL
    Write-Verbose "Updating Site Collection $($site.URL)"
    foreach ($web in $site.AllWebs)
    {
        Write-Verbose "Updating Web $($web.URL)"
        
        #If there's a list of folders to use as a whitelist then use them
        if ($librariesToCheck)
        {
            Write-Verbose "Updating libraries based on provided White list"
            $lists = $web.Lists | ? {$librariesToCheck -contains $_}
        }
        else
        {
            #If not then process all libraries.
            Write-Verbose "Updating all document libraries only"
            $lists = $web.Lists | ? {$_.BaseType -eq "DocumentLibrary"}
        }
        
        foreach ($list in $lists)
        {
            Write-Verbose "Updating list $($list.Title)"
            try
            {
                #Create a new taxonomy field
                $taxField = $list.fields.CreateNewField("TaxonomyFieldType", $columnName)
                
                #set the term store ID and the termset ID 
                $taxField.SspId = $termStore.Id
                $taxField.TermSetId = $termSet.Id
                
                #Add the column to the list
                $list.Fields.Add($taxField) | Out-Null
                $list.Update()
                
                #Remove the column
                $column = $list.fields[$columnName]
                $column.Delete()
                Write-Verbose "List Complete $($list.Title)"
            }
            catch
            {
                Write-Error "Error encountered on List: $($list.Title)"
            }
        }
    $web.Dispose()
    }
    
    #If a file path was given then write out the error log.
    if ($errorFile)
    {
        $error >> $errorFile
    }
    #Dispose of the site collection
    $site.Dispose()
}

Update-LibrariesInSiteCollection -siteURL $SCURL -termSet $termSet -termStore $termStore -errorFile $ErrorPath -Verbose

My thanks to my colleague Paul Hunt (aka Cimares) who found the fix that we scripted above.

11 thoughts on “Managed Metadata columns fail to sync between SharePoint and client applications

  1. Pingback: Managed Metadata Columns fail to sync from Office Client apps and SharePoint - Blog of an overweight SharePoint addict

  2. Dave Hanna

    Alex,
    Thanks for this. We have just encountered this same defect (as discussed on TechNet) and are looking for a workaround while we wait (probably in vain) for a fix from MS. The script above is great but I was wondering if you had anything that would copy the bad column values to the good column values.
    Thx
    Dave

    Reply
  3. Darren Cole

    Hi Alex

    I have a similar issue, but I don’t think it’s quite the same. I want to use Enterprise Keywords, but I have two major issues:

    Syncing – To isolate the issue I have set up a document library and switched on Enterprise Keywords. When I click ‘Documents’ under ‘Library Tools’ and select ‘New Document’ the document opens in Word. At the top of the word document I can enter phrases into the Enterprise Keywords field, which is shown in the ‘Document Properties – Server’ panel. Existing keywords are listed for me to select from as I type. So far so good. The trouble is these phases are not saved – not synced to SP or held within the document. I can update via SP.

    The other issue is that users who have read-only access to the library cannot see the enterprise keywords that are I can see as the library owner.

    Sorry to bother you with this. I wouldn’t normally ask, but this is the only article I’ve found that comes close to addressing the problem I have. I have followed the steps above but it hasn’t helped. I hope there’s a simple answer.

    Thanks

    Darren

    Reply
    1. Alex Post author

      That’s an interesting one. To clarify the fields are being pushed down from SharePoint happily but any changes made in the document are lost and not persisted even within the document, (as you say) unlike the issue we hit.

      If you save the document outside of SharePoint you can crack it open by renaming it as .cab and then looking at the XML. You might be able to identify a difference between the SP -> Word keywords and those set within the document.

      Hopefully, thanks to my stupidly late response, your issue will be sorted now. If not then stick a post up on TechNet and I or others can have a look in more detail than this forum allows.

      Alex

      Reply
      1. Alex Post author

        Thinking about this for a second you might be able to get that effect by adding the Enterprise Keyword column directly to the list but without ticking the ‘Add Enterprise Keywords’ option in the list settings. I believe that’s the bit that’s responsible for the synchronisation.

        Reply
  4. Pauli

    We have been investigating a similar issue at one of our clients (SharePoint 2013 SP1).

    A document library has 2 MM columns. If their values are updated in SharePoint (in Document / item Properties), everything works fine. If their values are updated in the DIP of the Office application (Whether Office 2010 or 2013), one of the columns in SharePoint updates perfectly, but the other is left at previous value. The Office DIP still displays the updated value however – i.e. the Document Properties and SharePoint item properties are out of sync.

    We found that the issue was dependent on two factors:

    1) How the document was originally added to SharePoint. If a the original document was “uploaded” to the library using the “Upload” button (or “Upload Document” icon in the Ribbon), and the metadata values entered on the upload form, any subsequent changes were synced perfectly – whether the change was made in SharePoint, or in the Office application DIP. But, if the document had been added in any other way (from SharePoint “new” button, saving a new document directly from Office, drag-and-drop to the SharePoint page), the issue appeared.

    2) After further investigations by both Microsoft support and our own consultants, we found that the issue only affects Managed Metadata columns which have spaces in their column name (or more precisely, in their ‘internal name’). These get saved twice into the Office Document Properties, once with the spaces intact, another time with the spaces replaced by ‘%20’. Following that, it appears that the Office application uses one of those properties, whilst SharePoint uses the other.

    Reply
  5. Pauli

    As an update to this:

    Microsoft Support has escalated the issue to their product team in January 2015 but it is not yet known when a hotfix might become available.

    The only method to fix the issue at present is to create a new field to replace the one causing problems, and copy the field values of existing items to the new field.

    We developed a PowerShell script to automate this – creating a replacement field across sites, libraries and content types; copying values to the new field; amending List Views to replace the old field where it was used). This worked well and solved the issue quickly. However, this still leaves a problem with documents where the metadata value may have been updated (but never written to the library); as these will then contain an out of date value.

    Reply
  6. Simon Rawson

    We found differences in the way MM is treated, with differences in DIP behaviour between Word 2013 and Word 2010, with SP Online. The former works just fine. The latter does not recognise the arm of the term set within which the term is located. Pauli may have given me a clue as to what’s going on…the arm of the term set has a space in the name. So I’ll both drop and re-add, and change the term set name.

    Reply
  7. obulreddy

    Im facing same issue , but this is not the correct method, of doing delete the meta data column and new metadata column.
    the data will lost, If I have data in the list..

    Please give the proper solution

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *