4 Replies Latest reply on Jul 13, 2015 8:53 AM by philmodjunk

    Sort order issue when calculated age reaches 11 months

    scottlynch

      Title

      Sort order issue when calculated age reaches 11 months

      Post

      I'm using this calculation to determine a persons age.

      GetAsText ( Year ( Get ( CurrentDate ) )  - Year ( Date of Birth) - If ( Get ( CurrentDate ) <  Date ( Month ( Date of Birth ) ; Day ( Date of Birth ) ; Year ( Get ( CurrentDate ) ) ) ; 1 ; 0 ) ) & " Years, " & GetAsText ( Mod ( Month ( Get ( CurrentDate ) ) - Month ( Date of Birth ) + 12 - If ( Day ( Get ( CurrentDate ) ) < Day ( Date of Birth ) ; 1 ; 0 ) ; 12 ) ) & " Months, "

      I then have a simple list that sorts the people into age order. The issue I'm having is that when a person reaches their age in years and "11months"  that particular person falls out of sort order. Im assuming that there's something wrong with the calculation, but i copied it from FileMaker help files? 

      This example is copied from my list.

      53 years 10 months
      34 years 11 months
      31 years 11 months
      25 years 11 months
      52 years 5 months
      49 years 9 months
      49 years 4 months

      Thanks in advance.
       

       

       

       

       

        • 1. Re: Sort order issue when calculated age reaches 11 months
          schamblee

          The formula is correct.  You need to sort on the date of birth field then your ages will be in the correct sort order.

           

          • 2. Re: Sort order issue when calculated age reaches 11 months
            philmodjunk

            Your sort order shown is correct for text values. To expand on Stacey's answer, you need a number field (Or calculation field with a number result type) with just the age in months and another of the same with just the age in years. You can then use these two fields in order to sort your ages into correct order. (These sort fields need not be visible on your layout.)

            • 3. Re: Sort order issue when calculated age reaches 11 months
              scottlynch

              Thank you both very much.

              Stacy - I'd have never of thought of using the DOB field as source for the sort. I assumed it would of been the cAge calculation field, as that is what returns the years and month(s) past current date.

              Phil - My cAge calculation field does return a number result. The DOB field is obviously a date. Should I change my fields to replicate what you've described. Because at present, cAge returns both years and months together within one field. They are not separated as you specify. 
              Will i run into future issues leaving it as is. Or is it a question of breaking things downs to the smallest components makes good database design?

               

              • 4. Re: Sort order issue when calculated age reaches 11 months
                philmodjunk

                My cAge calculation field does return a number result.

                What I see is not a number result, but a text result. The result type, may be number but the resulting VALUE is text and cannot be treated both as text and two different number values in the same field when it comes to sorting and calculations.

                I would leave this calculation alone and if I need accurate sorting/grouping by year and month, keep two separate fields for that purpose. If all you need is the correct order Stacey's suggestion to sort by the original data of birth is the simplest approach. But if you need to group records by year, month in a summary report, for example, you would need the separate sort fields that I have described. (This is something easily added at a later point in time if you don't need it now but might need it in the future.)