How to use lookup tables with parameterized queries (Karol Zadora-Przylecki)

Suppose you have two tables participating in a 1:many relationship, like Regions and Territories from the Northwind sample database:

Region

Territory

The task is to build a screen that lets the user select a Region from a drop-down list and displays Territories that are within the selected region. This is possible to do with LightSwitch without writing any code. In this post I am going to show you how.

Start with the data

First you need to get the definitions of tables participating in the relationship into your application. In this post we will be using Regions and Territories from the Northwind sample database, but you can also create the tables from scratch in LightSwitch. For more information about attaching to existing databases and creating tables in LightSwitch please refer to How to: Connect to Data and How to: Create Data Entities topics in LightSwitch documentation. At the end you should have two tables linked by a 1:many relationship (see below)

Create a parameterized query

Next, create a parameterized query that will list territories within given region. To do it, right-click Territories table in Solution Explorer and choose “Add Query”. Name the query “TerritoriesByRegion” and add a parameterized filter clause to select appropriate territories:

Create a screen

Next, create a screen based on the TerritoriesByRegion query. We’ll use editable grid screen template here but you can use another template as well. When a parameterized query is used to create a screen, LightSwitch will automatically create a TextBox UI for the query parameter, together with the associated screen field. This works, but requires the end user of the app to remember and type in the name of the Region(s) of interest, which is not ideal. Instead, we want the list of the regions to be retrieved from the database and presented to the end user as a list of choices, so we are going to delete the textbox UI and the corresponding screen field that LightSwitch has created. After that your screen should look like this in the designer:

Set up the parameter UI

To make the parameter UI work you need three ingredients:

1. A screen member (collection) to hold the list of choices,
2. A screen member (single Region instance) to represent the selected region, and
3. A ComboBox control wired up to the query parameter, so that when the selection changes, the TerritoriesByRegion query is executed and new territories are displayed

Let’s do it! Click the “Add Screen Item” button at the top of the screen designer, choose “Queries” option, select “Regions* (SelectAll)” from the list and name the query “AllRegions”

Next add a member for the selected Region—this time use “Local Property” option in Add Screen Item dialog:

Drag and drop the newly created SelectedRegion screen item on the left to screen content tree in the center of the screen designer. Change the control to ComboBox and customize the entry template so that it uses Horizontal Stack instead of Summary control. Leave only RegionDescription property in the stack (delete the RegionID which is redundant and not meaningful to the end user). Your screen content tree should look like this now:

Select the “SelectedRegion” ComboBox in the screen content tree. In the property sheet set the Choices property to “AllRegions”. This will ensure that the ComboBox is populated correctly:

Finally, we need to link the SelectedRegion ComboBox to the query parameter. Select the parameter in screen members area (it is under TerritoryCollection | Query Parameters), and set its Parameter Value property to “SelectedRegion.RegionID”:

That’s it!

You can now hit F5 and enjoy the newly created screen, when the user selects a region from the ComboBox, the territories are in that region are displayed:

Of course, if you created the data schema from scratch, you will need to create some Regions and Territories data first before testing the TerritoriesByRegion screen. ;-)

LightSwitch Beta2 Update

LightSwitch Beta 2 introduces a few changes affecting the scenario described above, but the impact is very minor:

• “Add Screen Item” command in screen designer is now called “Add Data Item”
• The “Combo Box” control is replaced by “Auto Complete Box” control
• Instead of using “Horizontal Stack” layout control, use “Rows” layout control

Everything else works just the same.