0 Replies Latest reply on Aug 25, 2010 7:56 AM by Blue-Cloud

    CF 9 Related grid columns: How update 2nd column from change in 1st column

    Blue-Cloud Level 1

      I want to update a value in 1 dropdown column and display associated data
      in a second dropdown column in a cfgrid.

      For starters, I want to display the current choices for the 2 columns from the database as well
      as the associated items for the second column in a dropdown list.


      Since the columns are related, I call a cfc from the grid's bind in the cfm file. The function gets the value of the 1st column,
      then calls (through a cfinvoke) another function to get the allowable items for the second column.


      The second function does return the correct 2nd column values, BUT
      I can't display the 2nd column's values in the cfgridcolumn's
      values and valuesdisplay attributes in the cfm file.


      This is the gridcolumn code for the 2nd column:
      <cfgridcolumn name="Labor_Cat" header="Labor Category"


      CatNames is the returnvariable from the function cfinvoking the second function.




      Here's the 1st function:


      <cffunction name="GetFTEs" access="remote" returntype="struct">
          <cfargument name="page" type="numeric" required="yes">
           <cfargument name="pageSize" type="numeric" required="yes">
            <cfargument name="gridsortcolumn" type="string" required="no" default="">
           <cfargument name="gridsortdir" type="string" required="no" default="">
           <cfargument name="RCP_ID" type="string" required="yes">


          <cfset var qGetFTEs = "" />
                          <!--- Select associated FTEs from chosen RCP --->

                          <CFQUERY name="qGetFTEs" datasource="#request.ATCdsn#">
                              SELECT FTE_id, FTE.RCP_id, Funding_Year, Labor_Cat,Labor_Band,Number_of_Ftes,


      FTE.Entry_Date, FTE.Entered_By
                              from FTE, RCP_table
                              WHERE FTE.RCP_id = #RCP_ID# and FTE.RCP_id = RCP_table.RCP_id
                              <cfif ARGUMENTS.gridsortcolumn NEQ "" and #ARGUMENTS.gridsortdir# NEQ "">
                                  ORDER BY #ARGUMENTS.gridsortcolumn# #ARGUMENTS.gridsortdir#


      <cfinvoke component = "GetRCPsForFTEs" method="GetassocCats" returnvariable="CatNames">
      <cfinvokeargument name="LaborBand" value="#qGetFTEs.Labor_Band#">


      <cfreturn QueryConvertForGrid(qGetFTEs, arguments.page, arguments.pageSize)>                          




      Here is the 2nd function:


      <cffunction name="GetassocCats" access="remote" returntype="string">
            <cfargument name="LaborBand" type="string" required="yes">


                              <cfquery name="getBandID"  datasource="#request.ATCdsn#">
                          Select Band_ID
                          from LaborBand_LKUP
                          where Band_Name = '#arguments.LaborBand#'
                          <cfquery name="getAssocCATIDs"  datasource="#request.ATCdsn#">
                          Select fk_Cat_ID
                          from Labor_BandCat_LKUP
                          where fk_Band_ID = #getBandID.Band_ID#
                          <cfquery name="getAssocCATNames"  datasource="#request.ATCdsn#">
                          Select Cat_Name
                          from LaborCat_LKUP
                          where Cat_ID = #getAssocCATIDs.fk_Cat_ID#


      Here's the grid in the cfm file:


           <cfgrid name="RCPFTEs" format="html"
                          title="FTEs on this RCP"
                          bindonload="no" bind="cfc:ATC_test._cfc.GetRCPsForFTEs.GetFTEs({cfgridpage},
                <cfgridcolumn name="RCP_id" header="RCP ID" display="no">
                <cfgridcolumn name="FTE_id" header="FTE ID" display="no">                                    
                <cfgridcolumn name="Funding_Year" header="Funding Year" select="no">
                <cfgridcolumn name="Labor_Band" header="Labor Band" select="no">
                     <cfgridcolumn name="Labor_Cat" header="Labor Category"
                <cfgridcolumn name="Labor_Group" header="Labor Group">
                <cfgridcolumn name="Number_of_Ftes" header="Number of FTEs">
                <cfgridcolumn name="Entry_Date" header="Last Changed on"  select="no">
                <cfgridcolumn name="Entered_By" header="Last Changed by"  select="no">


      CatNames is the returnvariable from the function cfinvoking the second function.


      I had tried using <cfreturn QueryConvertForGrid(getAssocCATNames) in the second function and using "getAssocCatNames" in the cfgridcolumn,


      but it didn't work.


      Any ideas or other approaches?


      Thanks for any assistance!