Tired of refreshing SharePoint external data columns?

SharePoint external data columns are an invaluable tool when drawing data from sources outside SharePoint, as I reported in previous blog entries. Whether the data exist in SQL Server, a WCF web service, or elsewhere, external data columns can integrate this data with out-of-the-box SharePoint lists.

External data columns do however have one major disadvantage. Unlike external lists, external data columns cache the information drawn from the external content type within SharePoint. While the information will be correct when the external data item is first selected, it may become out of date if the backend data changes after the external item was first populated into the list.

External data columns can be refreshed from the UI, but this can be a time-consuming and annoying process, and users may not realize data is out of date even if they are willing to manually refresh it. 

The solution to this problem is to build a custom SharePoint timer job that with some configuration can refresh external data columns.  If you are willing to tolerate a more resource-hungry timer job, the job could also be built to scan all lists within a web application and update all external data columns (no configuration would be necessary).

MSDN has a good article on building timer jobs here.  The trick for a timer job refreshing an external data column is to parse the related column containing the ID value that identifies the selected item in the external content type. This blog gives an introduction to getting this ID value. The key to parsing the so-called tombstone value is to know its format, which starts with "__b" plus an additional character, then is followed by a series of Unicode characers in a string encoded as four hex values. Within each Unicode character the byte order is little-endian (you will have to reverse the bytes to parse the character). The first such unicode character in the tombstone encodes the number of subsequent characters making up the ID, and the remainder make up the ID itself.

This question on Stack Exchange has some useful methods for querying an external content type programmatically, and setting the values of the cached external data column. The final piece of the puzzle is to determine the type of the ID argument expected by the Specific Finder method; this can be found as part of the IEntity object you obtain before grabbing the entity instance.

Of course, if you do not want to build your own timer job or do not have the skills in-house to do so, feel free to contact us. We would be happy to work on the project with you.

Learn more about DMC's SharePoint consulting services.