jQuery Grid Plugin - jqGrid - Topic: Cold Fusion cfc Sub grid Problem http://www.trirand.com/blog/?page_id=393/help/cold-fusion-cfc-sub-grid-problem Simple:Press Version 5.7.5.3 tony on Cold Fusion cfc Sub grid Problem http://www.trirand.com/blog/?page_id=393/help/cold-fusion-cfc-sub-grid-problem#p4316 Help http://www.trirand.com/blog/?page_id=393/help/cold-fusion-cfc-sub-grid-problem#p4316 Hello,

The problem maybe is that you use capital words when you return the data, by derfault this definition is with little words - i.e. you use "ROWS" - in the subgrid definition this is "rows".

Regards

Tony

]]> Tue, 03 Feb 2009 01:40:44 +0200 Jim P on Cold Fusion cfc Sub grid Problem http://www.trirand.com/blog/?page_id=393/help/cold-fusion-cfc-sub-grid-problem#p4301 Help http://www.trirand.com/blog/?page_id=393/help/cold-fusion-cfc-sub-grid-problem#p4301 I'm using Cold Fusion CFC to handle the data, not php.  I've got a grid with a subgrid that is returning jason for the subgrid data, but I'm getting this error:

sjxml[sgmap.root] is undefined

I've been searching the jqgrid (V3.3 - Oct 08) manual to no avail.  I got my code from the manual and modified to use CF cfc.

If I run my cfc call: I get jason back:

{"ROWS":[["3218798-1","FS","October, 10 2008 15:15:13","Open sub case to track Issues with system"]],"PAGE":1.0,"TOTAL":1.0,"RECORDS":1}

Then the error:

How do I define the sqmap.root?

How can I dynamically control the +/- on the main grid row if there are subrows?

TIA - Jim

Here's my code:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>jqGrid Demo</title>
<link rel="stylesheet" type="text/css" media="screen" href="themes/coffee/grid.css" />
<link rel="stylesheet" type="text/css" media="screen" href="themes/jqModal.css" />
<!--- <cfset session.myacct = "268440464"> --->
<script src="jquery.js" type="text/javascript"></script>
<script src="jquery.jqGrid.js" type="text/javascript"></script>
<script src="js/jqModal.js" type="text/javascript"></script>
<script src="js/jqDnR.js" type="text/javascript"></script>

 <script>
 $(document).ready(function()
  {
   $("#list").jqGrid(
   {
    url:'Users.cfc?method=GetAllCases&myacct=<cfoutput>#session.bizorg_objid#</cfoutput>', //CFC that will return the users
    datatype: 'json', //We specify that the datatype we will be using will be JSON
    colNames:['ID','Case Number','Site ID','Site Name', 'Objid'], //Column Names
    //The Column Model to define the data. Note you can make columns non sortable, specify width, alignment, etc.
    // [#objid#,#x_account_id#,#x_site_id#,#x_site_name#]>
    colModel :[
     {name:'objid',index:'objid', width:100, sorttype:"int"},
     {name:'x_object_id',index:'x_object_id', width:100, align:"center", sorttype:"string"},
     {name:'x_site_id',index:'x_site_id', width:100, align:"center",sorttype:"string"},
     {name:'x_site_name',index:'x_site_name', width:200, align:"left",sorttype:"string"},
     {name:'x_object_objid',index:'x_object_objid', width:1, align:"center", hidden:"true", sorttype:"string"}
     //{name:'UserName',index:'UserName', width:150,align:"left",sorttype:"string"},
     //{name:'UserAccountingCode',index:'UserAccountingCode', width:150, sortable:false},
     //{name:'Phone',index:'Phone', width:150, sortable:false}
    ],
    pager: $('#pager'), //The div we have specified, tells jqGrid where to put the pager
    rowNum:20, //Number of records we want to show per page
    rowList:[4,8,12], //Row List, to allow user to select how many rows they want to see per page
    sortorder: "desc", //Default sort order
    sortname: "x_object_id", //Default sort column
        //myDSN: "ClarifySMST", // pass in required vars
    viewrecords: true, //Shows the nice message on the pager
    imgpath: 'themes/coffee/images', //Image path for prev/next etc images
    caption: 'Open Cases', //Grid Name
    subGrid: true, // make it a subgrid
    subGridUrl: 'Users.cfc?method=GetSubCases', //CFC that will return the users
    subGridModel: [{
       name: ['x_object_id','x_priority','x_creation_dt','x_title'], //Column Names
       width: [100, 100, 100, 200],
       params: ['x_object_id']}
    ],
    height:'auto', //I like auto, so there is no blank space between. Using a fixed height can mean either a scrollbar or a blank space before the pager
    recordtext:'Total Records', //On the demo you will notice "7 Total Records" - The Total Reocrds text comes from here
    pgtext:'of',//You notice the 1/3, you can change the /. You can make it say 1 of 3
    postdata: "",  // my account
    editurl:"Users.cfc?method=GetAllCases",//Not used right now.
    toolbar:[true,"top"],//Shows the toolbar at the top. I will decide if I need to put anything in there later.
    //The JSON reader. This defines what the JSON data returned from the CFC should look like
    jsonReader: {
     root: "ROWS",
     page: "PAGE",
     total: "TOTAL",
     records:"RECORDS",
     cell: "",
     id: "0"
     }
    }
   );   
   
  }
 );
 </script>
</head>

