Ask Your Question
0

How to show null in filter table

asked 2020-07-08 03:08:30 +0100

gkick gravatar image

updated 2020-07-08 10:24:59 +0100

Alex Kemp gravatar image

Hi all,

Ok, one can show a selection or <all> of a listbox where all is basically a blank in the filter table. Just curious if it is at all possible to show records where the field actually is null ? Thank you for your thoughts.

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2020-07-08 03:35:11 +0100

Ratslinger gravatar image

updated 2020-07-08 07:07:39 +0100

Hello,

Not quite certain what you are asking. Checking a NULL in a filter table or in a field is the same. See SQL and sample in this post -> Base: Conditional Query. How to filter with a ListBox and add ALL?

Your SQL just needs :

WHERE "My_Field" IS NULL

Edit:

Based on further explanation in comment, you can do this with a second added control. You original list box remains as is. The second just needs to be a true/false condition in another field in the Filter Table. In my testing I used an on/off check box - here named SelAll.

With this SQL statement, where the condition is in a field named Fname there are three choices:

SELECT "Table".*  FROM "Table", "FILTER" WHERE
   ("FILTER"."SelAll" = TRUE AND  "FILTER"."Fname" IS NULL) OR
   ("FILTER"."SelAll" = FALSE AND "FILTER"."Fname" IS NULL AND "Table"."Fname" IS NULL) OR
   (NOT "FILTER"."Fname" IS NULL AND "FILTER"."Fname" = "Table"."Fname")

If SelAll is true & Fname is null, all records are selected.

If SelAll is false & Fname is null, only records with NULL Fname are selected.

If SelAll is true OR false & Fname is NOT null, only records equal to Fname are selected.

This was tested with embedded HSQL and should work with v2.5 as you requested.

edit flag offensive delete link more

Comments

@Ratslinger

Well, sory for being not clear, in a listbox I want to show options for display all, or selected choice or only those records where the field is blank, so I might have Peter, Paul and Mary showing up as all, only Peter if Peter is selected or only a blank where Mary has no fistname entry. Hope that makes sense.

I somehow think the sql for the listbox would need some sort of union statement

gkick gravatar imagegkick ( 2020-07-08 04:16:03 +0100 )edit

@gkick,

Yes, a much better explanation. Can easily see doing this with a macro (have done similar and more complex for personal use). Would like to try and do this with just SQL but you have not stated what database you are using. This is important in this situation.

Ratslinger gravatar imageRatslinger ( 2020-07-08 04:54:04 +0100 )edit

@Ratslinger sorry Lo 6.3 Win 10 backend HSQL 2.5

gkick gravatar imagegkick ( 2020-07-08 05:11:25 +0100 )edit

@gkick,

Please see edited answer for solution.

Ratslinger gravatar imageRatslinger ( 2020-07-08 06:59:09 +0100 )edit

@Ratlinger Thank you very much, you are a rel gem!

gkick gravatar imagegkick ( 2020-07-08 07:10:53 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2020-07-08 03:08:30 +0100

Seen: 56 times

Last updated: Jul 08 '20