A few months ago, Klout suggested I knew something about SharePoint. I laughed at the time but the last few weeks have seen me spending far more time working on a couple of SharePoint-based systems than I would like, so maybe Klout is less of a measure of social influence than one of future gazing…

…anyway, back to the point.

SharePoint lists have the ability to include columns that are lookups from other lists. That’s really useful when building a system with several lists of related items, for example Technologies and Vendors. I use a lookup on Name from the Vendor list to populate the available entries for a Technology item’s Product Vendor column.  That’s all fine but I also have Product Family, Product Name and Product Version columns. And then there is a Similar Products column – for which Product Name is not a clear enough lookup – I need a combination of Product Vendor, Product Family (where present), Product Name and Product Version – For example a concatenation of Microsoft + Office + SharePoint Server + 2007.

Creating a calculated column (Full Product Name) for the “in-list” (Product Family, Product Name, Product Version) is straightforward but certain column types (Lookup, Person, Group) are not available for calculated columns. I believe this is because they are based on internal SharePoint IDs, rather than the information displayed.

The workaround seems to be to create an additional text column and a workflow in SharePoint Designer that sets a value in this field.

The workflow (which I named Set Product Vendor) is a basic workflow with one step. That step has no conditions (i.e. it applies to all items) and an Update List Item action which updates the Current Item to set my new column (Product Vendor Plain Text) to the Technologies:Product Vendor value (using the fx button to select).

Now, when creating a new item the lookup is used to select an existing Product Vendor but the plain text version of the field can then be used for calculations, like the Full Product Name. The formula for my Full Product Name column is:

=IF([Product Family]=””,[Product Vendor Plain Text]&” “,[Product Vendor Plain Text]&” “&[Product Family]&” “)&[Product Name]&” (version: “&[Product Version]&”)”

This is used to create a single line of text.

The logic here is that a Product Family value might be empty. If it is, then I take the Product Vendor Plain Text value and add a space, if it’s not I take the Product Vendor Plain Text Value and the Product Family, adding spaces to separate, then (regardless of the previous condition), I add the Product Name and the Product Version (with some additional text around it).

Unfortunately, whilst the workflow can be triggered manually for an item (which is what I did to test it), or on item creation/update, there is no way to run the workflow on all of the existing items in a list.  But, like so many things in SharePoint, this also has a workaround. By viewing all items in SharePoint datasheet view and making a minor change to each one (for example adding an entry to a temporary column that can then be removed), this will update the item, triggering the workflow.

The bonus of all of this is that I have now updated my Similar Products column to be a lookup on Full Product Name instead of Product Name and, because it works on the item IDs, the entries are now correct, with no data cleansing required on my part.