Creating Master-Detail Tables

If two or more tables in a dataset share a foreign key column (relation column), a master-detail relationship can be established between them. Establishing a master-detail view allows you to easily organize, scale, and maintain your data.

In our example, we have two tables: the 'categories' table and the 'products' table. They share a foreign key column, the 'Category ID' column.

We can also use a lookup column in the products table for the CategoryID field; this lookup column retrieves the corresponding category names from the categories table (key: CategoryID, value: CategoryName).

Both tables look as shown below. Let's now create a master-detail relationship between these tables.

In the master table, you need to add a relation column. This column connects both tables using the foreign key.

STEP 1: To add this column, go to Setup > Columns.

STEP 2: In the 'Columns' window that opens, click on Add Visual Column > Add Relation Column.

STEP 3: Configure the required details as below:

• Type: Select the column type. We chose the 'Text' type because we wanted to display the category name.

• Column Name: Enter the name you wish to use for the new column.

Configure Relation Table

Enter the details of the child table that you want to connect to. To establish a connection to the products table, let's input the following details:

Connection: The name of the connection used to fetch the child table.

Schema: The name of the database schema where the table is available.

Relation Table: Select the necessary child table to connect to.

Configure Relation Columns

Column from Current Table: Choose the foreign key column from the current master table as the relation column.

Column from Relation Table: Choose the foreign key column from the relation table as the relation column.

This is the column that connects both tables.

Configure Label Column

Column To Display: Choose the label/display name that will appear when you drill down a record to its details table.

STEP 4: Click Save to save the configuration. The relation column is added.

The master-detail relationship is now established between the tables, as shown below: