A Tricky Relationship

[csharpmin]

I have 5 tables

contacts

groups

groupmembers

events

donations

groups can have contacts associated with them (through the join table "groupmembers"), and they can have an event associated with them.

donations have a contact associated with them and can have an event associated with them.

I have all these relationships set up and working, but the following relationship is giving me trouble.

On the donations page, I want:

A. to show all groups that the contact related to the donation is a member of.

B. ALSO, groups should only show if the event related to the donation matches the event related to the group.

I've been able to create a relationship for A, by matching the fID_Contact on the donations table to the fID_Contact in the groupmembers table.

And I've been able to create a relationship for B, by matching the fID_event on the donations table to the fID_event in the group table.

But I can't accomplish A & B in a single relationship because they point to different child tables.

I've tried to match the fID_event to a calculated lookup of the event_ID in the groupmembers table. But I can't get the lookup to work.

I'm not sure if I just need help getting my lookup to work, or if there's a better solution, like a self-join or something.

If you can help, I'd appreciate it. (By the way, I can't upload a clone because my company won't allow it.)

Edited September 4, 2010 by Guest

[comment]

I am having a hard time following this: it seems that the relationship between Events and Groups is one-to-many - i.e. each group has at most one event. Is that correct?

[fmbiz.net]

Hello csharpmin,

The 'tricky' part of this relationship is that you are asking for information not in the context of your Donation table. This kind of requirement is actually pretty easy to do if you are in an SQL environment. But FileMaker has a distinct way of building its data chain.

So you have two choices:

1. Use ESS with SQL queries; this would require you to mirror your existing tables on another Server, or

2. Create an additional multi-key field on the donation table along with adding two additional Table Occurrences, one for Contacts and one for Groups. The context would of course be from the Donation table. The 'trick' here is to use the new multi-key as the link back into the Groups that are not only affiliated to the Donor, but also sponsor the Event in which the donation occurs.

When a new donation is entered into the system and a Donor is assigned to the Donation, a script is triggered. This script will set the multi-key based on data from Events and Contacts (Group Membership).

I have included a sample Database of how I would do this, provided that I have completely understood your requirements listed above. A better and more elegant solution would involve using a Recursive custom function to determine the multi-key when the Donor is selected. But I will leave that upgrade for another time.

Hope this helps somewhat.

Best Regards,

Charlie - FMBiz.net

Dontations.fp7.zip