Exchange of idea about child records

[daikos]

Good afternoon everybody.

I'm here to exchange an idea with the fellows about child records. Let's suppose that I have a table Employees and it's related with another table named Hours.

Employees have a 1:many relationship with Hours where its recorded worked hours that this employee make in a month. When I open my form, I show the data of the employee and a browse with the hours.

If I'm working in ChangeRecord no problems, because the parent already exists and insert the childs is easy. My problem is when I insert new records where the parent not already exists.

At this point I worked 3 solutions:

1) Create a temporary .tps file that contains the child records and then when I accept the parent record i push all that info into DB.
2) Create a temporary table in DB that makes the same process but uses a Stored Procedure.
3) Use 2 different forms: one for InsertRecord (that uses .tps) and one for Change/ViewRecord that works directly at DB.

I don't know but I think all this 3 solutions is kind wrong and must have an easy way to do it. What the fellows think about it?

I appreciate all input.

Best Regards.

 

[ShankarJ]

Hi!

I assume that the tables Employee & Hours are related by EmployeeNo. So, why don't you just pass the EmployeeNo to the HoursForm so that it can add the row with the Employee Link. And, do not allow HoursForm to be called if EmployeeNo is blank.

Regards

 

[daikos]

Good Morning, ShankarJ.

That's what I do (pass the Employee number to HoursForm) but, think about it:
The employee ShankarJ does not (yet) exists, so when I add the first worked hour it gives me a "Violation of foreign key".
That's just an example because my application is quite different.
I know that the correct is, first add the parent (commit it) and after add the childrens (hours) but what if I need to do it at the same time?

Best Regards.

 

[ShankarJ]

Hi!

The problems you have are because your database is SQL and the table relationship are enforced.

So, in this case the BEST solution is to force the saving of the PARENT row before the first CHILD row is INSERTED.

Paste the text below into a text file with an extension of TPL and register it.

#!*****************************************************************************
#!*****************************************************************************
#!
#TEMPLATE(SHANK_TPL, 'CUSTOM TEMPLATE OF SHANKAR'), FAMILY('ABC')
#!
#!*****************************************************************************
#!*****************************************************************************
#!
#!-----------------------------------------------------------------------------
#EXTENSION(ShankSmartParentChild,'ABC Smart Parent-Child Handling'),PROCEDURE,MULTI
#!-----------------------------------------------------------------------------
#SHEET,HSCROLL
 #TAB('General')
  #BOXED('Parent File')
   #PROMPT('File/Table',FILE),%ParentFile,REQ,UNIQUE,DEFAULT('')
   #PROMPT('Primary Key',KEY),%ParentKey,REQ,UNIQUE,DEFAULT('')
   #DISPLAY('')
   #PROMPT('OK Control Button',CONTROL),%ParentOKCtl,DEFAULT('?Ok')
  #ENDBOXED
  #BOXED('Controls to Disable/Enable')
   #BUTTON('Controls'),AT(,,173,),MULTI(%CToggled,%CName)
     #PROMPT('Control Name: ',CONTROL),%CName,REQ,UNIQUE,DEFAULT('')
   #ENDBUTTON
  #ENDBOXED
 #ENDTAB
#ENDSHEET
#!
#!
#!---------------------------------------------------------------------
#AT(%WindowManagerMethodCodeSection,'Init','(),BYTE'),PRIORITY( 8100 )
#!---------------------------------------------------------------------
IF ThisWindow.Request = InsertRecord
  #FOR(%CToggled)
   DISABLE(%CName)
  #ENDFOR
  #IF (%ParentOKCtl)
   %ParentOKCtl{PROP:Text} = 'Save'
  #END
   #EMBED(%ShankSmartParentChildDISABLE,'ShankSmartParentChild: After DISABLE controls')
END
#ENDAT
#!-----------------------------------------------------------------------------
#AT(%WindowManagerMethodCodeSection,'TakeCloseEvent','(),BYTE'),PRIORITY(2550)
#!-----------------------------------------------------------------------------
IF ThisWindow.Response = RequestCompleted
   IF ThisWindow.Request = InsertRecord
     #FOR(%CToggled)
      ENABLE(%CName)
     #ENDFOR
     #IF (%ParentOKCtl)
      %ParentOKCtl{PROP:Text} = 'Ok'
     #ENDIF
      #EMBED(%ShankSmartParentChildENABLE,'ShankSmartParentChild: After ENABLE controls')
      IF NOT Access:%ParentFile.Fetch(%ParentKey)
         SELF.Saved = SELF.Primary.Me.SaveBuffer()
         #EMBED(%ShankSmartParentChildBUFFER,'ShankSmartParentChild: After BUFFER update')
         ThisWindow.Request         = ChangeRecord
         ThisWindow.OriginalRequest = ThisWindow.Request
         ThisWindow.Reset(True)
         RETURN Level:Notify
      END
   END
END
#ENDAT
#!*****************************************************************************

Apply this template extension to your PARENT form. Choose the OK Control and choose the CHILD Insert button as one of the controls to disable/enable.

This template will make the OK button appear as SAVE and only when the user SAVEs the PARENT row, the Insert button to add a CHILD row will appear.

This will work ONLY in C6 ABC.

Regards