DEFINE RELATION command

Concept Information

Relate tables

Defines a relation between two Analytics tables.

Note

You can relate up to 18 Analytics tables and access and analyze data from any combination of fields in the related tables as if they existed in a single table. You must specify a separate DEFINE RELATION command for each pair of related tables.

Syntax

DEFINE RELATION key_field WITH related_table_name INDEX index_name <AS relation_name>

Parameters

Name Description
key_field

The key field in the parent table.

You can select only one key field for each relation.

Note

When creating relations between parent tables and grandchild tables, you must specify a fully qualified key field name in the format table_name.field_name.

In Relate three tables, see: Vouchers.created_by
WITH related_table_name

The name of the related table.

INDEX index_name

The name of the index for the key field in the related table.

You must index the related table on the key field before you can relate the table.

AS relation_name

optional

A unique name for the relation.

By default, the name of the child table is used as the relation name. If you are defining additional relations to the same child table, you must specify a unique name.

Examples

Relate two tables

The example below relates the open table to the Customer table by using the customer number field (CustNum) as the key field:

DEFINE RELATION CustNum WITH Customer INDEX Customer_on_CustNum

Customer_on_CustNum is the name of the child table index on the key field. A child table index is required when you relate tables.

If the child table index does not already exist when you run the DEFINE RELATION command, an error message appears and the relation is not performed.

Tip

If you define a relation in the Analytics user interface, the child table index is automatically created for you.

Create a child table index before relating two tables

If required, you can create a child table index immediately before relating two tables. The example below shows creating an index for the Customer child table before relating the Ar table to the Customer table.

OPEN Customer
INDEX ON CustNum TO Customer_on_CustNum
Open Ar
DEFINE RELATION CustNum WITH Customer INDEX Customer_on_CustNum

Relate three tables

The example below relates three tables in the ACL_Rockwood.ACL sample project:

  • Vouchers_items the parent table
  • Vouchers the child table
  • Employees the grandchild table

By using the Vouchers table as an intermediary table in the relation, you can relate each voucher item with the employee who processed the item.

OPEN Vouchers
INDEX ON voucher_number TO "Vouchers_on_voucher_number"
OPEN Vouchers_items
DEFINE RELATION voucher_number WITH Vouchers INDEX Vouchers_on_voucher_number
OPEN Employees
INDEX ON employee_number TO "Employees_on_employee_number"
OPEN Vouchers_items
DEFINE RELATION Vouchers.created_by WITH Employees INDEX Employees_on_employee_number

Explanation of the syntax logic

  1. Open the Vouchers table and index it on the voucher_number field.
  2. Open the Vouchers_items table and relate it to the Vouchers table using voucher_number as the key field.
  3. Open the Employees table and index it on the employee_number field.
  4. Open the Vouchers_items table and relate it to the Employees table using Vouchers.created_by as the key field.

    Note

    Vouchers.created_by is available as a key field in the second relation because you already related Vouchers_items and Vouchers in the first relation.

Remarks

For more information about how this command works, see Relating tables.