Category Archives: Business Connectivity Services

SharePoint list as source for BDC model: how to handle hyperlink and lookup columns

My dear colleague Katharina Schröder recently blogged about using a SharePoint list as a source for an external content type in SharePoint 2010. This is not possible with SharePoint Designer, so she describes how to create a BDC model in Visual Studio 2010 for this scenario. You can find her blog post here: http://kaydee-at-work.blogspot.com/2010/08/use-sharepoint-as-source-for-external.html

But how to handle hyperlink and lookup columns? Both types of column display the actual value SharePoint stores in these columns. For hyperlink columns that is web address, description and for lookup columns that is ID;#Value. The image below shows both a lookup column and a hyperlink column:

image

So, now we would like both columns clickable and for the lookup column we need to find the URL to the appropriate list item too, before we can make it clickable.

Change a column type to a hyperlink

Let’s start with the easy part: change the column type to Hyperlink or Picture instead of Single line of text. For this we need to add a custom property to the TypeDescriptor corresponding to the column we’d like to change. Microsoft has described all available custom properties for Business Data Connectivity services here: http://msdn.microsoft.com/en-us/library/ff394525.aspx. We will use the SPCustomFieldType property.

In your BDC Explorer in Visual Studio select the appropriate TypeDescriptor (WebPage in this example) and open the collection of Custom Properties from the TypeDescriptor Properties pane:

image

Adding an entry with the Property Editor is a bit of a hassle, but it should work if you follow these steps:

  1. Double-click in the Name column of the blank line. The field will be marked with a red border.
  2. Type SPCustomFieldType and hit enter.
  3. Now double-click the Value field of your entry and type URL. Now click outside your property line, click OK and the property is added.

The column is now shown nicely as a hyperlink:

image

There seems to be a caveat though. This property only works for the SpecificFinder methods and not for your Finder method. So you can only add this property to the TypeDescriptor of your ReadItem method and not your ReadList method.

Now let’s see how we can create the URL for a lookup column.

Build the URL for a lookup column

As we have seen above, the value returned for a lookup column is not a valid URL, so we cannot click that to view the list item of the lookup field. The display link of a list item can formatted as <SiteUrl>/_layouts/listform.aspx?PageType=4&ListId=<ListID>&ID=<ItemID>&RootFolder=*. So we are going to need some code to get the values for the <SiteUrl>, the <ListID> and the <ItemID> of the list item the lookup column refers to. Fortunately, the list ID and web ID of the list a lookup column refers to are stored in the column properties.

The code below first checks if a lookup column does not allow multiple values, because then the building of the URL would be more complex. Next it reads the list ID and web ID of the list the lookup column is connected to. Using these properties we can build the URL to display the actual list item the lookup column references. Finally we add the display description, seperated with a comma. Be aware that there needs to be a space between the comma and the display value, or the column value will not be recognized as a SPCustomeFieldType of the type URL.

I hope this will help you with building your own External Content Types using a SharePoint list as a source.