How to search for what's missing?

[Ashley_Jackson]

Hi all, I'm working on a donor management system for a non-profit org.  Donors make pledges to give $x on x recurring basis, usually something like $50 a month.

I've got a table for Donors, one for pledges, and one for donations.

I need to have a way to check whether pledges are being fulfilled by donors.  So if John Smith pledged $50 a month, has he actually sent $50 a month?

But how do you search for what's NOT there???  I am stumped on how to approach this one!

Any thoughts???

[BillH]

It sounds like you create a donation record when one is received.  If you change the logic so that you create one for each expected donation, and add a status for received, it changes the search to something that exists but is in a specified status.  I know this is very simplified, but the way to accomplish it would depend on many variables, depending on time intervals (can they be weekly, quarterly, etc. ?), are they open ended or it there an ending date?...

It's similar to how financial systems deal with loans.

[Dan_Montoya]

Here is a simplified version of how I would solve this:

• Every donation has a date received field.  Use the Month() function to get the numerical month
• In the donor table use a rollup on the donations table to find the Max(values) of the month field. This gives you the number of the month of the most current donation.  
  •  
• Compare the most recent donation month with the current month and set a code for the state of the pledge (up to date or missing donation)

This gets more complicated if you want to track when you get less than the pledge amount or if you want to handle getting two in the same month.

[TheTimeSavingCo]

Assuming you have the date they started pledging donations, you could also try to do this by calculating the number of donations they should have made so far and compare it against how many donations there have been.  It won't flag the donation that's missing though (e.g. can't tell you that they didn't donate in, say, May)

Link to base

[pressGO_design]

This is really just a tweak of the above 2 solutions that changes the focus from comparing the expected number of donations to the expected amount of the pledge. This way, if a donor plays catch-up one month, the pledge status will change according to the total amount paid.

I'm guessing that in your Pledges table, you have fields for

• Donor (linked to Donors)
• Pledge Date
• First Payment Date
• Pledge Amount (this is the amount they're paying per month)
• Pledge Duration (the number of months they've committed to paying)

I would add formula fields that calculate

• Months Active (the difference between the date that you expected the first payment and today, in months, +1)
• Expected Amount (Months Active * Pledge Amount)
• Actual Amount (rollup of all donations that are linked to that pledge)
• Difference (Actual Amount - Expected Amount)
• Months Overdue (Difference/{Pledge Payment Amount}
• Pledge Status (a formula like 

IF(Difference<0, "Lapsed", IF(Difference=0, "On Track", "Overpayment"))​

Donations that are pledge payments are linked to the open pledges for those donors; donations that aren't pledge payments (like an IMO or IHO that comes in outside of a pledge payment) aren't linked to pledge payments, and you can pull the pledge statuses into the donors page so you can see at a glance who's behind.

Link to base here.