Introduction
We discussed in a previous article how to customise SharePoint’s built in Task List and briefly looked at the power of Views with Filters. In this article we will build upon that by demonstrating some advanced use of Filters.
This article assumes that you are already familiar with Views and Filters – if not please review the previous article.
Overdue Tasks
To show a task lists Overdue tasks use the following Filter :-
And
Note – [Today] is a special marker that SharePoint understands as the current date. |
 |
My Overdue Tasks
Add the following to the Overdue Tasks filter
Note – [Me] is a special marker that SharePoint understands as the current user. |
 |
Items added in Last 7 Days
It would be nice to be able to enter something like
- Created – is less than – [Today] – 7
in the filter, but alas its not quite that simple!
The workaround is to create a Calculated column and then filter on that column.
| Select Modify Settings and Columns, then Add a new columnGive the column name something like “Filter 7 Days” and select a Calculated column |
|
| Enter a suitable DescriptionIn the Formula box enter “=Created+7″Choose Date and Time format and Date OnlyClear Add to default view as we don’t want this field to appear on any of the views |  |
Create a new viewSetup a filter to show only :-
|
 |
Items Due in the next 7 Days
| Select Modify Settings and Columns, then Add a new columnGive the column name something like “Filter Coming Due” and select a Calculated column |  |
| Enter a suitable DescriptionIn the Formula box enter “=Due Date-7″Choose Date and Time format and Date OnlyClear Add to default view as we don’t want this field to appear on any of the views |  |
Create a new viewSetup a filter to show only :-
And
And
This will ensure that the view only displays Uncompleted items that are due in the next 7 Days but are not Overdue. |
 |
Items due in the current Calendar Month
If you need to filter for a Calendar Week/Month (rather than a rolling 7/30 day period) then this post details how
How To Use Filters in SharePoint to show items in the current Calendar Month
| Free SharePoint Calculated Column Cheat Sheet |
| a handy 3 page reference for calculated column functions and formulas. |
 |
