Sign in to follow this  
Followers 0

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

16 posts in this topic

Posted

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

Share this post


Link to post
Share on other sites

Posted

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.

1 person likes this

Share this post


Link to post
Share on other sites

Posted

 

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.

1 person likes this

Share this post


Link to post
Share on other sites

Posted

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!

2 people like this

Share this post


Link to post
Share on other sites

Posted (edited)

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 by LaRetta
1 person likes this

Share this post


Link to post
Share on other sites

Posted

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).  

Share this post


Link to post
Share on other sites

Posted

@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.

Share this post


Link to post
Share on other sites

Posted

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.

Share this post


Link to post
Share on other sites

Posted

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?

Share this post


Link to post
Share on other sites

Posted

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.

Share this post


Link to post
Share on other sites

Posted

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.

Share this post


Link to post
Share on other sites

Posted

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

Share this post


Link to post
Share on other sites

Posted

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.

Share this post


Link to post
Share on other sites

Posted

But not when finding.  

Share this post


Link to post
Share on other sites

Posted

But not when finding.  

 

Thanks heaven for that at least.

Share this post


Link to post
Share on other sites

Posted

Indeed!  :wink3: 

 

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.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0