Add EXISTS and NOT EXISTS operands to filter options
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 =!.
I like this idea 
Hi Michal,
Thanks for reaching out. You can script a Freehand SQL Calculated Field that includes NOT EXISTS in its' query.
For example:
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
Hi Michal,
Thanks for reaching out. You can script a Freehand SQL Calculated Field that includes NOT EXISTS in its' query.
For example:
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
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
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
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
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
Replies have been locked on this page!