Need a script for two sets of criteria

[Todd]

Here's what I need: I need to end up with a list of donors who have not donated in the past 12 months.

At first I used a calculated result that can grab the records (via the "date of donation" field) older or newer than 12 months. But I need to use that found set to eliminate everyone (by a unique donor id) who hasn't donated in the past 12 months.

I have assigned each donor a unique id called "donor id" and I believe I need to somehow incorporate this field in order to eliminate those donors who have given in the past 12 months, leaving all the rest who have not, but I can't quite put it together.

Any ideas would be appreciated.

Thanks

[Ugo DI LUCA]

If you can find those that did "donate within the 12 last months", then you can easily find those who didn't by a "Show Omitted".

[Todd]

Thanks. But I need to use two different sets of criteria to complete the find. When I find by the calculated result (in the date field), it will only produce the results by date - for instance, if I show all donations older than 12 months, it will show me just that. But that is not excluding all of those donors who have contributed in the past 12 months and prior to the past 12, only showing me the actual donations older than 12. We have a situation where donors donate repeatedly and we want to show those who have lapsed, i.e., have given in the past but not recently. It's not enough to show by date, unfortunately.

Somehow, I need to use that first set of criteria, pull the donor id's from it and then find "all" in a second find with those id's omitted, but I can't figure the script. Can I do two separate finds like that? Am I making it more difficult than it actually is? Is there an easier way?

Thanks

[Fitch]

I think you'll need to set up some kind of self-join and run a looping script, but before going any further, can you tell me if your donor system is all in one file? I.e., you have a record for each donation, which contains the donor info and the date? Is there only one date field per record? Are there any other files or fields involved?

[Todd]

Tom: Yes, there is only one date field per record. The database is set up like this:

Master File contains Donor name, address, and mailing info along with their unique id

Donation File contains the donor's unique id and the archived history of each donation

Linked to the Donation File are two more files which archive each Donation according to which particular fund (there are 2 separate funds) it is to be directed into. These files also contain the donor id and the date of the donation.

Thanks.

[Fitch]

Aha. Search in the Master file on the related date field for < a year ago, then run a script like this:

Loop

.. If [Max(related::donationDate) < (Status (CurrentDate) - 365)]

.. .. Go to Record (Next, Exit after last)

.. Else

.. .. Omit Record

.. End If

End Loop

[Todd]

Hmmm.. That seems to bring up a similar result - it doesn't eliminate the donors of the last 12 months. I think I see where you're going - maybe I don't have the field it's checking right. I'm not using a relationship in the date field, because it's already on this layout I'm working from. Am I missing something here?

Somehow I need to check the first found set (all donations < a year ago)against the donations > a year ago and omit the donor id's that match. That way I'll only be left with the donor id's that weren't active in the last 12 months.

[kenneth2k1]

Hi:

You can use this syntax to first find one criteria, and then constrain that found set:

Enter Find Mode [ ]

First find criteria

Perform Find [Replace Found Set]

Enter Find Mode [ ]

2nd find criteria

Perform Find [Constrain Found Set]

Just a side note, I wouldn't set up your database like this. If you stored donation data in a separate file, then you could maintain historical data on more than one donation, including date, type and other info. After all, you said that somebody might donate more than one time, right?

Then you probably wouldn't have this problem.

Ken

[enzo01]

if I show all donations older than 12 months, it will show me just that. But that is not excluding all of those donors who have contributed in the past 12 months and prior to the past 12, only showing me the actual donations older than 12.

Why not using a nested Case Unstored calculation.

c_OldDonors = Case(Record_Id = SelfJoin::Record_Id,

Case(Max(Selfjoin::donationdate>Status (CurrentDate) - 365,

Case(Max(Selfjoin::donationdate<Status (CurrentDate) - 730),1,0)

,0),

0)

May be I screwed up a bit this calc, but I think you get the logic.

Now, you've got several methods to re-use this unstored calculation. The easiest would be to search all records with a 1 in that calc field.

It may be long, but I guess this isn't the kind of data you're using daily, so...

[kenneth2k1]

if I show all donations older than 12 months, it will show me just that. But that is not excluding all of those donors who have contributed in the past 12 months and prior to the past 12, only showing me the actual donations older than 12.

If you search for donations older than 12 months, then the found set SHOULD BE the donations older than 12 months. I know they're not, because you said you have a donor ID set up to distinguish.

But I hope you see the redundancy of all this, and you should really consider a sparate donations file.

Ken

[Todd]

Ken:

Thanks for the Find Help. Donations Data is definitely stored in more than one file, in fact it's stored in a total of 3. I'm also toying with creating an additional file that holds the donor id along with the date of the most recent donation so that I can just portal my way to this information from the master file.

[kenneth2k1]

That's good news!

Then what I was thinking of was sorting by those dates, and then have that date field in your main file, and search on that field

[Todd]

using the additional file is definitely the key - it's so easy now. I'm putting the calculation in the new file's date field, show the info, and create a portal to the master file.

Thanks for the help.

Todd