Jump to content

  •  

Photo

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


  • Please log in to reply
15 replies to this topic

#1 Justin Close  apprentice

Justin Close
  • Members
  • 105 posts
  • LocationSalem, OR
  • FM Application:12 Advance
  • Platform:Mac OS X Mountain Lion
  • Skill Level:Intermediate
  • Membership:TechNet, FileMaker Business Alliance
  • Time Online: 2d 9h 40m 39s

Posted 30 January 2014 - 01:35 PM

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


  • 0

#2 comment  consultant

comment
  • Members
  • 24,274 posts
  • Time Online: 334d 12m 41s

Posted 30 January 2014 - 01:49 PM

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

#3 Wim Decorte  Carpal Tunnel

Wim Decorte
  • Moderators
  • 3,599 posts
  • LocationToronto
  • FM Application:13 Advance
  • FMGo:iPhone / iPod Touch, iPad
  • Platform:Cross Platform
  • Skill Level:Expert
  • Certification:7, 8, 9, 10, 11, 12, 13
  • Membership:TechNet
  • Time Online: 35d 1h 45m 32s

Posted 30 January 2014 - 03:30 PM

 

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

#4 LaRetta   Lifelong FM Student

LaRetta
  • Members
  • 9,839 posts
  • LocationOregon
  • FM Application:13 Advance
  • Platform:Mac OS X Mavericks
  • Time Online: 231d 20m 23s

Posted 30 January 2014 - 03:49 PM

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
Each assumption is an educated guess, a likely condition or event, presumed known and true in the absence of absolute certainty.

#5 LaRetta   Lifelong FM Student

LaRetta
  • Members
  • 9,839 posts
  • LocationOregon
  • FM Application:13 Advance
  • Platform:Mac OS X Mavericks
  • Time Online: 231d 20m 23s

Posted 30 January 2014 - 05:32 PM

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, 30 January 2014 - 06:27 PM.

  • 1
Each assumption is an educated guess, a likely condition or event, presumed known and true in the absence of absolute certainty.

#6 LaRetta   Lifelong FM Student

LaRetta
  • Members
  • 9,839 posts
  • LocationOregon
  • FM Application:13 Advance
  • Platform:Mac OS X Mavericks
  • Time Online: 231d 20m 23s

Posted 30 January 2014 - 06:01 PM

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


  • 0
Each assumption is an educated guess, a likely condition or event, presumed known and true in the absence of absolute certainty.

#7 Justin Close  apprentice

Justin Close
  • Members
  • 105 posts
  • LocationSalem, OR
  • FM Application:12 Advance
  • Platform:Mac OS X Mountain Lion
  • Skill Level:Intermediate
  • Membership:TechNet, FileMaker Business Alliance
  • Time Online: 2d 9h 40m 39s

Posted 31 January 2014 - 07:59 PM

@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 "1\n0" becomes the number ten, while "0\n1" 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.


  • 0

#8 LaRetta   Lifelong FM Student

LaRetta
  • Members
  • 9,839 posts
  • LocationOregon
  • FM Application:13 Advance
  • Platform:Mac OS X Mavericks
  • Time Online: 231d 20m 23s

Posted 31 January 2014 - 08:56 PM

It looks like LaRetta has the right of it:  FM ignores non-numeric characters in a number-type field, so the "1\n0" becomes the number ten, while "0\n1" 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.


  • 0
Each assumption is an educated guess, a likely condition or event, presumed known and true in the absence of absolute certainty.

#9 comment  consultant

comment
  • Members
  • 24,274 posts
  • Time Online: 334d 12m 41s

Posted 31 January 2014 - 08:56 PM

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?


  • 0

#10 LaRetta   Lifelong FM Student

LaRetta
  • Members
  • 9,839 posts
  • LocationOregon
  • FM Application:13 Advance
  • Platform:Mac OS X Mavericks
  • Time Online: 231d 20m 23s

Posted 31 January 2014 - 09:17 PM

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.


  • 0
Each assumption is an educated guess, a likely condition or event, presumed known and true in the absence of absolute certainty.

#11 Justin Close  apprentice

Justin Close
  • Members
  • 105 posts
  • LocationSalem, OR
  • FM Application:12 Advance
  • Platform:Mac OS X Mountain Lion
  • Skill Level:Intermediate
  • Membership:TechNet, FileMaker Business Alliance
  • Time Online: 2d 9h 40m 39s

Posted 01 February 2014 - 09:59 AM

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 "1\n0" became "10 + 1" with the result of "11". 

 

The indexing aspect is rather interesting to note, though.


  • 0

#12 Justin Close  apprentice

Justin Close
  • Members
  • 105 posts
  • LocationSalem, OR
  • FM Application:12 Advance
  • Platform:Mac OS X Mountain Lion
  • Skill Level:Intermediate
  • Membership:TechNet, FileMaker Business Alliance
  • Time Online: 2d 9h 40m 39s

Posted 01 February 2014 - 10:10 AM

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


  • 0

#13 comment  consultant

comment
  • Members
  • 24,274 posts
  • Time Online: 334d 12m 41s

Posted 01 February 2014 - 11:03 AM

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.


  • 0

#14 LaRetta   Lifelong FM Student

LaRetta
  • Members
  • 9,839 posts
  • LocationOregon
  • FM Application:13 Advance
  • Platform:Mac OS X Mavericks
  • Time Online: 231d 20m 23s

Posted 01 February 2014 - 11:08 AM

But not when finding.  


  • 0
Each assumption is an educated guess, a likely condition or event, presumed known and true in the absence of absolute certainty.

#15 comment  consultant

comment
  • Members
  • 24,274 posts
  • Time Online: 334d 12m 41s

Posted 01 February 2014 - 11:11 AM

But not when finding.  

 

Thanks heaven for that at least.


  • 0

#16 LaRetta   Lifelong FM Student

LaRetta
  • Members
  • 9,839 posts
  • LocationOregon
  • FM Application:13 Advance
  • Platform:Mac OS X Mavericks
  • Time Online: 231d 20m 23s

Posted 01 February 2014 - 11:30 AM

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.

  • 0
Each assumption is an educated guess, a likely condition or event, presumed known and true in the absence of absolute certainty.




FMForum Advertisers