Filter data in queries

Completed

Several ways to filter the data in resulting datasets of queries include:

• Defining the DataItemTableFilter property on a data item

• Defining the ColumnFilter property on a column

• Creating items of type Filter

• Writing AL code in the OnBeforeOpen trigger

DataItemTableFilter property

You can use the DataItemTableFilter property on data items to apply conditions on one or more fields of the table to limit the records in the resulting dataset of the query. You can filter on any field in the table, not just on those fields that are included as columns in the resulting dataset. The filters that you define in the DataItemTableFilter property are static; users or AL code can't override them.

query 7345 "Avail Qty. (Base) In QC Bins"
{
    Caption = 'Avail Qty. (Base) In QC Bins';

    elements
    {
        dataitem(Location; Location)
        {
            DataItemTableFilter = "Directed Put-away and Pick" = const(true);
            dataitem(Warehouse_Entry; "Warehouse Entry")
            {
                DataItemLink = "Location Code" = Location.Code;
                SqlJoinType = InnerJoin;
                column(Location_Code; "Location Code")
                {
                }
                ...
                dataitem(Bin_Type; "Bin Type")
                {
                    DataItemLink = Code = Warehouse_Entry."Bin Type Code";
                    SqlJoinType = InnerJoin;
                    DataItemTableFilter = Receive = const(false), 
                                          Ship = const(false), 
                                          Pick = const(false);
                }
            }
        }
    }
}

ColumnFilter property

You can use the ColumnFilter property on a column to apply a filter condition on a single field. The ColumnFilter property resembles the DataItemTableFilter property, but with some differences. Unlike filters that are set by the DataItemTableFilter property, filters that are set by the ColumnFilter property can be overwritten at run time by calling the SetFilter or SetRange functions from AL code.

If the ColumnFilter property specifies a filter on the same field as the DataItemTableFilter property, then the filters of the two properties are combined. To be included in the query dataset, records must meet the condition of both filters. For example, if the DataItemTableFilter property sets a filter on a field to include values less than fifty (<50), and the ColumnFilter property sets a filter on the same field to include values greater than twenty (>20), then the resultant filter on the field includes values that are greater than twenty and less than fifty.

The SetFilter and SetRange functions overwrite any filter on the same field that is set on a column or filter row by the ColumnFilter property. If a SetFilter or SetRange function filters on the same field as a filter on a data item, as specified by the DataItemTableFilter property, then the function filter and DataItemTableFilter property filter are combined.

The ensuing code example shows how the ColumnFilter property is used on the Qty. (Base) field.

query 7345 "Avail Qty. (Base) In QC Bins"
{
    Caption = 'Avail Qty. (Base) In QC Bins';

    elements
    {
        dataitem(Location; Location)
        {
            DataItemTableFilter = "Directed Put-away and Pick" = const(true);
            dataitem(Warehouse_Entry; "Warehouse Entry")
            {
                DataItemLink = "Location Code" = Location.Code;
                SqlJoinType = InnerJoin;
                ...
                column(Sum_Qty_Base; "Qty. (Base)")
                {
                    ColumnFilter = Sum_Qty_Base = filter(> 0);
                    Method = Sum;
                }
                ...
            }
        }
    }
}

Filter items

You can use filter items to enable dynamic filtering of the resulting dataset on fields that you don't want to include in the resulting dataset of a query.

For example, you might want to let users or developers filter on a date range; however, you do not want to include the date in the dataset. To define a filter, insert a new item in the data item for which you want to add a filter. Instead of adding a column, you can add a Filter, and then specify the field from the table on which you want to enable filtering.

The following code example shows how the filter item is used with the Item No. and Variant Code fields.

query 7345 "Avail Qty. (Base) In QC Bins"
{
    Caption = 'Avail Qty. (Base) In QC Bins';

    elements
    {
        dataitem(Location; Location)
        {
            DataItemTableFilter = "Directed Put-away and Pick" = const(true);
            dataitem(Warehouse_Entry; "Warehouse Entry")
            {
                DataItemLink = "Location Code" = Location.Code;
                SqlJoinType = InnerJoin;
                column(Location_Code; "Location Code")
                {
                }
                filter(Item_No; "Item No.")
                {
                }
                filter(Variant_Code; "Variant Code")
                {
                }
                column(Bin_Type_Code; "Bin Type Code")
                {
                }
                ...
            }
        }
    }
}

OnBeforeOpen trigger

The OnBeforeOpen trigger runs before its model is translated into a Transact-SQL statement and is run against the underlying SQL Server database.

You can use the OnBeforeOpen trigger to set filters on the query by using the SetFilter or SetRange AL functions. When you use AL, you can only filter the query on columns or filters that are defined in the query. You can't filter on any other fields that are present in any of the data items that you have not added to the query model as either a column or a filter.

The following example filters the query to include only those rows where Unit_Cost is not zero (0).

CurrQuery.SetFilter("Unit_Cost",'<>%1',0);