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
Items added in last 7 days filter. This worked great as we wanted a rolling 30-day view as our site has been up for a few months.
Ha! Nice tip! I used it to show all tasks modified in the past 7 and 14 days – perfect for status reports.
It really works great. A good filter for displaying the items that expires within 15 days, etc
Hi,
Is it possible to have two filters [7 days / 30 days] in the same view,To give users to choose the filter they desire?
Thanks
Flyguy – if you add two conflicting filters to the same view it will not make logical sense.
E.g.
*Items over 7 days AND items over 30 days will only show items over 30 days.
*Items over 7 days OR items over 30 days will show items over 7 days.
What you can do is create two views and then let the user pick the appropriate view/filter they need. In WSS 3 you pick the view from a dropdown box towards the top right.
hi thanks for the nice tip!
i tried to use the filter in a ContentQuery-Webpart – but it doesn’t work for me 🙁
when i use it in doc-lib, it works like a charm.
Any Ideas ?
Tim – just a guess but if you are using WSS 3 / MOSS 2007 then check out this article
http://blog.pentalogic.net/2008/11/the-truth-about-using-today-in-filters/
and specifically the link at the bottom
http://www.u2u.info/Blogs/Patrick/Lists/Posts/Post.aspx?ID=1782
Using Today in a CAML Query – the offsetDays attribute
I am trying to use the filters to display a task whos status is Not completed OR not Cancelled. The filter does not seem to work. The second OR filter does not seem to work?
This is a hosted Sharpoint site.
Ian – I think you’ve got a little logic error there as your filter will always be true, in other words whatever status you set is always going to be either not completed or not cancelled.
If you’re not clear work it out on a bit of paper with the status down the side and the two parts of the filter across the top – this will make it clearer.
I don’t know what your requirements are, but maybe you need Not Completed AND Not Cancelled?
The [Me} view works very well wooohoo – tyvm!! Do you know how to filter a completed record + 30 days so it falls off the list after 30 days? We have date complete in our list – is there a way to add 30 days to that complete date so it will not show on that view?
Thank you in advance for the help 🙂
Oh I got it! – I created a calculated field for Complete Date + 30 days; filtered by [Me] and the calculated field is greater than or equal to [Today] and it works wooohoooooooooo – thank you!
no sorry that doesn’t work if the complete date does not have a date in it 🙁 any ideas please? 🙂
Hi Cheryl – I think either an extra condition to exclude empty completed dates or using an IF statement may work. I’ll have a play around later today and let you know what I’ve found.
[Me] filter does not work when the task is assigned to a group the user is a member of. Does anyone know how to make the task appear in the user’s to-do list when the task is not assigned to the user directly?
Thanks
Please let me know your approach, if you have to deliver the following
Sample Scenario
Say we have a content type with 2 columns a) News Article Type b) News Article Category Column a & b are choice types with a list of values. But what we need is that if News Article type is selected as say Internal then only certain List values must be displayed in the News Article Category dropdown list.
News Article Type : Internal
Dropdown list: Corporate; Finance; Employee; HR;
News Article Type : External
Dropdown list: Corporate; Industry; 2010 FIFA;
Question
if there a way to filter this using calculated field or you guys think there is a better way of doing this?
A Google search for “sharepoint filtered drop down” gives some promising results.
How do you add more filters to the view?
Deaven – at the bottom of the Filter section you will see “Show More columns”, every time you click this it will add space for an extra filter term.
Well Ryan its good tip.
Iam showing date difference in calculated column as a number by using jquery. Now i need this number to be considered in filtering options of that view.
Is there any way? Please do the needful.
Nazakath – I don’t think you will be able to do that because it happens too late in the process :-
A) User requests a page
B) SharePoint gets a list, applies a filter to it and gets a set of results.
C) Renders the results to HTML
D) The browser then displays the HTML and runs your jquery javascript.
So by the time you are running your javascript the filter has already been ran.
You may be able to hide from view certain rows of the table using jquery depending on your calculated field – but this is not ‘real’ filtering.
What calculation are you doing in jquery – can you do the same using Filters?
Hi Rayn thanks for comments..
My requirement something like this.. I need to filter the requests which are equl or greater then 45 days from the date of request created. i need to filter them and show them in a view.
Regards
Nazakath
Nazakath – thats an easy one (assuming you are using WSS 3 or MOSS 2007) see this post.
http://blog.pentalogic.net/2008/11/the-truth-about-using-today-in-filters/
So you would create a new view and set a filter of
Created
is greater or equal to
[Today] + 45
My boss refuses to use Access Query’s and Reports linked to SharePoint. He insists there is a way to do this “easy”.
In access I used SQL:
TRANSFORM Count(OPSLOG.ID) AS CountOfID
SELECT OPSLOG.[Record Type], Count(OPSLOG.ID) AS [Total Of ID]
FROM OPSLOG
WHERE (((DatePart(“w”,[start date/time]))=1) AND ((OPSLOG.[Record Type]) In (“Vandalism”))) OR (((DatePart(“w”,[start date/time]))=7) AND ((OPSLOG.[Record Type]) In (“Vandalism”)))
GROUP BY OPSLOG.[Record Type]
PIVOT Format([Start Date/Time],”yyyy mm”);
There are a few parts to this. Can I do a WHERE 1 AND 2 are true, OR 3 AND 4 are true?
Second, is there something simular to DatePart() so I can only get weekend, or Weekday (I showed Weekend SQL)? I don’t want to make a whole column dedicated to useing the TEXT(WEEKDAY([Start DAte/Time]), “DDD”) just so I can filter on the extra column (Close to date work around above).
Anyone?
Ryan,
In my case I have people assigned to a document so when a particular person logs he is only able to view the documents that are being assigned to him. I do that using the filter utility in a view.
what I want to do in addition to this is
when any one who is in the site administrator group logs in then they can view all the documents irrespective of whether they are assigned or not. Can I do this?
I’m trying to setup a filter based on a specific column, but that column is not showing up in the list of available columns to filter by. Any ideas?
Thanks
Is it possible to filter a document library using a field from a related list?
The site I manage is used to manage project documentation. We use a list to link to the related project documents in separate libraries (e.g. Charters library, Timelines library, Weekly Reports library, etc.)I want to filter out documents in each library when their status in the main list has been changed to “Completed.” (i.e. I need to filter on the list item’s status and not a column within the document library I want to filter)
I hope this makes sense; it is very difficult to explain.
Any assistance would be greatly appreciated.
@Pratik – I don’t think you can do this OOTB
@Tim – If the list is a Calendar type you can’t use Start/End dates in the filter as SharePoint gets into a bit of a muddle with recurring events – you can workaround using a calculated column = [Start Time] but this will only work for lists that DONT contain recurring events. If its another list/column type get in touch and will see what I can figure out.
@Cindi – I am not really sure what you are trying to do. If you send over some screenshots that may help me understand.
http://www.pentalogic.net/about-us/contact-us.aspx
It seems that if you set an alert to “All changes, and “Anything changes”, you get an alert even if someone views an item in a list for instance. Anyway to filter out just the view of an item?
@Greg – not sure whats going on here, I’ve never heard of an alert for just viewing an item before! Are you using a basic list or is it a document library? Do you have any custom code or workflow on the list that actually modifies something in the list when its viewed?
I am trying to use the method “Items Due in the next 7 Days” to accomplish the next requirement: “Show a view with employees having their birthday in the next 30 days”
What I have is a list with name and birthday columns (date format is MM/dd/yyyy), like Ana, 10/20/1980.
I have created a calculated column which has the month and day of the birthday and the current year, called BirthdayThisYear: 10/20/2009.
I have also created a calculated column named UpcomingBirthday:
[BirthdayThisYear]-30 (DateTime column, Date only)
Then I have created a view with the following filter:
UpcomingBirthday is less than or equal to [Today]
AND
BirthdayThisYear is greater that or equal to [Today]
There are three employees with a birthday coming in the next 30 years, but nothing shows in the view I have created.
Am I missing something?
@Laura
I have created a calculated column which has the month and day of the birthday and the current year, called BirthdayThisYear: 10/20/2009.
How have you done this – have you used the infamous Today trick or are you entering the Year manually?
Try removing the filter and showing your two calculated fields in the view to ensure they are being calculated correctly.
Also – your method isn’t going to work correctly for people who’s birthdays are in the first so days of the year. Say bob’s birthday is 5th Jan, next birthday is 5th Jan 2010 but your lit shows 5th Jan 2009 so they won’t appear in the list until the 1st Jan when you change the year to 2010.
Have you seen this Birthday reminder web part?
Finally – now that Reminder supports recurring events you could use that to send out an email when peoples birthdays are due in 30 days
I am trying to filter a list for it to show the items that have been worked in a Month To Date timeframe, using the [Today]-30 or similar will provide a defined day qty but I am looking for calendar days defined by the month of a Worked Date field.
Any ideas?
@Ed – Can I clarify your question :-
Do you want to show only this month to date, so today its the 13th Nov so you would include items from the 10th Nov, but not the 30th Oct or the 15th Nov.
Have I got this right?
Ryan,
Thanks for your quick reaction.
Correct, I only want to show month to date info, nothing from Oct, nor future cases (however my list should not see any future date as the Date Worked).
Thanks in advance for your help!
Ed.
Hi Ed – it was a bit more complex than expected but this may help
http://blog.pentalogic.net/2009/11/howto-filter-items-current-calendar-month-view-sharepoint/
This is really useful! I’d be nice if Microsoft had this on their site! 🙂
Hi Ryan,
Is it possible to create special markers, on the lines of [Today]/[Me], programmatically. Something like [LastUserLoginTime] or [CurrentSiteValuePath]. Would be great if you throw some light on this.
Thanks,
Vijay
I have the same challenge as Flyguy. I want to make a view showing an item in a view, if either
* items are due within 7 days
or
* Items are due due within 30 days AND the value of column ‘How long does it take’ is greater than 10 hours.
@Jacob – sorry, missed your question first time round.
The expression you would need for this is (for a task list you would want to add AND NOT Completed.)
( Due Date > [Today]) AND
( Due Date 10 AND Due Date < [Today]+30) )
Although you can express this in CAML (the query language underlying SharePoint) it is just too complex a formula to setup in the View filter UI (do a search for the CamlQueryBuilder tool)
You've got a few options I think :-
* You could setup a DataView webpart and filter using XSLT
* You MAY be able to save the list as a template, modify the definition manually with the complex formula and then create a new list from it
(Both of these are way way beyond what I can describe here though!)
Maybe the easiest way though is to put two views on one page – one showing Due in 7 days if takes less than 10 hours , the other just below it showing Due in 30 days if takes over 10 hours?
This might make a good question for “Stump the panel” http://www.endusersharepoint.com/2010/01/04/stump-the-panel-sharepoint-qa-forum-now-live/
Hi all,
This question may be simple, but I’m stumped. I am trying to filter a standard view of a calendar list by the start date column, but it is not appearing in the “show the items when column…” drop down. Any ideas??
Thanks!
@Anna. SharePoint doesn’t allow you to filter on Start/End times in Calendar lists because of the way Recurring events work – its a long standing frustration!
You can fool SharePoint into letting you do this but if you use recurring events then you will get into all sorts of trouble.
http://weblogs.asp.net/gunnarpeipman/archive/2007/10/09/filtering-sharepoint-calendar-by-start-time.aspx
So, I’m trying to apply a filter to show me items that have not changed for more than 30 days (so these are older items that no one has touched). I’d be using the Modified date column. My brain is just not being logical.
Would that filter be [Today]+30?
@Mary – Assuming you are using SharePoint 2007 then you would want a filter of
Modified Date “is less than” [Today]-30
aka Modified date is older than 30 days ago.
Hi Ryan, Any clue how I can filter an issues list in sharepoint 2010 based on assigned to being empty?
can’t find any examples of what to put when I want to compare any field to nothing.
@gpnash – you can just do a filter for “Assigned To” “is equal to” and then leave the value textbox empty
Just wondering if it is possible to have more than one filter in a SharePoint view, i.e. is it possible to have an initial filter than a secondary filter?
@Brandan – sure it is. If you have a look at the first example its filtering on [Status] != [Completed] AND [Due Date] < [Today].
If you need more filters there is a "Add more" link.
Thanks Ryan.
My question relates to customised SharePoint views. Currently my SharePoint view has one yellow filter that runs along the top of the view where I am able to sort items in ascending or descending order and so forth.
I am wondering that within SharePoint if it is possible to apply a secondary filter i.e. I would have the top yellow filter than another one below it, ideally this would change depending on the document type selected, different metadata for different document types.
Cheers,
Brendan.
Hi Ryan,
I’ve read through some of the postings here; your responses are very helpful. Nice job.
I would like to know if there’s a way to implement the following conceptual filter:
where myFunction(value) = true
Ideally myFunction() would be .NET code, and “value” could be anything. A string would be fine. The function would always return true or false.
I understand (and would accept) any performance implications due to the complexity of myFunction.
Can this be done?
Thank you in advance,
-kevin-
Hi Kevin,
I am not aware of any way to do that directly, but the workaround if you haven’t already thought of it is to pre-calculated a value and store it in a column and then filter against that.
For example you could have some .net code running as part of a workflow or event receiver that when a record is added or modified will run the myFunction(value) and store the true/false result in a “Show Record” field.