© 2011 By: Dov Trietsch. All rights reserved
Calculated Fields and some of their Idiosyncrasies
Did you try to write a calculate field formula directly into the screen? Good Luck – You’ll need it!
Calculated Fields are a sophisticated OOB feature of SharePoint, so you could think that they are best left to the end users – at least to the power users. But they reach their limits before the “Professionals “do, and the tough ones come back to us anyway.
Back to business; the simpler the formula, the easier it is. Still, use your favorite editor to write it, then cut it and paste it to the ridiculously small window.
What about complex formulae? Write them in steps! Here is a case in point and an idiosyncrasy or two.
Our welders need to be certified and recertified every two years. Some of them are certifiable…., but I digress. To be certified you need to pass an eye exam, and two more tests – test A and test B. for each of those you have an expiry date. When renewed, each expiry date is advanced by two years from the date of renewal. My users wanted a visual clue so that when the supervisor looks at the list, she’ll have a KPI symbol telling her if anything expired (Red), is going to expire within the next 90 days (Yellow) or is not to be worried about (green). Not all the dates are filled and any blank date implies a complete lack of certification in the particular requirement.
Obviously, I needed to figure the minimal of these 3 dates – a simple enough formula:
=MIN([Date_EyeExam], {Date_TestA], [Date_TestB]).
Aha! Here is idiosyncrasy #1.
When one of the dates is a null, MIN(Date1, Date2) returns the non null date. Null is construed as “Far, far away”. The funny thing is that when you compare it to Today, the null is the lesser one. So a null it is less than today, but not when MIN is calculated.
Now, to me the fact that the welder does not have an exam date, is synonymous with his exam being prehistoric, or at least past due. So here is what I did:
Solution:
Let’s set a blank date to 1/1/1800. How will we do that? Use the IF.
IF([Field] rel relValue, TrueValue, FalseValue).
rel is any relationship operator <, >, <=, >=, =, <>. If the field is related to the relValue as prescribed, the “IF” returns the TrueValue, otherwise it returns the FalseValue.
Thus: =IF([SomeDate]="",1/1/1800,[SomeDate]) will return 1/1/1800 if the date is blank and the date itself if not.
So, using this formula, if the welder missed an exam, the returned exam date will be far in the past.
It would be nice if we could take such a formula and make it into a reusable function. Alas, here is a calculated field serious shortcoming: You cannot write subs and functions!!
Aha, but we can use interim calculated fields!
So let’s create 3 calculated fields as follows:
1: c_DateTestA as a calculated field of the date type, with the formula:
IF([Date_TestA]="",1/1/1800,[Date_TestA])
2: c_DateTestB as a calculated field of the date type, with the formula:
IF([Date_TestB]="",1/1/1800,[Date_TestB])
3: c_DateEyeExam as a calculated field of the date type, with the formula:
IF([Date_EyeExam]="",1/1/1800,[Date_EyeExam])
And now use these to get c_MinDate. This is again a calculated field of type date with the formula:
MIN(c_DateTestA, cDateTestB, c_DateEyeExam)
Note that I missed the square parentheses. In “properly named fields – where there are no embedded spaces, we don’t need the square parentheses. I actually strongly recommend using underscores in place of spaces in all the field names in your lists. Among other things, it makes using CAML much simpler.
Now, we still need to apply the KPI to this minimal date. I am going to use the available KPI graphics that come with SharePoint and are always available in your 12 hive.
"/_layouts/images/kpidefault-2.gif" is the Red KPI
"/_layouts/images/kpidefault-1.gif" is the Yellow KPI
"/_layouts/images/kpidefault-0.gif" is the Green KPI
And here is the nested IF formula that will do the trick:
=IF(c_MinDate<=Today,"/_layouts/images/kpidefault-2.gif", IF(cMinDate<Today+90,"/_layouts/images/kpidefault-1.gif","/_layouts/images/kpidefault-0.gif"))
Nice! BUT when I tested, it did not work!
This is Idiosyncrasy #2: A calculated field based on a calculated field based on a calculated field does not work. You have to stop at two levels!
Back to the drawing board: We have to reduce by one level. How? We’ll eliminate the c_DateX items in the formula and replace them with the proper IF formulae. Notice that this needs to be done with precision. You are much better off in doing it in your favorite line editor, than inside the cramped space that SharePoint gives you. So here is the result:
MIN(IF([Date_TestA]="",1/1/1800,[ Date_TestA]), IF([Date_TestB]="",1/1/1800,[ Date_TestB]), 1/1/1800), IF([Date_EyeExam]="",1/1/1800,[Date_EyeExam]))
Note that I bolded the parentheses and painted them red. They have to match for this formula to work.
Now we can leave the KPI formula as is and test again. This time with SUCCESS!
Conclusion: build the inner functions first, and then embed them inside the outer formulae. Do this as long as necessary. Use your favorite line editor. Limit yourself to 2 levels.
That’s all folks!
Almost! As soon as I finished doing all of the above, my users added yet another level of complexity. They added another test, a test that must be passed, but never expires and asked for yet another KPI, this time in Black to denote that any test is not just past due, but altogether missing. I just finished this. Let’s hope it ends here! And OH, the formula
=IF(c_MinDate<=Today,"/_layouts/images/kpidefault-2.gif",IF(cMinDate<Today+90,"/_layouts/images/kpidefault-1.gif","/_layouts/images/kpidefault-0.gif"))
Deals with “Today” and this is a subject deserving a discussion of its own!
That’s all folks?! (and this time I mean it)
