Incorrect contributions statements

[BrianShoe]

Hey PC+ power users -
I've noticed problems with a few of our year-end contributions statements (specifically, six out about 130 total). Note that we're running PC+ ver. 9, and our maintenance releasess are up to date through the 8-Aug-05 MR.

For these six people, the system either:
(1) has fabricated money they never contributed (i.e. that I can't find, looking in the posted contributions), or
(2) appears to show an amount against the family's fund A pledge that, in looking at the posted contributions, was actually credited to fund B. (that is, a total appears for fund "04-05 contributions" where the only check that could account for that amount is credited to fund "03-04 contributions").

I have reindexed the database, multiple times.

I have a scary feeling we may have a referential integrity problem. We tried deleting a family from the database earlier in the year, but ran into difficulties because the family had an envelope number and contributions to that envelope number. I believe the deletion finally succeeded, but now I see these fouled-up Contribution Statements and on the Active Pledges report I see a listing for envelope 190 with name "Envelope number not found".

I've checked everything I can, short of opening the Database Manager and running a SQL query on the appropriate tables (I'd have to bone up on the table and field names before attempting that).

Does anyone have any thoughts on what I should check to get to the bottom of this? We're in recess now (we don't meet during the summer), so I can get into the church office and take backups all I want. I also have "shadow" installation running on my personal laptop, so I can check out issues like this.

Thanks in advance -
Brian Shoemaker
Unofficial PowerChurch administrator
First Parish Unitarian Universalist, Canton MA[/b]

[NeilZ]

If I remember correctly, anytime you delete a family with contributions, the amounts go to an envelope that is 'nonprintable', that is, a number that is completely out of range and designed by the developers to stay that way. It will not appear in people's accounts.

In your case, it does sound like a number of issues are occurring, one of which, it appears, is that a cascading deletion didn't occur.

I've usually found that a reindex fixes most issues, however, in this case, I suspect you'll hear from one of the developers that you'll have to either give them a call and they'll walk you through a fix,, or send a backup copy of your database to them to repair.

[jeffkoke]

Brian,

There is another possibilty: contributions entries way outside the current dates. It may take a little poking around by generating some reports, but would be worth the look. Worst case would be doing a database query.

What I'm getting at is that a contribution could have been dated 7/15/2050 vs 7/15/2005.

Hope this helps,
Jeff

[NeilZ]

Good point !! However, would it show up in the contributions statement, when there's usually a date range included.

[jeffkoke]

Good point !! However, would it show up in the contributions statement, when there's usually a date range included.

Agreed, however, the date range can also mutually exclude the entry.

Jeff

[NeilZ]

Also true ... !!

[BrianShoe]

Jeff / Neil -
Great suggestion, especially since I've been able to rationalize some of the other strange-looking contributions statements for exactly this reason (e.g. donation to the Music Director fund was made just prior to the date range of the report). I just checked the "Posted Contributions" reports for these six cases with a wide-open date range (1-Jan-1990 to 31-Dec-2050), but got exactly the same results as before.
Mind you, I've also poked around in the "Maintain Pledges" and "View Posted Contributions" functions - the contributions listed don't account for the values shown on these reports.
In trying to characterize the issue, I've seen two types of situations:
a) A check for the errant amount exists in the posted contributions, but is credited to a different fund (i.e. Contrib Stmt shows $100 credited to "04-05 contributions" but posted contributions only has a $100 check credited to "03-04 Contributions")
b) The system has "found" an amount that NO entries in posted contributions can account for. A couple of cases have $1000 more in the "paid" column of their Contribn Stmt than I can find in the posted contributions!

I doubt I'm seeing a bug in the Contributions Statement report itself, since I only see irreconcilable errors in six statements. I'll be very happy if this is something other than a referential integrity problem, but so far I'm not finding anything reassuring.

Again, thanks for the suggestions. The next step may be to do some direct SQL queries of the underlying tables.

Brian Shoemaker

[Zaphod]

For the weird overages on the pledges, check to see if your pledges have a starting balance. That would certainly account for your "b)" problem above. To check that, look at Contributions -> Maintain Pledges -> Find/Locate the person, click on their pledge and click "Change" at the bottom. I'd be willing to bet that you have a starting balance entered, and that's the cause of your overage.

If that's the case, no amount of querying will find it where you'd be looking.

[BrianShoe]

Zaphod -
Thanks - that explained five out of the six cases. I had found the starting balance in one of them, but hadn't had chance to explore the others.

I clearly need to retrain our Finance committee members, who do the contribution entry on Sunday - need to show them these odd entries and explain the effect they've had.

I'm left with just one mystery case. The folks have only two checks recorded: one to fund #5, another to fund #4 (for $100). However, the contribution statement and the "Maintain Pledges" function both show a $100 payment to fund #7, same date as the check credited to fund #4! The check date is 31-Dec-2003, which predates the pledge period for fund #7 (1-Jul-2004 to 30-Jun-2005).

Any thought as to what I should examine, to find out why a check, shown as credited to one fund, appears as a payment to another fund on the Contributions Statement? I'm no stranger to SQL, so I can look in detail at the contents of the tables (through the Database Manager) if need be.

Brian

[Zaphod]

you can try this from the database manager:

use co <enter>
brow for env_no = 101 <enter>

This assumes the envelope number in question is 101, obviously. You'll want to change that to the number you're interested in. I'm expecting that for the contribution record for fund #4, you'll find in the pledge_no field a number that corresponds to the pledge which is set up to involve fund #7. If fund #4 doesn't have any pledges set up, that pledge_no field should be 0 or (possibly) blank. If you find anything else there, replace that field value with 0 (zero), exit the DBM, and reindex.

Of course, you'll want to make a backup before doing any of that.

That's how you find and fix it. How it got there in the first place is a mystery wrapped in an enigma.

[BrianShoe]

Bingo! That's precisely what I found. The entry in CO shows fund #4 and Pledge_no = 82; the record in COPLED with Pledge_no = 82 shows fund #7!

At this point, I don't really care how it got that way. I can fix it - and I've picked up a whale of a lot of information about how to check things out in this database.

Thanks -

Brian