Rolling Total Amounts?

[michaelzap]

Another logical twister for y'all:

Still working on a Donor and Donations database for a foundation. I have a Donor DB with unique IDs for each record and a Gifts DB which is related to it using the IDs as a match. I am happily using portals in both DBs (as well as in others that don't enter into this problem), but I'm finding myself stumped at how to calculate some specific Gift info.

Specifically, in Donors I have a portal listing all Gifts made by that donor by date. Below that I have calculation fields to show Average, Highest, and Lifetime donation amounts for that donor. Beautiful. HOWEVER, I also want to show total donations over the last 18 months, over the last 36 months, in this fiscal year, in last fiscal year, etc. I've been wracking my poor tired little brain for how to do this, but so far I can't resolve it.

So please oh please enlighten me: How should I do this?

P.S. I'm also looking at an old version of a similar database that does this, but the method seems bizarre and wrong to me. Basically the Rolling 18 month field is a lookup (no real-time update; no good!) of a summary of a local rolling total which is a calculation summarizing a rolling 18 month total of donations in the Gifts dB! It works (except for the fact that it doesn't automatically update), but that CAN'T be the best way to do this, can it?

[michaelzap]

I thought I had figured this out and made it work, but now I realize it doesn't...

HELP!

What I need is total donation amounts over different time periods for each donor in a donor database. What I've got is TOTAL donation amounts for all donors over these time periods. Obviously, I've got a problem with my relationship.

Example: I need to know how much each donor has given in the last 18 months.

So I created a Key_DateToday field in each Donor record with a calculation for what month we're in now (in FileMaker absolute terms) (12*Year(Today))+ Month(Today)

Then I created a Key_18Months match field in my Donations database for each donation which lists all the months (in absolute FileMaker terms) for which this gift would be included in a "last 18 months" calculation:

NumToText(Key_Date) & "

[Ocean West]

Time for a plugin....

the Ranges Plug in would solve this one in a jiffy!

[michaelzap]

I searched FileMaker's site for the Ranges plugin. It gives a URL that no longer works, then that domain does list it but says to download it from another developer, and that developer's download script is broken! I searched for the file name and haven't found it anywhere.

Any idea where I can get it? Or if you have it, can you email it to me ([email protected])? It's supposed to be free, so nobody should mind.

Thanks!

[michaelzap]

Or, better yet, anyone know how I can resolve this without a plugin?

[Rigsby]

I had a very similar problem:

I have 6 Salesmen selling several different products (solutions) (and the number could change to more salesmen). The solutions are created by one of 15 different people (any one of whom can work on any given product – and this number can also change)and the solutions are sold in 8 different countries (and yes you’ve guessed it, this can also change) to several companies.

I had months, and months of headaches with this, as I had to create a system of analyzing any statistic possible. This includes full time based statistics for any employee (salesman, or solution creator) each product, each country, etc. etc. etc.

I’ll try to draw a picture:

My sales parameters are such:

What?

Where?

When? (Any and every possible time period)

Created by?

Sold by?

Just to give you an example:

Solutions created by <John>, sold in <Germany> by <Mike> in <1999>

If we take the parameter <John> I need to repeat it for every country, every salesman, every year, and every month etc., etc., etc. In other words we’ve got parameters coming out of our asses. Or in computer terms, an array beyond belief.

So! What I did was this: I took each parameter and created a database for it. So for example:

What was sold: This database keeps track of total sales.

From this database, I then pass the information to a Where database: This produces the country statistics

And so on and so on, until I can draw any single statistic. I then created a master statistics database, which brings all of the results together in portals – processing the information even further. This technique is very long-drawn, especially keeping users out of the individual databases is a problem, i.e. using scripts to create and delete across several databases, but it works, and also keeps everything clean and easy to follow. Also, if you want to avoid the problems of printing portals, you need to use scripts to jump between databases and layouts, print, and then jump back.

I don’t know if you can follow any of this, but my tip is always look to see what you can put into separate databases, this keeps your file size down and allows you to improve specific areas of your whole solution easily.The important part is giving the user the feeling of only working in one file!

Rigsby

[michaelzap]

I figured out a solution which works fine in my situation. Since each Donor record had a Key_DateToday field and each Donation had a Key_18Months field, I simply added the unique DonorID to those fields also. Now the numbers generated all include the donor ID and the dates for which they should be matched, so I get only those donations for each donor in the specified time periods.

Whew! Same basic solution for the last 24 and 36 months, as well as this and last fiscal year and this calendar year to date. And when I look at the way it was set up in the old database here (which didn't work right anyway), it actually seems simple by comparison!

Thanks everyone for your suggestions and help.