Thursday, 5 March 2009

Filtering SharePoint Fields

It has been a frustration for many SharePoint Administrators over the last two years, but finally there is a solution to the dreaded ‘Secondary Filter’ issue. For Officetalk SharePoint consultants it has been hard to have to break the news to customers that Out of the Box SharePoint cannot handle column filtering. Yes, we can develop systems using SQL tables, but these can prove quite expensive and have to be developed for each list separately. Now, we have the answer in ‘Cascaded Lookup’ a SharePoint add-on from SharePoint Boost which makes it easy to add the function to all lists in just a few minutes. So why is Cascaded Lookup so useful? Perhaps the best way to explain it is with an example. After Villa’s disappointing exit from the UEFA Cup in Moscow last week I am going to use a European Football Players list as an example. Who knows if we do qualify for the Champions League it could be very useful next season. The Challenge then is to be able to add players to a list and be able to select the team they play for without having to scroll through a list of every team in Europe. If we were just to do top flight teams in each European Country we are talking over 500 (including Honka of Finland). I think it would be too long a list to have to read through each time. It gets worse when you include all the other leagues in each country. In England we have four leagues (although don’t get me started on why the old third division is now called the First Division. Twenty years ago I would never have thought I would see Walsall in the First Division). So we need a way to get SharePoint to filter a list of probably over 2000 teams by first Country and then by League. Surprisingly SharePoint lists cannot help much with this challenge. Or the more usual challenge of filtering a list of Cities by first Country and then County. There are lots of other examples such as filtering a list of car models by first car manufacturer (ie Ford) and then car make (ie Fiesta). Or a more IT use could be on an IT Helpdesk where you could filter an office printer by first make of printer (ie HP) and then model of printer (i.e. LaserJet 3000). Hopefully now you can see why so many customers ask Officetalk how to do this. At the end of January I was delighted when my trial version of ‘Cascaded Lookup’ fixed all of these problems at a price that was cheaper than a day’s Consultancy. With one easy install I now had an extra column option available on all my lists called ‘Cascaded Lookup’. With ‘Cascaded Lookup’ I have created a field on my Player entry screen that allowed me to select the player’s team. First there was a filter (taken from a list I had created earlier in Blue Peter fashion) that gave me a dropdown of all the recognised UEFA Countries (even Wales). Depending on the ‘Country’ I selected I would then be taken to a list of all the leagues in that country (again another list I created earlier, I got bored so most countries only have the top flight listed). It is unlikely that the Villa will ever play a team from the Albanian Third Division. Now the last part, (there is a picture below in case you are getting confused) depending on the ‘League’ I selected I would then be taken to a list of teams in that league. So I had filtered my list of over 500 teams (I said I got bored) first by Country and then by Division. As you will see I got a bit carried away and added lots of different ‘Cascaded Lookups’ to my European Player Directory including Position (filtering by Defence, Midfield or Attack), Previous Clubs (filtering by Division), Internationals (filtered by Continent). If ever I get the job of SharePoint Consultant for Aston Villa I will certainly be using this tool. So impressed have I been with ‘Cascaded Lookup’ and the support offered by SharePoint Boost that I have persuaded the Officetalk Sales team to become a reseller of ‘Cascaded Lookup’ so please visit our website or email to find out how you can purchase it. Alternatively you can just ask us to send you a 30 day free trial. Oh, free means free we won’t be asking for any credit card numbers or any personal details such as inside leg measurement. If you do purchase ‘Cascaded Lookup’ from Officetalk and are not a Birmingham City, Manchester United or CSKA Moscow supporter I will even send you my European Player List site Template free of charge. Don’t forget to check out some of the hyperlinks in this blog my favourite is the IT Helpdesk but the Champions League is very funny unless you support Chelsea. Click here to find out more about Cascaded Lookup


Forest Rain Marcia said...

Hi Andy,

Thanks for this post. Reading it made me wonder if you have reviewed KWizCom's SharePoint Cascading Lookup Plus Field Type. A comparison of these two products would be interesting…
You can download and try a full free version at:

Best wishes,
Forest Rain Marcia

jackob said...

hello, I just wanted to say that I really enjoyed your blog and this post.
You make some very informative points. Keep up the great work!
SharePoint Site Branding

digital signature said...

I loved the article. I found it also a couple of days ago. It's a great read.Thanks !