How to Create a Screen with Multiple Search Parameters in LightSwitch

Note: This article has been updated for Beta 2 on 3/17/2011

A couple weeks ago I posted about how to create a custom search screen where you could specify exactly what field to search on. I also talked about some of the search options you can set on entities themselves. If you missed it:

Creating a Custom Search Screen in Visual Studio LightSwitch

Since then a couple folks asked me how you can create a custom search screen that searches multiple fields that you specify. Turns out this is really easy to do – you can specify multiple filter criteria with parameters on a query and use that as the basis of the screen. Let me show you how.

Creating a Parameterized Query with Multiple Filters

For this example let’s create a search screen that searches for Patients that were born within a certain date range.  We’ll use the same Patient table that I used in the last article which has a date field called BirthDate. To search in a range, we’ll need two parameters – a StartDate and an EndDate – that will be used in the filter of the query.

To create a query, right click on the Patient table in the Solution Explorer and select “Add Query”. Another way to do add a query is if you have the Table Designer open you can just click the “Query…” button at the top of the designer. (For an intro to the Query Designer see this video: How Do I: Sort and Filter Data on a Screen in a LightSwitch Application?)

Name the query PatientsInDateRange . Click the “+ Add Filter” and add a “Where” condition and select Birthdate, >= (is greater than or equal to), and then choose “@ Parameter” for the value type. Then choose “Add New” and a parameter will be created below for you. Rename the parameter “StareDate”.

Next click on the “+ Add Filter” below the first condition you just created and add another “Where” condition. Select Birthdate, <= (is less than or equal to), and then choose “@ Parameter” for the value type and add a new parameter called “EndDate”. I’ll also add a sort on LastName then FirstName ascending. Your query should look like this:

Creating a Custom Search Screen

Now that we have a query that accepts multiple parameters we can create a screen based on this query. You actually don’t need to choose the Search Data Screen template, you can choose any template you want. In fact, for this example I will select the List and Details Screen. If you are still sitting in the Query Designer then you can just click the “Add Screen” button at the top of the designer then select the List and Details template and choose the PatientsInDateRange query we just created for the screen data:

Once you pick a template the screen designer opens. You will see the query and its fields on the left of the screen and a hierarchal view of the screen controls in the center. On the right is your properties window. Since our query has two parameters, LightSwitch automatically has added screen properties called “StartDate” and “EndDate” which are used as the parameters to the query (this is indicated by the arrow from the query parameter to the screen property). These values will be supplied by the user but you can control that by checking the “Is Parameter” property as I explained in my previous post.

So let’s run this and see what we get. Hit F5 and open up the “Patients In Date Range List Detail” screen and you will see two Date Pickers at the top.

I want to adjust the layout a bit so that the labels of the date pickers are flush with the list box so click the “Design Screen” button in the upper-right corner, select the List Column then click Add, New Group.

Move the group to the top using the blue arrow above. Delete the Patient Start Date and Patient End Date then select the Group and then click Add and select the Patient Start Date and Patient End Date fields to put then into the Group.

Finally, select the Group and in the properties below set the Vertical Alignment to Top.

That looks better! So now when we enter values the results are returned into the Patient ListBox below. And since we chose a “List and Details” screen template, when we select a Patient in the list we can edit the details:

Specifying Optional Parameters in Filters

In the above example we’re requiring the user to enter both the Start and End dates before any meaningful results are returned. But what if we want a screen where the user can search on combinations of fields? That’s also easy to do as well. Let’s create a query called PatientCustomSearch that allows users to filter on any combination of LastName, FirstName or SSN. This time add a filter “Where” condition and select FirstName, contains, and then choose “@ Parameter” for the value type just like we did in the previous example. Choose “Add New” and a parameter will be created below for you. Do the same thing for LastName and SSN. Next select each parameter and in the Properties window check “Is Optional” for all of them.

This time I’ll create a screen based on the Editable Grid screen template and select the PatientCustomSearch as the screen data.

I’m also going to disable any searching in the grid because we have three fields to search combinations of records now. Select the PatientCustomSearch query on the left and uncheck the “Supports search”.

Now hit F5 to debug and open up the screen. You will see three textboxes at the top that you can use to enter any combination of search criteria and results will be returned that contain the criteria you enter as you enter it. In my query I am matching on strings that are found anywhere in the fields (contains). You may want to change the filter clauses to “equals” if you want to perform an exact match to the fields.

As you can see creating your own custom search screens doesn’t require code, it just requires a query that takes parameters the way you want them. LightSwitch was designed to make it easy to search, filter, sort and page data so I encourage you to try it our for yourself. Visit the LightSwitch Developer Center to learn more.

Enjoy!

Comments

• Anonymous
  November 22, 2010
  Excellent, just plain excellent. I was wondering how to make the parameters optional. I had already figured out how to create a screen with multiple parameters. Thanks again Bernard

