Excluding records from a portal

[pjp]

Situation: Tables

I have a table of contacts, call it CONT.

I am setting up a system for sending direct mail to some of my contacts. There are different direct mail programs, each targeted at a different group of contacts. So I have defined a table called DMGroup one record for each group of contacts.

One group will contain multiple contacts; one contact can belong to multiple groups. So I have set up a join table DMGroupMembers. Its records contain only 3 fields: a primary key for this table and two foreign keys, one to the CONT table (fkey_cont) and one to the DMGroup table (fkey_dmgroup). Thus, each record in this table represents the membership of a CONT record to a DMGroup.

Situation: Layout and Portals

I have set up a layout that enables me to select (and deselect) the CONT members of a given DMGroup. At the top of the layout I can select the specific DMGroup of interest - via a popup menue, the key for the DMGroup goes into a global storage field.

Below this field, I have two portals for the CONT table, one at the left and the other at the right. At the left, the portal shows candidate members for the DMGroup. On the right I have a portal showing the current members of the specific, chosen DMGroup. Setting up the relationships for the right portal has been easy: the global field containing the specific DMGroup key is linked to a TO for DMGroupMembers:fkey_dmgroup, and then this TO has a relationship linking DMGroupMembers:fkey_cont to the primary key of CONT.

Problem

The left portal, also based on a TO of CONT, currently shows all the records in CONT, but I want to exclude those records that are already a member of this DMGroup. For the life of me, I cannot think how to set up the relationship to do this.

Another way of expressing the problem is as follows: the left portal should show all members of CONT that are not shown in the right portal.

So if I can find a way for a TO of a given table to show the records that are NOT in another TO of the same table then I will be home.

Any suggestions would be greatly appreciated.

[Fenton]

I am not feeling clever enough to tell you exactly how to set it up in your situation; but the solution you want is known as "dwindling," and it can be used for either a value list or a portal (better for a portal actually, as a script can Refesh (flush)).

I first saw the technique here:

http://www.nightwing.com.au/FileMaker/demos7/demo703.html

Edited November 17, 2007 by Guest
Moved my file further down

[pjp]

Fenton:

Thanks. I think that this has solved the problem. That is, I understand the principle of the solution and have implemented it. My implementation is working correctly half the time, but I realize that the remaining problems are to do with some aspect of how I have implemented the solution.

[Fenton]

It is a little tricky. The Refresh Window [ Flush cached join results ] is needed for the screen to show the new "dwindle". Otherwise it doesn't appear to work, unless you something drastic to make the screen refresh (Commit Record is not near enough).

[pjp]

I think that would solve one phenomena I am seeing. I will add that into the script.

The other problem I have is that I am not seeing any result in the calculation of the cAssignedIDs_VL (using the notation of your solution) where there are no assigned IDs for that record. I should at least see a space there. I think that this is an artefact of how I have implemented the solution.

[comment]

There are rather significant differences between versions regarding this. Using a space is very likely to fail in version 9 - see:

http://www.fmforums.com/forum/showtopic.php?tid/185959/post/265365/#265365

[Fenton]

You'd be right there. It fails in 9. What is needed is a value of the same type (text or number) which will not match any of the IDs. I redid both the files (though you should use the List() one for 8.5 or better).

Dwindle.zip

[pjp]

Fenton:

I finally got around to debugging this today, and it works perfectly. Thank you again for your help. I know that I would never have thought of this solution on my own, and your solution has opened up a whole new toolkit of techniques for me.

Philip