I feel this must have been discussed somewhere, but I can't find it...

Table A and Table B are connected by a join table. I have a portal showing all entries in B that are joined to (linked to) A. Is there any easy way to show in another portal all entries in B that are NOT joined to A?

Thx

Yes. Create a multikey calc field in Table A that = List (portal::_kF_TableB_ID). Now you have all the IDs from Table B that are in the join table.

Then, create a new relationship from TableA to TableB where multikey ≠TableB_ID, and use that for a new portal on the TableA form.

Use the forums search function and look for "dwindle"

--sd

Thank you. I never figured out what that List function was for.

A

Oh, the List function is my best friend. (I need a life, lol). It's a way, also, to show grandchildren in a portal. If you create a calc field in the child of its children (the grandchildren), then you can use List to display the grandchild data on a child row in a portal.

Thanks bCooney

Actually, I needed to add one feature to your solution. If the entry in Table A has no entries in the join table, then the List variable is empty, and the "doesn't match" Table B returns no entries. I fixed this by making the List calculation be

If (Count (Join Table::_key) = 0; "Impossible value"; List (Join Table::_key)).

On your second point, I'm not sure I see why you do this. I create a portal on the Join Table, then add fields from Table B, and they just show up. What is the use of List here?

It's somewhat easier for the calculation engine prefix the result from List( with these two chars ~¶ ... and the effect is the same! Dealing with "nothing selected yet"

--sd

This won't work in version 9 and higher, if the matchfield is a Number field:

http://fmforums.com/forum/showpost.php?post/274656/

BTW, OP shows version 7 - but there's no List() function in version 7.

Yes, I got caught by that the other day. I changed the multikey calc to result in a number that I know will not match any keys in the child table if List is empty. In my case, I use "1" since all keys start with 1000 in my solution.

BTW: I ignore the FM info on the side panel. It's rarely correct.

BTW, OP shows version 7 - but there's no List() function in version 7.

Well that explains why I was unfamiliar with it—I really fell behind on my reading after upgrading from v. 7. I wonder how I change my default profile?—can't find anything in "My Profile" link.

And, no wonder the right column is often wrong--I changed it for this post, and it continues to reflect the wrong values!

Edited April 21, 201015 yr by Guest
Saw what FMForums actually posted!