Another Birthdate Find or Calculation

[Mitch in Halifax]

I am designing a solution for mortgage broker.The main client table contains the client information for both husband and wife as well as birth dates.

What I'm tryiing to accomplish is to find the records of all client names that have a birthdate due in the next 30 days. I will send them out a birthday greeting.

I have tried a script

If (Get (CurrentDate) ≤ Date(Month(Client::DOB_H);Day(Client::DOB_H) ;Year(Get(CurrentDate) + 30)) and Get(CurrentDate) + 30 ≥ Date(Month(Client::DOB_H) ;Day(Client::DOB_H) ;Year(Get(CurrentDate))) to select the information, but I don't know how to display the information that it selects.

I'm also not sure if I should be displaying both husband and wife info on the same layout?

Mitch

[LaRetta]

Hi Mitch, you might try a script something like:

Go To Layout [ ... layout based upon client table ]

Set Error Capture [ On ]

Enter Find Mode [ ] ... uncheck pause

Set Field [ DOB_H ; Get ( CurrentDate ) & ".." & Get ( CurrentDate ) + 30 ]

New Record Request

Set Field [ DOB_W ; Get ( CurrentDate ) & ".." & Get ( CurrentDate ) + 30 ]

Perform Find [ ]

If [ not Get ( FoundCount ) ]

Show Custom Dialog [ "No birthdays within next 30 days." ]

Show All Records

Go To Layout [ Original Layout ]

Halt Script

End If

... do whatever you wish with found set.

As for whether both husband and wife should appear on the same layout? I assume they are different fields within the same record? It would make more sense to have two separate records and code each person's unique ID in the other record under a field called SpouseID. Because when you send the birthday card, how will you know which one you are sending it to if the record contains two people? It would be a nice touch to include "Hi Bill" and so on ... and if the records aren't split, you will be constantly splitting them anyway. In this way, you can have only ONE DOB field and you know you'll get the correct records which truly DO have a birthday.

LaRetta :wink2:

[Mitch in Halifax]

Thank you very much for your sage advice. I am very greatful for your assistance. I indeed will split the table into husband and wife with a relationship based on the mortgage clientID.

Cheers

Mitch

Edited July 29, 2006 by Guest

[Mitch in Halifax]

I have tried several iterations of the script to capture the data I have in my DOB field. I am wanting the script to find all next month dates. Although I have followed the steps outlined by LaRetta I just get the dialogue statement "No Birthdays..." when I run the script even though I know that there are records with DOB within the next 30 days.

Get ( CurrentDate ) & ".." & Get ( CurrentDate ) + 30 and

Let ( t = Get (CurrentDate) ; Date ( Month(t) ; 1 ; Year(t) ) & ".." & Date ( Month(t) + 1 ; 0 ; Year(t) ) )

Can anyone help me solve the problem.

Mitch

Client.zip

Edited August 2, 2006 by Guest

[BruceJ]

Something isn't working with your attachment.

[LaRetta]

Yes, your archive is empty. Where did the following come from? Certainly not from my suggestion ...

[color:green]Get ( CurrentDate ) & ".." & Get ( CurrentDate ) + 30 and

Let ( t = Get (CurrentDate) ; Date ( Month(t) ; 1 ; Year(t) ) & ".." & Date ( Month(t) + 1 ; 0 ; Year(t) ) )

That is not valid search criteria for a date field! If you split your single record into a husband and a wife record then you should only have ONE BirthDate field to search. The request in your original post was 1) "have a birthdate due in the next 30 days" and now you want 2) "next month dates" which isn't the same search at all. Please clarify what you want because, even if you get the file working, that answer won't be in the file.

Sorry I didn't answer sooner ... I was (honestly) off celebrating MY birthday.

LaRetta

Edited August 6, 2006 by Guest

[Mitch in Halifax]

I'm sorry for the confusion and the empty zip file.

I followed your script definitions and only included the Get statements on my last post.

