The recommended way to construct the filter argument for GI or GILOOKUPF 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
(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')||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.
* - may not be available with Velixo Reports v5 with Cache Mode = Filter Specific on Acumatica 2020R1 and earlier.
⚠ Velixo Reports GI functions fully support the OData v4 filter syntax. However, Acumatica ERP 2020 R1 and earlier only support v3 of the OData standard, and even that with certain limitations. Because of that, with Velixo Reports v5, some of the operators and functions will not work if the caching mode is set to Filter Specific in the Generic Inquiry Options for a given generic inquiry. The reason is that with Filter Specific caching, the filter clause is passed to the Acumatica server, whereas using Full Table caching the filters are applied locally.
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:
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