13 Replies Latest reply on Apr 30, 2013 12:55 PM by mikebeargie

    If Calculation

    robby@brays.net

      Hello,

      I am trying to set up a calculation and am not sure which one is best suited for what I am trying to do here.

      In my database, I have production forms with radio buttons Yes, NO to show whether an Item is complete or incomplete. If it is complete, the user pressed the yes Radio button, if not, it automaticaly chooses NO upon creation of the record.

      Now, I am trying to set up another field that checks 2-3 radio buttons, and if all three are "YES" it displays "yes" If all three are not yes, it displays "NO"

      I am not sure if the "IF" calculation is the right one for the choice, or Radio buttons or check boxes should be used.

      Thanks

      Robby

        • 1. Re: If Calculation
          mikebeargie

          You can do a calculation field based on a Case() function.

           

          Case (

             field1 = "yes" and field2 = "yes" and field3 = "yes" ; "yes" ; //returns yes where all three fields are yes

             "no"

          )

           

          You can replace "case" with "if" above and it will work still, but "if" only tests for one statement, where "case" is more flexible, allowing for multiple statements, such as:

           

          Case (

            field1 = "yes" and field2 = "yes" and field3 = "yes" ; "yes" ; //returns yes where all three fields are yes

            field1 = "yes" and ( field2 = "no" or field3 = "no") ; "incomplete" ; //you can keep adding tests

          "no"

          )

           

          Case works as if you were chaining a bunch of ifs together. In your example above though, you just need to add "and" between your tests, IE field = "yes" AND field2 = "yes" AND etc...

          • 2. Re: If Calculation
            taylorsharpe

            The Case function is similar to IF, but it evaluates things in order and only gives a result based on the first positive test.  There are many times this is useful, but I think your case would be easier to just stick with the "IF". 

             

             

            I would do:

             

                 If (     field1 = "yes" and

                          field2 = "yes" and

                          field3 = "yes"  ;

             

                          "yes" ;

             

                          "no" )

            • 3. Re: If Calculation
              robby@brays.net

              Hey Thanks Mike and Taylor !  That worked.  Really appreciate the prompt help !

              Robby

              • 4. Re: If Calculation
                mikebeargie

                I've met a few developers now who are only using "if" as a script step, and no longer as a calculation engine function. I myself have been warming to the idea that typing 2 extra letters gives you a lot more payback in the future.

                 

                As noted, in my first example you can replace "case" with "if" and it's the same syntax, and works just fine.

                 

                There's nothing IF() can do that CASE() can't do, and CASE() has a lot more functionality for future expansion.

                • 5. Re: If Calculation
                  taylorsharpe

                  I guess "If" is easier for a new person to understand than Case.  I know the SQL language supports CASE even though I don't seem to see it used very often:

                   

                  SELECT

                     CASE

                        WHEN @Price IS NULL THEN 'Not yet priced'

                        WHEN @Price < color="#ff0000">THEN 'Very Reasonable Price'

                        WHEN @Price >= 10 AND @Price < color="#ff0000">THEN 'Reasonable Price'

                        ELSE 'Expensive book!'

                     END

                   

                   

                  Always an interesting argument on what is the best way to do things.  I know that FileMaker has not deprecated many functions and some functions that have been improved upon by better ones have not disappeared probably due to backwards compatibility. 

                   

                  Mike, you always have interesting comments <grin>

                  • 6. Re: If Calculation
                    ch0c0halic

                    I read some of the other responses but I thought I'd throw in a KISS solution.

                     

                    PatternCount ( Field_1 & Field_2 & Field_3 ; "Yes" ) = 3

                    • 7. Re: If Calculation
                      mikebeargie

                      Ah well, at least the "IF" function is valuable for new developers coming on from other languages that don't support case statements, although I can't really think of any I know off the top of my head that don't.

                      • 8. Re: If Calculation
                        mikebeargie

                        Length ( Field_1 & Field_2 & Field_3 ) = 9

                         

                        also works in this case (where value can only be YES or NO)

                         

                        Six to one, half dozen to the other?

                         

                        But i think Robby above is looking for a calculation field that returns "yes" or "no", These two calculations only work as tests inside of IF or CASE calculations, or in the IF / ELSE IF script step.

                        • 9. Re: If Calculation
                          ch0c0halic

                          Set the Display Format of the field to "Boolean". It will show "Yes" or "No".

                          • 10. Re: If Calculation
                            robby@brays.net

                            Thanks for all the help everyone.  It works perfect!

                            • 11. Re: If Calculation
                              mikebeargie

                              Don't you also have to set the output of the calculation field to be a number as well?

                              • 12. Re: If Calculation
                                sporobolus

                                on 2013-04-30 12:38 Mike Beargie wrote

                                Ah well, at least the "IF" function is valuable for new developers coming on from other languages that don't support case statements, although I can't really think of any I know off the top of my head that don't.

                                 

                                Python and AppleScript are a couple

                                 

                                to be pedantic, FileMaker has no Case statement … it has a Case() function

                                 

                                i use several languages, and though the syntax and behavior in different

                                languages is maddeningly variable, i think the case for Case/Switch versus If

                                is pretty strong

                                 

                                if we're trying to push the OP toward best practices how about the benefits of

                                using 1/0 rather than "yes"/"no" for values in logical fields? then the

                                calculation becomes simply

                                 

                                   field1 AND field2 AND field3
                                

                                 

                                • 13. Re: If Calculation
                                  mikebeargie

                                  Yes, that's a good push, and combines well with ch0c0halic's notes below. Thanks for the notes on applescript,  I forgot it only worked on if/else/else if, Never touched python though.

                                   

                                  I've had a couple newbie programmers though that I say the word "Boolean" to and all they here is the "Boo", as if it's a terrorizing mathematical equation. It's good to have a reminder every now that then that using booleans usually boils your coding/work down to the lowest possible denominator.