Advanced filtering and query options

Important

This content is archived and is not being updated. For the latest documentation, see Microsoft Dynamics 365 product documentation. For the latest release plans, see Dynamics 365 and Microsoft Power Platform release plans.

Applies To: Microsoft Dynamics AX 2012 R3, Microsoft Dynamics AX 2012 R2, Microsoft Dynamics AX 2012 Feature Pack, Microsoft Dynamics AX 2012

The following filtering and query options are available when you use embedded filters or queries.

Syntax

Character description

Description

Example

Value

Equal to the value entered.

Type the value to find.

Smith finds "Smith".

!value

(exclamation mark)

Not equal to the value entered.

Type an exclamation mark in front of the value to exclude.

!Smith finds all values except "Smith".

From-value..To-value

(double period)

Between the two values entered separated by double periods.

Type the From value, then two periods, and then the To value.

1..10 finds all values from 1 through 10.

However, in a string field A..C finds all values starting with "A" and "B" and values exactly equal to "C." For example, "Ca" will not be found.

To find all values from "A*" through "C*", write A..D.

..value

(double period)

Less than or equal to the value entered.

Type the two periods and then the value.

..1000 finds any number less than or equal to 1000, for example "100", "999,95", and 1,000.

Value..

(double period)

Greater than or equal to the value entered.

Type the value and then the two periods.

1000.. finds any number greater than or equal to 1000, for example "1,000", "1,000.01", and "1,000,000".

>value

(greater than)

Greater than the value entered.

Type a greater than sign (>) and then the value.

>1000 finds any number greater than 1000, for example "1000.01", "20,000", and "1,000,000".

<value

(less than)

Less than the value entered.

Type a less than sign (<) and then the value.

<1000 finds any number less than 1000, for example "999.99", "1", and "-200".

value*

(asterisk)

Starting with the value entered.

Type the starting value and then an asterisk.

S* finds any string that starts with S, such as "Stockholm", "Sydney", and "San Francisco."

*value

(asterisk)

Ending with the value entered.

Type an asterisk and then the ending value.

*east finds any string that ends with east, such as "Northeast" and "Southeast."

*value*

(asterisk)

Contains the value entered.

Type an asterisk, then a value, and then another asterisk.

*th* finds any string that contains "th," such as "Northeast" and "Southeast."

?

(question mark)

Having one or more unknown characters

Type a question mark at the position of the unknown character in the value.

Sm?th finds "Smith" and "Smyth"

value,value

(comma)

Matching the values entered separated by commas.

Type all your criteria separated by commas.

A, D, F, G finds exactly "A", "D", "F" and "G".

10, 20, 30, 100 finds exactly "10, 20, 30, 100".

(SQL Statement)

(SQL statement between parenthesis)

Matching a defined query

Type a query as an SQL statement between parentheses.

(data source.Fieldname != "A")

T

Today’s date

Type “T”

(methodName(parameters))

(SysQueryRanget Util method between parenthesis)

Matching the value or range of values specified by the parameters of the SysQueryRangeUtil method

Type a SysQueryRangeUtil method with parameters that specify the value or range of values. For more information, see SysQueryRangeUtil.

  1. Click Accounts receivable > Common > Customer invoices > Open customer invoices.

  2. Press CTRL+F3 to open the Inquiry form.

  3. On the Range tab, click Add.

  4. In the Table field, select Open customer transactions.

  5. In the Field field, select Due date.

  6. In the Criteria field, enter the following:

    (yearRange(-2,0))

  7. Click OK. The list page is updated to list the invoices that match the criteria. For this specific example, invoices that were due in the previous two years are listed in the list page.

The following are additional examples of SysQueryRangeUtil methods with Parameters:

  • Yesterday – Enter “(Day(-1))”

  • Today – Enter “(Day(0))”

  • Tomorrow – Enter “(Day(1))”

  • Last 30 days – Enter “(DayRange(-30,0))

  • Previous 30 days and future 30 days– Enter “(DayRange(-30,30))”