Contents x
    Lookup key columns and replace with texts
      • Print
      • Dark
        Light
      • PDF
      Contents

      Lookup key columns and replace with texts

        • Print
        • 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

        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?
        What's Next

        SQL Spreads