SharePoint Lists Views + JS Links + Data Tables = Awesome Threesome
SharePoint Lists Views
SharePoint lists are the primary building blocks when setting up a SharePoint site. Pretty much any system is designed to takes and give information and SharePoint relies on lists to make that happen.So list are important, it stores the information you want to share, it sources workflows and forms and it defines how things relate to each other. We rely on List Views to interact with these lists which is great, but these views are created by SharePoint and gives us limited control in how we can configure the interface, which is a shame because sometimes the lists are just too important to rely of the basic configurations.
Thankfully, Microsoft gave us JS links, an ability to influence the way the views are rendered via JavaScript.
JS Links
This opens a great new world of possibilities, we can now use JavaScript to read the data from the list view, check if certain conditions are met, and if these conditions are met, we can change the information that needs to be displayed.For example, if you have a SharePoint list with a column that stores a RAG indicator, i.e. Red, Amber or Green indicator, you can, via JS Links, check the indicator value and display an image with the appropriate colour instead of the boring text.
And that’s the big advantage of JS Links, by being able to change the design of the list view, you can provide a much better user experience. Better user experience leads to better adoption, less room for errors and ultimately, greater success.
When it comes to JavaScript, there’s awesome open source libraries available (you can find them here: https://www.javascripting.com/). You may know the more popular ones like jQuery, React.js and Angular.js. I want to draw your attention to a library called Data Tables, a highly customisable tool that adds advanced interaction controls to HTML tables.
Data Tables
Data Tables, https://www.datatables.net/, are awesome, with a single line of code, you point this JavaScript library to a HTML table and it auto-magically provides search capabilities, sorting, styling and paging, it also stores everything in memory so the paging, searching and sorting works very fast, even with large dataset.So if you are looking for alternatives to the standard List Views, then the Data Tables JavaScript library is a great option. The views provided by SharePoint are still tops and should be used most of the time, but there are scenarios where I would see Data Tables as the better alternative, these scenarios are:
- For consuming, not necessary creating information – the greater range of styling options available via Data Tables makes this tool a great option for presenting information
- Enhanced User Experience – getting into the code of what is displayed gives you better control when defining the interface
- Working with large amount of data (but not too large) – with the data stored in memory, less database calls are needed when paging, searching or sorting making it a great choice for large, but not massive lists will take a long time to load into memory so there is a threshold to consider. I am assuming that 500 items is a good number for Data Tables, but will set the scene for some threshold testing.
So how do we get this Awesome Threesome?
1. Setting up the SharePoint List
First, let’s setup a SharePoint list. I want to see how Data Tables work with large lists, so I Googled and found an Excel file (https://community.tableau.com/servlet/JiveServlet/downloadBody/1236-102-1-1149/Sample%20-%20Superstore%20Sales%20(Excel).xls) with 21 columns and 8 400 rows of data, definitely overkill, since best practice dictates that SharePoint lists should not exceed 5 000 rows, but this will help us determine what threshold is acceptable and I want to see how Excel and SharePoint will handle this volume of data.To export this data to SharePoint, format the data as a table, select a cell in the table, select Design (if you don’t see the Design option, then you did not format the data as a Table), Select Export and then Export Table to SharePoint List (Confused? See image below).
Specify the SharePoint URL and List details and “Ta Daa!” the list exported to SharePoint successfully. (For this article, I used SharePoint Online via my O365 subscription and I defined a new list called “DataTableDemo”).
For those interested, uploading 8 400 rows to SharePoint Online took 12 minutes to complete, that’s pretty good.
Excel provided a nice progress bar for the first 75%, then it looked like it crashed with a “Not Responding” status. I ignored that message and checked the SharePoint site. I could see the new library and I could see the number of items increasing every time I refreshed the page, so it was still working. It finally stopped at 8 399 rows (I counted the header row which makes 8 400) and Excel got out of the “Not Responding” Status and in its place showed a wonderful “Export Successful” message with a link to the new list.
Given the volume of data I was exporting and where I was exporting it to, the way Excel handled it was impressive. Well done Microsoft!
Its good practice to create a new view rather than modifying the default “All Items” view. So I created a new, standard view called DataTableView with all the defaults selected. I will be working with this view going forward.
2. Setting up the JS Link
Now we need to configure the list to reference our JavaScript Code via JS LinkFirstly, SharePoint needs to be configured to accept Custom Script. To do so, navigate to the SharePoint Admin Centre and set the "Custom Script" option to "Allow".
Also, Modern views do not support JS Links, so you need to change the list settings from Modern Experience to Classic Experience. To do so, navigate to your list, then go to List Settings > Advanced Settings and select Classic Experience in the List and Libraries Experience section.
The system needs around 24 hours to fully apply all these changes. So log off and come back tomorrow to see if everything is working. This is important, I spent whole night trying to figure out why some options were not available, only to discover that I needed to wait it out.
You will know that all is configured correctly when you can navigate to your List view, edit the page, edit the web part that is displaying the list, and see the JS Link textbox under the Miscellaneous section.
Since we are here, lets add a reference to the JS file that we will use. Full paths will not work (SharePoint trims it to a relative path and does a poor job at that), so you need to reference a file in the site collection with the following syntax “~sitecollection/[Library]/[filename].js”
Now, open your site via SharePoint Designer and navigate to the Site Assets folder, create a blank file with a .JS extension.
I named my file DataTableJS.js. (so in the JS Link text Box, I entered ~sitecollection/SiteAssets/DataTableJS.js).
Muawiyah Shannak provides a great set of JS Link samples: The articles and code can be found here, https://code.msdn.microsoft.com/office/Client-side-rendering-JS-2ed3538a, and It’s an excellent resource that provides JS Link sample code from basic tasks to pretty sweet advanced stuff.
I will be focusing on the code that he used to transform a SharePoint list View into an Accordion style view (Sample 6). But instead of transforming the list into an Accordion, I’m going to transform it into a Data Table. Its pretty much the same concept, and I see Muawiyah’s code helping me the same way I help my wife: He will get me 95% of the way there, with the remaining 5% I have to do on my own. (Yes, I help my wife code ;).
3. Setting up the Data Tables
Muawiyah Shannak samples are clear and complete and I encourage you to go over there first to better understand how code via JS Link works. I won’t cover that, but I will talk about the adjustments I made to his Accordion view code (https://code.msdn.microsoft.com/office/Client-side-rendering-code-ccdb2a0e).First, here are a few things you need to do before you start writing code.
1. Know that web browsers cache .js files. So when you are editing your file and you want to see the results in the browser, refreshing the browser is not good enough. You must always do a “Clear Cache” refresh by pressing Ctrl+Shift+F5 or Ctrl+Shift+R.
2. The columns that you want to display MUST be selected for display in the View Settings section. If it’s not in the View, it’s not available for the Data Table to use and you will be presented with an “Undefined” error. I have selected a few columns of different types so we can see how they are handled.
3. In extension to point 2 above, de-select all the columns you will not use in the Data Table, otherwise you are giving SharePoint unnecessary work and if you don’t treat SharePoint well, he won’t treat you well.
4. The View must have an Item Limit that is high enough to send ALL the rows you want to display in the Data Table, not just the rows you want to display on the first page. The DataTable will handle the paging for you, but it needs all the data up front to store that into memory. I set the Item Limit for my DataTableView to 500 for now and will check acceptable thresholds at a later stage, most likely in a later blog post.
5. Know that Data Table library will only work on valid, well formatted HTML tables. These tables need a header (thead) and a single body (tbody), an optional footer (tfoot) can also be used. Here’s the outline that we need to build:
<table id="table_id" class="display">
<thead>
<tr>
<th>Column 1</th>
<th>Column 2</th>
</tr>
</thead>
<tbody>
<tr>
<td>Row 1 Data 1</td>
<td>Row 1 Data 2</td>
</tr>
<tr>
<td>Row 2 Data 1</td>
<td>Row 2 Data 2</td>
</tr>
</tbody>
</table>
So, with all the prep work done, let’s look at the code.
1. This code links the Data Table JS library to the correct HTML table. The table been referenced has an id of “DataTableMain” (I defined this id in section 3 below).
This is the standard code that you use when using Data Tables (as explained here: https://datatables.net/examples/basic_init/zero_configuration.html).
I kept this area separate because DataTables are highly configurable and if these examples: https://datatables.net/examples/index, inspires you take your DataTable to the next level, this is where you will do it.
2. This is the full line (cut off from the screenshot above):
“
(window.jQuery || document.write('<script type="text/javascript" charset="utf8" src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script><script type="text/javascript" charset="utf8" src="https://cdn.datatables.net/1.10.16/js/jquery.dataTables.min.js"></script><link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.16/css/jquery.dataTables.min.css"><script type="text/javascript">'+dataTableBinding+'</script>'));
“
This code references jQuery (a pre-requisite for the Data Table library), the Data Tables library, the Data Table Stylesheet and the code created in point 1 above.
Note that SharePoint Online will block all http references, so if you are using a CDN reference, make sure it’s a secured https reference.
3. This is the full line (cut off from the screenshot above):
“
dataTableContext.Templates.Header = "<table id=\"DataTableMain\" class=\"display\"><thead><tr><th>Order Priority</th><th>Order Quantity</th><th>Order Date</th><th>Customer Name</th><th>Product Name</th><th>Modified By</th><th>Action</th></tr></thead><tbody>";
”
Here we are replacing the header and footer components with our own code.
The code that we are applying is building the main shell for a valid, well-formed HTML table with an id of “DataTableMain” (so we can reference it to the Data Table library, as done in point 1 above) and a class of “display” (so we can use the default stylesheet available)
4. This is where we populate the HTML table with the data available via the SharePoint View.
This code is in a function that runs for each item in the View (so in this case, it will run 500 times).
SharePoint may see columns names differently to how we see it, and in code, we must reference the columns the way SharePoint sees it.
To see how SharePoint has named these columns “for internal use”, go to the List Settings section, select a column, as if you want to edit it, look for the “Field” parameter in the URL QueryString, and you will see the column name the way SharePoint recognises it.
So in the screenshot above, the Order Priority column is recognised as “Title” within SharePoint, so that's what I looked for when I populated the dataOrderPriority variable. Do this check for all your columns, you may be surprised on how SharePoint referenced them.
For the Order Date column (a date column), I provided code that shows you how to change the format of the date. The format that I used is probably the best one to use if you want to use the sorting feature for that column.
I included the Modified By column, so I could provide code on how to handle a People column. I extracted the name and email address so I can create a mailto link in section 5 below.
5. This is the full line (cut off from the screenshot above):
“
return "<tr><td>"+dataOrderPriority +"</td><td>"+dataOrderQty +"</td><td>"+dataOrderDate +"</td><td>"+dataCustomerName +"</td><td>"+dataProductName +"</td><td><a href='mailto:"+dataModifiedByEmail+"'>"+dataModifiedByName +"</a></td><td><a href='EditForm.aspx?id="+dataID +"'><image src='/SiteAssets/edit.png' alt='Edit'></a></td></tr>";
”
The code exits the function with a table row that is appended to the HTML table that we are building.
In the code, you will see that I am adding link that will allow the user to edit the list item. I added this because the standard way to edit a list item, i.e. via the Ribbon Bar, stopped working when we replaced the header and item portion of the display template with our own code.
SharePoint no longer knows when an item is selected, so it won’t enable the edit button in the ribbon. I’m sure we can write code via JavaScript that will “educate” SharePoint on what is selected, but I haven’t figured out how SharePoint is doing this, and since the edit button that I am providing is an acceptable workaround (it even has a cool icon image that I stored in the Site Asset library), I lost the motivation to study the default code. But if anyone knows how to do this, please mention in the comments.
And that’s it, the animation below shows the result.
I think that this is a pretty powerful alternative that gives me great control on what I want my end users to see and how I want them to see it. The interaction with the Data Table with 500 items is very fast (so I can see that it can handle a lot more)
With only 40 odd lines of code, I believe that this option is a true winner that I am sure that you will see and start using this as well.
Comments