With virtualized scrolling, Kendo Grids can be used to allow users to visualize and analyze data with extremely large datasets. A Kendo Grid with virtual scrolling minimizes the number of records by only retrieving records to populate the records within the browser window viewport which vastly improves rendering performance. In production, I use these virtualized grids, with a large number of columns, to render up to 75 million records! This article will demonstrate all of the steps necessary to deploy these powerful grids and teach you how to implement server-side paging, sorting, and filtering.
Kendo Virtual Grid Example
This virtual Kendo grid has over 150k records showing all of the major countries, cities, and states. Server-side sorting and paging are supported, and each column in the grid provides filters to allow the user to quickly retrieve the records. Both the live demo and the code are available below.
Kendo Grids with virtualized scrolling provide an enormous benefit allowing users to visualize data with millions of records in an easy-to-use and powerful HTML5 web-based grid. I use these types of Kendo grids often in enterprise finance departments to allow the administrators to analyze Payroll Subledger data. These grids have sorting and powerful search and filtering capabilities allowing users to isolate records from extremely large datasets. However, there are a few limitations to be aware of.
The main drawback to virtual grids is that users can't scroll over a million or so records, however, I don't know of a single user that will voluntarily scroll beyond several thousand records! The user will always seek to search the data instead of having to scroll through a ridiculously large set of records. To overcome this limitation, you can also provide server-side paging and implement column filtering as we have done here.
Differences Between Kendo Grid Pagination and Virtualization
To improve performance, Telerik suggests either using pagination or virtualization, but what is the actual difference here? Both techniques offer significant performance increases by limiting the amount of data required while rendering the Kendo grid. However, using virtual grids is the way to go if you are offering grid filtering or sorting (and with large datasets- you should). The reason is that while the pagination does limit the number of records, the default behavior sorts the data on the client side requiring all of the data to be analyzed before applying the search filters or sort order. On the other hand, since all of the data operations are offloaded to the server when using column virtualization, the client-side rendering is much more performant. Also, the database is generally superior in handling and messaging a large volume of data. As a general rule of thumb, if you are using more than 20k records, you should always consider using a virtual grid or having server-side pagination.
Kendo Virtual Grid Overview
The Kendo virtual grid sends a string of arguments as JSON to the server to determine which records should be displayed. The grid sends new arguments to the server whenever the user scrolls past a certain point or when sorting or filters are made. The server will take this JSON string and query the database to retrieve the records that need to be displayed. In this example, we will be using ColdFusion on the server. I have built many different functions to process the logic on the server. If you are using ColdFusion, this process is pretty much plug-and-play. Using this approach, implementing a virtual Kendo grid should only take 15 minutes or so. If you are using a different server-side language, such as PHP, I will explain the backend logic in a future article so that you develop your own logic to handle server-side logic for the Kendo virtual grid.
Client Side Logic
If you have been following along with our previous Kendo articles, the client-side code should be familiar to you and will explain the highlights of the code below. We are only going to highlight some important parts of the code, you may want to open up the code window and push it to the side when reading this to follow along.
CSS to Ensure that all Rows are Uniform in Size
Since the events are fired based on the user's current scroll position, each row in the grid should be uniform in height. This is accomplished using CSS by either eliminating word wrapping or making the grid wide enough so that no row wrapping exists. In this example, we are setting the minimum grid width to be at least 1190 pixels wide and explicitly setting the height of the row, we are also eliminating white space wrapping.
<style>
/*horizontal Grid scrollbar should appear if the browser window is shrunk too much*/
#WorldDbGrid table
{
min-width: 1190px;
}
.k-virtual-scrollable-wrap td {
font-size: 14px;
white-space:nowrap;
line-height: 13px;
}
#WorldDbGrid .k-virtual-scrollable-wrap tr td {
height: 15px
}
</style>
Using a Kendo Template to Display the Country's Flag
<!-- Kendo Template to display the flag next to the country name -->
<script type="text/x-kendo-template" id="country-flag">
<div class="country-flag">
<img src="/common/assets/flags/png250px/#: data.ISO2 #.png" width="20" height="14"/>
</div>
</script>
The Kendo DataSource
The virtual grid Kendo data source is nearly identical to the data sources handling the non-virtual grids. The differences are that we are using:
The server arguments, serverPaging, sorting, and filtering, defer all of the processing to the server. As we just mentioned, these settings send a string of arguments in JSON to the server when an action is needed to be performed. Schema.data refers to the data handle in the JSON string that contains the data object. Schema.total determines how many records we need to display. We will delve into the details of these settings later in the article.
Since we are using virtual scrolling (see the notes in the grid initialization below), server paging is optional. However, here we are using both virtual scrolling and serverPaging. You can use the two options in tandem and set the page size to determine how far the user can virtually scroll. In this example, the user can scroll a bit but will come to the end of grid when the user is at the 100th record (set in the pageSize argument). The pageSize setting must be set higher than the number of rows that can visually be seen in the grid without scrolling.
Note that the parameterMap logic is needed when using virtual grids. The rest of the settings have been covered in prior articles and should be self-explanatory.
// Datasource Declaration ---------------------------------------------------------------------------------
WorldDbDs = new kendo.data.DataSource({
type: "json",
serverPaging: true, // when set to true, the grid will automatically post the following json string to the processing page {"take":100,"skip":0,"page":1,"pageSize":100}
serverSorting: true,
allowUnsort: true,
filterable: true,
serverFiltering: true,
pageSize: 100, // If server paging is properly set above, the grid should send the following arguments to the cfc: "{"take":100,"skip":0,"page":1,"pageSize":100}"
allowCopy: true,
reorderable: true,
resizable: true,
columnMenu: true,
transport: {
read: {
// Note: since this template is in a different directory, we can't specify the subledger template without the full path name.
url: "<cfoutput>#cfcUrl#</cfoutput>?method=getWorldDbForKendoGrid", // /cssweb/applications/subLedger/subLedger.cfc?method=getVBarInProgressForGrid the cfc component which processes the query and returns a json string.
dataType: "json",
contentType: "application/json; charset=utf-8", // Note: when posting json via the request body to a coldfusion page, we must use this content type or we will get a 'IllegalArgumentException' on the ColdFusion processing page.
type: "POST"
},
parameterMap: function (options) {
return JSON.stringify(options);
}
},//transport
cache: false,
schema: {
total: "total", // Needed on virtual grids. The total and data are being returned by the cfc. Note the total and data vars are wrapped in quotes.
data: "data", // 'Data' is being returned by the Kendo virtual grid helper function.
model: {
id: "CountryId", // Note: in editable grids- the id MUST be put in here, otherwise you will get a cryptic error 'Unable to get value of the property 'data': object is null or undefined'
fields: {
CountryId: { type: "number", editable: false, nullable: false },
Country: { type: "string", editable: false, nullable: false },
Capital: { type: "string", editable: false, nullable: false },
ISO2: { type: "string", editable: false, nullable: false },
ISO3: { type: "string", editable: false, nullable: false },
StateId: { type: "number", editable: false, nullable: false },
State: { type: "string", editable: false, nullable: false },
CountryId: { type: "number", editable: false, nullable: false },
CityId: { type: "number", editable: false, nullable: false },
City: { type: "string", editable: false, nullable: false },
CityLatitude: { type: "number", editable: false, nullable: false },
CityLongitude: { type: "string", editable: false, nullable: false }
}//fields:
}//model:
}//schema
});//feedsDs = new kendo.data.DataSource
Grid Initialization
Most of these settings have been covered in prior articles, but there are a few things that are required when using virtual grids:
Virtual grids must use scrollable virtual:true. Since serverPaging is set to true in the Kendo data source, the user will have to click on the paging button at the bottom of the grid after scrolling to 100 records.
serverPaging is optional when using virtual scrolling, however, having endless scrolling can be confusing, so we are also using serverPaging here.
Setting the grid height is necessary when using virtual grids, and all visible columns are filterable allowing the users to search the records in the grid.
The flag column uses the Kendo template that we created earlier in this example to display the flag.
Unlike all of the other articles, the server-side logic required for virtual grids is quite extensive. I have developed various ColdFusion-based functions that are used that make implementing a virtual grid with ColdFusion a breeze, however, there is a lot of logic that takes place behind the scenes here. This approach also only works with MS SQL Server.
Create a Flat Table or a View as the Datasource
The approach that I have developed requires a SQL Server flat table or view. Here we are using a view that denormalizes our Country, State City SQL Server database. My virtual grid ColdFusion components only work with MS SQL server, and this MySql database was converted to MS SQL for this example.
Download ColdFusion Templates from GitHub
I have created four different files on GitHub to use to create Kendo virtualized grids.
You also need ou CfJson component if you don't already have it. This ColdFusion component is used for almost every Kendo widget that consumes JSON data using a ColdFusion service endpoint. This ColdFusion component is found at https://github.com/GregoryAlexander77/CfJson.
The first function (getWorldDbForKendoGrid) relies upon the KendoUtils.cfc component containing the core logic to prepare the SQL statements based on the JSON arguments sent by the Kendo grid.
Finally, the ColumnProperty.cfc component is used by the function to determine the column datatype.
Create the ColdFusion Service Endpoint
The service endpoint, called from the Kendo Datasource, needs some minor modification to get it to work in your environment. This function relies upon the files that need to be downloaded above.
The ColdFusion-based server endpoint requires 4 arguments and you must specify the columns that you are selecting.
The tableName should specify the name of the flat table or view.
Use the same table name as the tableAlias. This will create an alias name for the table. I put this in the logic as I wanted to use the same table for multiple grids and wanted to distinguish the queries separately.
The primaryKey argument is used to identify the selected row in the grid and is used if you use the grid for editing or having a master-detail interface. I will explain this concept later.
The defaultOrderByStatement allows you to specify how you want the records ordered. In this example, I am ordering the data by country.
You need to specify all of the columns that you want in the sqlStatement. The column names must be in the proper case, and be sure to leave the 'SELECT * FROM ( SELECT' string at the top of the page. I may recode this with an extra requiredColumns argument to make it easier in the future, but I'll leave the code that I use in production alone for now.
You should not have to touch any of the code underneath the sqlStatement argument.
<!--- Function to populate the grid --->
<cffunction name="getWorldDbForKendoGrid" access="remote" returnformat="json" output="false">
<!--- There are no arguments for this function. --->
<cfsetting enablecfoutputonly="true" />
<!--- Set params --->
<cfparam name="take" default="100" type="string">
<cfparam name="skip" default="0" type="string">
<cfparam name="page" default="1" type="string">
<cfparam name="pageSize" default="100" type="string">
<cfparam name="whereClause" default="" type="string">
<cfparam name="sortStatement" default="" type="string">
<cfparam name="searchFilter" default="false" type="boolean">
<cfparam name="logSql" default="true" type="boolean">
<!--- The name of the view (or a table that is derived from a view. --->
<cfset tableName = 'ViewCountryStateCity'>
<cfset tableNameAlias = 'vCountryStateCity'>
<cfset primaryKey = "CityId">
<cfset defaultOrderByStatement = 'ORDER BY Country'>
<!--- Get the number of records in the entire table, not just the top 100 for display purposes. We will overwrite this later if there are any new filters applied. --->
<cfquery name="getTotal" datasource="#dsn#">
SELECT count(#primaryKey#) as numRecords FROM [dbo].[#tableName#]
</cfquery>
<cfset totalNumRecords = getTotal.numRecords>
<!--- Make the query. Input the select statement *without* the from clause (or any other clauses) here. --->
<cfset sqlStatement = '
SELECT * FROM
( SELECT
CountryId
,Country
,Capital
,Currency
,CurrencyName
,ISO2
,ISO3
,Flag
,Latitude
,Longitude
,StateId
,State
,StateFlag
,Type
,CityId
,City
,CityLatitude
,CityLongitude
,CityFlag
'>
<!--- Note: you should not have to touch the following lines of this code. If you want a custom query name other than 'data', you will have to adjust tthe query name in two places. --->
<!---
Get the HTTP request body content.
The content in the request body should be formatted like so: {"take":100,"skip":9300,"page":94,"pageSize":100,"sort":[{"field":"ref2","dir":"desc"}]}
NOTE: We have to use toString() as an intermediary method
call since the JSON packet comes across as a byte array
(binary data) which needs to be turned back into a string before
ColdFusion can parse it as a JSON value.
--->
<cfset requestBody = toString( getHttpRequestData().content ) />
<!--- Double-check to make sure it's a JSON value. --->
<cfif isJSON( requestBody )>
<!--- Deserialize the json in the request body. --->
<cfset incomingJson = deserializeJSON( requestBody )>
<!--- Invoke the createSqlForVirtualGrid method in the kendoUtils.cfc component that will send back sql clauses. --->
<cfinvoke component="#KendoUtilsObj#" method="createSqlForVirtualGrid" returnvariable="sqlStruct">
<cfinvokeargument name="jsonString" value="#requestBody#">
<cfinvokeargument name="dsn" value="#dsn#">
<cfinvokeargument name="tableName" value="#tableName#">
</cfinvoke>
<cfif structFind(sqlStruct, "take") neq ''>
<cfset take = structFind(sqlStruct, "take")>
</cfif>
<cfif structFind(sqlStruct, "skip") neq ''>
<cfset skip = structFind(sqlStruct, "skip")>
</cfif>
<cfif structFind(sqlStruct, "page") neq ''>
<cfset page = structFind(sqlStruct, "page")>
</cfif>
<cfif structFind(sqlStruct, "pageSize") neq ''>
<cfset pageSize = structFind(sqlStruct, "pageSize")>
</cfif>
<cfif structFind(sqlStruct, "whereClause") neq ''>
<cfset whereClause = structFind(sqlStruct, "whereClause")>
</cfif>
<cfif structFind(sqlStruct, "sortStatement") neq ''>
<cfset sortStatement = structFind(sqlStruct, "sortStatement")>
</cfif>
<cfif structFind(sqlStruct, "searchFilter") neq ''>
<cfset searchFilter = structFind(sqlStruct, "searchFilter")>
</cfif>
</cfif><!--- <cfif isJSON( requestBody )> --->
<!--- Build the over order by statement. Make sure that a closing bracket ')' is at the end of the string. --->
<cfset overOrderStatement = ',ROW_NUMBER() OVER ('>
<cfif sortStatement neq ''>
<cfset overOrderStatement = overOrderStatement & sortStatement & ")">
<cfelse>
<!--- Default order by. --->
<cfset overOrderStatement = overOrderStatement & defaultOrderByStatement & ")">
</cfif>
<!--- Append it to the sqlStatement --->
<cfset sqlStatement = sqlStatement & " " & overOrderStatement>
<!--- Build the alias for the rownumber(). I am defaulting to 'as rowNumber' --->
<cfset sqlStatement = sqlStatement & " AS RowNumber">
<!--- Append the real and alias table name --->
<cfset sqlStatement = sqlStatement & " FROM [dbo].[" & tableName & "]) " & tableNameAlias>
<!--- Append the additional WHERE clause statement to it if necessary --->
<cfif whereClause neq ''>
<cfset sqlStatement = sqlStatement & " " & preserveSingleQuotes(whereClause)>
</cfif>
<!--- Log the sql when the logSql is set to true (on top of function) --->
<!--- <cfif logSql>
<cfset filePath = subledgerPath & 'logs'>
<cffile action="append" addnewline="yes" file="#filePath#/virtualGridSql.txt" output="#Chr(13)##Chr(10)#'#myTrim(sqlStatement)#'#Chr(13)##Chr(10)#" fixnewline="yes">
</cfif> --->
<!--- Testing carriage. If this is not commented out, the grids will not populate. --->
<cfoutput>#preserveSingleQuotes(whereClause)#</cfoutput>
<cfquery name="data" datasource="#dsn#">
#preserveSingleQuotes(sqlStatement)#
</cfquery>
<!--- Write the sql to the console log for debugging. Note: if you write this out- it will break the grid, so only do so in development.
<cfoutput>
<script>
if ( window.console && window.console.log ) {
// console is available
console.log ('#preserveSingleQuotes(sqlStatement)#');
}
</script>
</cfoutput>
--->
<!--- Using my jsonArray.cfc --->
<cfinvoke component="#application.cfJsonComponentPath#" method="convertCfQuery2JsonStructForVirtualGrid" returnvariable="jsonString" >
<cfinvokeargument name="queryObj" value="#data#">
<cfinvokeargument name="includeTotal" value="true">
<!--- When we use server side paging, we need to override the total and specify a new total which is the sum of the entire query. --->
<cfinvokeargument name="overRideTotal" value="true">
<!--- We set the totalNumRecords near the top of the function, however, if the filters were applied, the new total will be the number of records in the data query object. --->
<cfif searchFilter>
<cfset totalRecords = data.recordcount>
<cfelse>
<cfset totalRecords = totalNumRecords>
</cfif>
<cfinvokeargument name="newTotal" value="#totalRecords#">
<!--- The includeDataHandle is used when the format is json (or jsonp), however, the data handle is not included when you want to make a javascript object embedded in the page. --->
<cfinvokeargument name="includeDataHandle" value="true">
<!--- If the data handle is not used, this can be left blank. If you are going to use a service on the cfc, typically, the value would be 'data'--->
<cfinvokeargument name="dataHandleName" value="data">
<!--- Keep the case of the columns the same as the database --->
<cfinvokeargument name="convertColumnNamesToLowerCase" value="false">
</cfinvoke>
<cfreturn jsonString>
</cffunction>
In a future article, I will analyze the code on the server that prepares the data for this function. The goal of this article is to allow developers to fully implement a Kendo virtual grid without detailed elaboration on the backend.
In this article, we will cover advanced grid functionality and show you how to create an editable grid with cascading dropdowns with a custom header template. Kendo Templates allow us to essentially place widgets inside of a grid widget. Simply put- using these Kendo UI templates allows us to use the grid as a container to hold other Kendo UI widgets.
Like our other articles, we will be using ColdFusion as the backend- however, as we have stated before, Kendo UI is server agnostic and you can use the same techniques learned here with other server-side technologies.
Please see the example below to see a live demonstration of this code. The code is exhaustive, and we will do our best to cover all essential logical elements here.
Realtime Example
This example is a proof of concept intended to be used by the administrator to determine what categories are used for the CfBlogs Blog Aggregator found at cfblogs.org. The domain dropdown specifies a broader category type, here I am using Lucee and ColdFusion. The category domain is part of the RSS 2 specification and it is an optional category element. This optional element is rarely used, however, it will allow the Cfblogs logic to potentially determine which subcategory to be used when aggregating the posts.
If the category domain is 'ColdFusion' in this example, the administrator is allowed to select any category that exists in the CfBlogs database. I am grabbing all of the RSS categories when aggregating ColdFusion-related posts; there are thousands of them. If the user selects 'Lucee', I am defaulting to the subcategories found in the Lucee documentation. Once the domain and subcategories have been selected, the user can save the data to the database (however, this functionality is turned off in this example).
To help the users find the blog, there is a custom search engine provided at the top of the page.
Please click on the button below to see the demonstration. This article is quite extensive, you may want to click on the code button and put the window aside to follow along.
Note: this demonstration does not update the database. It is used for demonstration purposes only.
Using Kendo Templates to Embed Cascading Dropdowns in a Kendo Grid
Introducing Kendo Templates
As we mentioned in previous articles, most of the Kendo Widgets support using templates to extend the widget functionality. The Kendo template is similar to other JavaScript template engines, such as Angular, and is often used to bind data from the server. A template can include logic to display logic or use other JavaScript objects. For more information regarding the Kendo Templates see https://docs.telerik.com/kendo-ui/framework/templates/overview.
You can use a template for nearly every display property in a Kendo Grid. Some of the more popular temples in the Kendo Grid are the row and detail templates. In this example, we will apply a custom toolbar template at the top of the grid instead to apply custom search functionality along with buttons to save and export the data as well as adding a Kend Grid columns.template to embed the URL to the blog name found in the blog column.
Server-Side Queries to Obtain the Data Used in the Dropdowns
The following ColdFusion queries are used to prepare the data for the cascading dropdowns. The getCategoryDomain merely captures two records with the ID and the Category Domain, which are 1 and 3, and 'ColdFusion' and 'Lucee' in this case. The getCategory query captures the categories associated with the Domain Category Id (for example 'AJAX').
<cfquery name="getCategoryDomain" datasource="#dsn#">
SELECT
category_domain_id,
category_domain
FROM category_domain
WHERE category_domain <> 'Kendo UI'
ORDER BY category_domain
</cfquery>
<cfquery name="getCategory" datasource="#dsn#">
SELECT
category_id,
category_domain_ref as category_domain_id,
category
FROM category
ORDER BY category
</cfquery>
Changing Style Properties of the Kendo Grid
Use the k-grid class to change the font and the height of the rows. The k-grid class is quite extensive and can also be used for other properties as well. Be aware if you use the k-grid class, it will affect the display of all of the grids on the page.
Nearly all of the Kendo Widgets need to have a DIV element to contain the elements of the widget. You can create a static DIV like we have done here, or create the DIV dynamically as we have done when creating a dynamic Kendo Window.
<!--- Empty iv container for the grid. --->
<div id="feedsGrid"></div>
Kendo Grid Custom Header
Our feedGridToolbar custom header has buttons to export the grid data to Excel and PDF and has a custom search interface to allow the user to quickly find records.
External Kendo Templates
In this example, we will use a Kendo external template. These templates are embedded in JavaScript with the type of 'text/x-kendo-template'.
Custom Export PDF and Excel Buttons
It should be noted that the "k-button k-button-icontext k-grid-pdf" and "k-button k-button-icontext k-grid-excel" classes are used to create custom buttons to invoke Kendo's native saveAsPdf and saveAsExcel methods. Clicking on these buttons will either save the contents of the grid to PDF or Excel. Other helpful custom classes that you can apply to custom buttons are:
k-grid-add creates a button with a plus and will invoke the addRow method
k-grid-edit fires the edit method to edit the rows within the grid
Note: typically, if you don't want a custom toolbar, you can simply add the following code in the Kendo grid initialization to embed export to PDF and Excel capabilities along with a save button and search functionality.
toolbar: ["pdf", "excel", "save", "search"],
This will replicate the functionality that we have provided using the custom toolbar templates.
Note: the default search input functionality only works with 2019 R3 2019.3.917 release and greater.
Implementing a Custom Kendo Grid Search Interface
We are using custom JavaScript logic to handle the search interface. It should be noted that built-in search functionality is already baked into the grid that filters records on the client, however, there are advantages when using a customized search interface that returns search results as we are doing here.
When the search button is clicked, the onFeedsGridSearch() method will be invoked which will query the database on the server using the entered search string to retrieve the relevant records. The circular refresh button calls the refreshFeeds() method which will refresh the grid. We will cover these two methods below.
<!--- Grid toolbar template --->
<script type="text/x-kendo-template" id="feedsGridToolbar">
<div class="toolbar" style="margin: auto; float: left;">
<!--- Default Kendo UI buttons for PDF and Excel export. Note: when using ColdFusion, we need to escape any pound symbols in the template with a backslash --->
<a class="k-button k-button-icontext k-grid-pdf" style="margin: auto;" href="#"><span class="k-icon k-i-pdf"></span>Export to PDF</a>
<a class="k-button k-button-icontext k-grid-excel" id="feedGridExcelExport" href="#"><span class="k-icon k-i-excel"></span>Export to Excel</a>
</div>
<span class="toolbar" style="margin: auto; float:right; <cfif not session.isMobile>padding-right:10px;</cfif>">
<!--- Search --->
<label class="category-label" for="feedsGridSearchField">Search:</label>
<input type="text" id="feedsGridSearchField" class="k-textbox" style="width: <cfif session.isMobile>200<cfelse>400</cfif>px; padding :5px;"/>
<a href="javascript:onFeedsGridSearch();" aria-label="Search" class="k-link k-menu-link"><span class="fa fa-search" style="font-size:1em; padding :5px;"></span></a>
<cfif not session.isMobile>
<!--- Refresh --->
<a href="#" class="k-pager-refresh k-link k-button k-button-icon" title="Refresh" onClick="refreshFeedsGrid();" style="padding :5px;"><span class="k-icon k-i-reload" onClick="refreshBlogCategoryGrid();"></span></a>
</cfif>
</span>
</script>
Custom Search JavaScript Methods used by the Grid
The onFeedsGridSearch Method
This method simply takes the string that was entered by the user and passes it to the createSearchFilter method. These two methods can be consolidated, however, I separated them into two distinct methods as I may want to use the createSearchFilter method in other client-side interfaces to modify the grid.
The createSearchFilter method
This method takes a search term string and applies the filters to send it to the Kendo data source. The Kendo filters accept an array of items, and we will populate the new filter array using jQuery's push method. After the filter array is populated, we will apply it to the Kendo grid data source that is used to repopulate the Kendo grid.
function onFeedsGridSearch(){
// Extract the search term
var searchTerm = $("#feedsGridSearchField").val();
// Invoke the createSearchFilter function
createSearchFilter(searchTerm);
}//function
// Grid filters
function createSearchFilter(searchTerm){
// Get a reference to the grid.
var grid = $("#feedsGrid").data("kendoGrid");
// Instantiate the filter object as an array
$filter = new Array();
// Build the filters with the search term
if(searchTerm){
// Populate the array of filters
$filter.push({ field: "name", operator: "contains", value: searchTerm });
$filter.push({ field: "description", operator: "contains", value: searchTerm });
$filter.push({ field: "url", operator: "contains", value: searchTerm });
// Refresh the data with the new filters.
grid.dataSource.filter({logic: "or", filters: $filter});
}//if
}//function
Manually Refreshing the Kendo Grid
The circular button on the right of the custom header allows the user to manually refresh the grid. Here we are clearing the search input using jQuery, removing any previously applied filters, refreshing the data source using the datasource read method, and refreshing the Kendo grid.
function refreshFeedsGrid(){
// Clear any prevous search term in the search input
$("#feedsGridSearchField").val('');
// Remove the filters
$("#feedsGrid").data("kendoGrid").dataSource.filter({});
// Refresh the datasource
$("#feedsGrid").data("kendoGrid").dataSource.read();
}
The Logic for the First Cascading Category Domain Kendo Dropdown
Create the Local JavaScript JSON Variable for the Parent Dropdown
For performance reasons, all cascading dropdowns within a grid should use local binding. You can use server-side binding, however, since the grid can contain thousands, or potentially millions of records, the performance would be very slow and the interface would appear to be buggy.
For the parent category domain dropdown, we are creating a local JavaScript JSON variable to hold the dropdown values. Currently, this is either ColdFusion or Lucee. The output of this JSON is "var categoryDomainDataArray = [{ "category_domain_id":1,"category_domain":"ColdFusion"},{ "category_domain_id":2,"category_domain":"Lucee"}];"
/* Static data arrays are needed for the dropdowns within a grid. Putting the dropdown data in a datasource declaration is not sufficient as it loads the data from the server on every click making the dropdowns within a grid very slow. */
var categoryDomainDataArray = [<cfoutput query="getCategoryDomain">{ "category_domain_id":#category_domain_id#,"category_domain":"#category_domain#"}<cfif getCategoryDomain.currentRow lt getCategoryDomain.recordcount>,</cfif></cfoutput>];
//Note: this local js variable is not declared as a separate datasource for efficiency. When the grid is refreshed via the read method, having this in it's own datasource is problematic with large datasets.
Create a JavaScript Function to Initialize the Parent Kendo Dropdown
If you have been following our previous Kendo UI articles, you should recognize that the code to initialize the cascading dropdowns is quite similar. The main difference is that the initialization here is wrapped inside a JavaScript function. You should note that the serverFiltering argument is set to true which typically means that the filtering takes place on the server- however, this is not the case as our dataSource is using the local JavaScript JSON array that we just created. We are also using an onClose and onChange to invoke the onDomainChange method which is used to populate our child category dropdown.
/* This function is used by the template within the domain column in the grid */
function domainCategoryDropDownEditor (container, options) {
$('<input required data-text-field="category_domain" data-value-field="category_domain_id" data-bind="value:' + options.field + '"/>')
.appendTo(container)
.kendoComboBox({
serverFiltering: true,
placeholder: "Select domain...",
dataTextField: "category_domain",
dataValueField: "category_domain_id",
dataSource: categoryDomainDataArray,
// Invoke the onDomain change methd to filter the category when the domain has been selected.
close: onDomainChange,
change: onDomainChange
});//...kendoComboBox
}//...function
Create a Function to Display the Initial Values in the Parent Domain Category Dropdown
This function will populate the initial category domain values in the grid (which will be ColdFusion or Lucee). The getCategoryDomain function simply loops through the categoryDomainArray JSON that we created above and compares the domainId to the category_domain_ref returned from the server by the Kendo DataSource. When the two values match, it returns the value back.
// Function to display the initial domain in the grid.
// !! Notes: 1) the categoryDomainRef is coming from the query on the server that is populating the grid, 2) the dropdown functions and it's datasource must be outside of the document.ready scope.
function getCategoryDomainDropdown(category_domain_ref) {
// Set the default var.
var domain = '';
// Loop thru the local js variable.
for (var i = 0, length = categoryDomainDataArray.length; i < length; i++) {
if (categoryDomainDataArray[i].category_domain_id == category_domain_ref) {
// Set the global labelValue var in order to return it properly to the outer function. This should either be null or the proper value.
domain=categoryDomainDataArray[i].category_domain;
}//if (categoryDomainDataArray[i].category_id == category_id)...
}//for...
return domain;
}//...function
Create the Parent Domain Category Filter
The following function is used when filtering the data by clicking on the domain category column in the grid. This function indicates that the filter should search our categoryDomainArray and should return the category_domain for the dropdown label and set the category_domain_id for the value of the field. Note: this step is only needed when you explicitly set the grid's filterable argument to true.
// Filter for the dropdown. Note: the filterMenuInit event is raised when the filter menu is initialized.
function categoryDomainDropdownFilter(element) {
element.kendoComboBox({
serverFiltering: true,
dataSource: categoryDomainDataArray,
filter: "contains",
dataTextField: "category_domain",
dataValueField: "category_domain_id"
})//...element.kendoComboBox;
}//...function categoryDomainDropdownFilter(element)
Create the Parent onChange Function
The onDomainChange JavaScript function will get a reference to the category child dropdown and filter the category by the categoryDomain that was selected by the user. Here we are using the contains operator, which works in this case as every domainId is unique. If the IDs were not unique, we would change the operator to "equals".
// On change function that will filter the category dropdown list if a domain was selected.
function onDomainChange(e){
// Create a reference to the next dropdown list.
var category = $("#category").data("kendoComboBox");
// Filter the category datasource
category.dataSource.filter( {
field: "category_domain_id", //
value: this.value(),
operator: "contains"
});
}//function onDomainChange(e)...
The Logic for the Last Child Cascading Category Kendo Dropdown
For the child cascading menu, we are going to repeat all of the steps used to create the first category domain dropdown that we just performed, however, we can omit the last onChange step unless there is another dependent child dropdown, which is not the case here. Much of our logic is identical, but I will identify the main differences.
Create the Local JavaScript JSON Variable for the Child Dropdown
The logic here is nearly identical to the domainCategoryArray that we created. The main difference is that we are also including the category_domain_id, along with the category_id and the category. For any child dropdowns, you must include a value that is found in the parent dropdown in order to associate the dropdowns. Here, the category_domain_id will be the association that we will use to filter this child dropdown.
Create a JavaScript Function to Initialize the Kendo Child Dropdown
The ComboBox initialization is nearly identical to its parent, here we are assigning the category as the dropdown label and the category_id as the dropdown value. We are also omitting the onChange logic as this is the last dependent dropdown. If there are other child dropdowns dependent upon the selection of this value, the onChange logic must be included.
// This function is invoked inside of the grid template to populate the initial values for the first category dropdown. This dropdown is independent of the 2nd category dropdown
function categoryDropDownEditor(container, options) {
$('<input id="category" required data-text-field="category" data-value-field="category_id" data-bind="value:' + options.field + '"/>')
.appendTo(container)
.kendoComboBox({
placeholder: "Select category...",
dataTextField: "category",
dataValueField: "category_id",
dataSource: categoryArray,
});//...kendoComboBox
}//...function
Create a Function to Display the Initial Values in the Category Child Dropdown
Again, this logic is identical to the parent other than we are using the category_id and category instead of the domain category information.
function getCategoryDropdown(category_id) {
// Set the default var.
var category = '';
// Loop thru the local js variable.
for (var i = 0, length = categoryArray.length; i < length; i++) {
if (categoryArray[i].category_id == category_id) {
// Set the global labelValue var in order to return it properly to the outer function. This should either be null or the proper value.
category=categoryArray[i].category;
}//if (categoryArray[i].category_id == category_id)
}//for...
return category;
}//...function
Create the Child Category Filter
This is identical to the parent menu other than using the category_id and category that will be used when the user filters the data. This code is not necessary if the filterable argument was not explicitly set to true in the grid declaration.
Wrap the Kendo DataSource and Grid Initialization with a Document Ready Block
Most of the Kendo Widgets, especially the Kendo Grid, should be placed in jQuery's document-ready scope in order for the widgets to function. However, you must also place any universal JavaScripts that need to be invoked outside of the document-ready scope, otherwise, your JavaScripts will not be available outside of the ready block. Typically I wrap the ready block around the Kendo DataSource and the widget initialization code and keep all other scripts outside of the ready block. If your Kendo Grids are not initializing, this is one of the first things that I check and I will always try to place a comment at the end of the ready block.
$(document).ready(function(){
... code goes here
});//document ready
Create the Kendo DataSource
The Kendo DataSource here is nearly identical to the data source that we created in our last article showing you how to create the data source for an editable grid. The only difference is that here we are wanting to return information to create the category domain and category. There is nothing unique that we need here in order to use cascading dropdowns. Please see https://gregoryalexander.com/blog/#mcetoc_1gkr406c31 for more information.
// Create the datasource for the grid
feedsDs = new kendo.data.DataSource({
// Determines which method and cfc to get and set data.
transport: {
read: {
url: "/blog/demo/Demo.cfc?method=getDemoFeeds", // the cfc component which processes the query and returns a json string.
dataType: "json", // Use json if the template is on the current server. If not, use jsonp for cross domain reads.
method: "post" // Note: when the method is set to "get", the query will be cached by default. This is not ideal.
},
// The create method passes the json like so: models: [{"id":"","name":"","description":"sadfas","url":"","blogsoftware":"","demo_notes":"asdfa","demo_active":false}]
create: {
url: "/blog/demo/Demo.cfc?method=saveBlogReqestViaGrid&action=insert",
dataType: "json",
method: "post"
},
// The update function passes all of the information in the grid in a models JSON string like so: [{"total":147,"demo_notes":"","description":"Jochem's tech exploits due to fork","blogsoftware":"http://wordpress.org/?v=2.7","recentposts":0,"id":48,"rssurl":"http://jochem.vandieten.net/feed
","demo_description":"Jochem's tech exploits","demo_active":false,"url":"http://jochem.vandieten.net
","name":""it could be bunnies""}]
update: {
url: "/blog/demo/Demo.cfc?method=saveBlogReqestViaGrid&action=update",
dataType: "json",
method: "post"
},
destroy: {
url: "/blog/demo/Demo.cfc?method=saveBlogReqestViaGrid&action=delete",
dataType: "json",
method: "post"
},
// The paramterMap basically strips all of the extra information out of the datasource for the grid display. YOu must use this when you are using an editible Kendo grid otherwise strange behavior could occur.
parameterMap: function(options, operation) {
if (operation !== "read" && options.models) {
return {models: kendo.stringify(options.models)};
}
}
},
cache: false,
batch: true, // determines if changes will be send to the server individually or as batch. Note: the batch arg must be in the datasource declaration, and not in the grid. Otherwise, a post to the cfc will not be made.
pageSize: 10, // The number of rows within a grid.
schema: {
model: {
id: "id", // Note: in editable grids- the id MUST be put in here, otherwise you will get a cryptic error 'Unable to get value of the property 'data': object is null or undefined'
fields: {
// We are using simple validation to require the blog name, desc, url and rss url. The other fields are not required. More elaborate validation examples will be provided in future blog articles. It is somewhat counter intuitive IMO that these validation rules are placed in the Kendo DataSource.
name: { type: "string", editable: true, nullable: false, validation: { required: true } },
description: { type: "string", editable: true, nullable: false, validation: { required: true } },
url: { type: "string", editable: true, nullable: false, validation: { required: true } },
rss_url: { type: "string", editable: true, nullable: false, validation: { required: true } },
site_image: { type: "string", editable: true, nullable: false, validation: { required: false } },
// Note: the following two cascading menu fields need to have a default value
domain_category: { type: "string", editable: true, nullable: false, validation: { required: false }, defaultValue: 0 },
// The category is dependent upon the domain category. Each category has a domain category id
category: { type: "string", editable: true, nullable: false, validation: { required: false }, defaultValue: 0 },
request_approved: { type: "boolean", editable: false, nullable: false, validation: { required: false } },
}//fields:
}//model:
}//schema
});//feedsDs = new kendo.data.DataSource
Initialize the Kendo Grid
Like the Kendo DataSource, the grid initialization is quite similar to the grid initialization in our previous article, However, this script calls the CfBlogs Eernal Kendo Template that we created at the beginning of this article for the header. The category_domain and category columns in the grid have extra logic required to make our cascading dropdowns.
Handling the Parent category_domain Dropdown
For our first domain_category dropdown, there are three settings that are unique to the dropdown.
For the columns.template argument, we are invoking the getCategoryDropdown method to populate the initial read-only values when the grid is loaded.
The columns.editor argument is invoking the categoryDomainEditor JavaScript function that we created to initially populate the control once the edit button on the far right of the grid has been clicked.
The columns.filter argument is invoking the categoryDomainDropdownFilter function. We are also modifying the filtering capability of the domain dropdown to "equal" or "not equal to".
The columns.editor argument is invoking the categoryDropDownEditor JavaScript function that we created to initially populate the control once the edit button on the far right of the grid has been clicked.
The columns.filter argument is invoking the categoryDropdownFilter function. We are also modifying the filtering capability of the domain dropdown to "equal" or "not equal to".
$("#feedsGrid").kendoGrid({
dataSource: feedsDs,
// Edit arguments
editable: "inline", // use inline mode so both dropdownlists are visible (required for this type of cascading dropdown)
// Header
headerTemplate: 'CfBlogs',
// Toolbars. You can customize each button like the excel button below. The importExcel button is a custom button, and we need to wire it up to a custom handler below.
toolbar: kendo.template($("#feedsGridToolbar").html()),
excel: {
allPages: true
},
// General grid elements.
height: 660,// Percentages will not work here.
filterable: true,
columnMenu: true,
groupable: true,
sortable: {
mode: "multiple",
allowUnsort: true,
showIndexes: true
},
allowCopy: true,
reorderable: true,
resizable: true,
pageable: {
pageSizes: [15,30,50,100],
refresh: true,
numeric: true
},
columns: [{
// Columns
field:"id",
title: "I.D.",
hidden: true,
filterable: false
}, {
field:"name",
title: "Blog",
filterable: true,
width: "15%",
template: '<a href="#= url #">#= name #</a>'
}, {
field:"description",
title: "Description",
filterable: true,
width: "20%"
}, {
field:"url",
title: "Blog URL",
filterable: true,
width: "15%"
}, {
field:"domain_category",
width: "10%",
editor: domainCategoryDropDownEditor,
// The template should be a function that matches the id's and returns the title.
template: "#=getCategoryDomainDropdown(category_domain_ref)#",//The method that gets the id by the name that was selected.
title: "Domain",
filterable: {
extra: false,// Don't show the full filter menu
ui:categoryDomainDropdownFilter,
operators: {
string: {
eq: "is equal to",
neq: "is not equal to"
}//..string
}//...operators
}//...filterable
}, {
field:"category",
width: "12%",
editor: categoryDropDownEditor,
// The template should be a function that matches the id's and returns the title.
template: "#=getCategoryDropdown(category_id)#",//The method that gets the id by the name that was selected.
title: "Category",
filterable: {
extra: false,// Don't show the full filter menu
ui:categoryDropdownFilter,
operators: {
string: {
eq: "is equal to",
neq: "is not equal to"
}//..string
}//...operators
}//...filterable
}, {
command: [
// Opens the editable columns
{ name: "edit", text: "Edit" },
// Cancels the operation
{ name: "destroy", text: "Cancel" }
],
title: " ",
width: "12%"
}
]// columns:
});// $("#feedsGrid").kendoGrid({
The Kendo Grid is one of the most advanced HTML grid libraries. In this article, we will cover standard out-of-the-box grid functionality and show you how to use the Kendo Grid to view and edit records with ColdFusion on the backend. However, Kendo UI is server agnostic and you can use the same techniques learned here with other server-side technologies.
My CFBlogs blog aggregator follows around 150 ColdFusion-related blogs. In this example, we will recreate the Kendo grid found at https://www.cfblogs.org/?blogs and add a few additional columns to allow users to save data using a Kendo UI grid.
Out-of-the-box, Kendo UI provides rich functionality with most of its widgets without having to write custom code that is triggered by events. Here, we are using Kendo's extensive built-in functionality to create an editable grid with search and filter capabilities.
Example Demonstration and Client Side Code
This example will allow the user to interact with the capabilities of the Kendo UI Grid.
We will be using standard out-of-the-box functionality to create this first draft interface allowing the public to request that we add their own blog to the CFBlogs aggregator. In later articles, we will improve upon these interfaces in order to show you the benefits of the Kendo UI and the Kendo grid. Click the button below to see the Kendo Grid in action.
This article is quite detailed and lengthy, I recommend opening up both the demonstration and the client side code when reading the article to make more sense of it.
Note: You can click on the pin icon on the top right of the window if you want to keep the window stationary when scrolling.
General Considerations when using the Kendo Grid
Use a Large Screen Width
You should use a large screen width if possible. The Kendo Grid is used to visualize and interact with large amounts of data. There are techniques, such as using nested rows, to condense the width of the grid, however, it is most effective when used on wider devices. You should display as many relevant columns as you can in order to allow users to better interact with the data.
Enable Paging or Use Grid Virtualization
Adding paging to the grid restricts the number of rows in the grid, however, it allows the users to click on the paging buttons at the bottom of the grid to view the next x number of records. This provides better grid performance as well as condenses the number of records that are presented to the user at one time.
You can also implement grid virtualization to limit the number of records when dealing with large amounts of data. We will cover grid virtualization in future articles.
Allow the Users to Filter and Search for Relevant Data
It is important to allow the users to get to the relevant records as fast as possible. Not only does this have the positive benefit of saving time and frustration for the user, but it also limits the quantity of the records improving overall grid performance. In this article, we will show you how to implement out-of-the-box client-side search functionality along with sophisticated grid filters to allow users to seek relevant records.
Validate the Data on Both the Client and Server
Like most all of the other HTML5 widgets, you should validate data both on the client and the server. Here we will introduce the Kendo UI validator and provide basic examples of how to perform basic validation on the client and server.
Differences Between Editable and View-Only Grids
Differences Between Read-Only and Editable Grids on the Client Side
The major differences between an editable grid and a view-only grid that we previously covered are that the editable grid has extra logic placed in the Kendo Datasource declaration. I am not quite sure why Telerik chose to use extra logic to edit a record in the Kendo DataSource, including client-side validation for example, however, it is important to remember that this logic needs to be placed inside the Kendo DataSource.
There are some other minor logic differences outside of the Kendo DataSource as well and will cover these below.
Differences Between Read-Only and Editable Grids on the Server
The logic on the server handling a view-only grid is simple- the server needs to simply query the database and return the data as JSON. With an editable grid, we need to inspect and understand how the Kendo DataSource sends the data to the server and have additional arguments to determine how to save the data into the database.
Client-Side Logic
The KendoDataSource Explained
As we have previously mentioned in this article, most of the logic that pertains to editing the grid needs to be embedded into the Kendo DataSource. This includes configuring whether the columns are editable as well as the validation rules. We also need to configure the settings for the background AJAX requests to the server to modify the data.
We will try to cover most of the essential details of the code that is the basis for the demonstration example below. This is a fairly comprehensive explanation but it should make more sense when you peruse the code example further below.
Kendo DataSource Transport Arguments
We need to configure the background AJAX requests in the Kendo DataSource transport. We will set the URLs pointing to the service endpoints for our read, update and destroy operations.
In this example, the service endpoint for all operations is the Demo.cfc ColdFusion component. However, the methods are different- for read operations, we are calling the getDemoFeeds() method, and the udate, create, and destroy methods are calling the saveBlogReqestViaGrid and we are passing an additional action argument in the URL. The action URL argument will determine if we will update, insert or delete records in the database. This is a ColdFusion component, however, you should be able to adapt the code for other technologies as well. We will elaborate on the ColdFusion server-side logic later in the article.
Note that the name of the create, update and destroy methods must correspond to the toolbar "create", "update", and "destroy" strings in the toolbar's array in the code in the grid configuration below.
Finally, the transport configuration needs to include the paramaterMap configuration. The parameterMap transforms the data for read operations into JSON and this parameterMap string should always be used when using editable grids, otherwise, the excess information in the Kendo DataSource object will interfere with read operations.
Kendo DataSource Batch Argument
The batch argument determines if changes will be sent to the server individually or as a batch. I typically always set this to true as it only makes one AJAX request to update the data instead of sending multiple requests to the server.
Kendo Datasource Field Arguments
In this example, we will use field arguments to determine whether the column is editable and if the values can be null, and set the validation rules to make sure that certain columns are filled out.
The field type argument specifies the data type of the field. The type can be string, number, boolean, or date. You can also use object but we will not cover that here.
Editable determines if the user can make changes to the column. This is a true or false value. Nullable is similar and determines if a null value can be passed to the server.
Validation can take an array of arguments. We also use this to configure custom validation rules with the Kendo Validator. We will cover this in detail in a future article but in this example, we are only setting whether a column value is required. If the column is required, a message will be displayed on the client side when nothing was filled out by the user.
Kendo DataSource Code
feedsDs = new kendo.data.DataSource({
// Determines which method and cfc to get and set data.
transport: {
read: {
url: "/blog/demo/Demo.cfc?method=getDemoFeeds", // the cfc component which processes the query and returns a json string.
dataType: "json", // Use json if the template is on the current server. If not, use jsonp for cross domain reads.
method: "post" // Note: when the method is set to "get", the query will be cached by default. This is not ideal.
},
// The create method passes the json like so: models: [{"id":"","name":"","description":"sadfas","url":"","blogsoftware":"","demo_notes":"asdfa","demo_active":false}]
create: {
url: "/blog/demo/Demo.cfc?method=saveBlogReqestViaGrid&action=insert",
dataType: "json",
method: "post"
},
// The update function passes all of the information in the grid in a models JSON string like so: [{"total":147,"demo_notes":"","description":"Jochem's tech exploits due to fork","blogsoftware":"http://wordpress.org/?v=2.7","recentposts":0,"id":48,"rssurl":"http://jochem.vandieten.net/feed
","demo_description":"Jochem's tech exploits","demo_active":false,"url":"http://jochem.vandieten.net
","name":""it could be bunnies""}]
update: {
url: "/blog/demo/Demo.cfc?method=saveBlogReqestViaGrid&action=update",
dataType: "json",
method: "post"
},
destroy: {
url: "/blog/demo/Demo.cfc?method=saveBlogReqestViaGrid&action=delete",
dataType: "json",
method: "post"
},
// The parameterMap basically strips all of the extra information out of the datasource for the grid display. You must use this when you are using an editable Kendo grid otherwise strange behavior could occur.
parameterMap: function(options, operation) {
if (operation !== "read" && options.models) {
return {models: kendo.stringify(options.models)};
}
}
},
cache: false,
batch: true, // determines if changes will be sent to the server individually or as batch. Note: the batch arg must be in the datasource declaration, and not in the grid. Otherwise, a post to the cfc will not be made.
pageSize: 15, // The number of rows within a grid.
schema: {
model: {
id: "id", // Note: in editable grids- the id MUST be put in here, otherwise you will get a cryptic error 'Unable to get the value of the property 'data': object is null or undefined'
fields: {
// We are using simple validation to require the blog name, desc, url and rss url. The other fields are not required. More elaborate validation examples will be provided in future blog articles. It is somewhat counterintuitive IMO that these validation rules are placed in the Kendo DataSource.
name: { type: "string", editable: true, nullable: false, validation: { required: true } },
description: { type: "string", editable: true, nullable: false, validation: { required: true } },
url: { type: "string", editable: true, nullable: false, validation: { required: true } },
rss_url: { type: "string", editable: true, nullable: false, validation: { required: true } },
site_image: { type: "string", editable: true, nullable: false, validation: { required: false } },
blog_software: { type: "string", editable: true, nullable: false, validation: { required: false } },
notes: { type: "string", editable: true, nullable: true, validation: { required: false } },
request_approved: { type: "boolean", editable: false, nullable: false, validation: { required: false } },
}//fields:
}//model:
}//schema
});//feedsDs = new kendo.data.DataSource
Kendo Grid Configuration Explained
The Grid initialization is nearly identical to read-only grids, the main difference being the buttons on the toolbar (or custom toolbar template) that allow the user to edit and insert records into the grid. We are also going to introduce how to configure the toolbar to export the grid data into Excel or PDF and provide the standard out-of-the-box client-side search interface.
The Kendo UI Grid Toolbar
The Grid toolbar is at the top of the grid and can be configured with buttons and other custom elements and can be configured in multiple ways. If you're not using a custom Kendo UI template, the toolbar for the grid takes either a single string or an array of arguments to provide built-in functionality.
Every toolbar string will render a button on the toolbar. If the argument is not a built-in argument such as "excel", you need to also have a custom method in the dataSource transport in order to invoke a custom Javascript method.
We will explain the built-in arguments below.
Exporting Kendo UI Grid Data to PDF and Excel
Typically, if you are not using a custom toolbar, you can simply add the following code in the Kendo grid initialization to embed export to PDF and Excel capabilities.
toolbar: ["pdf", "excel"],
There are further configuration options for Excel export capability. Additional Excel configuration arguments are allPages, which allows the grid to export data from the entire grid, and fileName. A Typical example is shown below.
excel: {
allPages: true,
fileName: "Blogs.xlsx"
}
Toolbar Arguments to Provide Grid Editing Capabilities
The following arguments can be applied to the toolbar to render the edit, insert, and cancel buttons that are used to edit records. These buttons will invoke the same methods that were created in the transport section in the Kendo DataSource to insert and save any edited records.
The create button will add a row to the grid allowing the user to add a new record, the save button is used to save any edited records within the grid, and the cancel button allows the user to back out of any changes.
"create", "save", "cancel"
Toolbar Argument to Render a Client-side Search Engine Interface
The following argument will create a search panel interface allowing the users to search for any of the records.
"search"
If you want to fine-tune the search parameters, you can specify which columns are being searched and provide the search operators like so:
Note: the default search input functionality only works with 2019 R3 2019.3.917 release and greater.
Available Kendo UI Search Operators
The operators, for both the search panel as well as the column filters, are:
eq (equal to)
neq (not equal to)
lt (less than)
isnull (is null)
isnotnull (is not null)
gt (greater than)
gte (greater than or equal to)
lt (less than)
lte (less than or equal to)
isempty (is an empty string)
isnotempty (is not an empty string)
contains
doesnotcontain (does not contain)
startswith
doesnotstarwith
endswith
doesnotendwith
Putting the toolbar options together in our example we have the following which allows for Excel and PDF Export, buttons to create save, and cancel records, and an out-of-the-box client-side search interface.
When the columnmenu argument is set to true, Kendo UI will render a menu that is available by clicking on the ellipsis to the right of the column text. The user can use this menu to filter the grid (see below), provide sorting, and determine which columns are visible. I recommend setting this to true unless there is a reason to disable it.
The filterable Configuration
The filterable argument provides search filters for all of the grid columns. There are multiple options to filter the results. Out of the box, there are nearly a dozen different operators such as "equals", "contains" etc. The filters configuration also allows the user to select which columns to show in the grid allowing them to remove columns that they don't care about. The best way to comprehend the filters is to take a look at our example, click on the ellipsis to the right of any column, and choose filter. I will provide a few screenshots of our example. In my interfaces, I always include the filters using filterable: true.
The groupable Configuration
If you set the groupable argument to true, Kendo UI will create a new ribbon underneath the toolbar to allow the grid columns to be grouped. If nothing is already grouped, the ribbon will display 'Drag a column header and drop it here to group by that column'. The user can select and drag any column to this ribbon in order to group the data by the selected column, and multiple columns can be grouped. Note that the grouping takes place on the client side- there will be no interaction with the server.
If the sortable argument is set to true, the user will be able to sort the columns. You can specify which columns can be sorted, or enable all of the columns to be sorted as we have done here. The showindexes argument displays the order of the sorting and displays a 1,2,3 etc according to which column was sorted first. As with the filterable argument, this should always be set to true unless there is a compelling reason to remove the grid sorting capability.
The allowcopy and resizable arguments should be self-explanatory. 'allowcopy' allows the user to copy the data in the selected cells, resizable allows the user to resize the grid.
The pageable Configuration
The pageable argument is used to render a pager at the bottom of the grid. The pager can be used to limit the visible number of rows in a grid and provides a next n records interface.
There are two main benefits of a paging interface. First and foremost it limits the number of records that are displayed in the grid which can vastly improve page load times, additionally, it limits the number of records in order to fit them on a single page. If performance is an important consideration or you have a large number of records, you should enable paging. You may also use virtualized grids to enhance performance and we will cover this option in a future article.
The pageSizesargument below specifies the choices that are in the paging dropdown, in this example, the user can select to show 15, 30 50, or 100 rows. The refresh argument renders a circular icon allowing the user to refresh the grid and numeric displays the buttons to go to a certain part of the recordset.
The columns array configures the grid columns. In this example, the field is the name of the database column that is used to populate the column values. The title is the string that is used to label the column. The hidden argument determines whether to hide or display the column. Generally, the only column that I hide is the column that contains the value of the primary key as it does not have real meaning to the end user. The filterable argument determines if the filters are available for this column. The filterable argument is not necessary if the filterable argument is not set to true. Width is the width of the column, and you can either use percentages or a numeric value for the number of pixels. Of course, if you use percentages, all of the width percentages must add up to 100%.
It is important to note that even if the column for the primary key is hidden; the primary key column must be available in all of the editable grids!
The command Configuration
The command can be a string or an array of strings. Typically, the command is placed in the last column of the grid. LIke the toolbar, for every command, a button will be rendered in the column. The built-in edit and destroy commands will invoke the edit and destroy methods that are used in the Kendo DataSource to update or delete records on the server.
Finally, we need a div that has an id of the same name as the grid. The Kendo Grid will render inside of this div element. You can apply different styles to the div to set the height and width.
<div id="feedsGrid"></div>
Sever-Side Logic
Server-Side Logic Used to Initially Populate the Grid
The getDemoFeeds function, invoked by the Kendo dataSource read method, simply queries the database and invokes the CFJson component to convert and return the ColdFusion query object into JSON. The optional id and url arguments are not used in this demonstration.
<cffunction name="getDemoFeeds" access="remote" returnformat="json" hint="Returns all of the blog requests">
<cfargument name="id" required="false" default="" />
<cfargument name="url" required="false" default="" />
<cfquery name="Data" datasource="#variables.cfbloggersDsn#">
SELECT
id,
name,
description,
url,
rss_url,
blog_software,
site_image,
notes,
request_approved
FROM blog_request
<cfif len(trim(arguments.id))>
AND id = <cfqueryparam value="#arguments.id#" cfsqltype="cf_sql_integer" />
<cfelseif len(trim(arguments.url))>
AND url = <cfqueryparam value="#arguments.url#" cfsqltype="cf_sql_varchar" />
</cfif>
ORDER BY name asc
</cfquery>
<!--- Convert the query object into JSON using the convertCfQuery2JsonStruct method --->
<cfinvoke component="#application.cfJsonComponentPath#" method="convertCfQuery2JsonStruct" returnvariable="jsonString">
<cfinvokeargument name="queryObj" value="#Data#">
<cfinvokeargument name="includeTotal" value="true">
</cfinvoke>
<cfreturn jsonString>
</cffunction>
Server-Side Logic to Update, Insert or Delete Database Records Based Upon Values Passed via the Grid
The saveBlogRequestViaGrid method, invoked by the create, update and destroy methods found in the Kendo DataSource, updates the database on the server depending upon the action and models arguments. This function is written in ColdFusion, however, I will try to break it down to make it understood and relevant for non-Coldfusion programmers.
The Action URL Argument
Since much of the logic will be reused for all of our database operations to insert, update and delete records, I am using the action argument, passed via the grid in the URL, to specify which action to take when updating records. Simply put, I will process the incoming JSON, which will be the same for all operations, and use the action parameter to determine how to process the records.
Important note: the delete method is suppressed in our demonstration as I don't want all of the records to be deleted.
Inspecting JSON Data from the Models URL Argument
When using an editable grid, Kendo passes the grid data using JSON like so:
models:[{"total":147,"demo_notes":"","description":"Jochem's tech exploits ","blogsoftware":"http://wordpress.org/?v=2.7","recentposts":false,"id":48,"rssurl":"http://jochem.vandieten.net/feed
","url":"http://jochem.vandieten.net
","name":""it could be bunnies""}]
Server-Side Logic to Update Records in the Database
I don't want to get caught up in the details of the server-side logic as these articles are mainly focused on implementing Kendo on the client side, so I will keep this brief. Here are the general steps that I am using on the server side:
ColdFusion will remove the models URL argument, decode the URL and deserialize the JSON, and then loop through the data to determine the values that were selected by the user.
After the data is converted into a native ColdFusion array of structures, we will loop through the native ColdFusion object, set the default values for safety, and then validate the data. In this particular example, we are using JSoup to sanitize the data. We will cover more validation techniques along with JSoup in future articles.
After the data is properly validated, we will perform database operations that are determined by the action URL variable that was sent in.
Server-Side Code
<cffunction name="saveBlogReqestViaGrid" access="remote" returnformat="json" output="false">
<cfargument name="action" type="string" required="yes" hint="Either update or insert">
<!--- Note: the incoming string arguments will be like so:
models:[{"total":147,"demo_notes":"","description":"Jochem's tech exploits ","blogsoftware":"http://wordpress.org/?v=2.7","recentposts":false,"id":48,"rssurl":"http://jochem.vandieten.net/feed
","url":"http://jochem.vandieten.net
","name":""it could be bunnies""}] --->
<cfargument name="models" type="string" required="yes">
<!--- Remove the 'models' in the string --->
<cfset thisStr = replaceNoCase(models, 'models=', '', 'one')>
<!--- Decode the string and make it into an array --->
<cfset thisStr = urlDecode(thisStr)>
<!--- Use the desiarilze function to get at the underlying data. --->
<cfset thisStruct = deserializeJson(thisStr, false)>
<!--- Now that we have a clean array of structures, loop thru the array and get to the underlying values that were sent in the grid. --->
<!--- Loop thru the struct. --->
<cfloop array="#thisStruct#" index="i">
<!--- Note: some of the variables may not come thru if they are empty. Use error catching here to catch and continue processing if there is an error. --->
<cfparam name="id" default="" type="any">
<cfparam name="name" default="" type="any">
<cfparam name="description" default="" type="any">
<cfparam name="url" default="" type="any">
<cfparam name="rss_url" default="" type="any">
<cfparam name="site_image" default="" type="any">
<cfparam name="blog_software" default="" type="any">
<cfparam name="notes" default="" type="any">
<cfparam name="request_approved" default="" type="any">
<cftry>
<cfset blogId = i['id']>
<cfset blogName = i['name']>
<cfset blogDesc = i['description']>
<cfset blogUrl = i['url']>
<cfset blogRssUrl = i['rss_url']>
<cfset blogImage = i['site_image']>
<cfset blogSoftware = i['blog_software']>
<cfset notes = i['notes']>
<cfset approved = i['request_approved']>
<cfcatch type="any">
<cfset error = "one of the variables was not defined.">
</cfcatch>
</cftry>
<!--- Sanitize all of the incoming data using JSoup to prevent any tampering. I will cover JSoup in an upcoming article. --->
<cfif len(blogName)>
<!--- Sanitize the blog name --->
<cfset blogName = JsoupObj.jsoupConvertHtmlToText(blogName)>
</cfif>
<cfif len(blogDesc)>
<cfset blogDesc = JsoupObj.jsoupConvertHtmlToText(blogDesc)>
</cfif>
<!--- Test to see if this is a proper URL --->
<cfif len(blogUrl) and isValid("URL",blogUrl)>
<cfset blogUrl = blogUrl>
<cfelse>
<cfset blogUrl = "">
</cfif>
<cfif len(blogRssUrl) and isValid("URL",blogRssUrl)>
<cfset blogRssUrl = blogRssUrl>
<cfelse>
<cfset blogRssUrl = "">
</cfif>
<cfif len(blogImage)>
<cfset blogImage = JsoupObj.jsoupConvertHtmlToText(blogImage)>
</cfif>
<cfif len(blogSoftware)>
<cfset blogSoftware = JsoupObj.jsoupConvertHtmlToText(blogSoftware)>
</cfif>
<cfif len(notes)>
<cfset notes = JsoupObj.jsoupConvertHtmlToText(notes)>
</cfif>
<!--- Don't update the db unless the blog name came through properly (this should never be the case however) --->
<cfif len(blogName)>
<cfif action eq 'update'>
<!--- Update the database. --->
<cfquery name="updateBlogRequest" datasource="#variables.cfbloggersDsn#">
UPDATE blog_request
SET
name = <cfqueryparam value="#blogName#" cfsqltype="cf_sql_varchar" />
<cfif len(blogDesc)>
,description = <cfqueryparam value="#blogDesc#" cfsqltype="cf_sql_varchar" />
</cfif>
<cfif len(blogUrl)>
,url = <cfqueryparam value="#blogUrl#" cfsqltype="cf_sql_varchar" />
</cfif>
<cfif len(blogRssUrl) and isValid("URL",blogRssUrl)>
,rss_url = <cfqueryparam value="#blogRssUrl#" cfsqltype="cf_sql_varchar" />
</cfif>
<cfif len(blogImage)>
,site_image = <cfqueryparam value="#blogImage#" cfsqltype="cf_sql_varchar" />
</cfif>
<cfif len(blogSoftware)>
,blog_software = <cfqueryparam value="#blogSoftware#" cfsqltype="cf_sql_varchar" />
</cfif>
<cfif len(notes)>
,notes = <cfqueryparam value="#notes#" cfsqltype="cf_sql_varchar" />
</cfif>
WHERE id = <cfqueryparam value="#blogId#" cfsqltype="cf_sql_integer" />
</cfquery>
<cfelseif action eq 'insert'>
<!--- Insert a new record into the database. --->
<cfquery name="insertNewBlogRequest" datasource="#variables.cfbloggersDsn#">
INSERT INTO blog_request (
id,
name,
requestor_email
<cfif len(blogDesc)>
,description
</cfif>
<cfif len(blogUrl)>
,url
</cfif>
<cfif len(blogRssUrl)>
,rss_url
</cfif>
<cfif len(blogImage)>
,site_image
</cfif>
<cfif len(blogSoftware)>
,blog_software
</cfif>
<cfif len(notes)>
,notes
</cfif>
) VALUES (
<cfqueryparam value="#round(getLastBlogRequestId()+1)#" cfsqltype="cf_sql_integer" />
,<cfqueryparam value="#blogName#" cfsqltype="cf_sql_varchar" />
,<cfqueryparam value="" cfsqltype="cf_sql_varchar" />
<cfif len(blogDesc)>
,<cfqueryparam value="#blogDesc#" cfsqltype="cf_sql_varchar" />
</cfif>
<cfif len(blogUrl)>
,<cfqueryparam value="#blogUrl#" cfsqltype="cf_sql_varchar" />
</cfif>
<cfif len(blogRssUrl)>
,<cfqueryparam value="#blogRssUrl#" cfsqltype="cf_sql_varchar" />
</cfif>
<cfif len(blogImage)>
,<cfqueryparam value="#blogImage#" cfsqltype="cf_sql_varchar" />
</cfif>
<cfif len(blogSoftware)>
,<cfqueryparam value="#blogSoftware#" cfsqltype="cf_sql_varchar" />
</cfif>
<cfif len(notes)>
,<cfqueryparam value="#notes#" cfsqltype="cf_sql_varchar" />
</cfif>
)
</cfquery>
</cfif><!---<cfif action eq 'update'>--->
</cfif><!---<cfif len(blogName)>--->
</cfloop>
<cfset jsonString = []><!--- '{"data":null}', --->
<cfreturn jsonString>
</cffunction>
<!--- CFBlog request helper functions --->
<cffunction name="getLastBlogRequestId" access="remote" returntype="numeric" output="false">
<cfquery name="Data" datasource="#variables.cfbloggersDsn#">
SELECT
Max(id) as max_id
FROM blog_request
</cfquery>
<cfreturn Data.max_id>
</cffunction>
In future articles, we will cover how to improve upon the out-of-the-box functionality of the grid that we implemented in this article.
Thanks for reading, and feel free to ask questions or provide your comments.
The Kendo UI Grid is one of the most functional and elegant HTML5-based grid systems around. The Kendo UI Grid is used to easily allow users to visualize and interact with extremely large datasets- I have personally used Kendo Grids to convey over 60 million (60,00,000) records at a time using virtualization. The grids support paging, sorting, filtering, frozen columns, grouping, pivoting, etc, and allow for the editing of records. Grid data can also be exported to excel or pdf.
The grids are themeable and fully integrated with the other Kendo UI widgets and other Kendo UI widgets, such as Kendo dropdowns, can be embedded into a cell. The Kendo grids can be used natively with Angular, React, Vue and jQuery. Additionally, there are PHP and .Net wrappers, and Kendo UI can be easily used with ColdFusion as well. In this article we will show you how to use the grids with ColdFusion, however, like the rest of our articles, the client logic is written in JavaScript and jQuery and is server agnostic- you should be able to follow along.
Please click on the button below to see the Kendo UI Grid that uses this code.
When Should You Use a Kendo UI Grid?
I use Kendo UI Grids every time I would typically use an HTML table to convey tabular data. The Kendo UI grids are interactive and offer a lot of improvements over a traditional HTML table, such as adding the ability to sort and group the data and will create a uniform look and feel when using a Kendo Theme.
Kendo UI Grid Initializing and Binding Options
Kendo UI grids can be initialized using a simple HTML table or from a remote data service.
If the grids are initialized from a data service, the data can be bound to:
In this series of articles, we will focus on binding the grids both locally and to a ColdFusion Service Endpoint.
CfBlogs Database Background
In the examples below, we will use ColdFusion to make a query to the cfblogs.org RSS blog aggregator database (formerly cfbloggers). The Cfblogs.org database collects data every 15 minutes from over 147 different ColdFusion blog sites and is updated whenever a new blog post is made and generates a tweet that summarizes the new blog post on Twitter.
Initializing a Grid from a Table
In the demonstration below, we will create a Kendo Grid from a static table showing the top ColdFusion-related blogs according to the number of posts made in the last calendar year.
In this example, we are querying the CfBlogs MySql database for active blogs, counting the number of blog posts in the last year, and getting the date of the last post. We are then looping through the query to create the data within the table cells. This code should be familiar to the ColdFusion developer.
SELECT
COUNT(e.posted) as count,
MAX(date_format(e.posted, '%m/%d/%Y %r')) as maxDate,
b.name as blog,
e.categories,
b.description as blogdescription,
b.url as blogurl,
b.blogsoftware
FROM entries e, blogs b
WHERE e.blogidfk = b.id
AND b.active = 1
AND e.title is not null
AND e.posted > now() - INTERVAL 12 month
GROUP BY blog
ORDER BY count DESC
</cfquery>
<table id="grid">
<colgroup>
<col style="width:10%" />
<col style="width:25%" />
<col style="width:50%" />
<col style="width:15%" />
</colgroup>
<thead>
<tr>
<th data-field="count"># Posts</th>
<th data-field="blog">Blog</th>
<th data-field="blogdescription">Description</th>
<th data-field="maxDate">Last Post</th>
</tr>
</thead>
<tbody>
<cfoutput query="Data">
<tr>
<td>#count#</td>
<td><a href="#blogurl#" target="new">#blog#</a></td>
<td>#blogdescription#</td>
<td>#maxDate#</td>
</tr>
</cfoutput>
</tbody>
</table>
After generating the table, we simply initialize the Kendo UI Grid using a few lines of Javascript.
We are specifying the id of the table, grid, to initialize the grid. There are a few additional optional arguments that we will use to make this grid sortable.
The sortable argument makes the Kendo UI grid, well you guessed it- sortable, and the columnMenu argument places a menu at the top of the grid. The columMenu argument takes multiple additional arguments, and here we want to set filterable to false as we are binding the data to an HTML table.
These methods will be covered in more detail in future blog posts.
Initializing a Kendo UI Grid Using a ColdFusion Remote Server Endpoint
Server Side Code Using ColdFusion
The following function is in the Demo.cfc ColdFusion Component. You don't need to use a ColdFusion component as the service endpoint, you can use any ColdFusion template, however, it is useful to use a component with multiple methods for a variety of reasons. Namely, ColdFusion components use an object-oriented approach, they are generally faster than a UDF, and they are generally more secure and extensible.
This function is no different than the server-side functions that we used for the Kendo dropdowns in previous articles. We are simply querying the database and transforming the ColdFusion query object into JSON using our CfJson method and returning the JSON string. You should note that we are also using returnFormat="json" in the function declaration.
<cffunction name="getLastYearTopBlogsForGrid" access="remote" returnformat="json" output="false"
hint="Returns the most active ColdFusion blogs by most recent date for the last calendar year">
<!--- There are no arguments for this function. --->
<cfsetting enablecfoutputonly="true" />
<cfset dsn = "cfbloggers">
<cfquery name="Data" datasource="#dsn#">
SELECT
COUNT(e.posted) as count,
MAX(date_format(e.posted, '%m/%d/%Y %r')) as maxDate,
b.name as blog,
e.categories,
b.description as blogdescription,
b.url as blogurl,
b.blogsoftware
FROM entries e, blogs b
WHERE e.blogidfk = b.id
AND b.active = 1
AND e.title is not null
AND e.posted > now() - INTERVAL 12 month
GROUP BY blog
ORDER BY count DESC
</cfquery>
<!--- Using my jsonArray.cfc --->
<!--- Convert the query object into JSON using the convertCfQuery2JsonStruct method --->
<cfinvoke component="#jsonArray#" method="convertCfQuery2JsonStruct" returnvariable="jsonString">
<cfinvokeargument name="queryObj" value="#Data#">
<cfinvokeargument name="includeTotal" value="false">
</cfinvoke>
<cfreturn jsonString>
</cffunction>
The JSON String Returned by the ColdFusion Endpoint
The ColdFusion server endpoint returns the following JSON. Note: I formatted the JSON and have only included the first 5 records for brevity.
[{
"maxDate": "12/31/2021 01:46:00 AM",
"total": 30,
"blogsoftware": "http://wordpress.org/?v=2.9.2",
"count": 206,
"blogurl": "https://www.bennadel.com
",
"id": 134,
"blogdescription": "Ben Nadel's web development blog on ColdFusion, jQuery, HTML5, AJAX, SQL, and all aspects of web application development.",
"blog": "Ben Nadel",
"categories": "ColdFusion"
}, {
"maxDate": "11/08/2021 10:51:00 PM",
"total": 30,
"blogsoftware": "FeedGenerator",
"count": 124,
"blogurl": "https://www.intothebox.org
",
"id": 144,
"blogdescription": "ContentBox",
"blog": "ContentBox",
"categories": "News, Schedule, Speakers"
}, {
"maxDate": "12/17/2021 11:29:11 AM",
"total": 30,
"blogsoftware": "https://wordpress.org/?v=5.6.1",
"count": 111,
"blogurl": "https://coldfusion.adobe.com
",
"id": 104,
"blogdescription": "Connect with community",
"blog": "ColdFusion",
"categories": "Announcements,Blog,Online ColdFusion Meetup,announcements,blog,ColdFusion,online coldfusion meetup"
}, {
"maxDate": "11/13/2021 06:00:00 PM",
"total": 30,
"blogsoftware": "Eleventy",
"count": 77,
"blogurl": "https://www.raymondcamden.com
",
"id": 152,
"blogdescription": "DevRel at large, Star Wars nerd, Web/Serverless hacker, lover of good beer and good books. Oh, and cats.",
"blog": "Raymond Camden",
"categories": "eleventy,static sites"
}, {
"maxDate": "10/20/2022 04:45:00 PM",
"total": 30,
"blogsoftware": "Galaxie Blog",
"count": 74,
"blogurl": "https://www.gregoryalexander.com/blog/",
"id": 1,
"blogdescription": "A technical blog powered by Galaxie Blog - the most beautiful and functional open source ColdFusion/Kendo UI based blog in the world.",
"blog": "Gregory's Blog",
"categories": "Galaxie Blog"
}]
Inspect the JSON Yourself Using the Browsers Inspector
For debugging purposes, you often will want to inspect the JSON yourself using the browser's developer tools.
All of the major browsers have some type of developer console to inspect the web page elements and browser traffic. If you're using Chrome, launch the DevTools console by pressing the F12 button, or if you're on a Mac, press the function and F12 key at the same time.
Once the DevTools inspector is open, click on the network tab, and refresh the Kendo window and you should see a link to the Demo.cfc?method=topBlogsByPostsForCalendarYear in the list to the right. Click on it to see the JSON string returned from the server. For more information on the inspector, please refer to your browser's documentation.
The Kendo DataSource
The following Kendo DataSource invokes the topBlogsByPostsForCalendarYear function in the Demo.cfc ColdFusion component that we covered above. There is nothing different than the data source logic that we used in previous articles, however, in future Kendo Grid-related articles, we will identify the datatype inside the data source for advanced grid handling.
var topBlogsByPostsDs = new kendo.data.DataSource({
// Determines which method and cfc to get and set data.
transport: {
read: {
url: "<cfoutput>#application.baseUrl#</cfoutput>/demo/Demo.cfc?method=topBlogsByPostsForCalendarYear", // the cfc component which processes the query and returns a json string.
dataType: "json", // Use json if the template is on the current server. If not, use jsonp for cross domain reads.
method: "post" // Note: when the method is set to "get", the query will be cached by default. This is not ideal.
}//read:
},//transport:
cache: false
});//topBlogsByPostsDs = new kendo.data.DataSource
Initializing the Kendo UI Grid
The script to initialize the grid inserts the grid widget into the grid div in the client-side code.
We are using the topBlogsByPostsDs data source and setting the basic grid options to allow sorting and allowing the user to search the grid for data using filterable: true. We will cover the column menu arguments in later articles.
Arguments in the columns array are optional, you can omit them, however, the grid will render all of the columns with equal widths and the column title will be the name of the database column which is not ideal.
The columns array should always be used with the Kendo UI grid in order to specify the column title, whether you want the column to have search filters, and to set the column widths.
Here we are applying the column database name to the field, setting the title of the column, specifying whether the column filter menus are available, and set the column widths. The width argument can be a numeric value if you want to set a pixel width, or as a string, if you want to use percents.
The client-side code should be self-explanatory- the only thing of note here is that we are placing a div with the id of grid that is used to contain the Kendo grid.
<h2>Most active ColdFusion blogs in the last year</h2>
<p>Based upon the number of posts made within the last year according to <a href="www.cfblogs.org">CfBlogs.org</a></p>
<!--- Empty iv container for the grid. --->
<div id="grid"></div>
We will be covering more advanced Kendo UI Grid examples in future articles.
As we previously mentioned, the Kendo MultiSelect widget offers significant enhancements to a traditional dropdown menu and can be used to replace a long list of checkboxes. In this article, we will take a deep dive into the Kendo MultiSelect, show how it can be prepopulated with existing values, and how to dynamically populate it using cascading dropdowns.
Like the rest of the posts in this series, we will be using ColdFusion on the server side, however, it should be easily understood if you use a different server-side language, and you should be able to follow along.
Visual Demonstration of Kendo's MultiSelect vs HTML MultiSelect
A picture is worth a thousand words. I am going to contrast the Kendo MultiSelect against traditional HTML checkboxes to visually convey the benefits of using the Kendo MultiSelect.
In the following interfaces, we are prepopulating the form with the US states on the Pacific seaboard selected.
Kendo MultiSelect
As you can see here, with the Kendo MultiSelect the selected values are front and center allowing the user to quickly see what is selected without forcing them to scroll down a long list of items. The Kendo MultiSelect also has search functionality to allow the user to quickly select additional values. The Kendo MultiSelect is also much more elegant and it takes up less space on the screen.
The Kendo MultiSelect's ability to put information front and center makes it a perfect tool to conceptualize related groups of information, however, in order to convey this we need to be able to dynamically populate this list.
Traditional HTML Multi-Select
The traditional multi-select requires you to scroll way down to see Oregon and Washington that are selected, and you better make sure not to accidentally click on one of the items while scrolling or the selection will be gone!
Dynamically Removing the Selected Kendo MultiSelect Options
To remove all of the selected values in the Kendo MultiSelect, simply pass an empty array to the MultiSelect value method like so:
Populating a Kendo MultiSelect With Initial Values
Use the MultiSelect value method to populate the values of a Kendo MultiSelect.
You can either use an array with comma-separated values or an array of structures for the value. This value must have the same value and datatype as the data element that is used to populate the dataValueField. If the value and the data type do not match a data element used to populate the dataValueField, the value will be ignored. We will illustrate this below.
All of these examples are valid:
// Displays United States
value: [{ name: "United States", id: 233 }] // Note: the name will be ignored.
value: [{ id: 233}]
value: [233]
// Displays United States, United States Minor Outlining Areas
value: [233,234]
value: [{id:233},{id:234}]
However, these examples do not work. See comments in code:
[{ name: "United States" }] // United States does not match a data element in the dataValueField and will be ignored
"233,234,235"// Missing array (the square brackets)
Dynamically Populating a Kendo MultiSelect
Overview
To dynamically populate a Kendo MultiSelect, we need to inspect data using either AJAX or using the Kendo DataSource and prepare the data for the MultiSelect value method.
If you're using jQuery AJAX, and the data from the server is already a comma-separated string or an array of structures, you may be able to dump the data into the Kendo MultiSelects value method. Otherwise, you will have to prepare the string or array by looping through the data.
If you're inspecting data from a Kendo Datasource, we will use a similar approach that we would use to interrogate the data using jQuery Ajax- we will loop through the data triggered by a parent Kendo widget and populate the values array using the JavaScript push method and use the new array in the Kendo MultiSelect value method.
In this article, we will focus on interrogating data from the Kendo DataSource.
Potential Use Cases
Unlike the traditional HTML MultiSelect, a Kendo MultiSelect can be prepopulated with values to allow the user to easily visualize and interact with related groups of data. I often populate the MultiSelect widget to allow the users to conceptualize related data with a predefined starting point.
There are many other reasons to dynamically populate the MultiSelect. For example, we can clean up the selected values in a MultiSelect if the child values don't relate to other parent data. For example, in our previous Cascading MultiSelect article, we dynamically removed states from a child MultiSelect when the state's country was removed in the parent country MultiSelect menu.
MultiSelect Dynamic Population Demonstration
The following example allows the user to select a world region that they are interested in exploring. Once the user selects a world region, the region states will be dynamically populated in the child state multi-select.
We will go over the details of this code later in this article.
Countries by subregion
Extracting Data from a Kendo DataSource
To extract the data from a Kendo DataSource, we need to use Kendo's data method on the Kendo DataSource. Unfortunately, we can't use this data to directly populate the MultiSelect's value method as the JSON extracted from the Kendo DataSource has been transformed into a specialized Kendo Observable Array JavaScript object. Instead, we need to loop through the data and create a string of values separated by commas using the JavaScript push method.
The populateCountry function below performs all of the necessary steps to extract the data from the countryDs data source and populates the country MultiSelect with values. This function is invoked using the change method on the parent subregion dropdown when the user selects a country. The full code is provided at the end of this article.
This function will use the data method on the countryDs Kendo DataSource that was consumed via the change function invoked when the user selects a subregion. Here, we don't need to use the Kendo fetchor readmethod as the data source already consumed the service endpoint when a region was selected. We will cover the fetch and read methods in future articles, but they don't apply here.
After using the Kendo DataSource's data method, we will create an empty countryIdList array to hold the list of values that we will later use in the MultiSelect's value method and loop through the data, which is a specialized JavaScript array.
Our loop will be a simple JavaScript for loop, and we will continue to loop through the records until there are no more records in the Kendo Observable array.
Inside this loop, we will extract the items that we need to populate the Kendo MultiSelect. In this example, we are getting the id, which is the primary key of the country table. Once the id has been extracted, we will use the JavaScript push method to push the value into the new countryIdList array that we just created.
After the loop has been completed and all of the ids have been saved to our new array, we will use this countryIdList to populate the MultiSelect using the widgets value method.
Here is the function:
The populateCountry function
// Populate the selected MultiSelect values from the datasource
function populateCountries(e){
// Get the data from the datasource
var data = countryDs.data();
// Create an array that we will use to populate the dropdown
var countryIdList = [];
// Loop through the data to create an array to send to the multi-select
for (var i = 0; i < data.length; i++) {
// Get the countryId
var countryId = data[i].id;
// Populate our new array with the value surrounded by qoutes
countryIdList.push(countryId);
}//..for (var i = 0; i < capabilityDsData.length; i++) {
// At the end of the loop, opulate the multiSelect with the array that we just built
countryDropdown.value(countryIdList);
}//..function...
