Number' datatype for field: null, 0, and multiple values

[Justin Close]

Some observations from a remote toadstool...I was doing some testing on fields and things and ran across some apparent oddities (odd to me at least).  The field was set to 'Number' type:

 

If the field is empty (null, whatever), it still evaluates to zero (i.e.   field = 0?  YES ).  It doesn't seem like it should do this, i.e. NULL ≠ 0. 

 

You can add multiple lines to the field IF you are using a checkbox set.  So, create a checkbox set with value list "0¶1", check them both and the data in the field is "0¶1".  Now, I can't TYPE that data into the field (if it were a plain edit box - I can't hit 'return' and get a CR that is; I can hit option-7 to get a pillcrow).

 

Oh, just noticed, too, that if you type in some data in an edit box that data will remain there when using checkbox sets.  I guess this makes sense, but still seems a bit odd:  checkboxes just add remove the given value from the field, regardless of what is there.  Radio buttons properly obliterate existing values, of course.

 

More noticing:  if using a checkbox set a "0¶1" DOES equal 1, but "1¶0" does NOT.  Whilst the opposite is NOT the case:  ""0¶1" does NOT equal 0, NOR does "1¶0" equal 0.  (Pillcrows here are used to represent a carriage return, char(13), NOT the actual pillcrow character, char(182).  A CR/char(13) is what you get when using checkboxes.)

 

 

--  J

[comment]

If the field is empty (null, whatever), it still evaluates to zero (i.e.   field = 0?  YES ).  It doesn't seem like it should do this, i.e. NULL ≠ 0.

 

I wouldn't say that an empty field always evaluates to zero. For example:

Exact ( EmptyNumberfield  ; 0 )

is False.

 

However, keep in mind that an empty field evaluates to False in a Boolean test, and so does 0.

 

 

I didn't understand the rest of your post.

[Wim Decorte]

 

You can add multiple lines to the field IF you are using a checkbox set.  So, create a checkbox set with value list "0¶1", check them both and the data in the field is "0¶1".  Now, I can't TYPE that data into the field (if it were a plain edit box - I can't hit 'return' and get a CR that is; I can hit option-7 to get a pillcrow).

 

Oh, just noticed, too, that if you type in some data in an edit box that data will remain there when using checkbox sets.  I guess this makes sense, but still seems a bit odd:  checkboxes just add remove the given value from the field, regardless of what is there.  Radio buttons properly obliterate existing values, of course.

 

More noticing:  if using a checkbox set a "0¶1" DOES equal 1, but "1¶0" does NOT.  Whilst the opposite is NOT the case:  ""0¶1" does NOT equal 0, NOR does "1¶0" equal 0.  (Pillcrows here are used to represent a carriage return, char(13), NOT the actual pillcrow character, char(182).  A CR/char(13) is what you get when using checkboxes.)

 

 

--  J

 

Not quite sure what you intend to convey with these findings.  None of these are surprising. Is there a question in there?

 

You can't expect a multi field that contains "1 and 0" to easily be translated as true or false (single value boolean).  If you try FM will try its best to come up with a boolean for you but that result is very implicit.  As a developer you have to take control and test for multiple values and not let FM try and translate that into a boolean, or implement your own translation rules.

 

Checkboxes are meant to store multiple values in field so they will add / remove values as provided from the value list but leave other values alone.  Radio buttons by nature will only store the selected value.  Nothing surprising there.

[LaRetta]

if using a checkbox set a "0¶1" DOES equal 1, but "1¶0" does NOT. 

 

It is because you are using a number field which does not recognise the pilcrow.  To FM,  "0¶1" is same as 01 which is 1 but "1¶0" is seen as 10.  

 

Try this:  checkboxField > 9.  It will be true.  ;-)

 

It is very good to question everything and work through the nuances you observe, Justin; good for you!

[LaRetta]

As a developer you have to take control and test for multiple values and not let FM try and translate that into a boolean, or implement your own translation rules.

 

 

Yes, I also wanted to mention this, Justin:

 

An easy way is set this up is to have a value list named Select with only a 1 as the value.  Below I'll suggest including zeros in the field but still ... only the 1 will show to Users in their checkbox.  Now the fun part ...  really :-)

 

You can guarantee easily that a boolean stays clean.  What I mean is that multilines, spaces, carriage returns, text, even valid number characters such as decimal, dollar and minus can be stripped even if set via script by accident, copy/pasted by a User, or imported in error.

 

Set your boolean field is with auto-enter (replace) of:  GetAsBoolean ( Self ) and below uncheck the 'do not evaluate if all referenced fields are empty"

 

New records inherit 0.  If the developer accidentally types 0¶1 or types $23.45, it will change to 1.  If you wish to apply a *null condition then leave checked the 'do not evaluate'  and also in auto-enter check the Data checkbox but put nothing in it.  New records will fire the Data auto-enter in place of the calculation and the calc will not evaluate but an existing record will respond with 1 and 0 if the field is changed.

 

*Null state:  have the field begin life as empty but once User sets it to 1 then unchecks it again, changes to 0 then 1 from then on.

Oh, and the main reason I like zeros instead of empty (other than Null option) is because I can then use the layout-level number format of boolean.  ;-)

 

edited> I just tested and we can leave checked 'do not evaluate'.  I know when testing this several years back, it was necessary.  Either I had it wrong there or the behaviour changed through the versions.  Regardless, ignore the portion I crossed out please.  :-)

Edited January 31, 2014 by LaRetta

[LaRetta]

