SharePoint: Filtering with Web Part Connections
Author: Kerri Abraham
Have you ever noticed the ‘Connections’ option on web parts and wondered what it was meant to do? Have you played with it a bit, tried connecting one thing to another only to have nothing happen? Did you do a web search to try to figure it out and come back with results like “Connection Interface Pair: ICellProvider, ICellConsumer “ and then give up, just to assume that was something you needed Sharepoint Designer to master (and you don’t have Sharepoint Designer?) Welcome to the club!
Turns out creating a ‘connection interface’ between web parts is really simple; the hardest part is visualizing the purpose! It seems to me the best way to get the idea across is just to throw a few examples out there, run through the basics and hopefully that will be enough to get your wheels rolling with the concept.
Below is an example showing an abbreviated view of a list of stats we collect to monitor productivity:
This list actually deals with a number of issues I see people struggle with on Stump the Panel, so I’ll walk you through the capture of the information, just in case it helps to explain some other questions that might arise.
Here is what the New Item entry looks like:
It is best practice to never delete the Title column, so when it really isn’t needed, I just set the default to a generic value, in this case “Today’s Totals” since it has information in it, no one messes with changing it, and it wouldn’t matter if they did. The date fields all use another default based on a calculated value in a Date and Time column:
Note the use of ‘Today’ in the default value. Investigation into using [Today] in a calculated column might make the end-user leery of using ‘Today’ at all. Rest assured though, capturing today’s date as a default value is stable, since it will not change (to a new ‘today’s date’) even if the list item is later edited. Using default settings encourages end-user adoption because it makes filling out the information much quicker (and generally more accurate!) Using another calculated column, I capture how many days between dates and that looks something like this:
=[Scanning Date]-[Date Entered]
[Scanning Date] is the default of ‘Today’, [Date Entered] is the date we are capturing with a default of T-1, but it may be edited to hold other dates when holidays need to be factored in.
The other fields are quickly completed by entering the number of folders they need to record for the day. Terrific, easy to fill out form, now we are capturing data, looks good!
Then management asks for monthly average reports on those folder numbers.
Filter to just a month? Of course 12 different views of the list can be created, each filtering on the name of the month. No matter how we go about it, first is to create a column that returns the name of the month. Using a calculated column, that is pretty easy to sort out and looks something like this:
=TEXT([Scanning Date],"MMMM")
This formula converts the date entry into text. Using MMM will result in abbreviated month names, MMMM gives the text name in full.
Sharepoint handles averages with the aid of ‘Totals’ that can be created under Modify/Create View. Notice that some of the columns listed below will only support averages in datasheet view. That is because they were built on calculated columns. Web part connections also work in datasheet format but all connected web parts must be displayed as Datasheet.
Here is where it gets interesting. Create a second custom list called “Filter by Month.” Edit the list in Datasheet format to quickly enter the text names of the 12 months.
Below I’ve added both lists on a web part page. It will work on the default list page as well; just add the second list above the first.
In order to see ‘Connections’, the page must be in Edit Mode, so either Modify Web Part, or choose to Edit the Page. Choose Connections > Provide Row To. ‘Get Sort/Filter From’ is the other option and it works much the same way as I describe here only in reverse.
There are only 2 lists here, so the only option of making a connection is the Scanning Stats list. The need is to sort by month, which is the Title column, so use the Webpage Dialog and pick Title.
Next pick the column that contains the same data values as the first column, which is the Months column that lists out the full text names of the months. Both columns must be visible on the screen in order to create the connection.
Click Next (or Finish) and check out the Filter by Month list:
Choose the month to filter on by clicking within the circle.
Notice the filter icon showing above the Months column; it is pretty obvious what is happening there, just as if we had filtered using the drop down options at the top of every column.
Very cool, but there are few oddities I’ve discovered. First of all, the list can no longer be changed to Datasheet format, so if that is an option regularly accessed, consider building the connection with a datasheet to start with. Notice in the example below, connecting the same web parts as above but with datasheet views, that the circles to choose are no longer there. In datasheet views just click the column entry to filter. Also take note that the averages of the calculated columns are also present.
These datasheet views with web part connections open up endless filtering options. Datasheet views allow instant entry creation, so any data can be instantly entered in the Filter list and then click it to filter the list on that value.
Another odd thing: unless we navigate away from the page and re-enter, there seems to be no easy way to go back to an unfiltered view of the list. The URL holds the secret of what is up with that. I have found that providing a link to the .aspx page is all that is needed. How? Grab the URL from the page where the connections are placed prior to initiating any filtering. Add a Content Editor Web Part and add a title in text that indicates what clicking there might do. Add a hyperlink to the text using the URL you just copied and hide the title of the web part (to clean up the appearance.) I added the link below the filtering options:
One other discovery: I got stuck in a loop of never ending error messages when I tried to use the filter column in Preview Pane view. It works fine if the list holding the data is in preview pane, so keep that in mind.
You don’t need to use a separate list to create web part connections.
Maybe you need to display a most recent date. In this example I’ve used a variation of the month calculated column to return the most recent month and year. I called it Month, it looks like this:
=TEXT([Scanning Date],"yyyy-mm (MMM)")
This forces the year display so we can be sure of the most recent year.
This time add two of the same web parts to the page. I’ve modify the view to ‘group’ the Month column, this makes the most sense for my entries. It is possible to do the same thing with ‘modified’ if you want to group by a day. I’ve restricted the results coming back on my column I plan to filter with these settings:
Making sure I have the two ‘Month’ columns both displaying in each list, I create the connection between them. Because I have the filtering web part set on 1 group to return, those are the only results appearing in that filtering column and users can pick the specific date by expanding the grouped items. Filtering on recent date has proven a real challenge to me in the past without the use of Sharepoint Designer.
Connect multiple web parts and filter them on a common value.
Lastly, one more example and the origins of this connections discovery: my project management template. If you haven’t yet been turned on to Dux Raymond Sy, check out his project management resources on his blog MeetDux. Dux is passionate about getting the word out on using Sharepoint for Project Management, he spoke, I listened, and every one took notice.
Fourteen of the web parts shown in the picture to the left represent the lists from my original project management template and detail requirements capture for our PM process. All that was needed was to create a site based o the template that included these lists and some base content. Management was thrilled with the results. So thrilled, it turns out, that they (and the project managers) wanted to use the same method for smaller projects. I have to admit, I had been stumped as to how to do that (again completely out of the box) until it occurred to me how to use web part connections to get the job done.
The first web part here is the key. I created a ‘Project Name’ list. This list captures the project name, the project lead, and a yes or no column asking if the project is to be archived. The archive column allows me an easy way to filter projects off the list when they are no longer active.
Then I needed to create a site column which references this Project Name list as a lookup column.
From there I added the new site column to every one of the original 14 lists. Adding all the web parts to a web part page, making sure the new column was visible in each, and then creating the connections to each was all that was needed.
But imagine scrolling through that mess, even with connected web part filtering you might be thinking that can’t possibly be enjoyable for the user. Christophe from Path to Sharepoint provides the ideal solution with Easy Tabs; and they work perfectly with web part connections! Someone recently said via Twitter “If you aren’t using Easy Tabs, you are missing out!” I totally agree, best feature I can think of to expand my Sharepoint solutions.
The end result: (click to see full size)
Users can filter by project and click through the tabs to see only the information that pertains to that project. Now we have nearly the same project management solution for smaller related projects as we do for our larger ones. This in turn increases site usability by keeping all the elements in a familiar place and it eliminates rework since information for one related project can be assigned to another project by simply adding that project name to content.
Hopefully these examples will get your juices flowing on ideas for using these odd little connections. Tell me about how you are using them; I’d love to hear from you. Leave a comment!
Author: Kerri Abraham
Kerri Abraham is the Revenue Cycle Sharepoint Coordinator at Mercy Medical Center in Cedar Rapids, Iowa. Converting Sharepoint enthusiasts one information worker at a time, she is leading the charge to win end-user adoption through the creation of extensive training materials and knowledge capture. Introduced to Sharepoint in 2006, but holding a dedicated Sharepoint support role since 2008, and brand new to tools like Sharepoint Designer, she focuses entirely on out-of-the-box solutions.
Great stuff, Kerri!
Good job.
This is fantastic. So elegant, and so clearly presented.
More, please.
Holy smoke!
Great article, well layed out and well written. And I never knew you could use the today value in default date settings!
Good Stuff! Appreciate the shoutout
Love the solution. In SharePoint 2010 it seems that you don’t have to have the field visible in both lists, which is a nice improvement, but on the other hand they replaced the radio buttons for a strange double arrow that almost doesn’t make sense.
Did you find a way to change it back to a radio button? I am seeing the same strange double headed arrow and am wondering how to get rid of it. Thank you.
I do feel I have to mention one last discovery and perhaps a small edit (depending on how you view the discovery!) When I wrote the article I had not yet made a template from the project management site I describe, and I hadn’t actually created a new site from the template. No worries, the templating worked perfectly and everything stays filtered and connected as intended. However I’ve discovered that because I used a site column to create the lookup of projects, that the templated site column only seems to reference from the original projects list that serves as the lookup. So now all projects entered on the original project list filter down to lower sites created from the template. Very interesting and actually is the perfect solution to the project needs I was addressing. however this may not be the perfect solution for our future needs. I think I will be adjusting the template one more time and taking out the site column and creating a regular lookup column in its place so it references projects from the site itself, and not the master site list of the parent. For now, I’m going with it, since it happens to perfectly work with my most recent project requirements (and has me very curious about how I might use this discovery in other ways!) I’ll be sure to keep you all posted if anyting of interest comes from it.
And thanks to all of you leaving your kind comments. I’m so glad you found it helpful!
Hello Kerri,
Thanks very much for this article. I’ve worked a bit with connecting Web Parts before during this endless Sharepoint learning phase I seem to be in, but never really connected it to a real application.
The last part especially, using the Project template from Dux’s book and Easy Tabs, was perfect. I’ve set up the project template site a couple of times now, and have been trying to promote using Sharepoint for projects. When I added your setup to the site and showed it to one of my managers, very impressed. Hopefully another step forward. Over the next week or so I’ll try and finish a step-by-step on how to do it.
I also want to try to extend it a bit. We just did SharePoint Saturday Baltimore, both Dux and Mark were there by the way, and that always gets me thinking. We have a doc library that we need to filter and secure at the Folder level. I never want to promote folders, but then just last week I learned about Folder Content Types. I think the combination of your technique here and the Content Types, we may have something.
Good ideas coming from sharing better ideas….what a concept.
Here’s my twitter, @alduns. If you DM me an address, I’ll send you the outcome and documentation I come up with. May help someone else.
Thanks again,
al
Al, I have to say – You have inspired me right back! I’ve read about creating folder content types in the past, but since I have this aversion to folders, I’ve stayed away from them and relied entirely on metadata. Now I’m being asked to direct the URL in a high profile library to a specific group of policies, and I can do that with folders. So thanks for the inspiration!
And I see you found me on Twitter @ListMakerEUSP, so do keep updated! Thanks! ~Kerri
I’ve blogged about why you CAN use Today in calculated default values along with some examples of what you could do with this.
http://blog.pentalogic.net/2010/09/today-sharepoint-calculated-default-values/
Nice post Ryan. I saw it right away via Twitter and thought how timely to my article. Thanks for linking it in here. Another great resource for readers.
Great work, Kerri. Love the article! It will take me days to digest it all and try out everything in it. However, I’m curious as to why you left the unused title field in the default view of the Scanning Stats list, instead of replacing it with a simple edit icon. Just to save time?
Hi Keith. Honestly didn’t even notice, it was all a mock up. I have a few lists where I use the edit icon but my end-users are programed to look for those blue Title words to find their drop down menu, and I typically keep them in column 1 or 2 so it is a matter of consistency int the build, consistency for my end user experience. I rarely have to default value the title column to be truthful, it just doesn’t come up that often.
This was very timely for me and easy to follow. One question, is there a way to set a default value in the provider connection (month in your first example)? When the user enters the page, no provider value is set and the consumer web part consequently displays all of the items (scanning stats in your first example.
I am running MOSS 2007 and have access to SharePoint Designer but I am not a programmer.
Thanks.
Kim, Another thought. You could potentially manipulate how people get to the page and make it appear to have a default value. Since the URL holds the query string, providing a hyperlink with the filter in place could give the appearance of default, but that result is going to be provider value specific.
Many thanks, Kerri.
What an enlightening article! I always get so much from your practical guides, and you just connected up a few puzzle parts for me, combining some great SharePoint tools.
I already use Dux’s excellent SharePoint for Project Management, to collate all the information; and separately in scheduling applications, I use Christophe’s Easy Tabs along with the Connections feature as a powerful focus on the data that I need to highlight. I am looking to combine the two, and your solution is just what I need.
I have a couple of queries on your PM template:
How do you deal with large and small projects in the same lists? I have treated them separately, with small jobs recorded and tracked in lists with only the necessary detail, to save on PM effort, and full projects each given their own sub-site with all the usual PM lists. Is it just a case of using only the lists relevant to the complexity of the project?
Can you easily handle a number of complex projects within one site? It would be great for reporting, but do you get problems with archiving completed projects, or giving limited access to occasional users. I use this on BPOS, and wish to extend limited access to external users.
Allan Bury
@interezia
Hi Allan. Thanks for your nice comments! As for the project management template, we are currently only combining multiple related projects on one site. To say only smaller related projects is not entirely true either. Some projects are very complex, but because one project manager runs them and the tasks in each are very closely related, this set up works well for him. Another instance is a project with a huge time crunch, only a few months to complete, immovable deadline, but the project affects multiple departments, some with large, some with smaller project needs. With this template we can track them all and keep a strict eye on where we are as that deadline hurdles towards us. It is that thread of similarity that ties them to one site, which then also ties all the planning material together too, so when that information is all similar, the project managers work together to fill in the information as they require it; they only use the web parts they need at the time (all that planning information is completed at kickoff with the group). I included an ‘Archive’ column for every list on the multiple project site template, but I haven’t yet automated that process, as long as it is there I have the option with minimal work, when I get that far. I hope that helps.
Thank you, Kerri.
That will work for me. Identifying similarities will reduce the number of sites to a manageable level.
I appreciate your help.
Hi Kerri,
Can we give a title to the radio button column that appears once the connection is established between two lists?
Thanks
Pradeepti
Pradeepti, Sure, just edit the Appearance of the web part under Modify Shared Web Part and give it any Title you want. If you need more instruction for your end user, add a content editor web part. ~Kerri
I have 2 lists Employee and Project. Employee list contain empno,empname,address.
Project list contain project no,empno,project name.
I want create a search option. ie when giving empno, I want to get the empname,address,project name. Is it possible?
Hi ams. Try using a Form Web Part. If you have both Employee and Project lists on the page, both with employee number included in each view, you can connect the Form Web Part to the two lists, then use the field in the Forms web part to filter on employee number. The Form web part allows you a free text entry, but don’t be decieved, it really is not searching the list, it is filtering, so values have to match exactly in order for it to work.
Hi Kim, these web part connections are quirky little options. There are a number of limitations as you can see. Web part connections ultimately provide another filtering option, so you have to be thoughtful/creative with filtering/sorting. Take the monthly view in the example used above, we need to display no more than 30 values, so I limit the results to 30 (figuring in that weekends there are no values returned 30 is plenty for this list) then we can filter on month and be sure that we are seeing all the month’s values, but when the page opens, prior to a filter being used, the list will display the last 30 results, which could span more than one month. That’s the quirky part. A data view web part is certainly a more refined method for filtering, but if like so many out there, Sharepoint Designer isn’t in your tool box, these web part connections can help you accomplish a few extra filtering tricks.