DF_FIELD_RELATED_FILE
See Also: Get_Attribute, Set_Attribute, Set_Relate, DF_FIELD_RELATED_FIELD
The number of the table to which the field, on which the attribute is being used, relates.
Level
Column
Supported by
All Drivers
Type
Integer, permanent/temporary
Access
Read / Write
Values
A table number; table numbers range from 1 to 4095.
Remarks
This attribute should always be used in conjunction with the DF_FIELD_RELATED_FIELD attribute. The combination of DF_FIELD_RELATED_FILE and DF_FIELD_RELATED_FIELD attributes defines a relationship between two tables. Since these attributes are mutually dependent, both attributes are documented in this topic.
A relationship between two tables allows you to prevent redundant data in the database. For example, if a database stores sales information, one would have a table called sales to store information about each sale, such as salesdate, product, and amount. There is also information about the customer, like the name and address. If the customer information were stored in the sales table, it 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, known as 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, known as 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 than 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 three 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 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 the 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.