Simple boolean

[jtsroberts]

Hi,

 

New here, and new to FileMaker - please be nice smiley-smile

 

I am building a small db and one of the the forms has around 30 boolean values, yes/no, 0/1, on/off, whatever you want to call them! I am after the easiest way to use a checkbox on the form that simply updates the database field with a 1 or 0 depending on checkbox state.

 

I have read a few posts and the methods vary a fair bit.

 

Any suggestions on where to look, articles that detail how to do it, etc?

[LingoJango]

You can do this directly.

 

Define your boolean fields as number fields.

 

Define a value list called BooleanOne (just one value: 1) and/or a value list called BooleanOneZero (two values: 0 and 1). (The choice is yours and may depend on what you need your field content to be.)

 

Set field validation to require field content to be a member of your chosen value list.

 

Put the actual field on your layout, make it a checkbox field and choose the appropriate value list. If you just want a checkbox, resize your field until you can only see the checkbox (but watch out if you're allowing a value of 0 as opposed to either 1 or empty!). If you use BooleanTwo, right-click on the field and set the number format to format as Boolean, and you can make the values Yes/No, On/Off, or whatever you choose (I think the limit's 7 characters for each displayed value). Make sure the field size is adequate to display both values.

[jtsroberts]

Thanks for the reply.

 

I have added the Boolean list with only one value, 1. I have added a checkbox set to the form layout. I resized the control so that the number 1 is hidden, but the box is still able to be clicked. When I click the checkbox, the outline of it just flashes - I can't actually tick the box as such.

 

My next question, how can the database field be set to 0 if the checkbox isn't ticked? I'm assuming there would have to some soft of If statement to determine the state of the checkbox and insert either a 1 if it is ticked or a 0 if it isn't.

[LingoJango]

When I click the checkbox, the outline of it just flashes - I can't actually tick the box as such.

 

One possibility is that it is set to be non-enterable in Browse mode. Select the field, Ctrl-Alt-k or right-click and choose "Field Behavior". Make sure you allow the field to be entered in the appropriate modes.

 

My next question, how can the database field be set to 0 if the checkbox isn't ticked? I'm assuming there would have to some soft of If statement to determine the state of the checkbox and insert either a 1 if it is ticked or a 0 if it isn't.

 

If you don't actually need a value of zero in your field, the state of the checkbox will correspond exactly with the state of your field: checked = 1, unchecked = empty. That, by the way, is what you get when you set field validation to allow only a value of BooleanOne.

 

If you do need an actual value of 0 instead of a mere empty value, use BooleanOneZero for field validation, or you will get validation errors.

 

Use the Auto-Enter option in your field definition. Case ( yourfieldname = 1 ; 1 ; 0 ). Uncheck "Do not replace existing value for field".

[jtsroberts]

Ok, I've added the BooleanOneZero as the value list with only two values, 0 and 1.

 

In regards to the checkbox just flashing, my suspicions were correct. In the template I used, the background colour of the control was grey. Of course, the text in the checkbox was also grey....see where I'm heading!! So, I have now 'fixed' that problem by making the background colour white!

 

It all seems to be working just fine.

 

Thank you for your help LingoJango!

[Norma_Snockurs]

I am after the easiest way to use a checkbox on the form that simply updates the database field with a 1 or 0 depending on checkbox state.

There is a simpler and tidier way of achieving this:

 

Make a value list with a single custom value of '1'

 

