Filter a List based on Current Site’s URL

There have been a few situations where I’ve needed to be able to filter a web part to show data relevant to the name of the current site.    Most cases have been project sites.  Here’s an example: (This post is relevant to SharePoint 2007 or 2010)

All of a company’s projects not only have a project detail record which contains information about the project budget, dates, etc.  Also, each project has its own sub-site to a top level site called projects/
Since there needs to be one big list of projects that can be filtered, sorted and reported on, this “Project Details” list exists as just one list at the top level “Projects” site.  This list contains a hyperlink field called “Link To Project Site”, which contains the link to that project’s sub-site.  The tricky part is that the “Project Detail” one record about each project needs to be displayed on each project site AND this project site needs to be saved as a template and easily reused.

This dilemma is one that I spent a long time trying to figure out years ago, back in SharePoint 2003, and had never figured out.  My solution ended up being that each time I created a project site, I had to manually go into a data view web part on that site and set the filter to whatever the project name was.

Here are the steps to build a solution that allows you to show the detail record for one project on the home page of that project’s sub-site.

1. Create a list on the top level “Projects” site called “Project Details”.  This custom list will have all of the pertinent project related fields, such as begin date, end date, current budget, budget goal, project manager, etc.  It’s important that you create a hyperlink field called “Link to Project Site” also.
2. Create a sub-site for one project.  Usually these project sites are used to store all of the project related documents, calendars, tasks, etc.  Access to these sites is given to the people who are on that project team.  This is a graphic that gives an example of what the end goal is.
   
3. On the “Projects” top level site, create a web part page.  This will be used as a temporary location for the creation of the web part that will display the one project detail record related to the current project site.
4. Open your new web part page in SharePoint Designer.  This can be done in SharePoint 2007 or 2010, but these instructions will be for 2010.
5. Click the Insert tab in the ribbon, click Display Item Form, and choose the name of the list “Project Details”.
6. Use the Add/Remove columns button to display the needed fields on the page.  The Link to Project Site field does not need to be displayed here.
7. Click the Parameters button in the ribbon, because a new parameter needs to be created, which will store the URL of the current site.  You can call it “SiteURL”, and on the right, choose “Server Variable”.  The server variable name is URL.  Here’s one of many blogs out there that list out all of the other types of server variables that you can use.
   
8. The trick now is to filter the current list so that it shows the related item in the project details list, where the “Link To Project Site” field is the same as the current site.  Click the Filter button in the ribbon.  Check the box next to “Add XSLT Filtering” and then click EDIT.
   
9. This is the filter to use:
   [contains(@LinkToProjectSite,substring-before(substring-after($SiteURL,’projects/’),’/’))]
   
10. Click the Paging button in the ribbon, and choose to limit the list to one item.
11. It’s important to note that this since data view web part is created ON the top level site, it is NOT going to show any records when you preview the web part in the browser.
12. Export the web part, and then insert it on the Project 1 site home page.  Because of the formula above, the data view web part will automatically display the “Project 1” record when placed on the project 1 sub-site.
13. Save the Project 1 site as a template, and create a new site called Project 2 using that template.  Also, create an item in the Project Detail list called “project 2”.  Then, the DVWP on the project 2 site will show the correct detail record.