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"
                  values="#ValueList(CATNames.CatName)#"
                    valuesdisplay="#ValueList(CATNames.CatName)#">

       

      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#
                                  </cfif>
                          </CFQUERY>                        

       


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

       


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

       

       

       

      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>
                         
                          <cfquery name="getAssocCATIDs"  datasource="#request.ATCdsn#">
                          Select fk_Cat_ID
                          from Labor_BandCat_LKUP
                          where fk_Band_ID = #getBandID.Band_ID#
                          </cfquery>
                                           
                          <cfquery name="getAssocCATNames"  datasource="#request.ATCdsn#">
                          Select Cat_Name
                          from LaborCat_LKUP
                          where Cat_ID = #getAssocCATIDs.fk_Cat_ID#
                          </cfquery>
         
      </cffunction>

       

      Here's the grid in the cfm file:

       

           <cfgrid name="RCPFTEs" format="html"
                          title="FTEs on this RCP"
                          pagesize="5"
                          collapsible="yes"
                          selectmode="edit"
                          striperows="yes"
                          selectonload="false"
                          bindonload="no" bind="cfc:ATC_test._cfc.GetRCPsForFTEs.GetFTEs({cfgridpage},
             {cfgridpagesize},{cfgridsortcolumn},{cfgridsortdirection},{RCPs4FTEs.RCP_ID})"
               onChange="cfc:ATC_test._cfc.GetRCPsForFTEs.UpFTE({cfgridaction},
                                                          {cfgridrow},
                                                          {cfgridchanged}
                                                         )">
                <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"
                      values="#ValueList(CatsName.CatName)#"
                    valuesdisplay="#ValueList(CatsName.CatName)#">
                <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">
               </cfgrid>

       


      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!