Filtering Generic Inquiries containing blank values

Created by Damien Zwillinger, Modified on Tue, 16 Jul at 9:34 AM by Gabriel Michaud

Applies to:

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



When working with Generic Inquiries, sometimes we want to see those records where a specific field contains no data.  At other times we want to retrieve those records which either contain a specific value OR are empty.

 

Using the null keyword allows us to accomplish this.

 


 

TABLE OF CONTENTS


Examples:

 

Let's assume we have some data from a Generic Inquiry:

 



Example #1 - Showing records with an empty field

Let's see how we can show those lines where the DueDate is empty.

 

First, list the field we want to example and then the value in which we are interested (in this case - null)...



... and then use the GIFILTER function to create the necessary filter:

 


(note that we are using cell references for the parameters in our function)

 

Within our GI function, we reference that filter in cell C8...


... and we are able to retrieve only those rows where the DueDate is blank.


 
Example #2 - show EITHER blank OR matching a value

Sometimes, we want to show not only the blanks, but we *also* want to see those records that match a particular value:

 

Let's assume that we want to retrieve all records for which the DueDate fields is either blank or is prior to 3/10/2013.

 

Again, we list our criteria in the cells:

 


This time, our GIFILTER function will reference the cells in column D and column E:


This creates a filter based on OR (instead of AND).  This filter is looking to retrieve the records where the DueDate is either null *OR* corresponds to the value we are looking for.

 

Now, when our GI function references that filter...

 

... we get all the records where DueDate is blank or where the DueDate is less than the specified date.


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