Search between dates plus more

[Crafty Coyote]

Help Please!

I am trying to create series of reports to show production waste from a two printing presses sharing the same database log. I would like to provide a menu with buttons for the user to choice from. All searches would need to search between certain date ranges along with other criteria. I have two global fields for start date and end date then would need to search for a certain press number, press and customer ect.

[VICH]

the script for the date range would be....

if[date >= daterange1 and date <= daterange 2]

then put in what ever you want it to do next and that should find anything inbetween those dates and if you want it not to show inbetween those dates just flip the signs

as for searching does it have to be all at once cause i would make them do the date range...then do the other searches

[Crafty Coyote]

Thanks for helping me,

If possable search for a certain customer between the date range, or a press between the date range.

[VICH]

you would have to have a global field for press and customer and set a script that included it kinda like this :

go to record first

loop

if[(date >= daterange1 and date <= daterange 2)and press = press global]

go to record next [exit afterlast]

else

omit record

go to record first

end if

end loop

if you used that i would check the date range AND the press using the "and" command you force it to find records that match both criteria and yes that function would omit ones that are of the same press in the global field but not in the date range and things that are in the date range but not the same press

[RalphL]

Why use a loop when a Find will do what is needed?

Since we are talking FMP 5 you will haave to use either Paste Result or Insert Result to put in the date range in the form DateToText(StartDate) & "..." & DateToText(EndDate). You will need to be a layout that has the date field for this to work. The other fields can use Set Field. Then perform the find.

[VICH]

hmm...i guess that works too, is their any real advantage to doing it that way? just wondering if i should maybe change my method

[comment]

For starters, why don't you try timing both methods?

[Crafty Coyote]

Thank you both for the help, I started with RalphL find first and it works great. I am very new to file maker and will also try Vich's method.

I also will need to remove records that have invalid date in some fields. Here is the code so far I would appreciate if you both help me again. Let me know if you need to see my code.

[comment]

Go into Find mode, enter a ? (question mark) into a date field and click Find. This will find all the records with invalid dates in that field.

[VICH]

ralph's would probably work more efficient than mine but it would hurt to try just to get familiar with scripting and the process you have to go through...it took me a while of trial and error to make that script work...it would be a good learning experience

Edited August 19, 2005 by Guest

[Crafty Coyote]

When I excute the script it stays in the loop, I have to close FileMaker to end the script.

[VICH]

no you just have to hit the esc key

and that means you left out an exit command

[Crafty Coyote]

Here is the code:

ALLOW USER ABORT [OFF]

SET ERROR CAPTURE [ON]

GO TO RECORD/REQUEST/PAGE [FIRST]

LOOP

IF[(FLEXO LOG::DATE 1 >= FLEXO LOG::START DATE

AND FLEXO LOG: DATE1 <= FLEXO LOG END DATE)

AND FLEXO LOG::PRESS NUMBER = PRESS#]

GO TO RECORD [NEXT;EXIT AFTER LAST]

ELSE

OMIT RECORD

GOTO RECORD [FIRST]

END IF

END LOOP

Thanks Vich for helping again, my escape will not stop the scrip, I have to hit the X.

I hope you have a nice weeking.

Leroy

[VICH]

its very odd that that doesn't work for you...it worked for me....you would probably be better off using the other solution anyways

[-Queue-]

I am not sure I see the point in the Go to Record/Request/Page [First] step after omitting a record, but I would add an Exit Loop If [not Get(FoundCount)] step just before the End Loop.

[VICH]

when you have an omit record step and you try to loop it it skips records...thats how someone explained it to me in a post i had similar to this a while ago and when i did it with out the go to first it would only omit a fraction of what it should have but doing it that way forces it to hit all the records but exiting once only records that fit the parameters are left

[-Queue-]

It will only skip if you include the Omit step with Go to Record/Request/Page [Next], since omitting causes you to go to the next record automatically. You are not doing this, so

Allow User Abort [Off]

Set Error Capture [On]

Go to Record/Request/Page [First]

Loop

If [FLEXO LOG::DATE 1 >= FLEXO LOG::START DATE

AND FLEXO LOG: DATE1 <= FLEXO LOG END DATE

AND FLEXO LOG::PRESS NUMBER = PRESS#]

Go to Record/Request/Page [Next; Exit after Last]

Else

Omit Record

End If

Exit Loop If [not Get(FoundCount)]

End Loop

should work fine and not keep looping over the same records.