performing a find across multiple fields

[susan]

I inherited a database with 1000 records, keeping track of various services a student is recieving, as well as the services being provided by any given teacher. Each student (record) may have up to 10 teachers, listed in any particular order. I need to be able to find Teacher X and run a report on the students she is seeing and the time being contracted for.

The database is set up with Teacher1, Teacher2, etc., through Teacher10. Teacher X could be any of the 10, depending on the other services a student is receiving. (The client doesn't have time or money right now for me to modify the setup. i.e., I have to make it work the way it is)

So, how do I write a script to look at Teacher 1 through Teacher10 in all the records and come back with the students seen by Teacher X?

Thanks.

Susan

[LiveOak]

This is a good lesson in proper database structure. If the Students were stored as individual records in your main file and a second related file was used where each record was a Student/Teacher combination, the problem would be much simpler.

However, there are a couple of methods of solving the problem in your current file.

1) you can write a script that creates 10 find requests, each setting one of the 10 teaher fields to the teachers name.

Create a global field:

gTeacherName (global, text)

The corresponding script takes the user to a layout and pauses so that they can enter the teacher's name in the new global field.

Allow User Abort (off)

Set Error Capture(on)

Enter Browse Mode

Go to Layout (Find Dialog) <--- you must create this layout and place gTeacherName on it.

Set Field (gTeacherName, "") <--- clear old teacher name entry

Pause/Resume Script

Enter Find Mode()

Set Field (Teacher1, gTeacherName)

New Record/Request/Page

Set Field (Teacher2, gTeacherName)

New Record/Request/Page

Set Field (Teacher3, gTeacherName)

New Record/Request/Page

Set Field (Teacher4, gTeacherName)

New Record/Request/Page

Set Field (Teacher5, gTeacherName)

New Record/Request/Page

Set Field (Teacher6, gTeacherName)

New Record/Request/Page

Set Field (Teacher7, gTeacherName)

New Record/Request/Page

Set Field (Teacher8, gTeacherName)

New Record/Request/Page

Set Field (Teacher9, gTeacherName)

New Record/Request/Page

Set Field (Teacher10, gTeacherName)

Perform Find()

If (Status(Current Error) = 401)

Show Message("No Records Were Found")

End If

Go to Layout(original layout)

2) Create a calculate field which combines all the teacher name fields in a single field and perform the find on this field:

AllTeachers (calculation, text) =

Teacher1 & Teacher2 & Teacher3 & Teacher4 & Teacher5 & Teacher6 & Teacher7 & Teacher8 & Teacher9 & Teacher10

The usual find rules apply. Unless you use and exact match find, a find for Bob Smith will find Bob Roberts and Mary Smith and Bob Smith.

-bd

[ August 21, 2001: Message edited by: LiveOak ]

[susan]

Thanks for the info...I'm going to try Method #1. I've used Method #2 and it works well for finding the students, but I also need to be able to display Teacher X at the top of the report (something I forgot to mention in my first post)--how could I do this with Method #2? Thanks again.

Susan

[LiveOak]

My best recommendation is to change you database structure. You can, however, put the teachers name at the top of the report by putting in a new field you define:

gTeacher (global, text)

Global fields exist once per file (not once per record) and can be used to store temporary information. You can script the find by using the gTeacher field for the user to enter the desired teacher name (as in the first script, which I've now corrected!). This gTeacher field can also go on top of your report. It's value doesn't depend upon which record you are on or which subsummary part it is placed in.

-bd

[fireflyrsmr]

live oak - thanks for the info in this post. i had the same type of question.