DF_FIELD_RELATED_FIELD

See Also: Get_Attribute, Set_Attribute, Set_Relate, DF_FIELD_RELATED_FILE

 

The field number, in a related table, to which the field which the attribute is being used on, relates.

Level

Column

Supported by

All Drivers

Type

Integer, permanent/temporary

Access

Read / Write

Values

0 ~ maximum fields in the parent table

Remarks

This attribute should always be used in conjunction with the DF_FIELD_RELATED_FILE attribute. The combination of DF_FIELD_RELATED_FILE and DF_FIELD_RELATED_FIELD attributes defines a relationship between two tables.

A relationship between two tables allows you to prevent redundant data in the database. If, for example, a database stores sales information, one would have a table called sales to store information about each sale like salesdate, product and amount. There is also information about the customer like the name, and address. If the customer information would be stored in the sales table, the customer information would be duplicated for every purchase the customer makes. It is better to store the customer information once in a separate table and put a reference in the sales table that points to a record in the customer table. This reference is called a relationship. Usually records in a table are identified by one or more columns in that table, the primary key. When creating a relationship columns are added to the child file with the same type and length as the columns in the primary key of the parent file, the foreign key.

In DataFlex relationships are defined by setting column attributes (DF_FIELD_RELATED_FILE and DF_FIELD_RELATED_FIELD). This means you can only define one relationship between two tables. If there are multiple columns in a child table referencing columns in one parent table, the relationship is regarded as a multi segment relationship.

If there is a need to define more then one relationship between two tables, alias tables for the parent must be used. Every database query language supports a form of creating aliases for tables. In DataFlex, aliases are created by creating an additional filelist entry pointing to the same physical table and setting Alias_File for a DataDictionary to the alias table(s).

If the DF_FIELD_RELATED_FILE attribute is zero, the column does not have a relationship (regardless of the value of the DF_FIELD_RELATED_FIELD attribute).

If the DF_FIELD_RELATED_FILE is not zero and DF_FIELD_RELATED_FIELD is zero, it defines a so called Recnum relation. Recnum relations have been discouraged by Data Access for many years but it may be possible that legacy systems still have such relationships. It is strongly discouraged to use this type of relation.

This is a permanent attribute that can be set inside and outside a Structure_Start ... Structure_End operation. For the non Embedded Database drivers, the permanent value of this attribute is stored in the intermediate file using the Field_Related_File, FieldRelated_Field keywords. If the attribute is set outside of a structure operation the setting is in effect until it is reset or the program ends. A shortcut to set both attributes is the Set_Relate command.

Procedure ShowTree Handle hTable Integer iLevel

    Boolean bOpen

    Integer iNumColumns iColumn iItem iNumParents

    Handle hParent hoSet

    String sTable

    

    Get Create U_Set To hoSet

    Get_Attribute DF_FILE_OPENED Of hTable To bOpen

    If (Not(bOpen)) ;

        Open hTable

        

    //*** Show this table

    Get_Attribute DF_FILE_LOGICAL_NAME Of hTable To sTable

    Showln (Repeat("    ", iLevel)) sTable

        

    //*** Place all parent tables in the set

    Get_Attribute DF_FILE_NUMBER_FIELDS Of hTable To iNumColumns

    For iColumn From 1 To iNumColumns

        Get_Attribute DF_FIELD_RELATED_FILE Of hTable iColumn To hParent

        If (hParent > 0) ;

            Send Add_Element Of hoSet hParent

    Loop

    

    //*** Now show the members of the set

    Get Item_Count Of hoSet To iNumParents

    For iItem From  0 To (iNumParents - 1)

        Get Value Of hoSet iItem To hParent

        Send ShowTree hParent (iLevel + 1)

    Loop

    

    If (Not(bOpen)) ;

        Close hTable

    Send Destroy Of hoSet

End_Procedure

This example shows a recursive function that displays a tree of all ancestors (parents, grandparents and so forth) of the passed table.

