How Update 2 Tables from a Grid - CF 9

[Guest]

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:

GetRCPsForContracts.UpContract.cfc

  <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#
                 </cfquery>              
</cffunction>

ContractMngmnt.cfm

   <cfform  name="Contract_EDIT">

       <cfgrid name= "Contract_EDIT"
          title="Please update the following Contract/Task Information for your RCP"
          selectmode="edit"    
          insert="no"
          format="html"
          striperows="yes"
          bindonload="no"
          bind="cfc:ATC._cfc.GetRCPsForContracts.ChosenContract({cfgridpage},
        {cfgridpagesize},{cfgridsortcolumn},{cfgridsortdirection},  {RCPContracts.CTR_ID}, {RCPContracts.FTE_id}, {RCPContracts.clin_id})"
   onChange="cfc:ATC._cfc.GetRCPsForContracts.UpContract({cfgridaction},
                                                     {cfgridrow},
                                                     {cfgridchanged}
                                                    )">
            <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">          
          </cfgrid> 
    </cfform>

Thanks for any assistance you can provide.

Blue-Cloud

[Dan_Bracuk]

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

[Guest]

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.

Blue-Cloud

[Dan_Bracuk]

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.

[Guest]

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

Thanks for your help, Dan.

[Guest]

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#
                              </cfquery>
                      <cfelse>
                         <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#
                                </cfquery>
                            </cfif>
                      </cfif>
                 </cfif>
</cffunction>

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?

Thanks.