Gregory's Blog

A Comprehensive Analysis of the Editable Kendo UI Grid


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.



Implementing an Editable Kendo Grid

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:

toolbar: ["search"],
search: {
	fields: [
		{ name: "name", operator: "eq" },
		{ name: "description", operator: "contains" },
		{ name: "url", operator: "contains" },
	]
},

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.

toolbar: ["pdf", "excel", "create", "save", "cancel", "search"],
excel: {
	allPages: true
},

The columnmenu Configuration 

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.

sortable: {
	mode: "multiple",
	allowUnsort: true,
	showIndexes: true
},

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 pageSizes argument 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.

pageable: {
	pageSizes: [15,30,50,100],
	refresh: true,
	numeric: true
},

The columns Configuration 

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. 


The Grid Configuration Code

$("#feedsGrid").kendoGrid({
	dataSource: feedsDs,
	editable: true,
	// Toolbars. 
	toolbar: ["pdf", "excel", "create", "save", "cancel", "search"],
	excel: {
		allPages: true
	},
	// General grid elements.
	height: 740,// 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: "17%",
		template: '<a href="#= url #">#= name #</a>'
	}, {
		field:"description",
		title: "Description",
		filterable: true,
		width: "19%"
	}, {
		field:"url",
		title: "Blog URL",
		filterable: true,
		width: "15%"
	}, {
		field:"rss_url",
		title: "RSS URL",
		filterable: true,
		width: "15%"
	}, {
		field:"blog_software",
		title: "Blog Generator *",
		filterable: true,
		width: "9%"
	}, {
		field:"notes",
		title: "Notes",
		filterable: true,
		width: "17%"
	}, { 
		command: ["destroy"], title: "&nbsp;", width: "7%" 
	}
	]// columns:

});// $("#feedsGrid").kendoGrid({

Client-Side HTML

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:

  1. 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.
  2. 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.
  3. 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. 

This entry was posted on January 3, 2023 at 11:43 PM and has received 125 views.

CFBlogs.org 2.0 Released


The new version of CFBlogs ColdFusion Blog Aggregator has been released.

This version displays all of the blog posts in an attractive three-column card layout and displays the open graph image or a site image at the top of the post. The card images should allow the user to quickly convey the author of the post. Users can sort the grids by author by clicking on the card image.

This page also has several data grids in order to analyze ColdFusion blogging trends. I have put extra data available to the grids, such as including the generating blog software. Data from the grids and calendar widget on the bottom of the sidebar (opened by clicking on the hamburger) suggests that the current state of the ColdFusion blogging community is stronger than ever.

Advanced search capabilities are also present. Users can search from one or more active blog sites. There are nearly 150 ColdFusion-related blogs and all of the blogs have several years of data from which to search.  This should prove to be useful for ColdFusion developers wanting to research a particular ColdFusion-related topic.

This site should improve the SEO score of each blog covered as it provides a backlink to the blog postings. Google, and other search engines, consider backlinks 'votes' for a particular post and hopefully, this will improve the search scores for all the blogs covered. Additionally, there is also a ColdFusion community menu at the top of the page allowing users to quickly find other ColdFusion-related resources. 

CFBlogs also displays the posts from CFOverflow, and aggregates all of the posts to the CFBlogsFeed on Twitter, and the RSS feed can be used to show the ColdFusion blog posts on your own websites, instructions are found below.

The CFBlogs website can be found at https://www.cfblogs.org/

If you have any suggestions or want to add your own blog to the CfBlogs aggregator, please contact me.


How to put the CFBlogs feed on your own website

If you want to place the CFBlogs Feed on your own website, copy and paste the following code on your own site. An example of the following code can be found by clicking on the hamburger on this site.

