How to Group Child Records in a Report Detail Band

MORE INFORMATION

Example 1

• A parent table, PARENT.DBF, with two fields:
  • CODE: A character field of width 2
  • DESRIPT: A character field of width 2
• Two child tables, CHILD1.DBF and CHILD2.DBF, with identical structures:
  • CODE: A character field of width 2
  • SALES: A numeric field of width 10 with two decimal places

1. Create a program (CHLDLST.PRG) with the following code:

         PARAMETERS parentkey, childalias, string_exp
         *parentkey is the field that all tables are indexed on.
         *string_exp is the field in the child, must be a character expression
         STORE "" TO ret_val
         STORE ALIAS() TO was_alias  &&parent table
         m_exact=SET("EXACT")
         SET EXACT ON                 &&this is necessary for INLIST
         one2many=INLIST(UPPER(childalias),UPPER(SET("SKIP")))
         SELECT (childalias)
         IF NOT one2many
            SEEK EVALUATE(was_alias + "." + parentkey)
         ENDIF
         IF FOUND()                    && are there related child records?
            SCAN WHILE EVALUATE(childalias + "." + parentkey) = ;
            EVALUATE(was_alias + "." + parentkey)
         * get the data from the current record in the child
                STORE ret_val + EVALUATE(string_exp) ;
                   + CHR(13) + CHR(10) TO ret_val
         * add carriage return & linefeed
            ENDSCAN
        ENDIF
        SELECT (was_alias)
        SET EXACT &m_exact
        * ret_val is now a concatenation of child records, separated by CR/LF
        RETURN ret_val
   							

2. With the three tables open, and PARENT being the selected table, create a new report that has a data grouping on the CODE field from the PARENT table. In the group header band, place the two fields from the PARENT table. In the Detail band, create two fields with an empty line separating them. The expression for the first field is:

         IIF(is_newkey=1,chldlst("code","child1","str(sales,10,2)"),"")
   							

   The expression for the second field is:

         IIF(is_newkey=1,chldlst("code","child2","str(sales,10,2)"),"")
   							

   For both fields, do one of the following:
   • On the Windows and Macintosh platforms, open the Report Expression dialog box, choose the Top -- Field Can Stretch radio button under Position Relative To, select the Print When check box, and select the Remove Line If Blank check box.
     
     -or-
   • On the MS-DOS platform, open the Report Expression dialog box, and select the Stretch Vertically and Float As Band Stretches check boxes. From the Report menu, choose Page Layout, choose the Options button, and select the Suppress Blank Lines check box.
3. Create two report variables in the following order:
   
   NOTE: It is critical that these variables appear in the correct order in the list of report variables.
   • Variable Name : is_newkey
     Value to Store: iif(curr_key=parent.code,0,1)
     Initial Value : 1
   • Variable Name : curr_key
     Value to Store: parent.code
     Initial Value : ' '
     
     With these report variables defined, is_newkey = 1 only when the key field (CODE) in the PARENT table changes.
4. Make sure the program CHLDLST.PRG is in the same subdirectory as the report or in a subdirectory that is in the FoxPro path. Also make sure that the parent table is in the active work area.

Example 2

   USE customer IN 1
   USE invoices IN 2
   COPY TO inv2
   USE inv2 IN 3
					

   SELECT customer.cno, customer.contact, "child#1", invoices.ino, ;
      invoices.idate ;
      FROM customer, invoices ;
      WHERE customer.cno=invoices.cno ;
      UNION ALL ;
      SELECT customer.cno, customer.contact, "child#2", inv2.ino, ;
         inv2.idate ;
         FROM customer, inv2 ;
         WHERE customer.cno=inv2.cno ;
         ORDER BY 1