Generic Inquiry Filters Troubleshooting

Applies to:

• Velixo NX
• Velixo Classic
• Acumatica, Cegid, MYOB

Overview

This page is intended to help you troubleshoot the most frequent GI filtering errors you might encounter while using Velixo Reports.

• First, locate the situation or the error message that you're observing.

• Then, use one of the outlined solutions to resolve the error.

TABLE OF CONTENTS

• Overview
• Symptom #1
  • Likely root cause
  • Explanation
  • Possible Solution 1
  • Possible Solution 2
• Symptom #2
  • Likely root cause
  • Explanation
  • Possible Solution 1
    • Break down the list of allowed values into intervals
    • Delegate part of the OData filter to Excel
  • Possible Solution 2
• Symptom #3
  • Likely root cause
  • Solution
• Symptom #4
  • Likely root cause
  • Solution

Symptom #1

You receive error message: Acumatica could not recognize the OData syntax

Likely root cause

You are using an OData v4 function or expression that is not supported by Acumatica server-side.

Explanation

This is a relatively frequent error that stems from the following two facts:

• Velixo intends to comply with the OData v4 standard, while the Generic Inquiry endpoints in older versions of Acumatica only support OData v3 (and even of that, only a certain subset of the standard).

• At the same time, to avoid downloading too much data unless necessary, Velixo optimistically pushes your OData filter expressions to the server by default.

This makes the users encounter this error whenever they try to invoke such functions as tolower(), startswith(), or contains(), or another unsupported expression, in their OData filter with older versions of Acumatica.

Note that it also applies to the usage of wildcard symbols ("*") in GIFILTER() because it is effectively translated into a startswith() / endswith() / contains() call; check the output of your GIFILTER() call if you're unsure.

The startswith() and endswith() functions are supported by:

• Acumatica 2021R1 Update 16 (or higher)

• Acumatica 2021R2 Update 4 (or higher)

It is also possible to use the contains() function with these versions of Acumatica when using Velixo version 7.0.151 (or higher).

Otherwise, consult this cheat sheet to understand whether you're trying to invoke any of the unsupported OData v4 functions.

If you don't think any unsupported functions are used, but still see this error, please be informed that there are other slight syntax and type system differences between OData v3 and v4. 

Therefore, this error may be a result of using an expression (e.g. a type cast) that is supported by OData v4 only. Consult the OData standards for more details.

Possible Solution 1

The preferred solution is to avoid using unsupported functions or expressions in your OData query.

In particular, instead of doing a startswith() call, you could use the comparison operators.

 For example, if you want all subaccounts that start with "CON", then instead of writing:

startswith(Subaccount, 'CON')

you can say:

Subaccount ge 'CON' and Subaccount lt 'COO'

Possible Solution 2

The last resort solution is to enable Velixo to download the whole inquiry result set to the client before applying the filters. See this article for details on how to do this.

Please be advised that enabling client-side filtering comes with a performance penalty. Indeed, technically, you will be able to use most of the OData v4 syntax, expressions, and functions; however, it can result in Velixo trying to download a prohibitive amount of data from the server, especially if your inquiry looks at rapidly flowing transactional data, such as GL lines or invoices.

Symptom #2

You get error message: Acumatica returned a 'Not found' error while loading the data

Likely root cause

Your OData filtering expression might be too long - most likely, you are using too big a range in your GIFILTER() call.

Explanation

• The OData standard requires all filters to be included in the request URL directly (a GET request).

• At the same time, Acumatica's default limit for request URL length is 2048 characters, and can be tweaked by the systems administrator to a different value.

Therefore, Acumatica will refuse to serve data to Velixo when you supply too big a range to one of your GIFILTER() calls.

To verify, measure the length of GIFILTER()'s result in a tool like https://www.charactercountonline.com/ . If it's anywhere near 2000 symbols, this is definitely a problem of the filter being too long.

Possible Solution 1

The preferred solution is to reduce the length of the filters that Velixo pushes to Acumatica. There are two common solutions of this type, described below.

Break down the list of allowed values into intervals

For situations where you filter on a long list of allowed values, e.g.:

Column eq 'AA' or Column eq 'BA' or Column eq 'CA' ... or Column eq 'ZA' 

you may sometimes be able to replace the filter with:

Column ge 'AA' and Column lt 'ZB' 

A similar solution can be applied to numeric values.

As a general rule, try to break the list of allowed values down into intervals that you can cover with >= (gt) and < (le). That would likely result in a much shorter filter than a long list of "equals this or equals that".

Delegate part of the OData filter to Excel

In addition to the above, you can also discard some of the longest parts of your OData filter expression and use an Excel filter instead - a FILTER() function or an auto-filter.

Relaxing your OData filters would of course, result in more data being loaded from Acumatica. However, in most cases it is much more performant than discarding the server-side filters altogether.

Possible Solution 2

The last resort solution is to enable Velixo to download the whole inquiry result set to the client before applying the filters. See this article for details on how to do this.

Please be advised that enabling client-side filtering comes with a performance penalty. Indeed, technically, you will be able to use most of the OData v4 syntax, expressions, and functions; however, it can result in Velixo trying to download a prohibitive amount of data from the server, especially if your inquiry looks at rapidly flowing transactional data, such as GL lines or invoices.

Symptom #3

Filtering on a custom attribute yields no results

Likely root cause

You may be filtering on the Description column of the attribute (which is returned by the GI() call), whereas Acumatica expects you to specify the "Value ID" values in the OData filters for custom attributes.

Solution

See GI: Custom Attributes and Filtering on Custom Attributes (pitfalls section) for more detail.

Symptom #4

Filtering on a financial period column does not return any results

or

Filtering on a financial period column stopped return results after upgrading Acumatica from a pre-2020R2 version

Likely root cause

You might be using an incorrect format for the financial periods. The expected financial period filter format was changed in Acumatica starting from 2020R2, which means you might need to change your formulas to accommodate the new format.

Solution

See GI: Filtering on Financial Period IDs for details on which financial period format you should use.