Announcement

Collapse
No announcement yet.

Filter for null or not null

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Filter for null or not null

    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.
    Albert Drent
    aducom software netherlands
    scriptcase partner, reseller, support and (turn-key) development
    www.scriptcase.eu / www.scriptcase.nl

    Comment


    • #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.

      ??

      Comment


      • #4
        Originally posted by Kdriscoll View Post
        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.

        ??
        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?

        Comment


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

          Comment


          • #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.

            Comment


            • #7
              There's a button clear down under the search form. Clicking this will clear the search argument.
              Albert Drent
              aducom software netherlands
              scriptcase partner, reseller, support and (turn-key) development
              www.scriptcase.eu / www.scriptcase.nl

              Comment


              • #8
                Originally posted by aducom View Post
                There's a button clear down under the search form. Clicking this will clear the search argument.
                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?

                Comment


                • #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.
                  Albert Drent
                  aducom software netherlands
                  scriptcase partner, reseller, support and (turn-key) development
                  www.scriptcase.eu / www.scriptcase.nl

                  Comment


                  • #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

                    Comment


                    • #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

                      Comment

                      Working...
                      X