I have a many-to-many relationship with a join table between two tables, X and Y with join table XY. While viewing a record in X I have a portal of all the join records in XY that are linked to X. I'd like to show a portal of Y of all the records not linked.

I sort of have a solution. I have a cross going from X to Y and a filter with the following calc:

IsEmpty ( FilterValues ( x_Y~cross::id ; List ( x_XY::y_id ) ) )

This works as soon as I edit the filter calculation, but as soon as records get linked or unlinked, the filter is wrong. If I edit the filter calc, it updates. Refreshing the portals and window, even going to layout mode and returning to browse mode, don't show the correct records in the cross join portal.

Any suggestions for how to do this?

Edited June 22, 20187 yr by Chuck

Seems like you need another TO and another relationship. 

Three TOs: X with X as base table, x_XY with XY join table as base table, and x_Y~cross, with Y as base table.

X related to x_Y with X::id = x_XY::x_id.

X related to x_Y~cross with cross join.

Goal: portal into x_XY shows linked records, portal to x_Y~cross shows unlinked records.

Are these the actual names:X, Y,  x_y, and x_XY, x_y`cross?  Very confusing and hard to follow.  If they are not the actual names, actual names would help.

Posting a small sample file would also help, or some pics of what you want to show.

I apologize. I was trying to abstract out the problem from the specifics, and the actual tables would require quite a bit of explanation to get a handle on, but I can present the problem in terms that are, I hope, easy to understand.

Make X PERSONS and Y MEETINGS with the join table ATTENDEES. PERSONS and MEETINGS each have primary keys named id. ATTENDEES has two foreign key fields, person_id and meeting_id.

People has one table occurrence: PERSONS. ATTENDEES has a TO: persons_ATTENDEES. Relationship between these two is PERSONS::id = persons_ATTENDEES::person_id.

MEETINGS has one table occurrence: persons_MEETINGS~cross that is a cross join.

While on layout with PERSONS context, portal into persons_MEETINGS shows meetings the person is attending. I want portal into persons_MEETINGS~cross to show meetings the person is not attending. I'm doing this by filtering out records where the persons_MEETINGS~cross::id is not in the list of persons_MEETINGS::meeting_id.

So, perhaps there's a better way to do this, but this is working after I edit the filter calculation. But it doesn't stay working. I'll add an ATTENDEE record for the current person to one of the meetings, and the meeting still shows up in the persons_MEETINGS~cross portal. But, if I edit the filter calc, not so it returns a different result, but just rearrange it somehow, perhaps use Let variables or change it from using Let variables to a direct calc, the portal updates.

Are you committing the records after you assigned them to a meeting? Is the calc stored or unstored?

I've tried committing the records, refreshing the portal, refreshing the window, even entering layout mode and returning to browse mode doesn't fix the problem. It only seems to get fixed when I change the filter calc.

I'm familiar with the various techniques published at FileMaker Hacks, but those aren't working because they don't allow for attribute data in the join file. Are there other known technique to accomplish this?

Edited June 25, 20187 yr by Chuck