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
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
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.
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:
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
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.
Great. Many thanks Phil. A combination of the new relationship with the earlier calculation you lead me to works a treat!