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

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

Rate This
  • Comments 16

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.

Leave a Comment
  • Please add 6 and 2 and type the answer here:
  • Post
  • Very useful article.

    Thanks!

    ..Ben

  • That's very useful Eric. Thanks for sharing.

    Regards, Paul.

  • What if I wanted to use that variable in my code somewhere?  How would I reference it in VB code?

  • @Royce Bacon -

    Since the method is public and static, you can invoke it the same way you call any other public static method.  From your code, just call:

    Dim thirtyDaysAgo = ContosoSales.GlobalDates.ThirtyDaysAgo()

    -Eric

  • That's very useful Eric. Thanks So much

    put pleas  why i DONT SEE THE QUIRY SCREEN THANKS

  • @anwaralhasan - To create a query, right-click on the table in the Solution Explorer and select "Add Query". See the documentation here:

    msdn.microsoft.com/.../ee256728.aspx

    Cheers,

    -Beth

  • Hi,

    First, thanks for this howto ! I however have a question: According to what I observe in my application, if I store a global value as a private static variable, it is stored in the server and shared across sessions. That is, if I connect as UserA, set the global value, then connect as UserB, I will see the same global value, just as if I were UserA.

    Is this correct ? If it is, probably is it worth mentioning this in the howto, because it can be an interesting feature if people know about it, or a security disaster if people don't know about it.

    Thanks !

    Sébastien Barré.

  • @Sébastien - Yes this is by design and isn't particular to LightSwitch, any web application would exhibit this. That's why we are not storing these as variables above, they are recalculated every time. Storing static variables can be useful if costly resources are used to retrieve a value that is read-only across all sessions in the AppDomain. But keep in mind that when the AppDomain is recycled the state is lost and would be re-retrieved again. Generally you want to avoid static variables because they can cause weird side-effects.

    Cheers!

  • @Sébastien - Yes this is by design and isn't particular to LightSwitch, any web application would exhibit this. That's why we are not storing these as variables above, they are recalculated every time. Storing static variables can be useful if costly resources are used to retrieve a value that is read-only across all sessions in the AppDomain. But keep in mind that when the AppDomain is recycled the state is lost and would be re-retrieved again. Generally you want to avoid static variables because they can cause weird side-effects.

    Cheers!

  • This seems to break in 2012 RC...  It complains that the type or namespace name 'LightSwitchCommonModule' could not be found in the global namespace (are you missing an assembly reference?

    I had this problem after upgrading a project to 2012RC so I rebuilt it fresh in the RC, but the problem is still there...  Any ideas?

  • @Digital Camel - We did some refactoring in VS 2012 with regards to Modules and Namespaces that inadvertantly broke this scenario.  LightSwitch used to assume that the Global Values were in the namespace defined by your Application name.  But now it changed to assume that Global Values are in the namespace of the Module defining your Global Values.

    So to fix this in VS 2012, you’ll need to change your code's namespace from the name of your application (“ContosoSales” above) to “LightSwitchCommonModule”.  

    namespace LightSwitchCommonModule

    {

       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 doesn't support int type in 2012RC.

  • @Bob Zhao -

    I verified that int global values work correctly in VS 2012 RC.  Are you certain you used ReturnType=":Int32" for your GlobalValueDefinition?  If so, what specifically doesn't work for you?

  • @ Eric Erhardt  

    Thanks!

    When I change the data type from integer to ":Int32" , it appears in the drop down list.

    But since it has been moved to namespace LightSwitchCommonModule, I can't use LinQ in the function any more.

  • How can i do this in Lightswitch 2013?, someone can help me please?

Page 1 of 2 (16 items) 12