2 Replies Latest reply on Mar 9, 2015 6:50 AM by RobertT

    Totaling multiple values in a field for checkbox field.. IF or Case function

    RobertT

      Title

      Totaling multiple values in a field for checkbox field.. IF or Case function

      Post

      I created a checkbox field  for list of medical codes that equal a dollar value of cost.  Example  76856= 10 

      76881=25 and  76800=35   is there a way with the If( function) or the Case(function) to be able to add two or more values together.. i tried using "+"  but selecting more than one value equals a zero.  All the values work if I only select one.

      Thanks for any help

       

       

        • 1. Re: Totaling multiple values in a field for checkbox field.. IF or Case function
          philmodjunk

          This probably shouldn't be a single text field formatted with check boxes but rather a set of related records. Then a field in each record can record the numeric value and Sum ( RelatedTable::NumberField ) can return a total.

          The problem with using a single check box field is that when multiple boxes are selected, the value of each check box is inserted inthe field, separated from other values by a return character. This makes it tougher to "add up" the value of just those selected values.

          On method is to use the FilterValues function to test for whether a given check box is selected or not. Say your check box uses a value list of three values: "Red"; "blue" ; "yellow".

          You might set up something like this:

          Let ( CB = YourCheckBoxFieldHere ;
                 If ( Not IsEmpty ( FilterValues ( CB ; "red" ) ) ; 1 ; 0 ) +
                 If ( Not IsEmpty ( FilterValues ( CB ; "blue" ) ) ; 5 ; 0 ) +
                 If ( Not IsEmpty ( FilterValues ( CB ; "yellow" ) ) ; 10 ; 0 )
               )

          But note how much more complex of a solution this is and how you'll have to redesign this calculation every time the list of check box values or their associated numeric values need updating in the future--neither of which is much of a problem if you use related records in a portal instead of your check box field.

          And it's possible to list your different values in a portal that looks and acts like a check box, but which now has both a scroll bar and which can be sorted in ways not possible with the standard check box field. For multiple examples of this approach, see:

          "Adventures in FileMaking #2 - Enhanced Value Selection".

          • 2. Re: Totaling multiple values in a field for checkbox field.. IF or Case function
            RobertT

            thanks, I only have one field that can require multiple values. The rest are either yes or no values