Filter for null or not null

Applications Grid / Reports
#1

Hi all,

I have a column in a grid, some rows are null and some have values in them. How can I create a filter that I can use to either show all null values, or only rows with a value.

I have tried various combinations, but none work 100%. Any ideas?

Thanks
Rob

#2

If you filter by the search options of scriptcase this might be difficult. I wouldn’t know how to do this. But if you do it yourself then showing all values would be WHERE MYVALUE IS NOT NULL and visa versa. If you have empty values and NULL then you need an OR but that’s obvious.

#3

I guess I am lost here, cause my search has the ability to select null and not null records.
I have a grid that I have the ability to select invoices that have null content or not null content.

??

#4

[QUOTE=Kdriscoll;12506]I guess I am lost here, cause my search has the ability to select null and not null records.
I have a grid that I have the ability to select invoices that have null content or not null content.

??[/QUOTE]

Would you be able to share how you have created this? I cannot get it to work.

  • what is the data type of the search field?
  • what is the search criteria of the field?
#5

Under the grid options select Search>Search Criteria> And at the bottom of the list the Null and Not Null is listed.

#6

Sure that works, but how do you clear the filter and display all records again? I am stuck with either null or not null, but I cannot go back to both! Sure I could also put in another condition and allow the user to select that… but this is a hack and isn’t intuitive for the user anymore.

#7

There’s a button clear down under the search form. Clicking this will clear the search argument.

#8

Please don’t patronize me. You clearly haven’t tried this or even attempted to understand what I am asking.

Clearing the search form only clears the arguments, it doesn’t reset the conditions which in this case are ‘null’ or ‘not null’. So the search form is stuck using either ‘null’ or ‘not null’.

I have hacked around this by adding another condition which I am not using elsewhere in my application and then renaming it to ‘all’. This presents the user with ‘all’, ‘null’, ‘not null’. However clicking clear doesn’t reset this to ‘all’, and it’s still a hack.

So my question is still how does SC natively deal with this simple requirement?

#9

It’s our intention to help you, not to patronize you. Sorry if you feel this way, but we are all volunteers and sometimes things are misunderstood. Shit happens now and then.

#10

I agree… I am also trying to help
And the more I see the responses the more I am getting from your responses.
I would suggest using pictures in the future
From what you are now saying it sounds like you have either one search criteria or you have the criteria for the null column searched set as in this order … Null,not null, equal or whatever you entered for all

Move the all up to the top of the select

Hope this helps

By the way… At least you are getting responses, I post many times without any feedback

#11

Rob,

It looks like SC does not have an easy way to set a search field to ‘I do not want to use this as part of the search’. I ran into a similar problem when there are more than one field in the search. They all show up in the WHERE clause, even if the user does not put anything in them.

One suggestion for you. Sort of a hack… but if you all the ‘CONTAINS’ search option in addition to NULL and NOT NULL, then if the user selects the CONTAINS and does not put anything in it, the SQL ends up as LIKE ‘%%’ which will select everything.

Nick