Scripted Find Problem

[Veronica]

I am sorry to bug you guys and gals but I need some help.

I am trying to do a scripted find because I don't want the operators to have to figure out what they need to be looking for.

I want to do a scripted find on three fields: SchoolID, EntryDate and ExitDate. The SchoolID and EntryDate fields are global fields and calculated from user provided information. No problem there. These work by themselves or together every time.

However, when it comes to adding the third field to the search process it breaks down.

I've set up global fields developed from calculations in order to enter variable data based on multiple criteria for the ExitDate field. The first set of criteria is a date range and the second set of criteria is simply a blank/empty field.

I have tried using a SetField statement using a case function to fill the ExitDate field with the search criteria, but it doesn't work. I have tried using the Extend Found Set step but it only works the first time. Subsequent uses revert to the original data, over-riding the new calculations. Have I expressed this problem clearly?

Doing the search manually and using the Extend Found Set command works perfectly. Why can't this be scripted? I have been struggling with this for 2 weeks and I am exhausted and at an end. Please help!

[SlimJim]

Can you specify what exactly doesn't work when you use the SetField to fill the ExitDate field?

Do your calculations produce the data you are expecting?

Does the SetField put the data into the ExitDate field?

Does the find fail? or fail to produce the expected results?

Are there any error messages from FMP at any stage?

If you can do the search manually then it should be possible to script it? Could you post the script, or at least the part of it that is failing?

[Veronica]

Hi Slimjim,

What doesn't work is when I try to set the ExitDate field with a date range, such as: 7/11/2005...7/17/2005 or IsEmpty(ExitDate); using a Case statement.

If I simply use one criterium at a time it works but that doesn't give me the results I want. I want records where "SchoolID=SCH05" and "EnterDate<7/17/2005" and where "ExitDate is 7/11/2005...7/17/2005 OR IsEmpty".

The only time I get the results I'm looking for is when I manually perform a find with an "Extend Found Set" option.

I would like to send you a copy of the script but I have to confess that I don't know how to make it transferable/uploadable. Sorry.

Thank you in advance.

[comment]

You cannot enter a calculation into a field in Find mode.

In general, you need a separate request for every OR search. For example:

Enter Find Mode

Set Field [ SchoolID ; "SCH05" ]

Set Field [ EnterDate ; "< 7/11/2005" ]

Set Field [ ExitDate ; "7/11/2005..7/17/2005" ]

Duplicate Request

Set Field [ ExitDate ; "=" ]

Perform Find

will find all records where SchoolID = "SCH05" AND EnterDate < 7/17/2005 AND ExitDate is either between 7/11/2005 and 7/17/2005 or empty.

[swf]

Another strategy is to create a calculated field to flag those entries. Then you can use a calculation to place an X in the records you want flagged. Then just perform a find with x in flagfield. See attached file.

[swf]

Sorry, Here is the attached file.

flag.fp7.zip

[Veronica]

My Thanks to you all. Both solutions work fine, really fine! As a result of your help I found a flaw in my logic and also solved the next thing on my "to do" list.

For the benefit of anyone that may look at this series of posts, I am producing a report of enrolled students during a certain week. This report could/will be printed at anytime during the course of the school year. The flaw in my logic was that I would have missed any student whose ExitDate was greater than the range of dates provided, (representing a particular week), but who have still been enrolled. So instead of a range of dates representing a particular week I'll use a value that is "greater than" the first day of that week.

The second solution offered above not only lets me print the record of all enrolled students in a given week but also allows me to provide a count of enrollees.

This is a great community of helpful people. Bless you ALL.