<body>

 <table id="list" class="scroll" cellpadding="0" cellspacing="0"></table>
 <div id="pager" class="scroll" style="text-align:center;"></div>
 <div id="mysearch"></div>
</body>
</html>

Here's my cfc:

<cfcomponent output="false">
     <!--- ======================================== --->
    
 <cffunction name="GetAllCases" access="remote" returnformat="json">
        <cfargument name="page" required="no" default="1" hint="Page user is on">
     <cfargument name="rows" required="no" default="15" hint="Number of Rows to display per page">
     <cfargument name="sidx" required="no" default="" hint="Sort Column">
     <cfargument name="sord" required="no" default="ASC" hint="Sort Order">
        <cfargument name="myacct" required="no"  hint="Biz Org to get ">
     <!---    <cfargument name="nd" required="no" default="0">   --->
        
     <cfset var arrCases = ArrayNew(1)>
          
  <cfquery datasource="ClarifySMST" name="get_cases">
   select objid, x_object_id, x_site_id, x_site_name, x_object_objid
              from SA.TABLE_X_PORTAL_OBJECT
              WHERE x_parent_id is null
              AND   x_bus_org_objid = <cfqueryparam cfsqltype="cf_sql_integer" value="#myacct#">
             <!---   --->
           <!--- Sorting Here --->
   <cfif Arguments.sidx NEQ ""> 
    ORDER BY #Arguments.sidx# #Arguments.sord#
            <cfelse>
                ORDER BY x_object_id      
   </cfif>

  </cfquery>
       
          <!--- Calculate the Start Position for the loop query.
  So, if you are on 1st page and want to display 4 rows per page, for first page you start at: (1-1)*4+1 = 1.
  If you go to page 2, you start at (2-)1*4+1 = 5  --->
  <cfset start = ((arguments.page-1)*arguments.rows)+1>
  
  <!--- Calculate the end row for the query. So on the first page you go from row 1 to row 4. --->
  <cfset end = (start-1) + arguments.rows>
  
  <!--- When building the array --->
  <cfset i = 1>
  
 
  <cfloop query="get_cases" startrow="#start#" endrow="#end#">
   <!--- Array that will be passed back needed by jqGrid JSON implementation --->  
   <cfset arrCases[i] = [#objid#,#x_object_id#,#x_site_id#,#x_site_name#,#x_object_objid#]>
   <cfset i = i + 1>   
  </cfloop>
       
    <!--- Calculate the Total Number of Pages for your records. --->
  <cfset totalPages = Ceiling(get_cases.recordcount/arguments.rows)>
  
  <!--- The JSON return.
   Total - Total Number of Pages we will have calculated above
   Page - Current page user is on
   Records - Total number of records
   rows = our data
  --->
  <cfset stcCases = {total=#totalPages#,page=#Arguments.page#,records=#get_cases.recordcount#,rows=arrCases}>
  
  <cfreturn stcCases>

  <!--- <cfreturn get_cases> --->  
 </cffunction> 
     <!--- ======================================== --->
    
 <cffunction name="GetSubCases" access="remote" returnformat="json">
       
        <cfargument name="x_object_objid" required="no"  hint="Biz Org to get ">
        <!------>
     <cfargument name="page" required="no" default="1" hint="Page user is on">
     <cfargument name="rows" required="no" default="15" hint="Number of Rows to display per page">
     <cfargument name="sidx" required="no" default="" hint="Sort Column">
     <cfargument name="sord" required="no" default="ASC" hint="Sort Order">
        <cfargument name="nd" required="no" default="0">  
           
     <cfset var arrCases = ArrayNew(1)>
          
  <cfquery datasource="ClarifySMST" name="get_subcases">
   select x_object_id, x_priority, x_creation_dt, x_title
              from SA.TABLE_X_PORTAL_OBJECT
              WHERE x_parent_id = '#arguments.x_object_id#'   
  </cfquery>
               <!--- Calculate the Start Position for the loop query.
  So, if you are on 1st page and want to display 4 rows per page, for first page you start at: (1-1)*4+1 = 1.
  If you go to page 2, you start at (2-)1*4+1 = 5  --->
  <cfset start = ((arguments.page-1)*arguments.rows)+1>
  
  <!--- Calculate the end row for the query. So on the first page you go from row 1 to row 4. --->
  <cfset end = (start-1) + arguments.rows>
  
  <!--- When building the array --->
  <cfset i = 1>
  
  <cfloop query="get_subcases" startrow="#start#" endrow="#end#">
   <!--- Array that will be passed back needed by jqGrid JSON implementation --->  
   <cfset arrCases[i] = [#x_object_id#,#x_priority#,#x_creation_dt#,#x_title#]>
   <cfset i = i + 1>   
  </cfloop>
     
    <!--- Calculate the Total Number of Pages for your records. --->
  <cfset totalPages = Ceiling(get_subcases.recordcount/arguments.rows)>
  
  <!--- The JSON return.
   Total - Total Number of Pages we will have calculated above
   Page - Current page user is on
   Records - Total number of records
   rows = our data
  --->
  <cfset stcSubCases = {total=#totalPages#,page=#Arguments.page#,records=#get_subcases.recordcount#,rows=arrCases}>
  
  <cfreturn stcSubCases> 
 </cffunction>
</cfcomponent>                                                                                                                                                             

]]> Mon, 02 Feb 2009 14:54:18 +0200