<< Click to Display Table of Contents >> Navigation:  »No topics above this level« Table Relationships

 

The Table Relationships dialog allows you to control the relationship between DBF tables. This feature gives you precise control over how your data is retrieved when your report consists of two or more DBF tables.

 

 

DBF tables are linked by an expression from the Parent Table to an expression from the Child Table via an index.  The link between the two tables determines how the records in the parent table are related to the records in the child table.

 

Parent & Child Expressions

 

You can change the Parent and Child expressions by choosing the appropriate button.  The Expression Builder is displayed to allow you to make the desired modifications.  The following algorithm is used by ReportPro to relate records between tables:

 

PARENT EXPRESSION = CHILD EXPRESSION

 

This algorithm means that the return values of the parent and child expressions are compared to each other, and the two values must be equal.  This is the case for all data types except character.  For character data types, the two values are compared character by character for the length of the child value.  If the value returned by the parent expression is longer than the one returned by the child expression, the extra characters are ignored.  If the child expression returns a value that is longer than the parent expression return value, then the comparison fails.

 

Relationship Type

 

ReportPro allows you to specify how records in a child table are processed when a valid relationship is found.  There are two types of relationships supported by ReportPro.

 

The first type of relationship, the One-to-One relationship, occurs if one and only one record exists in a child table for each record in the parent table.  An example of this is when a Sales table is related to a Customer table.  Only one Customer can exist for each sale.

 

The second type of relationship, the One-to-Many relationship, occurs when more than one record exists in a child table for a record in the parent table.  This case exists when a Customer table is related into a Sales table.  Many Sales Orders can exist for each customer.

 

 

Refer to the figure above for the following discussion.  When the Relationship Type is set to One-to-One, records are processed in the following manner.  First, the record pointer in the parent table is positioned at the top of the table.  Let's assume the current record after the move holds customer Adams.  As you can see, there are three sales orders that belong in customer Adams.  Next, the record pointer in the child table is moved to the first Sales record for customer Adams.  At this point, the data from both tables is printed by ReportPro.  After printing, the Customer record pointer is moved to customer Jones, and the Sales record pointer is moved to the first sales order for customer Jones.  As you can see, we skipped over two Sales records that belong to customer Adams.  The sales orders were not included in the report.

 

If the Relationship Type had been set to One-to-Many, the following algorithm would have resulted.  After the Customer record pointer was moved to customer Adams and the Sales record pointer was moved to the first record for Adams, the records would have been printed on the report.  Next, instead of moving the Customer record pointer to the next customer, the Sales record pointer would be moved to the next Sales record for customer Adams.  The Customer record pointer does not move.  The new Sales record is printed along with the same Customer record.  After printing, the Sales record pointer is moved again.  This continues until there are no more Sales records for customer Adams.  Only after all sales orders have printed for customer Adams will the Customer record pointer move to customer Jones.

 

The record movement method you choose depends on the type of report you are trying to produce.  The One-to-Many method can take significantly longer to traverse a large data set since it is accessing many more logical records.

 

Child Index for Relationship

 

When two tables are related, an index must be used for the child table to support the relationship.  The report engine creates this index automatically based on the child expression if you do not specify which index to use.

 

The drawback to automatic index creation is that it increases the time it takes to run a report.  If an index already exists that properly supports the relationship, you can specify it via the Set Index button.  If an index is specified, ReportPro will assume that it correctly supports the relationship and will skip the automatic index creation step; this greatly speeds up the process of producing the report.

 

Index Tag

 

RDD Drivers such as the CDX driver support index files that contain multiple indexes.  A tag is required to identify which index should be used in the index file.  For those drivers that support compound index files, you can specify a Tag name by choosing the Set Tag Button.