Jump to content
Sign in to follow this  
Greg Hains

Date range serach

Recommended Posts

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

Share this post


Link to post
Share on other sites

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[]

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Many thanks Genx and Aldipalo.

Its so easy once you know how. ;)

Cheers,

Greg

Share this post


Link to post
Share on other sites

Hi GenX

This is a question, not a challenge. ;)

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

Al

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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 by Guest
Added green

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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 by Guest
Added update

Share this post


Link to post
Share on other sites

"I also color my find layouts yellow."

Funny, I colour my find layouts yellow too!

Great minds think alike.

Share this post


Link to post
Share on other sites

:beer:

Share this post


Link to post
Share on other sites

I use pale yellow sometimes lol...

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

I prefer Mellow Yellow myself.

LOL

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

:Borg: I WILL be FULLY xplat proficient :Borg:

Thanks for the help!

Share this post


Link to post
Share on other sites

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
Sign in to follow this  

×

Important Information

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