Dear all,

My database has a field in it with the child's birthdate in it which is used to work out their chronolgical age.

What I am after is a field which works out a child's age group according to their age. Here's the kicker............ If a child is (for example) 10 now or will turn 10 before 31/12/03 (s)he would be in the 10 yr old age group. It doesn't matter when they turn 10 (for example) they are in that age group the year they turn their age.

Is there a way of creating a field which would do it and update it every year.

Many thanx in advance

Poida

Hi Poida,

Working with date updating the normal approach is to be updated daily or anytime you open / connect to the database.

If I understand correcty you need to calculate the group when a record is updated and just ONCE a year. There are two or three ways but I will suggest you the one I like.

cd_theDate = (calculation date)

(that end of year)

Date (12; 31; Year (Status (CurrentDate)))

A not stored calculated value will update everytime you need.

cn_age_group = (calculation number) =

Year (cd_theDate) - Year (birthdate)

That gives you the numeral in which the child should be in. HTH, luck.

Here is one that works. You will have to adjust it for your dates though.

Lee

UnderAgeCalc.fp5.zip

Lee,

Your sample looks good.

I'm just wondering if there could be a way to index the result as to work with related records.

I cannot see anyother way than a script which would annually update each records based on your calc.

The script would go to related records and replace the "Age Group" with a new one.

Hi Ugo,

I used it as a "Very" flat file.

change the global each year and all is update as to eligibility.

Lee

Sorry Lee I was not clear.

1. I hadn't corrected my first post before posting. At first I thought the solution wanted was a monthly update, that's why I thought of a go to related record based on current Month::Month(Birthday).

I kept the go to related records in my post while it was referenced to nothing.

2. As I said, your calc, which is good, need to be updated.....AND indexed suitable for relationships on group as it is actually referencing a global field.

Annually changing the global field won't help, we'd need either a classic date field with a Annual Replace script, or a loop through records with a setfield on a date field, or lookup from the global to another date field.

3. This wasn't asked by the poster though, I just thought it could be useful to have it indexed. If not, why separate records in groups....

Are these rhetorical questions?

It sounds like to me that you are talking your way through your process?

Lee

Hello Poida,

This can be done very simply with a single unstored calculation field, using the formula Year(Status(CurrentDate)) - Year(DateOfBirth).

Alternatively, if you require that the age group field be stored and indexed (to facilitate searching etc) then I suggest that instead you use a number field with an auto-enter calculation, plus a start-up script that will trigger the first time the file is opened on the host in each calendar year, to update the age-group data throughout the file.

I am attaching a pair of files which show working examples of both methods.

AgeGroup.zip