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="Supplier"/><Value Type="Text">{SupplierID}</Value></Eq></Where></Query></View>"
to this:
selectcommand="<View><Query><Where><Eq><FieldRef LookupId='TRUE' Name="Supplier"/><Value Type="Text">{SupplierID}</Value></Eq></Where></Query></View>"
Hi,
I tried this using Gregs blog and now yours but can’t get it to work.
Working on a faciities management site, when an Asset is Viewed the view page should show columns in the Asset List, Related Service Requests from the Service Requests list and also related Service Request Tasks from the Service Request Tasks list.
To do this I decided to do away with the Dispform and use DVWP with EasyTabs, so far EasyTabs and DVWP are working great. When it comes to fitering though then I have issues and I can’t figure out what’s wrong.
There are 3 DVWPs, the first one just shows the Asset and the other 2 are joins of Assets-ServReq and Assets-ServReqTsks. I used the following Query to display a unique Asset when selected from the List Form Page for all assets:
ID = (Query String ID).
Using this I get the right Asset for the first DVWP, but when I use this for the DVWP with the joins it never shows anything. Now the parameter I use is for example AssetID and when I search for this value in the code it doesn’t turn up. When I add “Lookup=True” to the code again nothing works, the parent list filters to zero (that is it doesn’t show any of the items) and the child list also shows no items.
What am I doing wrong?
Thanks.
And thanks for your SPServices as well. Especially the Lookup, been a life saver.
Latte:
It’s a little hard to follow what you’re doing; I think you posted some code that WordPress stripped out. Why don’t you post this over at Stump the Panel? The code will “stick” over there.
M.
Thanks. Posted at Stump The Panel with the following topic under the SharePoint for Non-Geeks forum.
Post subject: Filter DVWP based on ID Query
Superb !!!!
Thanks
Bijay
http://www.fewlines4biju.com/2011/05/look-up-column-in-sharepoint-2010.html
Hmmm, very interesting. I have been neglecting imersing myself in the SharePoint community only because……..well, I just have. But this post has directly brought to my attention the high importance of doing so.
Great work mate. I know you received your MVP recently. Well deserved.