"Boolean" checkbox trouble

[mikedr]

I have a field "Completed" for a table "Task."  The idea is that it's set to 0 initially, and a user can check off a box to indicate that the record has been completed.

 

I set up the field as a number, with auto-enter data of 0.

I set up a value list "yes, no" with a single value, 1.

 

On the layout, for the field, I created a checkbox, and sized it to hide the value of 1, so you only see the box.

 

Before the box is checked, the value stored in the field is indeed 0.

However, when the box is checked, the value stored in the field is shown as:

0

1

 

That is, 0 on one line, and 1 on another (I have another layout that displays the actual value, for testing purposes).  Obviously, this is not a valid number.  I don't really understand what's going on. 

 

What am I doing wrong?  Just to make sure that there isn't some weird layout issue going on, I did a small test script to display a dialog with the value of this field, and sure enough, it shows:

0

1

 

If I uncheck the box, the value does go back to 0 correctly.  But if I check it again, it goes back to 0 and 1 on separate lines.

 

Does this have something to do with me specifying "auto enter data" of 0.  I.e., when you change the field  to 1 by clicking on the checkbox, somehow it's also adding the 0 in there too?

 

Very flummoxed!

[eos]

Does this have something to do with me specifying "auto enter data" of 0.  I.e., when you change the field  to 1 by clicking on the checkbox, somehow it's also adding the 0 in there too?

 

Yes and no; yes, it is caused by the auto-enter option, and no, the 0 isn't added and removed by your clicking the checkbox; it was added once – when you created the record – then (unseen) remained there.

 

Each checkbox simply toggles its associated value, but that has no impact whatsoever on other values that may already be in the field (and which, due to the checkbox formatting, are not displayed). So all you did by clicking the checkbox was adding and removing the 1.

 

I have another layout that displays the actual value, for testing purposes

 

You don't need another layout for testing; just put the field twice on the same layout, one as checkbox, the other as edit box.

 

In an nutshell: don't specify an auto-enter value, and toggle between 1 and <empty>, which works perfectly fine as Boolean False (which is what you're really interested in, rather than the numeric value 0).

[Mark Scott]

Hello Mike,

 

I set up the field as a number, with auto-enter data of 0.

I set up a value list "yes, no" with a single value, 1.

 

On the layout, for the field, I created a checkbox, and sized it to hide the value of 1, so you only see the box.

 

So far, so good!  Now, in Manage Database, add the following Auto-Enter setting to the field (in addition to keeping your "Data: 0" auto-enter setting):

 

     GetAsBoolean ( Self )

 

Lastly (and critically), uncheck the "Do not replace existing value of field (if any)" option.

 

You should then see exactly what you're aiming for.

 

hth,

 

Mark

[mikedr]

Hello Mike,

 

 

So far, so good!  Now, in Manage Database, add the following Auto-Enter setting to the field (in addition to keeping your "Data: 0" auto-enter setting):

 

     GetAsBoolean ( Self )

 

Lastly (and critically), uncheck the "Do not replace existing value of field (if any)" option.

 

You should then see exactly what you're aiming for.

 

hth,

 

Mark

 

OK, holy crap -- this works perfectly!  However, I have no idea what's going on here.  Can you explain?  I tried creating a new record; it defaults to zero, perfect.  Now, I click the checkbox, it goes to 1 instead of 0 on one line and 1 on the other (I'll refer to this as 0/1).  I assume, then, that 0/1 evaluates as 1 (i.e., Boolean true), and hence is then replaced with 1?  And, when creating a new record, 0 is entered and then evaluated as Boolean, which of course is 0 (Boolean false).

 

Is this correct?  I just want to make sure I understand what's going on.

Yes and no; yes, it is caused by the auto-enter option, and no, the 0 isn't added and removed by your clicking the checkbox; it was added once – when you created the record – then (unseen) remained there.

 

Each checkbox simply toggles its associated value, but that has no impact whatsoever on other values that may already be in the field (and which, due to the checkbox formatting, are not displayed). So all you did by clicking the checkbox was adding and removing the 1.

 

 

You don't need another layout for testing; just put the field twice on the same layout, one as checkbox, the other as edit box.

 

In an nutshell: don't specify an auto-enter value, and toggle between 1 and <empty>, which works perfectly fine as Boolean False (which is what you're really interested in, rather than the numeric value 0).

 

Thanks!  I think I like Mark's idea tho! 

 

I should've been more clear  about the layout . . . I have a production layout that my assistant sees, and then a testing layout that she can't see, where I'll just throw things on for testing, and I did put the field twice on the testing layout.  I'll create a test record so I don't muck up any "real" records, and mess around with that.

[Mark Scott]

OK, holy crap -- this works perfectly!

 

I love those "holy crap" moments, too!

 

 

I assume, then, that 0/1 evaluates as 1 (i.e., Boolean true), and hence is then replaced with 1?

 

Correct.

 

 

