Lookup key columns and replace with texts
  • Dark
    Light
  • PDF

Lookup key columns and replace with texts

  • Dark
    Light
  • PDF

When a SQL Server table is opened in Excel, SQL Spreads can lookup key values in a related table and replace the key values with text values. A Drop-down list with the text values from the related table will be shown in the cell in Excel:
image.png

When the user selects a new item in the Drop-Down list and saves the changes, the corresponding Key value will be saved into the database.

Lookups are set up in the Columns tab in the SQL Spreads Designer:
image.png

You can set up the lookup to fetch the text value from:

  • A table in a database in SQL Server
  • A comma separated list with Key-Text pairs

Lookup Key values from a database table


SQLSpreadsProLogo.png
Lookups in databases are available in SQL Spreads Pro and above


To lookup the key values in a column in a related table, click the Lookup icon for that column in the Columns tab in the SQL Spreads Designer:
image.png

In the shown dialog, select the Lookup in database tab:
image.png

Select the related table

First, select the database and table where to lookup the values:
image.png

Select the matching key column

Then, select which database column in the selected table, matches the key value in your table in Excel:
image.png

Auto-population of relations

If there is a relation defined in the database (A Foreign Key relation) for the column where you are adding a lookup, SQL Spreads will auto-populate the dialog with that relation.

Select which text to show in the Drop-Down list

You can show the text either from one column only, or from two columns with a separator between them:
image.png

Filter the items in the drop-down list

To filter which items from the database table are shown in the Drop-Down list, you can add a WHERE clause filter that will be applied to the list of items shown in the Drop-Down list. The syntax is the same as a WHERE clause in a SQL query:
image.png

Using paramters to filter the drop-down list

You can use parameters such as a selected value in a Tree Filter, or the value from a cell to filter which items are shown in the Drop-Down list.

Custom sorting of the items in the drop-down list

To add a custom sorting, you can add an ORDER BY clause to e.g. sort on other columns or to sort in descending order. The syntax is the same as an ORDER BY clause in a SQL query:
image.png

Default sorting

The items in the drop-down lists are by default sorted in ascending alphabetical order by the two texts shown in the Drop-Down list.

Column names containing spaces

If your column names used for sorting include spaces, you must enclose the column name in brackets, eg [Region Name].

Allow the user to select a NULL value

If a column allows NULL values, you can show a NULL item in the Drop-Down list by checking the Show NULL value in the Drop-Down list checkbox. If the column does not allow NULL values, this checkbox will be disabled.

Remove a Lookup

To remove the lookup for a column, click the Delete lookup button in the left bottom corner:
image.png

Lookup Key values from a fixed list

For cases where you do not have a related table and just have a handful of selectable values (such as Yes & No), you can add a simple list with key-text pairs which will be used for the lookup.

To create a lookup from a fixed list, click the Lookup icon for that column in the Columns tab in the SQL Spreads Designer:
image.png

In the shown dialog, select the Lookup in list tab:
image.png

Then enter a list with items to show in the Drop-Down list in Excel:
image.png

You can enter the list in two ways:

  1. As a list with key-text pairs - then the text will be shown in the Drop-Down list and the key will be written into the database
  2. As a list with texts - then the text will be shown in the Drop-Down list and the text will be written into the database

Fetch the existing values from the database column

To quickly create a new list, you can fetch the existing values in the database column by clicking the Get unique column values button:
image.png

The list in the dialog will then be replaced by the unique values that exist in the database.

Allow the user to select a NULL value

If the table allows NULL values to be stored in the table, you can show the NULL item in the Drop-Down list by checking the Show NULL value in Drop-Down list checkbox. If the table does not allow NULL values, this checkbox will be disabled.

Remove a Lookup

To remove the lookup for a column, click the Delete lookup button in the left bottom corner:
image.png


What's Next