The Problem
In re-designing our Intranet, we wanted to showcase everything exciting about the modern experience and digital workplace provided by Office365.
GTconsult has always prided itself on innovative solutions that focus on performance, accessibility and ease of use, as such we wanted to embrace the new technology platform and see how it worked for us.
Our Findings –
- Using the Out Of the Box toolset provided great capabilities with PowerApps and flow to capture our leave from an App and to send for approval.
- The Capture method on O365 benefited from the work done in PowerApps.
- Viewing aggregated information about all staff leave balances and calculation was lacking.
- Simple usability, filtering and search did not provide the greatest performance.
So, this was a real mixed bag of results, so we went on a journey to figure out how to improve this. One of the toolset we generally use in our Applications is* jQuery libraries***, they provide great richness in the user experience as well as improved usability with inline filtering. We decided we wanted to use that experience, so we had 3 options to add it to our application:
- Embed an ASPX page with custom code; 1. This can be slow and the experience can be undermined by the embed limitations of height and width of webpart, and the solution is not reusable, its designed for a single purpose.
- Add a Modern Experience Script Editor webpart 1. This is the equivalent of using a Content Editor webpart in SharePoint Apps of old. The experience is also Single Purpose and is often flawed when editing the page resulting in some duplications on the page.
- Add a SharePoint Framework webpart that reads a list and returns the data in a react framework that we can modify to incorporate our jQuery libraries. 1. This seemed the most practical for reuse but also fitted the best to the new design tenants as we could reuse the solution not just in SharePoint but also in Teams (once SharePoint Framework 1.7 hits production).
The solution
As you would have guessed, we decided to go with the third option due to the benefits. We use the SharePoint Framework libraries (https://github.com/SharePoint/sp-dev-fx-webparts) extensively as the base for any of our webparts, as often you can leverage off great designs and concepts to take you close to where you need to go. In this case we used the React content Editor Webpart. We extended this webpart to include Datatables.Net JavaScript to give use our functions. We also had to get creative in terms of the Datatable.net to allow grouping of data and aggregating which are not OOTB concepts with the datatables tools.
Building a filterable, aggregated table with SharePoint Framework and Datatables
Step 1 – Set up your site and list
In our scenario, we were setting up a leave list and our end goal was to monitor all leave and leave balances against all staff. As such we created a List with Leave Type, Staff Member, Date From, Date To and No of days. How you set up your approvals and flows is essentially up to your process, we know from experience that each organization manages leave in its own way and very rarely follows the same process.
Step 2 – Deploy your SharePoint Framework React Content Query Webpart
We started by taking the sample from the SPFX webparts library and customizing the webpart to be GTconsult specific, providing custom messages, making the hosted CDN libraries local etc. These steps can be found in the documentation on GitHub and will not be the focus of this blog as they are well documented already (https://github.com/SharePoint/sp-dev-fx-webparts/tree/master/samples/react-content-query-webpart).
Once you have made the adjustments you need to deploy your solution to your App Catalog on O365 and add the app to your site and we are ready to go.
Step 3 – Configure your webpart
Once you have added the webpart to the site you should be able to configure the webpart on a new Modern experience page. The webpart will be called React Content Query webpart unless you have customized it prior to deploying
Figure 1 – Configuring the List settings
- You will begin by choosing the list location where you are storing the data, select the Site / Web and List Title
Figure 2 – Configure Filters
- The next you configure your filters as you would a view in SharePoint, if you want all items you can skip this step, but remember if your view goes over 5000 items you are going to have issues!
Figure 3 – Configuring the Look and feel
- Set up the fields you want to show, this is important as you will need these values to customize the look and feel. You choose the columns you wish to see (remember though that if you are building links you might need additional columns like the ID, so plan what fields you will need). 1. By default a set of blocks are displayed as the result – these are useful to check the fields you are getting, but are not the best looking or usable
Once you happy with the data being returned and the fields you require you can edit the look and feel by selecting the “Edit Template”. The templates are built up using Handlebar which is pretty self explanatory and powerful, however to see more examples of how to use Handlebar – checking this out – https://github.com/helpers/handlebars-helpers- We are going to substitute the original HTML template as per below with our own that returns the data in a table rather than the divs provided, Yes I know Tables – those are so 1990’s, but our tables are going to be flexible and responsive due to the technology we apply afterwards. Don’t be tempted to add links to JavaScript and functions in here, this will not work and will result in hours of troubleshooting to no avail.
Figure 4 – Default Template
<table style="height: 2772px;" width="846"><tbody><tr><td width="623"><link rel=”stylesheet” type=”text/css” href=”https://cdn.datatables.net/1.10.15/css/jquery.dataTables.min.css” /><style type=”text/css”>
.dynamic-template .dynamic-items .dynamic-item {
background: #ffffff;
box-shadow: 0px 0px 6px #bfbebe;
margin-bottom: 15px;
}
.dynamic-template .dynamic-items .dynamic-item h3 {
background: #47b4de;
color: #fff;
padding: 5px 5px 7px 10px;
margin: 0px;
}
.dynamic-template .dynamic-items .dynamic-item .dynamic-item-fields {
padding: 10px;
}
.dynamic-template .dynamic-items .dynamic-item .dynamic-item-fields span {
display: block;
font-size: 12px;
}
tr.group,
tr.group:hover {
background-color: #ddd !important;
}
tr.subgroup,
tr.subgroup {
background-color: cornsilk !important;
}
tr.subgroup:hover {
background-color: #ddd !important;
cursor:pointer;
}
tr.thedata{
display:none;
}
</style>
<div class=”dynamic-template”>
<h2>All Users Leave</h2>
<table id=”searchable-table” class=”display”>
<thead>
<tr>
<th>Staff</th>
<th>Type of Leave</th>
<th>First Day</th>
<th>Last Day</th>
<th>Status</th>
<th>No of Day</th>
</tr>
</thead>
<tbody>
{{#each items}}
<tr class=’thedata {{replace StaffMember.textValue ” ” “”}}_{{TypeOfLeave.textValue}}’>
<td>{{StaffMember.textValue}}</td>
<td>{{TypeOfLeave.textValue}}</td>
<td>{{FirstDay.textValue}}</td>
<td>{{LastDay.textValue}}</td>
<td>{{Status.textValue}}</td>
<td>{{NoOfDays.textValue}}</td>
</tr>
{{/each}}
</tbody>
</table>
</div>
</td></tr></tbody></table>
Figure 5 – New Table code
- We are now ready to go – however you will see the new table isn’t too pretty and not much more functional than the original Div boxes. We are now going to add the magic, how to add your code and functions to make things work for you. We will add external scripts – in this case we are using JQUERY, DATATABLES and our own functional script called ST.JS, you can obviously add any jQuery component that you like, making this approach extremely flexible. Make note that it helps to add the script locally, I have had serious trouble pointing to CDN scripts.
Figure 6 – Adding your Scripts
- Designing the ST.JS – You will now need to initiate your JavaScript function. This has to be done in a particular way because of the way that data loads in the SharePoint Framework Webpart. The data needs to be rendered and the function called post the render using the react onPostRender function.
<table><tbody><tr><td width="623">ReactContentQuery.ExternalScripts.st2 = {
onPreRender: function(wpContext, handlebarsContext) {
// Do someting before rendering (ie: adding a custom block helper)
console.log(‘prerender’);
},
onPostRender: function(wpContext, handlebarsContext) {
// Do something after rendering (ie: calling a plugin on the generated HTML)
console.log(‘postrender’);
var table = $(‘#searchable-table’).DataTable({
“order”: , ],
“pageLength”: 100,
drawCallback: function ( settings ) {
var api = this.api();
var groupadmin = ;
var tableRows = api.rows( {page:’current’} ).nodes();
var lastGroup = null;
var lastSub = null;
var mySubGroup = null;
var count = 0;
$(tableRows).each( function () {
groupName = this.cells.innerHTML;
mySubGroup = this.cells.innerHTML;
status = this.cells.innerHTML;
if ( lastGroup !== groupName ) {
$(this ).before(‘<tr class=”group”><td colspan=”6″>’+ groupName +'</td></tr>’);
lastGroup = groupName;
}
if (lastSub !== mySubGroup) {
cleanname = lastGroup.replace(‘ ‘,”);
lastSub != null ? $(this ).before(‘<tr class=”sumgroup”><td colspan=”5″ user=”‘+lastGroup+'”><b>’+ lastSub +’: ‘+count+'</b></td><td><a onclick=”javascript:OpenSesame(”+cleanname+’_’+ lastSub +’’)”>Break Down</a></td></tr>’):console.log(‘no grouping’)
$(this).before(‘<tr class=”subgroup ‘+cleanname+’_’+ mySubGroup +'” style=”display:none;”><td colspan=”6″>’+ mySubGroup +'</td></tr>’);
//$(“#”+groupName+”_”+lastSub).val(count);
groupadmin.push(mySubGroup);
lastSub = mySubGroup;
count = 0;
}
status == ‘Approved’ ? count += Number(this.cells.innerHTML): console.log(‘not approved’)
});
}
});
}
}
function OpenSesame(subclass){
$(“.”+subclass).toggle();
}
1)
</td></tr></tbody></table>
Our resulting webpart using the SharePoint Framework with jQuery and Datatables to present a far better performance oriented and usable list of data. With a couple of tweaks to the DATABLE code we Group by each of the users and aggregate their leave balances, whilst also extending the framework to accordion the results in the table
Figure 7 – Final Webpart result