Portal showing all entries not joined?

[Alan H]

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

[bcooney]

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.

[Søren Dyhr]

Use the forums search function and look for "dwindle"

--sd

[Alan H]

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

A

[bcooney]

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.

[Alan H]

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?

[Søren Dyhr]

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

[comment]

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.

[bcooney]

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.

[Alan H]

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, 2010 by Guest
Saw what FMForums actually posted!