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

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

  • Comments 18

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

Region

Column Name

Type

RegionID

integer

RegionDescription

string

Territory

Column Name

Type

TerritoryID

integer

TerritoryDescription

string

RegionID

integer

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)

clip_image002

clip_image004

clip_image006

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:

clip_image008

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:

clip_image010

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”

clip_image012

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

clip_image014

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:

clip_image016

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:

clip_image018

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”:

clip_image020

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:

clip_image022

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.

Leave a Comment
  • Please add 4 and 5 and type the answer here:
  • Post
  • Well done Karol!

    You give great & detail explanations in the forum. Much appreciated!

    ..Ben

  • Hi, I'm following exactly the steps you described but the screen is not displayed in the UI. It's available only at design time. The strange thing is that I can't see it neither in the Screen Navigation control. I notice this is happening only with parameterized queries, while with simple filters screens work. Any help would be appreciated. Thanks!

  • Czesc Karol, tu kolega z Literackiej...dobra robota! :) Pozdrawiam!

  • Czesc Karol, tu kolega z Literackiej...dobra robota! :) Pozdrawiam!

  • Allesandro, please make sure that none of the screen items is marked as parameter. In other words the screen must not have any parameters in order to show up in the navigation menu area. Hope that helps! Karol

  • It works now, thanks Karol! The problem was that I still had one parameter. Fantastic post!

  • Thanks! Great post. Just what I was looking for. Very well explained. Keep the good work coming!

  • Great post.

    One thing you need to make clearer is that for every parameter, you have to make sure the "Is Paramater" checkbox is unchecked, else the screen will not show up.

  • Yes, Rake, a lot of people are confused by the screen disappearing from the navigation menu if it has any parameters. We did not realize the extent of the problem initially, sorry. In the final LightSwitch version we decided that designating a screen member as a screen parameter will be an explicit "opt-in" action. We hope this will make things easier. Cheers, Karol

  • Hi, Karol!

    Is it possible to update this article for LightSwitch R2?

  • I agree with Ciro ... it'd be REALLY great and save heaps of confusion if this article was updated for Beta 2 ... please!!!

  • Ciro and Pete,

    I have run through the scenario with Beta 2 bits and everything works pretty much the same as with Beta 1, just a few things have different names. I have summarized the changes at the end of the post. Thanks for bringing this to my attention!

    Cheers!

    Karol

  • Hi Karol,

    I have a question to the other tables of Northwind:

    How should I write a query which shows me all the Orders of Customers from London?

  • Hi John_C

    You can use the query designer to do this easily. Take a look at this video:

    msdn.microsoft.com/.../ff949857

    Also check out the "Working with Queries" learn topic on the Developer Center:

    msdn.microsoft.com/.../gg685457

    Cheers,

    -Beth

  • Hi Beth,

    you are great. Just one hint from you and I found the solution. Thx

Page 1 of 2 (18 items) 12