5 Replies Latest reply on Nov 9, 2016 9:27 AM by simonpsmith

    Finding an age/age range at the time of a diary event

    simonpsmith

      I’ve been scratching my head exactly how to find and age of a ‘client’ at date that is determined by a certain event that’s been entered in a diary table.

       

      To simplify, let’s say there are just two tables: CLIENTS and DIARY

      CLIENTS contains, say, 4 fields: CLIENTid, CLIENTname, CLIENTdate_of_birth, CLIENTage_at_referral

      DIARY contains, say, 3 fields: DIARYid, DIARYtype_of_contact, DIARYentry_date

       

      In the DIARY layout, someone will enter a client ID number, select a type of contact from a value list, and enter the date that happened.

       

      One of the DIARYtype_of_contact choices is “Referral” and I want to be able to, eventually, count the number of people in an age range that were referred during any period of time.

       

      So, for each client, I need to calculate their CLIENTage_at_referral by looking up the DIARYentry_date for when the client’s DIARYtype_of_contact = “referral”, subtract their CLIENTdate_of_birth from this and return their age on that date. I can then do a ValueCount to get my totals (I hope).

       

      Any help please?

        • 1. Re: Finding an age/age range at the time of a diary event
          philmodjunk

          See the calculation in this link: Age at date of death.

           

          Just use date of referral in place of date of death.

          • 2. Re: Finding an age/age range at the time of a diary event
            user19752

            Finding age, you can use date range instead of calculating age.

            For example, at today Sep 22, 2016 who aged 20 years old birth between Sep 22, 1996 and Sep 23, 1995

            • 3. Re: Finding an age/age range at the time of a diary event
              simonpsmith

              Thanks for that Phil.

               

              My problem is getting this "date of referral" to use.

               

              It's knowing how to express, in a calculation, that when "DIARYtype_of_contact" equals "referral", then use the relevant date value in the "DIARYentry_date" field to do the calculation you've led me to.

               

              I know I should know this, it's basic, but I just can't think what I should be doing...

               

              Just to clarify my ramble, if I had the DIARY table as below...

                  

              DIARYidCLIENTidDIARYtype_of_contactDIARYentry_date
              112345referral22/07/2016
              254321letter sent25/07/2016
              312345session29/07/2016
              454321referral29/07/2016
              512345session05/08/2016
              654321session

              08/08/2016

              and was calculating the ages, at referral, for clients 12345 and 54321, I would need to call into my calculation "22/7/16" for 12345 and "29/7/16" for 54321, and I'm not sure how to.

              • 4. Re: Finding an age/age range at the time of a diary event
                philmodjunk

                So the main issue is how to link back to the correct record in order to access the referral date. There are several options.

                 

                Is to set up this relationship:

                 

                Diary::constReferral = Diary|Referral::Diarytype_of_contact AND
                Diary::DIARyid = Diary|Referral::DIARYid

                 

                Diary|Referral is a new occurrence of Diary (a second "box" in your relationship graph that also refers to Diary)

                constReferral is a calculation field with the following expression as it's sole calculation term:

                "referral"

                Select Text as the result type

                 

                Then your age calculation can refer to Diary|Referral::DIARYentry_date in order to calculate the age at time of referral.

                 

                But maybe you have a Client or Patient Table?

                 

                If so, this relationship would make more sense:

                Clients::__pkClientID = Diary|Referral::DIARYid AND

                Clients::constReferral= Diary|Referral::Diarytype_of_contact

                 

                So that a date of birth field in clients could refer to Diary|Referral::DIARYentry_date in order to calculate age at time of referral.

                 

                In either case, an alternative approach is to use ExecuteSQL to access the date from the correct diary record for a given client.

                • 5. Re: Finding an age/age range at the time of a diary event
                  simonpsmith

                  Great. Many thanks Phil. A combination of the new relationship with the earlier calculation you lead me to works a treat!