<table align="center" class="k-content fixedPodTableWithWrap" width="100%" cellpadding="7" cellspacing="0">
	<cftry>
		<cfsilent>
		<cfset theURL = "https://gregoryalexander.com/cfblogs/rss.cfm">
		<cfhttp url="#theURL#" timeout="5">
		<cfset xml = xmlParse(cfhttp.filecontent)>
		<cfset items = xmlSearch(xml, "//*[local-name() = 'item']")>
		<!--- Set a loop counter to keep track of the current row for display purposes. --->
		<cfset feedLoopCount = 1>
		</cfsilent>
		<cfloop index="x" from="1" to="#min(arrayLen(items),5)#">
			<cfsilent>
			<cfset item = items[x]>
			<!-- Create alternating rows in the table. The Kendo classes which we will use are k-alt and k-content.
			We will create a border between the rows if the current row is not the first row. -->
			<cfif feedLoopCount mod 2>
				<cfset thisClass = 'k-content'>
			<cfelse>
				<cfset thisClass = 'k-alt'>
			</cfif>
			</cfsilent>
			<tr class="<cfoutput>#thisClass#</cfoutput>" height="35px;">
				<!--Create the nice borders after the first row.-->
				<cfif feedLoopCount eq 1>
				<td valign="top">
				<cfelse>
				<td align="left" valign="top" class="border">
				</cfif>
					<!--Display the content.-->
					<cfoutput>
					#item.comments.xmlText#<br/> 
					<a href="#item.link.xmlText#" <cfif darkTheme>style="color:whitesmoke"</cfif>>#item.title.xmlText#</a><br />
					</cfoutput>
				</td>
			</tr>
			<!---Increment the loop counter--->
			<cfset feedLoopCount = feedLoopCount + 1>
		</cfloop>
		<cfcatch>
			<tr>
				<td>
				<cfoutput>
				CFBloggers down
				</cfoutput>
				</td>
			</tr>
		</cfcatch>
	</cftry>
</table>
<br/>

 

This entry was posted on December 5, 2022 at 5:49 PM and has received 379 views.

Introducing the Kendo Grid


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.



Kendo Grid Demonstration

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:

  1. Local JavaScript Data
  2. Remote Server Endpoint
  3. Kinvey Backend Services
  4. GraphQL Service
  5. SignalR
  6. WebSockets

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.


<script>
	$(document).ready(function() {
		$("#grid").kendoGrid({
		sortable: true,
		columnMenu: {
			filterable: false
		}
		});
	});
</script>


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.

var topBlogsByPosts = $("#grid").kendoGrid({
	dataSource: topBlogsByPostsDs,
	sortable: true,
    columnMenu: {
        filterable: false
    },
    // Column properties
    columns: [{
        field:"count",
        title: "# Posts",
        filterable: true,
        width:"15%"
    }, {
        field:"blog",
        title: "Blog",
        filterable: true,
        width:"20%"
    }, {
        field:"blogdescription",
        title: "Description",
        filterable: true,
        width:"50%"
    }, {
        field:"blogurl",
        title: "URL",
        filterable: true,
        width:"20%"
    }]// columns:
});// var topBlogsByPosts = $("#grid").kendoGrid

Client Side Code

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.

Thanks for reading!

Further Reading


This entry was posted on October 31, 2022 at 10:10 PM and has received 283 views.

Dynamically Populating Kendo MultiSelects


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:

$("#stateDropdown").kendoMultiSelect({
	placeholder: "Select...",
	autoBind: false,
	dataTextField: "name",
	dataValueField: "id",
	filter: "contains",
	dataSource: stateDs,
	value: []
}).data("kendoMultiSelect");

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 fetch or read method 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...

This entry was posted on October 21, 2022 at 12:45 AM and has received 274 views.

Cascading Kendo MultiSelect Dropdowns


In this example, we will demonstrate how to create cascading menus' with Kendo MultiSelects. We have covered cascading menus with the dropdown lists, but there are fundamental differences.

Telerik has an example of how to create cascading dropdowns with MultiSelects, however, in my opinion, it is too complex and will suggest a simpler straightforward event-based approach. We will also briefly cover how to inspect the data coming from the parent dropdown data source and use the JavaScript push method to deselect MultiSelect values chosen by the user as well as using the Kendo DataSource to group city data in the menu.

In this example, I will try my best to replicate the UI of James Moberg's 'North America Search Demo' found at https://www.sunstarmedia.com/demo/countrystate/. James is a frequent contributor to the ColdFusion community and his elegant demo is used in production environments. Jame's demo uses a different library, Select2 UI, but it uses the same concepts that we want to show here.

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.



