How to get your Office 365 version number

Last week, the Office 365 home page of one of my customers suddenly turned blank.

In the past, my first reaction in this situation was to ask the user what they had changed, and fire the developer tools on IE or Chrome. I often work with power users, and as they say, they know enough about SharePoint to be dangerous!

These days however, my first move is to check the Office 365 version number.

In Office 365, Microsoft is now pushing minor updates on a regular basis, without your consent or even letting you know. Result: my customer’s Office 365 is different from my own Office 365, and also different from his neighbor’s Office 365.

What happened in my customer’s case is that he was on version 16.0.0.3002 while I and others were still on version 16.0.0.12xx. The page went back to normal within 24 hours, so I guess there was a bug with the release and Microsoft fixed it.

A similar case happened 3 months ago, when Marc Anderson reported on his blog a change in SharePoint pages that I couldn’t see. It turned out that here too the version he was using was different from mine.

With Office 365 rolling release model, such situations are becoming common, and when you report an issue to your advisor you should expect a “it works on my cloud” reply. So my recommendation, whenever something unexpected happens on your Office 365 pages, is to check the version number as part of the debugging process. How? Simply by appending /_vti_pvt/service.cnf to your SharePoint domain. In my case for example, to get my Office 365 version number, I would type the following url:

https://UserManaged.SharePoint.com/_vti_pvt/service.cnf

If you’d like to have that url handy on your site, simply add a link, for example in the quicklaunch, with /_vti_pvt/service.cnf as URL (no need to include your domain name).

If like me you work across multiple Office 365 sites, you can add a bookmarklet to your usual browser. In Chrome for example, go to the Bookmark Manager, add a page, and the URL field enter:

javascript:window.location.href=”/_vti_pvt/service.cnf”;

Side comment: I’d really like Microsoft to use us advisors, rather than the end users, as guinea pigs, and push Office 365 updates to us first!

Advertisements

Trick or treat? Group items by month

OrderedMonthsIt’s that time of the year again when the unnatural becomes the norm, so let’s continue the tradition started last year with the SPELL program. Our goal today will be to display list or library items grouped by month, as shown in the screenshot.

The deal is that we don’t want any custom code or workflow here, just the regular out of the box UI features. We’ll be creating two calculated columns, called Year and Month, where we’ll insert the appropriate formulas. I chose the “Modified” column for my sample formulas, but of course any other date column would work too.

The YEAR and MONTH functions

YearNumber

A quick review of the SharePoint date functions gives you YEAR and MONTH that should fit the bill:

Year:  =YEAR(Modified)
Month: =MONTH(Modified)

The result is less than satisfactory though (see screenshot), as the year is displayed with a thousands separator. and months are displayed as numbers.

The magical TEXT function

MixedMonthsFurther exploration will take you to the TEXT function. It is not very well documented in SharePoint, fortunately you can rely on the Excel documentation and come out with the following formulas:
Year:  =TEXT(Modified,"yyyy")
Month: =TEXT(Modified,"mmmm")

Still not happy with the result? Right, the months are displayed in alphabetical order, not sequential order, not yet an ideal experience for our end user.

So let’s pull our last trick, and use the following formula for the calculated month:
Month: =REPT(" ",13-MONTH(Modified))&TEXT(Modified,"mmmm")

You can see the final result live on this page.

What’s the trick? We are still relying on the out of the box alphabetical sorting, but to force the order, we are adding a bunch of white spaces before the month name. The calculated Month actually contains the following values (each _ represents a white space):
____________January
___________February
__________March

__November
_December

Now, why don’t we see these spaces on the Web page? What makes the magic work is that when you insert multiple spaces in a Web page, the html specification says that

user agents should collapse input white space sequences

That’s it!

If you want to get really fancy, you could even use the zero-width space character. The best part is that people who edit your formula won’t even understand the trick, as the zero-width space won’t be visible (there’s however a good chance that they break your cool formula).

To take this further

