Advanced filtering in Generic Inquiries

Scenario

Example Filter Clause

Explanation

Equality

AccountCD eq '10100'

Return rows where the account code matches "10100".

Inequality

AccountCD ne '10300'

Return rows where the account code is anything but "10300".

AccountCD eq '10100' or AccountCD eq '10200'

Return rows where the account code is either "10100" or "10200"

And

AccountCD eq '10100' and CustomerCD eq 'ABARTENDE'

Return rows where the account code is "10100" and the customer code is "ABARTENDE"

Less than

InvoiceDate lt 2010-01-01T00:00:00Z

Return rows where the invoice date is in year 2009 or earlier.

Less than or equal to

InvoiceDate le 2010-01-01T00:00:00Z

Return rows where the invoice date is on 2010-01-01 or earlier.

(In Acumatica, invoice or transaction dates usually point to the beginning moment of the day, so we will not be missing any documents from January 1st)

Greater than

InvoiceDate gt 2010-01-01T00:00:00Z

Return rows where the invoice date is at least
2010-01-02.

(In Acumatica, invoice or transaction dates usually point to the beginning moment of the day, so we will not be including any documents from January 1st)

Selecting a range of values

InvoiceDate ge 2010-01-01T00:00:00Z and
InvoiceDate lt 2011-01-01T00:00:00Z

Return rows where the date column value is in 2011.

Selecting null/empty values**

CustomerNbr eq null

("null" must be lowercase)

Returns rows where the CustomerNbr value is empty

Selecting non null/empty values**

CustomerNbr ne null

("null" must be lowercase)

Returns rows where the CustomerNbr value is not empty

Ends With*

endswith(DocType, 'Memo')

Return rows where document type ends with "memo"

Starts With*

startswith(CustomerName, 'John')

Return rows where the customer name starts with "John"

Contains Substring*

contains(Description, 'overdue') eq true

Return rows whose description column contains the text "overdue"

Length*

length(CustomerName gt 20)

Return rows where customer name is longer than 20 symbols

Index Of*

indexof(CustomerName, 'John') gt 0

Return rows where the customer name contains "John" but does not begin with "John".

Replace*

length(replace(Description, ' ', '')) lt 10

Return rows where the description, in which all whitespace was removed, has a length less than ten symbols.

Get Substring*

substring(SubCD, 3) eq 'DIST'

Return rows where the subaccount code substring beginning at index 3 equals "DIST".

Convert to Lowercase*

tolower(CustomerName) eq 'john'

Return rows where customer name, converted to lowercase, equals "john".

"JOHN", "john", "jOhN", "John" will all match.

Convert to Uppercase*

toupper(CustomerName) eq 'JOHN'

Return rows where customer name, converted to lowercase, equals "JOHN".

"JOHN", "john", "jOhN", "John" will all match.

Trim*

trim(tolower(Description)) eq 'present'

Return rows where the description matches the word "present" in any character case, and is surrounded by any amount of whitespace.

For example, " PRESENT " will match the filter.

Concat*

concat(FirstName, ' ', LastName) eq 'John Doe'

Return rows where the concatenated first and last name equal "John Doe".

Get Day from Date*

day(InvoiceDate) eq 31

Return rows where the invoice date is on the 31st day of any month.

Get Month from Date*

month(InvoiceDate) eq 3

Return rows where the invoice date is in the month of March (of any year).

Get Year from Date*

year(InvoiceDate) eq 2010

Return rows where the invoice date is in 2010

Round*

round(Amount) ge 100

Return rows where the mathematically rounded amount >=100.

For example, the amount of 99.6 will match the filter.

Round Down to Integer*

floor(NumericColumn) eq 5

Return rows where the value of NumericColumn (rounded down to the nearest integer) is equal to five.

Round Up to Integer*

ceiling(NumericColumn) eq 5

Return rows where the value of NumericColumn (rounded up to the nearest integer) is equal to five.