Case Studies
All Categories

SharePoint Timer Job to Automatically Refresh External Data Column Values

SharePoint Timer Job
Posted in Digital Workplace Solutions, SharePoint

Summary

DMC created a SharePoint 2010 list and timer job to periodically refresh the values stored in external data columns from the source external content type.

The SharePoint timer job allowed the Client to keep external data columns up-to-date as information in the data source changed and configure the frequency of such updates through SharePoint 2010 Central Administration, while the list allowed the Client to add or remove external data columns to be refreshed.

Solution

The SharePoint 2010 farm-based solution contained three features: the custom timer job, the configuration list, and a property associated with a web application that pointed the timer job to the location of the configuration list. Both the timer job and the property were scoped for a web application, while the configuration list was scoped for a site. When run, the timer job would check the property for the location of the configuration list, then load that list's items and update the corresponding external data column. As long as the property pointing to the configuration list was not set, or the configuration list contained no items, the timer job would do nothing. Deploying the feature containing the configuration list, which controlled all external data columns within its web application, the property would be populated by a feature receiver with the location of the configuration list. The configuration list contained two columns, one a hyperlink column pointing to the location of the list containing the external data column, and the other a text column containing the internal name of the external data column to be refreshed.

The timer job, upon loading an item from the configuration list, would load the list containing the external data column as well as the external data column itself. It would then iterate through all items in the list, querying the external data type associated with the external data column via the Business Connectivity Services (BCS) API for the current value of the external item. If the value in the external item differed from that in the external data column item, then the external data column item was updated. Note that every external data column item contains a hidden associated column containing the encoded BCS ID for that item. This ID was decoded by the timer job, and never updated. The decoded ID was used to query BCS one item at a time for the values associated with that column. The timer job also supports updating dependent external data columns brought in when adding or changing the external data column. The external data column as well as the dependent columns are simply text columns containing the values from the external content type, and these values were overwritten by the timer job.

When only updating those external data columns and their dependents that differed from the external data source, the run time for this timer job for a list containing hundreds of external data items was on the order of seconds. If the frequency of the backend data changing were increased, this runtime would increase as well, but for the Client's purposes the solution offered a lightweight way to update external data columns that were changed at a frequency of several a day. The Client chose to run the timer job every hour, but could have updated the external data more frequently had they chosen to do so.

Learn more about DMC's SharePoint Consulting services.

Customer Benefits

The Client had a need to use data stored in a third-party database within SharePoint, and to integrate this information within custom SharePoint lists. External data columns are the obvious choice for storing this data. The Client also needed to keep the external data current with what was stored in the third-party database as information within that database changed.

For example, the custom list containing the external data column contained views which would group items according to status stored in a dependent external data column. As the status of the items changed in the third-party database, the SharePoint list item was required to move from one status group to another without user interaction.

Instead of requiring staff to manually refresh the external data columns, the Client asked DMC to build a SharePoint 2010 timer job that would automatically refresh a set of external data columns according to an adjustable schedule. After successful delivery of the solution, the Client was able to define which external columns to refresh by adding or removing items from a configuration list and to control the schedule of the refresh job through the Central Administration timer job definitions, saving time for staff to work on more productive tasks.

Technologies