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

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).

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

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

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

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...

 DIARYid CLIENTid DIARYtype_of_contact DIARYentry_date 1 12345 referral 22/07/2016 2 54321 letter sent 25/07/2016 3 12345 session 29/07/2016 4 54321 referral 29/07/2016 5 12345 session 05/08/2016 6 54321 session 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

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

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