Procedure CreateTable

    Handle hTable

    Integer iColumn iIndex

    Handle hoWorkspace

    String sPath sOrigFolder

 

    //*** Make sure table comes in first folder of datapath by making that folder current

    Get phoWorkspace Of ghoApplication To hoWorkspace

    Get psDataPath Of hoWorkspace To sPath

    Get PathAtIndex Of hoWorkspace sPath 1 To sPath

    Get_Current_Directory To sOrigFolder

    Set_Directory sPath

 

    //*** Create physical table contacts

    Move 0 To hTable

    Structure_Start hTable "DATAFLEX"

        Set_Attribute DF_FILE_PHYSICAL_NAME Of hTable To "Contact"

        Set_Attribute DF_FILE_MAX_RECORDS Of hTable To 150000

 

        Create_Field hTable At iColumn

        Set_Attribute DF_FIELD_NAME Of hTable iColumn To "Customer_Number"

        Set_Attribute DF_FIELD_TYPE Of hTable iColumn To DF_BCD

        Set_Attribute DF_FIELD_LENGTH Of hTable iColumn To 6

 

        Move 0 To iColumn

        Create_Field hTable At iColumn

        Set_Attribute DF_FIELD_NAME Of hTable iColumn To "ContactDate"

        Set_Attribute DF_FIELD_TYPE Of hTable iColumn To DF_DATE

 

        Move 0 To iColumn

        Create_Field hTable At iColumn

        Set_Attribute DF_FIELD_NAME Of hTable iColumn To "Comment"

        Set_Attribute DF_FIELD_TYPE Of hTable iColumn To DF_TEXT

        Set_Attribute DF_FIELD_LENGTH Of hTable iColumn To (8 * 1024)

 

        Move 0 To iIndex

        Create_Index hTable At iIndex

        Set_Attribute DF_INDEX_NUMBER_SEGMENTS Of hTable iIndex To 2

        Set_Attribute DF_INDEX_SEGMENT_FIELD Of hTable iIndex 1 To 1

        Set_Attribute DF_INDEX_SEGMENT_FIELD Of hTable iIndex 2 To 2

        Set_Attribute DF_INDEX_SEGMENT_DIRECTION Of hTable iIndex 2 To DF_DESCENDING

    Structure_End hTable

 

    Set_Directory sOrigFolder

 

    //*** Add to filelist and generate fd

    Move 0 To hTable

    Get_Attribute DF_FILE_NEXT_EMPTY Of hTable To hTable

    If (hTable > 0) Begin

        Set_Attribute DF_FILE_ROOT_NAME Of hTable To "Contact"

        Set_Attribute DF_FILE_DISPLAY_NAME Of hTable To "Contact sample table"

        Set_Attribute DF_FILE_LOGICAL_NAME Of hTable To "Contact"

 

        Open hTable

 

        Get psDDSRCPath Of hoWorkspace To sPath

        Get PathAtIndex Of hoWorkspace sPath 1 To sPath

        If (Right(sPath, 1) <> Sysconf(Sysconf_Dir_Separator)) ;

            Move (sPath - Sysconf(Sysconf_Dir_Separator)) To sPath

        Move (sPath - "Contact.fd") To sPath

        Output_Aux_File DF_AUX_FILE_FD For hTable To sPath

        Close hTable

    End

End_Procedure

In this sample a new table called Contact is created. It has here columns: Customer_Number of type Numeric(6), Contactdate of type Date and Comment of type Text(8192) with an index on Customer_number and ContactDate (descending). The table is used in upcoming example code. The table will be created in the first folder of the data path of the current workspace; an fd file will be created in the first folder of the DDSrc path of the current workspace.

Procedure UseTempRelation

    Handle hTable

    Boolean bCustFound

    Integer iContactCount

 

    Open Customer

    Open Contact

 

    Set_Relate Contact.Customer_Number To Customer.Customer_Number

 

    Clear Customer

    Repeat

        Find Gt Customer By 1

        Move (Found) To bCustFound

        If (bCustFound) Begin

            Showln "Contacts for customer: " ;

                (Trim(Customer.Name)) "have taken place on the following dates:"

            Move 0 To iContactCount

            Attach Contact

            Find Ge Contact By 1

            If (Found) ;

                move (Customer.Customer_number = Contact.Customer_Number) to Found

            While (Found)

                If (iContactCount > 0) ;

                    Show ", "

                Show Contact.Contactdate

                Increment iContactCount

                Find Gt Contact By 1

                If (Found) ;

                    move (Customer.Customer_number = Contact.Customer_Number) to Found

            End

            If (iContactCount = 0) ;

                Showln "NO CONTACTS"

            Else ;

                Showln

        End

    Until (Not(bCustFound))

 

    Close Contact

    Close Customer

End_Procedure

In this example the Contact table is opened and a temporary relation to the Customer table is created using the Set_Relate command.

Set_Relate Contact.Customer_Number To Customer.Customer_Number

Note that the following two lines of code are equivalent to the Set_Relate:

Set_Attribute DF_FIELD_RELATED_FILE Of Contact.File_Number 1 To Customer.File_number

Set_Attribute DF_FIELD_RELATED_FIELD Of Contact.File_Number 1 To 1

Next the Customer table records are traversed and for every Customer record all the child Contact records are traversed. The first child record is found by using the Attach command to move value of the Customer.Customer_Number column into the Contact.Customer_number column.

Procedure MakeRelationPermanent

    Handle hTable

 

    Open Customer

    Open Contact Mode DF_EXCLUSIVE

    Move Contact.File_Number To hTable

    Structure_Start hTable

        Set_Attribute DF_FIELD_RELATED_FILE Of hTable 1 To Customer.File_number

        Set_Attribute DF_FIELD_RELATED_FIELD Of hTable 1 To 1

    Structure_End hTable

    Close Customer

End_Procedure

In this example the relation from Contact to Customer is made permanent.

 

To delete a relationship, set DF_FIELD_RELATED_FILE to zero.

The sample code below would delete the relationship of table hTable column iCol.

Handle hTable

 

Move TableName.File_Number to hTable

Move ColumnWithRelationShip to iCol

 

Structure_Start hTable "SQL_DRV"

    Set_Attribute DF_FIELD_RELATED_FILE of hTable iCol to 0

Structure_End hTable DF_STRUCTEND_OPT_IN_PLACE

 

Local DD Relationships

If you are using local DD (DataDictionary) relationships by setting pbUseDDRelates to True, then you need to use Field_Related_File and Field_Related_Field instead of these global table attributes.