Cascading MultiSelects Demonstration

 

Cascading MultiSelects Approach

We have already introduced this approach in our previous Cascading Country, State and City Kendo UI Dropdowns article and will briefly cover the major details.

Gregory's Approach to handle cascading dropdowns:

  1. Initialize the dropdowns as you normally would- but add an onChange method.
  2. Use the drop-down widgets onChange method to:
    1. Capture and store the user's selection into a hidden form field.
    2. Refresh any dependent dropdowns using the widgets refresh method.
  3. The data sources belonging to the dependent dropdowns will pass the necessary values in the hidden form fields to the remote end-point.
  4. If necessary, use the DataSource's onChange method to:
    1.  dynamically populate the values of a dependent MultiSelect
    2. or validate the data and perform any additional logic.

Differences in this approach when using the MultiSelect

  • For the most part, the cascading MultiSelect dropdowns are nearly identical to the cascading dropDownLists that we have already covered.
  • For the dependent multi-select dropdown, we are using different server-side logic in order to query the database allowing multiple choices for cities and countries. 
  • The major differences on the client side are that we are using multi-selects and passing comma-separated values to the server, and we are not disabling the multi-selects when there is a change in the state dropdown.
  • We are not using the Kendo DropDownList's select argument to set the default value of the Kendo MultiSelect when changes are made to the parent dropdowns. Instead, when a state is deselected; we need to dynamically recreate the values in the MultiSelect.

First Country Dropdown

The first dropdown is a Kendo DropDownList widget; I could have made it a multi-select, but the city list is already quite long even when a unique state is selected. I did not want to have a scenario where cities are shown for multiple states and multiple countries. This would negatively impact the performance of the UI and potentially crash the browser.

This dropdown is identical to the state dropdown in our Cascading Country, State, and City Kendo UI Dropdowns article. This dropdown list contains the flags of each country, if you are interested in how to accomplish this please see Adding the Country Flag to the DropDownList

Since we have already covered this dropdown, I will not share the code or elaborate on the details but will include the links to the original article below:

  1. Create the first dropdown
  2. Create The Country Dropdown Kendo Datasource
  3. Adding the Country Flag to the DropDownList
  4. Initialize the Country DropDownList
  5. The Country DropDownList onChange Event

Second State MultiSelect Dropdown

The state multi-select dropdown is dependent on the country dropdown and contains all of the states for the selected country and is a required field.

The user must select either one or more states. The state onChange event is used to fire another function to populate the cities into the next multi-select dropdown. The state multi-select dropdown is quite similar to the state dropdown-list, but has a few changes that I will note.


Server-Side Logic with ColdFusion

We are going to use the same method from the World.cfc that we discussed in our Using ColdFusion to Populate Kendo UI Widgets article. In a nutshell, this method queries the World, Countries City database and converts the ColdFusion query into a JSON string using our CfJson ColdFusion component. In this example, we are passing in the countryId from the country dropdown. We don't need to pass multiple values as the parent country dropdown is a Kendo DropDownList and not a MultiSelect.

Here is the server-side code:

<cffunction name="getStates" access="remote" returnformat="json" output="true"
		hint="Gets the world states by a variety of optional arguments">
	<cfargument name="countryId" type="string" required="false" default="" />

	<cfquery name="Data" datasource="cityDb">
		SELECT id, name, latitude, longitude FROM states
		WHERE 0=0
	<cfif len(arguments.countryId)>
		AND country_id = <cfqueryparam value="#arguments.countryId#" cfsqltype="integer">
	</cfif>
		ORDER BY name
	</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="false">	
	</cfinvoke>

	<!--- Return it. --->
	<cfreturn jsonString>

</cffunction>

The State Kendo DataSource

In this example, even though we are using a MultiSelect here instead of a DropDownList, the Kendo data source of the state dropdown is identical to the DropDownList state data source in our previous article. The only difference here is that in this example, the data-sources change method is used on rare occasions to enable and populate the cities dropdown when there is no state.

// ----------- State Datasource. -----------

