Get SPELL Tabs 1.1 from my Office 365 site

I am starting 2014 with a new channel to distribute my SharePoint solutions.

Since 2011, I have been working on my SPELL program to address some shortcomings of the solutions published in the SharePoint User Toolkit. I made a number of choices that shaped the future of SPELL:

  • A single framework with interconnected modules, rather than independent code snippets.
  • Dual presentation, inline code for developers vs. “no code” for end users (encouraging the use of the Page Viewer Web Part, more solid and reliable across SharePoint versions than other content/script Web Parts).
  • Move away from the free model, to adopt a freemium approach with gratis (no cost) and full version.

Because the setup forms are now included in SPELL, the delivery method has switched from an online configuration tool to more standard file downloads. The natural move for me was to make the files available in a public SharePoint library, but several users got stuck because of the counterintuitive behavior. As an anonymous user, you have to use a right click, not a regular click, to access a file. The file extension might also change in the download process (for example from .aspx to .htm).

As a quick workaround, I started sending files by e-mail (!). Of course I got bitten by corporate filters that blocked e-mails because they contained code, or just delivered the e-mail without the attachment…

So I finally decided to use myself the solution I had been promoting among my SharePoint customers in the past 12 months. Ever since the new version of Office 365 came out, I have been praising its fantastic ability to go across company barriers and allow extended enterprise collaboration. I just worked on such a project last week! Office 365 ‘s sharing options make it an easy and cost effective solution. For some of my customers, this is actually the number 1 reason for migrating to Office 365.

If you are interested in the gratis version of the SPELL Tabs 1.1, go ahead and fill out the contact form with your company information (granted, at this point contact forms are not Office 365’s most powerful or user friendly feature). SharePoint will store this information in a list (restricted access), and notify me. I’ll then send you a “shared link” to access the package.

Distributing files is just the first step. I expect to move further later this year, and open other areas of the site to my customers and partners, for example for online support, discussions, or to vote on enhancement requests.

If you’d like to experiment with Office 365 ‘s external sharing abilities yourself,  you are welcome to contact me for a free 30 day trial. Even if your company is already using SharePoint internally, you might be interested in a separate instance of Office 365 for projects that involve external partners!

About SPELL Newsletter 8 – Cascading Selects

EditorThis week I sent out issue 8 of the SPELL Newsletter. The topic of the month is cascading selects.

The SPELL Newsletter now has 450 subscribers. Unfortunately, for some reason a dozen addresses are returning me a “delivery failed” message. If you have requested to receive the newsletter but didn’t get any message this week, please get in touch with me to confirm your e-mail address. In the meantime, I am attaching the documentation to this post (see link below).

SPELL is a freemium program (free entry level solutions, full solutions for a fee). Even if you are not willing to pay for SPELL, I think the document is a good read as it explains in detail the purpose of cascading selects, and how they are related to lookup columns. And for the sake of transparency it also lists some alternate tools that will help you achieve the same result (see “What are my other options” at the end of the document).

SPELL Cascading Selects 1 1 0 – Evaluation Version

Script insertion via a Page Viewer Web Part

Three years ago, I published a post to clarify how scripts (and Web content in general) could be added to a page via a Web Part. It was SP 2010 at that time, and the main contenders were the Content Editor Web Part (CEWP) and the HTML Form Web Part.

I concluded the post with a small note about the Page Viewer Web Part (PVWP). It’s about time I explain what I meant…

Note: this article focuses on Web Parts. There are of course other ways to include html, stylesheets or scripts in a SharePoint page!

What is a Page Viewer Web Part?

A PVWP allows you to embed in your page another Web Page. Technically, a PVWP doesn’t do much, it is just a wrapper for a html iframe element.

iframes have a bad reputation. One of the main reason is that people often confuse them with frames. An excerpt from the MDN page:

Using the <frame> element is not encouraged because of certain disadvantages such as performance problems and lack of accessibility for users with screen readers. Instead of the <frame> element, <iframe> may be preferred.

