How to show found record(s) from related table in search

[tylertul]

Hello,

I am overlooking something very obvious here. Any help would be much appreciated.

I have 3 tables: Teachers->Students->Classes

I have a list view layout based on the Teachers table.

I have fields from all 3 tables in the list view.

In the Classes table there are 2 fields: Subject and Description.

I have 1 teacher record related to one student record related to 2 class records.

- The 1st class record -

Subject field contents: "Science"

Description field contacts: "Hard Class"

- The 2nd class record -

Subject field contents: "Math"

Description field contacts: "Easy Class"

PROBLEM:

When I am on the Teachers layout (List view) and search the field Classes::Description for the word "Easy" it does what I would expect, it shows the student record that is it related to but...

It shows the 1st record of the two related class records (i.e. Subject: "Science" Description "Hard Class")

QUESTION: How can I get it to show the related record for the which the match was based on (i.e. Subject: "Math" Description "Easy Class") in the list view.

Thanks!

[bcooney]

If you don't mind, I'd rather review the data model that you have. Why is a Teacher related to a Student?

I'm thinking, and this is a guess, that you need:

1. Each Teacher can teach several classes.

2. Each Student can attend several classes.

3. Each Class can have several students, but may have just one Teacher or may have several teachers (a team).

If the above statements are true, and each Class has only one Teacher, then you need the following tables:

1. Teachers

2. Students

3. Classes

4. A join table btw Students and Classes, possibly named, "Enrollment."

I'm not following what records you are trying to find.

Teacher's form layout could have a portal to all the Classes to which they are assigned.

Student's form layout could have a portal to all the Classes in which they are enrolled (based on the table "Enrollment."

Classes' form layout could have a portal to all the Students enrolled (again looking at the join table, "Enrollment.")

hth,

Barbara

[tylertul]

Thanks for the assistance Barbara!

I am experimenting with your suggested structure.

In the mean time a couple of quick questions.

1) I am unfamiliar with applications for join tables. Is the need for a join table determined primarily or solely by the need to have a many to many relationship?

2) You are most likely correct in that I need to change my data structure and again I am working on that. At the same time I am trying to understand a principle in my original question. Can you give me any clarity on that?

(i.e. How can you show the results of a search in a list view that was based on data a few relationships down the road? Without using a portal. I need to do quite a few different types of filtering and searching on the data and don't think that a portal is the way to go because of complexity and limitations on filtering by criteria that is a few relationships away from the primary data.)

Thanks again!

~ Tyler

[mr_vodka]

If you need all the data from the child records, then you should have your layout for those records. Why are you trying to do this from list view anyway?

[tylertul]

Mr. Vodka (My favorite drink as well)

Thanks for helping :

I have explored using a portal but I need to be able to filter on sub data. That appears to be very tricky.

For example: I want to build a layout based on SCHOOL and on it have a portal based on CLASSES. I then want to filter the portal to show me only what classes are about the subject "History" that are in a ROOM with a projector in it. I know I need another g_equipment and it seems like it should be in the SCHOOL table but I am not sure how to relate it to the ROOMS table to be able to display and filter data from the CLASSES table and related info (equipment) from the ROOMS table on the same portal row on the layout based on the SCHOOL table.

SCHOOL TABLE

g_subject

CLASSES TABLE

subject (many to many related to SCHOOL by g_subject)

fk_rooms

ROOMS TABLE

pk_rooms (related to CLASSES by fk_rooms)

equipment