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