6 Replies Latest reply on Jan 2, 2014 1:50 PM by BruceRead

    Bento to Filemaker Checkbox problems

    GeorginaRead

      Title

      Bento to Filemaker Checkbox problems

      Post

           Hi there,

            

           I have just migrated from Bento to Filemaker Pro Advanced over Christmas and thought everything was working OK ready for starting work today, but there seems to be an issue with the script I have setup to find records with a specific checkbox deselected. 

           I had it set to find records where the checkbox field value is = which I thought was the value assigned to an empty field, but the script doesn't show all the records expected. On other scripts where I am searching for the TRUE value (field name), this works fine, so how can I find FALSE values in the checkbox field or empty fields? I've read some forum answers on similar queries, but I'm not sure I need a separate lookup value table as finding true value fields is fine, it's just finding the empty fields that I'm struggling with. 

           Many thanks,

            

           George

            

        • 1. Re: Bento to Filemaker Checkbox problems
          philmodjunk

               Putting an = sign in a field tells FileMaker to find all records where the field is empty. A value of False could be an empty field, but it could also be a field that has a 0 or some text in some cases. Also, the value list/check boxes format can hide data that is present in the field but that doesn't match one of the values in the field's value list--and thus the field is not actually empty even though no check boxes show as selected.

               If that info doesn't enable you to fix this, please describe the value list used with your check box field.

          • 2. Re: Bento to Filemaker Checkbox problems
            BruceRead

                 I have tried entering another value in the checkbox field and cannot see how this could be done - it seems to be only checked or unchecked, but I have just run some conditional formatting on the data and this is exactly what is causing the problem. There are three sets of data - checked, unchecked and some mysterious version which looks unchecked, but the field cannot be empty. I'm thinking this may have occurred when migrating the data so may not present any further issues moving forward, but I can only see two options here (I can only do basic things having only just moved from Bento):

                 a) Ideally try and clean the data values so there are are only two options, but I don't know how to do this on checkbox fields

                 b) Make the script omit records have the 'true' text instead of trying to find empty fields

                 BTW I don't have a value list at all - do I need one? 

                 If the above sounds OK, how can I clean the data as I have several checkboxes on the forms and would rather not have to change all the scripts.

                 Many thanks,

                  

                 George

            • 3. Re: Bento to Filemaker Checkbox problems
              philmodjunk

                   Checkboxes are a format specified for a field object on your layout. If you enter layout mode, select the field object by clicking it and then check the data tab of the Inspector, you'll see that a check boxes format was selected and the name of the value list specified for it. You can click the pencil next to the name of the value list to open dialogs where you can see how your value list was defined. You can also open Manage | Value Lists to get to the same dialogs.

                   Try this test:

                   While in layout mode, option drag (Mads) or Ctrl-drag (windows) this field to make a copy of it. Drag it to an empty part of your layout body. Use the Inspector to change the format of this field from Check boxes to "Edit box". Now you can enter Browse mode and see the actual data present in this field. Since characters such as space or tab characters are not visible characters, you may need to click into this field and either drag or double click to check for invisible text.

              • 4. Re: Bento to Filemaker Checkbox problems
                BruceRead

                     I've checked the value list and there is only one value specified for the field, which seems right.

                     I've created the copy field and some of the 'empty' fields are in fact empty, whereas others have the word false, so this seems to be the root of the problem, but as they were checkboxes in Bento, I'm a little puzzled why they have moved across like this. 

                     I guess the question now is how I go about fixing it with a very simple solution! The problem seems to be the same on nearly all of the checkbox fields migrated over. Is it as simple as doing a find and replace on all False instances and replacing with nothing?

                      

                • 5. Re: Bento to Filemaker Checkbox problems
                  philmodjunk

                       Since I have never used Bento, I have no idea why this happened in terms of what was imported from Bento.

                       Enter find mode.

                       In the edit box copy of the field, enter the exact value that the single value value list enters when a check box is selected.

                       Click the "omit" button in the status tool bar so that you are finding all records where the check box is NOT selected.

                       Click or tab into this field and delete any text in it. Select Replace Field Contents and use the Current value option to clear this field for all records that do not have a selected check box.

                       Now select Show Omitted Only from the records menu and check to see if any records have the box selected, but also store additional data in the same field. If there are any, you can either manually edit the edit box copy of the field to delete the extra data or you can do the same steps as above to clear all the fields, then select the check box for one field and use Replace Field Contents to update all the other fields to match.

                       Note: since Replace Field Contents changes the data in large numbers of records, save a copy of your file before you start in case a mistake on your part messes up the data in a large number of fields.

                       How/why this happens in a FileMaker file:

                       Clicking an empty check box appends the associated value from the value list to the end of any text that exists in the field with a return character separating the other text from that appended by this mouse click. Click a selected check box to clear it and FileMaker deletes the first such return separated value from the field.

                       Where trouble of this sort usually occurs is when a developer modifies the value list to remove a value from the value list. Removing that value removes the check box for that value but does not modify the data stored in any field where you use that value list. The check box format then hides this other value from view, but it's still present in the field where it can affect calculations and finds such as you have been attempting here.

                  • 6. Re: Bento to Filemaker Checkbox problems
                    BruceRead

                         All done. There were only two spurious values - false and an empty field, so I'm not really sure why this would occur as the Bento database only ever had checkboxes for these fields and being a simple system, there wasn't the option of assigning values to the data. I guess I'll never know, but I'm relieved it's all sorted now!

                         Thank you very much for your help - I wouldn't have managed it without you!

                         Regards,

                          

                         George