// First state dropdown.
var stateDs = new kendo.data.DataSource({
	transport: {
		read: {
			cache: false,
			// The function allows the url to become dynamic to append additional arguements.
			url: function() { return "<cfoutput>#application.baseUrl#</cfoutput>/demo/WorldCountries.cfc?method=getStates&countryId=" + $('#selectedCountry').val(); },
			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"
		}
	},
	// Function to enable the city dropdown when a state does not exist for a selected country. This will only fire when there is no data in the Kendo datasource and will enable the city dropdown in order for the user to still be able to pick a city. This is fired everytime there is a change to the state datasource
	change: function() {
		// Get the datasource length
		var data = this.data();
		// Set a var for the city dropdown list.
		var cityDropdown = $("#cityDropdown").data("kendoMultiSelect");
		// If there are no states for this country...
		if (!data.length){
			// Now enable the city dropdown list.
			cityDropdown.enable();
			// Refresh the city dropdown
			cityDropdown.dataSource.read();
			// Note: we are not disabling the city MultiSelect in an else block if there is no data. Doing so would disable the city dropdown if you have made a new city selection.
		}//if (! data.length){..

		// Get the Kendo button at the end of the interface
		var cityButton = $("#cityButton").data("kendoButton");
		// Disable the the button
		cityButton.enable(true);

	}//change: function() {..
});//var stateDs...

// Note: in the cascading dropdown example, we are using the select event for the state dropdown to set the option to the default 'Select...' placeholder (stateDropdown.select(0)). However, this is not necessary here, and the select method does not exist for the multiSelect

Initializing the State MultiSelect

This is nearly identical to the state drop-down list example in our previous article. There are a few differences- we are substituting the string kendoMultiSelect for kendoDropdownList, using placeHolder instead of optionLabel for the hint. We are also calling functions via two MultiSelect events- the change event, which we have already covered, and a new deselect event that is specific to the Kendo MultiSelect.

Introducing Kendo MultiSelect Deselect Event

As we previously discussed in our prior article, every widget supports a certain set of events. Often, an event may be unique to a particular widget, such as the deselect event here with the Kendo MultiSelect.

All of the widgets supported events are documented on the Kendo UI site- search for the widget documentation and click on the API button.

Here, we need to use the deselect event to determine if any cities should be removed if the user deselects a given state. This event is fired every time a user removes one of the multi-select elements that they have previously chosen. We will use this event to remove any chosen cities when the parent state is deselected by the user. In this example, once a user deselects a state we will call the onStateDeselect function which will be covered later in this article.

// Create the state dropdown list
var stateDropdown = $("#stateDropdown").kendoMultiSelect({
	optionLabel: "Select State...",
	dataValueField: "id",
	dataTextField: "name",
	autoBind: false,
	dataSource: stateDs,
	filter: "contains",
	change: onStateChange,
	// Deselect event to remove cities if the parent state was removed by the user
	deselect: onStateDeselect,
}).data("kendoMultiSelect");//var stateDropdown...

State onChange Method

Just like the onChange method for the Kendo drop-down list, this method saves the selected values into the cityDropdown hidden form field and enables and refreshes the data in the next city multi-select.

/ ------------ onChange Event ------------
// Function to enable the last city dropdown menu
function onStateChange(e){
	// Get the next dropdown list.
	var cityDropdown = $("#cityDropdown").data("kendoMultiSelect");
	// Save the id in a hiden form in order to get at it in the next dropdown
	var id = this.value();
	// If the user selected a value, enable and refresh the next dropdown list
	if (id != '') {
		$("#selectedState").val(this.value());
		// Enable the city dropdown list.
		cityDropdown.enable();
		// Refresh the city dropdown
		cityDropdown.dataSource.read();
	}//..if (id != '') 

}//function onStateChange(e)

The Optional State onStateDeselect Function

This function is used to perform clean-up operations when a state is deselected from the state multi-select.

In our last cascading dropdown article, we reverted all of the child dropdowns to the initial value when a parent dropdown was changed (see the 'stateDropdown.select(0)' line at the very end of the code example of our previous Initializing the second state dropdown article).

Here, we need to do something similar when a state is deselected. For example, let's say a user selects the state of New York and California- and selects the city of New York and Los Angeles. If the user then deselects the state of New York, we will want to also remove the city of New York. However, we will leave the city of Los Angeles as the state of California was not deselected. 

