Date Search

[Nighthawk_nz]

Hi all..

I have seen a coiple of dat searchs here but not sure why i could get them to work

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)

[IdealData]

Welcome Forsyth

Date fields understand simple maths:

> 1/1/2006 (every date AFTER 1/1/2006)

< 1/1/2006 (every date BEFORE 1/1/2006)

The little trick to this is that you must set the field with the "mathematical" search by copy/paste techniques as the Set Field step insists on only accepting data for the correct field type (even in Find mode). Needless to say "<1/1/2006" is not recognised as a valid date, not would it be with any other operators.

See my sample.

DateSearch.fp5.zip

[LaRetta]

I would prefer Insert Calculated Result over User clipboard. Both Paste[] and Insert Calculated Result[] must be on the same layout as the field to search. Also note that, in versions greater than 6, Set Field[] will now accept calculations directly into date fields. And with vs. 8, wild cards are also accepted. :wink2:

[Søren Dyhr]

Although I agree with LaRetta, could you instead of searching make a global field primary key for a hashed list GTRR-locking to another hashed list exploiting this method when it's dealing with open ranges:

http://www.onegasoft.com/tools/smartranges/index.shtml

...it might seem like another layer of complexity, but it comes in handy when establishing counts of records live (in an unstored calc'field) and and by it completely scriptless, before even GTRR'ing. Say you wish to colour the records appearance in a listed layout!

--sd

[John Mark Osborne]

I prefer to use a script rather than adding a relationship. Here is the script.

Enter Find Mode []

Insert Calculated Result ["MyDateField", "DateToText(Status(CurrentDate) - 30) & "..." & DateToText(Status(CurrentDate))"]

Perform Find

In FileMaker Pro 7 or later, it's a lot easier and you can use Set Field so the target date field doesn't have to be on the current layout.

Enter Find Mode []

Set Field [MyTable::MyDateField; (Get(CurrentDate) - 30) & "..." & Get(CurrentDate)"]

Perform Find []

Edited May 30, 2006 by Guest

[LaRetta]

As an aside ... notice that these parenthesis aren't needed (on the calc for 7/8). And two dots for search range works as well as 3! So you can actually eliminate the red...

[color:red](Get(CurrentDate) - 30[color:red]) & "..[color:red]." & Get(CurrentDate)

For those always looking for the best ... and for those wanting to learn lean (but proper) syntax, these understandings are important. Someone trying to learn excellent calc logic would see this and always wrap a date with parenthesis when including a number. And it is unnecessary.

This response is only to stop perpetuation of 'cutting the ends off the ham' and is geared towards those using Forum posts to learn good calculation technique.

LaRetta

[Nighthawk_nz]

I knew it would be simple enough... thanx