Set your number field up on the layout to show as a checkbox set showing values from your boolean value list with literally only the first checkbox visible (but not it's value).

 

Under Field Behaviour disallow entry into the field in any mode.

 

Under Format/Button Setup assign a single step to the field of Set Field [Table::numberField; not Table::numberField]

 

Now when you click the checkbox the value will toggle 1 or 0.

If you require a default state of either 1 or 0 you can set this as auto-enter data in the field options.

 

See attached file for an example:

Boolean.fp7.zip

[LingoJango]

Can you have a value of 0 - literally 0 - when your value list only has a 1 in it? I wouldn't have thought so!

[Norma_Snockurs]

Can you have a value of 0 - literally 0 - when your value list only has a 1 in it? I wouldn't have thought so!

Download the example file (it's not forum hosted) and try it for yourself!smiley-laughing

 

not when the field is empty returns a 1

not when the field 0 returns a 0

not when the field = 0 returns a 1

 

The toggle will always work regardless of the field value being null, 0 or 1. Unless populated with auto-enter data at record creation, the field could only ever be empty before the first time toggled - subsequent toggles will always initiate with a value of either 1 or 0.

The value list of '1' needs to exist in order for the checkbox to fill otherwise you wouldn't need a value list at all with this method. FileMaker does not force you to have all the values possible for a field to be entered solely from a value list if one is assigned to it - even if the field is formatted as a checkbox set. It is quite possible for a field's value to be set from, potentially, several different methods at once. If the field contains a value not present in the value list for the checkbox set then it will not be reflected in the checkbox state(s) even though the value is there.

[David Head]

You can also do it with an auto-entered calculation that replaces existing values. See the modified file attached. The auto-enter calculation is:

 

If ( IsEmpty ( field ) ; 0 ; 1 )

 

This method doesn't require any field restrictions on the layout or scripting the field.

[bruns]

David, Hope you don't mind me asking but when would something like this be useful and what does the 0 and 1 mean??

 

Thanks

 

 

You can also do it with an auto-entered calculation that replaces existing values. See the modified file attached. The auto-enter calculation is:

 

If ( IsEmpty ( field ) ; 0 ; 1 )

 

This method doesn't require any field restrictions on the layout or scripting the field.

[David Head]

...when would something like this be useful and what does the 0 and 1 mean??

In Boolean and FileMaker Pro terms, 0 means false and 1 means true.

 

So where is this useful? Anywhere you need to test the truth of a setting. A practical example in a database I am working on right now:

 

Field: gallery_owned (number)

 

This field indicates by a flag (0 or 1) if the item has been purchased by the gallery. I can then use this in calculations where the result depends on whether the gallery owns the work. For example:

 

If( gallery_owned; "G/O"; "CONS" )

 

In this test I have said "If the item is gallery owned". See how the Boolean value can be used for a logical test?

 

Equally, in a script, I can write:

 

If [ gallery_owned ]

...do this

Else

...do that

End If

 

In these examples, I am using the Boolean logic and a good field name to clearly express what I want to say in an expression.

[bruns]

Thanks David, very imformative. Very good explanation.

When you said, "In Boolean and FileMaker Pro terms, 0 means false and 1 means true" where would a novice like myself find out tricks and info like this. Is it documented somewhere?

 

Thanks for you help

 

In Boolean and FileMaker Pro terms, 0 means false and 1 means true.

 

So where is this useful? Anywhere you need to test the truth of a setting. A practical example in a database I am working on right now:

 

Field: gallery_owned (number)

 

This field indicates by a flag (0 or 1) if the item has been purchased by the gallery. I can then use this in calculations where the result depends on whether the gallery owns the work. For example:

 

If( gallery_owned; "G/O"; "CONS" )

 

In this test I have said "If the item is gallery owned". See how the Boolean value can be used for a logical test?

 

Equally, in a script, I can write:

 

If [ gallery_owned ]

...do this

Else

...do that

End If

 

In these examples, I am using the Boolean logic and a good field name to clearly express what I want to say in an expression.

[David Head]

Advice for all developers - read the manual.

Open the online help and read it!

Or if you prefer, find the PDF User Guide and read that.

 

It may not be the most absorbing novel you have ever read but it is a (mostly) true story!

[Bush]

What I've not been able to manage is to display a count of NULL responses in this case.

 

So, I have a report that shows "7 yes, 3 no" answers, but have not been able to get it to display where NO input was given.

 

I've probably overcomplicated this, but to get the counts I simply did an IF

 

if(LikePopsicles="Yes";1;"")

 

or

 

if(LikePopsicles="No";1;"")

 

and then I do a SUM on those instances, adding the 1s and displaying the totals.

 

Unfortunately,

 

if(LikePopsicles="";1;"")

 

is NOT returning a count of all records where they forgot to indicate popsicle preference.

 

Suggestions?

[Bush]

AH! Found the answer (it really is just like Enlightenment, isn't it?) immediately after posting:

 

I needed to uncheck "Do not evaluate if all referenced fields are empty"!

 

Huzzahs! Thanks for all the help, Me!