Jump to content
Sign in to follow this  
susan

performing a find across multiple fields

Recommended Posts

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

Share this post


Link to post
Share on other sites

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 ]

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.