4 Replies Latest reply on Dec 8, 2013 6:15 PM by JeanneM

    How do UN-check a specific custom value in a value list without replacing all information in a field

    JeanneM

      Title

      How do UN-check a specific custom value in a value list without replacing all information in a field

      Post

      So...this previous post about how to check a specific custom value in a value list without replacing all information was helpful, although, I don't use scripts often enough to remember how to use everything:

      Script to ADD a checked value, NOT REPLACE the field contents

       

      NOW, the question is: how do I UN-CHECK a specific custom value, without again, using the (Mac) Command+= command to replace all values in a field in a found set?

       

      Example:

      Say my field is Colors

      And my custom values/check boxes are

      Red
      Orange
      Yellow
      Green
      Black

      Say, I've found a set and in it, I have erroneously checked Orange when I meant to check Black. So I want to fix that, but I can't simply replace the field with just the Black color checked. I want to KEEP any and all other colors that are checkmarked, which vary by record.

       

      Could someone help me do this and talk to me like a four-year-old when you explain what the script does and how to set it up? That way, I can modify it to use however I want?

       

      Thank you so much.

        • 1. Re: How do UN-check a specific custom value in a value list without replacing all information in a field
          philmodjunk

          A User, can, of course, just click the check box a second time to clear it. How will a script know which value to "uncheck"?

          I'll use a literal constant so that this script only removes "Orange" from the list and only adds "Black"....

          #First check to see if "Orange" is selected in the current record's checkbox field
          If [ Not IsEmpty ( FilterValues ( Table::checkboxfield ; "Orange" ) ) ]

          Filter values compares two lists of values and returns a list of all values in both lists. In this case, it will return "orange" only if it is a value selected in the check box field. IsEmpty will return True only if no value is returned, which will be the case only if Orange is NOT selected.

          Loop
             Set Variable [$I ; Value: $I + 1 //$I will be our counting variable to keep track of which variable we are checking.]
             Exit Loop If [$I > valueCount ( Table::Checkboxfield ) // exit loop after all values have been checked ]
            If [GetValue ( Table::Checkboxfield ; $I ) ≠ "Orange" ]
                 #Since value at Position number $I is not "orange", add it to list of values stored in the variable named $List
                 Set Variable [$List ; Value: List ( $List ; GetValue ( Table::Checkboxfield ) ; $I ) ]
             End If // if value is "Orange", skip it.
          End Loop
          #Copy list of all values but "Orange" back into the check box field
          Set Field [table::Checkboxfield ; $List ]

          #Now add "Black" to list only if it is not already in the list...
          If [ IsEmpty ( FilterValues ( Table::checkboxfield ; "Black" ) ) ]
             Set Field [table::checkBoxfield ; List ( table::checkboxfield ; "Black" ]
          End If

          If you replace "Orange" and "Black" with two variables or two fields, this script can be modified to remove any selected value and replace it with any selected value.
            

          • 2. Re: How do UN-check a specific custom value in a value list without replacing all information in a field
            JeanneM

            Thank you for your post Phil. I'm a major novice, so excuse me if this is stupid, but...when I try to try out your script, I am unable to input it in ScriptMaker. I can't find, for example, a command (or whatever the term may be) for "Not," or "IsEmpty," etc....

             

            I'm using FileMaker Pro 8 if that makes a difference...

             

            it won't just let me type in the script...

            • 3. Re: How do UN-check a specific custom value in a value list without replacing all information in a field
              philmodjunk

              It's really, really important to let people know what version of FileMaker you are using. In this case, you are lucky, both Not and IsEmpty are available in your version. They are not, however, script steps so you won't see them listed in the script editor. You will be able to add them to calculations by typing them into the specify calculation dialog box.

              I don't see where I used Not in any part of this script. (text after # or // are comments that can be left out.)

              Here's an example:

              In the script editor, double click the If script step listed in the right hand section of the editor to add it to your script. Then double click the newly added step or click the specify button at the bottom right to open up the specify calculaiton dialog. Then add the expression shown between the brackets in this dialog. You can add fields by double clicking them in the list of fields at top left. You can add functions by doubleclicking them in the list of functions at top right.

              • 4. Re: How do UN-check a specific custom value in a value list without replacing all information in a field
                JeanneM

                     I finally figured this out, too.

                     I didn't know that you could Calculated Result and Substitute in the Replace function to replace just part of a field, but that alone can be a little problematic for big automation jobs: I realized I still needed a script to check to see if there was a return carriage or not. Pesky things!

                      

                Script to REMOVE a checkbox

                (Replace the TABLE and FIELD in 6 places and VALUE in 4 places each time you need to use this)

                Loop

                If [ Pattern Count ( TABLE::FIELD ; "VALUE" & ¶ ) ]

                Replace Field ContentsTABLE::FIELD ; Replace with calculation: Substitute ( TABLE::FIELD ; "VALUE" & ¶ ; "" ) ]

                Go to Record/Request/Page [ Next ; Exit after last ]

                Else

                If [ Pattern Count ( TABLE::FIELD ; "VALUE" ) ]

                Replace Field Contents [ TABLE::FIELD ; Replace with calculation: Substitute ( TABLE::FIELD ; "VALUE" ; "" ) ]

                Go to Record/Request/Page [ Next ; Exit after last ]

                Else

                Go to Record/Request/Page [ Next ; Exit after last ]

                End If
                End If
                End Loop