need assistance with summaries

[radams]

I am designing a "donation" file for a NPO somewhat like was discribed in a

thread back in December started by Ron Fugazzi. (I am just now catching up

on my mail). What he described sounded a lot like what I am doing (a

renovation of what I have worked on for 6 years). I use FMP5. PC platform

on a network. Previously the bookkeeper had to enter deposits on excel,

enter totals to Peach Tree, and post donations for receipting on FMP. I am

trying to cut out the extra work and narrow it down to FMP and Peachtree.

I have come up with three related files.

Deposit

Deposit Record

Donor Record

We have many donors, some of whom give to several "funds" monthly.

The relationships are:

Donor Record :Donor ID->Deposit :Donor ID (this is a look up and works great)

Deposit :Deposit No->Deposit Record :Deposit No (this works great)

Deposit Record :Donor ID->Donor Record :Donor ID (this works great) also

Deposit Record:Fund->Donor Record:Fund

When a deposit is entered it is given a # and linked to the donor (works)

Then the Amount is entered in a portal to Deposit Record for each Fund (drop

down list) it is for (works) creating a seperate record for each fund

donation.

In the Deposit Record the funds can be sorted and summarized (works)

Problem #1 I am still trying to figure how to have current month donations

totals for each fund and ytd totals for each fund, major at this point

because it is only January.

The Donor Record has a record for each Donor and a portal to Deposit record

for each donation and fund.(works) And a field that summarizes total

donations for that Donor. But still no individual Fund YTD Summaries.

Problem #2 However, here I meet my BIG problem. I need to print a receipt

(including a short monthly letter from the director) summarizing the monthly

and ytd totals for each fund the donor has donated to. (related to the

other problem in the Donor Record). I cannot figure out a way to show these

summaries: fund name, current month donation, ytd donation, for each fund.

I have tried a summary part sorted by fund, with the field for Donor

Record:Fund, Donor Record:SumFund(Amount), but this doesn't work and I

haven't been able to create a sumfund(ytd amount). I just get the first

fund on the Donor's list and the sum of all that Donor's Amounts. I have

tried the receipt in both the Donor Record and the Deposit Record with no

luck either place. I cannot summarize the Fund as I cannot summarize a text

(atleast I think I can't).

Does anyone have any great ideas on these things. Mainly how to summarize

Fund by Donor, current donations and YTD Donations and put them in a Receipt

format with summary parts or otherwise. My brain just cannot conquer this

after many late nights.

My previous format (which I am revising) was using repeating fields,with

numerous fields for funds and months of the year, summaries of funds, etc. I

am trying to keep the number of field to a minimum and the bookkeepers work

to a minimum with one step scripts and such. I know basic scripting but get

lost with the IF-THEN and Looping etc, containers, globals and such.

I would greatly appreciate anyones imput as the bookkeeper is waiting to

enter new year stuff until I am finished.

[This message has been edited by LiveOak (edited January 13, 2001).]

[danjacoby]

I'll try to answer your questions with one general instruction:

When you summarize, you need to create a "sort field" on which to sort. For instance, to break out donations by year, for example, create a "year" field (make it a calc field that equals "Year(Date)", so it returns the year of the donation). Then create a sub-summary part when sorted by the "year" field.

That should get you started, at least.

[LiveOak]

A couple of ideas. I'm not sure the difference between your deposit and deposit record files, but in the file that stores the deposit transaction create two new calculated fields:

Month (calculation, number, Stored Indexed) =

Month(DepositDate)

Year (calculation, number, Stored Indexed) =

Year(DepositDate)

These fields will allow you to do a couple of things. First, you can sort by Year then Month to create reports with subsummaries by each. Second you can create a relationship from a globals in the Donors file (gMonth, gYear both global number) and use these relationships to create a receipt with summary numbers for month and year or portals to show records for month and year. I usually don't recommend printing transaction reports from the Donor file using portals, but the number of contributions is probably limited to some small number (2/month?), so this would probably work for a receipt.

To trigger the receipt, use a script to set the gMonth and gYear field from the donation date and the donations for the current month and year will pop up in the portals. If you only want year and month totals create the fields:

MonthTotal (calculation, number) =

Sum(gMonthToMonthRelationship::Amount)

YearTotal (calculation, number) =

Sum(gYearToYearRelationship::Amount)

Hope this helps. -bd

[radams]

Thanks to danjacoby and LiveOak. Your suggestions on making the month and year fields, along with the gmonth and gyear have helped a lot. Also the Month total and Year total though I had to manipulate them just a bit.

Amount

Sum amount = Total(Amount)

Month Total = Getsum(sum amount,month)

Year Total = Getsum(sum amount,year)

The summaries seem to work for each fund given to for month and ytd.

But