weird portal behavior

[Owen Mathews]

I'm uncertain whether this belongs in Portals or Relationships, since it pertains to both.

Perhaps I don't understand how Portals really work, but this seems counterintuitive to me. Say I have two tables, A and B, with a join table (call it J) between them. This accomplishes a many-to-many relationship by including the ID fields from both A and B. Additionally, I have a flag field in J whose purpose is to filter the join. To do my filtering, I can place a *global* flag field in A or B, and then in my relationship I can specify that not only do the IDs have to match, but also the flags.

Now imagine that I have set up a layout based on A. In it I put a portal viewing B. When I type in a particular ID for A, I should see rows in the portal corresponding to all items in B which have been related using J, and only those whose flag matches the global flag I set.

Here's what I don't understand. When I use A to store the global flag field and restrict the A-J relationship, everything works as expected. But if I use B instead (restricting the B-J relationship), the flag has no effect whatsoever: all rows in J that have A's ID will pass through to B and display in the portal, regardless of the value of the flag in that row.

To me this is counterintuitive because it shouldn't matter where the restriction is applied: in the first join from A-J, or in the subsequent one from J-B.

I've attached a file that does this; in this case A represents people, and B represents computers. (You can imagine that J keeps a history of all people who have worked on a computer, with the flag indicating that a person is currently working on that computer).

Any and all insight/advice is appreciated.

test.fp7.zip

[Ender]

You can't use a global on the match side of a relationship, which is what you would have in your filtered J-B relationship when referenced from A.

Just use two sets of Table Occurances, one to view the A-J-B relationship from A (filtered on A-J,) and one to view the B-J-A relationship from B (filtered on B-J.) Or connect them to make a A-J-B-J2-A2 chain of TOs.

[Owen Mathews]

Aha! I knew I was missing something crucial! Thanks so much. The database I'm working on is becoming so complex, and this is my first time using FileMaker.

So, to make sure I get this straight... You can only use a global as a relationship criterion at the very beginning of a "chain" of relationships (i.e. in the TO the layout is based on) when viewing related records either directly or through a portal.

Any idea why this limitation is there?

[Ender]

Think of it this way: When a global is used in a relationship, you can only see related records from the perspective of the table where that global resides. This is usually the "parent" side. There's some goofy chain relationships that are possible with a global as a filter in the middle of that chain (but only on the parent side.) In these cases, you can change the middle global filters, but it then requires a Refresh Window [ Flush cached join results ] script step to get the portal to refresh.