GIFILTER function

Modified on Fri, 15 Sep 2023 at 01:27 PM

Applies to:

GI Module
ERP: Acumatica, CEGID, Haute x360, JAMIS Prime, MYOB Advanced

Velixo recommends the use of Microsoft 365 or Excel 2021 or higher for this feature. Experimentally, Excel 2010 or higher can be used. For prerequisites and an overview of Generic Inquiries, see Introduction to Generic Inquiries.

TABLE OF CONTENTS

Description
Filtering Done by Acumatica
Syntax
Parameters
Examples
Additional Information and Examples

Description

Given a list of column / criteria pairs, the GIFILTER function produces a valid OData filter clause that can be used as the Filter parameter inside other GI functions such as GI or GILOOKUPF.

The main purpose of this function is to allow the user to use the familiar Excel criteria syntax, such as <>, >=, =, etc. instead of the raw OData syntax expected by Acumatica Generic Inquiries.

In addition to that, GIFILTER also:

Takes care of handling date/time values (converts Excel date/times to OData date/time as needed).
Enquotes values for string-typed GI columns.

Filtering Done by Acumatica

By default, Velixo Reports will apply your filters with Acumatica (before downloading the data), which can significantly reduce the time to load the data into Excel.

As a result, if you experience long load times for inquiries with a very large result set (e.g. GL transactions or invoices), be sure to always supply a GIFILTER() filter when using them in GI() or GILOOKUPF().

Syntax

This is the syntax for this function:

=GIFILTER(Connection, Name, Column, Criteria, [AnotherColumn1], [AnotherCriteria1], ...)

Parameters

This function has the following Parameters:

Parameter	Required/Optional	Description
Connection	Required	The name of the connection, as specified in the Connection Manager
Name	Required	The name of the generic inquiry.
Column	Required	The name of the first inquiry column participating in the filter. (note: this *cannot* be any formula-type column in the GI [i.e.,that is based on a formula with = ] )
Criteria	Required	A single value, an array of values, or a range of cells containing the criteria for the first column. Each value should contain an Excel comparison operator (<>, >, <, =, >=, <=), followed by a criteria value for the column, for example, ">=42". If the operator is not explicitly specified, equality operator "=" is assumed. In case this parameter is an array or a range of cells, each of those will be considered an alternative, and will be joined via a logical "OR" as part of a bracketed group.
AnotherColumn1	Optional	Additional generic inquiry columns to participate in the filter, along with the corresponding criteria. Each of those will be appended to the filter clause via a logical "AND". ? You can specify the same inquiry column multiple times in the filter if you wish to restrict it using multiple conditions that should hold simultaneously.
AnotherCriteria1	Optional
...	...
AnotherColumnN	Optional
AnotherCriteriaN	Optional

Filtering Large Generic Inquiry Datasets

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.

Examples

Example 1

=GIFILTER("Demo", "AR-Invoices and Memos", "Customer", C8:C10)

Description: gets a valid OData filter expression that can be used to filter the "AR-Invoices and Memos" generic inquiry. The resulting filter will only include documents from customers whose identifier matches one of the values inside the cell range C8:C10.

Result:

Suppose that the range C8:C10 contains customer codes "ABARTENDE", "TOYSTAR" and "ABCSTUDIOS". In this case, the result would be:

(Customer eq 'ABARTENDE' or Customer eq 'TOYSTAR' or Customer eq 'ABCSTUDIOS')

Example 2

=GIFILTER("Demo", "AR-Invoices and Memos", "Date", ">=2015-01-01", "Date", "<=2015-12-31", "Type", { "Credit WO", "Credit Memo" })

Description: gets a valid OData filter expression that can be used to filter the "AR-Invoices and Memos" generic inquiry. The resulting filter will only include credit memos or credit write-offs from the year 2015.

Result:

Date ge 2015-01-01 and Date le 2015-12-31 and (Type eq 'Credit WO' or Type eq 'Credit Memo')

Smart Expand feature

GIFILTER() supports wildcards to match the beginning, the end, and the body of a text strings.

On versions of Acumatica prior to 2021R1 Update 16 and 2021R2 Update 4, the use of Smart Expand feature requires you to set up GI filtering on your computer instead of the server.

When all of the following are true:

There is no explicit operator (=, <>, >=, etc.) contained in the criteria value
The General Inquiry column being searched is of OData type (not date/time, number, etc)
The criteria string is in one of the following formats, note the wildcard asterisk symbol:
- beginning*
- *ending
- *body*

GIFILTER will automatically replace the criteria with:

Criteria	Smart Expand
`beginning*`	`startswith(ColumnName, 'beginning')`
`*ending`	`endswith(ColumnName, 'ending')`
`body`	`contains(ColumnName, 'body')`
*other wildcard configurations (such as headtail) are not supported**

Smart Expand Example 1

=GIFILTER("Demo","AR-Invoices and Memos","CustomerName","*USA*")

Description:

A wildcard * at both the beginning and end of the criteria denotes that the CustomerName field must CONTAIN the string USA.

Result:

Smart Expand Example 2

=GIFILTER("Demo","AR-Invoices and Memos","Type","*Memo")

Description:

The wildcard * at the beginning of the criteria denotes that the Type field must END WITH the word Memo

Result:

Additional Information and Examples

Accommodating GI filters in Acumatica

Acumatica Generic Inquiries can include pre-defined parameters which are used within Acumatica to filter the results of the inquiry:

e.g.,

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 either (a) removing the parameters or (b) making them optional by ensuring that the conditions in your GI will return the full dataset when the parameters are not specified.