The best way to understand what is going on is to interact with the demonstration at the top of this article. Select at least two states and at least one city from each state. After making the selections, deselect one of the states and the associated cities that you selected will also automatically be deselected as well.

To perform this cleanup, we are going to use JavaScript to inspect the data coming back from the server and dynamically re-populate the Kendo MultiSelect. Don't worry about the details of this code yet- we are going to cover this in detail in our next article- just note what the purpose of this code is.

This step is completely optional. If you don't want the complexity of this code you can simply remove the cities whenever something is deselected by keeping the cities in the list or by setting the values of the multi-select to null. See the code below.


// ------------ onDeselect Event ------------
function onStateDeselect(e) {
	/* 
	Inspect the transformed JSON JavaScript object from the datasource to determine if we should eliminate certain cities that belonged to the deselected state
	*/

	// Determine what was just deselected
	var dataItem = e.dataItem;

	// Note: the dataValueField and dataTextField are available in this struct 
	var deselectedStateId = dataItem.id;
	var deselectedState = dataItem.name;

	// Get any cities that were selected
	var selectedCities = $("#selectedCity").val();

	// Get a reference to the city dropdown. We will use this in the loop below to set its items.
	var cityDropdown = $("#cityDropdown").data("kendoMultiSelect");

	// Now get a reference to the city dropdown datasource. Here we are setting the the datasource using dropDownVarName.datasource. Note: if this code is not inside of a document ready block, you can use the var that was created when creating the datasource. In this case it would be cityDs. However, if this is inside of a ready block, you will get a 'Cannot read properties of undefined' error. This is always a factor when using code inside of a document ready block.
	var cityDs = cityDropdown.dataSource;
	// Get the underlying datasource data. Note, using the cityDs variable that we created above won't work- we need to use the full chained name here. 
	var cityData = cityDropdown.dataSource.data(); 
	// Clear the previous values in the multiSelect
	cityDropdown.value([]);

	// Fetch the data from the cityDs Kendo datasource. We can also use the read method
	cityDs.fetch(function(){
		// Create an array in order to populate multiple values 
		var cityIdList = [];
		// Loop through the data to create an array to send to the city multi-select
		for (var i = 0; i < cityData.length; i++) {
			// Get the cityId
			var cityStateId = cityData[i].state_id;
			var cityId = cityData[i].id;
			// Check to see if the stateId matches the deselected state when the selected city is found in the datasource (listFind Javascript function: listFind(list, value, delimiter))
			if (listFind(selectedCities,cityId) > 0){
				// If the city does not reside in the state that was just deselected, add it to cityIdList array using the JS push method 
				if (cityStateId != deselectedStateId){
					// Use the Javascript push method to push the city into our cityIdList array that we will use to repopulate the MultiSelect.
					cityIdList.push(cityId);
				}
			}
		}//..for (var i = 0; i < cityIdList.length; i++) 
		// Repopulate the multiselect
		cityDropdown.value(cityIdList);
	});//..cityDs.fetch(function(){
}

Alternative Deselect Function to Remove All of the Selected MultiSelect Options on Deselect

To remove all of the selected options in the Kendo MultiSelect, simply use the following code:

// ------------ onDeselect Event ------------
function onStateDeselect(e) {
	/* This function will remove all of the cities selected by the user by setting the MultiSelect value to null */
	// Get a reference to the city dropdown. 
	var cityDropdown = $("#cityDropdown").data("kendoMultiSelect");
	// Set the value to null (with an empty array)
	cityDropdown.value([]);
}

The Dependent City MultiSelect Dropdown

This multi-select is dependent upon the chosen values in the country dropdown and the state multi-select. This example has some very minor differences from the first state multi-select. One of the main differences is that since the user can select multiple states with the multi-select instead of just one when using the drop-down list, the ColdFusion logic on the server side must change to allow for multiple states to be passed in. We are also going to introduce Datasource grouping.

Introduction to Grouping Using the Kendo DataSource

When using a widget that allows the user to select multiple values, like the MultiSelect, it is nice to be able to group the data of the next dependent dropdown by the selected values. Nearly every Kendo Widget, including the MultiSelect, has the ability to group data. You are not limited to grouping the data by the dropdown value or the label- you can use any field in the JSON that is returned by the server. 

