Scripted multiple find problem

[Veronica]

My apologies if the answer to my problem is already somewhere in this forum but I can't find it and after looking and reading for the past two days, I am now totally confused.

I have a multiple FIND request problem. In essence there is a choice in one field and I am confused as to what remedy is appropriate.

The desired result:

A report of attendance for only those students actively enrolled in that class for any particular week.

We don't want students who were once but are not enrolled in that class during that reporting period/week. We also don't want students who are on the books but were enrolled at some future date of the reporting period/week.

My original found set consists of ALL students enrolled in ONE of many classes. After the user adds or edits records for this class, they click a button that takes them to a "Report Selector" layout. On this layout they enter a date, representing a particular week or month they wish to print an attendance report for.

This "requested date" is a global field (gWeek_of), used to calculate a number of other global date fields to be used in a find and/or "Set Field" request. They are named: DateVar1 thru DateVar5.

DateVar1= gWeek_of (Finds date enrolled)

DateVar2= gWeek_of + 5 (Date enrolled + 5)

DateVar3= gWeek_of (Used to find ExitDate and is probably redundant)

DateVar4= gWeek_of + 5 (Used in calculating ExitDate)

DateVar5= An "empty" field (Used in finding "No ExitDate")

Arbitrarily picking week 5 of class, what I want to find is:

All students in "x" class who were enrolled before week 6 and their "ExitDate" field is blank; or

All students in "x" class who were enrolled before week 6 and their "ExitDate" field contains a value between "DateVar3" and "DateVar4".

In my prior attempts I've gotten close to the result I'm looking for. I've gotten all the students fitting those date criteria. But, that's just my problem, I've gotten ALL the students listed in my report regardless of their classes.

As I move thru the process from picking the class to running the script that brings me to the "Report Selector" layout and entering the date of the report I wish to print, my record count does not change. As soon as I execute the script to print the report, (containing the search criteria), the record count changes reflecting all the students in all classes that fit my search criteria.

I would have thought that if I created a "globalClassID" field from the "ClassID" text field in the same TO, that would solve my problem. But NOOOoooOOoo! For some reason that global field does not populate. The source for this field is in the same TO. So what gives?

I have used scripted "If" statements and "Set Field" statements in various combinations and I can't seem to find the right combination to get me exactly what I want. Please Help.

In advance, Thank You so much.

Veronica

[RalphL]

I would like to the stucture of your file. How many tables and how they are related. What table are you using to maake the report?

Can you post a copy?

[Veronica]

Thanks, for taking a look at this.

The core tables making up the system are:

Student

Attendance

Class

Calender

The Attendance table has all the layouts involved in this problem. A Finder Layout, Enrollment, Report Selector, and Attendance Roster layout. Typically the user moves through the process in this order.

The Enrollment layout is the layout where the actual attendance records are kept/used.

There are two other files externally related to this file: Teachers and Schools. The Teachers file, though related is not yet used. The School File supplies the School Name through the Student TO.

When the user arrives on the "Report Selector" layout, they provide a date, (gWeek_of) for a report of weekly attendance that they want. Then they click a button to launch the script that prints the report. That script goes to the Attendance Roster layout; sets the DateEnter and DateExit fields; performs the find; and prints the report.

The "DateVarn" fields are global, calculated, date fields used to supply, (Set Field), the dates to populate the "DateEnter" and "DateExit" fields on the Attendance Roster layout.

I no longer have access to a ZIP application and I don't know any other way to download/upload the File(s). I hope the desription above is helpful.

Thanks, again.

