How to use advanced filter queries in AX 2012

In AX, almost all list gird forms can be filtered by the Advanced filter form.

On any form, go to File > Edit > Filter > Advanced Filter/Sort (Ctrl-F3):

You’ll know that you can select pretty much any Field to filter by, and in the ‘Criteria’ field you can use various simple wildcards like *, for instance:

Wildcard Description For example, if used to filter the Vendor account field as above
A specific value Any specific value Returns just the one record where that field matches the entered value
* Any character or any number of characters * = All accounts

A* = all accounts starting with A

*B = all accounts ending in B

*C* = all accounts with a C anywhere in the account

D*E = all accounts starting with D and ending in E

? Any single character ?????? = all six digit or six character account codes

??DE?? = all 6 digit accounts codes where DE and are the 3rd and 4th characters

??FG* = all account codes where F and G are the 3rd and 4th characters

.. (double-period) Between Happy .. Sad = All account codes greater than ‘Happy’ and less than ‘Sad’

.. Happy = All accounts less than or equal to ‘Happy’

Sad .. = All accounts greater than or equal to ‘Sad’

You’ll also know that you can combine and reverse all of these filters:

Wildcard Description For example, if used to filter the Vendor account field
, (comma) All filters specified as separated by comma Happy, Sad, Z* = Account code Happy, Account code Sad and all accounts starting with a Z.
! (explanation mark) Not / reversal !A* = All accounts except those starting with an A.

There is also one commonly used special filter:

Wildcard Description For example, if used to filter a Date field
T or today() Today’s date .. today() = all invoices with a date less than or equal to today’s date

However, many people don’t know that you can also define simple field comparisons. You can enter this in as the criteria on any field (not just the fields being compared):

SQL statement Description For example, if used to filter the Vendor account field above
(SQL Statement) An SQL statement that’ll evaluate back to true or false between parenthesis () (VendInvoiceJour.InvoiceAccount != VendInvocieJour.OrderAcount) will select all vendor invoices where the invoice account is different from the vendor account

There are also advanced filter queries based on the fact that there is a generic filtering class called SysQueryRangeUtil.

Here are some examples:

Date based queries Description For example
(Day(n)) Number of days .. (Day(-1)) = All dates up to and including yesterday

(Day(2)) .. = All dates greater than or equal to the day after tomorrow

(DayRange(n,m) Date range (DayRange(-30,30)) = Previous 30 days and future 30 days

The System query range utility is the foundation for a whole family of specific and useful filter queries, and it is possible to customise the method to include customised filters. The Cues found on the standard Dynamics AX role centres give a number of examples on how these filters can be used, for instance:

Table Field Example filter
Actions Person in charge (currentEmployeeId())
Actions End date/time (lessThanDate(14))
Activities Responsible ((ResponsibleEmployee == currentEmployeeId()) || ((ResponsibleEmployee != currentEmployeeID()) && (Sensitivity != 2)))
Activities End date/time (lessThanDate(0))
Order lines Ship date .. today()
Open customer transactions Due date (Day(0))
Net requirements Plan (currentstaticmasterplan())
Net requirements Reference (( ReqTrans.RefType == 8) || (ReqTrans.RefType == 33))
Employee dates Trial period (dayRange(0,30))
Loan Planned return ((lessThanDate(14))&& (schedReturnDate != “”))
Leads Owner ((OwnerId = currentEmployeeId())&& ( OwnerId != “”))
Activities Responsible ((ResponsibleEmployee == currentEmployeeId()) && (ResponsibleEmployee != “”))
Leads Date opened (greaterThanDate(-8))
Vendor invoice journal Due date (day(0))

For more information check out the on-line help page at: https://technet.microsoft.com/en-us/library/aa569937.aspx

Advertisements

2 thoughts on “How to use advanced filter queries in AX 2012

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s