3 Replies Latest reply on Jan 9, 2014 7:59 AM by ourcalling

    Which operator will return both empty and non-empty fields?

    ourcalling

      I have a script that builds various variable and then applys those to a "Perform Find" query.

      The problem I am having is that sometimes the results need to include both empty and non-empty.

       

      For examples, I can search for Gender = "Male" or Gender = "Female" or Gender = "*" or Gender = "=".

      But how can I search for all records when some have values and others are null (meaning the operator never filled in the gender question).

       

      I have the value I want to search in a variable that is passed to the "Perform Find" script command.

      This gets really complicated when I want to search by an age range, gender and other various biographical data.

       

       

      Thoughts?

        • 1. Re: Which operator will return both empty and non-empty fields?
          beverly

          various ideas to ponder (not all inclusive):

           

          1. there is no single 'operator', you must make two requests (new record/request) for this to work.

           

          enter find mode

          set field gender; "Male"

          new record/request

          set field gender; "="

          perform find.

           

          You can test this manually, as well as scripted.

           

          The new record/request turns this into an OR search.

           

          2. In this instance if you ONLY have the three possibilities:

               Male

               Female

               empty

          then you can think "reverse" and find "Male" or empty, by doing a Find "Female" and omit, but that's probably confusing to users manually.

           

          3. There is also the Expand find request, however with multiple fields to search, this could even be more complex.

           

          If you are having user enter values to "search", capture them (into variables, perhaps?) and think about the OR method above. With multiple fields, I'm not sure this would get you accurate results.

           

          4. another idea is the ExecuteSQL function which could do a slick combination of AND with OR in the WHERE clause.

           

          (x=var1 AND y=var2) OR (b=var3 OR b IS NULL) <--- just some examples that may not match your needs

           

          So many possibilities.

           

          Beverly

          • 2. Re: Which operator will return both empty and non-empty fields?
            AlanStirling

            Hi Ourcalling

             

            I think the operator that your searching for - is just plain 0' in your primary key field - this will start by finding all the records - anything you add in terms of find criteria alongside that will just reduce the number of records found.  In this case, a blank field in a find will have no effect, which it the effect I think you are looking for.

             

            Best wishes - Alan Stirling, London UK.

            • 3. Re: Which operator will return both empty and non-empty fields?
              ourcalling

              Alan, I don't understand.

              Are you suggesting the string of characters zero (0)  and apostrophe (') ?

              This doesn't work.