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

# If Calculation

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

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

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

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

Robby

• ###### 4. Re: If Calculation

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

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

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

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

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

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

• ###### 10. Re: If Calculation

Thanks for all the help everyone.  It works perfect!

• ###### 11. Re: If Calculation

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

• ###### 12. Re: If Calculation

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

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.