Advanced filtering in Generic Inquiries
Created by Damien Zwillinger, Modified on Thu, 05 Oct 2023 at 05:50 PM by Gabriel Michaud
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
- Always using Filtering with large GI data sets
- Use Proper Casing for All Column Names
- Boolean values need to be lowercase
- Enquote String Literals
- Do Not Enquote Non-String Values
- Do Not Directly Use Excel Dates in Filter Clause
- Take Extra Care of Quotation Marks when Concatenating with Cell References
- Understand the Difference between Date and DateTimeOffset
- Do not use pre-defined parameters in your Generic Inquiry
- Acumatica does not support filtering on "calculated" fields.
- 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.
Example Filter Clause
AccountCD eq '10100'
Return rows where the account code matches "10100".
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"
AccountCD eq '10100' and CustomerCD eq 'ABARTENDE'
Return rows where the account code is "10100" and the customer code is "ABARTENDE"
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)
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
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
Return rows where document type ends with "memo"
Return rows where the customer name starts with "John"
contains(Description, 'overdue') eq true
Return rows whose description column contains the text "overdue"
length(CustomerName gt 20)
Return rows where customer name is longer than 20 symbols
indexof(CustomerName, 'John') gt 0
Return rows where the customer name contains "John" but does not begin with "John".
length(replace(Description, ' ', '')) lt 10
Return rows where the description, in which all whitespace was removed, has a length less than ten symbols.
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(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(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(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 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
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.
Was this article helpful?
Thank you for your feedback
Sorry! We couldn't be helpful
Thank you for your feedback
We appreciate your effort and will try to fix the article