And, when creating a new record, 0 is entered and then evaluated as Boolean, which of course is 0 (Boolean false).

 

Also correct.  And, lastly, when you uncheck your checkbox, the momentarily empty field then evaluates to 0 (Boolean false).

 

Eos' suggestion (leaving "uncompleted" as an empty field rather than 0) has some advantages that should definitely be considered.  In a very large table with a lot of records where the value is "uncompleted," this can reduce file size as well as potentially improve performance of certain operations.  Those benefits notwithstanding, I prefer 0 in most cases, as being less ambiguous.  (Google "nulls" and "two-value" vs "three-value logic," if you're interested.)

 

Glad it works.

 

Mark

[Josh Ormond]

Another consideration, is how many other auto-enters you have in the table.  I am currently rebuilding a solution that has several in the same table. By going to something more like eos suggestion, I was able to reduce the time it takes 1 script to run, from 5 mins down to 1 min. Then moved to setting all the fields/records through a single relationship with a single commit and got it down to about 3 secs. Auto-enters have over-head.

 

And while it does somewhat depend on your solution, I prefer to chose the most efficient method if possible.

[mikedr]

Another consideration, is how many other auto-enters you have in the table.  I am currently rebuilding a solution that has several in the same table. By going to something more like eos suggestion, I was able to reduce the time it takes 1 script to run, from 5 mins down to 1 min. Then moved to setting all the fields/records through a single relationship with a single commit and got it down to about 3 secs. Auto-enters have over-head.

 

And while it does somewhat depend on your solution, I prefer to chose the most efficient method if possible.

Let me ask you, how many records are you talking about? I'm a newbie, and haven't been overly concerned about efficiency right now. My primary table has 2,500 records, and in all likelihood we'll be adding no more than 200 a year. Is efficiency something I should be concerned about with this size?

[Josh Ormond]

Depending on the rest of the design, you may not notice a difference. That's not a lot of records for FileMaker to handle.

[mikedr]

What is considered "a lot" of records in FM?

[LaRetta]

I suggest that every act which could have been optimised but wasn't is poor practice.  It is not poor practice because of principle but rather because of:

1. If you let slide optimisation in this area on 2,500 records, the next time you'll be inclined to think letting slide with 3,000 records is fine.  It is a slippery slope which breeds sloppiness and drags a good solution down into less than ideal.
2. We can not always predict the environment and venue our solutions will be fired under.  It may be fine over LAN but when accessed from an iPad with low-grade provider, the process which only took a blink over LAN now takes 4 seconds and THAT is a LONG time to a User.  As for me, if I go to a website which takes more than 6 seconds to load, I move on.  Users today will not tolerate slop.  Compounded, each of the blow offs which were small, now each contribute to network traffic and begin to crawl.

Do not give in.  Optimize everything you do at all times.  It is one of the few things you can control and it is the best investment in your solution.  You may think you are only saving one second but multiply that over six months, performed by 25 users and you see clearly where this is going.  Do not fall for the disease of sloppiness.  Commit to excellence in your work.

 

And yes, it is only my opinion because my opinion is the only one I have to share.  And yes, I'm an obnoxious idealist.  If that is the worst I am called, I'll take it.

[LaRetta]

Not to mention wasted electricity!!!  

 

Something Michael said awhile back.

