Finding birthdays- scripting issue

[skingjack2]

Good day,

Perplexing problem (likely minor) but I've created a script that finds all birthdays within a certain range of the current date (i.e. 1 day prior, 7 days post current date).

set field [Client_Data::Birthdate]; Month(Get(CurrentDate)) & "/" & day(Get(CurrentDate)) & "/*"

New Record/Request

set field [Client_Data::Birthdate]; Month(Get(CurrentDate)-1) & "/" & day(Get(CurrentDate)-1) & "/*"

New Record/Request

set field [Client_Data::Birthdate]; Month(Get(CurrentDate)+1) & "/" & day(Get(CurrentDate)+1) & "/*"

New Record/Request

etc....

It works fine up to 7 days from the current date & 1 day prior however if I wish to extend past the 7 day timeframe to, for example, 14 days, it fails. Not sure why... the script should remain the same

should it not?

set field [Client_Data::Birthdate]; Month(Get(CurrentDate)+14) & "/" & day(Get(CurrentDate)+14) & "/*"

On a similar issue, said same script is called by another script that displays the recent & upcoming birthdays in a new window. The issue with this is that only displays up to the 7 day post current date timeframe (obviously related to the first issue) however will not display any birthday 1 day prior to the current date.

i.e. set field [Client_Data::Birthdate]; Month(Get(CurrentDate)-1) & "/" & day(Get(CurrentDate)-1) & "/*"

The script that calls this original birthday script is:

Find B-days (portal)

New Window [ Name: "Birthdays-recent & upcoming"; Height: 240; Width: 300; Top: 30; Left: 950 ]

Go to Layout [ “Birthdays w/i week (portal)” (Client_Data) ]

Set Error Capture [ On ]

Perform Script [ “Find Birthdays w/i 7 days” ]

View As

[ View as Form ]

Show/Hide Status Area

[ Hide ]

Show/Hide Text Ruler

[ Hide ]

If [ (Get ( LastError)= 0 ) ]

Go to Layout [ “Birthdays w/i week (portal)” (Client_Data) ]

Else

Show Custom Dialog [ Title: "Sorry, there was no match"; Message: "Sorry, there are no clients with recent or upcoming birthdays in

the next week."; Buttons: “Keep window open?”, “Close this window?” ]

Close Window [ Current Window ]

End If

There is nothing that I can readily see that would prevent this script not displaying the appropriate details.

Any thoughts/ suggestions as to what might be amiss?

TIA.

Jack

[LaRetta]

Hello Jack,

It will always break when the date switches to a different month because, by using a text calculation, FileMaker can't properly increment through the dates. You must use true dates and -1 and +14 days against that true date, accordingly.

So create a field in your table with your birthdays, called cConvertBD (result is date)

Date ( Month ( Birthdate ) ; Day ( Birthdate ) ; 4 )

... then search THIS field via script which would be:

Enter Find Mode [ ] uncheck pause

Set Field [ Client_Data::cConvertBD ; Let ( today = Date ( Month ( Get ( CurrentDate ) ) ; Day ( Get ( CurrentDate ) ) ; 4 ) ; [color:green]today - 1 & ".." & today + 14 ) ]

Set Error Capture [ On ]

Perform Find [ ]

By referring to the current date by the same year (4), you will always find the proper records, ie, by leaving the green portion as true date, it will increment back and forward through proper dates and will always work and you will only need the original single find request instead of several (because you use a range). :smile2:

[skingjack2]

Hi Laretta,

Your solution works wonders! Now to solve the second part of the dilemma...why your solution & mine will not show properly

when called by the other script

FYI, in my ongoing dealings with Filemaker, I'm finding that what appears to be logical doesn't always work (always learning). Fortunately, thru the help of yourself & many others with much more FM savvy, there is always more than one solution available (some better than others).

Thanks again!

Jack

[LaRetta]

If you can post a zipped, empty clone of your file, we might help with your first script as well. I assure you that there is logic behind it. It is the bugs and unexpected (and undocumented) behaviors which can bite you - but the logic is pretty straight.

If nothing else, post the script which is giving you problems, but it is easier to see the file itself so we know the point of view and table occurrences and relationships which are dependent. :smile2:

[skingjack2]

Hi Laretta,

Finally able to free up time to look at the database & try to improve upon it *again*. Re: the second script, I've come to the realization that depending on which value I choose for the numerical portion of your solution "today + [color:green]14", in only rare cases will there not be an upcoming birthday so have abandoned that idea (amazing what some time away

will do to provide clarity).

There is a related issue that does arise though; when I add a sort step (by birthdate) after performing the find, naturally it sorts by year (ascending or descending). In essence it sorts by age. Any suggestions on how to attack sorting to ignore the year in the birthdate and return only the month and date. I thought about incorporating the left function somehow i.e. left (birthdate:5 where date is in the format 12/15/09). However, this failed.

Finally, in reference to providing a clone for public viewing, I would be *very* embarassed/concerned as the database is a *horrible* hodgepodge of ideas I've tried to incorporate from reading/reverse engineering others ideas/solutions. I'm sure I've broken every rule with respect to proper database design and implementation while trying to learn FM & it would certainly provide much amusement/bewilderment for anyone who viewed it:)

