Code On Time applications support custom functions in view filter expressions. The new function $external() has been introduced in the latest web application generator update. You can use this function to access values in the URLs of the pages and values of fields listed in Context Fields property of lookup fields.
Consider the EmployeeTerritories table from the Northwind sample database.
Any territory can be associated with an employee only once due to the primary key constraint.
Here is the screen shot of the form that allows creating new EmployeeTerritories records.
The employee last name is already selected. If a user clicks on (select) link in Territory Description field then an entire set of 53 territories will be presented.
Let’s make sure the the territories that are already assigned to a selected employee are not available in the list. For example, if we assign a new territory to employee Davolio then the last territory on the first page in the list of territories in the screen shot above, Wilton from Eastern region, is not listed along with a couple of other territories.
Start the code generator, click on the project name, click Design button, and select EmployeeTerritories data controller, click Edit, and enter EmployeeID in the input labeled Context Fields under Dynamic Properties section. Save changes.
From now on, the application will pass the value of the selected EmployeeID to the lookup view Territories.grid1 attached to field TerritoryID. If the lookup view has the field EmployeeID then the rows of Territories will be automatically filtered to match the value of EmployeeID selected in the new record.
Well, there is no field named EmployeeID in the table Territories.
Let’s create a filter expression for grid1 view in data controller Territories. Click on Home link in the bread crumbs of the Designer at the top of the page. Select Territories data controller. Activate Views tab and choose grid1. Click Edit button.
Enter the following in Filter Expression property:
$external('EmployeeID') is null or not TerritoryID in (
select "TerritoryID" from "EmployeeTerritories"
where "EmployeeID" = $external('EmployeeID')
)
Save changes, exit Designer and generate the application, navigate to Employee Territories page, select Davolio in the Employee Last Name field, and click (select) in Territory Description input. You will see a lookup window with 50 records in it. The three records that are already assigned to Ms. Davolio are not presented in the list.
Navigate to dedicated Territories page and observe that 53 records are still available there. If you enter a URL parameter EmployeeID with value of “1” in the address bar of your browser and hit Enter key then 50 records will be displayed as well. Your URL may look as the one below.
http://localhost:36745/Pages/Territories.aspx?EmployeeID=1
The filter expression assigned to grid1 view does not work if there is no external filter passed in the page URL or in the Context Fields of the lookup view. This is guaranteed by the first comparison in the expression.
$external('EmployeeID') is null or . . .
The second part of the filter expression will test the TerritoryID to ensure that it is not matched to any territories that are already present in EmployeeTerritories and uses $external(‘EmployeeID’) to further limit the scope of test.
Notice the use of double quotations around field and table names. Please use the appropriate symbol that works with your database server to ensure that application will not be trying to resolve the name against the dictionary of fields of the data controller.
Here is physical SQL statement executed by your application. Note that parameters @p0 and @p1 will be replaced with the value of the EmployeeID passed in a URL or as a context field.
with page_cte__ as (
select
row_number() over (order by "Territories"."TerritoryID") as row_number__
,"Territories"."TerritoryID" "TerritoryID"
,"Territories"."TerritoryDescription" "TerritoryDescription"
,"Territories"."RegionID" "RegionID"
,"Region"."RegionDescription" "RegionRegionDescription"
from
"dbo"."Territories" "Territories"
left join "dbo"."Region" "Region" on "Territories"."RegionID" = "Region"."RegionID"
where
(
(((@p0) is null or not "Territories"."TerritoryID" in (
select "TerritoryID" from "EmployeeTerritories"
where "EmployeeID" = (@p1)
))))
)
select * from page_cte__ where row_number__ > @PageRangeFirstRowNumber and row_number__ <= @PageRangeLastRowNumber