8 Replies Latest reply on May 17, 2014 6:50 AM by DavidTory

    Check box sets



      Check box sets


           I have multiple category values per contact in a check box on the contact record.

           I want to import a set of  categories for a number of the contacts that already have checked values for other categories.

           When I import the new set it wipes out the other checked categories for a found contact.

           For example: I have 10000 contacts. Categories include among many others, "Student", "Eventattendee", Donor.

           I want to add (check off) 500 "Donors" to the relevant Contacts records, some of which are already checked off as "Student"s and/or "Eventattendee".

           How do I import those 500 donors without eliminating the other categories already checked for the same contacts?

           I'm not particularly keen on checking each one by hand.


        • 1. Re: Check box sets

               Import your data with an "update" option but map this data to a different text field or import with the "add new records" option into a completely separate but related table.

               Either option temporarily puts this data into a different field.

               Then use Replace Field Contents to append these additional selections to data already present in the field.

               The following expression assumes that a single value is being imported:

               If ( Isempty ( FilterValues ( Category ; NewValue ) ; List ( Category ; NewValue ) ; Category )

          • 2. Re: Check box sets

                 Many thanks for your quick response.

            • 3. Re: Check box sets

                   I imported the new data to a temporary text field  called NewVal in the Contacts table.

                   The Checkbox set is in a field called Relationships

                   The Replace Field Contents - Replace with calculated result - expression should, therefore, be - based on your Answer above,

                   If (Isempty (Filtervalues(Relationships;NewVal); List (Relationships;NewVal);Relationships)

                   When run the expression is rejected . Semicolon before List highlighted with message - too many parameters




              • 4. Re: Check box sets

                     I left out a parenthesis:

                     If (Isempty (Filtervalues(Relationships;NewVal) ); List (Relationships;NewVal);Relationships)

                • 5. Re: Check box sets

                       Ooops. I should have caught that. Cardinal rule - count your parens!!!


                       THks. Huge help. Working well.

                  • 6. Re: Check box sets

                         I am  having difficulty understanding the broader manipulation of check boxes.

                         Your previous answer provided me with all I needed to do a significant amount of Check Box updates. I have played with the Script you gave me to generate other interesting and useful results.


                         Unfortunately, I now need to turn off a large number of occurences of a given category in a check box of categories. I use the Isvalid parameter but I manage only to turn off all the categories in the check box.

                         How do I do turn off just the one category?

                    • 7. Re: Check box sets

                           Before you do anything else. Do this experiment as a way to better understand what you are working with.

                           Put a field on a FileMaker Layout and format it with a group of several checkboxes. Make a copy of this layout object, but change it back into an Edit box so that you have two field objects for the same field.

                           Now enter browse mode and click various check boxes. Note what appears and disappears from the edit box copy. You may want to resize the edit box if you can't see all the data appear and disappear as boxes are selected and deselected.

                           Now try editing the data directly in the edit box. If you type in text that exactly matches the text of one of your check box values on its own row of text, you should see that check box appear as selected automatically. If you delete a row of text--or even just modify one character so that it no longer matches one of the values, that check box automatically clears. Note that you can have data hidden in the field that is not visible in the check box formatted copy if it does not match one of the check box values.

                           What a script to clear check boxes must do is very similar to your editing the data directly in the Edit Box copy. And you'll want to be careful not to leave any extra, hidden text behind when you do.

                           A script or a Replace Field Contents operation can remove one selected value at a time from the list of selected values in a check box field with this type of operation:

                           Let ( TheList = Substitute ( ¶ & YourTable::checkboxField & ¶ ; ¶ & "valueToRemove" & ¶ ; ¶ ) ;
                                   Middle ( TheList ; 2 ; Length ( TheList ) - 2 )
                                 ) // let

                           The first line removes the text from the list, but extra returns are needed at the beginning and end of the list to make this work, so the second line uses Middle to remove the leading and trailing returns.

                           This expression is set up this way so that substitute can successfully remove "Apple" from a list of values such as "Apple¶Apples¶AppleSauce" and remove the value "Apple" without producing: "s¶Sauce" as the result.

                      • 8. Re: Check box sets

                             What fun!! Many thanks. Great explanation. Like all such, once explained well,  very clear.

                             I have to say, you are making my programming rediscovery a joy. Thank you.