11 Replies Latest reply on Jun 16, 2014 2:01 PM by philmodjunk

    If function problem



      If function problem


           I must be doing something ridiculously stupid, but in the following equation, the alternate, male, equation is always selected, regardless of whether Patient::sex=f. Patient has a 1 to many relationship with Calc where the calculation field resides:



           The "f" or "m" are selected on Patient, through a radio button; it is a text field.

        • 1. Re: If function problem

               Have you already tried defining the calculation simply as Patient::Sex to make sure the relationship is working correctly? You could define it thus to make sure:


               Where the field will show -1 if it is unable to get Patient::Sex = "m" or "f".

          • 2. Re: If function problem
            Markus Schneider

                 Patient is a related Table/TO -> that relation is correct?


                 (Edit: Typos)

            • 3. Re: If function problem

                   Is the context of the calculation correct? (Dropdown menu at the top of the calculation window)


                   Edit: content must be context 

              • 4. Re: If function problem

                     Make sure that Patient::Sex is a field of type text and not number.

                • 5. Re: If function problem

                       I know the parentheses are unnecessary, but it cleans it up a little.  With a simple IF/THEN calculation, it defaults to the "THEN" value if the field is blank. With this CASE calculation, it defaults to a "" (Blank) until a value is entered.


                       Case (

                                       Patient::Sex="f" ; (10*kg) + (6.25*cm) – (5*Age) - 161 ;

                                       Patient::Sex="m" ; (10*kg) + (6.25*cm) - (5*Age) + 5 ;


                  • 6. Re: If function problem

                         Answering the above:

                         - Markus: Patient is related to Calc in a 1 to many. I'm not sure what you mean by 'edit typos'; can you point them out?

                         - hbrendel: Edit: content must be context; I'm unsure of your meaning.

                         - PhilModJunk: Patient::Sex is definitely a text type field.

                         - W Brent Simon: Your solution does work. Thank you.


                         However, I'm still perplexed as to why my original equation doesn't work and I'd hoped to nest a stack of equations using 'if'.

                    • 7. Re: If function problem

                           Edit: content must be context; I'm unsure of your meaning.

                           Disregard this. It was an explanation of my edit in the top line
                      • 8. Re: If function problem

                             Strangely, I've just got my original 'if' function working correctly.

                             What criteria would make you choose: Case over If?

                        • 9. Re: If function problem

                               If is a simplified from of the Case function. If, one day, FileMaker announced that the next version of FileMaker would not use the If function, only case, it would bother me little if any, but since I have both functions, I tend to use If when a single Boolean (True or False) expression is all I need to produce on of two results. If I need more than one such expression, I use Case instead, but this is strictly a preference on my part as I could use Case every where that I use If and the only difference would be the word "case" taking the place of the word "if" in the defined calculation.

                          • 10. Re: If function problem

                                 There is no inherent advantage/ disadvantage of using 'if', when you will be mixing in 'and', 'or' etc into the 'switches'?

                            • 11. Re: If function problem

                                   It depends on the Boolean expressions that you need to use. Sometimes multiple Boolean expressions, each with their own result type make for simpler Boolean expressions and then Case makes sense as a way to use those multiple Expressions. That might make for simpler expressions where you would otherwise use Or but not when you need to use And.

                                   If ( A = B or C = D ; True Result Here ; False Result Here )

                                   Could be replaced by:

                                   Case ( A = B ; True Result Here ;
                                              C = D ; True Result Here ;
                                              False Result Here

                                   That produces simpler Boolean Expressions (A=B, C = D), but you then have to repeat the same "True Result" expression more than once.