11 Replies Latest reply on Mar 26, 2014 10:41 AM by mark_scott

    Checkbox on Layout Issue

    rmittelman

      My database needs to have Boolean fields. Since there aren't any, the practice is to use a numeric field, and populate it with 0 or 1 (or any number for that matter).

       

      I Googled how to make a checkbox control on a layout to indicate the value of the "Boolean" field, and it seems the conventional wisdom is to have a value list with only 1 in it, and make the layout field a checkbox set, pointing to that value list. The "1" shows up next to the checkbox, but if you size the field properly, that is not visible.

       

      I'm having some issues, as some of my records have 0 in the pertinent field, and some have nothing, just empty. I'm grouping my report by the value of that field (descending), so instead of having 2 sections ("true" and "false"), I'm getting 3 sections. They consist of records with 1 in that numeric field, then records with 0, then records where the field is empty.

       

      I think this is caused by my lack of understanding of how to use booleans. The early records are empty in that field, whereas all the later records have 0 in that field. I added another text field on my layout, also pointed to that same boolean field, to see what was happening. If there is a 0 in the indicator field, checking the checkbox causes a "?" to show up. If the field is empty, checking the checkbox causes a "1" to show up, and unchecking causes the field to become empty.

       

      I can't find any way for the field to contain either "0" or "1" depending on whether I check or uncheck the corresponding checkbox, which is what I would expect. I tried making the value list 1 and 0, or 0 and 1, or "1" and "0", or "0" and "1", all to no avail. The checkbox only seems to work if there is a single 1 (or "1") in the value list.

       

      So my question is: Is there a way to fix this to operate how I *thought* was properly, or should I simply change all the "0" values to empty and remove the requirement that the field be not empty, and also the 0 to 1 value requirement?

       

      Thanks for any help...

        • 1. Re: Checkbox on Layout Issue
          ch0c0halic

          The behavior you want is for a Radio Button, not a checkbox.

           

          A checkbox regardless of how many values are there will always add or remove the single value associated with the checkboxes. It never clears the field.

           

          You can make it a radio button, which is the correct thing to do based on Human Interface Guidelines.

          Make the checkbox a button that does a set field to clear the field if a 1 is present and sets it to 1 if it isn’t. Remember you are dealing with a multiple line entry like a text field, not a true number, so you have to deal with the impossible combinations. Off the top of my head I think this will work. But, you will have to try it.

           

          Set Field

          • 2. Re: Checkbox on Layout Issue
            rmittelman

            I have to think about this one, I don't quite understand the answer.  In my non-Filemaker experience, a check box is always a good way to represent a boolean field.  It's either on or off.  I'm not sure if a radio button is an on/off kind of thing.  I thought you could just turn them on, and if another one in the same group was turned on, the first one is turned off.  Perhaps FM works differently?  In my playing with the existing setup, checking and unchecking the checkbox alternately fills the field with 1 or with nothing.  This seems to work ok, except that when testing the value of that field in a calculation, I need to use IsEmpty in an IF test, so I can return 0 if it's empty.  Otheerwise, it seems to work ok.  This is just confusing in FMP.

             

            Thanks for the answer, I will try to figure it out a bit...

            • 3. Re: Checkbox on Layout Issue
              erolst

              ch0c0halic wrote:

              You can make it a radio button, which is the correct thing to do based on Human Interface Guidelines.

              It's not entirely correct, because we're not talking about changing the radio's frequency, but turning the thing off or on. That's probably where this metaphor breaks down.

               

              If you want to follow the guideline, you either need two values and two buttons, or one button and a script; either way, using a checkbox is more convenient.

              ch0c0halic wrote:

              A checkbox regardless of how many values are there will always add or remove the single value associated with the checkboxes. It never clears the field.

              It does clear the field if the associated value was the only value in the field.

              • 4. Re: Checkbox on Layout Issue
                erolst

                rmittelman wrote:

                This seems to work ok, except that when testing the value of that field in a calculation, I need to use IsEmpty in an IF test, so I can return 0 if it's empty.

                 

                If you use a number field that either holds the value 1, or the value 0 or is empty, you can simply use

                 

                If [ myFlagField ], or Case ( myFlagField ; … )

                 

                or

                 

                If [ not myFlagField ] etc.

                 

                No need to "return 0 if it's empty".

                 

                Interestingly, if you use a step like Set Field [ myFlagfield ; not myFlagField ], you will automatically toggle between 1 and 0. But as long as your value list only consists of 1, a value of 0 will simply not trigger the checkmark's display.

                 

                And check out the Boolean formatting options in the Inspector.

                • 5. Re: Checkbox on Layout Issue
                  hbrendel

                  It does clear the field if the associated value was the only value in the field.

                  If the field contains a value that is not (anymore) in the value list, then clearing all checkboxes does not clear this 'orphanized' value.

                   

                  This can happen when a valuelist changes. It would be a problem if the field would be tested for being empty.

                  • 6. Re: Checkbox on Layout Issue
                    erolst

                    hbrendel wrote:

                     

                     

                    It does clear the field if the associated value was the only value in the field.

                     

                    If the field contains a value that is not (anymore) in the value list …

                    … then the value associated with the clicked checkbox obviously was NOT the only value in the field.

                    hbrendel wrote:

                    It would be a problem if the field would be tested for being empty.

                    Yes, if that check was performed visually …

                    • 7. Re: Checkbox on Layout Issue
                      hbrendel

                      Do with it as you like. I only meant to state that clearing all checkboxes is not the same as clearing the field.

                      • 8. Re: Checkbox on Layout Issue
                        rmittelman

                        ch0c0halic wrote:

                         

                        A checkbox regardless of how many values are there will always add or remove the single value associated with the checkboxes. It never clears the field.

                         

                        So now I finally get it.  Assuming my value list consists only of "1", clicking the checkbox simply adds a "1" to the field.  If there was already a "0" in the field, then I'm left with List ( "0" ; "1" ).  Unchecking takes off the "1" and leaves me with "0".  This is the reason I saw "?" in the "shadow" text box on my layout, which was tied to the same table field.  FM was confused by the list in a numeric field.  This also explains why it works just fine if the field was originally empty.

                         

                        Thanks @ch0c0halic!

                         

                        hbrendel wrote:

                         

                        If the field contains a value that is not (anymore) in the value list, then clearing all checkboxes does not clear this 'orphanized' value.

                         

                        You are so right.  I discovered this when I played with having a "0" also in my value list.  This didn't work, then I removed the "0", and found a bunch of "0"'s in the records, which I had to remove manually.

                        Thanks for your help, I appreciate it.  Every time I think I am getting to know FMP, I find something else that's totally different from my experience with other products.

                         

                        erolst wrote:

                        If you use a number field that either holds the value 1, or the value 0 or is empty, you can simply use

                         

                        If [ myFlagField ], or Case ( myFlagField ; … )

                         

                        or

                         

                        If [ not myFlagField ] etc.

                         

                        No need to "return 0 if it's empty".

                         

                        While that is true when generally testing the boolean field, it's not quite true when using the field to add to a list.  Consider this scenario:

                         

                        The Select Reports layout, based on the Reports table, lists each report in a self-join portal.  After selecting the ones I want, I tap the "Process" button, and the script goes through the portal rows, and saves anything needed from each selected one.  These items include report name, type, orientation, all_records, exclude_inactives, no_filtering.  The last 3 are booleans for each report.

                         

                        All items are appended to their respective lists, then the layout is closed, bringing me back to the Members layout so I can generate the report(s).

                        When this didn't work properly, single-stepping through the script showed me that when the boolean fields were empty, there was nothing added to the list during the prior steps.  So I needed to append to the list like so:

                         

                        Set Variable [$all_records_list ; Value: List ( $all_records_list ; If ( IsEmpty ( ReportsSelf::All Records ) ; 0 ; ReportsSelf::All Records ) )]

                         

                        That way, at least SOMETHING was always added to the list of boolean values.

                        • 9. Re: Checkbox on Layout Issue
                          erolst

                          Well, if you need an actual, tangible value, you could just use

                           

                          Set Variable [$all_records_list ; Value: List ( $all_records_list ; GetAsBoolean ( ReportsSelf::All Records ) ) ]

                          • 10. Re: Checkbox on Layout Issue
                            rmittelman

                            Well, that WOULD be easier, wouldn't it?

                            • 11. Re: Checkbox on Layout Issue
                              mark_scott

                              Hello rmittelman,

                               

                              Here's how I enforce two-value logic (0 or 1; no nulls) in a field:

                               

                              Field is a number field type (I believe you've done that), with the following two Auto-enter settings:

                               

                                   (a) Data: 0 (or 1, depending on which value you want new records to default to)

                               

                                   (b) Calculated value: "GetAsBoolean ( Self )" [uncheck: Do not replace existing value of field (if any)]

                               

                              The rest, on the layout itself, is as you've set up, namely a Checkbox set control style with "values from:" your value list containing only the value "1", and the field control shrunk down to hide the "1" on the layout.

                               

                              This won't take care of legacy records that are null (or that have any other value/s entered)—a "Replace Field Contents" operation or similar will clean those up—but from then on, as well as all new records, clicking in the checkbox will reliably toggle the value between 1 and 0.

                               

                              FileMaker should probably have a Boolean field type built in.  C.J. Date (God Father of Relational DB theory, after founding father E.F. Codd) has opined that, while a database will naturally support multiple data types, the only type that absolutely must be supported in the relational model is Boolean.  (I have trouble finding that passage, but I have zero doubt that I read exactly that in one of his books or online writings.)  Absent a Boolean field type, however, this is a quick and reliable way to set up a two-value-logic field in FileMaker.

                               

                              hth,

                               

                              Mark