4 Replies Latest reply on May 7, 2013 9:09 AM by vlight

    Field calculation giving erratic results



      Field calculation giving erratic results


           I have two fields for data input, AGE and BIRTHDATE.  Sometimes the user knows a person's actual birthDATE, and can enter it. And sometimes they only know how old someone is, say 54, and they can enter that in the AGE field.

           Related to those two input fields, i have created 4 calculation fields (see screenshot showing the 4 calculations).

           I just noticed that there is an occasional problem with "Under30_BD_calc"  — the answer should be giving me the actual BIRTHDATE for anyone who is under 30 years old,  if there is a date in the BIRTHDATE field. 

           See the screenshot "ss-2-good"  which shows how this works well.

           But as seen in screenshot "ss-1-not working", for some reason this one is not working. Since the person's age is under 30 (6), AND there IS a date in the BIRTHDATE field, then i should be seeing that date in the field "Under30_DB_calc".

           Any ideas why it doesn't work consistently?  And how to fix it?


        • 1. Re: Field calculation giving erratic results

               I would guess that the check box "do not evaluate if all referenced fields are empty" check box has not been cleared for AgefromBD_calc.

               This should also be an unstored calculcation or it will not update to show a different age as time passes.

               And your BD calc can be off by 1 if today's date is < this year's birthday for a given individual.

          • 2. Re: Field calculation giving erratic results

                 per your suggestion, I have made them unstored.

                 however, your other suggestion, to uncheck "do not evaluate if all...are empty" -- i tried this;  it did not fix the problem;  but it did start to give me question marks in the field, and i don't want to see the question marks... i'd rather see the field be empty;  so i re-checked that option. 

                 Perhaps one more illustration might give you a better idea of what the problem is — see attached file for how these fields are displaying.

                 As you can see, the problem only shows up in the single digit ages (with the exception that one of the 2-year olds, showing a birthdate of 11/29/2011, actually shows perfectly!).  Does that give you any further ideas?

            • 3. Re: Field calculation giving erratic results

                   Yes, remove the quotation marks from around the numbers in your calculation. Putting them in quotes causes fileMaker to treat your numbers as text. As text, "200" < "5" is a true statement.

              • 4. Re: Field calculation giving erratic results

                     Thank you!  That solved the issue completely!