Filtering a portal using many-many relationship

[bideau]

Hi Everyone. Newbie here. I'm having a difficult time understanding filtering records from a portal. I'm using a many-many relationship with a join table between the two related tables. Here's the easiest analogy I can come up with to describe my situation.

I have a school. In that school, I have student records and I have class records. Students can be enrolled in multiple classes and classes can have multiple students. I've created a join table to handle this many-many relationship. Now, when I enroll a student in a class, I create a join table record linking the student with the class. I can go to a class layout and, in a portal, see all the students enrolled. I can also go to a student record and see all the classes they're enrolled in. So far, so good.

Here's my issue. When I go to the class layout, I have a button called "enroll a student". Currently, I go to a portal which displays all the students in school (using a relationship between the school and students). Clicking on a portal row enrolls the student. The problem, though, is that I don't want students who are already enrolled in that class to be displayed in the portal. The reverse is also true if I go to the student layout. I have a button which will display all the classes and by clicking on a class, the student is enrolled. I don't want classes that the student is already enrolled in to be displayed.

I'm having a very difficult time wrapping my brain around how to do this. Anyone have any ideas?

[Ender]

Check out the discussion of this in Chuck's thread:

http://www.fmforums.com/threads/showflat.php?Cat=0&Number=128500

Note that it's not necessary to use a custom function for this, as the later samples in that thread show.

[bideau]

Thanks for pointing out that thread. I'll have to study it and give it a try.

[bideau]

I studied the thread pointed out. But this doesn't really match my needs. The discussion involves pop up value lists to make selections. In my situation, I could have several hundred possible selections. I'd prefer allowing the user to make a selection from a list layout.

I've included a pared down version of what I've come up with. Under the class management section, select "update a class", select a class. The layout will show currently enrolled students. Select "add a student" and the list layout will show only students which have not yet been enrolled in that class. Select a student, and that student is removed from the available list and added to the roster. "add a student" uses the script called "select student from class".

Although this works as I need it to, I can't help but think that there's an easier and faster solution. I've tried a variety of things from not equal relationships to trying to manipulate found sets.

I don't really understand found sets very well. I know that given a class ID, I can get a found set of all the link records for that class. If there was a way to do it, it seems to me that I could simply omit the found set of related student records from the complete set of student records without having to loop through every student as the script is currently doing.

If anyone has the time or motivation, could you examine what I've done and give me feedback. Any help is greatly appreciated.

school.zip

[Ender]

Using the same relationship as we used for our dynamic value lists, you can jump to the related records to pick the name from a list view.

In my sample from that thread, a calc called AssignedIDs is used to list the IDs of the Users that are already assigned to a Group. You would need a similar field to list the IDs of the Students that are already assigned to the current Class. Relating this to another TO of Student with a <> relationship show the Students not assigned to the current Class. Then to jump to the Students not assigned, you would use a Go to Related Records [ Show only related records ] script step.

[wmugrad28]

Hi Ender,

Can you please update the link for Chuck's post? I need the same information. Thank you.

Greg

[bideau]

I modified the relationships according to your example. When I go to my select student layout, all students are listed including those that have been added to the class. I modified the name field in the update class layout to show me the assigned ID value list and the correct student IDs are listed for each class.

Obviously I'm missing something, but I just can't see it. Can you spot what I'm doing wrong?

Thanks again for all your help.

school.zip

[RalphL]

You might want to check out the files on thi s thread http://www.fmforums.com/threads/showflat.php?Cat=0&Number=117552&page=2&fpart=3&vc=1

[Ender]

You missed the Go to Related Records step to jump to the Unassigned Students.

See attached.

school.fp7.zip

[bideau]

Thanks for looking at it, ender. I'll check your attachment when I get home from work (I'm doing this on my own time).

[wmugrad28]

I don't know if the link was down or not, but it works now. I downloaded your file and it makes sense! Thank you!

Greg

[wmugrad28]

Hello Ender,

My selection portal has a "Go To Related Record" in a few scripts. Everything works fine until there are no more related records. When you watch a script run that step (when there are no related records) it doesn't go to that layout. It just stays on the last layout. Is there a way to force it to go to that layout? I want to use Get(FoundCount) = 0 to make the script exit early on if there are no records so a users time isn't wasted.

Greg

[wmugrad28]

Nevermind, this was discussed in post #142599.

Greg

[bideau]

Neither the unassigned students portal or the unassigned student list are getting updated unless I close the database and reopen it. It appears that the unassigned relationship isn't gettng updated. However, the assigned ID value list gets updated.

[Ender]

Have you employed the Commit Records [] step and Refesh Window [ Flush cached results ] step? This was discussed in the other thread and included in the version of your file that I posted.

[bideau]

Yes, I played your version of the database without changes. The only way the unassigned portal and the list layout get updated is if I close the database and then reopen it. It's as if the unassigned "not equal" relationship is not getting updated.

I'm not seeing anything that says "flush cached results" associated with refresh window. Is that just a byproduct of refresh?

[bideau]

I think I see what the problem may be. I need to download an update which will give me the flush cache option in refresh window. I'll try that this evening and see how it works.

[bideau]

Downloading the V3 update did the trick. It's working great now. I guess I'll have to pay closer attention to update notices.

Thanks for all the help.