Ask a Jedi: Noticing an empty CFGRID

This post is more than 2 years old.

Brett asked:

I have a CFM with a CFGrid that uses a Bind to a CFC. On the CFM page a I have a few CFSelects that allow users to filter down the results being displayed in the CFGrid. I have also included a text input for keywords that uses a vertiy search to filter the results down even more. However is there a way to display to the user that no records were found from their search, the only thing that I get now is an empty grid.

To get an idea of what Brett was talking about, I created a simple grid example that used a text box to filter results. <cfform name="test"> <input type="text" name="filter"> <cfgrid autowidth="true" name="entries" format="html" bind="cfc:test.getEntries({cfgridpage},{cfgridpagesize},{cfgridsortcolumn},{cfgridsortdirection},{filter@keyup})" width="600"> <cfgridcolumn name="id" display="false"> <cfgridcolumn name="body" display="false">

<cfgridcolumn name="title" header="Title"> <cfgridcolumn name="posted" header="Posted"> </cfgrid> </cfform>

The CFC method in the back end is a simple query against my blog database. I added an additional argument, filter, that will limit the results when something is typed in the filter. You can see an example of this here. Type something wacky (like dflkldfk) in the field and the grid will empty out. There is no real simple visual cue that the results were filtered to an empty set. (Well, ok, it is obvious to me, but I can see some users getting confused.)

I began to dig into the grid to see if there was a nice way to handle this. I started off by setting up a function to run when everything was loaded:

<cfset ajaxOnLoad('init')>

In my init() function, I began by grabbing a reference to the grid:

var mygrid = ColdFusion.Grid.getGridObject('entries')

I jumped over to the Ext docs and began to dig. I knew that I could get access to the datasource with the following:

var ds = mygrid.getDataSource()

Then I checked to see if there was an event I could listen for that involved data. Yep, a nicely named "load" event.

