This post extends my last post where I created a new Schedule table and then created a relationship between that and my Customer table. Now I want to make my application a bit more effective and efficient by creating a query to help me better visualize my scheduling data.

(Thank you Beth Massi, those video tutorials walk-through posts are fantastic!)

What is a LightSwitch Query?

A query is something that returns a bunch of date based on some criteria or filter. In LightSwitch, queries can be created and then used for screens or even in code. I want to use queries as a way of better getting only the data I need to immediately see. More specifically, I want to create a query that will only provide me with scheduling information for today, as well as a query for scheduling information in the future.

I don’t really need to see past scheduling, because I don’t live in the past, although I do learn from it; hence how proficient I have become at software development (yes, I’ve made a LOT of mistakes…in the past…I’m perfect now! ). So, with my superior skill of learning from my mistakes, I will make a whole bunch of mistakes during this demonstration – and learn from them. I am going to create a query that will only return schedule items that are scheduled in the future.

Creating a Query

In LightSwitch, when a query is created, it is added to the application model, meaning that the query is essentially baked into the design of the entity the query is created for. For what I want to achieve, I need to create a query on my collection of Schedule records.

In my LightSwitch application Solution Explorer I expand the Application Data node of the Data Sources folder. I right-click on my Schedules collection and click the Add Query menu item.

Adding a query to my Schedules collection

LightSwitch opens a query designer window for a query titled Query1. I immediately rename this query to FutureSchedule.

Updated query name and display name in query designer

With that out of the way, I now need to add some criteria to my query.

Filter Conditions

I want to only display schedule items that are either for today, or in the future. To do this, I am going to add a filter condition to the query. A filter condition is what helps narrow my query results. In this case, I am going to filter my query results to only display schedule items that have a date of today or greater.

So, I click on the Add Filter link.

Click the Add Filter button

Clicking the Add Filter button presents me with a filter criteria where I can enter the conditions of my filter.

The Filter Condition

In the first drop down I have an option to select a value of Where or Where Not. The condition I want is to return records where there is a StartDateTime value that is greater than or equal to today’s date. So, I;

• select Where from the first drop down,
• then select StartDateTime from the second drop down, which is a list of the attributes of the Schedule entity,
• select the greater than or equal to operator value in the next drop down, And
• …select…wait a minute. I see a problem, can you see a problem?

Hmm, I immediately see that there is going to be an issue here. My requirement is to filter using a value that will change day to day. I can’t simply stick a comparison to a literal date in there because the day is going to change each day.

Looking more at that list of comparison values, I see an item named Parameter. This might be how I can solve this problem. If I can somehow pass the current date to the query, and then have the query use that date as a comparison value, the query should be able to filter correctly with each passing day. Let’s give a go shall we then?

For now, I am going to select Literal as the comparison value, and leave the defaulted value in the filter condition. I’ll update this condition once I have my parameter all set up.

My temporary filter condition

Parameters

Parameters are a way of passing values to a query when the application is running. For me, I want to pass the current date as a parameter to the query, and then have the query use that parameter as the filter condition.

In the query designer, I click the Add Parameter button.

Click the Add Parameter button

Clicking the Add Parameter button presents me with a textbox and and parameter type dropdown box. I enter StartDateTimeParameter as the name of my parameter, and select DateTime as the type of parameter.

The StartDateTimeParameter Parameter

In the Properties window for the parameter, I update the Display Name property to  Schedule From so that if I ever have a need to be prompted for a parameter, the prompt will be better named.

Cool, now that I have my property created, I go an update the filter I previously created so that it will use the parameter value instead of a literal value.

Updated Filter Condition

One more tweak left -sorting

Sorting

I want to make sure that query results show my most recent schedule. I need to order the results by StartDateTime, in ascending order.

No problem, I click the Add Sort button in the query designer window.

My Sort criteria

I select StartDateTime as the field I want to sort on, and then select Ascending as the order by which I want to sort the field.

The Screen

Are you sitting comfortably? Then we’ll being…

Armed with my query, I now want to create a screen that shows my my query results. So, on the menu bar at the top of the query designer, I click the Add Screen… button.

Click the Add Screen button

A simple list of scheduled items is all I need, so in the Add New Screen window I select the Editable Grid Screen template.  LightSwitch defaults a Screen Name of EditableScheduleGrid, which is fine. Then from the Screen Data drop down box I select the Schedule query I just created. I click the OK button.

Creating the screen for the query

LightSwitch opens the screen designer for my new EditableScheduleGrid screen. The first thing I do is update the Display Name property for the screen to say My Schedule

Changing the Display Name for the screen

Almost there. I need to now somehow get the current date to the parameter for use by the query. To do that, I have to first create a property for my screen to hold the date.

When LightSwitch created my new screen it recognized that the data for the screen is my query. LightSwitch also saw that there is a parameter for the query, so it added the parameter as a property of the screen. I can see this because it is listed on the left of the designer.

Parameter query, and screen property showing in designer

I like how this is done automagically. I still need to wire the parameter to the screen property. This is easy, all I do is click the screen property (StartDateTimeParameter in grey), and LightSwitch wires the two together – as shown by the little arrowed line connecting the two.

Query parameter and screen property wired together

With the StartDateTimeParameter screen property selected, I notice that the Property Type for the StartDateTimeParameter screen property is shown as a DateTime? value. Not sure what it means if that value has a question mark on the end of it, so I select the DateTime value (without the question mark).

Updating the StartDateTimeParameter property type

Okay, now to get that date in there.

There are two things that I need to do next. The first is to call the screen to open it, and the pass the parameter to the screen that I am opening. So, I open the screen designer for the screen I know will open first in the application. In this case, the screen is named CustomerList.

I open the CustomerList screen in the screen designer. In the screen designer I expand the Screen Command Bar tree node and click the Add button. I then click to add a New Button.

Adding a new button to the screen command bar.

Now I have a new button that will show up at the top of the screen when the application runs. I need to now set some properties on this button so it shows up all nice like.

With the new button selected, I head over to the properties pane and make some edits. I enter My Schedule in the Display Name property.

Updates to the new button properties

Over on the left of the designer, I see the Button object added to my screen.

The Button object

I click on it to view the properties of the object. I edit the Method Name property to say ShowMySchedule. Below that, I click the Edit Execute() Code link.

The new Method Name for the button, and the Edit Execute() Code link

LightSwitch opens the CustomerList.vb code window for with a stub created for my new method.

Namespace MyFirstApplication

  Public Class CustomerList

    Private Sub ShowMySchedule_Execute()
      ' Write your code here.

    End Sub
  End Class

End Namespace

It is here that I am going to open my new screen and pass the current date as a parameter to the new screen. Here is what enter into the method.

Namespace MyFirstApplication

  Public Class CustomerList

    Private Sub ShowMySchedule_Execute()

      ' Get just the date, not the both the date and time.
      Dim curDate As DateTime = Date.Now.Date

      ' Show the screen created for the query and pass the
      ' parameter to the query via the screen.
      Me.Application.ShowEditableScheduleGrid(curDate)

    End Sub
  End Class

End Namespace

Cool, now lets hit the old F5 key and see what happens….

SCORE!! My application opened. And because the first screen has that button added to the top, the new button shows up as expected.

The button showing on the opening screen.

When I click on the button, the code fires and opens the new screen showing my filtered Schedule collection, ordered by start date. Awesome!

The new Schedule screen using the current date parameter passed to the FutureSchedule query.

Cool! Now with a few tweaks to the screen I have an excellent scheduling system available.

Doh! I’m late for supper. I gotta go.

Cheers!