About relating tables

Relating allows you to combine up to 18 ACL tables with different record structures and access and analyze data from any combination of fields in the related tables as if they existed in a single table (Figure 1). Record structures are different if they have one or more data elements that differ. The result of relating tables is virtual – the related tables remain independent, and can be unrelated at any time. Unlike joining or merging, relating does not create a new table. Instead, the fields of the related or child table(s) become available in the parent table from which the relation was created. If required, you can perform a separate operation and extract any combination of fields from the parent and child tables to a new table.

Relating is a good choice for informational work that requires a quick picture of data associations across several physical tables, or for linking codes with corresponding full names prior to generating reports.

Figure 1. Relating tables

Requirements when relating tables

To be related, table pairs require a common key field – that is, a data element such as employee number or vendor ID that appears in both tables. When identical values exist in the key fields, the result is a match that relates individual records from the separate tables. Partial matching is not supported by the basic relation operation.

The key fields can be any data type, or computed fields, but they must be the same data type as each other. Datetime subtypes (date, datetime, and time) can only be related to the same subtype. Key fields must also be the same length, and use the same justification and case. They do not need identical names, or the same start position, but they must describe the same data element.

Parent and child tables and key fields

The tables and key fields in the relation operation are identified as parent and child based on the order in which you add the tables to the relation:

You are free to choose whatever parent and child tables and key fields you want. (For more information, see About key fields.) However, the relation will succeed only if the tables and fields meet the requirements for relating.

Accessing child table fields

Once a relation is established, you can add fields from any of the child tables to the parent view, although there is no requirement that you do so. You can access and analyze child table fields through the parent table – using the From Table drop-down list in ACL dialog boxes and the Expression Builder – whether or not you have added them to the parent view. Child table fields accessed through the parent table are displayed in table name.field name format to indicate which table the fields are from. You can access related tables, and modify relations, only through the parent table, not through a child table.

Sorting and indexing of related tables

The virtual table resulting from a relation uses the existing sort order of the parent table. You do not have to sort or index the parent table key field prior to relating tables. However, you might choose to do so, because there is no Presort option available for the parent table during a relation.

As part of the internal functioning of the relation operation, the child table key field(s) are automatically indexed in ascending order. These child table indexes persist even after the child tables are unrelated, and can be manually deleted, if necessary.

Additional information about relating

Table 1 provides additional information about relating.

Table 1. Additional information about relating

Functional area

Details

Record matching

Relating a table pair is the logical equivalent of joining them using the All Primary Records option – that is, using the type of many-to-one join that includes matching primary and secondary records (parent and child records), and unmatched primary records.

As with the corresponding many-to-one join, the relating operation matches parent key values to the first occurrence only of a matching child key value. If additional occurrences of a matching child key value exist, they are ignored. You need to take this behavior into account when planning your table relations, especially if a child table contains legitimate multiple occurrences of a matching key value. One approach is to try reversing the relation of the two tables, making the child the parent, and vice versa.

Unmatched records and missing field values

If a parent key value has no match in a related child table, for the missing field values, ACL displays a blank in character and datetime fields, a zero in numeric fields, and “F” in logical fields.

Duplicates or blanks in child table key field

If duplicates or missing values in a child table key field render subsequent analysis invalid, pre-processing the child table to remove the duplicates and/or blanks may be a solution in some circumstances.

Extracting data from related tables

You have two options when extracting data from related tables:

  • Using either the View or Fields option in the Extract dialog box, you can extract some or all of the data from the parent and child tables into a new ACL table. The new table is no longer related to any other tables.

    If you use the View option, you must first add the pertinent child table data to the parent view.

  • Using the Record option in the Extract dialog box, you can extract the data from the parent table into a new ACL table. The new table retains the relations of the original parent table. The Record option does not support extracting child table data.

Identical key field length not enforced

ACL does not enforce identical lengths for the common key fields in parent and child tables. However, it is recommended that you always use fields of identical length, manually harmonizing the lengths prior to performing the relation, if necessary. Results derived from relating using key fields of different lengths are not reliable.

Datetime key fields can be different lengths because ACL, when performing operations involving dates, datetimes, or times, uses an internal ACL datetime format.

Changing key field data type

You cannot change the data type of a parent or child key field while it is being used to relate tables. If you need to change the data type of either field, you must first delete the relation. If the change results in the data types being different from each other, you are no longer able to use the two fields to relate tables.

Avoiding conditional indexing

Do not use a conditional index for child table key fields. Instead, apply conditions when you perform operations against a parent table and its related table(s). Using conditional indexes when building relations can cause unintended data gaps at different points in a relational hierarchy. The safer approach is to build relations that present as full a data set as the relations warrant, and subsequently apply conditions, if required.

Restrictions on location of tables being related

To be related, tables must be in the same ACL project. Server tables must be on the same server, and must be accessed using the same server profile. You cannot relate a local table and a server table.

Harmonizing justification and case

When you relate table pairs using character key fields, justification and case must be the same:

  • Both key fields must have the same justification. Use the LTRIM( ) function to remove leading blanks from the key fields.

  • Both key fields must be in the same case – UPPER, lower, or Proper. To harmonize the case, use the UPPER( ), LOWER( ), or PROPER( ) function.

Relating UTC-based and non-UTC data

A UTC-based and a non-UTC datetime key field can be used to relate two tables. (UTC is Coordinated Universal Time, the time at zero degrees longitude.) When performing operations involving datetimes or times, ACL uses an internal ACL datetime format, so the following two datetimes are interpreted as identical, and constitute a match:

  • UTC-based – 31/12/2014 10:30:15-05:00

  • non-UTC – 31/12/2014 15:30:15

You should exercise caution if you mix UTC-based and non-UTC time data in an ACL operation. Although ACL will match the two time values above, it may not make logical sense to do so, because one value references a time zone, and the other value does not. For more information about UTC, see Options dialog box: Date and Time tab.

Related concepts
Joining versus relating
Common uses of joining or relating
About joining tables
Using multiple key fields
How table relations are structured
About key fields
Combining data
Data structure and data format requirements
Harmonizing fields
Related tasks
Relating tables
Modifying relations


(C) 2015 ACL Services Ltd. All Rights Reserved.