Multiselect relationship column

A multi-select relationship column lets you associate one or more records from a lookup table with each record in the primary table. It uses a relation table to store the relationships between the primary and lookup tables, enabling many-to-many associations.

By retrieving values from a lookup table, multi-select relationship columns help maintain data consistency, reduce manual data entry, and ensure that users select only valid values.

In this article, you learn how to add a multi-select relationship column to a primary table and add column values that are fetched from the records in a lookup table.

Prerequisites#

Before creating a multi-select relationship column and configuring a many-to-many lookup, make sure that the following tables exist in the database:

• The primary table that contains a primary key.
• The lookup table that contains a primary key and one or more columns with selectable values.
• The relation table that contains an identity column and foreign keys that reference the primary key columns in the primary and lookup tables.

If the required tables do not exist in the database, create or import them to the database before configuring the multi-select relationship column.

You can import them using PowerTable from external sources such as Excel or CSV files. To learn more, see this section.

Add multi-select column#

In the following example, you'll add a Sales Rep multi-select column to the Products table so you can assign one or more sales representatives to each product.

This column retrieves column values from the Sales Representatives table (lookup table) using the relationship information obtained from the Relation_table_products table (relation table).

To add a multi-select column:

1. Select Insert Column > Visual Column > Add Multi-Select Relationship Column.

2. Enter a name for the column in the Column Name. Here, enter the name as Sales_Rep.
3. Enter the lookup table details. This is the table from which the list of values for each record will be retrieved. Use the dropdown menus to select the items listed below.

• Lookup Table: Sales Representatives
• Lookup Value: FirstName
• Lookup Key: Sales Rep Key

4. Specify the relation table, the reference column that stores the lookup table key, and the identifier column used to match records between the primary table and the lookup table. These mappings are used to establish and maintain the relationship between the tables.

Use the dropdown menus to select the relation table details and identifier column:

Table: Relation_table_products

Reference Column: Sales Rep Key (lookup key)

Identifier Column: ProductKey (matching column between the tables)

5. Select default value if needed.
6. Select Save.

You have now added a multi-select relationship column that lets you assign multiple sales representatives to each product. The column also looks up and displays the corresponding sales representatives for each product based on the data in the relationship table.

Enter values in the column#

To enter values into the multi-select column, select the cell and choose one or more from the list.

Select Save to Database and Proceed to save your changes to the database.

The relation table is also simultaneously updated based on the selected values.

Add new lookup records#

If you want to add options from the lookup table that aren't currently available for the record, you can add them directly from the lookup column in the Products table.

1. Select the lookup cell, and then select the drop-down arrow.
2. Select + Add.

3. Select one or more records from the lookup table and select Add.

The selected records are added to the current record and become available in the drop-down list for that record.