How to be in sync with Lookup when it changes
One of the biggest puzles of inconsistency is keeping a page that using Lookup column uptodate to all the changes that happened to underlying list. Image you have a web page in a Documents library. That page has a column say Type which is a lookup to a list Types below.
| ID | Type |
| 1 | Document |
| 2 | Article |
| 3 | Podcast |
Imagine you’ve chosen a Podcast for a type. Then, later on you’ve changed the “Podacst” value in theTypes to “Webcast”. The problem is that you page still thinks that its type is “Podcast”. Furthermore, the pagae isn’t updated speaking of its LastDateModfied property. The incremental crawler will not even include the page, and full crawl will extract the same “Podcast” value.
| ID | Type |
| 1 | Document |
| 2 | Article |
| 3 | Webcast |
if you dig deep enough you’ll see that it’s a problem with the ItemFieldValue implementation of the lookup field. When the value changed, it is persisted on the Field level but not persisted in the ListItem scope. Knowing this helps to find a way out.
So, how do you make sure that the search gets the correct values for lookups. Obviously, we don’t want to browse all the pages to update them.
Here is the elegant solutiuon. Have a batch or custom action on the list that updates the list itself. You’ll have to use SystemUpdate() on the list item to avoid LastModified stamp changed.
Here is the whole class that makes the job. Enjoy!
using System;
using System.Collections;
using System.Collections.Specialized;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Reflection;
using System.Linq;
using System.Text;
using System.Globalization;
using System.Web.UI;
using System.Web.UI.WebControls;
using Microsoft.SharePoint.WebControls;
using Microsoft.SharePoint;
using Microsoft.SharePoint.Utilities;
namespace LookupUpdater
{
/// <summary>
/// Utility serves the purpose of updating the SharePoint list or library to refresh all Lookups .
/// </summary>
/// <owner alias="leonidly"></owner>
internal sealed class MetatagHelper
{
#region Methods
/// <summary>
/// Updates the list checking all list items and all fields
/// </summary>
/// <param name="siteUrl">Url to the site</param>
/// <param name="listName">Name of the list or library</param>
public static void Update(string siteUrl, string listName)
{
WriteMessage("Updating Lookup values in {0} at {1} ...", listName, siteUrl);
try
{
using (SPSite rootSite = new SPSite(siteUrl))
{
Uri uri = new Uri(siteUrl);
using (SPWeb web = rootSite.OpenWeb(uri.AbsolutePath, false))
{
SPList targetList = web.Lists[listName];
if (targetList.ItemCount > 0)
{
bool listUpdated = false; // flag to update the list / library
foreach (SPField field in targetList.Fields)
{
SPFieldLookup fieldLookup = field as SPFieldLookup;
if (fieldLookup == null) continue;
if (fieldLookup.LookupField == null) continue; // no User lookups
if (fieldLookup.ReadOnlyField) continue;
if (fieldLookup.Hidden) continue;
WriteMessage("Processing field {0}", field.Title);
string internalName = fieldLookup.InternalName;
foreach (SPListItem item in targetList.Items) // looping all
{
if (item[internalName] is SPFieldLookupValueCollection)
{
SPFieldLookupValueCollection itemValues = item[internalName] as SPFieldLookupValueCollection;
bool itemChanged = false;
SPFieldLookupValueCollection colValues = new SPFieldLookupValueCollection();
foreach (SPFieldLookupValue itemValue in itemValues)
{
int lookupId = itemValue.LookupId;
string lookupValueSaved = itemValue.LookupValue;
string lookupValueCurrent = GetLookupValue(rootSite, fieldLookup, lookupId);
if (string.Compare(lookupValueSaved, lookupValueCurrent, CultureInfo.CurrentCulture, CompareOptions.None) > 0)
{
colValues.Add(itemValue);
item[internalName] = lookupValueCurrent;
itemChanged = true; // and flag list to update item
}
}
if (itemChanged)
{
item[internalName] = colValues;
item.SystemUpdate(); // updating the SPListItem
listUpdated = true; // and flag list to update list
WriteMessage("SPListItem {0} updated <multiple values>", item.Url);
}
}
else if (item[internalName] is String && item[internalName].ToString().Contains(SPFieldMultiChoiceValue.Delimiter))
{
SPFieldLookupValue itemValue = new SPFieldLookupValue(item[internalName].ToString());
int lookupId = itemValue.LookupId;
string lookupValueSaved = itemValue.LookupValue;
string lookupValueCurrent = GetLookupValue(rootSite, fieldLookup, lookupId);
if (string.Compare(lookupValueSaved, lookupValueCurrent, CultureInfo.CurrentCulture, CompareOptions.None) > 0)
{
item[internalName] = lookupValueCurrent;
item.SystemUpdate(); // updating the SPListItem
listUpdated = true; // and flag list to update
WriteMessage("SPListItem {0} updated", item.Url);
}
else continue;
}
}
}
if (listUpdated) // final touch: change the list LastModified date so crawler picks it up.
{
targetList.Update();
}
}
}
}
}
catch (Exception ex)
{
WriteMessage(ex.Message);
}
}
#endregion
#region Utility
/// <summary>
/// Returns lookup value for a given field and item
/// </summary>
/// <param name="site">SPSite</param>
/// <param name="field">SPFieldLookup</param>
/// <param name="id">int</param>
/// <returns>string</returns>
private static string GetLookupValue(SPSite site, SPFieldLookup field, int id)
{
using (SPWeb lookupWeb = site.OpenWeb(field.LookupWebId))
{
SPList lookupList = lookupWeb.Lists[new Guid(field.LookupList)];
SPField lookupField = lookupList.Fields[field.LookupField];
SPListItem lookupItem = lookupList.GetItemById(id);
object result = lookupItem[field.LookupField];
return (result != null) ? result.ToString() : null;
}
}
/// <summary>
/// Wrapper for logging messages
/// </summary>
/// <param name="msg"></param>
/// <param name="args"></param>
internal static void WriteMessage(string msg, params object[] args)
{
System.Console.WriteLine(msg, args);
}
#endregion
}
}