• Anonymous
  November 23, 2010
  Thank you very much Ms.Beth You answered my questions immediately Thank you This is  I want to learn is very important for me thank's

• Anonymous
  November 23, 2010
  Can you help me with this? I am building a windows application and i am facing this error "Unable to install or run the application. The application require that assembly Microsoft.SqlServer.DataProfilingTask Version 10.0.0.0 be installed in the Global Assembly Cache (GAC) first." when i try to install it to another machine with Windows XP OS. Another problem that i am facing is of remote connectivity. previously when the application got sucessfully installed it did not connect to the Server machiene with Windows server 2003 R2 OS. The firewall is off, the connection string works fine but still non of the other machien is being connected to the SQL Server Instance.

• Anonymous
  November 24, 2010
  @Unzila -- I'm not familiar with that specific error and would probably need more info on what you are building. Please ask these types of questions in the forums. For windows application development try: social.msdn.microsoft.com/.../threads HTH, -B

• Anonymous
  December 13, 2010
  Thanks Beth! Excellent material, and very well explained. Also, it is so easy to follow! I had done some previous work on my own, but some points were missing and now it is all covered. Thanks again!

• Anonymous
  March 25, 2011
  Thanks , It is very useful for me :))

• Anonymous
  September 19, 2011
  The comment has been removed

• Anonymous
  September 19, 2011
  Hi Rachida, Thanks for using Visual Studio LightSwitch! Please take a look at this post, it explains how to do what you need: blogs.msdn.com/.../how-to-use-lookup-tables-with-parameterized-queries-karol-zadora-przylecki.aspx HTH, -Beth

• Anonymous
  November 27, 2011
  Your examples are very great and help for us! Thanks

• Anonymous
  December 04, 2011
  Hi Beth, Thanks for this great blog. Is it possible to overwrite the default search handler? I would like to customize the search results using the default search editbox. Let's say i have 2 records in my table:

1. Product="DVD-001", Category="DVD", Description="DVD-RW, rewritable, 4GB"
2. Product="DVD-002", Category="DVD", Description="DVD-RW, rewritable, lightscribe, 9GB" I filter to products "DVD", in addition I need to search! within the results like this "+rewritable +lightscribe" (This example is very simplified, I really need to search with multiple criteria in the default searchbox - not a additional filter!) Thanks in advance, Paul

• Anonymous
  December 05, 2011
  Hi Paul, You can use the technique above to present the user with a category filter first and then the description. You would just require the Category and make the Description optional and make sure you make the filter on description is using 'contains' keyword. Otherwise you can override the _PreProcessQuery method to specify addional query clauses you want to add in code. HTH, -Beth

• Anonymous
  January 04, 2012
  Hi Beth, Thanks for your quick answer and the hint with "PreProcessQuery" which put me to the correct direction. In meantime I found the "Lightswitch Filter Control" (code.msdn.microsoft.com/.../Filter-Control-for-Visual-90fb8e93) which is a nice extension (+easy to use) which allows the user to create a customizable filter (multiple criteria) during runtime. Hope we see more control extensions like this from Lightswitch team in future ;-) Best, Paul PS: Happy new year!

• Anonymous
  September 07, 2013
  Hello Beth, nice tutorial. But it's not by chance that you selected two date columns. what about adding a filter for a column pointing to another entity, e.g. filter orders for articles. Here you can only use a edit control and have query where you limit e.g. the article name using = or like operator. BUT: select an article from a list isn't easyly possible. You would have to a list or a or grid to the screen and then capture the change in the selected item. this is doable but has the BIG disadvantage that both controls use LOT's of screen space. The possiblity to use a combobox or a modal window picker would be really helpful. Maybe in the next version of lightswitch.

• Anonymous
  September 09, 2013
  @this is a little cheated ;-)  -- You can certainly do what you're describing. Just add a data item to the screen of the Entity type you want. It will automatically create a selectable list. Then databind that to your query. I show how to do that here: blogs.msdn.com/.../how-to-use-a-picker-as-a-filter-in-your-lightswitch-apps.aspx Cheers, -Beth

• Anonymous
  May 13, 2014
  Beth, this is a great article. I want to modify what you have written. I want a beginning and ending date but I want two pull downs labeled as follows: Date1 - 1-30 days Date2 -31-60 days. These are both pull downs and once selected for Date1 a items appears 'last 30 days'.  This is queried against a date field known as  Date2 and the query states query all records that have Date2 from the last 30 days. Date2 has a pull down labeled '31-60' and that Date2 field  query all records that have Date from the last 30-60 days. Is that possible?

• Anonymous
  May 16, 2014
  Hi Beth, this tutorial is very good, thanks. Could you help me to solve a simple problem? I have 2 Integer parameters on my sreen (I didn't "flaged" the option "Is Parameter" in both), and they are appearing with a default value of 0 when the screen opens, I need to remove this 0, or show a text like "Enter your phone number here" on the parameter field. And these parameters can't be "optional". I am using VB Web App.