Lookup and Relation

PowerTable allows you to configure any field in the table using the single-select input type. The lookup and relation field is specifically designed for setting up single-select type columns. In addition to manually configuring the dropdown options, PowerTable offers additional methods for sourcing values from dimensions, even if they reside in a different table or data source, through the use of lookup tables.

In summary, we have the following methods to configure the options:

1. Manual: Configure the dropdown options manually.

2. Distinct Values: Use the existing values in the column as a drop-down list with distinct options.

3. Lookup Table: Source values from another table in a database, which is usually done for foreign key fields.

This section will provide a detailed procedure for configuring dropdown values for each method.

1. Manual

This method allows you to type in the drop-down options and labels manually.

Let us take an example where the Product Color column options are set manually:

Adding options manually

1. Click on the pencil icon beside the ProductColor field.

2. Choose Single Select and then Manual in the Type fields.

3. Enter the options and labels as required.

4. Click Save to save the configuration.

5. Click Add to add new options and the bin icon to delete an option.

Other options

You can now edit the table with the added dropdown options. PowerTable differentiates the single-select options by using different text-fill colors.

Selecting an option

2. Distinct Values

When you select this option, PowerTable creates a distinct list of options from the existing values.

1. In the table below, the Product Name field already contains a list of product names.

Existing values in the table

1. When you choose Distinct, these values become the drop-down options. Click Save.

Distinct values

You can now choose the product name from the list of distinct values that has been created. Notice how the column values are color-coded to indicate a single-select dropdown list.

Selecting an option

3. Lookup Table

PowerTable allows you to look up and source values from other tables as well.

1. When you select 'Lookup,' the highlighted section appears, where you can enter the schema name, the lookup table, the current table column containing the keys, and the lookup table column from which the drop-down options should be sourced.

Schema and lookup table details

Note: If there are any common columns between the lookup table and the current table, you can also set up filter criteria based on those matching columns. Only drop-down values that meet the filter criteria will be displayed, ensuring accuracy and ease of option selection.

Filter based on value in matching column

1. Click Save to save the configuration.

The existing sub-category keys are replaced with their corresponding sub-category names using lookup.

lookup values

For newly inserted rows, you can use the drop-down to select from these lookup values.

In the next section, we will look at the display settings.