As a matter of fact, iframes are enjoying a surge in popularity in modern Web design. They serve as building blocks in modular pages (several SharePoint Web Parts rely on iframes), and are a key component of application pages (for example on Twitter, Facebook or with Disqus). I would expect them to become even more popular once the new iframe html5 attributes become widely available.

Note: besides linking to Web pages, the PVWP also has options to display folders or file content.

How is this similar to the CEWP?

As explained in my old post, a CEWP allows you to embed, via its content link property, Web content (html, stylesheets or scripts) in your page. A PVWP acts in a similar way, except that the linked file is a whole Web page, instead of a fragment stored in a text file.

But this means two different pages?

Right. With a CEWP, the code fragment becomes part of the host page. With a PVWP, the code remains in its own page. That said, it doesn’t mean that the two pages cannot communicate, within the boundaries allowed by the same origin policy. In particular, an iframe can access and modify its host page. In the basic example shown below, the code in the iframe accesses the parent body to change its background color.

ParentDocument

Any real life example to share?

Sure! In the past few months, I published in this blog several demos from my SPELL library. SPELL is built to work in dual mode: either “inline” for direct insertion within a page (CEWP, Master Page, etc.), or “app” for inclusion in an iframe. For example, the process navigation showcased in this blog post is inserted via a PVWP. Same for its sibling featuring a button navigation. Other solutions like the SPELL Tabs or mini-BI work exactly the same way.

Why use a PVWP rather than a CEWP?

That’s a good question. While other environments have a good case for iframed scripts, we SharePoint users can already rely on the CEWP to link to html, css and JavaScript. Still, I see a couple benefits when using a PVWP.

1/ Sandbox

Because the iframe loads another page, this provides a clear separation between the host page and the iframe content. The sandboxed environment avoids code conflicts. This can be for example useful:

  • for charting apps, to make sure that the styles of the main page don’t interfere with the styles of the chart itself.
  • for scripting: if your app requires a specific jQuery version, you can run it within the iframe, without having to worry about the main page running a different version.

2/ Standalone

One constraint with Web Parts is that they have to live in a page. Picture a SharePoint dashboard: you view it as a whole, with no easy option to isolate a specific chart. With a PVWP, the chart can be viewed either embedded in the dashboard or directly in its underlying page. This scenario is showcased in this post: with read only permissions on my site, you cannot build a SharePoint dashboard page, but you can still interact with the individual chart page.

Even better, standalone pages work great with SharePoint dialogs. Iframing is actually the technique SharePoint relies on with forms: when you open a new, display or edit form in a dialog, you are simply opening newform.aspx, dispform.aspx or editform.aspx in an iframe! Do I have a dialog example with SPELL? Thanks for asking 🙂 You’ll find it here. Working with standalone pages allows to pile up dialogs and create the drill-down effect.

3/ Asynchronous load

Because the host page and the iframe are two different pages, the browser can load them in parallel, so this “should” result in better performance. With a CEWP, the content becomes part of the host page and execution will follow the page flow.

4/ Links across site collections

The CEWP allows you to link to an external file, but only within a same site collection. An iframe doesn’t have such restrictions, so a single application page stored in SharePoint can be reused across site collections.

5/ Storage of parameters

This is a technique I use with the SPELL library – not really standard but let me explain it anyway.

Because the script running in the iframe can access its own url, this url becomes a place where you can store parameters. For example the URL for the SPELL Tabs will look like this:

Tabs.aspx#css.activeBackground=orange;css.inactiveBackground=blue;css.hoverBackground=lightblue

For cascading dropdowns, the url would be for example:

CascadingSelect.aspx#form.parentLabel=State;form.childLabel=City;source.listName=Locations;source.parentField.DisplayName=State

You might find the above urls intimidating, but if you look at SharePoint urls they actually work in a similar fashion. The point is: if you used a CEWP, you’d need to store one snippet of code for each cascading dropdown you implement. With the above method, one single page called CascadingSelect.aspx can be reused across all site collections!

