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.
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.
Right-click on Views, and press Paste. A duplicate of view “grid1” will be 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.
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.
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.
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