How to Create a Screen with Multiple Search Parameters in LightSwitch

How to Create a Screen with Multiple Search Parameters in LightSwitch

Rate This
  • Comments 16

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

image

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:

image

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:

image

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.

image

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.

image

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.

image

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

image

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:

image

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.

image

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

image

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

image

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.

image

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!

Leave a Comment
  • Please add 1 and 4 and type the answer here:
  • Post
  • 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

  • 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

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

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

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

  • Thanks , It is very useful for me :))

  • Beth, I love all your tutorial, because of them I'm learning Lightswitch and I'm adopting this wonderful technology in my work.

    I have one question related to this topic (Multiple Search Parameters in LightSwitch).

    My screen represents client infrmation with the name of the client with an another column (field) which is a drop down box (Us states), the States field is linked to another table (lookup table for States only).

    How can I create the Search Parameters in LightSwitch with this dropdown box?

    Thanks again for your support.

    Rachida Dukes

  • 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

  • Your examples are very great and help for us! Thanks

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

    1) 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

  • 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

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

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

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

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

Page 1 of 2 (16 items) 12