Advanced filtering in Generic Inquiries

Created by Damien Zwillinger, Modified on Tue, 26 Mar 2024 at 06:21 PM by Harry Lewis

Applies to:

Velixo NX
Velixo Classic
Acumatica, Cegid, MYOB

The recommended way to construct the Filter parameter for either the GI or GILOOKUPF function is by using the GIFILTER helper function (which allows you to specify multiple filtering criteria using the Excel syntax, rather than the raw OData syntax).

However, you may prefer to use the OData syntax directly in more advanced scenarios.

TABLE OF CONTENTS

OData Filter Syntax Cheat Sheet
Avoiding Common Pitfalls
Using optional filters

OData Filter Syntax Cheat Sheet

Below you can find some of the common operators and functions used in the OData filter clause. For more details, consult the URL conventions of the OData standard.

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.

* Using the functions marked with an asterisk requires:

Acumatica 2021R1 Update 16 or higher
Acumatica 2021R2 Update 4 or higher
On other Acumatica versions, to use the marked functions, you will need to set up filtering on your computer instead of server-side (incurs performance penalty for large inquiries).

⚠ Velixo Reports GI functions fully support the OData v4 filter syntax. However, Acumatica ERP's OData interface for Generic Inquiries only supports the version 3 the standard, and even that with certain limitations. Because of that, with Velixo Reports, some of the operators and functions will not work unless local filtering is enabled in the Generic Inquiry Options for a given generic inquiry.

** Available with Velixo version 7.0151 and Velixo NX version 0.15.582 and higher

Avoiding Common Pitfalls

When directly using the OData filtering syntax, you need to take extra care to avoid some of the common pitfalls, especially regarding coalescing Excel values into the format expected by the OData endpoint.

In case you encounter any errors or unexpected results, make sure that you are aware of each of the following.

Always using Filtering with large GI data sets

If you experience long load times for inquiries with a very large result set [e.g. GL transactions or invoices], make sure to always supply a GIFILTER() filter when using the other GI-related functions with such inquiries. Also verify that it doesn't involve ODATA functions unsupported by Acumatica [such as startswith() or toupper()]. This will allow Velixo Reports to be able to apply your filters on the server [before downloading the data] which can significantly reduce the load time.

Use Proper Casing for All Column Names

If a column is named CustomerID in a generic inquiry, trying to filter by customerid will produce an error.

Boolean values need to be lowercase

Boolean (true/false) filter values need be in lowercase:

Incorrect: "Active eq FALSE" , "Active eq False"
Correct: "Active eq false"

Additional Note: With Velixo NX, do not use non-Boolean values (e.g., 0, 1, -1) for Boolean filters. Use only true or false.

Include quotes with String Literals

Explicitly place quote around all values corresponding to an inquiry column of a string type:

Incorrect: "AccountCD eq 10100"
Correct: "AccountCD eq '10100'"

Do Not quote Non-String Values

Do not place quotes around values corresponding to an inquiry column of a numeric or a datetime type:

Incorrect: "AccountID eq '123'"
Correct: "AccountID eq 123"

Do Not Directly Use Excel Dates in Filter Clause

You must convert Excel date/time values to text before using them in the OData filter clause.

The reason for that is that internally, Excel date/time values are just numbers. Unless you convert this value to text, the filter "LastModifiedDateTime eq "&C8 (where C8 stores an Excel date) will produce a value like "LastModifiedDateTime eq 123456 , which will produce an error.

To remedy this, you may use the TEXT function to convert an Excel date/time value to a string. Make sure to pass the formatting parameter like "yyyy-mm-dd" into it: the OData filter syntax expects date literals to be in either the yyyy-mm-dd (date), or yyyy-MM-ddThh:mm:ssZ (date/time offset) format.

Take Extra Care of Quotation Marks when Concatenating with Cell References

It is very easy to forget string literal quotes expected by OData when concatenating multiple values:

Incorrect: "AccountCD eq "&C5
Correct: "AccountCD eq '"&C5&"'" (note the single quotes encompassing the cell reference).

Understand the Difference between Date and DateTimeOffset

Often, you might experience errors when you write a filter like "InvoiceDate gt 2010-01-01".

This is because InvoiceDate is likely marked by Acumatica to be of the Edm.DateTimeOffset type, and 2010-01-01 is an Edm.Date literal. To remedy this problem, you can:

either use an Edm.DateTimeOffset literal: "InvoiceDate gt 2010-01-01T00:00:00Z",
or strip the timezone from the offset type: "cast(InvoiceDate, Edm.Date) gt 2010-01-01".

Do not use pre-defined parameters in your Generic Inquiry

Acumatica Generic Inquiries can include pre-defined parameters which are used within Acumatica to filter the results of the inquiry:

e.g.,

Acumatica's OData interface (used by Velixo Reports for querying the GI) does not support this type of filtering. To be compatible with your Velixo report, you would need to modify your GI by one of the following:

Removing the parameters, or
Making them optional by ensuring that the conditions in your GI will return the full dataset if the parameters are not specified

Acumatica does not support filtering on "calculated" fields.

If the field is calculated as part of the inquiry (i.e., is computed with a formula starting

with = ), it can be reported but cannot be used in a filter for that inquiry.

Using optional filters

In certain cases, you may want to apply certain filters while, in other case, you do not.

Using an asterisk ( * ) for the criteria of your filter can accomplish this.

For example, let's assume that sometimes we want to filter our inquiry by the PostPeriod, and sometimes we do not.

When we want to apply the filter, we can enter a value for the criteria:

When we do not want to use that field, we can use * as the criteria:

In this way, we do not need to change the GIFILTER function, the function will simply ignore any filter with * as the criteria.