7 Replies Latest reply on Feb 25, 2011 10:27 AM by philmodjunk

    calculation help (checkboxes)



      calculation help (checkboxes)


      Trying to write a case statement and it just gives me nothing. What is wrong? any help would be appreciated

      Case ( goal_type = "RC"  and amount_dbe_rc_total_paid > ${amount_goal_fed$} ; ${amount_goal_fed$} ; goal_type  = "RC"  and amount_dbe_rc_total_paid  < ${amount_goal_fed$} ; amount_dbe_rc_total_paid )

      Type goal="RC" is a field with a value list set to checkboxes. I want to to grab dollar amounts when that check box is grabbed. This is where I thought some of my trouble might be coming from is the checkboxes.


        • 1. Re: calculation help (checkboxes)

          Check boxes are designed to hold multiple values.  If you only want one value then you'd use a radio button.  If you are looking for the case ' goal_type = "RC" ' then the check box will have to have only that in it.  You could try that for a record with only the RC value checked.  Or change it to be:

          Patterncount ( goal_type ; "RC" ) > 0

          Why are you using curly braces?  And does the use of the $ sign not upset FM's idea of what are variables?

          • 2. Re: calculation help (checkboxes)

            Check boxes store each selected value in a list separated by return characters. The order in which they are listed is the order in which the user selected them by clicking check boxes. You can watch this take place as a test if you put a copy of your check box field next to the original, but change it back to edit box format. Then click various check boxes in the group and watch how the values change in the Edit Box formatted copy of the field.

            To check and see if a specific value was selected in a field formatted with a group of check boxes, use the FilterValues function:

            Case ( not IsEmpty ( FilterValues ( goal_type ; "RC" ) ) and amount_dbe_rc_total_paid > ....

            • 3. Re: calculation help (checkboxes)

              Hi Kilbot Laughing

              $ is a reserved character to FM and it cannot easily be used in calculations which FM will tell you immediately after field creation.  It adds the braces so the field is usable but it increases the complexity of working with those fields.  Also if you plan on sharing your data with MS SQL, MySQL, Access, Oracle etc then you increase the risk of problems.  Even beginning a field with a space will cause issues.  Best to stay with generic text, underscore and numbers.  It is therefore suggested that you drop the $ from your field names.

              PatternCount(), when pulling a value from a list, can produce false positives if any other value contains ‘rc’.  It seems that the logic plays like this:  If the field contains RC then use the minimum value of the two amounts, i.e. review your existing calc ...

              goal_type = "RC"  and amount_dbe_rc_total_paid > ${amount_goal_fed$} ; ${amount_goal_fed$} ;

              goal_type  = "RC"  and amount_dbe_rc_total_paid  < ${amount_goal_fed$} ; amount_dbe_rc_total_paid )

              If this is the case, another way to approach your calculation could then be:

              Case (
              not IsEmpty ( FilterValues ( goal_type ; "RC" ) )  ;
              Min ( amount_dbe_rc_total_paid + 0 ; amount_goal_fed + 0 )

              • 4. Re: calculation help (checkboxes)

                Okay I'm listening but still having issues.

                1) I changed to radio buttons because I do not want the user to be able to choose multiple items.

                I re-wrote the calculation but it just returns a blank or amount_goal_fed_dollars. Need some smartening up, help....

                Case (

                not IsEmpty ( FilterValues ( goal_type ; "RC" ) ) and amount_dbe_rc_total_paid  > amount_goal ; amount_goal_fed_dollars;
                not IsEmpty ( FilterValues ( goal_type ; "RC" ) ) and amount_dbe_rc_total_paid <  amount_goal ; amount_dbe_rc_total_paid *  amount_goal_percentage



                • 5. Re: calculation help (checkboxes)

                  With single values only due to your radio button format, you no longer need the filtervalues function. The purpose of that function is to successfuly extract one value from a list of multiple, return separated values. You can simple test with field = value syntax. (Since it's possible to hold down the shift key and select multiple radio buttons, cautious programmers will use the test anyway just in case.)

                  Also, check to be sure that the paid and dollars fields are either number or a calulation field that returns number. If either of these are text it can affect how the inequalities evaluate.

                  • 6. Re: calculation help (checkboxes)

                    Phil said, "Also, check to be sure that the paid and dollars fields  are either  number or a calulation field that returns number. If either  of these are  text it can affect how the inequalities evaluate."

                    Actually  it doesn't.  Numberic calculations perform the same on text fields but I  agree that fields should reflect the proper data type to keep the  fields 'honest.'

                    Kilbot, are you creating a true calculation field or are  you creating a number field with an auto-enter calculation?  You need a  true calculation if you expect the results to change through all the records for you automatically.

                    Is there a reason that you didn't use my calculation?  I see that you added *  amount_goal_percentage.   Should that be applied against the result of the original calculation  (whichever of those two fields is the lesser amount) or only applied  against amount_dbe_rc_total_paid?

                    I suspect that we may not have all the information here.  It would be helpful if you could upload your file for us to review.

                    • 7. Re: calculation help (checkboxes)

                      If these are text "2" > "10" is a true statement.