Posts Tagged ‘View’

How to Create a SharePoint “Does Not Begin With” Filtered List View

Date:September 9th, 2011 Author: Tags: , , ,
Category: Calculated Columns, Filter Comments:6 ;

Wonderful though SharePoint is most of the time, occasionally it just lets you down at the most unexpected moment.

So, last week, I wanted to look at a list of all of our sales that were not made to organizations in the USA.

Simple I thought, since all our sale reference numbers start with a 3 letter country code I shall just create a view for all records where Reference Number does not begin with “USA”

So, create a new view, off we go:

Oh dear, we have an option for “begins with” and “contains” but nothing for “does not begin with” or “does not contain”. How disappointing!

So, quick call to Ryan, the king of the calculated column, and we have a handy workaround which seemed worth sharing with you.

Create a calculated column using the following formula which will just display the first 3 letters of the reference code – call it something like “First 3 letters”.

=LEFT([Reference Number],3)

Then create a view with a filter:

WHERE “First 3 Letters” “Is not equal to” “USA”

Easy as that!

You can use this method to create a view for:

  • Does not begin with
  • Does not end with (using the RIGHT instead of LEFT function)

“Does not contain” is a tad more complicated but can be achieved by using the SEARCH function. So in our example

SEARCH("USA",[Reference #],1)

Will return the first position of USA in an entry in the Reference Number field, or an error if it can’t be found.

So combining this calculated column:

=ISERR(SEARCH("USA",[Reference #],1))

Which gives “Yes” where it returns an error ( “does not contain”) and “No” where “USA” is found, with a filtered view with shows only “Yes” results, will give you records which do not contain USA.

See our Calculated Column Cheat Sheet for these and other useful formulas.

Hope this helps, and if you have any suggestions for other calculated column ninja moves we would love to hear them.

How To Use Filters in SharePoint to show items in the current Calendar Month

Date:November 26th, 2009 Author: Tags: , , ,
Category: Calculated Columns, Filter, SharePoint Ideas Comments:209 ;

Its very easy using the Filter feature of SharePoint views to show only items completed in the last 30 days or items due in the next X days; for example :-

Completed >= [Today]-30

On the Advanced SharePoint View and Filter techniques post Ed asked how you could filter for the current calendar month rather than a rolling 30 day period (for example all tasks due this calendar month).

Like all great questions it seemed simple enough – something like only showing items where the Month and Year of the due date = the Month and Year of the current date.

(more…)

Advanced SharePoint View and Filter techniques

Date:October 26th, 2008 Author: Tags: , , ,
Category: Filter, SharePoint Ideas Comments:119 ;

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.

(more…)