I'm trying to create a button that lists all client records where their birthday is due within x days. I.E. Press the button and there appears a list of clients who's birthdays fall within the next 30 days. I'm sure it's been done a hundred times, but the method has changed in FM7 and I'm stuck. I would appreciate any help from you gurus out there!

You can do this at least 2 ways.

1) With a convention find script would look something like this:

Enter Find Mode [ ]

Go to Layout that has Birthday on it

Insert Calculated Result [ A::Birthday; GetAsText ( Get ( CurrentDate ) ) & "..." & GetAsText ( Get ( CurrentDate ) + 30 ) ]

[ Select ] Perform Find [ ]

2) By a relationship:

Make 2 unstored calculated fields This Day = Get ( CurrentDate ) & Thirty Days = Get ( CurrentDate ) + 30.

Make a self relationship to a TO of the same table This Day <= Birthday and Thirty Days >= Birthday

Script is now go to related record [show]

RalphL's methode only finds people who were born in that given range of dates, but I think you really need to find people who are more than 1 years old, so ...

A simple, easy to understand way is to create 2 calc fields:

cMonth = Month(Birthdate)

cDay = Day(Birthdate)

then whe you want people who have a birthdate on a give month and day just search those fields.

C.

It would be hard to find people that haven't been born. You can make a calculated field MonthDay = 100 * Month(Birthdate) + Day(Birthdate) and use in the find or in the relationship. You will have modify the relationship or the Insert Calculated Result.

Note: FM7 does not require Insert Calculated Result, converting dates to text, or going to a layout without the field on it.

Enter Find Mode [ ]

Set Field [datefield; date1 & "..." & date2]

Perform Find [ ]

is valid.

I have exactly that problem. These script all become a bit problematic when you are performing the search on dec 28 2004 for someone who's birthday is Jan 2 2005 ... Any thoughts?

Lets see if I'm understanding you correctly.

You want to find Birthday for a person that hasn't been born yet, in a year that started yet?

No wonder your scipt is problematic.

Lee

Hi Orlando,

I think what we see above is a way to do this by calculating month and day from the birthdate, and searching on that.

But I would try this with a relationship using DayOfYear(Birthdate) for the match field and a multi-key of upcomming DayOfYears for the parent key:

ParentKey (calculation, text result) =

DayOfYear(status(currentdate)) & &

DayOfYear(status(currentdate)) + 1 & &

DayOfYear(status(currentdate)) + 2 & &

DayOfYear(status(currentdate)) + 3 & &

...

case(DayOfYear(status(currentdate)) > 364, 1 & ) &

case(DayOfYear(status(currentdate)) > 363, 2 & ) &

case(DayOfYear(status(currentdate)) > 362, 3 & ) &

...

I image there are prettier ways to do that wrap around for seeing January birthdates in December, but that's the idea.

You could also create an unstored calculation of Date( Month(DOB), Day(DOB), Year(Status(CurrentDate)) ) and perform your date range find on this field.

You could also create an unstored calculation of Date( Month(DOB), Day(DOB), Year(Status(CurrentDate)) ) and perform your date range find on this field.

I realize this thread is over 6 years old, but... You saved me from going crazy with this simple solution. Thanks much for the help.

Bruce

See also:

http://fmforums.com/forum/topic/48048-birthday-reminder-how-can-i-do-it/page__p__224375#entry224375

(stored calculations work faster for finds).