New learner frustrated with "many-to-many" portal

[Glacier]

Hi, first time to the forum.

 

I've been learning FM on and off for about a month, now I'm stuck.

 

I have 3 tables (Student, StudentClass_Join, Class) for a "many-to-many" relationship. I made a layout based on Student, and on the left-hand side I insert a portal controlled by a drop-down list to list all the students registering a certain class. When clicked, an individual name on the list will show the detail information of that individual on the right-hand side.

 

The Problem: Only part of the candidates registering that class will show up on the portal list.

 

The portal filtering design is based on the post "http://filemakerinspirations.com/2010/10/dynamic-portal-filters-with-multiple-criteria/"

 

I believe there is something I didn't understand and make it right with "many-to-many" relationship, just don't know Where and How to correct it.

 

Forgive me for the newbie question. Any ideas would be very helpful, and, if you could, please give some details for me to understand.

 

Thank you very much.

 

The file I made is attached.

Student-Class.zip

Student-Class.zip

[eos]

Your filter field needs to come from the same context as your layout

 

Student 2::gFilter = Class::kp_ClassID

 

See attached file.

Student-Class_eosMOD.fmp12.zip

[bruceR]

And here is a slight variation.

Student-Class_bfr_MOD.fmp12.zip

[Glacier]

Thank you, eos and BruceR, really appreciate your time!

eos, you fix my problem on the ClassName field, and it's great. However, for "Science" and "Social Study" the list is still missing candidates. I just don't understand why.

BruceR, your modification is brilliant and it's working great. I just need time to digest your ideas given my limited knowledge of FM.

Thanks again for the helpful replies.

[eos]

eos, you fix my problem on the ClassName field, and it's great. However, for "Science" and "Social Study" the list is still missing candidates. I just don't understand why.

 

I didn't initially study your file carefully enough (and thus rightly didn't deserve any credit); the problem is that your filter calculation only looks at the first related classID of a student.

 

What you actually want to do is check if that class is contained within a list of all classes of a student; so either use … 

not IsEmpty ( FilterValues (  Student 2::gFilter ; List ( StudentClass_JN::kf_ClassID ) ) )

… as a filter expression – or use a different relationship chain (and better TO names …) and a filtered relationship, instead of a filtered portal, which will give better performance with a growing number of students.

 

As an aside, I suggest you add a primary key to the join table, and in general, use meaningless, auto-entered IDs as primary keys for your relationships (instead of codes/identifiers that are outside of your control, and consequently are not guaranteed to be unique or persistent).

 

You can still use a class code or another human readable and meaningful nomenclature on top of that; e.g. if you start to use IDs, then for the value list of your global filter/selector field, create a value list with the primary class ID as the first field, and a calculated text field of class code and subject as the second field.

[Glacier]

eos, thank you for the explanation. I'm just going back to the drawing board.