3 Replies Latest reply on Jul 20, 2012 1:20 PM by philmodjunk

    Checkboxes Fill



      Checkboxes Fill & Delete


      Please help. I’ve read from FileMaker Pro 10, but couldn’t find specifics on filling and deleting check boxes in multiple records, especially if limited to a found set. 

      Regarding finding empty checkboxes, Omit finds all empty checkboxes in the database, but how can I find empty checkboxes limited to a found set.

      I know how to use Replace Field Contents, but not with Checkbox fields.

      Does this require a Formula or a Calculation? If so, what does the formula look like?




        • 1. Re: Checkboxes Fill & Delete

          From Your description, I think you are referring to a field formatted with multiple checkboxes--a check box set as it is called when you set it up this way in the inspector.

          The key to working with such a field is understanding how data is actually modified in the field when you click the checkboxes that come from the value list specified for the field.

          Each time you click an empty checkbox, FileMaker appends the value of that check box to the end of any data already present in the field, separated by a return character. Each time you click a selected checkbox, Filemaker deletes that value from the list of selected values stored in the field. If there is data stored in the field that does not match any of the values specified for the value list, it remains in the field, but the check box format hides it from view. This can create problems if you ever modify the values in the value list without also updating the data entered in the field to match the new values.

          You may find it educational to place a copy of your checkbox formatted field next to the current copy, but formatted as an edit box so that you can see the actual values stored and watch how they change when you click different checkboxes.

          but how can I find empty checkboxes limited to a found set.

          If you enter find mode, use your method to find the empty checkboxes but use constrain found set instead of perform find, you should be able to remove all empty fields from your current found set. In a script, you can do this by specifying a single asterisk (*) in this field as your find criteria.

          If you want to use Replace Field Contents to select a check box value in every record in your found set without removing any other selected values, use this calculation with replace field contents:

          IF ( IsEmpty ( FilterValues ( YourTable::checkboxField ; "ValueToSelectHere" ) ) ;
                List ( YourTable::CheckBoxField ; "ValueToSelectHere" ) ;
                YourTable::CheckboxField )

          Note: this calculation checks to see if the value is already selected and appends the value if it is not already selected. This keeps duplicates from being entered into this field, but in reality, this checkbox formatted field will function correctly even if duplicate values exist so you could just use this expression: List ( YourTable::CheckBoxField ; "ValueToSelectHere" ) and let there be duplicates in the field. This would only create issues if you ever need to count the number of selected values in your field with the ValueCount function or to display the data in the field without checkbox formatting.

          • 2. Re: Checkboxes Fill & Delete

            Hi Phil,

            I obviously don't know what I'm doing. I may be slow to get it, but I take great notes and won't ask the same question again, once I see it work.

            Goal 1 - Example 1: (next goal to be stated in a follow-up reply)

            I have a check box set of 9 checkbox values in a checkbox field called Categories Misc.  Its Type is Text.

            One value is called Consider Drop.

            There are 44 records with Consider Drop checked.

            How do I uncheck/deselect all 44 without unchecking each one manually?

            If it requires using the formula you referred to, how do I make the window that's needed to create the formula/calculation active.

            Also, how would the formula look with my Values and Field Names inserted? Is this a script?

            Please reply if my question is clear. Otherwise, help me make it clearer.



            • 3. Re: Checkboxes Fill & Delete

              What I have shared so far is a calculation, not a script.

              Deleting a value from the list is harder than appending a new value.

              If you have FileMaker Advanced, you can search one of the web sites that list custom functions for one that does this or you can use the following expression as the caclualtion used in a Replace Field Contents action. (This is a menu choice found in the records menu.)

              Let ( [ListA = ¶ & Categories Misc. & ¶ ; // putting extra returns before and after the ends of the list of values is needed for this to work
                       ListB = Substitute ( ListA ; ¶ & "Consider Drop" & ¶ ; ¶ ) ; // deletes the "consider Drop" value from the list but you still have the extra returns
                       L = Length ( ListB )   // note that there is NO semi-colon at the end of this line
                      Middle ( ListB ; 2 ; L - 2 )   // This expression strips off the extra leading and trailing returns

              Note that you can use this in a script so that you do not have to remember all the details of this expression each time. You can also use a Script Parameter, field or variable in place of "Consider Drop" to use this same script to clear any one value from the list created by clicking checkboxes in this field.

              As a script, it would look like this:

              Replace Field Contents [no dialog; YourTableNameHere::Categories Misc. ; //Put the expression shown above here ]

              You would need to make one small change to the above calculation to make it work in this script step: Change Categories Misc. to YourTableNameHere::Categories Misc. in the first line.

              Cautionary Note: Replace Field Contents can change every record in your table and you can't undo the change. It's a good idea to make backup copies of your file just before you try using it for the first time--just in case you don't get the results you expected.