14 Replies Latest reply on Dec 8, 2013 6:09 PM by JeanneM

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

    JeanneM

      Title

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

      Post

      Hello, whenever I use checkboxes (as opposed to radio buttons) in FileMaker Pro 8, it's because we want to utilize being able to check multiple boxes. So, it becomes important to have a fast way to change all the records in a found set (or current) set by ADDING or taking AWAY checks, NOT REPLACING an entire field contents.

      I can't find any help on how to create a script that would do this. Everything I have found has involved REPLACING a field's contents, which will not work for my needs.

      A script is necessary in order to create a loop so I can change all of the records in a found set.

      I am new to FM scripts and am not familiar with the language, but here is my set-up for ADDING checks and it is not working...

      I'm in a table named Contact Manager

      I have a VALUE LIST called Known Ticket Buyers with Use Custom Values and its contents are -

      0803 Aronoff;
      0904 Aronoff;
      1101 Dialogues;

      I have a FIELD called Field Known Ticket Buyer with all default options (didn't change anything) that is set-up to use the values from the above value list.

      In any one operation, I might like to say, Check the checkbox 0803 Aronoff; value in all of the found records WITHOUT unchecking any other option (so, again, NOT a replace function).

      Here's my script, which doesn't seem to do ANYTHING but go through all of the records. I don't see the checkbox checked at all.

      Loop

      Set Field [Contact Mnagement::Field Known Ticket Buyer["0803 Aronoff;"]]

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

      End Loop

      Can anyone please offer specific help? I don't know enough to toy with scripts any further. I don't understand why it's not showing the boxes marked. I would also like to know if you can show me how to use a similar script to UNCHECK a specific value.

      Thank you very, very much. Been trying to figure this out for a long, long, long time and have done so many searches that have led no where!

        • 1. Re: Script to ADD a checked value, NOT REPLACE the field contents
          FentonJones

          The way to "add a value to a field" (at end) is to set the field to itself, add a carriage return, then add the new value (or add the new value to the beginning, by adding it, then a return, then the current field's value)

          Set Field [ a field; a field & ¶ & "a new value" ]

          This can also be use in a Replace Field Contents script step, which automatically does all records in the found set.

          In either case, backup your database first, as neither of the above can be "undone".

          • 2. Re: Script to ADD a checked value, NOT REPLACE the field contents
            JeanneM

            I tried this:

            Loop

            Set Field [Contact Management::Field Known Ticket Buyer[Contact Management::Field Known Ticket Buyer & ¶ & "0803 Aronoff;"]]

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

            End Loop

            And again, it went through all the records, but the check box with the value 0803 Aronoff; never got checked on any of the records. Is there something wrong with me using a semi-colon? Or a space? I don't understand why it won't show up as checked. In my value list, there is a return carriage after each value. I tried including the enter key inside the Specify Calculation window, but that didn't change anything.

            It seems like it's doing something by going through the loop, but I don't see boxes checked!!! So frustrating.

            • 3. Re: Script to ADD a checked value, NOT REPLACE the field contents
              raybaudi

              This:

              Set Field [Contact Management::Field Known Ticket Buyer[Contact Management::Field Known Ticket Buyer & ¶ & "0803 Aronoff;"] ]

              must look so:

              Set Field [ Contact Management::Field Known Ticket Buyer ; Contact Management::Field Known Ticket Buyer & ¶ & "0803 Aronoff;" ]

              You are actually entering a calculated value into the space reserved for the repeatition of the field ( and that is wrong ! ) 

              • 4. Re: Script to ADD a checked value, NOT REPLACE the field contents
                philmodjunk

                Once you get that working, you may want to consider this approach for doing the same thing:

                Set Field [Contact Management::Field Known Ticket Buyer ; If ( IsEmpty ( FilterValues ( Contact Management::Field Known Ticket Buyer ; "0803 Aronoff" ) ) ; List ( Contact Management::Field Known Ticket Buyer ; "0803 Aronoff" ) ; Contact Management::Field Known Ticket Buyer ) ]

                It doesn't add any value if 0803 Aronoff has already by selected for that field and also avoids adding a leading ¶ if the field was empty. Neither issue will keep the check box group from displaying the correct values, but the cleaner data in the field itself may be easier to work with in other circumstances.

                • 5. Re: Script to ADD a checked value, NOT REPLACE the field contents
                  philmodjunk

                  You have two specify buttons for any set field step. (and please try Raybaudi's suggestion first as it's simpler to put in place.)

                  Click the first specify and select Contact Management::Field Known Ticket Buyer from the list of fields that pops up. Click the second, lower specify button and type in all the text to the right of the semi colon. Do not type in the semi-colon, FileMaker will add that for you.

                  • 6. Re: Script to ADD a checked value, NOT REPLACE the field contents
                    philmodjunk

                    Nevermind my suggestion, you'd need a newer version of FileMaker.

                    Do you have a function called FilterValues available in your version?

                    • 7. Re: Script to ADD a checked value, NOT REPLACE the field contents
                      JeanneM

                      HOORAY! I got this to work! PhilModJunk, I didn't have the "List" function, so I used the Else Function. This is what I did and it works! (I think!)

                      Loop
                          If [Is Empty ( Database table you're using::The name of the field )]
                              Set Field [ Database table you're using::The name of the field; "Complete line of value in value list you want checked"]
                          Else
                              Set Field [ Database table you're using::The name of the field; & ¶ &
                      "Complete line of value in value list you want checked"]
                          End If
                          Go to Record/Request/Page [Next; Exit after last]
                      End Loop

                      (Sorry, I changed my actual variables into a descriptive text for future reference)

                      • 8. Re: Script to ADD a checked value, NOT REPLACE the field contents
                        JeanneM

                        NOW, how would I UN-CHECK something???

                        • 9. Re: Script to ADD a checked value, NOT REPLACE the field contents
                          philmodjunk

                          Good to see you are figuring this out. The one thing this doesn't cover is the possibility that this value has already been selected. That's what the IsEmpty ( Filtervalues.. part of the expression was checking for. Duplicate values won't cause a display problem as far as I know here, but you would need additional code to check for that possibility if you want to avoid it.

                          • 10. Re: Script to ADD a checked value, NOT REPLACE the field contents
                            JeanneM

                            PhilModJunk,

                            I read http://www.filemaker.com/help/html/func_ref3.33.49.html and still don't understand what FilterValues does. Does it narrow down the found set to records whose filtered values would match all the values in filterValues?

                            In your code, I don't understand why it's inside the IsEmpty function. Can you suggest how to use it in my code I pasted in the previous post?

                            I also suspect that understanding FilterValues could help me figure out how to write the code to uncheck a box?

                            THANK YOU!

                            • 11. Re: Script to ADD a checked value, NOT REPLACE the field contents
                              philmodjunk

                              FilterValues compares two lists of values and returns a list of the values found in both.

                              Filtervalues ( Checkboxfield ; "CheckBoxvalue" ) will return "CheckBoxValue" if that value was selected and will be empty if it is not.

                              Thus, IsEmpty ( FilterValues ( CheckBoxField ; "CheckBoxValue" ) ) is checking to see if the value has already been selected for this field.

                              It will help you understand what's happening "under the hood" if you temporarily place a copy of your check box formatted field next to the original field on your layout, but formatted as an Edit box instead of a check box group. You can then see what is actually taking place in the field when you check and uncheck boxes in this field.

                              Your field stores the selected values as text separated by returns. "Checking" a check box requires adding the correct text, correctly separated from exisiting text with a return character. That's what you've figured out to how do to thus far. To "uncheck" a box, you have to delete that value from this list of return separated values.

                              Here's one calculation that can do that:

                              Let ( L = Substitute ( ¶ & YourTable::CheckBoxField & ¶ ; ¶ & "CheckBoxValue" & ¶ ; ¶ ) ; Middle ( L ; 2 ; Length ( L ) - 2 ) )

                              Please note that substitute is case sensitive, so you need to be careful to use consistent capitalization throughout or this may fail to work.

                              • 12. Re: Script to ADD a checked value, NOT REPLACE the field contents
                                JeanneM

                                Hi, Phil,

                                I don't use the scripts on Filemaker Pro 8 (is my version) very much, so my eyes cross and I go into seizures when I try to understand them.

                                 

                                So...to make sure I am not adding text (checked values) when they have already been selected, my code I was using before seems insufficient? (Image below.)

                                So, I need to add

                                IsEmpty ( FilterValues ( CheckBoxField ; "CheckBoxValue" ) )

                                somewhere first?

                                Where do I add that? Right after Loop and before the If ?

                                 

                                P.S. I haven't tried to understand the un-check yet. Again, eyes crossing. Seizures ensuing. I have just figured out I could use Edit > Find & Replace... That seems to be doing okay...

                                • 13. Re: Script to ADD a checked value, NOT REPLACE the field contents
                                  JeanneM

                                  Drat, I also don't have a "IsEmpty" command. Or a FilterValues.

                                  I'm on FileMaker Pro 8.

                                  I could possibly get the latest version on discount (I use it for my non-profit org), but, I don't know if it's worth it. Thoughts?

                                   

                                  One more thing, I can't even recall what each of those command lines up there was for.

                                  I think the first If & Set Field was for If the field is empty (does If [Table::Field] just mean, "is empty" ???), to set the field as specified.

                                  The Else and second If and the Position script...I have no clue what that's for, it says to go to the next record.

                                  The Second Else, second Set Field - means to add my value...

                                  ???

                                   

                                  I tried to look up Position and I just couldn't even start to get it.

                                   

                                  Ugh. Sorry I'm an idiot.

                                  Thank you for your help!

                                  • 14. Re: Script to ADD a checked value, NOT REPLACE the field contents
                                    JeanneM

                                         Hallelujah, yes, years later, I have figured out what I think is pretty good.

                                         This accounts for fields that are already checked with the value you wish to have checkmarked and also makes sure not to add extra return carriages.

                                         THIS IS FOR FILEMAKER PRO VERSION 8.0

                                          

                                    Script to check an additional checkbox item on all found records

                                    (Will have to REPLACE TABLE & FIELD NAME in 5 places and VALUES in 3 places each time you use it)

                                          

                                    Loop

                                    If [ IsEmpty ( TABLE::FIELD ) ] 

                                    Set Field [ TABLE::FIELD; "VALUE" ] 

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

                                    Else

                                    If [ PatternCount ( TABLE::FIELD ; "VALUE" ) > 0 ]

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

                                    Else

                                    Set Field [ TABLE::FIELD ; TABLE::${FIELD} & ¶ & "VALUE" ]

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

                                    End if
                                    End if
                                    End Loop