Record Summary from One File to Appear in Another File

[richpayne]

How do I get a summary of records from one file to appear in another file?

I am trying to track donation records for a non-profit organization. I have a Donor file which lists all of the personal info on the donors, and a Donations file which lists all of the individual donations that a donor makes. So, I may have 500 Donor files and 10,000 Donation files.

In the Donor file I can call up a Donor Summary page and see all of the donations that a person made via a portal over to the Donations file. The portal shows me all of the donations for that particular person, but I don't always want to see "all" of their records.

For example, I want to see all of the donations that Joe Blow made in 2001. In the Donations file this is easy, and I can generate a total of his donations (say $100). But I can't get this total, which is based on a sub-set of the total records in the Donations file, to appear over in the Donor file. The portal in the Donor file insists on showing me "all" of Joe's records from the Donations file.

I have tried portal and non-portal options but cannot get a sub-set of records in the Donations file to appear in the Donor file. The point of this is to be able to generate a single thank-you letter in the Donor file for each donor, which will include their total gift amount for any given period.

If I generate the letters in the Donations file I end up with a letter for every donation rather than a letter for every donor. If I use a portal in the Donor file to view the records from the Donations file, I end up with "ALL" of Joe's records instead of just the ones for 2001.

I'm stumped! Any help would be greatly appreciated!

[LiveOak]

You need to create a special kind of relationship from the Donor file to the Donations, one based upon a composite key. In this case create the following fields:

In the Donor file:

gYear (global, number)

iKey (calculation, text, indexed, ASCII) = gYear & "-" & DonorID

In the Donations file:

iKey (calculation, text, indexed, ASCII) = Year(DonationDate) & "-" & DonorID

Create a relationship called "FilteredDonations" with iKey matching iKey.

Create a portal based upon this relationship and place whatever fields you wish in the portal.

Create a field in Donors:

FilteredTotal (calculation, number) = Sum(FilteredDonations: onationAmt)

In operation you will enter a date into gYear to select a display of donations for the selected year for the Donor record you are on. The FilteredTotal field will display the total of the records in the portal.

Recognize that your actual field names will have to be substituted for DonationDate and DonorID.

If you need to be able to view an arbitrary date range, you will need to relate build a key in Donations:

iKey (calculation, text, indexed, ASCII) = DonationDate & "-" & DonorID

and a Multi-key in Donors. This multi-key is a global field populated by a script to contain data which looks like:

5/12/2000-34 <cr> <---- Where 34 is an example DonorID and <cr> is carriage return.

5/13/2000-34 <cr>

5/14/2000-34 <cr>

5/15/2000-34 <cr>

5/16/2000-34 <cr>

This would display a 5 day period of dates. For a whole year you would need 365 entries, if you want to be able to look at an arbitrary range. You can populate the global field with a script or use a FM plug-in built for this purpose.

-bd

[richpayne]

Thank you so much! I was able to get this to work, and now I can get a total for the years to show up correctly.

However, I still have what may be perhaps a less complicated issue pertaining to the same question. If I can get my Donations database to sort the way I want it to (i.e. find all the records for January 1999), can I essentially "select" these records in Donations (via a checkbox or whatever), and then have just these selected records appear over in Donors? If so, then can't I use a formula similar to your first solution to generate "one match" for each donor with a total, and then generate my letters based on that?

Maybe I made this sound too complex - all I want to do is have a certain selected set of records from Donations appear over in Donors. I can do all the sorting and finding in Donations, but I only want one record for each donor to "match" over in Donors, with their total. I want one record to show up in Donors, "Joe Blow", with whatever total applies to the 5 or 6 records I found for him over in Donations. That now works for one particular year, but it really needs to apply more to a date (>1/1/1999 for example). Does that make sense? Thanks again for your help!!