Date Search

Nighthawk · #1 (**permalink**) 05-28-06, 01:27

Hi all..

What I have is a date field, LastContacted and would like to make FMP to search for any dates that are over say 30 days or older from the current date and have them list on start up as a reminder... once contact is made or an email is sent the date is updated and shouldn't come up againt for another 30 odd days...

so my question is what is the formula for a date search to find all dates in that range.. i tried date ranges but it don't seem to work, and got myself confuddle

i know can be done and must be pretty simple.. im just not that simple

Enter Find Mode []
Set Field ["LastContacted", formula I need"]
Perform Find []
Goto layout ["List']

Oh I am using FM5 (on windoze XPee)

Nighthawk · #2 (**permalink**) 06-02-06, 19:03

Okay heres what I did as a work around it probably not the most eligent way...

Make a new global feild gTodayDate and in the startup script set with current date
In my settings database new field gContactReminder this way the user can set how many days.

New script in the last contacted

Code:

Enter Find Mode []
Set Field ["gTodayDate", ""<" & DateToText(Today-settings::gContactReminder)"]
Perform Find []
Copy ["gTodayDate""]
Enter Find Mode []
Paste ["LastContacted"]
Perform Find
if [status(CurrentError) <> 0"]
    Goto layout ["List"]
    Show All Records
    Perform Script ["Subscript", "ScreenRefresh"]
    Show Message "No Client needs contacting"]
    Exit Script
Else
    Goto layout ["List"]
    Sort ["Restore, No dialog"]
    Perform Script ["Subscript", "ScreenRefresh"]
    Show Message "The Following Clients need contacting"]
End if

I have taken it further and added that the user can select the clients that he wishes to be reminded on. Simply by adding a new field/ checkbox NeedsContacting and adding an extra step in the final search

Code:

Set Field ["NeedsContacting", ""Yes""]

As I said not the most eligent but it works ....

And yes I know version 8 it is easier but since I am in Ver5 I have to work with what I got

<Ender> · #3 (**permalink**) 06-02-06, 22:26

This is slightly better:

Code:

Enter Find Mode []
Insert Calculated Result [ LastContacted, "<" & status(currentdate)-settings::gContactReminder ]
Set Error Capture [ On ]
Perform Find []
If [ status(CurrentError) <> 0 ]
    Go to layout [ List ]
    Show All Records
    Perform Script [ Subscripts, "ScreenRefresh" ]
    Show Message [ "No Client needs contacting" ]
    Exit Script
Else
    Go to layout [ List ]
    Sort [ Restore, No dialog ]
    Perform Script [ Subscripts, "ScreenRefresh" ]
    Show Message [ "The Following Clients need contacting" ]
End if

Be sure that the LastContacted field is on the layout this script uses.

Nighthawk · #4 (**permalink**) 06-02-06, 22:57

I tried that at first... In theory that should work... but I get showing all records...

chriscozi · #5 (**permalink**) 06-04-06, 09:23

I know you specifically want a search in your question but why oh why use a find????

This is what Relational db's are all about and are excellent at achieving.

Just set up a self join relationship based on the 'last contacted' <= 'todays date' and voila, a portal contains the results.

Now ANYTIME there are out of date contacts they show in the portal. There are things you can do to have a HIGHLIGHT field show up on other layouts to show the status of these records, etc.

Because people may leave the application open longer than a day or so they won't run the script and your globals and scripts don't run.

What do you think? This is WAY more flexible, I would think.

Internet Services

Web Development

Digital Marketing

Consumer Tech