Syncronization of Office Document Properties with WSS Document Libraries

I've been spending a lot of time with Office and syncronizing properties with WSS document libraries I thought I would share. There are a few sources out there like the WSS FAQ section on Office Interaction but I thought I would try to get into some more detail here as there are some dos and donts and gotchas along the way.

Concept

When you upload a document through one of several ways, properties from the document will transfer to SharePoint fields automagically. This assumes you've actually setup the fields in WSS (only the Title property is created by default). There are four ways to upload a document to a SharePoint document library where this works:

  1. Copy and paste the document into Explorer View
  2. Drag and drop a document via a mapped drive (Windows XP only) or through your Network Places folder
  3. Save the document directly from Word/PPT/Excel into a WSS document library.
  4. Upload multiple files via the Office 2003 capability

The last two options require Office 2003 (although you can save from Office 2000 and XP it doesn't always work properly as WebDAV is a little kludgy)

Uploading the document through the "Upload Document" option on the toolbar for the document library is a Upload->Set Properties excercise. In other words, you have to upload the document (by picking it via the Browse button) then set the properties. When you select a file from your system to upload, the properties are not automatically set (although I feel they should be so hopefully we'll see this in a future version or service pack). Using the "Upload Multiple Files" gets around this problem, but again requires an Office 2003 client.

Built-in Properties

Word, Office, Powerpoint and Excel (and maybe OneNote and the other "core" products) have a set of default file properties. You can set these programatically (through COM automation) or via the File | Properties menu. These properties are:

  • Subject
  • Author
  • Manager
  • Company
  • Category
  • Keywords
  • Comments
  • Hyperlink base

These properties do not transfer to a SharePoint document library (again, I think a failing of WSS and should be fixed).

Note: There is something deep in the central administration screens that talk about mapping Office DAV properties so I'm still looking into how this works and maybe to overcome this problem.

Custom Properties - Microsoft

In addition to the built-in properties, Microsoft creates by default a series of custom properties. These are accessible via the File | Properties menu on the Custom tab. By default they're all blank but you can choose to set any of them. These are named:

  • Checked By
  • Client
  • Date completed
  • Department
  • Destination
  • Disposition
  • Division
  • Document Number
  • Editor
  • Forward To
  • Group
  • Language
  • Mailstop
  • Matter
  • Office
  • Owner
  • Project
  • Publisher
  • Purpose
  • Received from
  • Recorded by
  • Recorded date
  • Reference
  • Source
  • Status
  • Telephone number
  • Typist

Creating a field in the WSS document library with the same name as these will result in a transfer (if the property is set in the document and you use one of the four ways to upload the document). Note that once you create a field, it has an internal name (like Status) but you can change it after the fact. It will always have that internal name and if that name maps to a property, it stays that way no matter what name you change it to.

Custom Properties - Yours

Just like the default custom properties that Microsoft creates, there's no stopping you from adding any custom property. Just type in the name in the File | Properties custom tab and click Add. This will add a new property like "Creator" or something. You'll still have to set this but if you do, it will transfer to WSS (again if you create the field in the document library to match).

Transfering

As mentioned in this blog, properties set in Office documents will transfer to a WSS document library as long as two conditions are met:

  1. The property is set in the document itself as a text custom property
  2. The field is created in the WSS document library as a text field

Custom Properties - Lookups

One of the nice things about having meta-data for your documents in WSS is that you can control the properties and lookup values. Lookup tables are great for this but there's a problem with properties and transfering using a lookup. It doesn't work. Say you have a field called "Status" (one of the default custom properties) and set it "Draft" in the document. Status is a lookup into another SharePoint list you created where the user can set the value. When you transfer the document to WSS, the property doesn't get set.

However if your "Status" field in WSS is a choice field with the values you want AND you have the document property set to something that matches, the transfer works correctly. WSS doesn't care you have a custom property called "Status" in your document with a value of "Fixed" but the choices in WSS are only "Draft" and "Final". It will gleefully set the value to "Fixed" but when you edit the properties, since WSS can't find "Fixed" in the list of choices, it'll revert back to nothing (or the default value if this is a mandatory field).

So basically, if you want to have a picklist for your lookups and syncronize those values from Office -> WSS and back again:

  1. Ensure the WSS field type is "Choice" with a set of values (multi-choice through checkboxes does not syncronize)
  2. Ensure the property in Office is a text value and matches one of the choices in the WSS document library

Value Types

Office documents let you create 4 different value types for custom properties. Synchronziation (and the field type) in WSS must be text only and the type in the office document must be the same so store all custom properties in your office documents as Text fields in order to ensure synchronization.

Syncronization

As for keeping things in sync, if you set the property in WSS it will transfer to the document so when you open it and look at the File | Properties | Custom tab they'll be updated. If you set it in the document it will tranfser using one of the four methods described above. If you save a single document directly into the document library, it will clear out the file property and ask you for the value.

Summary

In conclusion, it's a powerful feature to synchronize (and populate) SharePoint document libraries with properties from Office documents. Imagine if you had a document library with all kinds of views using meta-data and you had a massive amount of documents with these properties set. A simple drag-n-drop operation into your document library and now all your views are ready to go. Even with issues around syncronization and non-existent built-in properties, it's a useful feature to leverage in your solutions.

Other References

WSS FAQ - Interaction with Office
WSS FAQ - Syncronization Issues with Certain Properties

No Comments