6 Replies Latest reply on May 8, 2013 1:29 PM by liyangao

    Question about a logical function in calculation

    liyangao

      I have a field defined as a number type, which we call it "test". Then I define a calculation field test01 as if (0<test<200;1;0). It went alright without any warning or error message from calculation window. However, the calculation field test01=1 when test=1400. What is wrong? Did I miss anything?

      Thank you very much for your help!

      Liyan

        • 1. Re: Question about a logical function in calculation
          mikebeargie

          It should be:

           

          if ( 0 < test and test < 200 ;1;0)

          • 2. Re: Question about a logical function in calculation
            liyangao

            Mike, thank you for yout reply. I tried to use one-side inequality and it works.

             

            But does that mean FileMaker does not allow for two-sided inequalities?

            Liyan

            • 3. Re: Question about a logical function in calculation
              mikebeargie

              Filemaker CAN solve 2 sided inequalities in certain situations, just not in the context you're trying to use.

               

              You need a boolean response ( yes/no or as filemaker returns it: 1/0 ) to use an If() calculation, a two-sided inequality does not return a boolean response.

               

              With what you tried the first time, it only ran through as much of your equation as it needs to get a boolean response ( 0<1400 ) and cutoff the rest, which is why you are required to test for both with the AND operator in between.

              • 4. Re: Question about a logical function in calculation
                liyangao

                Mike, thank you again for your explanation. One more question, how about an case() calculation, is it the same as the if() calculation?

                Would you like to elaborate the situations where FileMaker will handle 2-sided inequalities so that I know the differences? I appreciate your prompt responses.

                Sincerely,

                Liyan

                • 5. Re: Question about a logical function in calculation
                  mikebeargie

                  Case() is pretty much the same as If(), with the exception that it runs through all your statements until it returns a positive boolean, or if there is no positive boolean, whatever your last result is.

                   

                  What is your use for compound inequalities? It might help if you had an example situation where it was required. I can't think of any programming sense where you would need it to be compounded rather than the split inequalities I listed above.

                   

                  Filemaker doesn't handle native math variables in it's calculation engine, so a compound inequality, such as "-3 < 2x < 5" would error out, since filemaker can't solve for x.

                   

                  If you're looking to use filemaker to do complex algebra for you, it's probably the wrong program to use.

                  • 6. Re: Question about a logical function in calculation
                    liyangao

                    FileMaker allows for the range search for 0...200, which is the same as  0<the variable<200. So I thought it will handle the compound inequalities. Probably the range search is handled differently from the calculations.

                     

                    I understand that complex algebra is not the main purpose of FileMaker. I run into this when I was trying to do a complicated search criteria.

                     

                    Thank you for your explanation. It might help if the calculation window will throw out a warning for this kind of entries instead of letting it go and errors out later.

                     

                    Thanks again for your replies.

                    Liyan