Advanced filtering in Generic Inquiries

Created by Harry Lewis, Modified on Mon, 15 Jul at 5:55 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

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

  • To use the marked functions with older versions for Acumatica, you will need to set up filtering on your computer instead of server-side (incurs performance penalty for large inquiries).


⚠ The Velixo GI functions fully support the OData v4 filter syntax.  However, Acumatica ERP's OData interface for Generic Inquiries only supports version 3 of the standard (and even that with certain limitations).  Because of that, 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.0.151 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 or use 0 or 1:

 

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

  • Correct: "Active eq false"

  • Correct: "Active eq true"

  • Correct: "Active eq 1"

  • Correct: "Active eq 0"



 


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 43210, 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.


Using if the GIFILTER function can be a great help in ensuring that your filters are in the correct 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 Classic and Velixo NX 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?

That’s Great!

Thank you for your feedback

Sorry! We couldn't be helpful

Thank you for your feedback

Let us know how can we improve this article!

Select at least one of the reasons
CAPTCHA verification is required.

Feedback sent

We appreciate your effort and will try to fix the article