Note:  In practice the SPELL library includes editors, the above paths are not built manually.

Of course, iframes also have some drawbacks. The asynchronous load complicates the relationship between the iframed content and the parent’s content. The sandbox means that if the same code is needed in both the host page and the iframe, locally you have to load it twice (or find a trick…). Both the CEWP and the PVWP have their place in SharePoint design!

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.

Quick Demo: Process Navigation for SharePoint Forms

ProcessSteps

This month I am releasing version 1.1 of the SPELL library, with two major module rewrites. One of them is the SPELL Form module, which allows to enhance the out of the box SharePoint forms, and now includes features such as cascading selections and inline navigation. I’ll have more code to share next month, but for now let me just share a quick demo.

When we refer to inline navigation, we usually think tabs, like what we’ve been accustomed to with the Easy Tabs. The SPELL Nav and SPELL Form modules will render tabs by default, but beyond that they also have the ability to integrate other designs. For today’s demo, I have chosen a type of navigation often found in forms, and usually referred to as “process navigation” or “process steps”, where each “tab” is displayed as an arrow.

Click here for the live demo. Note that such designs rely on css properties that might not work in older browsers.

For the record, I had to fake the form, as you cannot display the out of the box forms on an Office 365 public site (!).

Quick update: more pocketSOAP testing

RetrieveWebPartsThe other day, someone on twitter made fun of the name I chose for my demo library, pocketSOAP. That’s ok, and I am actually glad that the name matches the intent. As I have already said, pocketSOAP is just a toy to experiment with SharePoint Web Services.

Something else that sounds funny: people sometimes ask me which services are supported by pocketSOAP (see for example the twitter link above), and my answer is… I don’t know. At the end of the first pocketSOAP article, I listed a number of existing libraries that abstract SOAP services. All those libraries follow the same technique and hardcode the service call. pocketSOAP and its parent library SPELL are radically different: instead of having the information hardcoded, they go and fetch it on SharePoint. That explains why the libraries are so lightweight. By the way SPELL is the smartest of the two, and when it learns a new service it memorizes it for next time.

In the past few weeks, I had the opportunity to do more testing based on users’ requests. I confirmed that my libraries worked fine for the following calls:

  • Lists.asmx:
    • GetListItems: retrieves items from SharePoint lists
  • WebPartPages.asmx:
    • GetWebPartProperties2: collects the list of Web Parts present on the page and their properties
    • SaveWebPart2: saves changes made to the properties of a Web Part (e.g. its title)
  • Workflow.asmx:
    • GetTemplatesForItem: collects the list of workflows for a given list
    • StartWorkflow: allows to trigger a workflow

The code for WebPartPages and Workflow is more intricate than what I posted in my previous pocketSOAP articles, so I am not sharing it here. If you have registered to the SPELL interest list, you’ll receive two entry level demos with the August newsletter:

  • a Web Part Page Quick Editor (features the WebPartPages service)
  • a button that triggers workflows (features the Workflow service)

So far so good, all the services I have tested have responded just fine. Now waiting for the next challenge!

HTML Calculated Column + Client-Side Rendering

Five years after the first release, the HTML Calculated Column remains the most popular topic on this blog.

The original page has been visited more than 200,000 times. It is definitely outdated, and in recent years I have pushed several new variations of this technique. The most popular is the color coding solution posted in the SharePoint User Toolkit, backed by this tutorial.

The most frequent issue reported by users has been the upgrade from SharePoint 2007 to SharePoint 2010. This is actually all taken care of in the above links… but you need to read the fine print.

I plan to rewrite the instructions, especially as in the meantime Microsoft has pushed another version of SharePoint, and the SP 2010 update… doesn’t work with SP 2013! Well, there’s actually a simple fix for SP 2013, and “Panoone” posted it as a comment a couple days ago (@Panoone: thanks again! And let’s get in touch to discuss this further).

