2 Replies Latest reply on Dec 11, 2008 1:02 PM by Melinda

# Filter function?

### Title

Filter function?

### Post

Please bear with me as I try to explain what I need to do...  I'm not sure it can actually be done, but I am hoping someone will tell me it can...

I need to be able to create a report, by instructor name, that shows all of the projects a particular instructor has worked on - each project has a specific number id.  The problem is that we may have up to six instructors listed per project, so Jane Smith, for example, might be instructor 1 for one project and instructor 3 for another. Instructor fields are labeled Instructor 1 FN and Instructor 1 LN (two separate fields).

Ultimately, I'd like to be able to enter the instructor's name and have a list of all projects she's been a part of regardless of her instructor number.  Is this possible?  If I need to clarify anything in order for you to help me, please let me know, I am really stumped!!

Thank you!

Melinda

• ###### 1. Re: Filter function?

Melinda:

The problem with your current set up is that you have separate fields for the six different instructors.  You can relate the first occurrence of the instructor to the project, but how do you connect the second, third, fourth, etc. instances to the project?  I'm sure that is the dilemma you're facing.

You have a classic many-to-many relationship.  That is, an instructor may have "x" projects, and a project may have "y" instructors.  Connecting the two tables together by a common key will be difficult, because if you use an "InstructorID" (Last name and first name?) field, you certainly can display an instructor for each project, but you won't be able to display a project for each instructor.  The same result occurs with a ProjectID (Project Name?) field.  Therefore, you need to create a third table.  In that table, you would have the InstructorID and ProjectID field.  Here, you will be able to see each Project for every instructor, and every instructor for each Project.  Have this third table connect to the Instructor table through the InstructorID, and connect to the Project table through the ProjectID field.

This should get you pointed in the right direction, even if it means massively changing your current setup.  However, in the long run, it will be easier to manage.

Please let me know if you need clarification for any of the above steps.

TSGal

FileMaker, Inc.

• ###### 2. Re: Filter function?

Thank you, TSGal, for your prompt response!  I will give your suggestion of adding a third table a try and let you know how it works...  and, I will certainly be back for more help if I need it!

Melinda