How to update more than one table in an update form

Tags: #<Tag:0x00007f46e27d14e8> #<Tag:0x00007f46e27d1380>


Here is a method to use if you have an update form on a particular table and you want to be able to have fields from another related table.
This works one there is a one-to-one relationship between the two tables. I’ll call the second table a “sister” table in this example.
The key is to use a FieldPairsClass to determine if changes have been made to the sister table at the correct points in the update form.
The two things you need to catch is when the user saves the form and when the user cancels the form.
When the user saves the form you need to update or insert the sister table row if changes have been made.
When the user cancels you need to see if changes have been made to the sister table and prompt the user to see if they want cancel or save their changes.

In my example the update form is on the Job table and the sister table is the JobBidBond table. There is only one JobBidBond row for each Job row.

First declare your FieldPairsClass

    SaveBidBondFields    CLASS(FieldPairsClass)

In the Window Manager INIT method after the tables are opened, you need to fetch the sister table record if it exists and then initialize the FieldPairsClass

   JBB:JobUnique = JOB:JOBUnique
 !Initialize and add all fields from the sister table

In the Window Manager Kill method you need to kill the FieldPairsClass

In the TakeCloseEvent you can determine if the form is closing when changes have been made.
This will prompt the user if they want to save if they’ve changed a value in the sister table.

ThisWindow.TakeCloseEvent PROCEDURE

ReturnValue          BYTE,AUTO

  ReturnValue = PARENT.TakeCloseEvent()
   If ReturnValue = Level:Benign AND SELF.Primary.Me.EqualBuffer(SELF.Saved)
     If NOT SaveBidBondFields.Equal() 
       CASE SELF.Errors.Message(Msg:SaveRecord,Button:Yes+Button:No+Button:Cancel,Button:Cancel)
       OF Button:Yes
         RETURN Level:Notify
       OF BUTTON:Cancel
         RETURN Level:Notify
  RETURN ReturnValue

Finally, you need to save the sister table if the main update form is being saved. This is done in TakeCompleted.

ThisWindow.TakeCompleted      PROCEDURE

ReturnValue                     BYTE,AUTO

Looped                          BYTE
    IF Looped
      RETURN Level:Notify
      Looped  = 1
    ReturnValue  = PARENT.TakeCompleted()
    If Returnvalue = Level:Benign                                    !Successfully saved the main table
      If NOT SaveBidBondFields.Equal()
        SaveBidBondFields.AssignLeftToRight()                        !Save the "new value" in the internal buffer inside fieldspair class
        JBB:JobUnique  = JOB:JOBUnique
        IF Access:JobBidBond.Fetch(JBB:UniqueKey) = Level:Benign
          SaveBidBondFields.AssignRightToLeft()                      !Move "new valus" back to the table record 
          JBB:JobUnique  = JOB:JOBUnique
          SaveBidBondFields.AssignRightToLeft()                      !Move "new valus" back to the table record 
    RETURN ReturnValue
  ReturnValue  = Level:Fatal
  RETURN ReturnValue

Calling update form to update two tables?

Neat idea. Thanks Rick.

I like to find uses for the fieldpairsclass too. I have a global proc where I pass a fieldpairs object to set up whichever groups of fields I commonly use.
For example, one table only gets the time stamp updated if certain fields are updated. The procedure sets up these “rules” in one place.


Hi Rick, I have finally managed to clear my updates backlog and got some time to get back onto this magic code you kindly supplied. I have got it working which is great. One thing I have noticed though is if I make a change in the field in the sister file and then select another tab and then come back the changes are gone.

Just wondering if you have seen this?




Sounds like you have the sister file setup in the data pad as related to the primary file so it is getting “lookedup” in the thisWindow.Reset method. Move the sister file to the “Other” files. You’ll have to manually fetch the record in the window .Init method, if you aren’t already doing that.


Thanks Rick, yes that is exactly what I had done which would have obviously been overriding what I have placed in the window .Init method. Will give it a go later.

Many thanks



Going back to the 1:1 update situation in Rick’s original post, if one is using SQL, it seems important to me to consider moving such code to an SQL trigger. Relying upon application code to maintain the 1:1 requirement could be risky if future data access is not solely limited to the one application.

Not sure about other backends, but in PostgreSQL this can be achieved using an INSTEAD OF trigger on a view.