Jump to content

  •  

UPGRADE DEADLINE - SEPTEMBER 26, 2014!
FileMaker Inc. has a deadline for users of version 10,11, 12 as Individual box or volume licenses (with expired maintenance).
If you don't renew your maintenance and upgrade to FMP 13 you will no longer be eligible to upgrade, at the discount pricing.

Volume Licensing upgrade pricing for FileMaker Pro 13, FileMaker Pro 13 Advanced and FileMaker Server 13 will be discontinued.
Individual upgrade pricing for FileMaker Pro 13 and FileMaker Pro 13 Advanced will increase after September 26, 2014.
As of 27-September-2014, FileMaker 10 products will no longer be available for purchase or support.

http://help.filemaker.com/app/answers/detail/a_id/13865


Photo

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


  • Please log in to reply
15 replies to this topic

#1 Justin Close  novice

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

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,185 posts
  • Time Online: 328d 20h 4m 5s

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,533 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: 32d 13h 9m 6s

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,786 posts
  • LocationOregon
  • FM Application:13 Advance
  • Platform:Mac OS X Mavericks
  • Time Online: 217d 11h 20m 53s

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,786 posts
  • LocationOregon
  • FM Application:13 Advance
  • Platform:Mac OS X Mavericks
  • Time Online: 217d 11h 20m 53s

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,786 posts
  • LocationOregon
  • FM Application:13 Advance
  • Platform:Mac OS X Mavericks
  • Time Online: 217d 11h 20m 53s

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  novice

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

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,786 posts
  • LocationOregon
  • FM Application:13 Advance
  • Platform:Mac OS X Mavericks
  • Time Online: 217d 11h 20m 53s

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,185 posts
  • Time Online: 328d 20h 4m 5s

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,786 posts
  • LocationOregon
  • FM Application:13 Advance
  • Platform:Mac OS X Mavericks
  • Time Online: 217d 11h 20m 53s

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  novice

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

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  novice

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

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,185 posts
  • Time Online: 328d 20h 4m 5s

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,786 posts
  • LocationOregon
  • FM Application:13 Advance
  • Platform:Mac OS X Mavericks
  • Time Online: 217d 11h 20m 53s

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,185 posts
  • Time Online: 328d 20h 4m 5s

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,786 posts
  • LocationOregon
  • FM Application:13 Advance
  • Platform:Mac OS X Mavericks
  • Time Online: 217d 11h 20m 53s

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