A relation column creates a one-to-many relationship between records in two tables. It links a record in a parent (master) table to one or more related records in a child (details) table, enabling you to organize and manage related data across tables. This is called a master-detail relationship.
This feature is used to model a hierarchical relationship between two sets of records by connecting one record (the parent) to one or more related records (the children). This structure enables logical data grouping and is used to represent a master record along with its associated details. For example, there could be a list of customers (parent records), each with several orders (child records).
A single record in the master table can be linked to multiple records in the detail table, whereas each record in the detail table can only link to one record in the master table.
Adding a relation column provides the following benefits:
Organized data structure: Group related records to make complex datasets easier to navigate and understand.
Data integrity: Maintain relationships between parent and child records to prevent incomplete or inconsistent data, such as an order item without an associated customer.
Streamlined data entry and updates: Reduce data duplication by storing common information in parent records and allowing child records to inherit those values.
Simplified reporting and filtering: Filter, summarize, and drill down into data by using parent-child relationships.
Improved user experience: View and manage related parent and child records from a single interface.
Scaling and maintenance: Scale your databases while maintaining data accuracy and integrity.
A relation column uses the following components to establish a relationship between two tables:
Master table: Contains the primary records. Each record represents a unique entity, such as a customer, project, or product.
Detail table: Contains records that are related to a record in the master table. Multiple detail records can be associated with a single master record.
Foreign key column: Stores the reference to the primary key in the master table, establishing the relationship between the two tables. PowerTable uses this column to link each detail record to its corresponding master record.
This section explains the steps to add a relation column and create master-detail linked tables using an example.
Prerequisite
If two or more tables in a dataset share a foreign key column, a master-detail relationship can be established between them.
Steps to add a relation column
In this example, you create a master-detail relationship between the Product Subcategories and Products tables. Both tables share the ProductSubcategoryKey column, which acts as a foreign key.
Optionally, configure the foreign key column as a lookup column in the Products table to display the corresponding subcategory name from the ProductSubcategories table.
To create the relationship, add a relation column to the master (subcategories) table.
Go to Insert Column > VisualColumn > Add Relation Column. A side panel opens.
Configure the column properties:
Column Name: Enter a name for the relation column.
Relation Table: Select the child table to relate to, such as Products.
Column from Current Table: Select the column in the current (master) table that establishes the relationship.
Column from Relation Table: Select the matching foreign key column in the related (child) table. ProductSubcategoryKey is the matching foreign key column in this example.
Column To Display: Select the column whose values are displayed as reference labels for related records when you expand a record. In this example, select Product Subcategory Key. The corresponding lookup labels are displayed as reference labels for related records in the details table.
Display name for Relation Table: Enter the text to display in the relation column. Users select this text to expand a record and view its related records.
If Condition: Optionally, select Add Filter to add one or more filter conditions. Combine multiple conditions by using AND or OR. PowerTable displays only related records that meet the specified conditions. For example, you can display child records only when the Product Price is more than a specific value.
Cascade delete to linked records: Enable this option to delete related child records automatically when the parent record is deleted.
The display properties for the relation column can be configured in the panel's Display tab. To learn more, see Display.
Select Save.
The relation column is added to the subcategories table. The number of related child records for each parent record is displayed in parentheses.
Expand a parent record to view its related child records. The related records open in a new table, identified by the selected subcategory label.
