How to Set Up Relationship in Data Env for Concatenated Fields

• Microsoft Visual FoxPro for Windows, version 3.0

SUMMARY

Use the following steps to create a relationship involving the concatenation of two or more fields in the parent and child tables in the data environment of a form or report:

1. Create an index tag in the child table from the concatenation of the fields.

2. Add the parent and child tables to the data environment.

3. In the data environment, drag the first field of the concatenation from the parent table to the concatenated index tag in the child.

4. Bring up the Property sheet for the Relation object and set its RelationalExpr property to the concatenation of the fields (field1+field2 if the fields are both character data type).

MORE INFORMATION

Step-by-Step Example

This example uses a program that creates the parent and child tables.

1. Run the following program to create the tables to be used:

   * Start of CREATABS.PRG

   * This program creates a parent table called PAR_TAB.DBF and a child * table called CHLD_TAB.DBF

   * Create and fill parent table

   CREATE TABLE par_tab FREE (cfield1 C (3), cfield2 C(3))

   FOR nI=1 TO 10

         FOR nJ=1 TO 3
           INSERT INTO par_tab (cfield1,cfield2) ;
             VALUES (CHR(64+nI), ALLTRIM(STR(nJ)))
         ENDFOR
      ENDFOR
   
      * Create and fill child table
      CREATE TABLE chld_tab FREE (cfield1 C (3), cfield2 C(3), cfield3 C(3))
   
      FOR nI=1 TO 10
         FOR nJ=1 TO 3
            FOR nK=1 TO nJ
              INSERT INTO chld_tab (cfield1,cfield2,cfield3) ;
                VALUES (CHR(64+nI),ALLTRIM(STR(nJ)),ALLTRIM(STR(nK)))
            ENDFOR
         ENDFOR
      ENDFOR
   
      * End of CREATABS.PRG
   
   

2. Modify the structure of Chld_tab.dbf and create the concatenated index tag. Name the index tag Cfld1cfld2, and in the index expression, enter Cfield1+Cfield2.

3. Create a form, and add Par_tab.dbf and Chld_tab.dbf to the data environment of the form.

4. Drag the first field in the concatenation (Cfield1) from Par_tab.dbf and drop it on the Cfld1cfld2 index tag of Chld_tab.dbf. This creates a relationship.

5. Right-click the relationship line in the data environment, and click Properties to bring up the Property sheet. Edit the RelationalExpr property of the Relation1 object. This will now contain Cfield1. Edit this to read Cfield1+Cfield2 -- the same thing entered as the index expression for the Chld_tab table. The relationship is now established.

6. Drag Cfield1 and Cfield2 from the parent table and onto the form to create text boxes. Drag the child table by its title bar onto the form to create a grid.

7. Add a command button to the form with the following code in its Click event:

   SKIP Thisform.Refresh

8. Save and run the form. Click the command button to move the record pointer through the parent table. Note that the values in the text boxes from the parent table match the first two columns of the grid, which are the corresponding fields in the child table.

Additional Notes

• The first table added to the data environment is initially selected by default. If the child table is added to the data environment first, the InitialSelectedAlias property of the DataEnvironment object should be set to the parent table name.
• After the relationship is established as previously described, the line representing the relationship in the data environment comes from the Fields heading in the parent.
• The structure of each of the fields in the concatenation must be identical in the parent and the child.
• Although the previous example uses a form, the data environment setup is the same for a report.
• If numeric fields must be concatenated, they must be first converted to strings. For example, str(nfield1)+str(nfield2) could be used for two numeric fields in the index expression of the child table and the RelationalExpr of the Relation object. Likewise, a character and numeric field could be concatenated with the expression cfield1+str(nfield2).