8 Replies Latest reply on Mar 29, 2013 9:08 AM by philmodjunk

    Calc to deselect one choice when any others are selected

    vlight

      Title

      Calc to deselect one choice when any others are selected

      Post

           In FM11, I have a field that is using checkboxes for choosing among 4 options from a value list, where the 4th option is "No".  

           I also have that field defined with an auto-calc to select "No" whenever a new record is created.

           Later, the User can choose any (or all) of the remaining 1-3 values.

           When they do, however, I would like the "No" selection to be automatically deselected.

           How would I accomplish that?

        • 1. Re: Calc to deselect one choice when any others are selected
          JimMac

               You are mixing Logic.  It is "No" OR  {3 options using AND logic].  You might remove the "No" to another single item list to set and use a script trigger to Activate the 3 option check boxes.

               Jim...

          • 2. Re: Calc to deselect one choice when any others are selected
            philmodjunk

                 If ( Not IsEmpty ( FilterValues ( Self ; "No" ) ) and ValueCount ( Self ) > 1 ;
                      Let ( tempList = Substitute ( ¶ & self & ¶ ; ¶ & "No" & ¶ ; ¶ ) ; Middle ( TempList ; 2 ; Length ( TempList ) - 2 ) ) ;
                      Self
                     )

                 Be sure to clear the do not replace existing values check box.

            • 3. Re: Calc to deselect one choice when any others are selected
              vlight

                   I've replaced my original auto-enter calc with the one you offered, and cleared the "do not replace existing values" checkbox, and unfortunately it's still not working.  So i'm not sure what is missing.  And, is the auto-enter calc the wrong place to put it?

                   (Also, to be clear, I realized that i actually have 5 options in that value list, with the 5th one being "No."  I had originally said it was the 4th & final one. Thinking that may have caused the calc not to work properly, i went ahead and temporarily deleted one of the other options... but it still wasn't working, so i've put it back to the original 5 options.)

              • 4. Re: Calc to deselect one choice when any others are selected
                philmodjunk

                     I copied and pasted the exact expression shown into a test database before I posted it and it works just fine. If No is selected and I click any other check box, No is cleared even as the other value is selected. I can then select additional values but am locked out of selecting No unless I first clear all other check boxes. Is that what you needed?

                     One possible issue is that substitute is case sensitive. The text from "No" needs to be capitalized exactly like you have it in the value list. If you have "NO" in the value list, you must use "NO" in the calculation, "No" or "no" won't work.

                • 5. Re: Calc to deselect one choice when any others are selected
                  vlight

                       thank you -- In essence your description is what i want -- but  with one difference (which was my original auto-calc) -- on record creation, i want the auto-calc to select "No" -- and then, later, the user can select any of the other options, but when they DO, then i want the selection "No" to be automatically DE-selected.  

                       How should the auto-calculation look... to include the automatic selection of "No" first, and THEN auto-deselect when any other options are selected?

                  • 6. Re: Calc to deselect one choice when any others are selected
                    philmodjunk

                         It should look exactly as I have posted it. You can copy and paste this expression right into the Specify calculation dialog.

                         To auto-enter that default value of "No", type this text into the Data box on the auto-enter tab. And as I have previously cautioned, make sure that you have used any capitalization consistently in each case--both in the Data box, the value list and the auto-enter calculaton.

                    • 7. Re: Calc to deselect one choice when any others are selected
                      vlight

                           Thank you!  Typing No into the Data Box was the missing piece i needed.  It works perfect for new records.  

                           The only hitch that i discovered is that it wasn't working quite right for records already created, with a "No" selected through my earlier auto-enter calc.  In order to make it work on those records, i had to manually de-select the "No" and then reselect it.  Then your new auto-calc works on it perfectly when selecting other values.   (thank goodness i didn't have many of these existing records to change)

                           Thanks for sticking with me.  I appreciate it!

                      • 8. Re: Calc to deselect one choice when any others are selected
                        philmodjunk

                             Yes, changing an auto-enter calculation does not automatically update the data in this field in existing records. You can, however, use Replace Field Contents with a copy of the calculation (have to replace "self" with the name of the field), to update them.

                             Much of the time, you can also use this trick: Updating values in auto-enter calc fields without using Replace Field Contents