5 Replies Latest reply on Jul 18, 2016 7:24 AM by globe11123

    Table calculation problem

    globe11123

      1.png

      I'm using the above calculation to differentiate the units in our system. Anything after serial number 19653 and the given product codes will be a revision Unit.

       

      For some reason Revision is showing up on serial numbers less than 19653. What's wrong with this calculation?

       

      Is there a better way to do this IF statement?

        • 1. Re: Table calculation problem
          PeterWindle

          if ( Units::Serial Number > 19653 and Units::Product Code ="blah" or

          Units::Serial Number > 19653 and Units::Product Code ="blah" or

          Units::Serial Number > 19653 and Units::Product Code ="blah" or

           

          etc...

           

          or even better might be with a case statement

           

          case(

          Units::Serial Number > 19653 and Units::Product Code ="blah" , "Revision",

          Units::Serial Number > 19653 and Units::Product Code ="blah" , "Revision",

          Units::Serial Number > 19653 and Units::Product Code ="blah" , "Revision",

          "")

          • 2. Re: Table calculation problem
            globe11123

            Ah thanks, I used the case statement seems to have done the job!

            • 3. Re: Table calculation problem
              globe11123

              1.png

              For some reason the code still isn't working correctly. As you can see in the image a unit with the serial number 8027 is still showing up as a revision.

               

              2.png

               

              Here is the calculation that I am using currently. I can't understand why a number less than can appear.


              One thing that might be messing up is that Serial Number is actually a Text Field as some of the units end with a T i.e. 4000T and also R200 etc.

              • 4. Re: Table calculation problem
                coherentkris

                Case (

                Units:: Product Code = "GSE434G" and GetAsNumber ( Units:: Serial Number) > 19653....

                 

                 

                Evaluation a text string with an operator against a numeric value will always throw you if you dont know how FM evaluates the expression. Filemaker treats the string as a series of characters and then compares the result to the number.

                if you do some investigation you will see that code ("abcde" ) returns 10100100000990009800097 which is what it would be using in an evaluation against a number

                 

                Furthermore this calc can be improved with the Let function

                 

                Let (

                _sn = GetAsNumber ( Units:: Serial Number ) ;

                Case

                (

                Units:: Product Code = "GSE434G" and _sn > 19653...

                )// close case

                )// close let