12 Replies Latest reply on Oct 8, 2015 4:20 PM by keywords

    Filemaker if then formula

    ThomasFileMaker

      Another newbie formula question.

       

      Here is what I am trying to do.

      If PRIMARY or LIFT not empty then OVERHEAD = 1

       

      Thanks, for any and all help.

        • 1. Re: Filemaker if then formula
          siplus

          Overhead, calc field, with formula  PRIMARY or not IsEmpty(LIFT) ?

          • 2. Re: Filemaker if then formula
            ThomasFileMaker

            So the formula is not isempty(Primary) or not isempty(Lift)?

             

            How does it know to put a 1 in Overhead?

             

            Thanks. 

            • 3. Re: Filemaker if then formula
              Nehme

              In a calculation, it is better to use "Case" instead of "If"

              If I am not wrong, the "If" allows only a single condition.

               

              I suggest this calculation to be inserted in the "OVERHEAD" field :

              Case (

              not IsEmpty ( PRIMARY ) ; 1 ;

              not IsEmpty ( LIFT ) ; 1 ;

              "" )

              • 4. Re: Filemaker if then formula
                siplus

                if you want to feel more secure, know that

                 

                getAsBoolean(false or not 1)  gives a numerical value of 0, and from this you can derive a lot.

                • 5. Re: Filemaker if then formula
                  ThomasFileMaker

                  Can I put this code in the auto-enter tab, calculated value, Specify?  Would I check or uncheck the "Do not replace existing value of field (if any)"?

                   

                  Thanks.

                  • 6. Re: Filemaker if then formula
                    erolst

                    Nehme wrote:

                     

                    In a calculation, it is better to use "Case" instead of "If". If I am not wrong, the "If" allows only a single condition.

                     

                    If you only have a single test, then your choice of conditional doesn't make any difference; so there isn't a notion of “better” in the absolute – there's only “better suited to the job”.

                     

                    Having said that, Case() can do everything that If() can, and more, so some people use it exclusively so as not have to think about it.

                     

                    Also note that neither Case() nor If() require an empty default result.

                     

                    ThomasFilemaker wrote:

                    How does it know to put a 1 in Overhead?

                     

                    siplus's calculation works because

                     

                    not IsEmpty ( PRIMARY ) or not IsEmpty ( LIFT )


                    returns a 1 if either field is filled, because 1 is the numerical value of True (which is the result or OR if either predicate is True). On the other hand, it returns 0 is neither is True, which may not be desired.


                    So I would try


                    Case ( not IsEmpty ( PRIMARY ) or not IsEmpty ( LIFT ) ; 1 )

                    • 7. Re: Filemaker if then formula
                      Nehme

                      Yes you can put it as auto-enter calculation

                       

                      Check or Uncheck the Do not replace ...: This depends on what you want to do

                      It you which to update your value, uncheck it

                      • 8. Re: Filemaker if then formula
                        ThomasFileMaker

                        Is there a way to check multiple answers as correct?  I think you all nailed it, in different ways. 

                        • 9. Re: Filemaker if then formula
                          justinc

                          Nehme wrote:

                           

                          If I am not wrong, the "If" allows only a single condition.

                           

                          You can include multiple conditions in an IF statement, there is just one 'test' clause.  E.g.:

                           

                          If ( not isempty(Primary) and not isempty(Lift) ; 1 ; 0 )

                           

                          With Case(), you can have multiple test clauses, each one could also have multiple conditions in it.

                          • 10. Re: Filemaker if then formula
                            justinc

                            erolst wrote:

                             

                            Also note that neither Case() nor If() require an empty default result.

                             

                            They may not require that result, but they always INCLUDE a result.  If you don't provide one it will provide <null>, "", <empty>...  (whatever term you like to call it).  For example:

                             

                            Let ( a = 0;

                                Case(

                                    a = 1; 1;

                                    a = 2; 2;

                                ) 

                            )

                             

                            This returns empty/NULL/"".  I think it's always a good idea to supply an explicit 'Else' case for either function.

                            • 11. Re: Filemaker if then formula
                              erolst

                              justinc wrote:

                              For example:

                              Let ( a = 0;

                                  Case(

                                      a = 1; 1;

                                      a = 2; 2;

                                  )

                              )

                               

                              This returns empty/NULL/"".  I think it's always a good idea to supply an explicit 'Else' case for either function.

                               

                              That, of course, is your prerogative.

                               

                              Personally, I find it verbose (in the tradition of the perennial argument between the schools of implicit and explicit coding), though it may have a calming effect on novices …

                              • 12. Re: Filemaker if then formula
                                keywords

                                Some interesting discussion here on differences between If() and Case(). Just note that:

                                     If ( not isempty(Primary) and not isempty(Lift) ; 1 ; 0 )

                                is not the same thing as:

                                     Case (

                                          not IsEmpty ( PRIMARY ) ; 1 ;

                                          not IsEmpty ( LIFT ) ; 1 ;

                                          "" )

                                 

                                If BOTH fields contain a value, the first will return a 1. If EITHER field contains a value the second will return a 1, but you will not know whether (1) both contain a value, or, if only one of them does, which one is empty.

                                 

                                Remember that Case is hierarchical: it will only test until it gets a True result, then it will ignore the rest. If you want to be more analytical, you would need something like:

                                 

                                     Case (

                                          not IsEmpty ( PRIMARY ) and not IsEmpty ( LIFT ) ; 1 ;

                                          not IsEmpty ( PRIMARY ) ; 2 ;

                                          not IsEmpty ( LIFT ) ; 3 ;

                                          "0" )