[comment]

 (Google "nulls" and "two-value" vs "three-value logic," if you're interested.)

 

That is an excellent argument why you should not use 0 in addition to 1 and <empty>. You should reserve such arrangement for cases where 0 means something different than <empty> - for example: 0 - male, 1 - female, <empty> - unknown.  Of course, in such case, you would not be auto-entering 0 (or anything else). And you would be using a radio button set for selection.

 

Forcing 0 as a replacement for <empty> in a simple true/false field just adds unnecessary moving parts.

[LaRetta]

Forcing 0 as a replacement for <empty> in a simple true/false field just adds unnecessary moving parts.

 

Unless null is a requirement on the field (so management can distinguish between null = 'never selected' or 0 = 'selected no' or 1 = 'selected yes', I still like to auto-enter 0 in a  toggle field because I like to use the data format Boolean logic with yes and no, or any other two true/false values.  But it needs to have a 0 - it won't display on null.  And they STILL limit character number to 7.

 

Either way you go, it is important to be consistent and make it clear when searching.  Nothing is worse than thinking that searching for = (empty) will find all records which are not 1 and that can catch people.  0 also allows relationship whereas empty is more difficult.  it must be full moon - I have high energy!  

You're the one who turned me on to use boolean data formatting! 

[Aussie John]

Always the option to use a radio button

 

Unless null is a requirement on the field (so management can distinguish between null = 'never selected' or 0 = 'selected no' or 1 = 'selected yes', I still like to auto-enter 0 in a  toggle field because I like to use the data format Boolean logic with yes and no, or any other two true/false values.  But it needs to have a 0 - it won't display on null.  And they STILL limit character number to 7.

 

Either way you go, it is important to be consistent and make it clear when searching.  Nothing is worse than thinking that searching for = (empty) will find all records which are not 1 and that can catch people.  0 also allows relationship whereas empty is more difficult.  it must be full moon - I have high energy!  

You're the one who turned me on to use boolean data formatting! 

[comment]

0 also allows relationship whereas empty is more difficult.

 

Right, so there are exceptions.

 

You're the one who turned me on to use boolean data formatting!

I had a feeling this was going to turn out to be my fault...

[LaRetta]

Right you are - as always!  

[David Jondreau]

Nobody's mentioned a custom control? I'd ditch the radio button/checkbox control and the auto-enter entirely and script the process.

 

Google checkmark and select images. Grab whatever checkmark looks nice to you. On your layout, make a box using the rectangle tool. Make the rectangle a script that sets the field. Insert the checkbox into the layout over the box. Add a "Visible when..." calculation to the mark.

[Josh Ormond]

Radio buttons run the same issue. If you just want a single check box, it doesn't help. Plus they can be miss used. It is possible to multi-select a radio button.

 

Always the option to use a radio button

 

LaRetta makes some good points. It's that balance between what is necessary and what is most efficient.  There are multiple ways to handle many things. Even the having null values can be worked around in relationships.

[LaRetta]

I didn't think the discussion was about the control used but rather the data that enters the field and what it represents. Good discussions!

[Mark Scott]

That is an excellent argument why you should not use 0 in addition to 1 and <empty>. You should reserve such arrangement for cases where 0 means something different than <empty> - for example: 0 - male, 1 - female, <empty> - unknown.  Of course, in such case, you would not be auto-entering 0 (or anything else). And you would be using a radio button set for selection.

 

Forcing 0 as a replacement for <empty> in a simple true/false field just adds unnecessary moving parts.

 

I'd argue the opposite.  Null means "missing" or "inapplicable." I'd argue that Mike's "incomplete" task status is neither of these.  Not that I wouldn't consider using an empty field instead of 0 for this, for all the good reasons mentioned above, when performance benefits.  At least conceptually, however, and often in practice, I'd regard "incomplete" as a semantic "false" (0) value.

[Mark Scott]

Nobody's mentioned a custom control? I'd ditch the radio button/checkbox control and the auto-enter entirely and script the process.

 

Google checkmark and select images. Grab whatever checkmark looks nice to you. On your layout, make a box using the rectangle tool. Make the rectangle a script that sets the field. Insert the checkbox into the layout over the box. Add a "Visible when..." calculation to the mark.

 

I favor this approach, too, David.  In FM13, you can take it a step further and use checkbox images (checked and unchecked) as "image" fill types, for buttons, adding sufficient left-padding to the text label to allow room for the left-aligned image fill.  Save as styles—always a good "best practice" in the new custom-theme era—layer the "checked" button on top with a "Hide when…" setting (as you point out), and you can have a highly functional and nice looking control.  Saved to the theme and reused wherever needed, the new pair of button styles adds precious little overhead to the file or to layout rendering.  This approach also allows the user to click anywhere on the text label to select/deselect the checkmark, which is a behavior they're likely to expect.

 

Mark

[comment]

 Null means "missing" or "inapplicable."

 

I have carefully avoided using the term NULL because Filemaker is not SQL and mixing the concepts can only lead to (even more) confusion. Unlike SQL, Filemaker has no concept of NULL as something that is distinct from an empty string. Unlike SQL, in Filemaker an empty string is not a value.

 

IMHO, the meaning of <empty> in Filemaker can be whatever you deem it to be. Insisting that it must mean "missing or inapplicable" can lead to absurdities - consider, for example, a DateCompleted field.

[Mark Scott]

Unlike SQL, Filemaker has no concept of NULL as something that is distinct from an empty string.

 

 

Good point, comment.  I had deleted almost exactly the same sentiment from my previous reply before posting, so I agree with you.  An empty field in FM sometimes acts like a Null (e.g., in relationships), sometimes like an empty string (e.g., in text calculations), and sometimes like a 0 (e.g., in arithmetic calculations).  (For example, in FileMaker, A + B + C will return 7, when A=3, B=4, and C is empty.)  It's definitely got that "whatever you want it to be" sort of ambiguity going for it.

[Josh Ormond]

In this instance we are talking about a boolean value. To FM, 1 = True and 0 = False and "" = False.

 

For a boolean discussion there is no 3rd option. Testing for boolean has the same result if it's 0 or False.  As long as it's consistent, like LaRetta said, it doesn't matter. So in that case, I'm usually going to opt for not storing a value that isn't necessary. The exception being if there is no other way to use it in a relationship...but as we know there are ways.

[comment]

For example, in FileMaker, A + B + C will return 7, when A=3, B=4, and C is empty.

 

That's a good example: it shows that the root of the difference is Filemaker's loose data typing (a blessing by itself, IMHO).