Add EXISTS and NOT EXISTS operands to filter options

Michal shared this idea 2 years ago
In Progress

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
1

Hi Mike,

Any update on the enhancement request. I am facing the same issue.

I have one field where i do have null values and my customer wants that field to be used as a filter and blank value should also be one of the option in list of values for that filter.

/b88a97fb170285a1a3c8649de165c8dc

1. When i select that blank value from filter, it does not filter out anything, since it trying to do direct null comparison which is not possible.

/6ccf7272c8aed2951515ff79a0aa72c8

2. If i used a calc field and assign certain text to blank value like 'Blank' then it will work.

/b3568cb23afc52c8e970cfe77b060b91


But we are not sure if customer would agree for 2 option.


Could you please help us here.

Regards,

Abhishek Gangrade

+1-908-699-1912

photo