But that’s not all. SP 2013 brings a bunch of new client side technologies, and one of them works very well in our case: Client-Side Rendering.

What is Client-Side Rendering?

JSLinkEditYou can use Client-Side Rendering (CSR) in SharePoint to manipulate the rendering process of list views. Does this sound familiar? That’s exactly what my HTML Calculated Column has been doing for years! Except that now it is an official component integrated with list views. When you edit a Web Part, the very last option is the JS Link placeholder (see screenshot).

It will certainly take several weeks before I find time to update the SharePoint User Toolkit, so for those of you who are already familiar with both CSR and the HTML Calculated Column, let me share the code for SP 2013.

In the code, Calculated is the name of the calculated column.

var ccContext = {
  Templates: {
    Fields: {
      "Calculated": {"View": "<#=STSHtmlDecode(ctx.CurrentItem.Calculated)#>"}
    }
  }
};
SPClientTemplates.TemplateManager.RegisterTemplateOverrides(ccContext);

So easy, just 200 characters! STSHtmlDecode() is a JavaScript function provided by SharePoint that allows the conversion of the html string.

Of course, you could avoid using a calculated column, and build the html directly in the JS Link file. But then each rendering would require a different file. The beauty of the HTML Calculated Column is that one single JS Link file can support all the views in your site.

A warning!

Some time ago, I wrote about security risks when you use the HTML Calculated Column. As far as I can tell, the same warning applies to Client-Side Rendering. Handle with care!

CSR references

When Microsoft released SharePoint 2013, the documentation on Client-Side Rendering was quite poor. Fortunately SharePoint bloggers stepped in and you can find some nice posts to get familiar with CSR. Wes Preston’s blog is an excellent start.

So is the old way dead in SharePoint 2013?

Not quite yet! Not every view accepts Client-Side Rendering, and for example you’ll still find my tutorial helpful for calendar views.

More pocketSOAP examples: Pie and Bubble Charts

A couple weeks ago, I introduced pocketSOAP, an experimental JavaScript library that facilitates interactions with SharePoint SOAP services.

Today let me share two more examples built on SOAP and the templating engine, combined with Google Visualization Charts, to render charts from SharePoint lists.

If you have subscribed to the SPELL newsletter, you’ll find pocketSOAP in your mailbox this week. Keep in mind that it is an experimental library, only the full SPELL library is meant for production environments (cf. disclaimer in the previous post).

I am not expecting you to be impressed by the screenshots below. After all, I’ve been publishing client side charting demos in this blog since 2008, and since then many others have followed. The real kicker here is how compact and versatile the code is. Even if you are not interested in client side scripting for a production environment, a tool like SPELL/pocketSOAP could prove really useful in a prototyping phase, and save you many hours of hardcore (re)programming!

Example 1: Pie Chart
PieChart
For this first example, I used the same SharePoint list as in the previous post, with two columns:

  • the default “Title” for the item name
  • “Priority” as a choice field with 3 options (high/medium/low)

The code:

<div id="results"></div>
<script type="text/javascript" src="pocketSOAP.min.js"></script>
<script type="text/javascript" src="//www.google.com/jsapi"></script>

<script type="text/javascript">
// Load the Google charting library
google.load("visualization", "1", {packages:["corechart"]});
// Get items from the Projects list
var promisedItems = pS.soap({
 // Service info
 site:"https://usermanaged.sharepoint.com/TestZone",
 service:"Lists",
 operation:"GetListItems",
 // Service parameters
 listName:"Projects",
 viewFields:"<ViewFields><FieldRef Name='Title' /><FieldRef Name='Priority' /></ViewFields>"
});
pS.when(promisedItems)
.then(function(request){
 // When ready, run the charting function
 google.setOnLoadCallback(function(){drawChart(request);});
});
 function drawChart(request) {
 // The templating engine formats the data string according to Google format
 var dataString=pS.applyTemplate({
 template:'[["Project","Priority"][<|,["|Title|","|Priority|"]|>]]',
 prefix:"ows_",
 data:pS.byTagNS(request.responseXML,"row","z")
 });
 var dataTable = google.visualization.arrayToDataTable(JSON.parse(dataString));

// Group by priority. Priority is the second column (index = 1)
 var data = google.visualization.data.group(dataTable, [1], [{'column': 1, 'type': 'number', 'aggregation': google.visualization.data.count}]);
 var chartOptions = {'title':'Priority','width':400,'height':300,colors:['red','#FFC200','green']}; // #FFC200 is the color code for amber
 var chart = new google.visualization.PieChart(document.getElementById('results'));
 chart.draw(data, chartOptions);
}
</script>

