Wednesday, February 13, 2013
“Filter Expression” Property of Views

The “Filter Expression” property allows limiting the records displayed in a specific view by an SQL compatible logical expression.

The default Orders grid view in a sample Northwind web application displays all orders.

List of all orders.

Let’s create a new view by the name of “Orders This Month” that will only display orders placed in the current month.

Start the Project Designer. In the Project Explorer, switch to the Controllers tab. Right-click on Orders / Views / grid1 node, and press Copy.

Copying 'grid1' view of Orders controllers.

Right-click on Views, and press Paste. A duplicate of view “grid1” will be created.

Pasting onto Views node of Orders controller.      Duplicate of 'grid1' view, called 'v100' has been created.

The properties screen for the new view will be open in the Project Browser. Make the following changes:

Property New Value
Label Orders This Month
Filter Expression $thismonth(OrderDate)

Press OK to save. Double-click on Orders / Views / grid1 node.

View 'grid1' of Orders controller.

Change the label:

Property New Value
Label All Orders

Press OK to save. On the toolbar, press Browse.

Navigate to the Orders page. The All Orders view will continue to display an unfiltered list of orders.

The default grid view of Orders displaying an unfiltered list.

Use the View Selector dropdown in the top-right corner to switch to the Orders This Month view. Only orders placed in the current month will be displayed.

A list of orders filtered in the current month.

When the command is configured in order to retrieve data from the database, the Filter Expression is parsed by the application framework and any standard filter operators will be replaced by the SQL equivalent. SQL compatible logical expressions may also be used. In the example above, the filter is passed to the application framework as the following:

$thismonth(OrderDate)

The framework converts the filter to the following where expression. 

"Orders"."OrderDate" between @p0 and @p1

This expression is then inserted into the select command and passed to the server.

with page_cte__ as (
select
row_number() over (order by "Orders"."OrderID") as row_number__
,"Orders"."CustomerID" "CustomerID"
,"Orders"."EmployeeID" "EmployeeID"
,"Orders"."OrderDate" "OrderDate"
,"Orders"."RequiredDate" "RequiredDate"
,"Orders"."ShippedDate" "ShippedDate"
,"Orders"."ShipVia" "ShipVia"
,"Orders"."Freight" "Freight"
,"Orders"."ShipName" "ShipName"
,"Orders"."ShipAddress" "ShipAddress"
,"Orders"."ShipCity" "ShipCity"
,"Orders"."OrderID" "OrderID"
,"Customer"."CompanyName" "CustomerCompanyName"
,"Employee"."LastName" "EmployeeLastName"
,"ShipVia"."CompanyName" "ShipViaCompanyName"
from
"dbo"."Orders" "Orders"
    left join "dbo"."Customers" "Customer" on "Orders"."CustomerID" = "Customer"."CustomerID"
    left join "dbo"."Employees" "Employee" on "Orders"."EmployeeID" = "Employee"."EmployeeID"
    left join "dbo"."Shippers" "ShipVia" on "Orders"."ShipVia" = "ShipVia"."ShipperID"

where
(
("Orders"."OrderDate" between @p0 and @p1)
)
select * from page_cte__ where row_number__ > @PageRangeFirstRowNumber 
    and row_number__ <= @PageRangeLastRowNumber