Many Pies

Many Pies

Wednesday, March 21, 2007

Getting gift information out of Raiser's Edge

This is a very technical Raiser's Edge post, which may be of use to a small number of people. You'll need to understand SQL.

We post gifts out of Raiser's Edge and they undergo a lot of processing afterwards. Despite all the information you can put into the reference when you post out (constituent id, name, fund name etc.) you can't get everything.

If you run the following SQL statement just after posting, putting in the date and times when the post ran, then you can get most of the gifts that were posted (see below for getting the rest). If someone edited a gift in the middle then that would throw it out, but either
a) have a bad on editing gifts or
b) do a reconciliation with the post file, crosschecking constituent ids with the Excel VLOOKUP function, for example.


SELECT GiftGLDistAccount.Debit_Number, GiftGLDistAccount.Credit_Number, GiftGLDistAcctProject.Project_Number,
GiftGLDistAcctProject.Project_Desc, GIFT.DATECHANGED, GiftGLDistAccount.GiftGLDistributionId, GiftGLDistribution.Amount,
GiftGLDistribution.AdjustmentId, GiftGLDistribution.GiftId, RECORDS.CONSTITUENT_ID, GIFT.DTE
FROM GiftGLDistAccount INNER JOIN
GiftGLDistribution ON GiftGLDistAccount.GiftGLDistributionId = GiftGLDistribution.Id INNER JOIN
GiftGLDistAcctProject ON GiftGLDistAccount.Id = GiftGLDistAcctProject.GiftGLDistAccountId INNER JOIN
GIFT ON GiftGLDistribution.GiftId = GIFT.ID INNER JOIN
RECORDS ON GIFT.CONSTIT_ID = RECORDS.ID
WHERE (GIFT.DATECHANGED > CONVERT(DATETIME, '2007-03-20 11:00:00', 102)) AND
(GIFT.DATECHANGED < CONVERT(DATETIME, '2007-03-20 12:00:00', 102))


What the above doesn't do is get the old values for adjusted gifts. For that you need this:

SELECT GiftGLDistAcctProject.Project_Number, GiftGLDistAcctProject.Project_Desc, GIFT.DATECHANGED, GiftGLDistribution.Amount,
GiftGLDistribution.AdjustmentId, GiftGLDistribution.GiftId, RECORDS.CONSTITUENT_ID, GIFT.DTE, GiftSplit.Amount AS Expr1,
GiftPreviousSplit.Amount AS Expr2
FROM GiftGLDistAccount INNER JOIN
GiftGLDistribution ON GiftGLDistAccount.GiftGLDistributionId = GiftGLDistribution.Id INNER JOIN
GiftGLDistAcctProject ON GiftGLDistAccount.Id = GiftGLDistAcctProject.GiftGLDistAccountId INNER JOIN
GIFT ON GiftGLDistribution.GiftId = GIFT.ID INNER JOIN
RECORDS ON GIFT.CONSTIT_ID = RECORDS.ID INNER JOIN
GiftAdjustment ON GIFT.ID = GiftAdjustment.GiftId INNER JOIN
GiftSplit ON GIFT.ID = GiftSplit.GiftId INNER JOIN
GiftPreviousSplit ON GIFT.ID = GiftPreviousSplit.GiftId
WHERE (GIFT.DATECHANGED > CONVERT(DATETIME, '2007-03-20 11:00:00', 102)) AND
(GIFT.DATECHANGED < CONVERT(DATETIME, '2007-03-20 12:00:00', 102))

This query returns duplicate rows for the gifts, so will need tweaking to get just what you want.
Finally, if you want deleted gifts, then look in the GiftGLAudit table.

tags: Raiser's Edge
Post a Comment