Second Life of a Hungarian SharePoint Geek

February 15, 2010

Multiple column lookup field for SharePoint

Filed under: Custom fields, SharePoint — Tags: , — Peter Holpar @ 03:23

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:

image

I’ve added the following items to the list:

image

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:

image

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.

image

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.

image

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

image

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

image

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

image

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:

image

And here is a list item having the custom field:

 image

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."

Advertisements

21 Comments »

  1. Do you have a solution already complied? This is what I need but Im not sure where to put the compiled file.

    Can you help

    Comment by Massimo — May 3, 2010 @ 00:45

  2. Hey Peter,

    I was able to install it manually, I also applied the hotfix as well. when I try to use it, it doesn’t seem to work as illustrated. I am only trying to bring in 1 additional column to my list.

    Comment by Massimo — May 3, 2010 @ 17:47

    • Hi Massimo,

      Do you use the internal name of the field? I suggest you to either debug the solution or add trace commands to follow the process and identify the issue.

      Peter

      Comment by pholpar — July 16, 2010 @ 14:21

  3. Hi Peter

    I’ve read http://www.sharethispoint.com/archive/2006/08/07/23.aspx
    At the bottom of the page, there’s indicated:

    “1. Build your assembly and install it in the GAC on the server.
    2. Copy the ascx control to C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\TEMPLATE\CONTROLTEMPLATES
    3. Copy the FLDTYPES_Telephone.xml to C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\TEMPLATE\XML
    4. Reset IIS
    Now when you add fields to a list you should have to option of choosing Phone Number as a field type. Go try it out!”

    What about building the assembly? That is done, not? So it’s not clear what is meant with “installing it”?

    Best regards

    Tom

    Comment by Tom — July 16, 2010 @ 14:11

    • Hi Tom,

      The package contains the built assembly as well. “Installing it” simply means here to copy the assembly to GAC either manually, using Gacutil or through custom code. You should complete steps 2 and 3 using the file .ascx and .xml files included in the package.

      Hope it helps.

      Peter

      Comment by pholpar — July 16, 2010 @ 14:19

  4. Ok thx Peter

    I’ve never done something like that (i’m an IT generalist), and I’m only used to run exe’s or the install wsp’s using stsadm.

    I’ll try to figure out how GACutil works.

    Best regards

    Tom

    Comment by Tom — July 16, 2010 @ 14:32

  5. should be something like
    gacutil /il MultiColumnLookupField.csproj.FileListAbsolute.txt
    ? that’s all?

    Comment by Tom — July 16, 2010 @ 14:36

    • Hi Tom,

      If you have a text file with a list that contains the path to the assembly (e.g. the name of the .dll file) you can use /il. But it might be easier to use the /i option (see http://msdn.microsoft.com/en-us/library/ex0ss12c(VS.80).aspx for detailed help). But it is the simplest to drag and drop the assembly into the Global Assembly Cache (GAC) folder that is typically C:\Windows\assembly.

      Peter

      Comment by pholpar — July 16, 2010 @ 17:28

  6. Hi Peter

    I’ve installed it, but it doesn’t seem to work like it should be.

    Look to this screenshot:
    http://www.sendspace.com/file/9pbxbf

    Any idea Peter?

    Best regards Tom

    Comment by Tom — July 26, 2010 @ 09:57

    • Hi Tom,

      To tell the truth, I’ve tested it only with the US English version of MOSS 2007. Have you tried it using that version either?

      Peter

      Comment by pholpar — August 3, 2010 @ 17:32

  7. Hi Peter

    I’m using the english version of WSS 3.0 with a Dutch language pack.
    I will try to build a VM running this without the language pack and will provide you feedback.
    Of course it would be great this functionality could also work with versions in other languages or languagepacks.

    Best regards

    Comment by Tom — August 5, 2010 @ 17:53

  8. Hi Peter,

    congratulations on your work! Very well done! I am strugling with some limitations of the lookup also, and I’d like your opinion about what I could do.

    I have today three lists with their columns:
    Application (App Name, App Status, Windows Servers, Linux Servers)
    Windows Servers (Server Name, Memory, IP address, etc…)
    Linux Servers (Server Name, Memory, IP address, etc…)

    I have to put all this in two different views that should be something like this:

    First View (Servers view):
    Server Name, Memory, IP address, App Name (show all linked to the server)

    Second View (Application view):
    App Name, Servers (list of all the servers linked to the App)

    I am using a Lookup field with multiselection on the Application list for the Windows Servers and another Lookup field with multiselection for the Linux Servers.

    Note that I only link the server to the application in the application list. The server list doesn’t have a column to refer to the application, instead of it, I have to use the relation of server name to do the list… But I have no idea about how to do it…

    Any ideas?

    Thanks!!

    Comment by Eduardo — October 21, 2010 @ 13:35

  9. Hi Tom, this looks ideal for what i need it for!
    I have tried loading this into visual sudio 2008 and 2010 and neither like it.
    Can i install this straight onto my sharepoint server using the files in the package? if so can you provide instructions for this. I see in your previous posts you mention dragging and dropping into the assembly folder. do i then need to run a STADM command?

    Comment by John — October 4, 2011 @ 15:50

  10. I followed the instructions to install this nice feature: put the dll on GAC, copy the ascx control to controltemplate directory, copy xml to xml direcotory, reset IIS. Well it seams working until I started to put in the new items. It crashed with “Unable to cast object of type ‘System.Int32’ to type ‘System.String’. ”
    it is a 64bits windows 2008 server. Any suggestion???

    [InvalidCastException: Unable to cast object of type ‘System.Int32’ to type ‘System.String’.]
    Company.SharePoint.FieldTypes.MultiColumnLookupFieldControl.SaveHandler(Object sender, EventArgs e) +484
    Microsoft.SharePoint.WebControls.SaveButton.OnBubbleEvent(Object source, EventArgs e) +1906
    System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +70
    System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +29
    System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +29

    Comment by cynthia — October 19, 2011 @ 21:04

    • Remember that this feature was designed for and tested with WSS 3.0 / MOSS 2007. Might it be that one of your fields included in the multi-column field is a numeric one?

      Comment by Peter Holpar — October 19, 2011 @ 21:42

    • yes. I am installing this to MOSS 2007 and all fields I included in the multi-column are “single line of Text”.

      Comment by cynthia — October 20, 2011 @ 13:45

    • Cynthia ….
      To get around this problem I simply change line 77 in the MultiColumnLookupFieldControl.cs class file
      from:
      currentId = Int.Parse((String)Value);
      To:
      currentId = Convert.ToInt16(Value);

      Even though the field values will now save I am now running into some weird behaviour in the list item field edit control. It appears the the template is being showed for the related fields but not the main field.

      Comment by Brett — March 8, 2012 @ 03:29

    • Hi … further to my comment above, the weirdness in the edit form is due to the following:

      Line 59 – MultiColumnLookupFieldControl.cs ….

      Was …
      BaseFieldControl fieldControl = GetFieldControlByName(fieldName);
      fieldControl.Visible = false;
      fieldControl.Parent.Parent.Visible = false;

      Changed to:
      BaseFieldControl fieldControl = GetFieldControlByName(fieldName);
      if (fieldControl != null)
      {
      fieldControl.Visible = false;
      fieldControl.Parent.Parent.Visible = false;
      }

      Basically there was an unhandled null refernce exception. This does not hide the related fields but fixes the isse with the master lookup field in the edit form.

      Comment by Brett — March 8, 2012 @ 04:06

  11. Do any of you realize that IBM Lotus Notes had this absolutely critical feature out-of-the-box, 20 years ago? Yeah, Gates should have $50 billion, sure he should. No, he ought ot be forced to provide some real-world functionality in his garbage.

    Comment by WOTAN — March 23, 2012 @ 02:38

    • I totally agree with you Wotan – trying to solve simple requirements that with IBM Lotus Notes were “out of the box” with SharePoint instead becomes a nightmare. But Gates has the much better marketing – and decision makers in this world are too blind to see the difference

      Comment by StP — July 31, 2012 @ 12:45


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: