How-to: Filtering and ordering

Example 1

Task

There are remaining books in stock.

CLASS Book 'Book';
name 'Name' = DATA ISTRING[100] (Book);

CLASS Stock 'Warehouse';
name 'Name' = DATA ISTRING[100] (Stock);

// for example it is made a data property, although usually it is calculated
balance 'Balance' = DATA INTEGER (Book, Stock); 

We need to create a form to display the balances of books in a given stock in alphabetical order.

Solution

FORM onStockObject 'Balances'
    OBJECTS s = Stock PANEL
    PROPERTIES(s) name SELECTOR

    OBJECTS b = Book
    PROPERTIES READONLY name(b), balance(b, s)
    ORDERS name(b)

    // Option 1
    FILTERS balance(b, s)

    // Option 2
    FILTERGROUP bal
        FILTER 'With positive balance' balance(b, s) > 0 'F10'
        FILTER 'With negative balance' balance(b, s) < 0 'F9'
        FILTER 'With balance' balance(b, s) 'F8' DEFAULT
        FILTER 'No remainder' NOT balance (b, s) 'F7'
;

Option 1 sets up a fixed filter that the user cannot remove. Option 2 allows the user to choose between predefined criteria (by default the one for which the DEFAULT option is set).

Example 2

Task

Similar to Example 1.

We need to create a form to display remaining books in several warehouses, with the possibility of filtering by a specific warehouse. Ordering should be first by warehouse, and within that by book title.

Solution

filterStock = DATA LOCAL Stock ();
nameFilterStock 'Warehouse' = name(filterStock());

FORM onStockLocal 'Balances'
    PROPERTIES() nameFilterStock

    OBJECTS sb = (s = Stock, b = Book)
    PROPERTIES READONLY name(s), name(b), balance(b, s)
    ORDERS name(s), name(b)

    FILTERS s = filterStock() OR NOT filterStock()
;

In this case a warehouse cannot be declared via the OBJECTS block, because then not specifying a warehouse for filtering will not be an option.

The condition s = filterStock() OR NOT filterStock() is the standard optional filter pattern: while the filter value is empty (filterStock() is NULL), the OR NOT branch is true for every row and the form shows all records; once a warehouse is selected, only its rows remain.

Example 3

Task

There is a list of orders for certain customers

CLASS Customer 'Customer';
name 'Name' = DATA ISTRING[100] (Customer);

CLASS Order 'Order';
date 'Date' = DATA DATE (Order);

customer 'Customer' = DATA Customer (Order);
nameCustomer 'Customer' (Order o) = name(customer(o));

We need to create a form to display the list of orders allowing to filter by date and/or customer. By default, orders should be in descending date order.

Solution

filterCustomer = DATA LOCAL Customer ();
nameFilterCustomer 'Customer' = name(filterCustomer());

FORM orders 'Orders'
    PROPERTIES() nameFilterCustomer

    OBJECTS dates = (dateFrom = DATE, dateTo = DATE) PANEL
    PROPERTIES df = VALUE(dateFrom), dt = VALUE(dateTo)

    OBJECTS o = Order
    PROPERTIES(o) READONLY do = date, nameCustomer
    ORDERS do DESC
    FILTERS date(o) >= dateFrom, date(o) <= dateTo,
            customer(o) = filterCustomer() OR NOT filterCustomer()
;

It should be noted that the dates in this case should always be selected (by default, the current date will be set when the form is opened). But it is possible not to select a customer.

Also, note that what is set in ORDER BY is not an expression but a specific property added to the form. Thus, we cannot order by a property that has not been added to the form.

Example 4

Task

Similar to Example 3.

We need the orders form to let the user filter by customer without a separate property in a panel: the filter row should be shown above the table as soon as the form opens.

Solution

FORM ordersFiltered 'Orders'
    OBJECTS o = Order
    PROPERTIES(o) READONLY date, nameCustomer FILTER
    ORDERS date(o) DESC
;

The FILTER option declares a user filter for the customer property. When the form opens, its condition row is already shown with an empty value and filters nothing; once the user enters a customer, only that customer's orders remain. The user cannot remove the row - resetting the filters only clears its value - but, unlike the local property of Example 3, with the filter controls shown the user can change the comparison, and no property outside the form is needed.