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.

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.

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:

⚠ 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.

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 be in lowercase:

  • Incorrect: "Active eq FALSE" , "Active eq False"

  • Correct: "Active eq false"

Enquote String Literals

Explicitly enquote all values corresponding to an inquiry column of a string type:

  • Incorrect: "AccountCD eq 10100"

  • Correct: "AccountCD eq '10100'"

Do Not Enquote Non-String Values

Do not enquote 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.,

gi_extra_filters2.png

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

Did this answer your question?