Finding Records

[Thom]

Here are a few ideas:

1) Create an OPTIONS file with fields pupilID, optionName, optionLetter. Make a relationship from the PUPILS file to the OPTIONS file based on pupilID. In the PUPILS file, make a portal with this relationship. Each option is a row in the portal. You could then search for French in the portal, and any PUPIL record with French as an option would be found.

2) With optionA through optionE as fields in PUPILS, make a calc field that combines the options as a return separated list. Search the calc field:

options(calc,text)=optionA & "

[Bob Ratcliffe]

Thom,

Thanks for getting back to me so quick! So far I'm trying No.2/2a but I'm stuck with what I should be doing with what to be doing with the "Go to related record" script bit...is it just the one command or do I need a lot more (sorry, but this I'm relatively new to scripting!!)

Thanks, Bob

[Vaughan]

The problem is that the options should be in their own related database.

[tlsparker]

Looking at Thom's suggestions, his suggestion #1 appears to make the most sense. A little redesign will make your life much easier:

Instead of having five different fields for Options A through E, just create one Option field. In this field, you enter either A, B, C, D, or E. Next, create a Subject field (This is where you would enter French). Assuming each student has five options, A through E, they would each have five records in this database.

Example: Options database

Student ID Option Subject

222 A French

222 B Algebra

222 C History

222 D PhysEd

222 E Advanced Particle Physics

Now, you should also have a students database that has one record for each student. This file also has a student ID field. The files are related by the student ID field and you can create a portal that displays Options and Subjects for each student.

If you use this file structure, you can search in the Options database by finding for French in the subject field and you will find all the students who are taking French regardless of the Option. You could perform a similar search in your portal in the Students database.

Hope that makes sense.

[Bob Ratcliffe]

Hello at FM Forum,

I've sought your help in the past, and I think this site is great for

helping people like me who is not an expert, but loves FileMaker and

wants to improve my knowledge and understanding of it! As a teacher in

secondary (11-18) I've been creating loads of relational database

systems within the school using FM5. I now have a little problem which

I hope you maybe able to help or at least point me in the right

direction......

I have one file called OPTIONS (I use it as part of a relational system). In each record I have:

Pupil ID

Name

Surname

Option A

Option B

Option C

Option D

Option E

I use a layout that shows each record as a row. If I want to FIND out

which pupils are studying say, FRENCH. I have to go to the FIND mode

and choose FRENCH in the Option A block...I then have to repeat that in

Option B, and then Option C, etc. (Bear in mind that French could appear in any of the Options)

Is it possible to write a script that will allow me to find

what pupils are studying French in any of the Options all at once,

without having to go through each of the Options in turn?

I'm really needing the basic of help on this one!!

Here's hoping,

Bob Ratcliffe

[Bob Ratcliffe]

Dear All,

It now makes complete sense! Thanks very much.

Bob