ds.addListener('load', function() {

The addListener function takes the name of an event to listen to and an inline function.

So what next? The ds object represents the data. But here's the weird thing. Even when you see nothing in the grid, you still have 10 rows of data in the grid. I'm guessing this is something on the ColdFusion side, or perhaps just how Ext works. What you can do - though - is look at the data. This is what I used:

var count = 0; ds.each(function() { if(this.data.ID != null) count++ })

Basically, loop over the data, and check the field (ID comes from one of my columns) to see if it is null. This worked perfectly. I brought it all together by using jQuery to update a div when count was 0. It may be overkill to use jQuery for one line of code, but, meh, I'm a fan boy. So here is the entire CFM:

<html>

<head> <script src="jquery/jquery.js"></script> <script> function init() { console.log('Ran') var mygrid = ColdFusion.Grid.getGridObject('entries') //console.dir(mygrid) var ds = mygrid.getDataSource() ds.addListener('load', function() { //console.dir(ds) var count = 0; ds.each(function() { if(this.data.ID != null) count++ }) if(count == 0) $("#msg").text('Sorry, but there are no records to display.') else $("#msg").text('') })

}; </script> </head>

<body> <cfform name="test"> <input type="text" name="filter"> <cfgrid autowidth="true" name="entries" format="html" bind="cfc:test.getEntries({cfgridpage},{cfgridpagesize},{cfgridsortcolumn},{cfgridsortdirection},{filter@keyup})" width="600"> <cfgridcolumn name="id" display="false"> <cfgridcolumn name="body" display="false">

<cfgridcolumn name="title" header="Title"> <cfgridcolumn name="posted" header="Posted"> </cfgrid> </cfform>

<div id="msg"></div>

</body> </html>

<cfset ajaxOnLoad('init')>

Notice the new DIV at the bottom. That's just there for the message. You could use an alert or some other way to warn the user. You can play with this code here.

Raymond Camden's Picture

About Raymond Camden

Raymond is a senior developer evangelist for Adobe. He focuses on document services, JavaScript, and enterprise cat demos. If you like this article, please consider visiting my Amazon Wishlist or donating via PayPal to show your support. You can even buy me a coffee!

Lafayette, LA https://www.raymondcamden.com

Archived Comments

Comment 1 by Scott Stroz posted on 4/9/2009 at 9:52 PM

@Ray - the 'empty' rows are a result of using QueryConvertForGrid(). When you use that function, CF will 'pad' the results with empty rows. If you have 20 items per page and the last page only has 12 items, your query will have 8 empty rows.

Seems kind of odd to me, but there are ways around it.

Comment 2 by Steve &aposCutter&apos Blades posted on 4/9/2009 at 10:01 PM

Ext 2.0 added the ability to set options for the GridView, including an 'emptyText' attribute allowing a developer to define text to display in the grid when the record count is 0. (Different issue, as Scott pointed out. Hopefully there's an Ext upgrade in Centaur.)

Comment 3 by Justin Carter posted on 4/9/2009 at 10:04 PM

Yep Scott is spot on, QueryConvertForGrid() is the culprit. The only way I know to work around it is to do you own paging and JSON serialisation instead of using QueryConvertForGrid().

Comment 4 by Raymond Camden posted on 4/9/2009 at 10:09 PM

I wonder what would happen if you manipulated the result from queryConvertForGrid before returning it?

Comment 5 by Raymond Camden posted on 4/9/2009 at 10:15 PM

Well well well. I wrote this very horrible, non var scoped, code to copy the query into a new query. The new query only has the proper amount of rows:

<cfset foo = queryConvertForGrid(q,arguments.page,arguments.pagesize)>
<cfset zoo = queryNew(foo.query.columnList)>
<cfloop index="x" from="1" to="#foo.totalrowCount#">
<cfset queryAddRow(zoo)>
<cfloop index="l" list="#zoo.columnlist#">
<cfset theval = foo.query[l][x]>
<cfset querySetCell(zoo, l, theval)>
</cfloop>
</cfloop>
<!---
<cfdump var="#zoo#">
<cfdump var="#queryConvertForGrid(q,arguments.page,arguments.pagesize)#"><cfabort>
<cfreturn queryConvertForGrid(q,arguments.page,arguments.pagesize)>
--->
<cfset foo.query = zoo>
<cfreturn foo>

When run, the grid now actually shrinks to the size of the result set.

Hmm. That may be bad for times when the record larger than page count.

Comment 6 by Raymond Camden posted on 4/9/2009 at 10:16 PM

Changing the loop to:

<cfloop index="x" from="1" to="#min(foo.totalrowCount,arguments.pagesize)#">

actually works. Kinda cool. Not sure if that is 'better' or not.

Will do a second blog entry today showing this method as well.

Comment 7 by Justin Carter posted on 4/9/2009 at 10:39 PM

It's probably nicer to do your own paging at the database level (if performance was a concern), but this is at least a good hack to remove those pesky blank rows :) I'm guessing this is something that will get cleaned up in a future CF release though too.

Comment 8 by Raymond Camden posted on 4/9/2009 at 10:42 PM

To be fair, I think a grid that always shows X rows is better than one that shrinks. The UI is just wonky (to me anyway). You would think there would be a nicer solution though. The grid itself should have an option to NOT shrink when rows returned < page size. CF could then return smaller data which would help improve performance. I'm putting the blame back on Ext. You going to stand for that Steve? ;)

Comment 9 by Justin Carter posted on 4/9/2009 at 10:47 PM

Usually with paging grids I set a static height in pixels so that the grid doesn't shrink when the result page doesn't have "enough" records, works a treat. I'd guess the <cfgrid> tag has a height attribute, but to be honest I've never used it in production code :)

Comment 10 by Raymond Camden posted on 4/9/2009 at 10:49 PM

Oh snap - you are right. Setting the height works. The only issue is that you have to supply an initial height that works well with 10 rows. Which isn't horrible.

All fodder for the next blog entry. Thanks Justin.

Comment 11 by Michael Schmidt posted on 4/9/2009 at 11:12 PM

I actually had to tackle the QueryConvertForGrid for another developer the other day and had to write this quick work around hack
<cfset iCount = Max(0,(rs.RecordCount - ((Arguments.page -1 )* arguments.pagesize) ) )>
<cfset retStr = QueryConvertForGrid(rs,arguments.page,min(arguments.pagesize, iCount))>

Comment 12 by Sean Sekora posted on 5/1/2009 at 6:02 PM

Thanks Michael Schmidt your idea worked in my case.

Comment 13 by Adam posted on 7/20/2009 at 11:50 PM

Forgive my newb-ness. I am wondering what the edit is with the cfc to enable the "filter" to filter. I am new to cfcs and find this entry well-suited for my needs.

Thank you,
Adam

Comment 14 by Adam posted on 7/21/2009 at 12:35 AM

I was able to solve this on my own. Thanks for the helpful post.

Comment 15 by kelli posted on 10/10/2009 at 5:10 AM

Has anyone noticed that the 'load' listener function is ran twice?

Comment 16 by James Lamar posted on 5/21/2010 at 5:22 PM

Could you provide the CFC code? I am getting the error: Error invoking CFC /test.cfc : Error Executing Database Query. I am also using CF9.

Thanks

Comment 17 by Raymond Camden posted on 5/21/2010 at 5:25 PM

I can, but it won't help you. The code was specific to BlogCFC. There are other examples though of CFCs hooked up to CFGRID. Here is one: http://www.garyrgilbert.com...

Comment 18 by sainisha posted on 6/23/2010 at 11:45 PM

iam trying to use cfgrid with bind and all i get back is grid with no data in it. i have tried the basic simple example and it doesnt work. however it works great with the query but not when i use the bind attribute. please help. i need to be able to complete my task quickly and this has been dragging me down.

Comment 19 by Raymond Camden posted on 6/28/2010 at 5:17 PM

What does Firebug show you?

Comment 20 by Michael Appenzellar posted on 7/21/2010 at 5:16 PM

You have helped me more than enough lately but when I go to http://www.coldfusionjedi.c... I don't see a message displayed, only a blank cfgrid..tested in firefox, and in ie I get the console firebug error.

Comment 21 by Raymond Camden posted on 7/23/2010 at 6:23 AM

This was written for CF8. The JS API changed for grids in CF9.

Comment 22 by Misty posted on 9/25/2011 at 2:57 PM

Hi Ray, it still does not work, even after loading and if cfgrid comes as empty, it shows both the messages

Comment 23 by Raymond Camden posted on 9/26/2011 at 8:30 PM

Please see the comment above. This is an older blog post for CF8. It would need to be updated for CF9. As I don't really make use of CFGRID, I have no plans to.

Comment 24 by Fernando Lins posted on 11/23/2011 at 10:44 PM

Just completing Steve's suggestion, in CF9 you can do this:

ColdFusion.Grid.getGridObject('my_grid').view.emptyText = "No Records were found with the specified search criteria";

And the message will appear if the grid is empty.

Comment 25 by Mike Weisert posted on 1/5/2012 at 12:07 AM

For CF9, you will want to change where it says .getDataSource() to just .getStore().

I display the records this way, but the above works too. Mine is based on CF9.0.1 which is using Ext Grid 3.1. Not sure if it will work on out of the box 9:
mygrid = ColdFusion.Grid.getGridObject('yourGridName');

// FOR CF 9 ROW COUNTING
var mds = mygrid.getStore()
mds.addListener('load', function() {
$("h2").html(mds.getTotalCount() + ' ');
});

Comment 26 by Robert posted on 5/23/2013 at 11:03 PM

I wondered why my cfgrid was empty when it moved to production. Turns out it doesn't like some data. When a company name ends in something like /W/ it halts and throws an error in firebug: "missing ] after element list" If I take out the slashes it moves on until it encounters another /whatever/ and halts. Is there a way to escape characters? This is just an html grid from a query. No cfc or binding.

Comment 27 by Raymond Camden posted on 5/23/2013 at 11:16 PM

All I can suggest is ensuring you are patched to latest. This is one more reason why I recommend folks not use any CF UI stuff.