Script for finding upcoming Birthdays etc

[mylesevans]

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!

[RalphL]

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]

[Charles Delfs]

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.

[RalphL]

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.

[-Queue-]

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.

[Orlando Sardaro]

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?

[Lee Smith]

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

[Ender]

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.

[-Queue-]

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.

[bwyrwitzke]

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

[comment]

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