6 Replies Latest reply on Apr 11, 2011 11:18 AM by philmodjunk

    Small Coding Problem



      Small Coding Problem


      I am going crazy trying to figure this out.  Numbers 1, 2 and 4 work perfectly, but 3 just comes up blank when I enter data within the parameters.  Can anyone help me.  I'm sure it is something simple.

      Thank you.

      If(LT < 94 and DB > 100 and RE = "NIL", "4", If((LT >= 94 and LT <= 149) and (DB >= 51 and DB <= 100) and RE = "SLOW", "3", If((LT >= 150 and LT <= 200) and (DB >=  21 and DB <= 50) and RE = "MEDIUM", "2", If(LT > 200 and DB <= 20 and RE = "FAST", "1", "" ))))

        • 1. Re: Small Coding Problem
          Hi, You need to provide more "real world" info. What are you trying to do? Show us what Db and LT mean, for example. These are not fields or variables recognized by Filemaker unless they all reference the same table. What are numbers 123 etc? Edit: I just noticed you're using commas instead of semicolons. This is not acceptable. Read up on the syntax of your calc. RW
          • 2. Re: Small Coding Problem

            Actually, commas work just fine. Back in the old days commas where what you used in all calculations. I've found that when I forget and use a comma, FileMaker automatically converts it into a semi colon.

            Instead of nesting If functions inside of if functions, try using the case function as it will be much easier to work with:

            Case (LT < 94 and DB > 100 and RE = "NIL" ; "4" ;
                     (LT >= 94 and LT <= 149) and (DB >= 51 and DB <= 100) and RE = "SLOW"; "3";
                     (LT >= 150 and LT <= 200) and (DB >=  21 and DB <= 50) and RE = "MEDIUM" ; "2" ;
                     LT > 200 and DB <= 20 and RE = "FAST" ; "1" ;
                     "" )

            "Numbers 1, 2 and 4 work perfectly, but 3 just comes up blank"

            What values of LT, DB and RE did you try?
            In your original expression and my version with Case, LT = 95, DB = 52, RE = "Slow" should return "3".

            • 3. Re: Small Coding Problem

              Thank you for taking the time to answer me.

              I am trying to determine the hazard classification of volatile liquids.  LT = Liquid Temperature; DB = Degrees (F) below boiling point and RE = Relative Evaporation Rate.  I am determining the number portion of the hazard classification.  I am having no problem with the letter portion and it is programmed the same way.  So when done, it would look like: A-1, B-2, etc.

              All are Text fields.  I have tried just using the LT variables alone, same result. I have tried to use just the DB variables alone, same result.  I have tried the Case statement, same result.

              I have tried using different versions of Filemaker, same result.

              This is making no sense to me.  I hope this helps.  Real bad juju on this one.

              Thank you again.

              • 4. Re: Small Coding Problem

                Where did you take the values of your calculation ?

                Try with Xylene: it is into the 3 Hazard Class, has RE 0.6 ( Slow ) and BP = 284°F


                doesn't fit any criteria of your calculation.

                • 5. Re: Small Coding Problem

                  The value ranges come from Title 8 California Code of Regulations,  Section 5154, Table V-8.  That's what I have to work with. This table has not been changed since 1975 and that has to come from the California Legislature.  Lotsa luck.

                  Anyway, the PEL for xylene is 100 ppm so that makes it hazard classification B.  The number portion of the classification comes from the temperature of the xylene as it is currently being used.  Yes, 284 deg. is the boiling point of xylene, but the formula is looking for the number of degrees BELOW the boiling point that the current temperature represents.

                  Also, I solved my own problem.  Variables LT and DB were text  variables.  As soon as I changed them to number, voila, it works  perfectly.

                  I knew it was somthing simple.

                  Thanks to everyone who answered or even thought about my problem.

                  • 6. Re: Small Coding Problem

                    There are also ways to replace this calculation with a look up table so that if the powers that be change the categories, you can simply update the lookup table instead of having to redefine your calculation.