To group data, simply add the field to the JSON from the server and add that field to the Kendo UI group argument in the Kendo data source. You don't need to use grouping in your SQL- the only thing that you need is the JSON string to return the field that you want to group by, add the field that you want to group by, and the Kendo data source will perform the grouping for you.

Here, we will add grouping to group all of the cities by the chosen states.


Grouping the City MultiSelect Dropdown by the Selected State

If you want to group the city MultiSelect by the states, we need to:

  1. Add the state name column to the ColdFusion query that generates the JSON
  2. Add the new field to the group argument in the Kendo DataSource.

Server Side Logic using ColdFusion

Adding the State Name Column

To group by the state, the state field must be returned in the JSON. We are going to change the query in the getCities method in the World component on the server and join the country table to add the state name.

For Kendo MultSelects, the Query Clause Must Also Support Multiple Values

The state MultiSelect widget will either pass one or more numeric values, separated by commas when a selection is made. This selection is saved into the selectedCity hidden form and passed along using the Kendo DataSource.

This ColdFusion function takes these comma-separated values and validates the list using the justNumericList custom function to ensure that the values are numeric and do not have any empty values. This validation step is necessary as the database query will fail if there are empty or non-numeric values.

There are many ways to accomplish this, but in this example, I chose to use the justNumericList function as it was modified by James Moberg, and had initially assumed that he modified this function for his own cascading dropdowns that we are replicating. However, after creating the logic for this article, I found out that he is using this function for different purposes. 

Once the comma-separated stateIdList string has been validated, we will query the database with these values using the SQL IN keyword. Once the query has been made, the ColdFusion Query Object will be transformed into JSON using the convertCfQuery2JsonStruct method found in the CFJson component.

<cffunction name="getCitiesByStateIdListGroupByState" access="remote" returnformat="json" output="true"
		hint="Gets the world cities using a stateId list. This is a separate function to keep it isolated from the getCities function">
	<cfargument name="stateIdList" type="string" required="true" default="" />

	<cfset validatedNumericList = justNumericList(arguments.stateIdList)>

	<cfquery name="Data" datasource="cityDb">
		SELECT states.id as state_id, states.name as state, cities.id, cities.name, cities.latitude, cities.longitude 
		FROM cities
		INNER JOIN states 
		ON states.id = cities.state_id
		WHERE 0=0
		AND cities.state_id IN (<cfqueryparam value="#validatedNumericList#" cfsqltype="integer" list="yes">)
		ORDER BY cities.name
	</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>

	<!--- Return it. --->
	<cfreturn jsonString>

</cffunction>

The City Kendo DataSource

The Kendo DataSource takes the values from the hidden selectedState form field and passes the comma-separated list to the getCitiesByStateIdList method. We are also grouping the data by the state name using group: { field: "state" }. This will group the data by the state in the dropdown.

This grouping is more effective when the data is not as extensive. When there are scores of cities for each state, like in this example, you will have to scroll quite a bit if the state is not the first entry in the list. 

// City populated by the second state dropdown.
var cityDs = new kendo.data.DataSource({
	transport: {
		read: {
			cache: false,
			// The function allows the url to become dynamic to append additional arguements. Here we are sending both the countryId and the stateId. There are some countries that do not have states.
			url: function() { return "<cfoutput>#application.baseUrl#</cfoutput>/demo/WorldCountries.cfc?method=getCitiesByStateIdListGroupByState&stateIdList=" + $('#selectedState').val(); },
			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"
		}//read..
	},//transport..,
	// Group by the state
	group: { field: "state" }
});//var cityDs..

Initializing the City MultiSelect 

This function is fundamentally the same as city dropDownList that we have previously covered, however, the string kendoDropDownList is replaced with kendoMultiSelect, and optionLabel is replaced with placeHolder. Also, we will not use the select argument and the end of the initialization to default the multi-select to the first value in the list (the multi-select does not have an initial value and thus does not support the select argument).

// ----------- City MultiSelect. -----------
var cityDropdown = $("#cityDropdown").kendoMultiSelect({
	placeHolder: "Select City...",
	dataTextField: "name",
	dataValueField: "id",
	autoBind: false,
	enable: false,
	filter: "contains",
	change: onCityChange,
	dataSource: cityDs
});//var kendoMultiSelect...

// Note: the select method does not exist for the multiSelect

The City onChange Method

The city onChange method simply saves the selected cities into the selectedCity hidden form and enables the button at the bottom of the UI once a selection has been made.

// ------------ onChange Event ------------
// Function to enable the button to launch a new window showing the details
function onCityChange(e){
	var id = this.value();
	// If the user selected a value, enable teh button at the end of the interface
	if (id != '') {
		// save the selected value in a hidden form
		$("#selectedCity").val(this.value());
		// Get the Kendo button at the end of the interface
		var cityButton = $("#cityButton").data("kendoButton");
		// Enable the button
		cityButton.enable(true);
	} else {
		// Disable the button at the bottom of the UI
		cityButton.enable(false);
	}//..if (id != '') {
}//function onStateChange(e)

// Create the last button.
var cityButton = $("#cityButton").kendoButton({
	enable: false
}).data("kendoButton");

Client Side HTML

The client-side HTML is straightforward. Here we have 3 hidden form fields: selectedCountry, selectedState, and the selectedCity. All of these hidden fields are populated by the widget's respective onChange events. The countryDropdown, stateDropdown, and cityDropdown fields contain the Kendo UI widgets, and the 'View City Details' button at the end of the UI in this example is for visual purposes.

<table width="100%" class="k-content">
  <input type="hidden" name="selectedCountry" id="selectedCountry" value=""/>
  <input type="text" name="selectedState" id="selectedState" value=""/>
  <input type="hidden" name="selectedCity" id="selectedCity" value=""/>
  <tr>
	<td align="left" valign="top" class="border" colspan="2"></td>
  </tr>
  <tr>
	<td align="right" style="width: 20%">
		<label for="countryDropdown">Country:</label>
	</td>
	<td>
		<!-- Create the country dropdown -->
		<select id="countryDropdown" name="countryDropdown" style="width: 95%"></select>
	</td>
  </tr>
  <tr>
	<td align="left" valign="top" class="border" colspan="2"></td>
  </tr>
   <tr>
	<td align="right" style="width: 20%">
		<label for="stateDropdown">State/Province:</label>
	</td>
	<td>
		<!-- Create the state dropdown -->
		<select id="stateDropdown" name="stateDropdown" style="width: 95%"></select>
	</td>
  </tr>
  <tr>
	<td align="left" valign="top" class="border" colspan="2"></td>
  </tr>
  <tr>
	<td align="right" style="width: 20%">
		<label for="cityDropdown">City:</label>
	</td>
	<td>
		<!-- Create the state dropdown -->
		<select id="cityDropdown" name="cityDropdown" style="width: 95%" disabled></select>
	</td>
  </tr>
  <tr>
	<td align="left" valign="top" class="border" colspan="2"></td>
  </tr>
  <tr>
	<td></td>
	<td>
		<button id="cityButton" name="cityButton" class="k-button k-primary" type="button" disabled>View City Details</button>
	</td>
  </tr>
</table>

Further Reading

This entry was posted on October 13, 2022 at 12:29 AM and has received 222 views.




Footer Logo

Your input and contributions are welcomed!

If you have an idea, BlogCfc based code, or a theme that you have built using this site that you want to share, please contribute by making a post here or share it by contacting us! This community can only thrive if we continue to work together.

Images and Photography:

Gregory Alexander either owns the copyright, or has the rights to use, all images and photographs on the site. If an image is not part of the "Galaxie Blog" open sourced distribution package, and instead is part of a personal blog post or a comment, please contact us and the author of the post or comment to obtain permission if you would like to use a personal image or photograph found on this site.

Credits:

Portions of Galaxie Blog are powered on the server side by BlogCfc, an open source blog developed by Raymond Camden. Revitalizing BlogCfc was a part of my orginal inspiration that prompted me to design this site.

Version:

Galaxie Blog Version 3.12 (Toby's Edition) December 10th 2022 Delicate Arch theme