If you're like me, you can't help thinking about SharePoint lists as if they were full fledged relational databases, sadly they are not. Still, my experience in database management compels me to organize unique data sets into separate lists and "relate them" using Lookup Columns. The problem for end users like me is that SharePoint doesn't always reward our elegant data architecture. Leveraging the relationships between lists to create the most useful output and displays can be difficult, sometimes even requiring development.
Let's say that in my Team Site, I have a list of active projects, a list of team members, and an additional list of cost centers that must be associated with each project. In setting up my site, I have elegantly leveraged lookup columns so that when I add a new project to my project list, I can simply choose the lead project manager from a list of all potential team members, and assign a cost center based on a centralized list of department codes.
However, if I wanted to create a report that lists all active projects including the name of the lead project manager and their email address, I'm kind of stuck. The email address for my project managers is in a separate contacts lists, not my project list.
Similarly if I wanted to create a list of projects sorted by cost center, and I want to display any extended data about that cost center (e.g. the "friendly name" of a particular department, not some unrecognizable numeric code), same problem... that extended data in a related list isn't easily accessible to me.
Bamboo's Cross List Web Part was designed specifically to solve this problem. This Web Part allows you to construct a data view for a given list that includes data from any columns in related lists connected as Lookup columns. Whew, that's a mouthful. But many of you out there must encounter this difficulty because the Cross List Web Part is just outside the top ten list of Bamboo all time best sellers.
The following screenshot is a simple example of the output you can create, pulling data points from columns across three different lists. Note that you can instantly sort this display by clicking on any column header. You may optionally add a Print or Export button to the display. [NOTE: It occurs to me that in a pinch, this Web Part could be used to combine two related lists... simply create the combined data view, export to Excel and re-import as a new list. Crude but effective.]
Ok, so does Cross List Web Part truly deliver relational database capabilities for SharePoint lists? Well, that's probably a bit of a stretch. But it does allow you to leverage the relationships between lists, and create superior reports and displays based on these relationships. It's certainly more than we get from SharePoint out-of-the-box.
Tips on Configuring Cross List Web Part
1. Don't forget that Cross List Web Part can be used to "merge" data from up to two related lists. It may not be immediately obvious that after adding columns from a Lookup List that you can then choose a second Lookup List and add columns from that list as well.
2. By default, Cross List Web Part applies a column header to your display that takes the format of "List.ColumnName" As a column header, this text is likely too long and not extremely readable. To edit these column headers, click on the "Source Edit" button in the Web Part tool pane.
3. Dress it up with CSS. You can create an infinite variety of looks for your output with just a few tweaks to CSS. Apply shading, borders, etc. for maximum legibility.
As always, please remember that you can download a free trial version of Cross List Web Part (or any other Bamboo product). Play with it just a little, and I think you'll find this to be a handy tool you can put to use in many ways across your SharePoint portal. Cross List Web Part is also one of the components of the Bamboo Project Management Suite.
Mar 11 2009, 01:15 PM
My name is Steve Gaitten, I am Director of Online Operations at Bamboo. My primary mission is to make Bamboo Nation the most useful SharePoint community site on the web. I am also focused on ensuring a world class shopping experience for customers who visit the Bamboo Solutions Online Store. Prior to Bamboo, I spent over a decade at America Online. At AOL my most recent roles included Director of Product Management in the Messaging & Social Media division as well as Managing Editor of AOL Money & Finance. I am a patented inventor, a bad golfer, an enthusiastic horticulturalist and a dog lover.