I am trying to derive an exclusion list from related tables.

Simply - I have 3 tables, Groups, Contacts and Joiner.

There are many Groups. There are many Contact. One Contact can be in Many Groups and the group membership is stored in the Joiner table.

Using different layouts with portals I can 1. Show a Group and in a portal all it's related Contacts and I can 2. Show a Contact and in a portal all the Groups it belongs to.

What I want to do is show a Group and all the Contacts that do not belong to it. (Ultimately I want to create a script to add Contacts to groups that are not already members)

I tried by putting a "not equal" sign in the relationship between Joiner and Contact. It does not work.

Any advice will be greatly appreciated.

Thanks

This is somewhat easier in version 8.5, but still requires some work:

In Groups table, define an unstored calculation field (result is Text) =

List ( Joiner::ContactID ) & ¶ & " "

Uncheck the "Do not evaluate if all referenced fields are empty" option.

Define a relationship from Groups to a new occurence of Contacts (let's name it AvailableContacts):

Group::newCalcField ≠ AvailableContacts::ContactID

A portal based on this new relationship will show all contacts that do not belong to the current group.

Note than any script that adds/removes a contact to a group should end with:

Refresh Window [Flush cached join results]

Hi Comment,

That is a beautiful thing. Thank you both for your quick response and better yet, the right answer.

If you wouldn't mind, could you briefly explain how it works? I don't want to be a hog here, I just want to understand why the calc field List() does the trick.

Thank you again

The List() function collects all the RELATED contact IDs in a return-separated list. The last empty value is required so that the field is never truly empty (a relationship will ignore an empty field).

The result is a multi-key field (see Help > Working with related tables and files > About relationships > About match fields for relationships - last note).

Defining the relationship to use the 'not equal' operator makes it look for contacts whose IDs are NOT included in the list.

Once again, thank you for taking the time and for providing great information.

Looking at that part of the manual reminded me of the old chestnut:

Shall we say, pistols at dawn?

We can say it. I don't know what it means but we can say it.

I read the part you reference in my search for a solution but it didn't make any sense to me. Your explanation and subsequent re-read made it clear.

Thanks again.

Edited December 18, 200619 yr by Guest

Can this be done in Filemaker 8.0, i.e. without the List() function of version 8.5?

I really want to implement a portal-to-portal move interface - imagine my chagrin when List() was not available in my calculation window! :D

I really want to implement a portal-to-portal move interface

Well with appropriate freshing measures, which unfortunatly is most safely done scripted could ValuelistItems( over the related dynamic value list be used.

If you just were on fm7dev could you have utilized this CF: http://www.briandunning.com/cf/39

But yet another option is to something in the vicinity of this:

--sd

port2port.zip

I'm looking for a little more. Something like this:

User #1: 

    Languages: French    Avail. Languages: Spanish

               Russian                     English

                                           Korean



User #2:

    Languages: French    Avail. Languages: Spanish

                                           English

                                           Korean

                                           Russian

Where a Languages table contains all the languages the system knows about; Avail. Languages calculates and displays, somehow, the DIFFERENCE between the portal showing the assignment table of languages to users AND the table of all available languages.

Can this be done in FM 8?

I think the demo I have posted here does exactly that.