Filtering Generic Inquiries containing blank values

Created by Damien Zwillinger, Modified on Tue, 09 Apr 2024 at 06:25 PM by Harry Lewis

Applies to:

Velixo Classic
Velixo NX
Acumatica, MYOB Advanced, 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:
- Example #1 - Showing records with an empty field
- Example #2 - show EITHER blank OR matching a value

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.