Implicit Lookup Filters with Filter Expression

Property Context Fields can pass values from the current record to the lookup data view. The value is passed as a map in the format LookupFieldName=LocalFieldName.

If LookupFieldName is an actual field in the lookup data view, then an automatic “equals” filter is applied to the view. The value of LocalFieldName data field is copied from the current record to the filter expression.

If the lookup view does not have a data field that matches LookupFieldName, then automatic filtering will not occur. Developers can specify a view Filter Expression or create a business rule to use the value to implement filtering.

Let’s create a Filter Expression for a lookup view that takes advantage of values passed in the Context Fields property.

Navigate to the Orders page and select an order. Create a new Order Details record, and activate the ProductID lookup.

A complete list of products (77 in total) will be displayed by default.

Let’s limit the set of products displayed to only those that are not already associated with the relevant order. If there is an OrderDetails record in the current order with a specific ProductID, then the product should not be displayed in the lookup.

Start the Project Designer. In the Project Explorer, switch to the Controllers tab and double-click on OrderDetails / Fields / ProductID field node.

Change the Context Field property:

Press OK to save the field. Double-click on Products / Views / grid1 view node.

Notice that the ExistingOrderID field does not exist in the Products data controller. The application framework will not perform automatic filtering.

Change the Filter Expression:

@ContextFields_ExistingOrderID is null or ProductID not in
(
    select "OrderDetails"."ProductID" 
    from "Order Details" OrderDetails
    where OrderDetails.OrderID = 
        @ContextFields_ExistingOrderID
)

The ExistingOrderID field that was mapped in in the Context Fields property of OrderDetails.ProductID is referenced as @ContextFields_ExistingOrderID in the filter expression above. The application framework will automatically bind the parameter value.

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

Navigate to the Orders page, and select an order. Create a new order detail, and activate the ProductID lookup. The set of products available for selection will be limited to those that don’t exist for the order. As more OrderDetails records are added, the total number of products available for selection will gradually decrease.