Veronica :(

[RalphL]

Could you send a .pdf of the relationship graph. That may help.

You can download aa free version of Suffit http://www.stuffit.com

[Veronica]

Ralph,

I'm not sure what I've done, but here goes.

I don't think I created the right kind of file to upload but...

Anyway, I've been experimenting with what was supposed to worked before, as far as it went. I just discovered that after my first search, subsequent searches using different criteria failed to produce appropriate results. I keep getting the same results over and over. I now understand about scripting a find but there must be some way of modifying the search criteria without relying on the operator to do it. I am looking for a totally automatic way of getting appropriate result from varying search criteria at different times. This getting to look harder and harder to accomplish but I'm sure FileMaker Pro can do it somehow.

Thanks, again

Veronica

New_Sys_Graph2.txt

Edited August 11, 2005 by Guest

[RalphL]

Sorry but that was useless as is. I don't know if I cando anything with it. I was looking for a .pdf file. I don't know if you have that option on windows.

I am sure that what you want to do can be done, but without your file it will difficult. I need to see the structure, relationships, fields, etc.

[Veronica]

Ralph,

My apologies. There are things common to most frequent computer users that I don't know and I have little help here in this office. I've been working on this while we've had this little dialog going and I got so frustrated that I thought I'd check my old "FileMaker Pro 5.5 Bible" to see what it says about multiple scripted finds. (I guess I need to do more studying.)

I would still appreciate any advice on automating multiple FIND Requests based on situations similar to what I've described previously.

I realize that their may not be many books or CD's dealing with Advanced Scripting in FileMaker Pro 7 yet but is/are any currently out there still relevant to FileMaker Pro 7?

Thanks

Veronica

[Lee Smith]

Hi Ralph,

It opened up a browser page when I clicked on it. The file looks a lot like looking at a FileMaker File in a Text Editor.

Lee

[RalphL]

Hi Lee,

That is what I got too. I have tried to read it as fp7 and pdf with no success.

[RalphL]

The best overall book on FMP 7 is Using FileMaker 7 by Lane, Bowers, Love & Moyer.

The Help file is very useful. Each function and script step is described with examples.

I would like to know about how your tables are setup. Student & Class are most likely a many to many relationship with Attendance as a join file. What I don't know is what is stored in Attendance. Do you store the start and end dates here? Do you store the actual attendance here or anywhere?

Are you using Constrain Found Set in your script?

[SlimJim]

I no longer have access to a ZIP application and I don't know any other way to download/upload the File(s).

Hi Veronica

I see that you are using Windows XP and ZIP capabilities are built in. If you have an "untweaked" version then you can do the following:

right click on any empty part of the desktop go to the "new" option in the menu that pops up, usually at the bottom, select it and you will get a list of new filetypes you can make, one of which is Compressed Folder (that is a ZIP folder) select that and you will get a new folder on your desktop give it a name and add the .zip extension . If you now drag and drop files into this, from an explorer window, they will automatically be compressed and you can upload the folder(file) to fmforums.

[Veronica]

Hi Everybody,

Thanks for hanging in there with me. I think I found a partial solution to the problem.

First I didn't realize that when I executed the script to produce the report, the FIND steps that are in it superceeded or erased my original find. So I added another SET FIELD statement right after the ENTER FIND MODE statement to fill in that necessary information. It solved my problem up to a point.

The script looks like the following:

Allow User Abort [On]

Set Error Capture [Off]

Enter Browse Mode

Show/Hide Status Area [show]

Go to Layout ["SchoolAttendance" (EnrollmentTO)]

Enter Find Mode []

Set Field [EnrollmentTO::SchoolID; EnrollmentTO::gSchool]

Set Field [EnrollmentTO::DateEnter; "<" & EnrollmentTO::DateVar4]

Set Field [EnrollmentTO::DateExit; Case

(EnrollmentTO::gWeek_of & "..." & EnrollmentTO::DateVar4;

IsEmpty (EnrollmentTO::DateExit)

)]

Perform Find []

Sort Records [specified Sort Order: etc,etc]

Enter Preview Mode [Pause]

Go to Layout [Report Selector" (EnrollmentTO)]

Enter Browse Mode

This works up to a point or so it seems. There are 33 records in the file where 25 are for one school. I've inserted attendance data into these records within specified standards for weeks beginning 6/27/05 to the present.

The script seems to work for every week until I arrive up to 8/1/05. Starting at that date I get enrollments/attendance records that should be ignored because the don't fit the specifications of the Set Fields in the script.

For instance, one example: In printing the Attendance Roster for the week of 8/1/05, I want all students of a school (SCH05) who were enrolled in that school prior to 8/7/05 and whose ExitDate is between 8/1/05 and 8/6/05 OR whose ExitDate field is blank/empty.

What I get is a student or students whose ExitDate is prior to 8/1/05.

In reading up on "Set Fields" I saw where a case statement was used to find multiple criteria for the specified field and it seemed to work for me up to this point.

The attendance, EnterDate, ExitDate are fields in the EnrollmentTO. The SchoolID field is a text, pop-up field in EnrollmentTO but related to the SchoolTO.

When I say I studied up on Set Fields, I have some old and limited resources. So there may be something different in the processing of said field from earlier versions of FMP.

PS: I'll do that thing on my desktop and see if I can find that ZIP application, Thanks.

Veronica