Multiple column lookup field for SharePoint

I think I’m not alone missing the possibility in WSS 3.0 to include multiple columns from the target list when creating a lookup or user field in a list.

If you have read my former posts about custom fields and reordering of list fields you already know what you need to create such field types yourself.

In this post I won’t include lengthy code snippets, instead I’ve created a CodePlex project for the source code.

To illustrate the working of the fields I’ve created a custom list called Companies in SharePoint using the following fields:

I’ve added the following items to the list:

Next I’ve created a list called Contracts, and added my custom multi column lookup field (let’s call it master field) as shown on the following figure:

You might notice that the user interface of the field settings is not very sophisticated. You are absolutely right, it is not very error prone to specify the internal names of the related fields (the ones you would like to include into the list additionally to the original lookup field) using a text field separated by a pipe character (‘|’). In this implementation I simply inherited the original FieldEditorUserControl of the lookup field. In forthcoming releases I might have more time to create an advanced one, but in the current phase it was not the most important part of the development.

After adding your new field you can see that only the master field is visible on the Columns section.

But when you check the field order page, you can see that there are additional columns for your related fields as well. It is important that our master field is ordered automatically to the last position, as it must be able to interact with related fields on edit and new list forms.

Furthermore, if you add the master field to the default view on creation, all related fields are automatically added as well.

In the next step I create a new item. Notice, that only the master field is visible on the form.

After adding some additional items I had the following content in the Contracts list:

You can see that related fields are filled automatically.

If you go back to the field settings page of the master field, you can modify the value of the related fields (add or remove fields). Values in columns for added related fields are automatically populated as well, however I should note that this pattern might be not ideal if you have a lot of items in your list or there are multiple parallel editors and enabled check-outs, as it can cause performance issues or conflicts due to checked-out items.

There is an additional field that is very similar but it is for user field.

You can add the custom field to you list as shown here:

And here is a list item having the custom field:

 

Here are the internal name – display name pairs of some common user fields you might be interested in:

Name – Account
EMail – Work e-mail
Department – Department
Title – Name
FirstName – First name
LastName – Last name
WorkPhone – Work phone
Office – Office
UserName – User name

If you would like to enable multiple values for the field, it is important to install the following update on the server:

Description of the Windows SharePoint Services 3.0 Cumulative Update Server hotfix package (Sts-x-none.msp): December 15, 2009

It includes the fix for this issue:

"You develop a custom field type that inherits from the SPFieldLookUp class. You want to store multiple values in a field of that custom field type. Therefore, you set the AllowMultipleValues property to True. However, after you set the AllowMultipleValues property to True, the field type is displayed as Lookup instead of the custom field type."