How Do I: Create and Use Global Values In a Query (Eric Erhardt)

Visual Studio LightSwitch has a powerful query subsystem that enables developers to build the queries their applications need in order to display business data to end users.  Developers can easily model a query that will filter and sort the results.  They can also model parameters that can be used in the filters.  And where the designer falls short, developers can drop into code and use LINQ to build up the query to get the job done.  Read more about how to extend a query using code.

One scenario that often comes up in business applications (and probably most applications) is the ability to use contextual information, what we call Global Values in LightSwitch, when filtering data.  For example, I want my screen to display the SalesOrders that were created today.  Or I want to display the Invoices that are 30 days past due.

Visual Studio LightSwitch allows you to use some built-in Global Values in your query easily through the query designer.  It also allows you to add your own Global Values.  Global Values are very powerful and in this post I’ll show you how to use the built-in ones as well as how to add your own.

Using Global Values in a Query

As previously stated, LightSwitch defines some built-in Global Values that can be used out of the box.  These values are all based on the Date and DateTime types:

  • Now
  • Today
  • End of Day
  • Start of Week
  • End of Week
  • Start of Month
  • End of Month
  • Start of Quarter
  • End of Quarter
  • Start of Year
  • End of Year

In order to use one of these Global Values, create a table named “SalesOrder” with a CreatedDate property of type “Date”.  Add a new Query on the SalesOrder table either by clicking the add Query button at the top of the designer, or by right-clicking the SalesOrders table in the Solution Explorer and selecting Add Query.  Name the query “SalesOrdersCreatedToday”.  Add a Filter and select CreatedDate for the property.  In order to access the Global Values list, you need to select a different type of right value in the filter.  To do this, drop down the combo box that has the ‘abl’ icon in it and select “Global”.

image

Since there are Global Values defined for the Date and DateTime types, anytime you create a filter on a property of these types, the “Global” option is available in this combo box.  For types that do not have Global Values defined, the “Global” option won’t show up.

Now that you selected “Global”, you can pick from the built-in values for the value.  Open the combo box at the right and choose “Today”.

image

You now have a query that returns only the SalesOrders that were created on the day that the query is executed.  To display the query results on a screen, create a new screen named “NewOrders” and in the “Screen Data” picker, choose the SalesOrdersCreatedToday query.

image

When you open that screen, only the SalesOrders that were created today will be displayed.

Note: Date and DateTime properties work differently because DateTime also stores the time.  So if the property you are using is of type DateTime, using a query filter ‘MyDateTime = Today’ will not work like it does for a Date type.  Today really means System.DateTime.Now.Date – which equates to 12:00:00 AM of the current day.  For a DateTime, Today really means Start of Day.  So in order to create an equivalent query using a DateTime property, you would need to use the “is between” operator as shown below.

image

Defining a Global Value

Although the built-in Global Values that LightSwitch provides enable a lot of scenarios, there are times when a developer would want to create their own Global Value to use in their applications.  The example I gave above, Invoices that are 30 days or more overdue, could be one of those times.  Imagine I have an Invoice table with a “Closed’” Boolean property and a “DueDate” Date property.  I want to define a query named OverdueInvoices that returns all Invoices that have not been closed and were due more than 30 days ago.  Creating the first filter is very easy:

image

But defining the second filter cannot be done using the built-in Global Values.  There isn’t a way to say “Today – 30” in the value picker.  So instead, I would create a new Global Value named “ThirtyDaysAgo”.  (You could also click on the “Edit Additional Query Code” link in the properties window to code this filter into the query.  But some values are used so often, in different parts of the app, that you don’t want to write code every time you use them.)

To define a new Global Value, you will need to open your project’s ApplicationDefinition.lsml file using an xml editor and manually add a snippet of xml.  There wasn’t enough time to create a visual designer for this scenario, so the only way to define new Global Values is to manually edit the xml.  If you are unfamiliar with editing xml, you may want to create a back-up copy of the ApplicationDefinition.lsml file, in case you make a mistake and the designer is no longer able to load.

To open the file, click the view selector button at the top of the Solution Explorer and select “File View”.

image

Under the “Data” folder, you will find a file named “ApplicationDefinition.lsml”.  Right-click that file and select “Open With…” and choose “XML (Text) Editor”.  This will open the xml that the LightSwitch designer uses to model your application.  Directly under the xml document’s root “ModelFragment” element, add the highlighted GlobalValueContainerDefinition element from the following snippet:

<?xml version="1.0" encoding="utf-8" ?>
<ModelFragment xmlns="http://schemas.microsoft.com/LightSwitch/2010/xaml/model"
               xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml">
  <GlobalValueContainerDefinition Name="GlobalDates">
    <GlobalValueDefinition Name="ThirtyDaysAgo" ReturnType=":DateTime">
      <GlobalValueDefinition.Attributes>
        <DisplayName Value="30 Days Ago" />
        <Description Value ="Gets the date that was 30 days ago." />
      </GlobalValueDefinition.Attributes>
    </GlobalValueDefinition>
  </GlobalValueContainerDefinition>

You can name your GlobalValueContainerDefinition anything you want, it doesn’t need to be named GlobalDates.  Also, you can define more than one GlobalValueDefinition in a GlobalValueContainerDefinition.

Now the only thing that is left to do for this Global Value is to write the code that returns the requested value.  To do this, in the Solution Explorer right click on the “Common” project and select “Add" –> “Class…”.  Name the class the exact same name as you named the GlobalValueContainerDefinition xml element above, i.e. “GlobalDates”.  The class needs to be contained in the root namespace for your application.  So if your application is named “ContosoSales”, the full name of the class must be “ContosoSales.GlobalDates”. 

Now, for each GlobalValueDefinition element you added to the xml, add a Shared/static method with the same name and return type as the GlobalValueDefinition.  These methods cannot take any parameters.  Here is my implementation of ThirtyDaysAgo:

VB:

Namespace ContosoSales  ' The name of your application

    Public Class GlobalDates  ' The name of your GlobalValueContainerDefinition

        Public Shared Function ThirtyDaysAgo() As Date  ' The name of the GlobalValueDefinition
            Return Date.Today.AddDays(-30)
        End Function

    End Class

End Namespace

C#:

using System;
namespace ContosoSales  // The name of your application
{
    public class GlobalDates  // The name of your GlobalValueContainerDefinition
    {
        public static DateTime ThirtyDaysAgo()  // The name of the GlobalValueDefinition
        {
            return DateTime.Today.AddDays(-30);
        }
    }
}

The Global Value is now defined and ready for use.  Next, we need to hook our OverdueInvoices query up to this newly defined Global Value.  Since we manually edited the ApplicationDefinition.lsml file, the designer needs to be reloaded to pick up the new model definitions.  To do this, switch back to the “Logical View” using the view selector button at the top of the Solution Explorer.  Right-click on the top-level application node and select “Reload Designer”.

image

Open the OverdueInvoices query and add a new filter using the Global Value we just defined:

image

That’s it!  Now you can use this query on a screen or in your business logic and it will return all open Invoices that are 30 days past due.

Note: You can define a Global Value that returns a type other than DateTime.  For example, you could make a “Current User” Global Value that returns the string name of the currently logged-in user by returning “Application.Current.User.Name” from the Shared/static method.