Multi-select relationship column

A multi-select relationship column allows you to select one or more values from a related table for each record in the primary table. A relation table maintains the relationships between the primary and related tables, enabling a record to be associated with multiple related values.

By retrieving values directly from another table, it helps maintain data consistency, reduces manual data entry, and ensures that you choose only from valid entries. Multi-select relationship columns also make it simple to join records across tables that share one or more common columns.

In this article, you learn how to add a multi-select relationship column in the primary table and connect to records in a related table.

Prerequisites

Before creating a multi-select relationship column, make sure that the following tables exist in the database:

• The primary table.

• The related table (lookup table) containing the selectable list of values.

• A relation table that stores the relationships between the records of the primary and lookup tables.

Note

If the required tables do not exist, 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. For more information, see this section.

Add multi-select column

In the following example, you 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 Rel_SalesRep_Products table (relation table).

To add a multi-select column:

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

1. Enter a name for the column in the Column Name.

2. 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 column: First Name

• Lookup Key: Sales Rep Key

1. Specify the relation table, the reference column that stores the lookup table key, and the identifier columns 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.

Relation table details

This stores or updates the mapping between products and sales reps in the relationship table as we assign and unassign sales reps to the products in the app. Click Save.

Review the multi-select relationship column

We have now successfully added a multi-select relationship column, which allows you to select and assign multiple sales representatives to each product.

Multi-select relationship column

After assigning, you can click Save and Proceed to save your changes to the database.

Save changes

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