The SOAP call is almost the same as in the previous post. The only difference is that I have explicitly told SharePoint which fields I want to retrieve, via the viewFields option. This is not mandatory but it will reduce the size of the dataset and improve performance.

The use of the templating pattern (pS.applyTemplate) to convert data formats is unusual – as a matter of fact I have never seen this used elsewhere. I am not advertising it as a best practice, and more traditional object manipulation could be used here to the same effect. But I really like how templating makes the code easier to maintain.

Templating is a very popular pattern in modern JavaScript, and can be found in many libraries, like jQuery, mustache, handlebars and others. The SPELL/pocketSOAP flavor might not be as powerful as those big names, but in just 1 kb of code it still has a lot to offer, and – as you would expect – it is well adapted to work with SharePoint Web Services, for both XML and JSON output (to use it with JSON instead of XML, simply replace the [< >] notation with [{ }]).

Example 2: Bubble Chart

BubbleChart

For this second example, I created a SharePoint list with 5 columns:

  • the default “Title” for the product name
  • “Risk” as a choice field with 3 options (low/medium/high)
  • “Cost” and “Revenue” are numbers
  • “Profit” is a calculated column:
    [Profit]=[Revenue]-{Cost]

The code:

<div id="results"></div>
<script type="text/javascript" src="pocketSOAP.min.js"></script>
<script type="text/javascript" src="//www.google.com/jsapi"></script>

<script type="text/javascript">
// Load the Google charting library
google.load("visualization", "1", {packages:["corechart"]});

// Get items from the PMO list
var promisedItems = pS.soap({
 // Service info
 site:"https://usermanaged.sharepoint.com/TestSite",
 service:"Lists",
 operation:"GetListItems",
 // Service parameters
 listName:"PMO"
});

pS.when(promisedItems)
.then(function(request){
 // When ready, run the charting function
 google.setOnLoadCallback(function(){drawChart(request);});
});

function drawChart(request) {
 // The templating engine formats the data string according to Google format
 var dataString=pS.applyTemplate({
 template:'[["Product","Cost","Revenue","Risk","Profit"][<|,["|Title|",|Cost|,|Revenue|,"|Risk|",|Profit|]|>]]',
 prefix:"ows_",
 data:pS.byTagNS(request.responseXML,"row","z"),
 sanitize:function(string){return string.replace(/float;#/,"");}
 });

var dataTable = google.visualization.arrayToDataTable(JSON.parse(dataString));

var chartOptions = {
 title: 'PMO',
 width:500,
 height:400,
 colors:['green','#FFC200','red'], // #FFC200 is the color code for amber
 legend:{position:'top'},
 hAxis:{title: 'Cost',maxValue:100},
 vAxis:{title: 'Revenue',maxValue:100}
 };

var chart = new google.visualization.BubbleChart(document.getElementById('results'));
 chart.draw(dataTable, chartOptions);
}
</script>

Note in the template the difference between text fields (for example “|Title|”) and number fields (for example |Cost|). Also, this time I had to include a sanitize function to remove the weird “float;#” string that SharePoint prepends to calculated columns.

The above code samples could easily be tweaked to work with other client side charting libraries, like Dojo, HighCharts, or [name-your-own]. If you are interested in a demo, leave a comment with the name of your favorite charting tool!