Using a Coldfusion cfc how do you format return data for jqgrid dependent selects in the edit form?

Question

I am trying to figure out how to set up dynamic dependent selects. This is an edit of my original post to try to make it more readable and clear. I have included my whole grid in case it helps but it might be too much information. The grid displays perfectly.

<!---stylesheets --->
<link rel="stylesheet" type="text/css" media="screen" href="jquery-ui-1.8.17.custom.css" />
<link rel="stylesheet" type="text/css" media="screen" href="ui.jqgrid.css" />
<script src="jquery.js" type="text/javascript"></script>

<script src="jquery-1.5.2.min.js" type="text/javascript"></script>
<script src="grid.locale-en.js" type="text/javascript"></script>
      <!--- The language file --->
<script src="jquery.jqGrid.min.js" type="text/javascript"></script>
<script>        
$(document).ready(function()
                {        
                    $("#list").jqGrid(
                    {
url:'Users2.cfc?method=getUsers', //CFC that will return the users
datatype: 'json', //We specify that the datatype we will be using will be JSON
cmTemplate:{resizable:false, width:124},

colNames:['Bill To Code','User ID', 'GL_comp_key', 'Branch ID', 'Warehouse ID', 'Final Approver',  'Active', 'Primary Approver', 'Administrative','Secondary Approver'], //Column Names
//The Column Model              

colModel :[
                {name:'ar_bill_key',index:'ar_bill_key',editable:true,searchoptions:{sopt:                 ['eq','lt','le','gt','ge','bw','ew','cn']},
                    editable:true,edittype:"text",editoptions:{size:50},editrules:{required:true},formoptions:{elmprefix:"(*)"}},
{name:'userID',index:'userID',searchoptions:{sopt:['eq','bw','ew','cn']},
                    editable:true,edittype:"text",editoptions:{size:50},editrules:{required:true},formoptions:{elmprefix:"(*)"}},

Below are the 3 fields I am trying to set up as dynamic dependent selects. Choose the gl_cmp_key and the SO_BRNCH_KEY and so_whs_key selects would display dependent lists. Do I need to have 2 different grids? One for add with dynamic dropdowns and one for edit with dynamic dependent dropdowns?

                {name:'gl_cmp_key',index:'gl_cmp_key',searchoptions:{sopt:['eq','bw','ew','cn']},
//the values are hard-coded here to get something started.
editable:true,edittype:"select",editoptions:{value:['37','36','CM','35']},editrules:{required:true},formoptions:{elmprefix:"(*)"}},

    {name:'SO_BRNCH_KEY',index:'SO_BRNCH_KEY',searchoptions:{sopt:['eq','bw','ew','cn']},editable:true,edittype:"select",editoptions:{dataUrl:'Users2.cfc?method=getBrnchKey'}, 
    buildSelect: function(data) { 
                 var response = jQuery.parseJSON(data.responseText); 
                 var s = '<select>'; 
                 if (response && response.length) { 
                     for (var i = 0, l=response.length; i<l ; i++) { 
                         var ri = response[i]; 
                         s += '<option value="'+ri+'">'+ri+'</option>'; 
                     } 
                 } 
                 return s + "</select>"; 
             },

This is the data I get in the firebug JSON response from cfc shown below . This does not display in the select:
{"COLUMNS":["SO_BRNCH_KEY"],"DATA":[["BC30"],["BM35"],["BF50"],["BF51"],["BF53"],["BF54"],["BTCO"],["BF55"],["BF56"],["BD65"],["BW66"],["BI75"],["BI76"],["BI77"],["BI78"],["BI79"], ["BI80"],["BI81"],["BCFG"],["BC43"],["BC44"],["BC48"],["BC49"],["BCCO"],["BCMN"],["BCSA"]]}

How do I format this to display as html. I realize the columns, data format does not work. Here is is the CFC: Select so_brnch_key FROM so_brnch_tbl

 </cffunction>

I have tried changing the return format. Although I see changes in the data in firebug then, no display.

                 editrules:{required:true},formoptions:{elmprefix:"(*)"}},
                {name:'so_whs_key',index:'so_whs_key',searchoptions:{sopt:['eq','bw','ew','cn']},
                    editable:true,edittype:"select",editoptions:{dataUrl:'Users2.cfc?method=getWhsKey'},editrules:{required:true},formoptions:{elmprefix:"(*)"}},

//fields for grid select yes or no

                {name:'final_approver',index:'final_approver',searchoptions:{sopt:['eq','bw','ew','cn']},
                    editable:true,edittype:"select",editoptions:{value:"1:Yes;0:No"},editrules:{required:true},formoptions:{elmprefix:"(*)"}},  
                {name:'active',index:'active',searchoptions:{sopt:['eq','bw','ew','cn']},
                    editable:true,edittype:"select",editoptions:{value:"1:Yes;0:No"},editrules:{required:true},formoptions:{elmprefix:"(*)"}},
                {name:'primary_approver',index:'primary_approver',align:"left",sorttype:"text",searchoptions:{sopt:['eq','bw','ew','cn']},
                    editable:true,edittype:"select",editoptions:{value:"1:Yes;0:No"},editrules:{required:true},formoptions:{elmprefix:"(*)"}},  
                {name:'admin',index:'admin',searchoptions:{sopt:['eq','bw','ew','cn']},
                    editable:true,edittype:"select",editoptions:{value:"1:Yes;0:No"},editrules:{required:true},formoptions:{elmprefix:"(*)"}},  
                {name:'secondary_approver',index:'secondary_approver',searchoptions:{sopt:['eq','bw','ew','cn']},
                    editable:true,edittype:"select",editoptions:{value:"1:Yes;0:No"},editrules:{required:true},formoptions:{elmprefix:"(*)"}},

            ],

pager: $('#pager'), //The div tells jqGrid where to put the pager
rowNum:20, //Number of records to show per page
rowList:[20,30,40,50], //Row List, to select how many rows  to see per page
sortorder: "asc", //Default sort order
sortname: "ar_bill_key", //Default sort column
viewrecords: true, //Shows the message on the pager
caption: 'Permissions', //Grid Name
recordtext: "Record {0} - {1} of {2}",

//Pager information

     rownumbers: true,
     rownumWidth: "30",
     sortable: true,
     height:'auto',
     mtype:'POST',  
     toolbar:[true,"top"],              

//The JSON reader. defines the JSON data returned from the CFC
jsonReader: {
    root: "ROWS", //our data
                page: "PAGE", //current page
                total: "TOTAL", //total pages
                records:"RECORDS", //total records
    userdata:"USERDATA", //Userdata we will pass back for feedback
    cell: "", //Not Used
    ar_bill_key: "0",//Will default to first column
    id:"10" 
                },
    editurl:"Users2.cfc?method=addeditUser" //The Add/Edit function call                
            }               
        ).navGrid('#pager',
            {
        search:true,searchtitle:"Search",//title set for hover
        edit:true,edittitle:"Edit User",viewPagerButtons:false,
        add:true,addtitle:"Add User",
        del:false,deltitle:"Delete User"
            },

//Edit Options. savekey parameter will keybind the Enter key to submit.

{editCaption:"Edit User",edittext:"Edit",closeOnEscape:true, savekey:  [true,13],errorTextFormat:commonError,reloadAfterSubmit:true,bottominfo:"Fields marked with (*) are required",top:"60",left:"70",width:500}, 
//Add Options
{width:500,addCaption:"Add User",edittext:"Add",closeOnEscape:true,savekey: [true,13],errorTextFormat:commonError,reloadAfterSubmit:true,bottominfo:"Fields marked with (*) are required",top:"60",left:"70"}, 

//Search

{errorTextFormat:commonError,Find:"Search",closeOnEscape:true,caption:"Search Users",multipleSearch:true,closeAfterSearch:true}
        );          

//Function called when add/edit encounters an error. The returned message is what will be shown to user

function commonError(data)
{           
return "Error Occured during Operation. Please try again";
}

});

