The following article is the continuation of How to extend capabilities of lookup columns.
I wish SharePoint becomes as colourful as the real world…is it possible ?
Did you just think of visual indicators that would make data assessment easier ?
Wow, this is so desired! I’d just love to take a glance at my list and immediately grasp the summary…and I guess most of us also would.
It is intuitive that the visual reference has to be created in a different list that can be referred to from wherever it may have its applicability. Hence let’s call this as Reference List which is a basic custom list containing a title (default) column and an additional Hyperlink or Picture column named Icon, which is our main column of interest while referencing this list.
Now the task is to display the image from the column Icon in main lists, let’s call them Dependent List in the scope of the article. And this Dependent List needs creation of a lookup type column referencing the Reference List.
Are we done…was this all?
Not, so soon. Normally, a simple lookup column referencing another entity limits the scope of available columns from the list.
However, in our case, if we create a lookup column ImageReference in the Dependent List, the marked section in the following screen shot indicates the possible OOTB lookups to our Reference List.
Here’s where the essence of this article lies. The following sections detail the process.
How to extend range of lookup columns?
This is a two-stage process, wherein the first stage consists of tweaking the structure of the Reference List to be able to work with the given limitations. We begin with creating a Single line of text column ImageURL.
The utility of creating this additional column is exploiting the given limitations. Here’s where we start the second stage of our process with creating our first automated action using the SPD. The goal is to pass on the image URL from the Icon column to ImageURL column that can be easily referenced in Dependent List. Since this column is not intended to even show up in the edit form, it should be hidden when editing items. Go to List Settings –> General Settings –> Advanced Settings
Set Allow management of content types as Yes.
On the Settings page, now click on Item under Content Types to set column properties.
Click on ImageURL and make Column Settings as Hidden.
How to automate data transfer between list columns
It would be both cumbersome and painful if one had to fill in the same image URL in multiple columns in the same list. Think of a condition where modifications are made and each change has to be made attentively. To ease the burden, an automated step is created using the SPD.
Create a workflow CopyImageURL with SPD 2010 on the Reference List that is started when an item is created or modified.
The only action that exists in this process is to set the value of column ImageURL from data in column Icon.
So that the final process looks like the one in the following screen shot.
The workflow is now ready to be saved and published. Once published, the first significant change observed in the Reference List is addition of columns ImageURL which is the ‘actual’ column to be looked up on any dependent list, and the CopyImageURL is the workflow status indicator column and can be hidden from the list views.
To see it perform as intended, add data to the list. If the list should be using existing data, to implement our modifications, each list item should be updated since the workflow is set to be triggered either when items are created or changed. This change however can just be a tricking process by open items in edit mode and save them without any significant changes.
A snapshot of the Reference List is provided below:
Now that the ImageURL column is set up as a Single line of text column, this is readily available for lookup in Dependent Lists. Go to the Dependent List and create a Lookup column as indicated in the following screen shot.
We worked though our first limitation to be able to pass on URLs between lists.
How to display images corresponding to looked up URL?
Performing the above step alone however doesn’t solve our purpose. The reason being – we’ve just copied a text type column from a reference list into our main list. It is required that we use the column as a Hyperlink or Picture column as intended. For this, create a column of type Hyperlink or Picture as in the following screen shot.
This column is meant to contain the same image as in our Reference List, however it still doesn’t have the corresponding URL. Here’s where we use the SPD 2010 to trigger an automated action again has to populate this column with the corresponding data for the URL that we fetched into the lookup column.
Create an SPD 2010 workflow on the Dependent List that is started when an item is created or modified, like the DisplayImage workflow in the given example.
What we do in this workflow is to set the Image column such that it displays the corresponding image for the ImageLookup column that we created earlier. Set the data in Image as indicated in the following screen shot.
The workflow finally looks like this:
Having done this, save and publish this workflow. Its impact can be seen when creating or modifying an item.
When editing or adding items, select the required URL in the ImageLookup column and leave the Image column blank.
On doing this, the workflow fills in the Image column with the image from the Reference List corresponding to the URL in ImageLookup column. The workflow status appears in the column with the same name as the workflow (DisplayImage) in the list view.
Hence, our Dependent List looks like the one in the screen shot below which was what we intended.
Since we don’t enter data in the Image column in the edit forms, this column is not even intended to show up there, and hence it should be hidden when editing items. Go to List Settings –> General Settings –> Advanced Settings
Set Allow management of content types as Yes.
On the Settings page, now click on Item under Content Types to set column properties as illustrated below.
Also, since the ImageLookup and DisplayImage columns are not from the ones required in the list views, we can modify list views to hide those columns.
Go out and flaunt the colors!
Look for my other posts in this series:
