Summary for current year only?

[D.M. Wrider]

Okay... I have a question, and I don't even know how to ask it.

A client of mine runs a non-profit organization and sends his donors receipt letters. He would like those letters to include a year-to-date total, so they can see how much they've given for the current year.

I developed a couple summary fields that seemed to do the trick. But...those fields summarize ALL the donor's giving, not just the current year's. So, for now, it would work, but come January, the total wouldn't go back down to $0.

Any ideas? I'm really lost on this one. I'm afraid that I may have gone through the back door to get things done one to many times, and I'll have to redo a bunch of stuff, but if that's the case, I'll have to deal with it.

Do I need to run a script instead of having a straight up summary field? Does it need to be some kind of calculation?

Help!

[mr_vodka]

You can have it script to find only the customer records that belong to the current year. Since the summary will be based off the found set, it will reflect the year's total.

[D.M. Wrider]

Thanks for responding.

So...let's say my client wants to send receipt letters to people who have given in February only, but also wants just that year's total... How would you go about setting up a script to run after a search for a date range has already been done? (I hope I'm making sense.) Does the script need to run before their month search? Or....?

[D.M. Wrider]

Maybe I need to restate...

Is there a way that I can have a field show a summary of donation totals for only the current year?

(I've tried creating several date fields, and then basing a calculation field on those [showing a number only if the gift date matched the current date] but I couldn't get it to work.) Basically, I need to always be able to see the current year's total for each patron, not needing to run a search.

Is that possible?

[mr_vodka]

Since you do not want to run a summary report, try this. Create a global field in your donors table (lets call it gYear) and create a stored calc field (lets call it cYear) of Year (DonationDate) in your Donations table.

Next create a relationship from your Donors (keying on pkDonorID and gYear) to your Donations table (keying on fkDonorID and cYear).

Next you can create another calc field in Donors that is: Sum (TheRelationship::DonationAmt)

This should give you the total amount for the yea r that is inputed in the gYear field fo reach Donor.

So now you can create as part of your opening script when you file opens, a set field command to set the gYear to the current year.

Set Field [gYear; Year ( Get ( CurrentDate ) ) ]

Commit Record []

[D.M. Wrider]

Woo - okay, I'll give it a shot. I feel like a novice at best here, so I'll see if I can figure it out...I might be back!

Thanks a million - we'll see how far I can get.

[D.M. Wrider]

It's been forever since I've had a chance to sit down and work with my database again. Unfortunately I feel like an idiot.

I followed your directions (I think) and I'm starting to understand a little bit more how it should work, though I'm not quite there yet.

Seems I ran into trouble when I established the relationship. I already have a relationship between the Donors and the Donations by way of an ID number. On the Donors screen, I have a portal, showing that donor's most recent gifts, to make browsing easy. When I established an additional relationship between those two tables (Donors and Donations), my portal information disappeared.

Hmm....

I'm afraid I'm not understanding something I should.

[mr_vodka]

Creating an additional relationship to another Table occurence of the Donations table should not have effected your first relationship.

Are you sure that you did not delete the initial relationship or the portal/fields on the portal are still based on orig relationship?