</script>

<table id="list" class="scroll" cellpadding="0" cellspacing="0"></table>

<div id="pager" class="scroll"></div>

I have a few problems: how to format the data for form edit or add selects from the cfc, how to display dynamic selects and how to set them up as dependent for the form edit. Using dataUrl and value I have gotten the column name and a list of values to display but incorrectly (not shown). Any direction to tutorials, or posts would be appreciated. I will post my solution when found. Thanks

Answer 1

Yes, you should use the editoptions: dataUrl parameter, just as you've done for in your posted colModel. However, the response from your CFC is incorrect. From the jqGrid docs:

The editoptions dataUrl parameter is valid only for element of edittype:select. The dataUrl parameter represent the url from where the html select element should be get. When this option is set, the element will be filled with values from the AJAX request. The data should be a valid HTML select element with the desired options - something like:

<select> 
<option value='1'>One</option> 
<option value='2'>Two</option> 
...
</select>

So, in your case, rather than returning a query object as JSON from your CFC, you need to return HTML. Currently, your CFC returns this:

{"COLUMNS":["glKEY"],"DATA":[[""],[35.0],[""],[""],[""]]}

But you should return this (based purely on your example data):

<select>
  <option></option>
  <option>35.0</option>
  <option></option>
  <option></option>
  <option></option>
</select>