Compare multiple fields

[meerestier]

Hi,

I have 2 tables

Courses table with unique Course_ID

and

Teachers table with

1.) a combined field with all Course_IDs separated by comma

Course1_ID, Course2_ID, Course3_ID

2.) Individual fields for every Course related:

Course1_ID

Course2_ID

Course3_ID

What is the best method to find all matching Courses for any Teacher?

I can compare one field but that does not give me the other Courses...

I need some basic advice on how to proceed...

Thank you

Cheers

Lars

[comment]

How many teachers can teach the same course?

[meerestier]

There can be up to 10 teachers...

[comment]

If more than one teacher can teach the same course, you should have three tables: Teachers, Courses and a join table for assigning teachers to courses (and vice versa).

See a basic demo here:

http://www.fmforums.com/forum/showpost.php?post/246136/

[meerestier]

Thanks, I have basic understanding of a join table (from sql) but how do I handle this in Filemaker. How do I create it etc.?

Just a basic method pointing me in the right direction...

Maybe I can avoid the join table, since I have the information stored in the 2 tables already (distributed across different fields though)

Thanks again...

Edited August 23, 2010 by Guest

[comment]

how do I handle this in Filemaker

That's what the demo shows?

I have the information stored in the 2 tables already (distributed across different fields though)

You do - but as you have found out, it's not very usable.

[meerestier]

Since the data is provided "as-is", I don't want to create any new join-tables.

Would it be feasible to make a script that gradually narrows down the search:

1. Search for Courses that have Course_ID = Teachers::Course1_ID

2. Search for Courses that have Course_ID = Teachers::Course2_ID

3. Search for Courses that have Course_ID = Teachers::Course3_ID

4. Search for Courses that have Course_ID = Teachers::Course4_ID

etc.?

Any advice on this?

Thnaks again...

Cheers

Lars

[comment]

It would be even more feasible to define a calculation field (result is Text) =

List (

Course1_ID ;

Course2_ID ;

Course3_ID ;

...

Course10_ID

)

and search there.

In addition, if you define a relationship matching this calculation field with the CourseID in Courses, you can place a portal to Teachers on a layout of Courses (and vice versa) and/or use Go to Related Record[] to show all teachers of a course, or all courses of a teacher.

However, you will still be limited in many aspects with this structure - so the question is how often is this data provided and how do you intend to use it. Once you create the proper structure, the process of importing the given data can be automated (basically, you do 10 imports, each time importing the TeacherID and one of the 10 columns).