Filtering Generic Inquiries

Overview

This article describes filtering methods you can apply to Acumatica Generic Inquiries to focus your report on desired records. In most cases, we recommend building your filter with GIFILTER and passing the result into GI or GILOOKUPF functions.

Such filters are easier to maintain than writing raw expressions by hand, especially when you need multiple criteria or optional filters.

Most common filtering scenarios

Filter by an exact value

Use equality when you want rows that match one value exactly.

AccountCD eq '10100'

Filter by multiple conditions

Combine conditions with and or or.

• AccountCD eq '10100' and CustomerCD eq 'ABARTENDE'

• AccountCD eq '10100' or AccountCD eq '10200'

Filter by dates

Date and date-time values must be formatted as text in the OData expression.

• InvoiceDate ge 2010-01-01T00:00:00Z

• InvoiceDate lt 2011-01-01T00:00:00Z

Filter blank or non-blank values

Use the lowercase keyword null to filter empty values.

• CustomerNbr eq null — returns rows where the field is blank

• CustomerNbr ne null — returns rows where the field is not blank

This example shows filtering of the following GI data to return lines where the EndDate is empty

First, create a GIFILTER formula:

Run a GI formula that leverages the created filter:

Combine blank and value conditions with OR

To return rows where a field is blank or matches another condition, build an OR filter with GIFILTER. Reference the column name in two separate column parameters within the GIFILTER call — one with the null criteria and one with the value criteria. When GIFILTER receives two column references for the same field, it produces an or condition instead of and.

For example, to retrieve records where EndDate is blank or is earlier than a specific date, list the EndDate field twice in GIFILTER — once with null as the criteria and once with the date value.

List the field and the desired value in a horizontal array and create a GIFILTER formula based on these fields:

Run a GI formula that leverages the created filter:

Use optional filters

If you want a criterion to be optional, use an asterisk * in place of the criterion value in GIFILTER. Velixo ignores any criterion set to *, which allows you to keep one reusable formula instead of rewriting the filter each time.

Filter by financial period

Financial period fields require special care because the expected format depends on the Acumatica version.

Determine the correct format

Filter a range of financial periods

Do not use simple ge and le comparisons on financial period strings. Financial periods are treated as text, not dates, so such comparisons return incorrect results. For example, filtering between 022019 and 052019 would also include 032013, 042016, and other unrelated periods.

Instead, generate the list of periods you need and build a filter from that list:

1. Use FINANCIALPERIODLIST to generate the periods between a start and end period.

   

2. Remove dashes from the output using Excel's SUBSTITUTE() function: SUBSTITUTE(FINANCIALPERIODLIST(...), "-", "")

3. Pass the resulting list into GIFILTER. Use the # spill-range operator at the end of the cell reference (for example, A1#) to pass all values from the spilled range.

4. You can combine the period list with other filter criteria in the same GIFILTER call — for example, adding a non-invoice filter alongside the period list.

   

   
   

   

   
   

Filter on custom attributes

You can include custom attributes in a Generic Inquiry result set and report them with GI. However, filtering on those fields has one common pitfall, where the GI output may display the attribute Description, but OData filtering expects the Value ID.

For example, if the displayed attribute value is 1-100, the filter may need to use 100 instead, depending on how the attribute values are defined in Acumatica.

If a filter on a custom attribute returns no results, verify whether you used the Value ID (not the displayed value).

OData filter reference sheet

Below are common operators and functions used in the OData filter clause. For the full specification, consult the OData v4 URL conventions standard.

Comparison operators

String functions*

Date functions*

Numeric functions*

Troubleshoot filtering

Common problems to avoid

• Use the exact column casing. If the GI column is CustomerID, then customerid can fail.

• Use lowercase boolean values. Write true or false, or use 1 and 0.

• Use quotes with literal string values. Example: AccountCD eq '10100'.

• Do not quote numeric values. Example: AccountID eq 123.

• Convert Excel dates to text. Raw Excel serial values are not valid OData date literals. Use Excel's TEXT() function with a format like "yyyy-mm-dd".

• Handle quotes carefully when concatenating. String values need single quotes inside the final filter text: "AccountCD eq '"&C5&"'".

• Match date types correctly. A DateTimeOffset column requires a value like 2010-01-01T00:00:00Z. Alternatively, cast the column: cast(InvoiceDate, Edm.Date) gt 2010-01-01.

• Avoid predefined GI parameters. Acumatica's OData interface does not support this filtering pattern. Remove GI parameters or make them optional so the GI returns the full data set when parameters are not specified.

  

• Do not filter on calculated GI fields. Calculated fields (those computed with a formula starting with =) can be reported but cannot be used in OData filters.

Problems, likely causes, and resolutions