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.
- 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.
![ch5[110]](https://i0.wp.com/wonderlaura.com/wp-content/uploads/2016/03/104-ch5110_26df0ba8.png?resize=452%2C336 "ch5[110]")
- 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.
![ch5[109]](https://i0.wp.com/wonderlaura.com/wp-content/uploads/2016/03/104-ch5109_26df0ba8.png?resize=466%2C228 "ch5[109]")
- 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.
- 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.
- Click the Insert tab in the ribbon, click Display Item Form, and choose the name of the list “Project Details”.
- 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.
- 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.
![ch5[114]](https://i0.wp.com/wonderlaura.com/wp-content/uploads/2016/03/104-ch5114_26df0ba8.png?resize=508%2C348 "ch5[114]")
- 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.
![ch5[115]](https://i0.wp.com/wonderlaura.com/wp-content/uploads/2016/03/104-ch5115_26df0ba8.png?resize=447%2C270 "ch5[115]")
- This is the filter to use:
[contains(@LinkToProjectSite,substring-before(substring-after($SiteURL,’projects/’),’/’))]
![ch5[116]](https://i0.wp.com/wonderlaura.com/wp-content/uploads/2016/03/104-ch5116_26df0ba8.png?resize=651%2C192 "ch5[116]")
- Click the Paging button in the ribbon, and choose to limit the list to one item.
- 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.
- 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.
- 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.
I usually do it the other way around: store the project information in a list at the sub-site level, and aggregate the data at the site level (CQWP or DVWP). In my case, aggregation usually works better because permissions are managed at each project level (the budget for example might be confidential information). Added bonus in your example: the site URL is one of the fields attached to a list item, you don’t even need to type it.
Thanks Christophe, I agree, I usually discuss with clients the pros and cons of doing it each way… roll up or roll down. In this particular case, the client wanted to be able to created their own, multiple views of the project list on the fly, and needed the ability to report off of the list. One big list made all of this easier.
Thank you for your guide. However, I am not able to replicate this functionality. Following this guide, step-by-step, even recreated exact structure and names of sites and lists… When I try to Import exported webpart I can’t view Project 1 site anymore. Error is shown, telling me that “List doesn’t exists. Selected site contains list which doesn’t exist. List were probably deleted by another user” I have czech language version of SharePoint, so this error message is in my language and this is my attempt to guess how it would look in original english, not exact text. Any idea how to make it work or where the problem could be?
Jiri, I don’t know, are you able to successfully import it to other pages in the same top level site? I’m sorry it’s very frustrating. Maybe try creating it as a data view web part instead of an xslt list view web part. Here’s my screencast on how to do that: http://www.youtube.com/watch?v=r2eODYHp73A
Hi Laura,
i was trying to do the exact thing with a CQWP, but i wasnt able to get it work… I have one big site with the project list and some “projects” as subsites.
I was so happy to see an other solution, beacause i was searching for hours in google… even for days…
Now i tried it step by step as you described it, but now i have the same error like Jiri. I cant even get access to my subsites where i imported the webpart. The error is the same like Jiri described. Do you have a solution? I would be so happy… 🙂
TY
Hi Laura,
i was trying to do the exact thing with a CQWP, but i wasnt able to get it work… I have one big site with the project list and some “projects” as subsites.
I was so happy to see an other solution, beacause i was searching for hours in google… even for days…
Now i tried it step by step as you described it, but now i have the same error like Jiri. I cant even get access to my subsites where i imported the webpart. The error is the same like Jiri described. Do you have a solution? I would be so happy… 🙂
TY
Hi, Laura. Are you regretting this post yet? 🙂 Like some other commenters, I am having trouble exporting from a parent site and importing to a subsite, but my main difficulty is in making the parameter filter work on the original site (I added a list item whose “SiteURL” value is the current site’s URL, in order to test the filter.) After step 9, no change — it acts as if there is no filter. I will really appreciate any tips you can offer. Thanks very much.
For those of you having issues, make sure you’ve created a data view web part and not an xslt list view web part. I’ve done this successfully in several different environments this way. Sorry this is so frustrating!
Laura, this is Jim from the BSUG meeting. This post has been a great help. However, I am having a problem with the substring part of my filter. Your example substrings after ‘/projects/’ in the URL. In my situation, I need to substring after sites that will change. For example, I need to substring after ‘/southcentral/’ in the following URL – /region/southcentral/999. — southcentral/ is a region name that may change. (e.g., /region/atlantic/123). Ultimately, I need to pass the “LocationID” – (999 or 123) to the filter. Is there a way to do this? Thx
Laura, I was able to come up with a solution to my substring problem. Instead of trying to extract the “LocationID” from the URL. I created a new concat field in my list called ‘regloc’ that returned “Region/LocationID” from the Region and LocationID fields in my list. (Example regloc concat: southcentral/999) I then adjusted the filter expression to: [contains(@regloc, substring-before(substring-after($SiteURL, ‘regions/’),’/default’))]. Ultimately, I just needed to extract a unique value from the URL instead of just the “LocationID”. Works like a champ.
Hi Jim, that’s awesome that you figured it out! Good idea!
Laura,
Great Article. I have a different scenario, I am trying to extract store acronym from my user id which is Snehal Rana (MW MWC) I need the list to filter based upon 3 digit of store acronym. In this case MWC. How do I accomplish this?
Thanks
Snehal H Rana
Snehal,
If your store acronym is in the URL, then you could use this method, but if it’s not, I don’t know how you’d accomplish that in a situation like this.