The HotDocs Computation Archive

0122 - Nested REPEATs and the DB Connection

Description:

How to maintain master-detail relationships between linked tables when using the Database Connection (Updated 07/26/2002).

• Code •

//  Computation to take detail records retrieved through the DB
//  connection, and properly relate the detail records to the
//  appropriate master records.

//  This is necessary, because HotDocs does not associate master 
//  and detail records properly.  The issue is that HotDocs DOES NOT
//  use two or more levels of reference values when pulling info 
//  from the DB connection into a nested dialog (contrary to
//  the instructions in the HotDocs manual)

ASK UNANSWERED

//  Pull the master records from the database.
//  The variables linked to the database are also
//  contained in a dialog named "Master-dlg"
ASK Master-db

//  Set the master record counter to zero
SET MasterCounter-n TO 0

REPEAT Master-dlg

   //  Increment the master record counter
   SET MasterCounter-n TO MasterCounter-n + 1

   //  Set the filter to pull the appropriate detail records
   //  for this master record
   SET ThisMasterID-n TO MasterID-n

   //  Pull the appropriate detail records from the database.
   //  The variables linked to the database are also
   //  contained in a dialog named "Detailed-tmp-dlg", which is 
   //  nested on "Master-dlg"
   ASK Detail-db

   //  Check how many detail records were retrieved.
   //  If there are any, we must copy the detail records 
   //  to the correct location(s).
   SET DetailCount-n TO COUNT( Detail-tmp-dlg ) 
   IF DetailCount-n > 0

      //  Set a value in the target dialog "Detail-dlg" in the record 
      //  equal to the total number of detail records which were 
      //  retrieved for this master record
      SET DetailText-t [ MasterCounter-n, DetailCount-n ] TO "x"
   END IF

   //  Repeat the target dialog "Detail-dlg", which is also 
   //  nested on "Master-dlg"
   REPEAT Detail-dlg

      //  Set each value in the target nested dialog from the value 
      //  in the source nested dialog 
      SET DetailDate-d TO DetailDate-tmp-d [ COUNTER ] 
      SET DetailDate-tmp-d [ COUNTER ] TO UNANSWERED
      SET DetailText-t TO DetailText-tmp-t [ COUNTER ] 
      SET DetailText-tmp-t [ COUNTER ] TO UNANSWERED
      [One line for each variable taken from the source nested dialog to
       the target nested dialog]

   END REPEAT

END REPEAT
ASK DEFAULT

• Explanation •

The Problem: The Database Connection will only work with flat tables. If your table has linked records in a one-to-many relationship, those linked records cannot be pulled into a nested dialog. This computation provides a work-around which will pull in related records and properly place them into a nested dialog, re-associating them with their master record.

Notes:

1. Master-db and Detail-db are the database components. Each of the destination variables in the Master-db database component must also be variables mapped to the Master-dlg dialog. Each of the destination variables in the Detail-db database component must also be variables mapped to the Detail-tmp-dlg dialog.
2. Each variable in the Detail-db component and the Detail-tmp-dlg has "-tmp" in the name of the variable, and there should be a variable with the same name (minus the -tmp) in the Detail-dlg.
3. Detail-tmp-dlg and Detail-dlg are nested dialogs, and must be included in the Master-dlg dialog.
4. Each of the dialogs should have its Dialog options set to "Repeat as Spreadsheet".
5. The Detail-db component must have a filter set for the index value (ID, MatterID, SSN, etc.) relating the detail record to the appropriate master record.

• Contributors •