4 Replies Latest reply on Jun 26, 2017 9:16 AM by David_McKee

    FM 15A - NULL Data Question

    ron.harris

      FM 15A - I use MSSQL DB TABLE's via ODBC.  I'm trying to load a layout with records where a specific field is null (i.e. MSSQL equivalent: select [field list] from [table] where [specific field] is null).  I've tried a many various "trial and error" methods to achieve this but have failed miserably.  Any help would be appreciated.

       

      Thank you,

      Ron Harris

        • 1. Re: FM 15A - NULL Data Question
          David_McKee

          FileMaker does not have a distinct concept of NULL.   It uses NULL, "empty" and 0 interchangeably in cases and inconsistently in others.

           

          However, you can FIND for empty by doing  "==" (minus the ""s) in the field in question and performing the find, this will produce all records where that field doesn't have a value (empty).   Via other methods (like relationships) you might need to create a calculated field that results in "1" if the field(s) in question is empty, otherwise "0", then join on all the 1 records.

           

          Hope this helps!

          3 of 3 people found this helpful
          • 2. Re: FM 15A - NULL Data Question
            ron.harris

            Thank you.  This worked well.  One method I tried was ="" and another was =="".  == by itself worked (I'll be darned!)

            • 3. Re: FM 15A - NULL Data Question
              Malcolm

              David,

               

              Will we get the same result using a single equal sign? Or does the double equal sign add something extra to the mix?

               

              Malcolm

              • 4. Re: FM 15A - NULL Data Question
                David_McKee

                While = will work too, for this specific case,  == is the operator you generally want to use when you are using criteria that should apply for the entire contents of the field.  It's just a good habit to always think in that way. 

                 

                Otherwise, you might find yourself using "=c" rather than "==c" when you are looking for only records that have "c" in them, etc.   ( The former will actually find records with "a c" (etc) in them too, while the latter will only find records with "c". )

                1 of 1 people found this helpful