Tags: filters, Formula, SharePoint, View
Hello!
Is it possible to make a filter that only display the items that createt in the current year?
For Example: i have items created 2009 and 2010 in my list.
Now i only want to display the items that created in 2010.
In 2011 in want to display only the items from 2011 and hide the 2010,2009 items and so on…
Cheers!
Philipp
This post shows you how to do the current calendar month and at the bottom there are examples for current year.
http://blog.pentalogic.net/2009/11/howto-filter-items-current-calendar-month-view-sharepoint/
You can adapt this for the previous year (use YEAR([Date])-1)
Thank you, works perfect!!!
[…] With reference too http://msdn.microsoft.com/en-us/library/bb862071.aspx http://blog.pentalogic.net/2008/10/advanced-sharepoint-view-and-filters/ […]
Nice post Ryan !…
Heres a post to describe how to filter the list view web parts with JavaScript
http://rahul-vartak.blogspot.com/2010/08/sharepoint-2007-filtering-list-view-web.html
Hi
I want to show items which are after 2 hours to present time in a list view. Please let me know as this includes working against TIME too where the TODAY cannot be used for TIME calculations.
Or let me know how to get the current time through any calculated column.
Regards,
Kiran
@Kiran – you can’t use Today in a calculated column
Sorry – I don’t know any way to do what you want as there is no Time function in SharePoint that you can use in filters.
I’m interested in a different case. Is there a way to filter objects (specifically calendar items) based on the group membership of the Created By field?
EG we have a master Sharepoint site with many sub-sites within it. Each sub-site has groups of users, and also has it’s own calendar for when and where people are traveling or otherwise unavailable. I just added some time I’m spending out of the office to three different calendars on the different sub-sites where I’m a member. It would seem more productive to have a master calendar, and a filtered view for each sub-site showing only those people relevant to the sub-site, and I was wondering whether it is possible, and how to accomplish such a thing.
The information here is helpful, however I am interested in filtering calendar events that are upcoming in the next 3 days including today. Is there a way to do that?
Thank you Ryan for your interesting post and your helpful answers.
Does anyone know how to put some intelligence into some SharePoint date fields that when somone enters a date before today, they get an error message. I was told that this can be done through List Forms Extensions, bu I have looked at their user doc and can’t figure it out. Please help.
I need to put some intelligence into some SharePoint date fields that when someone enters a date before today, they get an error message. I was told that this can be done through List Forms Extensionns, but I have looked at their user doc and can’t figure it out.
Hi,
great blog!
I have a question regarding filtering techniques: is it possible to configure a view based on metadata? I can’t find a way to do this…
Hello,
is there a way to always have the filter look for items ‘created’ (or other column) yesterday? e.g., [today]-1 or something?
i have to look at a list every day and count items added yesterday – a formula to do this would be very helpful!
thanks!
Hi Justin – its exactly that (assuming you’re using SharePoint 2007 or above)
Where (Some Date) > [Today]-1
http://blog.pentalogic.net/2008/11/the-truth-about-using-today-in-filters/
Thanks Ryan,
That worked “[Today]-1”
saved me some time!
Hi Ryan, this was an excellent article and I’ve used many of your techniques. I need to track daily tasks by time. As soon as they go past due I need to contact the assigned resource to get an update. [TODAY] only shows tasks that are at least 1 day past due. In Excel I would use Now(), but that doesn’t work in SharePoint. Have you run across a way to show tasks in a view that are past due even by a 1 minute? Could I possibly use JavaScript?
Any guidance you can provide would be greatly appreciated as it would be a huge time saver.
Regards,
Steve
Hi Steven – I haven’t tried this myself but this looks promising.
http://nickgrattan.wordpress.com/2008/04/24/filtering-views-by-time-and-date/
I would be interested to know how you get on!
Hi Ryan, Wondering if you can help.
I need a filter to exclude some records.
We have a work team og 15-20 people.
I’m trying to generate a View where “AssignedTo = [Me]” – works OK but then excludes records where the “Completed Date” is greater than, say, 7 days.
This way end up with a view showing me all my tasks plus what I’ve completed in the last 7 days.
Any thoughts please?
Andrew
Hi Andrew
You should be able to combine these with an AND operation.
In between your filters you will see radio buttons for AND or OR – it defaults to OR but for your case you will want to choose AND.
Hi, i wonder if you can hide the latest document in a view and only show the top 5 documents after the latest.
How do you filter a calendar that has multiple calendars in view? I have the default calendar plus a few extra calendars overlayed and I want to filter everything by location (location is equal to X) but when I filter it, it only filters information put on the default calendar and not the calendars I had overlayed in the view.
I came across this post when I was trying to find a way to filter my list to show items 4 hours after they were created. This was a follow up list where we needed to look at accounts again after four hours. I ended up getting it to work on my SharePoint MOSS site.
I added a calculated column to the list that I named Four Hour Delay. The column is calculated as =[Created]+.166 (four hours being approximately .166 of a day). Then I filter my list on Four Hour Delay < [Today].
Nice post Ryan.
Can I use query string paramter to filter the list somehow? If yes, please tell me how.
This should help – http://www.vbduncans.com/blog/2008/12/30/UseTheQueryStringToFilterAnySharePointListView.aspx
Thanks Ryan.
But I was looking for a way to specify the querystring parameter name in the filter fields. Any idea? Is it possible?
@Manish – I am not sure what you mean, can you elaborate please?
I mean can we retrieve a value of a query string parameter and filter the list based on those parameter using out of box functionalities?
Say in your first example,
I would like to do something like “Status” is equal to ““.
Ryan, I have 2 columns, one labeled Due Date, and the other – Completed Date. I am trying to create a filter that filters only items where the Completed Date is greater than the Due Date, thus giving me a filter for completed items that are late. Any help you can give would be appreciated.
Greg
@Greg – this should help “How To setup a SharePoint view filter to compare 2 list fields”
http://blog.pentalogic.net/2010/06/how-to-setup-sharepoint-view-filter-to-compare-2-list-fields/
Hi. I hope you can help. I am looking to create a view that displays the last entry for a particular account record. So for example.
Entry 1 on my list – Acct 123, Date 1/1/2011.
Entry 2 on my list – Acct 123, Date 4/1/2011.
I want to create a View where only Entry 2 will display because the date is greater.
Any clue how to accomplish this?
@Nikki – you can setup a view, set it to sort by your date field in descending order and then set Item Limit = 1 and this will return just the newest record.
Sorry Ryan…I think i missed a little info.
Say I had Entry 3 on the list – Acct 456, Date 5/1/2011. So i want the View to have…
Acct 123, Date 4/1/2011.
Acct 456, Date 5/1/2011.
@Nikki – I am not sure I understand you?
Sorry. I apologize for not being clear. So i have a list of accounts. Some of the accounts repeat. I want a view
that will display only the latest date for each account.
For example.
Entry 1 on my list – Acct 123, Date 1/1/2011.
Entry 2 on my list – Acct 123, Date 4/1/2011.
Entry 3 on my list – Acct 456, Date 5/1/2011.
I want the view to return :
Acct 123, Date 4/1/2011.
Acct 456, Date 5/1/2011.
The view should exclude Entry 1 because Acct 123 is a duplicate of Entry 2 but Entry 2 is the latest. Entry 3 should be on the view because its the only entry for Acct 456. Hopefully, that’s more clear. I want to show the latest date for all accounts and all unique accounts without duplicates.
@Nikki – Ahh, gotcha! I remember seeing a similar post (from you?) on one of the SharePoint forums. I am afraid I don’t know how you cold do this without some coding, sorry!
Thanks anyway Ryan. I think I posted one more place today…but that was it. Still stomped on this one. May try a different approach. Thanks for thinking about it. Will continue to search.
Ryan,
Thank you for all the great info. I am trying to filter my items to show 30+days old. Meaning after created date I need it to display the items that have passed the 30 day mark. Created column called “30 Days” =created+30 then Filter to show 30 days-is less than-[Today] it shows all current items. If I use greater than it shows all items plus future items. I tried, Created-is greater than or equal to-[Today]+30 with different results but not accurate ones. Ideas??
This article was written originally for 2003 and in 2007 and onwards there is an easier way (See where it says Edit in article).
Basically you don’t need to muck around with the calc column, you can do it all in the filter, so
Filter = “Created” “Is Less Than or Equal To” “[Today]+30”
http://blog.pentalogic.net/2008/11/the-truth-about-using-today-in-filters/
Still no luck. When I use that filter then nothing displays at all. I am trying to build KPI’s for the Service Desk Trouble Tickets and this one is supposed to show “only” tickets that have been opened 30+ days. I have 4 that are over 30days and only 1 shows when I filter “30 Days” -less than-[today]. I have flipped, flopped and tried to figure it out and still come up short.
I was trying to filter with multiple calculated fields. From my understanding that is not going to work. I believe the issue is now resolved……
Hi Ryan,
Solid article, big thanks. However, I’m hoping you could help me with following issue in MOSS 2007.
Got 2 lists – “AllContacts” and “Companies”.
In List: “Companies”, there is a column: InternalEmployees.
I would like to have this as a hyperlink which when clicked, will filter through AllContact list and display only contacts that are employed by that Company (value can be taken form column Company in list AllContacts)
I hope it is clear enough and possible.
Ryan,
Is it possible to have anyone enter in a due date, but only a select few can alter the due date once it has been entered in a list?
Is there any workaround for SPS 2003 to use the =Created+7? Thanks.
Adding to my above post, whenever I try =Created+7 or even =[Created]+7 I get the following error:
One or more column references are not allowed, because the columns are defined as a data type that is not supported in formulas.
Created does not even show up in the “Insert Column” box.
@Airin – the workaround for SharePoint 2003 is in this very post! See “Items added in Last 7 Days”
Are you putting the [Column]+7 in the correct place (a calculated column set as a Date type) or are you putting it in a filter?
Thanks for the reply. Here is my exact steps.
1. click “modify settings and columns”
2. Under “Columns” section, I click “Add a new column”
3. Type in “7 day filter” as column name and select “Calculated (calculation based on other columns)” option
4. Now in the formula box I can try =Created+7 or
=[Created]+7 (both no spaces). Now the insert column box to the right doesn’t show “Created” as an option where it does above in your post.
5. Below I select “Date and Time” and “Date Only”
6. Either formula I try I get the “One or more column references are not allowed, because the columns are defined as a data type that is not supported in formulas.”
In my “All document” view, the “created” column is displayed so I’m not sure why I can’t use it. Am I missing something?
Thanks again.
This is very helpful! I have a slightly different question, though. Is there a way to do nested logic in a filter? i.e. I need to select items where (status=x AND date>=today) OR (status=x AND date=blank). I can get either the first condition or the second, but not both together.
@Sallie.
I think you’ve got two options here – 1) Convert this to a Data View Web Part (DVWP) using SharePoint Designer which allows you to enter complex expressions like that 2) Use a Calculated column to do the calculation and generate “TRUE” or “FALSE” then do the filter on that true/false value – search “calculated column cheat sheet” for inspiration.
Or… hang for a few days as you’ve given me inspiration for a nice little blog post…
I’m fairly new to SP, so haven’t yet played much with Designer. However, calculated columns did the trick. (Yes, plural – it’s a bit ugly as I also have another iteration where I need to select status=y with the same conditions.)
I’ll look forward to the post and hope to make this a bit more elegant in the future. Thanks!