Advanced filtering and query syntax

This article describes the filtering and query options that are available when you use the "matches" operator in the Advanced filter/sort dialog.

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".
(SQL statement) (SQL statement between parentheses) Matching a defined query Type a query as an SQL statement between parentheses. (data source.Fieldname != "A")
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.
  1. Click Accounts receivable > Invoices > Open customer invoices.
  2. Press Ctrl+Shift+F3 to open the Inquiry page.
  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 (yearRange(-2,0)).
  7. 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)).