4 Replies Latest reply on Aug 31, 2011 2:20 PM by philmodjunk

    Filter Table Occurrence contents with Many-to-Many Join Table

    FM_Rookie

      Title

      Filter Table Occurrence contents with Many-to-Many Join Table

      Post

      I am a relative FM newbie, so please speak slowly...

      I have separate tables so users can set up their own groups for a contact to be a member of (actually using a SQL DB) & edit them.

      Contact::ContactID -< ContactGroupMember::ContactGroupMemberID >- ContactGroup::ContactGroupID

      I need a table occurrence of "Contacts" that will take the contacts that are volunteers & display only "Volunteers."  This table occurence result will then be used in a script.

      Thanks in advance.

       

        • 1. Re: Filter Table Occurrence contents with Many-to-Many Join Table
          Sorbsbuster

          If I understand your question correctly, you could perfrom a Find in the Join table for the Contact Group ID for 'Volunteers', the use the script step 'Go to related records' to the Table Occurance 'Contact' (showing all related records).  This will show you all of the Contact IDs.

           

          You could try it from the ContactGroup Table and GTRR back to the Contact Table - may well work also (didn't try that).

          • 2. Re: Filter Table Occurrence contents with Many-to-Many Join Table
            philmodjunk

            Hmmm, First check your relationships as what you report has a problem, but it could just be a typo on your part.

            You need:

            Contact::ContactID = ContactGroupMember::ContactGroupMemberID

            ContactGroup::ContactGroupID = ContactGroupMember::ContactGroupID

            (Can't have Contact::ContactID storing the same exact value as ContactGroupMember::ContactGroupID as they are independent values here.)

            If you are using FileMaker 11 and have a field in ContactGroupMember that identifies who are the Volunteers, you can set up a filtered portal to ContactGroupMember with this portal filter expression to list only the volunteers:

            ContactGroupMember::MemberType = "Volunteer"

            You'll need to use your field name and the appropriate value for volunteers as entered in your database in place of the field name and literal constant that I used here.

            • 3. Re: Filter Table Occurrence contents with Many-to-Many Join Table
              FM_Rookie

              Sorry for the incomplete info.  Here are the tables:

              Contact::__kp_ContactID   =   ContactGroupMember::_kf_ContactID
              Contact::FName                      ContactGroupMember::_kf_ContactGroupID              =   ContactGroup::__kp_ContactGroupID
              Contact::LName                      ContactGroupMember::_kp_ContactGroupMemberID       ContactGroup::Description
                                                                                                                                              ContactGroup::Group

              I need the Contacts table occurrence that have the ContactGroup::Group/Description of "Volunteer" be shown.  Can't change the design of the DB because is it from a SQL DB that is being augemented by FM.  Maybe this can't be done.  In Access I would have just created a query, but I'm stumped here.

              Thanks again!

              • 4. Re: Filter Table Occurrence contents with Many-to-Many Join Table
                philmodjunk

                From what you show, all members of a group are either all "volunteer" or all something else. Are you sure that will work for you here?

                Are you listing groups for a contact or contacts for a group?

                To list all groups that are "volunteer" for a given contact, place a portal to ContactGroupMember on your Contact layout. Use this portal filter:

                ContactGroup::Description = "Volunteer"

                This functions very much like a SQL WHERE clause.