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:
In the column setup window, select the pencil icon next to the required column.
Set Input Type to Single Select.
Select Manual as the Values Type.
Enter the required options and labels. Then, configure the background color for each label.
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:
Select the pencil icon next to the required column.
Set Input Type to Single Select.
Select Distinct Values for the Values Type.
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:
Select the pencil icon next to the required column.
Set Input Type to Single Select.
Select Lookup as the Values Type.
In Lookup Schema, select the schema that contains the lookup table.
In Lookup Table, select the table that contains the lookup values.
Under Lookup Key Column, select the current table column that contains the key values.
Under Lookup Display Column, select the lookup table column that contains the values to display.
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.
Last updated
Was this helpful?