5 Replies Latest reply on Aug 10, 2010 1:24 PM by Blue-Cloud

    How Update 2 Tables from a Grid - CF 9


      Is there a way to do this in CF 9?


      Some of the grid columns need to update the table "contract".
      The update code for the other table, "fte" is below. It will
      fail, obviously, since several columns in the update query
      belong to the "contract" table.


      Here's the relevant code:




        <cffunction name="UpContract" access="remote"> 
           <cfargument name="gridaction" type="string" required="yes">
            <cfargument name="gridrow" type="struct" required="yes">
             <cfargument name="gridchanged" type="struct" required="yes">                         


              <cfset var qUP  = "" />
                       <!--- Get column name and value --->
                       <cfset colname=StructKeyList(ARGUMENTS.gridchanged)>
                       <cfset value=ARGUMENTS.gridchanged[colname]>
                       <!--- Perform update --->             
                          <cfquery name = "qUP" datasource="#request.ATCdsn#">
                       UPDATE fte
                       SET #colname# = '#value#'
                       WHERE FTE_id = #ARGUMENTS.gridrow.FTE_id#






         <cfform  name="Contract_EDIT">


             <cfgrid name= "Contract_EDIT"
                title="Please update the following Contract/Task Information for your RCP"
              {cfgridpagesize},{cfgridsortcolumn},{cfgridsortdirection},  {RCPContracts.CTR_ID}, {RCPContracts.FTE_id}, {RCPContracts.clin_id})"
                  <cfgridcolumn name="contract_number" header="Contract ##">                                    
                 <cfgridcolumn name="to_number" header="Task Order ##">
                 <cfgridcolumn name="award_date" header="Award Date">
                 <cfgridcolumn name="clin" header="CLIN" display="yes">
                 <cfgridcolumn name="pop_start" header="POP Start">
                 <cfgridcolumn name="pop_end" header="POP End">
                 <cfgridcolumn name="price" header="Unit Price">
                 <cfgridcolumn name="" header="Number of Units">
                 <cfgridcolumn name="units_description" header="Unit Description">
                 <cfgridcolumn name="total" header="Total Price">          


      Thanks for any assistance you can provide.



        • 1. Re: How Update 2 Tables from a Grid - CF 9
          Dan Bracuk Level 5

          You can always do it the old fashioned way.  Submit the form and write the necessary queries to do the necessary work. 

          1 person found this helpful
          • 2. Re: How Update 2 Tables from a Grid - CF 9
            Blue-Cloud Level 1

            I tried what I thought was the "old-fashioned" way, by

            using a submit button and going to a cfm page that

            checked if the submission was a "U" by looping through

            the rowstatus.action of the form. That didn't work b/c

            I needed an "onChange" to go with the "bind" parameter.


            The code I sent my forum post didn't work with the above

            method either.


            Do I need to break apart the ExtJS that underlies the

            "onChange" event to update 2 tables from the grid?


            Thanks for any assistance.



            • 3. Re: How Update 2 Tables from a Grid - CF 9
              Dan Bracuk Level 5

              Actually, it might be quite simple.


              In your function, create two lists.  Each will contain the fields associated with each table.  When you call the function, see what list the gridcolumn is in and update that table.

              • 4. Re: How Update 2 Tables from a Grid - CF 9
                Blue-Cloud Level 1

                Could you provide some pseudo code to illustrate what you mean?


                Thanks for your help, Dan.

                • 5. Re: How Update 2 Tables from a Grid - CF 9
                  Blue-Cloud Level 1

                  This is what I came up with:

                  <cffunction name="UpContract" access="remote"> 
                  <cfargument name="gridaction" type="string">
                    <cfargument name="gridrow" type="struct">
                     <cfargument name="gridchanged" type="struct">  
                                   <cfset var qUP  = "" />
                                  <!--- Get column name and value --->
                                <cfset colname=StructKeyList(ARGUMENTS.gridchanged) />                
                               <cfset value=structfind(ARGUMENTS.gridchanged,#colname#) /> 
                                  <cfset clinTab = "clin,pop_start,popend,price,units_description,total" />
                                <cfset contractTab = "contract_number,to_number,award_date" />
                                  <cfif gridaction eq "U">
                                  <cfset temp = "ListFindNoCase(clinTab, colname)" />      
                        <cfif temp neq 0> 
                                                <cfquery name = "qUP" datasource="#request.ATCdsn#">
                                                UPDATE clin
                                                SET #colname# = '#value#'
                                                WHERE FTE_id = #gridrow.FTE_id#
                                           <cfset temp = "ListFindNoCase(contractTab, colname)" />
                                             <cfif temp neq 0>      
                                                  <cfquery name = "qUP" datasource="#request.ATCdsn#">
                                                  UPDATE contract
                                                  SET #colname# = '#value#'
                                                  WHERE FTE_id_fk = #gridrow.FTE_id#


                  If I make one change to the first table, this works.

                  An additional change, with a column from a second table, doesn't work.

                  I get an error saying that the column name is invalid for the query it's trying to run.

                  It's still using the values from the change to the first table. 

                  I must be missing something in the way Coldfusion handles the update.


                  Any ideas?