SQL Spreads can create drop-down lists that let the user select a text value from another table but SQL Spreads will insert the key value into the updated table.

In this demo we have a table with cities which has columns for the city name and the country code. We would like the user to be able to select the full country name in a drop-down list instead of entering the country code. You can download the demo here Drop-down List Demo.

Drop-down lists must be setup using the Advanced Setup. The easiest way to use the Advanced Setup is to start with selecting a table in the Designer, and then go into the Advanced Setup and do further modifications.  

1. Select the Cities table in the SQL Spreads Designer. This will load the table into Excel, and also create a Database Import and a Database Mapping in the Advanced Setup.                                                                                
2. Go to the Advanced Setup and select Database Mapping                                                                                                 
3. Select the Cities Database Mapping in the list and click Edit.                             
4. Click Yes to accept switching from the Designer to the Advanced Setup:                                                                      
5. We would like to add the drop-down list in the CountryCode column. To do so, click the Add button in the rightmost column for the CountryCode row:  
6. The dialog for creating the drop-down list is shown. a. First, select the referenced table to fetch the drop-down list values from (Countries). b. Then select which column in the referenced table that contains the key values (the CountryCode). c. Lastly select which column from the referenced table that should be shown in the drop-down list (The country name in the Name column):                                                                                                                                  
7. Click Ok in all three dialogs to get back to Excel.                                                                                                                   
8. We also need to update the import so that the Country name is shown in Excel, instead of the Country Code. We do that by updating the import SQL query that loads data into the spreadsheet. Go to the Advanced Setup and click the Database Import:                                                                                                                                                                 
9. Then select the Cities Database Import and click Edit:                         
10. In the import SQL query we will have to join the Cities table with the Countries table. Then we need to replace the CountryCode column with the country name from the joined Countries table:  
    

    SELECT

    [Cities].[Id] [Id],

    [Countries].[Name] [Country],

    [Cities].[CityName] [City]

    FROM

    [SQLSpreadsDemo].[dbo].[Cities] AS [Cities]

    INNER JOIN

    [SQLSpreadsDemo].[dbo].[Countries] AS [Countries] ON [Countries].[CountryCode] = [Cities].[CountryCode]                                                                                                                                                                       

11. Click Ok three times to go back to Excel.                                                                                                                               
12. To update the spreadsheet with the new settings, click the Refresh button. You will now see the drop-down list showing the country name. If you select a new country and click Save, the county code for the selected country will be stored in the Cities table: