Sum Donations made over past year

[himalayan learning]

hi,

we're a small charity with our own database incl. table of donors and table of donations. we need to work out how much a donor has given in the last year so that if it says zero, we can write to them.

donors table:donations table is one:many.

we therefore need to add the amount of each of the donation records in the past year for each donor. we need it to be done instantly, rather than in a button.

thank you very much for your time in helping our charity

Oliver

www.himalayanlearning.org

[himalayan learning]

sorry, i forgot to mention, therefore we have a field to add up the total donations from one person, unfortunately we're not sure what the code is for changing this so it's only donations in the last 365 days or 1 year.

[Genx]

One way of doing it would be to utilize an unstored calculation field within table:donations...

1. Make a new calc field in table:donations. Set it to not store results and the calculation itself to be something like: If(Get(CurrentDate) - 365 <= Dateofdonation; "valid"; "")...

2. Make a new text field with global storage in table:donor for the purpose of the relationship. Insert the value "valid" into it...

3. Create a new table occurance of the donations table and call it table:donationscalc or something.

4. Relate the original table:donor table to the the table:donationscalc by the id, as well as by the new calc in table:donationscalc to the global text field in table:donor...

5. Make a calc field in your donor table, with a calculation something like this...: If(Sum(table:donationscalc::donation) = 0; "Write-To"; "")

6... hope it helped

Genx

[comment]

I don't think that will work - the relationship being based on an unindexable field in Donations.

Try it this way (attached).

DonationsByYear.fp7.zip

[Genx]

sure it does?

donationsbyyear2.zip

[comment]

No, it doesn't: your CurrentYear is stored, therefore it will not update as the date changes. A donation entered today will remain valid forever (or until you re-enter the date).

[Genx]

ahahahahahahahahhaha... yeh your right sorry

[comment]

I have changed my file to reflect donations made in the past year period - rather than calendar year.

DonationsPastYear.fp7.zip

[Genx]

mmm your way makes lots more sense, mine was ah sorta... backwards, ill try make sure i improve my logic

[himalayan learning]

Hi guys,

thanks very much for your support, i've run through both models and i have one question to ask, is it possible to have the total donations for each person to be either in the donors table or the donations table?

we previously had it working per year, so it would add up any donations in 2006, however as you can recognise, this doesn't work for people who give at Christmas and then get told they've not paid on new years day!

sorry, off the point, anyway would it be possible to get the totals coming up on either of the 2 tables rather than in a report.

thank you once again

Oliver

www.himalayanlearning.org

[himalayan learning]

Hi guys, i think that i've sorted it, i made 2 fields, one (this year amt) which said

If ( Date_Banked ≥ Year_Ago ; If ( Sponsorship = "Y" ; Amount ; "0" ) ; "0" )

and the second (this year) saying

Sum ( Donation 2::This Year Amt )

where i set up a second copy of the table "Donation"

phew, thanks for your support!

Oliver

www.himalayanlearning.org :(

[FM4me]

Hi Oliver

thanks for your post, and was glad to know you were able to get the donation file maker program works.

Please i have a question which i think your experience will be of tremendous help or anyone else out there.

I have posted this before, but guess had little response, i mistakenly posted twice.

I have a Donation files, one call contact, and the second call donations, please how can i calculate the total donations made by each individual for each year, this is year end, starting from 2005.

I am a beginner and have read the way you made yours to work, a little details will help.

sorry fro this trouble, i have to help a non-profit organization (http://www.hivnotme.org) to send a receipts and thank you letter to donors for year 2005, and could not figure out this part.

thanks so much

mike