See Also: Attach, Find, Set_Relate, Relationships, Table Relationships
To find parent records matching the data in a child table's record buffer. This command is usually not used when working with Data Dictionaries.
Relate {child-table} […{child-table}]
Relate moves field data on which relationships to parent (related to) database tables are based from the record of child-table which is in its buffer into the buffer of each parent table which is open. Then it attempts a find in each parent table for a record with matching field data. A child table is one with one or more of its fields related to one or more of the fields of the parent table. Note that existing data in parent buffers is not replaced with child buffer data.
If any of the parent tables has parent tables of its own, a relate is done on each such table to find its parents, and so on until all ultimate parents have been related to. If more than one child-table is named in the command, this process is repeated for all such child-table, none of which needs to bear any relation to any other child-table.
Relate is used with the find process to find all related to records in a database.
Find Ge Pay_Tran.Posting
Relate Pay_Tran
In this example, a record in database table pay_tran is found according to data in Field posting. Then records in all parent tables with field data matching that of the pay_tran record in the buffer are found, and data from pay_tran is moved to the related to fields in their buffers. This process is repeated for any parent tables related to by the parent tables of pay_tran.
If pay_tran had a single parent, employee, and the relation was from Field emp_num in pay_tran to recnum in employee, then the following commands would have the same effect as the relate pay_tran statement above, so long as employee itself had no parent of its own:
Clear Employee
Move Pay_Tran.Emp_Num To Employee.Recnum
Find eq Employee.Recnum
The only performance differences between the two methods are: (a) if the required record from employee is already in its buffer, relate does not find it again; (b) if the record in employee is not found, relate will not declare an error; and (c) relate requires a relationship to be defined from pay_tran to employee.
If a relate finds no matching record in a parent table, a subsequent save command will relate the child-table record to Record Number 0 in such parent table (null relationship).
Relate works only when the record buffer for child-table is active, that is, when it contains a record previously retrieved from the database.
If a parent table is not open, it is not related to, and no error is reported. Likewise, any parent tables of such a parent table are unaffected by the relate command.
Relate functions only on the basis of relationships among and between tables defined in the database, or in preceding set_relate commands executed in the program running.
The attach command works in the opposite direction from that of relate. While relate moves data to, and finds records in parent tables, attach moves data from parent tables to child tables.
The command:
Relate a_table b_table
//is exactly the same as:
Relate a_table
Relate b_table
Relate is useful for writing programs or macros which can operate independently of the particular field names of the tables in use.
When child-table relates to a particular parent table through more than one field, the lowest numbered field in child-table is used for the find in the parent table. If this find fails, no further finding will occur.
You can pass either the child-table's logical name or its table number (in the filelist) to the relate command.