Jack

[comment]

I suggest you explore these:

http://fmforums.com/forum/showtopic.php?tid/173264/

http://fmforums.com/forum/showtopic.php?tid/179057/

http://fmforums.com/forum/showtopic.php?tid/179538/

http://fmforums.com/forum/showtopic.php?tid/182474/

[skingjack2]

Thanks Comment!

That was certainly a quick response; I'll explore the suggested threads.

Jack

[fmbuddy]

Hi,

I was trying this script from this topic for my solution.

Script:

Enter Find Mode [ ] uncheck pause

Set Field [ Client_Data::cConvertBD ; Let ( today = Date ( Month ( Get ( CurrentDate ) ) ; Day ( Get ( CurrentDate ) ) ; 4 ) ; today - 1 & ".." & today + 14 ) ]

Set Error Capture [ On ]

Perform Find [ ]

I made one change and that is for range[i.e. 14]. I want it in editable format. Though I failed to do that more efficiently, so I created a new layout and a new field name "range", type: number.

Now the script in my database look like this :

Enter Find Mode [ ] uncheck pause

Set Field [ Client_Data::cConvertBD ; Let ( today = Date ( Month ( Get ( CurrentDate ) ) ; Day ( Get ( CurrentDate ) ) ; 4 ) ; today - 1 & ".." & today + ContactManagement::Range ) ]

Set Error Capture [ On ]

Perform Find [ ]

In layout I put this filed and made it a drop down list with number from 1 to 31 as range.

What I planned was, I will choose the range 1,2, 10 or 31 and the above script will automatically update and search as per the given range.

To test it I added some dob in my database.

1. 12/29/1909

2. 1/1/1890

3. 12/29/1980

4. 12/25/1890

But when the month is changing then the script isnt showing the results properly. I mean till 10 days range its showing perfectly 3 results found but when I am choosing 31 days then its not showing the 2nd result [i.e. 1/1/1890].

I have tested the script also in its original form and change the range from 14 to 31 but still it didnt showed the all results.

Help will be appreciated.

Thanks,

FMBuddy

** sorry the reply should be for the post of LaRetta.

Edited December 20, 2009 by Guest

[LaRetta]

I made one change and that is for range[i.e. 14]. I want it in editable format. Though I failed to do that more efficiently, so I created a new layout and a new field name "range", type: number.

Is this ContactManagement::Range field a global?

[fmbuddy]

Yes, the Range field is Global.

Type Number, Option/Comments Global

[LaRetta]

Hi fmbuddy,

The problem with the above example is not that it was breaking when crossing the month, but rather it was breaking if you cross the year boundary. Comment has provided links which can address this issue in some instances but not (that I could tell) if someone wants to increase the span search by any number of days indicated. Believe me, I tried to adjust his examples.

So I have resolved it by using a second find request if the span crosses the year boundary. If anyone has other ideas, I would love to hear them. I hope this file is helpful.

Replaced file: Corrected $spanYr variable to Year ( $trueEnd ) > Year ( $start ) so someone could enter huge numbers such as 388 and it wouldn't break. Added startup script to ask if the person wants to search for birthdays. And I added the search results on the top of the list so someone could see what was searched to produce the results.

FindBDspansREV.zip

Edited December 22, 2009 by Guest
Replaced file

[fmbuddy]

Hi,

The script is working fine. Though I found that if I enter some big values in the gNum field then its creating little issues.

as an example : If I enter 365 days then it should list all the birthdays but its not showing that. Its showing only those birthdays which after exact 365 days.

Note: I am not even a good in observing the script so my thoughts may be wrong.

Also I want to make another script which will ask everytime "Do You want to search for birthdays" when I open the database.

I thought of Showing custom dialouge box but failed to change the layout when ok or cancel button pressed.

So, I created another layout and added 2 buttons where I perform the layout changes.

Is it possible to make this using any script?

Thanks,

FMBuddy

[color:gray]My grammar and spelling can be very tough to understand some time.

[LaRetta]

340 works, if today is 12/22/2009. 340 produces: 12/21/0004...12/31/0004 and 1/1/0004...11/26/0004. It was designed to address 1, 2, 7, 14, 31 etc normal future days to search ahead. If you want all birthdays, you could just Show All Records. I didn't really have time now to adjust it further; maybe others can or I'll try probably after the holidays.

UPDATE: I'll attach a script for opening later today if I get the chance.

[LaRetta]

I have fixed the file above so someone can put in 365 and find all birthdays (I needed to adjust $spanYr so it tested a year change instead of a month change. This was only way to allow for huge numbers in gNum. Keep in mind that, if one puts in 388 (for example), that it will only find 25 records (the rest from this year and 13 from the next). There is only a certain amount of logic we can address, ie, it can't jump years ahead - it is designed to work within a generic span.

I have also added test upon opening so it is automated for you and will ask if you want to find birthdays. And I placed the search criteria on the top of the layout so if you want to see what specifically was searched to produce the found set, it will show you.

[fmbuddy]

There is nothing I can say after this.

You are just awesome.

Very few people take this much of responsibility while answering in Free Forums.

Its a pleasure to get a help from you.

Thank You very much.

Wish you a very happy holidays.

Edited December 22, 2009 by Guest