Jump to content

Date Search


Nighthawk_nz
 Share

This topic is 5665 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies

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 : :hammer:

Enter Find Mode []

Set Field ["LastContacted", formula I need"]

Perform Find []

Goto layout ["List']

Oh I am using FM5 (on windoze XPee)

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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:

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 by Guest
Link to comment
Share on other sites

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. :wink2:

LaRetta

Link to comment
Share on other sites

This topic is 5665 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.