Each record is two people (husband and wife or partner) that are entered and has a banking reference ID the is used for reference only and has little to do with the overall record, although my client can use the SFR_ID to sort or search. At this point I have not created a unique ID serial number although that might be wise. The SFR_ID is the mortgage reference # that is entered on each record but each person on that records has a unique SFR.

The key for the find is locating any record from the Client table that has DOB occuring in the next 30 days.

If you have any thoughts I would appreciate them..

Thanks

Mitch

Client_Management.fp7.zip

[LaRetta]

Hi Mitch,

The error was mine. I've been overdue for break. I provided a standard range find. But birthdays are different because every YEAR will be different so we can't search the DOB field directly (because you are using vs. 7). Note: You can with vs. 8 because you can use wild cards and even ranges within month, days & year independently. We could create an unstored calc, converting each birthdate to Get (CurrentDate)'s year but, because it can't be indexed, it would be a slow search. I suggest instead that you turn your DOB field into a number calc and search on that. In this way, it can be indexed and can also properly span ranges. And your file still has both people within the same record so I suggest you split them into two records as soon as possible.

Create a calculation (number) called BDmoYr_CO: Month ( DOB_CO ) + Day ( DOB_CO ) / 100

Create a calculation (number) called BDmoYr_P with: Month ( DOB_P ) + Day ( DOB_P ) / 100

If person was born on 8/15/1977 then the above calc would produce 8.15. To include the range using the current date, we do the same thing within the Find. So your script change would be:

Enter Find Mode [ ]

Set Field [ BDmoYr_CO ; [color:blue]below calc ]

New Record Request

Set Field [ BDmoYr_P ; [color:blue]below calc ]

Perform Find [ ]

[color:blue]Let ([

from = Get ( CurrentDate ) ;

to = Get ( CurrentDate ) + 30

] ;

Month ( from ) + Day ( from ) / 100 & ".." & Month ( to ) + Day ( to ) / 100

)

Your search this produces will look like: [color:blue]8.12..9.11. This number range will adjust properly when spanning all dates because the day is moved forward by 30 BEFORE converting to a number so it jumps month/leap-year barriers just fine. And, because it is a number, it spans ranges perfectly as well.

NOTE: It will still break at end of year because your search would be: 12.15..1.14. I can find no way to resolve this issue. Maybe others have ideas or maybe you should just use unstored date calcs turning your birthdates into current year as: Date ( Month ( DOB ) ; Day ( DOB ) ; Year ( Get ( CurrentDate ) ) and search on that instead, using my original script.

LaRetta

Edited August 10, 2006 by Guest
Added Note

[comment]

It will still break at end of year because your search would be: 12.15..1.14.

Interesting problem. One possible solution, I think, would be to to make the calculation field return a text result, and change to formula to something like:

Right ( "00" & Month ( DOB ) ; 2 ) & Right ( "00" & Day ( DOB ) ; 2 )

&

Case ( Month ( DOB ) = 1 ; "¶13" & Right ( "00" & Day ( DOB ) ; 2 ) )

Then you would search this field for:

Let ( [

today = Get ( CurrentDate )

;

Right ( "00" & Month ( today ) ; 2 ) & Right ( "00" & Day ( today ) ; 2 )

& ".." &

Right ( "00" & Month ( today ) + 1 ; 2 ) & Right ( "00" & Day ( today ) ; 2 )

)

EDIT:

I can't test this right now, but I think a single calculation field could accomodate both birthdays, separated by a carriage return.

Edited August 10, 2006 by Guest

[LaRetta]

Wonderful! And it indeed works combining both fields into multiline for the search. Thank you so much!

Mitch, here's your file back. The calc is called cDOBsearch and I modified your script called Birthday Next Month Copy. You'll want to fix that layout break but I didn't know where you were coming from and where you were going.

You rock, Michael!

L :wink2:

Client_ManagementREV.zip