DOCUMENT:Q137952  11-DEC-1999  [foxpro]
TITLE   :How to Set Up Relationship in Data Env for Concatenated Fields
PRODUCT :Microsoft FoxPro
PROD/VER:WINDOWS:3.0
OPER/SYS:
KEYWORDS:

======================================================================
-------------------------------------------------------------------------------
The information in this article applies to:

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

Additional query words: VFoxWin

======================================================================
Keywords          :  
Technology        : kbVFPsearch kbAudDeveloper kbVFP300
Version           : WINDOWS:3.0

=============================================================================

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS
PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND.  MICROSOFT DISCLAIMS
ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES
OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.  IN NO
EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR
ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL,
CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF
MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE
POSSIBILITY OF SUCH DAMAGES.  SOME STATES DO NOT ALLOW THE EXCLUSION
OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES
SO THE FOREGOING LIMITATION MAY NOT APPLY.

Copyright Microsoft Corporation 1999.