You can apply this trick to other situations. A typical example is a color code. The alphabetical order will give you Green-Red-Yellow or Amber-Green-Red, you can address that by adding the appropriate leading spaces.

Be careful with this technique though: even if the rendering looks fine, the spaces are indeed stored in the field, and this might break other customizations. So this trick is better kept in a calculated column that will be exclusively used for rendering purposes.

Trick or Treat? Text to html, the wicked no-code way (Part II)

At the end of the previous post, we had this aha moment when we realized that rendering a field as rich text might not be that difficult, after all.

I’m back, after taking the time to set up a brand new demo to confirm our assumptions:

Demo

 

It works! As you guessed, just using a SharePoint Designer workflow to copy the content of the calculated column to a rich text field did the trick. For the record, here are the detailed steps:

  1. Create a list (I used the Tasks template here).
  2. Create a calculated column called cc, with the html formula. For the above demo I used calculated gradients (just did a copy/paste of the formula in the post).
  3. Create a column of type “Multiple lines of text” called HTMLcc, and specify that the type of text is “Enhanced rich text”.
  4. Create a one step workflow that copies the cc column to the HTMLcc column, every time an item is created or modified (screenshot below)
  5. We’re done!

 

The benefit of this approach is that we now have a clean field that renders our graphic – no ugly code around. I left the cc field visible for the purpose of the demo, but in production I would naturally make it a hidden field.

Although this looks great and easy, there are a couple gotchas to be aware of, if you decide to use this trick.

First, it is rather heavy: two fields and one workflow! And of course it won’t work for you if SharePoint Designer is not allowed in your environment (or you’ll need another way to copy cc to HTMLcc).

Second, it won’t help in views such as calendars, as these views don’t render rich text fields.

Third, as with any workflow action there’s a latency. The HTMLcc field will not refresh as quickly as other fields.

One last, important point: SharePoint is going to “sanitize” the html you copy to the HTMLcc field, and might decide to remove some of the content. This can actually be considered a good thing, as it addresses a security concern I reported in a previous post. On the other hand, it will block some script-based enhancements, like hover effects or countdowns.

Enjoy, and see you for next year’s trick: how to sort a list by month name. Happy Halloween!

Trick or Treat? Text to html, the wicked no-code way

As I am actively working on a new training program called SPELL, it recently occurred to me that I should make Halloween a special day. So let’s start the tradition, the SPELL program annual trick or treat!

I have described many times my HTML Calculated Column technique on this blog. The idea is to use a calculated column to build html content. You have certainly already tried them out, for example for color coded calendars, progress bars, or KPIs.

The catch is that SharePoint won’t naturally interpret the string as html, so extra work is needed to do the conversion – usually done using JavaScript or xslt.

Now, rendering an html string as actual html is something any rich text editor can do, why is it so complicated with SharePoint? Oh yes, here is the thing: the output of calculated columns is just plain text. Ah, if only I could change it to rich text! Or find a way to transfer the content of my calculated column to a field that understands rich text!

But…Wait…

[To be continued]

Workaround for the SP 2010 calendar resize bug

In SharePoint 2010, the calendar views are rendered via a script on the client side. This is a significant improvement over the heavy calendars of the previous versions (2003 and 2007), and in the 2013 version client side rendering has been extended to all list views.

Unfortunately, the SP 2010 calendar also came with a bug that I reported a couple years ago. It is made of two layers – one for the grid and one for the events – and when the calendar gets resized the two layers get out of sync. Really annoying! As far as I know, Microsoft has never fixed this. I keep receiving reports of this bug from users, especially as it occurs when calendar views are combined with the Easy Tabs.

If you have to deal with this annoying behavior, here is a simple workaround: go to the Web Part settings and give the Web Part a fixed width.

Hope this helps!

Maximum number of IF statements in a Calculated Column?

If you said seven, or eight, you spoke too fast…

I just answered a question on the Stump the Panel forum, and thought others could be interested.

The context

I recently published a new version of my tool for color coding calendars and lists. This tool generates a formula that associates each item of a choice list with a color:

=IF([Status]=””,”Black”,IF([Status]=”Choice1″,”Red”,IF([Status]=”Choice2″,”Gold”,IF([Status]=”Choice3″,”Green”,IF([Status]=”Choice4″,”DarkBlue”,IF([Status]=”Choice5″,”DarkCyan”,IF([Status]=”Choice6″,”DarkRed”,IF([Status]=”Choice7″,”Gray”,””))))))))

For example, if the [Status] column contains Choice5, the output of the formula will be DarkCyan.

The issue

In practice, users often have more than 7 items in their choice list. For 8 choices, the formula becomes:

=IF([Status]=””,”Black”,IF([Status]=”Choice1″,”Red”,IF([Status]=”Choice2″,”Gold”,IF([Status]=”Choice3″,”Green”,IF([Status]=”Choice4″,”DarkBlue”,IF([Status]=”Choice5″,”DarkCyan”,IF([Status]=”Choice6″,”DarkRed”,IF([Status]=”Choice7″,”Gray”,IF([Status]=”Choice8″,”Yellow”,””)))))))))

But if you try it, here is the message SharePoint throws back at you:
The formula contains a syntax error or is not supported.

So here is the sad truth: you cannot have more than seven nested IF statements in a calculated column.

When users discover this – by trial and error, via a forum, or from their experience with Excel – the next step for them is usually to try and break their formula across multiple columns, each containing a maximum of seven nested IFs. However, in most cases there is a simpler way.

The workaround

We have seen that a calculated column doesn’t allow more than seven nested IFs. But what if they are not nested? Let’s give it a try with my formula generator:
First pass: choice1 to choice7
=IF([Status]=””,”Black”,IF([Status]=”Choice1″,”Red”,IF([Status]=”Choice2″,”Gold”,IF([Status]=”Choice3″,”Green”,IF([Status]=”Choice4″,”DarkBlue”,IF([Status]=”Choice5″,”DarkCyan”,IF([Status]=”Choice6″,”DarkRed”,IF([Status]=”Choice7″,”Gray”,””))))))))
Second pass: choice 8 to choice 14
=IF([Status]=””,”Black”,IF([Status]=”Choice8″,”MediumSlateBlue”,IF([Status]=”Choice9″,”SpringGreen”,IF([Status]=”Choice10″,”MidnightBlue”,IF([Status]=”Choice11″,”Sienna”,IF([Status]=”Choice12″,”SlateGray”,IF([Status]=”Choice13″,”OliveDrab”,IF([Status]=”Choice14″,”Gray”,””))))))))

Now, I just have to concatenate these two using the & symbol:

=IF([Status]=””,”Black”,IF([Status]=”Choice1″,”Red”,IF([Status]=”Choice2″,”Gold”,IF([Status]=”Choice3″,”Green”,IF([Status]=”Choice4″,”DarkBlue”,IF([Status]=”Choice5″,”DarkCyan”,IF([Status]=”Choice6″,”DarkRed”,IF([Status]=”Choice7″,”Gray”,””))))))))&IF([Status]=””,”Black”,IF([Status]=”Choice8″,”MediumSlateBlue”,IF([Status]=”Choice9″,”SpringGreen”,IF([Status]=”Choice10″,”MidnightBlue”,IF([Status]=”Choice11″,”Sienna”,IF([Status]=”Choice12″,”SlateGray”,IF([Status]=”Choice13″,”OliveDrab”,IF([Status]=”Choice14″,”Gray”,””))))))))

There is still something wrong as the “” (no selection) option appears twice. So I need to remove the redundant one (in orange above, don’t forget the last parenthesis) to get my final result:

=IF([Status]=””,”Black”,IF([Status]=”Choice1″,”Red”,IF([Status]=”Choice2″,”Gold”,IF([Status]=”Choice3″,”Green”,IF([Status]=”Choice4″,”DarkBlue”,IF([Status]=”Choice5″,”DarkCyan”,IF([Status]=”Choice6″,”DarkRed”,IF([Status]=”Choice7″,”Gray”,””))))))))&IF([Status]=”Choice8″,”MediumSlateBlue”,IF([Status]=”Choice9″,”SpringGreen”,IF([Status]=”Choice10″,”MidnightBlue”,IF([Status]=”Choice11″,”Sienna”,IF([Status]=”Choice12″,”SlateGray”,IF([Status]=”Choice13″,”OliveDrab”,IF([Status]=”Choice14″,”Gray”,””)))))))

And voila, a formula that fits in one calculated column, with 15 IF functions!

“That’s cool, Christophe! So… when can we see this in your formula generator?”
Ok, I’ll update it… but seriously, is it reasonable to use more than 7 colors for color coding?

Other options

For the record, IF is not the only SharePoint function allowing to build conditional formulas. See for example in this post how I used the CHOOSE function. And yes, there will be another formula generator built on CHOOSE!

A method to customize Web Part zones

Warning: the script below contains a time bomb. Be sure to read the whole article before using it.

A question from the STP forum: how can I change the layout of my Web Part zones, without SharePoint Designer or server side access? For example: on the home page (default.aspx), I only want one column. Or I want two columns with the same width (50%).

In the past, I have already answered specific cases. Today I am proposing a more generic approach.

In a SharePoint page, there is nothing special that identifies Web Part zones. When you look at the page html, zones are just regular table cells. My idea is to include in the Web Part zone a CEWP (Content Editor Web Part) that will act as a tracking device. It will identify its host zone and then apply customizations to it. btw this technique is similar to the one used for the Easy Tabs.

Here is a sample script including two examples:

<script id="RightWPzone" type="text/javascript">

/*************************************
  Customize the Right Web Part zone
  Christophe Humbert
  Christophe@PathToSharePoint.com
*************************************/

function GetParentTable(elem)
{
while(elem !=null && elem.tagName !="TABLE") elem=elem.parentNode;
return elem;
}

var thisWPzone = GetParentTable(GetParentTable(document.getElementById("RightWPzone")).parentNode);

if (thisWPzone.id != "MSOZone") { // Remove this line if the customizations also apply in edit mode

var thisWPzone = thisWPzone.parentNode;

// Example 1: set the Web Part zone width
thisWPzone.style.width = "50%";

// Example 2: hide the Web Part zone
try {thisWPzone.previousSibling.style.display = "none";} catch(err){}
thisWPzone.style.display = "none";

} // Remove this line if the customizations also apply in edit mode

</script>

Simply add a CEWP to your Web Part zone, add the above script, and change it as needed (the main tool for these customizations is the style object). 

When you reuse the script for several zones on your page, make sure that each script has its own id (the id appears twice in the script). In my sample script the id is “RightWPzone”.

A time bomb, you said?

Let’s say we decide to hide a Web Part zone, both in edit and view mode (so that nobody is tempted to drop a Web Part there):

<script id="RightWPzone" type="text/javascript">

/*************************************
  Hide the Right Web Part zone
  Christophe Humbert
  Christophe@PathToSharePoint.com
*************************************/

function GetParentTable(elem)
{
while(elem !=null && elem.tagName !="TABLE") elem=elem.parentNode;
return elem;
}

var thisWPzone = GetParentTable(GetParentTable(document.getElementById("RightWPzone")).parentNode);
var thisWPzone = thisWPzone.parentNode;
thisWPzone.previousSibling.style.display = "none";
thisWPzone.style.display = "none";
</script>

So far so good…but what if I need to make changes? Well, I’m stuck, as I can’t access the zone content anymore, even in edit mode! Fortunately – and this is one reason why I love working with CEWPs – there’s always the option to undo the customization. You can remove the Web Part from the page, with this well known trick:
…/default.aspx?contents=1
If you append the ?contents=1 querystring to the URL, you’ll be sent to the maintenance page, where you can manage your Web Parts.

Another way to deal with this issue is to apply a “best practice”: don’t put the code directly into a CEWP, place it in a separate text file instead, and link the CEWP to it (cf. this article for more details).