We helped a custom recently with an interesting SharePoint question – how to identify if a List item is new or has been modified? In addition they also needed to be able to setup a View to show only New items and another to show Modified items.
Archive for the ‘Calculated Columns’ Category
How to Identify New or Modified List Items (records) in SharePoint
Date:October 22nd, 2015 Author:Ryan Tags: Calculated Columns, Filter, TipCategory: Calculated Columns, Filter, SharePoint Reminder Comments:0 ;
Using Calculated Columns with Yes/No (Boolean) fields in SharePoint
Date:June 15th, 2015 Author:Ryan Tags: Calculated ColumnsCategory: Calculated Columns Comments:5 ;
A fairly common SharePoint question is how to deal with the Yes/No field type (technically known as Boolean fields) when using Calculated Columns.
Typically you may want to do something like change Yes/No to something else for display purposes – for example if the fields is for Important you could put “VIP” or blank in a View instead.
What does NOT work?
If we assume the Yes/No field is called “Important” then here are some of the formula that WILL NOT work
=IF([Important]="True","VIP","")
The reason is that you’re working with a Boolean field under the hood so TRUE is a special key word and TRUE is not the same as “TRUE” which is a string with the letters T, R, U and E in.
(I show my wife examples like this when she wants to know why computer programmers are so pedantic!)
These also fail :-
=IF([Important]="Yes","VIP","")
=IF([Important]=1,"VIP","")
So what does work?
So what you can use instead is (note NO quotes!)
=IF([Important]=TRUE,"VIP","")
The opposite is of course
=IF([Important]=FALSE,"Not a VIP","A VIP")
You can also use this as a shorthand for =TRUE
=IF([Important],"VIP","")
For more tips on calculated columns be sure to check out our handy Calculated Column Cheat Sheet and other blog posts about SharePoint calculated columns and formulas.
Tip – for View Fitlers still use Yes or No (no quotes) – this is just for calculated column formula.
Complete guide to filtering SharePoint lists by the current week.
Date:September 17th, 2012 Author:Ryan Tags: Calculated Columns, Guide, TipCategory: Calculated Columns, Filter, SharePoint Ideas Comments:7 ;
A common requirement in SharePoint is to filter lists so you’re only showing the current week, month, quarter etc. If you only want to see the last X number of days (e.g. last 30 days) then it’s simple enough.
If you want setup a SharePoint view to see the current calendar week then its a little more complex – especially if you define your week differently from the standard Sunday to Saturday or Monday to Sunday.
First you must look at and really understand the method for setting up SharePoint views to filter the current calendar month or week as it’s not really obvious.
Long story short – here are the steps :-
- Use one of the calculated column formula below to find the “Start of week“
- Use a calculated column formula of “End of Week“
= [Start of Week] + 6
- Setup a View Filter showing items when [Today] is in that range.
Start of Week <= [Today] AND End Of Week >= [Today]
Again – see this post for showing the method in step-by-step detail.
If your week goes Sunday to Saturday or Monday to Sunday then use :-
| Start of week | End of week | |
| Sunday to Saturday |
=[Due Date] - WEEKDAY([Due Date]) +1 |
=[Start of Week] + 6 |
| Monday to Sunday |
=[Due Date] – WEEKDAY([mydate],2) + 1 |
=[Start of Week] + 6 |
Obviously you will replace [Due Date] with your own particular date field.(Check out the WEEKDAY function to understand what the 2nd argument does)
The other options are a bit more complex to find the start of the week (again end of week = start + 6)
| Start of week | |
| Tuesday to Monday |
=[Due Date]-WEEKDAY([Due Date])+IF(WEEKDAY([Due Date])<3,-4,3) |
| Wednesday to Tuesday |
=[Due Date]-WEEKDAY([Due Date])+IF(WEEKDAY([Due Date])<4,-3,4) |
| Thursday to Wednesday |
=[Due Date]-WEEKDAY([Due Date])+IF(WEEKDAY([Due Date])<5,-2,5) |
| Friday to Thursday |
=[Due Date]-WEEKDAY([Due Date])+IF(WEEKDAY([Due Date])<6,-1,6) |
| Saturday to Friday |
=[Due Date]-WEEKDAY([Due Date])+IF(WEEKDAY([Due Date])<7,0,7) |
See this picture for some test data, its in a strange order (Thursday to Wednesday if first) but check it out on your desk calendar if you’re not convinced!
How to Create a SharePoint “Does Not Begin With” Filtered List View
Date:September 9th, 2011 Author:Clare Tags: Calculated Columns, filters, SharePoint, ViewCategory: 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 not to develop a SharePoint [Today] Calculated column
Date:August 22nd, 2011 Author:Stuart Pegg Tags: Calculated Columns, SharePoint Development, TodayCategory: Calculated Columns, Highlighter, Training Comments:4 ;
After developing SharePoint Highlighter, we seriously considered expanding on this area with a Calculated Column that would allow you to use [Today] in the formula. If you’re thinking “But you can already!” I suggest looking at Ryan’s article about the [Today] column trick.
Needless to say, this would be a very useful tool for almost any SharePoint user. After much researching and prototyping we came to an unfortunate conclusion: Although it was possible to make a Custom Field Type to do this, it was almost certainly impractical (certainly for us).
The avenues we investigated finished in three types of dead-end:
- Brick wall: A completely impassable system limitation.
- Overgrown with brambles: Nothing but pain for all involved.
- Swamps: A long unpleasant slog, with an uncertain outcome.
The Brick Wall
The first thought in any developer’s mind when making a variation on a class should be “Inherit it”. It allows you to take advantage of the existing class’ methods with minimal difficulty and yet offers (almost) total control over its behaviour.
Attempting to do so will reward you with the slightly misleading error:
The type ‘Microsoft.SharePoint.SPFieldCalculated’ has no constructors defined
A brief search in the MSDN documentation for SPFieldCalculated shows that the absence of public constructors is intentional:
Windows SharePoint Services 3.0 does not support inheriting from this class.
And so this avenue ended in a brick wall. More worryingly; disallowing the inheritance of a class is usually a sign that something so heinous is happening inside that Microsoft doesn’t want it reproduced.
Brambles
Intrigued by the promise of horrifying creatures dwelling within, I had a look inside the class to see what was happening (using my own powers of code intuition, and not any kind of questionable reverse engineering method).
It seems the actual calculations take place outside of the SPFieldCalculated class, using instead a call to SPRequest. This new lane of research quickly ended in spiky brambles, as using a direct call to SPRequest would make our product (and hence anything it’s installed on) unsupported by Microsoft.
Undeterred, I took inspiration from the billing system at my old work and looked at keeping the ‘black box of calculation mystery’ class running in the background, with the new class acting as a wrapper around it.
Creating a Custom Field Type that kept a hidden field for data storage in the background was an architecture we’d experimented with when creating Highlighter, so adding a relatively minor call to update the formula with the current date and time seemed quite easy.
The thorny ending in this case was the enormous potential server load; updating the formula every time the list was viewed (to keep it up to date) caused the entire list’s worth of calculations to be refreshed. Viewing just 20 items would make up to 5000 items recalculate their values for each of our columns on the view.
Swamps
The final and most desperate option was to do it ourselves: Completely rewrite all or a subset of the calculated column functions, and handle the associated function nesting.
I’m DIM: Doin’ It M’self.
Following this path would mean wading through the boggy and unpleasant process of exactly reproducing Microsoft’s function calls; avoiding the murky bottomless pools of performance issues. All the while hoping we wouldn’t run into any of the hungry and sharp-toothed show stoppers along the way.
As you may have guessed, we didn’t venture down this path.
Why are you telling me all this?
Because although these paths aren’t available to us as an ISV, it doesn’t mean they aren’t options to you (except the brick wall, of course).
Brambles: Using SPRequest is unsupported, but if this doesn’t deter you then this is still very much an option. Similarly, if you have an abundance of processing power (or really need a space heater in your server room) performing complete column recalculations on every view shouldn’t bother you.
Swamps: If you have very specific requirements you could get away with only recoding a few functions, which would greatly reduce the chances of you disappearing forever. Alternatively, if you have a large herd of idle developers you could set about recreating the whole set. However, I doubt this is a serious option for anyone except the head of the Microsoft Silverlight team.
|
SharePoint Calculated Column Cheat Sheet
Date:May 19th, 2011 Author:Clare Tags: Calculated Columns, SharePoint 2010Category: Calculated Columns, SharePoint Ideas Comments:18 ;
Calculated columns are a really useful feature of any SharePoint list.
Whether you want to automatically show the profit on a sale, or the financial year that a date falls in to, or tidy up sloppy user input by capitalising names. There is a lot you can do it with a Calculated Column.
But remembering the formulas and functions needed is not always easy, especially before the first coffee of the morning. I know there is a way to get rid of the decimal places on a number but is it TRUN, or TRUNC, or TRUNK – oh no, hang on, that last one is just to do with elephants!
So, over the years we’ve gathered together a quick reference of some of the most commonly used functions and formulas – stuck on a wall above the desk it saves a lot of time and head scratching. We’ve found ourselves sending it to clients from time to time, and they seem to find it quite handy too.
So we decided to tidy it up – clean off the coffee stains and make a nice PDF of it – and make it available to download, free – yes that’s right, FREE!
|
So why not download our free Calculated Column Cheat Sheet and save yourself some time and headaches? This easy 3 page reference sheet gives you a selection of commonly used calculated column formulas, along with a pretty thorough reference of most of the functions and operators you are likely to need in your day to day work.
Download, print, stick it on the wall, and we hope you find it as handy as we do!
How to use [Today] in a SharePoint list
Date:May 12th, 2011 Author:Stuart Pegg Tags: Calculated Columns, Highlighter, Javascript, SharePoint, SharePoint Designer, SharePoint Highlighter, WorkflowsCategory: Calculated Columns, Highlighter, SharePoint Development, Training Comments:0 ;
Quite frequently we see questions about using the current date to display messages on a list when a date or time is (or will soon be) overdue. Most of these queries are from frustrated users who are trying to use [Today] in a calculated column, but find it missing.
I’ll quickly outline why this option is missing from calculated columns, and hence why the notorious [Today] trick is quite so controversial. But most importantly, I’ll run you through ways to actually display the information you want.
Why is [Today] missing from calculated columns?
When we look at a SharePoint list, the vast majority of us are instantly reminded of Excel (or Grandfather Excel, if you prefer). Because of this we quite reasonably expect it to act like Excel, and hence expect it to reconsider every formula and data item on the page each time we view it.
Unfortunately because of the much larger amount of information SharePoint has to pass back and forth, it takes a different approach to updating the data items. It will only update calculated values when the related item is added or edited.
Consider SharePoint as an overworked secretary; if you ask for a file to be updated they’ll find it in the filing cabinet, update the information, and correct anything else that’s awry on the paperwork while they’re there. If you ask for all the files for people named “Smith” to be put on your desk, unless you specifically say “And update the information on every single one while you’re at it”, it’s not likely they’ll do the extra work for no reason.
Because of this change in records-keeping, Microsoft had to skip functionality that would make this lower-maintenance updating method obvious. Since having a [Today] option would cause unedited items to go out of date every day, they had to leave it out. The [Me] option was another casualty, as it would require checking the current user every time the data was displayed.
So how do we get our files updated without further flustering our imaginary secretary? There are several options:
- JavaScript: Use a Content Editor Web Part. Also known as “Do it yourself.”.
- Designer: Create a custom view in SharePoint Designer. “Ask the secretary really nicely”.
- Code: Make your own custom field type from scratch. “Get your own team to do it”.
- SharePoint Highlighter: Our own solution to the problem. “Hire an extra secretary”.
JavaScript 
Of course there’s always a JavaScript solution, and this one in particular from Paul Grenier uses jQuery to help with the process. This will mean adding the jQuery libraries to your site, and it’s also necessary to add a Content Editor Web Part to each page you want the feature to work on.
A little JavaScript knowledge will probably be necessary to make it fit your requirements, and as with any code tweaking, the more you know the better the result will be.
Designer 
SharePoint Designer is a free and powerful (and hence often banned) tool from Microsoft for modifying your SharePoint site. Using Designer it’s possible to alter a view so that it uses Today when rendering the data on the page. This done by modifying the view’s XSLT (the template the view uses to decide what data goes where).
If you’d like a dabble to test the water of such changes, MSDN has a nice tutorial on how to get started.
However, if you already have some experience with Designer (or you’d like to just jump straight in), Greg Osimowicz has an article on using Today in XSLT to calculate holidays accrued to date (scroll down to “Below are the steps I followed:”).
Code 
There are a number of possible different methods to solving this problem using a custom field type. As always this comes with my standard disclaimer that developing a custom field type from scratch for a single purpose requires a disproportionate investment of time for the results.
In 2010 it’s possible to do the calculations in the custom field type class (with some light persuasion), however this is not possible with 2007 as it does not use the class to render its information on list views.
Unfortunately not even CAML can save us in this instance, as the View Schema doesn’t provide the current date in a usable form. But if you’re using 2010 you may be able to create a workaround with XSLT and Greg’s Designer XSLT solution in the section above.
The last option is to use the field type to get JavaScript onto the page instead of using a CEWP. This has the benefit that you don’t need to put the CEWP on each page, but as I mentioned in my previous article it can be very troublesome to do so: How to do list highlighting in SharePoint
SharePoint Highlighter 
Normally this is the section where I’d talk about the benefits of a commercial solution, and try to avoid mentioning SharePoint Highlighter too much (I might even link to our product comparison page). However, it’s very difficult to talk in general terms about commercial products on the market that have this functionality when yours is the only one that does.
So I’m not going to beat around the bush: If you want to buy a commercial solution to change your list view display based on today’s date, then SharePoint Highlighter is the only (and hence best) solution. It does much more than just this, but I’m not going to blow my trumpet too much.
If you want to see a real life problem and its solution using SharePoint Highlighter; have a look at this SharePoint Overflow question.
Using [Today] in SharePoint calculated default values
Date:September 2nd, 2010 Author:Ryan Tags: Calculated Columns, SharePoint, Tip, TodayCategory: Calculated Columns Comments:16 ;
Despite the infamous “fake today column trick” still appearing in new blog posts on a monthly basis you can’t use Today in calculated columns in SharePoint.
I’ve seen people assume (not unreasonably) that the same rule must apply with calculated default values used in columns that are not calculated columns as well – but strangely, you actually can use Today in calculated default values, so I am going to explain why it works and how you might use this.
What is the difference between Calculated Column and Calculated Default value?
A calculated column, erm… calculates values from other columns in your list.
For example if you have an orders list with a Quantity and Price column you can calculate a Grand Total of Price * Quantity.
With a calculated default value you use a calculation to set the default value for a column that is not a calculated column – this is the value that is entered automatically when a new list item is created and may be over-written by the user.
So why may I want a calculation using Today as the default value?
Suppose we have a list containing customer complaints and we have a target to resolve everything in 3 days. Sometimes though for complex queries or if there are public holidays we may give more time. In that case we can set the default value as
= Today + 3
But the user can override that if necessary (public holiday etc).
So why can I use Today here but not in calculated columns?
With a calculated column the calculation is only reworked every time a record is updated. When it’s just viewed then it picks up the saved result from the last edit. This makes perfect sense for most calculated values, but not for values using Today or Me.
So for example if we tried to calculate the days left until a due date we might want to use
Days left = Due Date - Today
If we added the complaint on Monday then it would show that we have 3 days left to do it (Thursday). If we just look at the record on the Wednesday it would still show 3 days (as this was the calculation saved on Monday) when in fact we only have 1 day left to keep the customer happy.
That’s why SharePoint wont let you use Today in calculated columns.
However with a default calculated value the result is only worked out once when the record is being created – it’s understood that what is saved in this field is not updated automatically so there is no problem with using Today.
For a bonus point – you can’t reference other fields in calculations for default values (e.g. Start Date or Order Date) as before a record is being created the fields don’t yet contain values.
What else could I do with this?
The OP in this thread is using it to set default financial years. Suppose that your company’s financial year starts in April then you could use this formula to default to 2009/10 or 2010/11 on or after April.
=IF(MONTH(Today)>=4,"2010/11","2009/11")
or generating the year 20xx/xx notation automatically :-
=IF(MONTH(Today)>=4,YEAR(Today) & "/" & (YEAR(Today)+1),(Year(Today)-1) & "/" & YEAR(Today))
It’s also worth noting that you can use a similar formula in calculated columns – suppose you have an Order Date field and the financial year depends upon when the order was placed then simply replace Today with Order Date.
NB – This has been tested with SharePoint 2007/2010.
| Free SharePoint Calculated Column Cheat Sheet |
| a handy 3 page reference for calculated column functions and formulas. |
|
How To Use Filters in SharePoint to show items in the current Calendar Month
Date:November 26th, 2009 Author:Ryan Tags: Calculated Columns, Filter, Tip, ViewCategory: 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.
Tip – Showing multiple columns in SharePoint Planner Webpart
Date:October 14th, 2009 Author:Ryan Tags: Calculated Columns, Formula, gantt, Planner, SharePoint, Tip, WebPartCategory: Calculated Columns, SharePoint Planner, SharePoint webparts Comments:1 ;
There may be times when you want to display timeline or category information from more than one list column in a gantt chart. For example in the chart below we have taken information from the “priority” and “title” columns of a list to populate the category labels.
With Planner you can show multiple columns in the Category or on the Timeline of a Gantt chart by using a calculated column to merge fields together.
For example to merge Priority and Title columns together you would :-
- From your list click Settings > Create Column
- Set a name such as “Category”
- Choose Calculated for the type
- In formula enter (where [Title] etc is the name of the columns you want to merge)
=[Priority] & " : " & [Title]
- Uncheck ‘Add to default view’ checkbox if you don’t want this appearing for the normal list view.
- In Planner you can now select this column.
You can merge multiple columns together, for example :-
=[Priority] & " : " & [Title] & " - " & [Assigned To]
You can also use SharePoint standard functions and formula (many similar to excel) such as :-
=Text([Start Time],"mmm-yyyy") " : " & [Title]
Which would prefix the Title with Month and year, like so October-2009 : Your title here
To display the the duration in days you could use
=DATEDIF([Start Time],[End Time],"d") & " Days"
