Using the ID Value for a Lookup Column

Lookup columns in SharePoint can be very useful as a way to standardize input values.  One of the drawbacks of Lookup columns is that they violate the Rules of Data Normalization (see my previous post entitled “SharePoint Is Not a Database”).  Specifically, by storing the text value of the lookup rather than the ID, we violate Rule 1: Eliminate Repeating Groups – Make a separate table for each set of related attributes, and give each table a primary key.  While the key exists in the lookup location, the actual value is stored in the item and is generally what folks use in their Data View Web Parts (DVWPs).

There’s a useful post over at the Microsoft SharePoint Designer Team Blog by Greg Chan, Program Manager on the SharePoint Designer team, entitled SharePoint Conference – Building a SharePoint Designer Mashup (Part 1) that shows how to use the ID of the lookup value in DVWPs rather than the text so that you can at least try to play by the rules.  (The post is really about some demos that he did at a Seattle SharePoint Conference, but the trick is embedded in it.)

Lookup columns actually do store the ID of the lookup value (the stored value is of the form ID;#text, e.g., ‘4;#Supplier Name 4’ or ‘1;#Betty’s Golf Shack’), but the ID is normally not exposed.  By using the LookupId attribute in your CAML, you can get at the lookup ID.  You can read Greg’s post for the full details, but here are the key bits.

In your selectcommand (where the CAML is stored) in your DVWP, add the attribute/value pair “LookupId=’TRUE’”.  So, from Greg’s example, to get the Supplier’s ID rather than the text value, your selectcommand needs to change from this:

selectcommand="<View><Query><Where><Eq><FieldRef Name=&quot;Supplier&quot;/><Value Type=&quot;Text&quot;>{SupplierID}</Value></Eq></Where></Query></View>"

to this:

selectcommand="<View><Query><Where><Eq><FieldRef LookupId='TRUE' Name=&quot;Supplier&quot;/><Value Type=&quot;Text&quot;>{SupplierID}</Value></Eq></Where></Query></View>"

“SharePoint Is Not a Database”

This is something that I’ve told people more times than they really want to hear.  Sure, SharePoint sits on top of SQL Server, which is an excellent database, but SharePoint itself is not a database!  So is this a dire “abandon all hope, ye who enter here” message?  Absolutely not.  With planning and forethought, you can build quite nice applications in SharePoint that act in a database-like way.  What it does mean is that all of the old rules still apply.

When I think about this, I’m always reminded of the Rules of Data Normalization poster from Database Programming & Design magazine by Marc Rettig which was hanging in one of my colleague’s cubes in the late 1980s.  (Through the miracle of the Web, I was actually able to find this picture of it!)  Though I can’t recite the rules from memory, it has informed my database and now custom list designs to this day. 

  1. Eliminate repeating groups
  2. Eliminate redundant data
  3. Eliminate columns not dependent on key
  4. Isolate independent multiple relationships
  5. Isolate semantically related multiple relationships

I’m not going to go into the details of what each of these rules means (a quick search on the Web or an introductory Computer Science course will explain them to you).  These rules and the nice 80s colors are cool, but what does this mean when you develop SharePoint applications today?  Well, quite a few things.

  1. First and foremost, make sure that you build your lists following these rules.  One key flaw in SharePoint’s architecture (it’s there for good reasons having to do with Office compatibility) that forces violation of the rules is the Lookup Field Type.  Rather than storing the ID of the referenced item, the text value is stored.  Try to avoid using the Lookup Field Type directly and…
  2. Build custom forms to replace Newform.aspx and EditForm.aspx that enforce these rules.  The default forms contain a single List View Web Part, which works very well in creating and editing items in a standalone list.  When you want lists to interrelate, you’ll want to build custom forms.  These custom forms can contain multiple Data View Web Parts (DVWPs).  A DVWP used as an input form can only interact with a single list, but you can have the DVWPs interact using hidden columns and JavaScript.  (Yes, you can write custom Web Parts here, and I’m not discouraging that, but in many cases managed code overcomplicates things.)Rather than using Lookup Field Types, you can display the available values from the lookup list(s) and capture the IDs in selects or with radio buttons, etc. which you build yourself in DVWPs.  With JavaScript on the events, you can then “stuff” the chosen values into the appropriate columns in the target list.Several notes on custom forms:
    * Don’t edit the default forms directly — take a copy and change the copy.  You never know when you might want to revert to the original form.
    * Change the forms associated with the list by right clicking on the list container in SharePoint Designer, Properties, Supporting Files tab.  Making the change here will cause a redirect to your custom form even in places that the original form is requested.
  3. Handle deletes elegantly.  There is no hook for workflows in SharePoint Designer to trap on a delete.  However, if you build your forms well, you can provide delete capabilities that you can trap on and handle.  There’s no such thing as cascading deletes in SharePoint lists (it’s not a database, remember?), so you’ll need to do the work yourself.  If you’ve followed the data normalization rules well, this should not be too onerous a set of tasks.  One simple way to protect yourself is to just disallow deletes in lookup lists.  This means that you will need to provide some administrative function to handle them, but you will avoid simple breaks to the application.  Be sure to…
  4. Teach your users the value of all of this.  If you build your forms well, the amount of education ought to be minimal, but you may want to explain your methods so that they understand how it all holds together.  Why?  Well, if they don’t understand, they may try to do things to get around your good planning, which will make it all fall apart.

I’ve written many posts in the past that have snippets of how to do each of these things, so if you have questions about any of the details, take a troll back through my old posts or shoot me your questions.