Date range serach

[Greg Hains]

Hi.

Im relatiely new to FMP and am havig difficulty searching on a date range.

I have three fields. _DateFrom, _DateTo, and Date. I am trying to locate all records in the Date field between and including _DateFrom and _DateTo. I understand the mat hbehind it, but cannot seem to get it working.

I thought perhaps that in a Find I could use the two fields yet it only allows me to insert literal text. I have tried Omitting as well - same problem.

Anybody please give me a pointer in the right direction?

Cheers,

Greg

[Genx]

Hi Greg,

You have to do a range search..

Enter Find Mode, then click in your datefield, and enter

"1307...31407" to find all dates between 1st of March and 31st of April

To automate this, make _DateFrom and _DateTo global fields and add a button with something like the following script attached:

Commit Records[]

Enter Find Mode[]

Set Field[Date ; _DateFrom & "..." & _DateTo ]

Perform Find[]

[aldipalo]

If you are doing a search from a layout then in your "Date" field enter date from ... date to. In a date format, e.g. 04/01/2007...04/10/2007.

If in a script

Enter Find Mode() /* uncheck Pause

Set Field(Date, dateto & "..." & datefrom)

Perform Find()

You can also add a custom Dialog to the script.

Show Custom Dialog("Enter Date to and Date From)

/* Click on the "Input Fields Tab" to select the Date To and Date from fields to display in the Custom Dialog*/

If (Lastmessagechoice = 1)

Enter Find Mode() /* uncheck Pause

Set Field(Date, dateto & "..." & datefrom)

Perform Find()

Endif

HTH

[Greg Hains]

Many thanks Genx and Aldipalo.

Its so easy once you know how.

Cheers,

Greg

[aldipalo]

Hi GenX

This is a question, not a challenge.

Is commit records necessary and why. Please explain cause I never use it.

Al

[Genx]

Necessary?

In the case of IWP where records aren't committed automatically - Yes.

In FM not always, but sometimes you have unexpected behaviour if you don't commit the record first.

Habbit I guess...

[Dchall_San_Antonio]

I want to find all the records in the date range for different project numbers. This thread would seem to answer that, but it is not quite working. I'm trying to get away from typing the dates by using drop down calendars to select my dateto and datefrom. When I set the dateto and datefrom with dropdowns, the find mode wants my records to have the datefrom AND dateto on all the found records. Thus I get no records returned. If I use the script above, leave dateto and datefrom emptym and type the range in the date field manually, then the find works. Do you have ideas for doing a date range find using drop down calendars?

[LaRetta]

Use two global date fields, gFrom and gThru. Attach your drop-down calendar to them. Then your script would be:

Set Error Capture [ On ]

Enter Find Mode [ ] ... uncheck pause

Set Field [ yourTrueDateField ; gFrom & ".." & gThru ]

Perform Find [ ]

If [ not Get ( FoundCount ) ]

Show Custom Dialog [ "No Records Found' ]

[color:green]Show All Records

Exit Script

Else

... do whatever with found set

End If

Script typed without testing

UPDATE: If you truly have datefrom and dateto fields, change your Set Field [ ] to two steps:

Set Field [ datefrom ; ":great: " & gFrom ]

Set Field [ dateto ; ":less:" & gTo ]

UPDATE: I added a Show All Records. I dislike leaving a User on an empty set.

LaRetta

Edited August 22, 2007 by Guest
Added green

[Dchall_San_Antonio]

Thank you. Setting them as global did the trick for the dates. I read that in Genx's post but forgot about it in implementing the script.

I still need to find records with both the date range AND the project number. When the script runs it stops and asks me for the project number, but it is just as easy to set the project number beforehand just as I am setting the start and end dates beforehand. Can I set the project number field in the same "set field" script step?

[LaRetta]

There are many ways of handling this; from using a Custom Dialog to modifying your find request to allow the User to type the project number. Personally, I prefer keeping a process consistent, ie, if I'm using globals to hold part of the process, I'll use global to hold the other User's input as well.

Ideally, this means using a dedicated find layout with globals and then yes, to answer your question, you would include a THIRD Set Field [ ] which would be:

Set Field [ ProjectNumber ; gProjectNumber ]

If only those three fields (all globals) are on the layout which is clearly marked FIND LAYOUT then Users are clear on its purpose. I also color my find layouts yellow. Users know when the layout is yellow that they are in find mode. A simple PERFORM FIND button (with your script) will handle the rest. You might also test for empty fields. If you'd like an example, let us know. As I've said, there are many ways of handling this request ...

UPDATE: Also consider the following on that date range ... if a User enters a start date but no end date, what end date do you want? If User enters an end date but no start date, what should the start date be? You MUST have a date in both portions for a range find but you CAN default to a specific date if they leave one blank.

Edited August 21, 2007 by Guest
Added update

[Vaughan]

"I also color my find layouts yellow."

Funny, I colour my find layouts yellow too!

Great minds think alike.

[LaRetta]

:beer:

[Dchall_San_Antonio]

Thanks again. It's working great!

[David Jondreau]

What shade yellow?

[Genx]

I use pale yellow sometimes lol...

[Vaughan]

I use the lightest shade of yellow in the default FMP colour set, then set it to 50% shading.

[Lee Smith]

I prefer Mellow Yellow myself.

LOL

[LaRetta]

I use the lightest shade of yellow in the default FMP colour set, then set it to 50% shading.

I as well, Vaughan. But I wonder what you mean by 50% shading. Are you talking about a pattern fill or a part of the color palette itself, ie, increasing the lum? I tried to test it on my Mac but, well, I tried to right-click the body to change the color and I kept getting the Part Definition instead. :

So I couldn't test my Mac to see if something said specifically 'Shading.'

LaRetta

[LaRetta]

Clarification ... I know to right-click the body tab to change the body color. But on my Mac, I can't do it!

[Vaughan]

Yes I mean the 50% pattern (next to the paint can).

[LaRetta]

Thanks. Again I ask anyone ... when the Status Area isn't displayed, and I want to change the body color (using a Mac), how do I work with the Fill Color or Fill Pattern? On Windows, when I right-click the body tab, I get:

Part Definition

Fill Color

Fill Pattern

I can find no similar action on a Mac.

[comment]

I only have a single-button mouse. When I control-click a part's tab I get the contextual menu. I was under the impression that right-clicking should produce the same result.

[Lee Smith]

That's what I get on my Mac by using the right button.

[David Jondreau]

Double tapping the trackpad on my MacBook Pro gives me that context menu.

I don't think I ever work on a layout without displaying the Status Area though.

[LaRetta]

Hmmm, I have no trackpad but rather a Mac wireless Model A197 mighty mouse. The whole top is one click. But I found preferences and the right portion can be programmed as secondary button. Sorry to hijack the thread ... learning xplat isn't always intuitive; nor is it listed in OS hardware or software documentation; nor FM Help without a LOT of research.

I WILL be FULLY xplat proficient

Thanks for the help!