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

    Lookup key columns and replace with texts

    • Dark
      Light
    • PDF

    Article summary

    Intro

    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

    Create a Lookup of 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

    Dependent Drop Downs

    You can create dependent drop downs with a workaround that can be found in our Tips and Tricks section.

    Create a Lookup of 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


    Was this article helpful?