FILEMAKER: A READER ASKS: Donation Totals By Campaign And Donor

From Dwayne Wright PMP, PMI-ACP, CSM
Certified FileMaker Developer

WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

A READER ASKS
I realize I'm being a little presumptive and will understand if you say "no." But, is there any chance you can help me figure out how to do something?

I am trying to create a simple donation tracking database for our local volunteer emergency medical services agency. I'm no expert but I'm reasonably able when it comes to FileMaker. But, the one feature I need has me stymied. A given donor may make zero, one or more donations to any given campaign -- for example, our 2008 Spring Fund Drive. I am looking for a portal that summarizes their donations by campaign.

John gave $50 for A, $50 for B, another $50 for A.
So, our portal would show

A $100
B $ 50

I would also like to able to search through this portal so, for example, I can find those who gave more than $100 (need to send them an acknowledgment for tax purposes), who gave last year but not this year, etc.

-------
DWAYNE RESPONDS
Well, what you are asking for isn’t really that simple. You are looking to search for the aggregate total amount of a user for a particular campaign. If you have other users in this database, you might want to script the process. Anyway, here is how I went about it...

Here we can see your relationship graph in the file you sent me before I tweaked it.

Since the main thing we are searching is donations, we will want to start in that table. What I want to do is have each donation record calculate the sum total of all the donations by that same donor and for that same campaign. So I am going to create a self relationship and it will have a two level comparison.

Here we can see the self relationship of donations to donations and the relationship requires that the campaign and the donor id be the same value for all records for a valid relationship.

Next, I created a calculation field to know the total donations for the same campaign by the same donor within my Donations table. I named by field .... SUM_campaigndonationsSameDonor ... which is a little verbose but I can live with it.

My calculation for this field is ...
Sum ( Donations_selfByDonorID::Amount )

I added this new field into my portal row but you still cannot search upon it. This is because the field is a calculated value based on relationships and FileMaker cannot calculate a second level aggregate in find mode. So you need to do your search from a layout in the donations file.

FYI... To help keep users from trying to do this, I made the Campaign sum field that I put in the portal unavailable to click into while in Find mode.

So here I decided to write a script. I named my script Find Campaign Donations By Donor. I added comment script steps before hand, to visualize what I want to do and will fill in the actual steps later.

Here you can see my script with just the comments, so I have visualized the result of my script.

Here you can see all the separate script steps that I used to perform this script (click image to expand). Almost all of these steps can be searched upon in the FileMaker Scripting Explored Blog, except the Show Custom Message script step. You can get the scoop on that in the FileMaker Help, which does an admirable job.

=
More info about the author and FileMaker in general, contact me at info@dwaynewright.com.

© 2008 - Dwayne Wright - dwaynewright.com

The material on this document is offered AS IS. There is NO REPRESENTATION OR WARRANTY, expressed or implied, nor does any other contributor to this document. WARRANTIES OF MERCHANT ABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE EXPRESSLY DISCLAIMED. Consequential and incidental damages are expressly excluded. FileMaker Pro is the registered trademark of FileMaker Inc.