Add EXISTS and NOT EXISTS operands to filter options

Michal shared this idea 4 months ago
Idea Logged

Hi,

When using the filters?: "Different from" and "Not in List" , if the table contain null values, all rows which contain null values in the specific field will be dismissed from the query.

for example, when filtering on the below table, where description not in "Activation fee" ( for example), all the revenue category for "ATM\POS" will be removed from the result.

the reason for it: behind the scene the SQL query will generate the following code:

1. For Different from - Description =! "Activation fee"

2. For "Not in List" - Description not in ("Activation fee")

in both cases the SQL will return false value when compering to the Null description, and then any item which contain null description, will be removed. this is how SQL works..

the solution: implement the query to use "Not Exists" instead of Not in and =!.

/b98d307414bddeb12d4de9bb098216df


/62fdd3617ae69464dfb8d0b2c8951fc6

Comments (3)

photo
0

Hi Michal,

Thanks for reaching out. You can script a Freehand SQL Calculated Field that includes NOT EXISTS in its' query.

For example:

/825fda366c81870194c43b33c81ff565

/0565a126f7cbb848bc7ed2d7f086864d

I'm of course not familiar with your data and use case, but is there a reason utilizing this method wouldn't work for you? If not, I'd be happy to change this to an Idea ticket and submit an enhancement request to add an Exists and Not Exists operand to our filtering. Please let me know.

Regards,

Mike

photo
0

Hi,

It can work for me, but we cannot expect from to user to think about it.

users are using "Not in List" and get non expected results.

moving it to Idea will be great.

Thanks,

Michal

photo
1

Hi Michal,

Thanks for confirming. I've gone ahead and converted this to an Idea ticket and submitted an enhancement request for this.

Any potential updates regarding this will be posted here.

Regards,

Mike

photo
1

Cool, thank you.

photo
1

Hi Michal,

You're welcome!

Regards,

Mike

photo