Portal relationship logic with aggregate functions

[HandOfCode]

Hello all, I'll start of with a basic schema of what I'm using then I'll describe my problem :

so I have 6 tables:

Event Donations 1:N (occurrence of the event table)

Donations N:1

-_DonationID

-_EventID

-_PeopleID (who donated it)

People 1:N

-_PeopleID

TeamMembers N:1 (bridge table)

-_PeopleID

-_TeamID

-MemberTotalDonations (calculated field of sum())

Teams 1:N

-_TeamID

EventTeams N:1 (bridge table)

-_TeamID

-_EventID

Event

-_EventID

where each cardinality applies to the table bellow (ex. 1 people can have many donations)

I have a layout based on the EventTeams layout with a portal currently based on TeamMembers. What I want to show in the portal on each row of a team member (the team members display correctly) is the MemberDonationsTotal that is related to that specific _EventID but what I get is an overall sum of all donations that person has made.

for ex. we have 2 people: foo and bar

foo participates in 2 different events baz and bax

foo donates $22 to baz and $33 to bax

bar only participates in baz and donates $38

if I show the event team that foo and bar were in (they were on the same team) for the baz event, foo shows a total donation of $55 and bar shows $38.

when I show the donationID on the rows as well it shows the wrong event ID for one of the people so I know it's pulling the wrong data.

Any ideas what to do to fix this? I can provide more info if it's too confusing.

Also what kind of joins does filemaker use? this looks kinda like a right outer join to me.

[comment]

Let me rewrite what I understood about your structure (mainly for the sake of my own sanity):

People -< Donations >- Events -< EventTeams >- Teams -< TeamMembers >- People

Now, in order to see what you described you will need a chain of relationships:

EventTeams >- Teams -< TeamMembers >- People -< Donations

with the last relationship filtered by the currently viewed event.

This can be reduced to two relationships:

1.

EventTeams::TeamID = TeamMembers 2::TeamID

2.

TeamMembers 2::PeopleID = Donations 2::PeopleID

AND

TeamMembers 2::gEventID = Donations 2::EventID

The global gEventID field in TeamMembers must be synchronized with the EventID field of the currently viewed record in the EventTeams table; this can be done by triggering a script on RecordLoad in the EventTeams layout.

A calculation field in TeamMembers* = Sum ( Donations 2::Amount ) should produce the desired result.

---

(*) evaluated, of course, from the context of TeamMembers 2

Edited December 1, 2010 by Guest

[HandOfCode]

Ah it works! brilliant thank you!