9 Replies Latest reply on Mar 26, 2010 5:29 AM by Pebbles

    Calculating a date with a future age

    Pebbles

      Title

      Calculating a date with a future age

      Post

      Hello, I am using FM 10 on Windows XP. I am fairly new at this program but I have created a one to many relational database with multiple fields.  

      What I need now is to calculate a date based on a future age of a child. 

      For example I have a EOE date (End of Eligibility) on my parent table.  I need to create a EOE date on the child's table based on when the child turns 22 yrs of age.  In other words I want to calculate the future date of when the child turns 22 yrs of age based on the end of eligibility date on the parents table.  

       

      The calculation for the EOE on the parents table was simple this is what I have for that: 

      (three QAD + 1095) which is basically the qualifying arrival date plus 3 yr. 

       

      Any help would be appreciated. 

       

      Thank you

        • 1. Re: Calculating a date with a future age
          ninja

          Howdy Pebbles,

           

          Would DOB happen to be one of the fields you have on your parent table?  Could you just add 22 years to that field?

          • 2. Re: Calculating a date with a future age
            Pebbles

            Hello Ninja, 

             

            The DOB field is on the child table with a portal on the parent table.  

            • 3. Re: Calculating a date with a future age
              ninja

              Howdy,

               

              This may seem like a "duh" question, but I don't know all the details of your dbase's function...

               

              Could there ever be more than one child record per parent record?

               

              If not, what is the purpose of the child table?

              If so, how do you distinguish which child record the 22yrs should be based upon?  We can then use that distinction to get your calc done.

              • 4. Re: Calculating a date with a future age
                ninja

                Pebbles wrote:

                 In other words I want to calculate the future date of when the child turns 22 yrs of age based on the end of eligibility date on the parents table.  


                As I reread your OP, this seems to be the crux of it.  How is the child turning 22yrs old based on the end of eligibility?  Could you describe more about what exactly the date should be and how you would calculate it if you were doing this on paper?

                 

                Thanks!


                • 5. Re: Calculating a date with a future age
                  Pebbles

                  Hello Ninja, 

                   

                  Yes, there can be one or up to nine children to a parent record. Each child is identified by a child id serial number.  And entered through a portal in the parent table.  Each parent table also has a unique serial number.  

                  In our database the child is only eligible until they are 22yrs for our services and that is also based on the End of Eligilbility date which only is for three years.  

                   

                   

                  Thanks

                  • 6. Re: Calculating a date with a future age
                    ninja

                    OK,

                     

                    Getting confused between children of humans and children of records...

                     

                    So a person is eligible until they are 22yrs or have been with you for three years whichever comes first?

                     

                    The start of service date (SOS) is on the parent table, and the person's DOB is on a child record...which of the 9? All?

                     

                    The calc resulting in date is starting to look like:

                     

                    If (  (Parent::SOS + 1095) < (ChildTable:: DOB + 8036) ; Parent::SOS + 1095 ; Month(ChildTable:: DOB) & "/" & Day(ChildTable:: DOB) & "/" (Year(ChildTable:: DOB) + 22)  )

                     

                    and it raises the question of accuracy needed.  Is a year 365 days, or must leap years be accounted for?  the 8036 includes 6 extra days for leap years across 22 years...you could add the month/day/year parsing into the IF condition to make this more accurate.

                     

                    Edited to remove emoticons.

                    • 7. Re: Calculating a date with a future age
                      Pebbles

                      Thank you Mr. Ninja,   you are the best... it worked out perfectly..  :smileywink:

                       

                       

                      One more question How do I add a field to an existing portal?

                      • 8. Re: Calculating a date with a future age
                        ninja

                        Howdy,

                         

                        First, the field must exist in the table the portal show records from (or a related table to it).  If it is New-New...add it to the appropriate table under File>Manage>Database.

                         

                        Then simply go into Layout mode and put the field into the portal line using the field tool.

                         

                        Have you put a new field onto a layout before?  If so, a portal is no different, you just need to stay within the lines...

                        • 9. Re: Calculating a date with a future age
                          Pebbles

                          thanks Ninja.... for all your help

                           

                          Kudos!!!