Advanced filtering and query syntax

Important

Customers using Human Resources, the functionality noted in this article is currently available in both the stand-alone Dynamics 365 Human Resources and the merged Finance infrastructure. Navigation might be different than noted while we make updates. If you need to find a specific page, you can use Search.

This article describes the filtering and query options that are available when you use the Advanced filter/sort dialog or the matches operator in the Filter pane or grid column header filters.

Advanced query syntax

Syntax	Character description	Description	Example
value	Equal to the value that is entered	Type the value to find.	Smith finds "Smith".
!value (exclamation point)	Not equal to the value that is entered	Type an exclamation point and then the value to exclude.	!Smith finds all values except "Smith".
from-value..to-value (double period)	Between the two values that are 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 that start with "A" and "B", and values that are exactly equal to "C". For example, this query won't find "Ca". To find all values from "A" through "C", type A..D.
..value (double period)	Less than or equal to the value that is entered	Type two periods and then the value.	..1000 finds any number that is less than or equal to 1000, such as "100", "999.95", and "1,000".
value.. (double period)	Greater than or equal to the value that is entered	Type the value and then two periods.	1000.. finds any number that is greater than or equal to 1000, such as "1,000", "1,000.01", and "1,000,000".
>value (greater than sign)	Greater than the value that is entered	Type a greater than sign (>) and then the value.	>1000 finds any number that is greater than 1000, such as "1000.01", "20,000", and "1,000,000".
<value (less than sign)	Less than the value that is entered	Type a less than sign (<) and then the value.	<1000 finds any number that is less than 1000, such as "999.99", "1", and "-200".
value* (asterisk)	Starting from the value that is 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 that is entered	Type an asterisk and then the ending value.	*east finds any string that ends with "east", such as "Northeast" and "Southeast".
*value* (asterisk)	Containing the value that is 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 that are separated by commas	Type all your criteria, and separate them by using commas.	A, D, F, G finds exactly "A", "D", "F", and "G". 10, 20, 30, 100 finds exactly "10, 20, 30, 100".
"" (two double quotes)	Matching a blank value	Type two consecutive double quotes to filter for blank values in that field.	Two consecutive double quotes ("") finds rows with no value for the current column.
(Finance and operations query) (finance and operations query between parentheses)	Matching a defined query	Type a query as an SQL statement between parentheses using the finance and operations query language.	((AccountNum LIKE "US*") && (DirPartyTable.Name LIKE "Cont*")) as an example of syntax for a filter condition on a field from the root datasource as well as a field from a different datasource (for the All customers page)
T	Today's date	Type T.	T matches today's date.
(methodName(parameters)) (SysQueryRangeUtil method between parentheses)	Matching the value or range of values that are specified by the parameters of the SysQueryRangeUtil method	Type a SysQueryRangeUtil method that has parameters that specify the value or range of values.	Click Accounts receivable > Invoices > Open customer invoices. Press Ctrl+Shift+F3 to open the Inquiry page. On the Range tab, click Add. In the Table field, select Open customer transactions. In the Field field, select Due date. In the Criteria field, enter (yearRange(-2,0)). Click OK. The list page is updated and lists the invoices that match the criterion that you entered. For this example, invoices that were due in the previous two years are listed. See the table in the next section for additional details about SysQueryRangeUtil date methods, and several examples.

Advanced date queries that use SysQueryRangeUtil methods

Method	Description	Example
Day (_relativeDays=0)	Find a date relative to the session date. Positive values indicate future dates, and negative values indicate past dates.	Tomorrow – Enter (Day(1)). Today – Enter (Day(0)). Yesterday – Enter (Day(-1)).
DayRange (_relativeDaysFrom=0, _relativeDaysTo=0)	Find a range of dates relative to the session date. Positive values indicate future dates, and negative values indicate past dates.	Last 30 days – Enter (DayRange(-30,0)). Previous 30 days and next 30 days – Enter (DayRange(-30,30)).
GreaterThanDate (_relativeDays=0) GreaterThanUtcDate (_relativeDays=0)	Find all dates after the specified relative date.	More than 30 days from now – Enter (GreaterThanDate(30)).
GreaterThanUtcNow ()	Find all date/time entries after the current time.	All future date/times – Enter (GreaterThanUtcNow()).
LessThanDate (_relativeDays=0) LessThanUtcDate (_relativeDays=0)	Find all dates before the specified relative date.	Less than seven days from now – Enter (LessThanDate(7)).
LessThanUtcNow ()	Find all date/time entries before the current time.	All past date/times – Enter (LessThanUtcNow()).
MonthRange (_relativeFrom=0, _relativeTo=0)	Find a range of dates, based on months relative to the current month.	Previous two months – Enter (MonthRange(-2,0)). Next three months – Enter (MonthRange(0,3)).
YearRange (_relativeFrom=0, _relativeTo=0)	Find a range of dates, based on years relative to the current year.	Next year – Enter (YearRange(0, 1)). Previous year – Enter (YearRange(-1,0)).