Hi all,

I have an age calculation field. I use the calculation:

Year(Get ( CurrentDate )) - Year(Birthdate) -
(Month(Get ( CurrentDate )) < Month(Birthdate)) -
((Month(Get ( CurrentDate )) = Month(Birthdate)) and
(Day(Get ( CurrentDate )) < Day(Birthdate)))

(thanks to Glenn Schwandt).

So every morning when I open the database (I use FMP server 7) it takes
a few minutes to calculate for the 11,000 or so records.

The problem is that since I have migrated to FMP 7, this calculation
changes the modification date of every record every day, defeating the
purpose of having a modification date field.

How does one calculate an age every day without modifying every record?
My only idea is to populate the age field one time with a calculation
such as the above, then have it be a plain old (uncalculated) number
field. Then have a script on opening the database that searches for
records whose DOB has the day and month of any days that have passed
since the last opening of the database, then calculate the age for only
those records. Feels klunky.

I'm sure there are better methods out there. Anyone?

Thanks in advance,

Amanda

In article <slrnddnes9.35f.t-use (AT) ID-685 (DOT) user.individual.de>,
Martin Trautmann <t-use (AT) gmx (DOT) net> wrote:

We need the age. This is for a social services program that helps people
find medical services. We need to have the exact integer age in years
for every client, every day. They become eligible for things as they
turn 19, 40, 65, etc.


It's close. I'm thinking about it.

Thank you for these ideas. I like the stored field for the month and
day. I'm still unsure how to handle the range to check. We open the
database virtually every weekday, except for a short vacation in
November and a longer one in December. I need a balance between not
taking too wide of a range, but obviously _never_ skipping a date.

Amanda

In article <amanderr-8FA513.13020618072005 (AT) news (DOT) dallas.sbcglobal.net>,
Amanda Robin <amanderr (AT) yahoo (DOT) com> wrote:

Here's the age calculation I use (FMP6).
If(IsEmpty(BirthDate),"",(Year(Today) - Year(BirthDate)) -
If(DayofYear(Today) >= DayofYear(BirthDate), 0, 1))

Not having used FMP7 I can't help you with the modification date
problem. Is it maybe an issue of whether or not the calculation result
is stored?

--
Goal 2005: Convincing James Hetfield to cover the Strawberry Shortcake
"Are You Berry Berry Happy?" song.

In article <uce-F1F049.17283718072005 (AT) comcast (DOT) dca.giganews.com>,
uce (AT) splook (DOT) com says...
Probably, but unstoring it is totally worthless if he wants to search on
it.

The solution of searching for a birthdate range instead of a
recalculated daily age is the right way to approach this. It shifts the
'what is today's date anyway dynamic' into the search criteria instead
of putting it in the indexed data.

In article <MPG.1d45c4283951b9e7989c06 (AT) shawnews (DOT) vf.shawcable.net>,
42 <nospam (AT) nospam (DOT) com> wrote:

Um. No, actually it's not. Searching works fine here with a non-stored
calculation. That's even fairly well implied by the phrasing within the
Calculation Options window.

From the task described, I'm not sure if search is an important function
anyway, so even the modest speed hit may not be a real problem.

G

--
Goal 2005: Convincing James Hetfield to cover the Strawberry Shortcake
"Are You Berry Berry Happy?" song.

In article <uce-E134DC.21511018072005 (AT) comcast (DOT) dca.giganews.com>,
uce (AT) splook (DOT) com says...
If its not stored then it has to be calculated on the fly for a search;
the result by definition can't be indexed. Sure you can brute force
search several thousand records on a fast machine without it dragging
you down if its a simple enough calc that does't use aggregates or
related files, but its still a silly approach that can't scale well as
the database grows.

I say "silly" because its completely unnecessary... a different but
equivalent query lets you search an indexed field.

When you want to display the age, then an unstored calc makes perfect
sense. If you want to search on an age, it makes more sense to query the
indexed date field for a date range rather than an unstored calculation.

If you wanted to find all the invoices that were made last week you'd
search for invoices dated between x and y. You wouldn't define some
calculation off the current date and then update each record each day...
with how many weeks old each is and then search that. Why would the age
of people be handled differently?

In article <amanderr-8FA513.13020618072005 (AT) news (DOT) dallas.sbcglobal.net>,
Amanda Robin <amanderr (AT) yahoo (DOT) com> wrote:

If you want to find records when someone turns 19, 40, 65, etc, then
create a separate table that has one record and has calculated fields
that determine what the birth date would be if someone is 19, 40, 65,
etc. Have another field in this table with a stored value of 1 or
something. You can then join against this table when doing a search for
each age where the value of that stored field is 1. You now have
calculated fields in one record and not every record, so you won't have
to recalculate for every record.

Or have this as multiple records in the new table, with a field with the
age and a field with the calculated birth date. You can then join this
with the table. So do a search on the contact list where age in the new
table is 19, for when you want to find everyone who is 19.

In article <MPG.1d4602b3d6ab3c1b989c0a (AT) shawnews (DOT) vf.shawcable.net>,
42 <nospam (AT) nospam (DOT) com> wrote:

You didn't say "silly" originally. You said "totally worthless." If
searching on the calculated field is not a high priority, that's not
true.


Yes. If. You seem to be focussed on solving a problem different from
that described. And I think I see what happened. Someone else brought up
the notion of a search, but that's actually, AFAICT, along a branch
toward one suboptimal solution to the original problem.


Because you may actually be primarily looking at _the_people_ rather
than the age. There's no indication in the original post or Amanda's
follow-up that she's looking to search or sort on age. "This is for a
social services program that helps people find medical services. We need
to have the exact integer age in years for every client, every day."
Says pretty clearly to me that they're looking at individuals' records
on-demand.

--
Goal 2005: Convincing James Hetfield to cover the Strawberry Shortcake
"Are You Berry Berry Happy?" song.

In article <slrnddpmns.48g.t-use (AT) ID-685 (DOT) user.individual.de>,
Martin Trautmann <t-use (AT) gmx (DOT) net> wrote:

I've misread, then. I thought the question was how to eliminate a
behavior, newly discovered in FMP7, where the recalc was changing the
modification date on the records.

I didn't see that as the task. I thought it was "When I'm looking at
this person, I need to be able to see their current age."

G

--
Goal 2005: Convincing James Hetfield to cover the Strawberry Shortcake
"Are You Berry Berry Happy?" song.

In article <uce-0E83A0.12492919072005 (AT) comcast (DOT) dca.giganews.com>,
Gregory Weston <uce (AT) splook (DOT) com> wrote:

Thank you, Gregory. You have it right. Yes, the important part is that
we need to know every client's age, every day. We have that already. It
takes about 3 minutes in the morning and that's not too much time (down
from 14 minutes under our old system). But it would be really nice if
that calculation didn't make our modification date useless. That's what
we don't have.

About why we need the age: We can't predict whom we will call on a given
day. We can predict even less who will call us. We have some staff with
minimal computer skills. They believe what they see on the screen. They
need to know the client's age. Period. They don't need to have to press
a button to find it. They don't need to be thinking, gosh, her birthday
is tomorrow--I wonder if she is 41 today or will be 41 tomorrow. The
19/40/65 threshold thing was an example. More examples are that we do
statistics and reporting that are based on age. We sort and print
monthly records based on age groups.

Thanks for the help. It still seems to me that I need to have day and
month of birthdate stored and do a selection every day to compute the
ones who have recently turned over.

Amanda