finding records between two dates then reporting

[ITOjim]

I am creating a Trouble Report/Log database for a Computer Operations Center so they can track issues and their resolution.

The dbase has a number of reports that will allow the users to enter a start date and time and an end date and time. It then finds all records during that time frame.

Here are the scripts I came up with:

Script One

Setup for Search- When they push the Report button in the header

Go to Layout [ Choose Reprt ]

Insert Calculated Result [ Start_date_search, "" ]

[ Select entire contents ]

Insert Calculated Result [ Start_time_search, "" ]

[ Select entire contents ]

Insert Calculated Result [ End_date_search, "" ]

[ Select entire contents ]

Insert Calculated Result [ End_time_search, "" ]

[ Select entire contents ]

Set Field [ gt_op_on_duty, "" ]

Go to Field [ Start_date_search ]

Script two

The script for the actual search- When you enter a start date and time and a end date and time you then press the Enter button.

Set Error Capture [ On ]

Allow User Abort [ Off ]

#----------The following just makes sure they make all entries----

If [ IsEmpty(Start_date_search) or IsEmpty(Start_time_search) or IsEmpty(End_date_search) or IsEmpty(End_time_search) ]

Show Message [ Buttons: "OK", "Cancel", ""; Data: "You must specify a date and time range." ]

Halt Script

End If

#-----Here is where the calc is made and put into the field Date_of_Problem---

Enter Find Mode

Insert Calculated Result [ Date_of_Problem, DateToText(Start_date_search) & "..." & DateToText(End_date_search) ]

[ Select entire contents ]

Set Field [Date_of_Problem, gt_date]

Perform Find

[ Replace Found Set ]

#-------------------If it doesn

[comment]

The first thing I would do is insert a Pause Script step just before Perform Find, and see if the requests are filled as expected.

[Ender]

Hi Jim,

A couple possible issues:

Insert Calculated Result [ Date_of_Problem, DateToText(Start_date_search) & "..." & DateToText(End_date_search) ]

[ Select entire contents ]

Set Field [Date_of_Problem, gt_date]

The Set Field seems to be canceling the Insert Calculated Result.

Enter Find Mode

Insert Calculated Result [ Date_of_Problem, DateToText(Start_date_search) & "..." & DateToText(End_date_search) ]

For this step to work, the target field must be on the current layout.

Are Start_Date_Search, End_Date_Search, Start_Time_Search, and End_Time_Search defined as globals? If they are not, the entered values won't be available in Find Mode.

This problem aside, you might consider allowing blank entry on those fields and script it to make an open range search. If End_Date_Search is empty, then set the search to be ">=" & Start_Date_Search, etc.

[aaa]

You can use Smart ranges technology. This technology gives opportunity to see your data on given period in the portal. At the form you have Start_Date and End_Date, which you can change and have the set of needing records in a portal.

[ITOjim]

Thanks to all for the help.

The main fix (there were other changes) turned out to be that I did not have the field "Date_of_Problem" on the Prereport page.

Dont have a clue why this makes it all work, maybe someone out their can explain.

**NOTE**

I have currently eliminated the Time end of things just to get it to work. I may be back if this causes me problems.

*****

Here's what I have:

The Header has a button that takes the user to a Prereport page (via script 1 mentioned earlier).

There are now three fields and two buttons on this page.

Field one- Start_Date_find (DATE Global)

Field two- End_Date_Find (Date Global)

Field three- Date_of_Problem (Hidden behind Button 1- font: 2Pt, White)

Button one- Cancel

Button two- has Script Two attached to it.

You enter the start and end dates and then hit Button two and you go to the report page.

The report has a total of 6 fields. date of problem, operator entering, time of problem, type of problem, problem title and status.

Not real complicated, but it still a mystery to me why it needs the date of problem field on the "search" page, you cannot even enter text in it even if you could magically get under the button. hmmmm.

Is this another "why are we here" type of question or is there a legitimate reason for needing it?

Jim

[Ender]

The Insert Calculated Result step, along with Copy, Cut, Paste, and a couple others, must have the target field on the current layout to work. It's kind of like the step is doing what a user would be doing manually, and if the field isn't there, the user wouldn't be able to change access the field either.

The Set Field step does not have this limitation, and is preferred for setting data into fields. Unfortunately, with FM5/6, you cannot set a date range in a find with Set Field, as it only allows an actual date. So Insert Calculated Result is used.

It is not a good practice to place hidden fields on the layout. My recommendation is to have a developer layout in each file that has all fields. If an Insert Calculated Result step (or one of the others) is needed, have your script navigate to this layout, run the step, then navigate back to finish the script.

[ITOjim]

Thanks for the explanation and I took your advice and just created a layout with everything on it.

I started to add the time parameter and of course ran into problems. It is searching just during the time frame input during each day. Meaning put in 1am into start time and 11pm on end search for 2/25/05 and 2/26/05 and I only get records for each day between 1& 23:00.

i am thinking that maybe I should be timestamping instead. Where they would put in 2/25/05 1am and 2/26/05 11pm.

[DykstrL]

As Ender pointed out, it is a little difficult to script date range finds.

BUT, there is good news - there's a workaround. Use numbers instead. FileMaker dates are based on a number that represents the number of days since January 1, 1900 (I think thats the date), anyway, you can do range searches in number fields without bumping up against the invalid date format issue and having to use the copy/paste or insert calculated result.

For each date field, create a calculation field that equals the date field, but the result would be a number. You can then do range finds using "Set field" and the find fields can be hidden (not on any layout).

I attached a file that demonstrates the concept. I haven't tested it, but I am quite sure that this method will also work for version 7 also.

...hope it helps.

DateFinds.zip

[Ender]

For including times in this range find, use numbers representing the number of seconds. Convert the Dates and Times to seconds, and find for the numbers, similar to how DykstyL did this in his example.

To convert everything to seconds, use:

Seconds = (Date * 86400) + Time

[DykstrL]

Thanx, Ender, I forgot to mention the time thingy, which also works great. Now you've made me think about having to redo my example file.

[Ender]

To convert everything to seconds, use:

Seconds = (Date * 86400) + Time

Oh, make sure the result of this calc is a number.

[comment]

Another option is to emulate Excel's date & time, where time is the fractional portion of date:

GetAsNumber ( Date ) + Time / 86400

[ITOjim]

If you end up rewriting that example please let me know, I would like to get a copy.

What is the purpose of _layout (Global Number)?

Jim

[DykstrL]

It is used to take the user back to the starting layout if they cancel the find.