Lookup and relation

This article explains how to configure columns that use the Single Select input type.

Single-select columns allow users to choose a value from a predefined list of options. You can configure the available options in one of the following ways:

• Manual - Define the dropdown options manually.
• Distinct Values - Generate dropdown options from existing column values.
• Lookup - Retrieve dropdown values from another table, typically to establish foreign key relationships.

Using predefined options helps maintain data consistency, simplify data entry, and standardize values across records.

The following sections describe how to configure each option source.

Manual#

Use the Manual option to define dropdown values and labels directly.

To configure dropdown values manually:

1. In the column setup window, select the pencil icon next to the required column.
2. Set Input Type to Single Select.

3. Select Manual as the Values Type.
   

   

4. Enter the required options and labels. Then, configure the background color for each label.

5. Select Save.
   

   

Use Add to create additional options or the delete icon to remove an existing option.

After the configuration is saved, you can select values from the configured dropdown list when editing records.

Distinct Values#

Use the Distinct Values option to generate dropdown values from the existing values in the selected column.

To configure dropdown values from existing data:

1. Select the pencil icon next to the required column.
2. Set Input Type to Single Select.
3. Select Distinct Values for the Values Type.

4. Select Save.
   

   

PowerTable creates a unique list of values from the selected column and uses them as dropdown options. You can then select values from the generated dropdown list.

Lookup#

Use the Lookup option to retrieve dropdown values from another table.

This option is commonly used to display user-friendly values for foreign key fields while storing the corresponding key values in the database.

To configure a lookup table:

1. Select the pencil icon next to the required column.
2. Set Input Type to Single Select.
3. Select Lookup as the Values Type.
4. In Lookup Schema, select the schema that contains the lookup table.
5. In Lookup Table, select the table that contains the lookup values.
6. Under Lookup Key Column, select the current table column that contains the key values.
7. Under Lookup Display Column, select the lookup table column that contains the values to display.
8. Select Save.

Optionally, select Add Hierarchy to configure additional levels in the lookup hierarchy. To remove a hierarchy level, select the Delete icon next to it.

After a lookup is configured, the key values in the current table are replaced with the corresponding display values from the lookup table.

For example, consider a Products table that contains a ProductSubcategoryKey column with key values.

The Subcategory table serves as the lookup table and contains the ProductSubcategoryKey column along with the corresponding SubcategoryName values.

When the lookup is configured, the key values in the ProductSubcategoryKey column are replaced with the corresponding label values from the SubcategoryName column. This makes the data more readable and easier to understand.

When inserting or editing records, users can select values from the lookup-based dropdown list.

Filter based on another column

Use the Filter based on another column option to further restrict the lookup values displayed in the dropdown list.

You can configure one or more matching column pairs between the current table and the lookup table. When a filter is applied, only lookup values that satisfy all configured matching conditions are displayed.

FAQ#

What does the Values Type set to Distinct Values do for a Single Select column?

The Distinct Values option builds the dropdown list from the unique values that already exist in the column instead of using a predefined list.

PowerTable reads all unique values in the column and displays them as the available options in the Single Select dropdown.

If Distinct Values builds the list from existing data, how can users add a new value?

Use the Allow Adding New Options checkbox in the Constraints section of the Edit Column dialog.

When this option is enabled, users can search for a value in the dropdown. If the value doesn't already exist, PowerTable displays an option to add it to the list.

Does a lookup column display only the values that are present in the current table?

No. A lookup column displays the complete set of distinct values from the lookup table, not just the values that are present in the current table or the column where the lookup is configured.

How do I add a new value to a lookup column?

To add a new value to a lookup column, first insert the value into the table that contains the lookup values. The new value then becomes available in the lookup column.

In a lookup column, what is stored in the row - the key or the display value?

A lookup column stores the key for the displayed value.

When you configure a lookup column, PowerTable treats the values in the column as business keys and displays the corresponding values from the same table or a different table.

Can I configure a lookup column by using the same table?

Yes. You can configure a lookup column that references the same table.

For example, an Employee table might contain an Employee ID, Employee Name, and Manager ID. You can configure the Manager column as a lookup that references the Employee table to display the manager's name.

What does Add Hierarchy do in the lookup configuration?

The Add Hierarchy option displays a drill-down hierarchy in the lookup dropdown, making it easier to organize and navigate lookup values.

You can configure the hierarchy by using multiple tables that are related through common columns.

What does Filter based on another column do in the lookup configuration?

The Filter based on another column option filters the values in a lookup column based on columns that are common between the source and lookup tables.

This option displays a filtered list of values in the lookup dropdown based on the corresponding value of another column in the same row.