Paging grid w/coldfusion

[llisam]

I'm trying to get the paging footer to work with coldfusion and maybe I'm not doing the coldfusion part correctly because for some reason, even though my query returns 36 results, the paging footer always displays 1-10 (the limit I've specified).

What I'm doing on the coldfusion page is restricting the returnset to 10, but returning the totalProperty count as 36 in my cf generated xml.

Am I doing this correctly? Below is the cf page (the stored proc is returning all records now until I modify the sp to take in a start and a limit parameter) Thanks for any help!
~Lucy



<cfsetting enablecfoutputonly="yes">

<cfparam name="locationid" default="">
<cfparam name="numofdays" default="45">
<cfparam name="sortByField" default="">
<cfparam name="start" default="0">
<cfparam name="limit" default="10">

<cfset lastrunjobdt = CreateDateTime(Year(now()), Month(now()), Day(now()), 3, 0, 0)>

<CFSET xmldoc = "<?xml version=""1.0"" encoding=""UTF-8""?>
<dashboardpatients>">

<cfquery name="Get_NewPatReg_DashboardList" datasource="reg_dsn">
{CALL Get_NewPatReg_DashboardList (#locationid#, #numofdays#)}
</cfquery>

<cfparam name="end" default="#Get_NewPatReg_DashboardList.RecordCount#">
<cfif start IS NOT "">
<cfset end = start + limit>
</cfif>

<cfoutput>
<cfloop query="Get_NewPatReg_DashboardList">
<cfif Get_NewPatReg_DashboardList.CurrentRow GTE start AND Get_NewPatReg_DashboardList.CurrentRow LTE end>
<CFSET xmldoc = xmldoc & "
<patient>
<patientid>#trim(XMLFormat(patientid))#</patientid>
<createdate>#DateFormat(createdate, 'mm/dd/yyyy')#</createdate>
<lastrunjobdt>#DateFormat(lastrunjobdt, 'mm/dd/yyyy')#</lastrunjobdt>
<locationid>#locationid#</locationid>
<lastname>#trim(XMLFormat(lastname))#</lastname>
<firstname>#trim(XMLFormat(firstname))#</firstname>
<patientname>#Get_NewPatReg_DashboardList.CurrentRow# #trim(XMLFormat(UCASE(patientname)))#</patientname>
<eventtype>#trim(XMLFormat(eventtype))#</eventtype>
<startdate>#trim(XMLFormat(DateFormat(startdate, "mm/dd/yyyy")))#</startdate>
<needinfo>#trim(XMLFormat(needinfo))#</needinfo>
<needdocs>#trim(XMLFormat(needdocs))#</needdocs>
<icr>#trim(XMLFormat(icr))#</icr>
<bvf>#trim(XMLFormat(bvf))#</bvf>
<worklists>#trim(XMLFormat(worklists))#</worklists>
</patient>">
</cfif>
</cfloop>
<cfset xmldoc = xmldoc & "<total>" & Get_NewPatReg_DashboardList.Recordcount &"</total>">
</cfoutput>

<CFSET xmldoc = xmldoc & "
</dashboardpatients>">

<cfcontent type="text/xml">
<cfoutput>#xmldoc#</cfoutput>



// create the Data Store
var ds = new Ext.data.Store({
// load using HTTP
proxy: new Ext.data.HttpProxy({url: index_content_dashboard_xml.shtml?locationid=' + js_locationid + '&numofdays=' + js_numofdays}),

// the return will be XML, so lets set up a reader
reader: new Ext.data.XmlReader({
// records will have an "Item" tag
record: 'patient',
id: 'patientid',
totalProperty: 'total'
}, [
// set up the fields mapping into the xml doc
// The first needs mapping, the others are very basic
'patientid', 'createdate', 'lastrunjobdt', 'locationid', 'patientname', 'eventtype', 'startdate', 'needinfo', 'needdocs', 'icr', 'bvf', 'worklists'
])
});

var cm = new Ext.grid.ColumnModel([
{header: "MPI", width: 75, dataIndex: 'patientid', renderer: renderMPI},
{header: "Patient Name", width: 175, dataIndex: 'patientname', renderer: renderPatientName},
{header: "Event Type", width: 80, dataIndex: 'eventtype'},
{header: "Start Date", width: 80, dataIndex: 'startdate'},
{header: "Need Info", width: 65, dataIndex: 'needinfo', renderer: renderNeedInfo},
{header: "Need Docs", width: 65, dataIndex: 'needdocs', renderer: renderNeedDocs},
{header: "ICR", width: 50, dataIndex: 'icr', renderer: renderICR},
{header: "BVF", width: 50, dataIndex: 'bvf', renderer: renderBVF},
{header: "Worklists", width: 50, dataIndex: 'worklists', renderer: renderWorklists}
]);
cm.defaultSortable = true;

var grid = new Ext.grid.Grid('dashboard-grid', {
ds: ds,
cm: cm
});

grid.render();

ds.load({params:{start:0, limit:10}});

var gridFoot = grid.getView().getFooterPanel(true);
var paging = new Ext.PagingToolbar(gridFoot, ds, {
pageSize: 10,
displayInfo: true,
displayMsg: 'Displaying patients {0} - {1} of {2}',
emptyMsg: "No patients to display"
});

paging.bind(ds);

});

[evant]

It looks correct, use firebug to check the XML being returned and ensure that it's only returning 10 records.

[llisam]

Thanks for the response! But it looks like the XML is correct as I can see it is returning the correct number requested.

One thing I noticed though is the total is returned as 10 instead of 36. Would that be the reason why the paging footer is not working correctly? Thanks for your help.

~Lucy

[evant]

Total should always return the total number, ignoring paging.

[JeffHowden]

I'd start off with some adjustments to your CFML. There are spots in it that are unnecessarily confusing. The biggest confusion is by using string concatenation to build up the XML you're sending down the pipe. This isn't necessary at all. Since you're returning the results as text/xml, you can just output it to the page and it'll accomplishing the same thing without the need for the string building exercise. The second thing that's adding confusion is not using the startrow/endrow attributes of the <cfloop> tag which enable you to loop over only a portion of a recordset. That said, here's my adjusted version of your code:


<cfsetting enablecfoutputonly="yes">

<cfparam name="locationid" default="">
<cfparam name="numofdays" default="45">
<cfparam name="sortByField" default="">
<cfparam name="start" default="0">
<cfparam name="limit" default="10">

<cfscript>
lastrunjobdt = CreateDateTime(Year(Now()), Month(Now()), Day(Now()), 3, 0, 0);
</cfscript>

<cfquery name="Get_NewPatReg_DashboardList" datasource="reg_dsn">
{CALL Get_NewPatReg_DashboardList (#locationid#, #numofdays#)}
</cfquery>

<cfscript>
end = Get_NewPatReg_DashboardList.RecordCount;
if(end - start GT limit)
end = start + limit;

start = start + 1;
</cfscript>

<cfcontent type="text/xml"><cfoutput><?xml version="1.0" encoding="UTF-8"?>
<dashboardpatients><cfloop query="Get_NewPatReg_DashboardList" startrow="#start#" endrow="#end#">
<patient>
<patientid>#Trim(XMLFormat(patientid))#</patientid>
<createdate>#DateFormat(createdate, 'mm/dd/yyyy')#</createdate>
<lastrunjobdt>#DateFormat(lastrunjobdt, 'mm/dd/yyyy')#</lastrunjobdt>
<locationid>#locationid#</locationid>
<lastname>#Trim(XMLFormat(lastname))#</lastname>
<firstname>#Trim(XMLFormat(firstname))#</firstname>
<patientname>#Get_NewPatReg_DashboardList.CurrentRow# #Trim(XMLFormat(UCASE(patientname)))#</patientname>
<eventtype>#Trim(XMLFormat(eventtype))#</eventtype>
<startdate>#Trim(XMLFormat(DateFormat(startdate, "mm/dd/yyyy")))#</startdate>
<needinfo>#Trim(XMLFormat(needinfo))#</needinfo>
<needdocs>#Trim(XMLFormat(needdocs))#</needdocs>
<icr>#Trim(XMLFormat(icr))#</icr>
<bvf>#Trim(XMLFormat(bvf))#</bvf>
<worklists>#Trim(XMLFormat(worklists))#</worklists>
</patient></cfloop>
<total>#Get_NewPatReg_DashboardList.Recordcount#</total>
</dashboardpatients></cfoutput>

[llisam]

Sorry the cf code was confusing - I had originally generated an xml file since I had trouble getting the grid to work.

But I took your revisions and unfortunately the paging still isn't working. I'm not sure where else to look.

I thought it was the recordcount but actually it is returning the total number of records correctly. :( Any other ideas?

[evant]

Until you get it working, just hardcode correct data into your server-side page. This will identifying whether it's a javascript problem or a data problem.

[dawesi]

this is usually caused by bad data formatting returned from the server.

A common way to do this is to use json.cfc (google it). There are other examples on these forums of how to use it correctly.

Also check out cfext.com in the next few weeks. (launching soon - cf and ext)

[pmarcotte]

@llisam, I just got my paging grid working with JSON yesterday. I concur with dawesi that json.cfc is a good way to serialize your data sets. My solution for limiting the returned records isn't the most elegant, but I'm willing to share it if you're interested. :)

@dawesi, looking forward to checking out cfext!

[llisam]

Thank you for all the responses, I will try json after I get rid of a pesky js error. I really like the paging footer but it's more of a nice-to-have than a requirement for my app.

I have to say, I really appreciate the quick responses from everyone, I'm beginning to become an ext fan!

~Lucy

[JeffHowden]

So what isn't working about the paging? Is the entire recordset being returned as XML? Or, is a portion of it being returned, but starting and ending at the wrong rows? Or, does it all load up like it should on the first page, but get all messed up when you try to move to the second page? If you can be more specific, I can help you find the answer.

[llisam]

The problem is that even though I have 100 or so records and return only 10 in the xml (with the total set to 100) the footer always displays 1 of 10 records. And so there is only ever 1 page to page over.

The xml seems correct but I've gotten suggestions to try json instead. So will try that once I get my other js error out of the way. Thanks for your help though!

~Lucy

[JeffHowden]

Would be handy to see a (sanitized, if necessary) sample of XML the grid is trying to render.

[jpetilo]

index.cfm

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<link rel="stylesheet" type="text/css" href="resources/css/ext-all.css" />
<script type="text/javascript" src="yui-utilities.js"></script>
<script type="text/javascript" src="ext-yui-adapter.js"></script>
<script type="text/javascript" src="ext-all.js"></script>
<script type="text/javascript">
Ext.onReady(function(){

var ds = new Ext.data.Store({
proxy: new Ext.data.HttpProxy({
url: 'results.cfm'
}),

reader: new Ext.data.JsonReader({
root: 'results',
totalProperty: 'total'
},[
{name: 'admin', mapping: 'admin', type: 'int'},
{name: 'caminho_arquivo', mapping: 'caminho_arquivo'},
{name: 'email', mapping: 'email'},
{name: 'idade', mapping: 'idade'},
{name: 'nome', mapping: 'nome'},
{name: 'senha', mapping: 'senha'},
{name: 'sexo', mapping: 'sexo'},
{name: 'sobrenome', mapping: 'sobrenome'},
{name: 'usuario', mapping: 'usuario'}

]),

remoteSort: false
});


var cm = new Ext.grid.ColumnModel([{
header: "Usuario",
dataIndex: 'usuario',
sortable: false,
resizable: false,
width: 50
},{
header: "Nome",
dataIndex: 'nome',
sortable: true,
width: 60
},{
header: "Sobrenome",
dataIndex: 'sobrenome',
sortable: true,
width: 80
},{
header: "Email",
dataIndex: 'email',
sortable: true,
width: 85
},{
header: "Sexo",
dataIndex: 'sexo',
sortable: true,
width: 45
},{
header: "Idade",
dataIndex: 'idade',
sortable: true,
width: 45
},{
header: "Pasta Particular ",
dataIndex: 'caminho_arquivo',
sortable: true,
width: 202
},{
header: "Senha",
dataIndex: 'senha',
sortable: true,
resizable: false,
width: 80
},{
header: "Admin?",
dataIndex: 'admin',
sortable: true,
width: 80
}]);

// by default columns are sortable
cm.defaultSortable = true;

// create the editor grid
var grid = new Ext.grid.Grid('grid-paging', {
ds: ds,
cm: cm,
selModel: new Ext.grid.RowSelectionModel({singleSelect:true}),
enableColLock:false,
loadMask: true
});

// make the grid resizable, do before render for better performance
var rz = new Ext.Resizable('grid-paging', {
wrap:true,
minHeight:100,
pinned:true,
handles: 's'
});
rz.on('resize', grid.autoSize, grid);

// render it
grid.render();


var gridFoot = grid.getView().getFooterPanel(true);


// add a paging toolbar to the grid's footer
var paging = new Ext.PagingToolbar(gridFoot, ds, {
pageSize: 25,
displayInfo: true,
displayMsg: 'Mostrando registros {0} - {1} de {2}',
emptyMsg: "Sem registros para exibir"
});


// trigger the data store load
ds.load({params:{start:0, limit:25}});
});
</script>
<title>Meu Primeiro Grid com EXT-JS</title>
</head>

<body>
<div id="grid-paging" style="border:1px solid #99bbe8;overflow: hidden; width: 100%; height: 300px;"></div>
</body>
</html>

toJson.cfc

<cfcomponent>
<cffunction name="queryToJSON" returnType="string" access="public" output="false" hint="Converts a query to JSON">
<cfargument name="data" type="query" required="true" />
<cfargument name="rootelement" type="string" required="true" />
<cfargument name="totalProperty" type="string" required="true" />
<cfargument name="cDataCols" type="string" required="false" default="" />

<cfset var s = createObject('java','java.lang.StringBuffer').init("") />
<cfset var col = "" />
<cfset var columns = LCase(arguments.data.columnlist) />
<cfset var txt = "" />
<cfset var d1="" />
<cfset var d2="," />

<cfset s.append('{"#arguments.rootelement#":[') />
<cfloop query="arguments.data">
<cfset s.append("#d1#{") />
<cfset d1="" />
<cfloop index="col" list="#columns#">
<cfset txt = arguments.data[col][currentRow] />
<cfif isSimpleValue(txt)>
<cfif listFindNoCase(arguments.cDataCols, col)>
<cfset txt = escText(txt) />
<cfelse>
<cfset txt = safeText(txt) />
</cfif>
<cfelse>
<cfset txt = "" />
</cfif>

<cfset s.append('#d1#"#col#":"#txt#"') />
<cfset d1=d2 />
</cfloop>

<cfset s.append("}") />
</cfloop>
<cfset s.append("], ""#totalProperty#"":#data.recordcount#}") />
<cfreturn s.toString() />
</cffunction>
</cfcomponent>

mySample results.cfm using ObjectBreeze

result.cfm

<cfsetting enablecfoutputonly="yes">
<cfset ob= createObject("component", "objectBreeze").init("MySQL","guest").list("users")>
<cfset url.callback = createObject("component", "toJSON").queryToJSON( ob.getQuery(), "results","total")/>
<cfoutput>#url.callback#</cfoutput>


Jeff