I just realised that, if I use Data checkbox all the time, setting it to 1 or 0 or empty (depending upon the business rule) that it means we can leave checked 'do not evaluate'.  This would save some needless evaluations I believe and add consistency.  I love working through things like this!!

 

I set booleans so that the User action against the field is NOT the norm, naming them as:  isActive1AE (meaning that Active is 1 and it is auto-entered) and must be unchecked or isPostedAE (meaning records are created null and must be posted to 1 so that we can track the null state).  

[Justin Close]

@Comment:  The formula I was using to check with was:  If ( data = 0 ; "Yes" ; "No" ).  I haven't used Exact() before; looks interesting.  As for the rest of it, was just commenting on seemingly strange behavior with a number field:  in an edit box you can't type multiple lines the same way you can in a text field.  But if using checkbox sets, you can get multiple lines.  Also how FM treats those multiple lines when retrieved.

 

It looks like LaRetta has the right of it:  FM ignores non-numeric characters in a number-type field, so the "1n0" becomes the number ten, while "0n1" becomes the number one.  Thanks for helping me understand that better, LaRetta.

 

And I like your boolean system.  I will have to add that bit to the toolbox.

[LaRetta]

It looks like LaRetta has the right of it:  FM ignores non-numeric characters in a number-type field, so the "1n0" becomes the number ten, while "0n1" becomes the number one.  

 

Actually this is not quite right, Justin.  It is the function applying TO a number field ... the GetAsBoolean which strips non-numeric characters when 'Replace' is used.

 

You can insert alpha characters in number fields and you can use script to insert carriage returns.  Try a script with single Set Field [ numberField ; "one¶two" ]

 

So it is the application of the auto-enter which is used to protect the number field. 

FileMaker is a bit strange in this regard ... allowing alpha in number fields.

[comment]

I must say that I am a bit surprised by =

EmptyNumberfield = 0

returning True. One is tempted to justify this by saying that the content of a number field must be a number, and since =

EmptyNumberfield < 0

is False, and so is =

EmptyNumberfield > 0

then it follows that EmptyNumberfield must be equal to 0.
 

 

However, by the same logic,

GetAsNumber ( "" ) = 0

should also be True - but it is not. What's worse,

GetAsNumber ( "" ) < 0

returns True, and one must ask: well, how much exactly is it, then?

[LaRetta]

I meant to add ... so yes FM only sees number (or $ or decimal in number fields) to determine what is a number for math.  But field values which lack a number are not indexed so won't appear in value lists.  And when searching for a field which contains "1¶2",  you must search for 1 or search for 2  and you cannot search for 12.  So when searching FM sees them as separate numbers but not when applying mathematics to them.

 

I am just now turning to the observations on 0.  Intriguing stuff.

[Justin Close]

Actually this is not quite right, Justin.  It is the function applying TO a number field ... the GetAsBoolean which strips non-numeric characters when 'Replace' is used.

 

You can insert alpha characters in number fields and you can use script to insert carriage returns.  Try a script with single Set Field [ numberField ; "one¶two" ]

 

So it is the application of the auto-enter which is used to protect the number field.

 

I see what you mean about the function:  if you use the number field as if it were text (e.g. simply displaying the value - ala merge field, or concatenating it with other text) then it leaves the non-numeric characters in place.  But if you use the same field as a number (e.g. passing it to function that expects a number, or using simple math on it) then it will remove those same non-numeric characters.

 

I used simple math ("data + 1") to explore what you said about using it as a number.  So my "1n0" became "10 + 1" with the result of "11". 

 

The indexing aspect is rather interesting to note, though.

[Justin Close]

I must say that I am a bit surprised by =

EmptyNumberfield = 0

returning True. One is tempted to justify this by saying that the content of a number field must be a number, and since =

EmptyNumberfield < 0

is False, and so is =

EmptyNumberfield > 0

then it follows that EmptyNumberfield must be equal to 0.

 

 

However, by the same logic,

GetAsNumber ( "" ) = 0

should also be True - but it is not. What's worse,

GetAsNumber ( "" ) < 0

returns True, and one must ask: well, how much exactly is it, then?

 

 

I created a calculation field with this calculation:

Case (
    GetAsNumber ( data ) < 0 ; "Less Than" ;
    GetAsNumber ( data ) > 0 ; "Greater Than" ;
    GetAsNumber ( data ) = 0 ; "Equals" ;
    "None of the above"
)

Then I tested it with my little checkbox set:

[empty]  =>  'Equals'

"0"   =>   'Equals'

"1"  =>  'Greater than'

 

So in a field it appears that 'empty' does equal zero.  I also tried this calculation (just to see if there were differences in the order of the case()):

"LT result: " & (GetAsNumber ( data ) < 0) & 
"¶GT result: " & (GetAsNumber ( data ) > 0) &  
"¶Equals result: " & (GetAsNumber ( data ) = 0)

And I never got a hit in the less-than check; it returned the same findings as the first calculation.  So it appears that an empty field is treated differently than an empty string?

 

--  J

[comment]

Not sure why you need all those calculation fields; why not simply open the Data Viewer and see the result of a comparison directly as 1 or 0?

 

 

So in a field it appears that 'empty' does equal zero.

 

In a Number field it does.

[LaRetta]

But not when finding.  

[comment]

But not when finding.  

 

Thanks heaven for that at least.

[LaRetta]

Indeed!   

 

I have seen many folks get tripped by that one, searching number for 0 and missing the records with empty values (which is correct but not what they always expect).  Another common misperception is in creating this calc to flag all true numbers:

 

num ≠ "0"

 

But if the field is empty, it is true.  This is one of the reasons why numbers should not be quoted and turned into text.