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

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 head*tail) 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:

 

contains.png

 
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:

 

endswith.png

 

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

gi_extra_filters2.png

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.

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 atleast one of the reasons
CAPTCHA verification is required.

Feedback sent

We appreciate your effort and will try to fix the article