To specify filters on a data item in Query Designer, you set the DataItemTableFilter Property of a data item. You can apply a filter on any field in a table, not just those fields that are represented by a column in the query.

The following illustration shows Query Designer for a query that links table 18 Customer and table 37 Sales Line. The illustration includes the Properties window for the Sales Line data item that sets a filter to include only sales lines that have a quantity greater than 10.

The following illustration shows the resulting dataset for the query.

A data item filter is static which means that it cannot be overwritten by a filter on a column or filter row in Query Designer or by the SETFILTER or SETRANGE functions in C/AL code. If one of these filter types is applied to the same field as the data item filter, then the filters are combined. In logical terms, this combination corresponds to an "AND" operation. For example, if the data item filter applies a filter on a field to include values greater than 10 (>10) and a column filter applies a filter on the same field to include values less than fifty (<50), then the resultant filter includes values that are greater than 10 and less than fifty (10< value <50).

The DataItemTableFilter property corresponds to a WHERE clause in an SQL SELECT statement. For more information, see Equivalent SQL SELECT Statements for Query Filters .

Unlike data item filters, filters on a column or filter row are dynamic and can be overwritten from C/AL code at runtime from a call to the SETFILTER or SETRANGE function that sets a filter on the same field.

You use filters on a column to filter on fields that are included in the dataset. To apply a filter on a column, you set the ColumnFilter Property of the column. You can apply a filter on any column, including aggregated columns that are applied a totals method by the Method Property.

You use filters on a filter row to filter on fields that you do not want included in the dataset. To set up a filter row, you add a row of the type Filter in Query Designer that specifies a field, and then set its ColumnFilter property. For more information about how to set up filter rows, see How to: Set Up Filter Rows in Query Designer.

The following illustration shows Query Designer for a query that links the Customer table and the Sales Line table and retrieves the total quantity of items ordered for each customer. The query includes the following filters, as shown by the Properties windows in the illustration.

• A filter on the Sum_Quantity column to include only records from the Sales Line table where the total quantity is less than 50.
  
• A filter on filter row for the Location Code field of the Sales Line table that includes only records where the location code is WHITE.
  

The following illustration shows the resulting dataset for the query.

In an SQL SELECT statement, filters on a column or filter row that do not apply a totals method, as with the Location Code filter row in the example, would correspond to a WHERE clause. Filters on a columns or filter rows that do apply a totals method, as with the Quantity column in the example, would correspond to a HAVING clause. For more information, see Equivalent SQL SELECT Statements for Query Filters .

If you are familiar with SQL, then it is helpful to know how filtering in Microsoft Dynamics NAV queries relates to SQL statements. To specify filters in an SQL statement, you use WHERE and HAVING clauses. The WHERE clause filters on fields. The HAVING clause filters on the results that have aggregated values as applied by of a totals method.

The following example shows the corresponding SQL SELECT statement for the previous data item filter example that links the Customer and Sales Line tables and filters on the Quantity field.

	Copy Code
SELECT Customer."No.", Customer.Name, "Sales Line".Quantity FROM Customer LEFT OUTER JOIN "Sales Line" ON Customer."No." = "Sales Line".Sell-to Customer No. WHERE "Sales Line"."Quantity" > 10

The following example shows the corresponding SQL SELECT statement for the previous column and filter row example that links the Customer and Sales Line tables and filters on the Location Code field and the total sum of the Quantity field.

	Copy Code
SELECT Customer."No.", Customer.Name, SUM("Sales Line".Quantity) as Sum_Quantity FROM Customer LEFT OUTER JOIN "Sales Line" ON Customer."No." = "Sales Line".Sell-to Customer No. WHERE “Sales Line”.”Location Code” = WHITE GROUP BY Customer."No." HAVING Sum_Quantity 50

You can call the SETFILTER and SETRANGE function from the C/AL code of the Microsoft Dynamics NAV object that runs the query object or from the OnBeforeOpen Trigger of the query object.

To call the SETFILTER function, you use the following code.

	Copy Code
Query.SETFILTER(Column, String)

• Query is a variable of the Query type that specifies the query object.
  
• Column is the name of the column or filter row as defined by its Name property.
  
• String is the filter expression.
  

To call the SETRANGE function, you use the following code.

	Copy Code
Query.SETRANGE(Column, FromValue, ToValue)

• Query is a variable of the Query type that specifies the query object.
  
• Column is the name of the column or filter row as defined by its Name property.
  
• FromValue is the lower value of the range.
  
• ToValue is the higher value of the range.
  

For more information, see SETFILTER Function (Query) and SETRANGE Function (Query).

Referring to the query example in the previous section, you can add the following code to the OnBeforeOpen trigger of the query object to change the filters on the Quantity column and the Location_Code filter row to include quantities of less than 50 and a location code of RED.

	Copy Code
currQuery.SETFILTER(Sum_Quantity, '<50'); currQuery.SETFILTER(Location_Code, '=RED');