I am helping some manage a database in FMP7 used to keep track of donations.
We have two tables: prospects (the people) and donations (one entry each
time a donation is made). I have a subtotal field so when I sort by last
name, all of the donations are totaled for the person and you can see how
much each person has given.

I would like now to do a sort based on the subtotal to generate a list based
on the total each person has given. But the term "subtotal" is greyed out
in the Sort dialogue box. What do I need to do/change to get a report
sorted by the total amount each donor has given???


Thanks

In article <1KGdnZACZuS2Xk7fRVn-hA (AT) comcast (DOT) com>, "Stephen Larivee"
<NOlariveeslSPAM (AT) yahoo (DOT) PLEASEcom> wrote:

You can't sort by Summary fields.

There's a number of ways to obtain that same value though.

Since you've must already have a Relationship defined to link a
Prospect record with all the Donations records for that person, the
easiet way is to have a new Calculation field in the Prospects table
that simply totals the RELATED values using the Sum function.
ie.
Total Contributions Calculation, Number Result, Unstored
= Sum(Relationship:onationAmount)

where Relationship is the name of the Relationship you're using to link
the records, and DonationAmount is the field in the Donations table.

Since each record now has it's own total, this new field can be used to
sort the records and produce a report from the Prospects file along the
lines of:

Name Total Contrabutions



Similarly, you can use any of the aggregate functions via the
relationship link. For example, you can count the number of related
records by using the Count function
ie.
Count(Relationship::LinkField)

where LinkField is the field used to match Prospect and Donation
records to each other. It's best to use that field since you know it
has to have data in it - the Count function will not count records
where the specified field is empty.

Others that may be useful are:

Average(Relationship:onationAmount)

Max(Relationship:onationAmount)

Min(Relationship:onationAmount)





Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)