Portal Categories
Articles in this folder
You may like to read

GIFILTER function

Created by Harry Lewis, Modified on Tue, 16 Jul, 2024 at 9:34 AM by Gabriel Michaud

Applies to:

  • GI Module
  • ERP: Acumatica, CEGID, Haufe x360, JAMIS Prime, MYOB Acumatica
 

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

 

 


 Important

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" })

escription: 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')

 

 


Example 3 - filter is longer than allowed data


Description:  Assuming that a segmented key is defined within the ERP with length 10


 

and a value is defined such as:


ABCSTUDIOS  (length of 10 characters)


It is possible to use a GIFILTER expression with more than 10 characters (such as ABCSTUDIOS123456)..


In this situation, everything after the 10th character will be ignored by the ERP (thus, the ERP will consider this to be ABCSTUDIOS)


Result:


Even though ABCSTUDIOS123456 does not exist in the ERP, the ERP will still return the dataset as if you specified the correct value of ABCSTUDIOS.




 


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

Feedback sent

We appreciate your effort and will try to fix the article

Preview
0 of 0