How to update more than one table in an update form

questions tips
,
1

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)
                     END

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
  Access:JobBidBond.Fetch(JBB:JobKey)
 !Initialize and add all fields from the sister table
  SaveBidBondFields.Init()
  SaveBidBondFields.AddItem(JBB:CompletionDays)
  SaveBidBondFields.AddItem(JBB:CompletionBasedOn)

In the Window Manager Kill method you need to kill the FieldPairsClass
SaveBidBondFields.Kill()

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

  CODE
  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
         POST(Event:Accepted,SELF.OKControl)
         RETURN Level:Notify
       OF BUTTON:Cancel
         SELECT(SELF.FirstField)
         RETURN Level:Notify
       END
     End
   End
  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
  CODE
  LOOP
    IF Looped
      RETURN Level:Notify
    ELSE
      Looped  = 1
    END
    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
        WATCH(JobBidBond)
        JBB:JobUnique  = JOB:JOBUnique
        IF Access:JobBidBond.Fetch(JBB:UniqueKey) = Level:Benign
          SaveBidBondFields.AssignRightToLeft()                      !Move "new valus" back to the table record 
          Access:JobBidBond.UPDATE()
        ELSE
          JBB:JobUnique  = JOB:JOBUnique
          SaveBidBondFields.AssignRightToLeft()                      !Move "new valus" back to the table record 
          Access:JobBidBond.INSERT()
        End
      End
    End
    RETURN ReturnValue
  END
  ReturnValue  = Level:Fatal
  RETURN ReturnValue
3 Likes
2

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.

3

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?

Regards

Graeme

4

Graeme,
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.

5

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

Graeme

6

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.