Jump to content

Exclusion List - Newbie help


emjay
 Share

This topic is 5751 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

Link to comment
Share on other sites

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]

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by Guest
Link to comment
Share on other sites

  • 3 weeks later...
  • Newbies

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

  • Newbies

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?

Link to comment
Share on other sites

This topic is 5751 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.