3 Replies Latest reply on Jun 29, 2009 10:05 AM by philmodjunk

    If

    frog22

      Summary

      If & Case function do not work with rounded numbers Filepro 9.0

      Description of the issue

      FileMaker Product(s) involved:FileMaker Pro 9.0v3 Operating System(s) involved:Mac 10.5.7 Detailed description of the issue:I am unable to use calculated fields that use the "round" function in functions "If" and "Case" Exact steps to reproduce the issue:1.  Create a database 2. Created Tables A & B.   3. In table A created fields "Height", "Weight", "Gender", "In Standards" 4. In table B created fields  "M Weight", "F Weight", "Height" 5. Created relationship between two tables Connecting Table A "Height" = Table B "Height" 6. Created code....If ( Round(Table A::Height; 0) = Table B::Height; If (Gender = "Male"; Table B::M Weight; If (Gender = "Female"; Table B::F Weight; "No Gender")))-Table A::Weight Expected Result: Given:  In table A there is a file created with the following values Gender         Height               Weight                  In standards   Male             60.5                  155                       Calculation field In table B there is a file created with the following values...M Weight             Height              F Weight160                       61                   180  Database Rounds Table A Height (Such as 60.5 rounds to 61) and matches the height from Table A with Table B.  Once the database finds a match, it then looks at the gender from Table A.  Because the gender is male it will pull the weight value from the "M Weight" from the Height file matching "61" (it would pull the value 160).  Finally, it would take the value found (160) and would subtract the weight value from Table A (155).  The value it should display should be 5 Actual Result:Blank Field Exact text of any error message(s) that appeared:N/A Any additional configuration information/troubleshooting that is relevant to the issue:1. The function works with non-rounded numbers (30, 60, 75).  This function also works using "Case" & non-rounded numbers.  It does not work with numbers that are rounded (55.5 rounded to 56, 76.5 rounded to 77, etc). 2.  I have tried creating a new field that rounds the "Height" first.  Then, write the calculation using this new field.  The results did not change.  The calculation would take place for non-rounded numbers and would not take place for the rounded numbers. 3.  The function is accurate when working with the non-rounded numbers. Any workarounds that you have found:N/A   I am a beginning/intermediate user.  I know how to use functions, relationships, etc, but may not always know all the rules.  This function is in a more complicated database, but none of the data fields are being manipulated in any other way except what is described above. Any help that can be offered would be greatly appreciated! Kevin 

        • 1. Re: If & Case function do not work with rounded numbers Filepro 9.0
          philmodjunk
            

          You have a problem with your relationship not the If and case functions.

           

          5. Created relationship between two tables Connecting Table A "Height" = Table B "Height"

           

          This relationship will match records between table A and table B only if TableA::Height and TableB::Height are exactly equal.

           

          If TableA::Height = 60.5, then there is no matching record in Table B and null is returned to your expression where I've styled the text in bold:

           

          If ( Round(Table A::Height; 0) = Table B::Height; If (Gender = "Male"; Table B::M Weight; If (Gender = "Female"; Table B::F Weight; "No Gender")))-Table A::Weight

           

          To make this work, create a calculation field in Table A: Round(Height,0) and use this field in your relationship in place of TableA::Height.

          • 2. Re: If & Case function do not work with rounded numbers Filepro 9.0
            frog22
              

            I tried everything except that.  Thank you, the function now works.  Is there an easier way to write the calculation that does not require creating a new field for the rounded calculation.  Possibly changing the relationship in a way where it would recognize the rounded number?  It's not a big deal to get an answer....Knowledge is power.  Anyway, thank you for your help!  I greatly appreciate it!

             

            Kevin 

            • 3. Re: If & Case function do not work with rounded numbers Filepro 9.0
              philmodjunk
                

              You could put a pair of Height fields in table B and use the inequality operators instead of '='.

               

              TableA::height > TableB::Minheight

              AND TableA::height < TableB::Maxheight

               

              Whether that's really better than simply using a calculation field to round the values depends on the design details of your data base.