Adjustment Views
(blame Ruffin Bailey for bugs)
The goal is to create a view that handles adjustments in a more intuitive fashion that what’s currently in the Raiser’s Edge 7+ database schema by creating a replacement for the GiftSplit table. That is, instead of linking Gift to GiftSplit, a report designer could link Gift to this new view and instantly have more intuitive adjustment handling in their report, without breaking any needed relationships that were initially supported by the GiftSplit table. This quick paper will explain why adjustments and historical reporting are issues and how the new views try to account for them.
Adjustments Now
Essentially adjustments are handled very simply in Raiser’s Edge. When a gift is adjusted, its old GiftSplit entries have their GiftId field set to null, breaking the link with the original gift. New GiftSplit entries are created with the original gift’s Gift.Id in their GiftId field.
Using the SQL statement below, the newly adjusted gift would appear to have never been adjusted at all. It would simply have the new gift values, funds, campaigns, and so forth displayed.
select * from
gift g
inner join
giftsplit gs
on g.id = gs.giftid
Though this is a straightforward fix for reports that need to display the present-day state of gifts, this is not a good fix for historical reports. The Gift.id to GiftSplit.giftId relationship does not include any reference that the gift has been adjusted. Indeed, in these two tables, the only reference that a gift was adjusted is that the gift’s current GiftSplits have GiftId’s and AdjustmentId’s that do not match.
Looking for gifts of type = 28, adjustment-type gifts, does not help either. Adjustment-type gifts do not have links back onto the Gift table to see what gift they have adjusted. Nor do adjustment-type gifts return any values using the above SQL statement; they do not have any GiftSplit entries that reference them via the Gift.id = GiftSplit.giftId relationship as one has come to expect with “typical” gifts. To make things more complicated, the adjustment-type gift’s Amount does not show the amount of change from the original gift. So the below SQL statement (even ignoring write-off and pledge issues for the moment) will not show the total gift amount for an RE database:
select sum(amount) as givingTotal from gift
Note that with this set-up, gift types cannot be adjusted. Because the Gift.type field shares space in the database with the Adjustment flag, you’re stuck with the original gift type throughout the life of the gift. In other words, the database schema doesn’t allow for a cash gift to be adjusted to a stock gift.
Supporting Historical Reporting
To provide log and audit (or “historical”) reporting capabilities, a report must show the gift’s original amount and original gift split information at the date of the original gifts, and adjustment-type gifts do link to GiftSplit using GiftSplit’s AdjustmentId. Using the InstallmentPayment link table, it is possible to drill down and get original gift information as well as pointers/relationships between each adjustment-type gift and its fund, campaign, and other GiftSplit information as it should have appeared with each adjustment of the gifts.
This is currently done using five views, as described below. The end result is a “new GiftSplit table”, named bbps_vwAdjustmentSplits. This “new table” gives the original gift entry back its original GiftSplit entries. It also assigns GiftSplit entries via a “GiftId” link (field name of giftLinkId) to adjustment-type gifts. To make the numbers in this new view give an accurate reflection of what’s in the database, each adjustment-type gift will have two groups of related bbps_vwAdjustmentSplit entries. The first group will be negative amounts that “undo” the last gift entry values. The second group will add the amounts for the new GiftSplit values, so each fund/campaign/appeal/package combo will reflect the appropriate changes.
The GiftAdjustment table holds the relationships needed to pull this information, and it is used to link Gift information with the appropriate GiftSplit’s in bbps_vwFinalAdjustmentSplits. This view creates the giftLinkId field necessary to link GiftSplit entries to adjustment-type gifts, and links what adjustment (first, second, third, etc) it is that each set of GiftSplit entries belongs in the adjustmentSequence field.
bbps_vwFirstAdjustmentSplits grabs the original GiftSplit entries for the gift, and links the Gift table entry to these by way of giftLinkId. The adjustmentSequence field is filled in with a zero (0) to show that these are the GiftSplit entries that belong to the “zeroeth” adjustment (ie, before any adjustment).
bbps_vwPreAdjustmentSplits is named that because it’s “Pre-final”, which is a pretty bad name. Though Jon might want to call it the “penultimateAdjustmentSplit” view, it’s probably better thought of as the “Positive Adjustment Split” table. It unions all the GiftSplit's that are now linked through the previous two views to the Gift table on giftLinkId into one table. This view holds all of the positive GiftSplit amounts for a particular gift. If you summed the amounts, you’d have the original amount of the gift plus the amount it was adjusted to plus the amount that was adjusted to, and so on. It is missing the first group of bbps_vwAdjustmentSplit entries as described above, the negative gifts.
Negative GiftSplit entries are linked in to the gifts using bbps_vwNegativeAdjustmentSplits. This view basically slices off the adjustmentSequence and gift link information for the current adjustment and splices it onto the amount of the previous adjustment (linking to an alias of bbps_PreAdjustmentSplit using the adjustmentSequnce field minus one (to get the previous gift amounts) multiplied by negative one (-1) to balance the old amounts). Note, of course, that the original gift will not have negative values associated with it.
bbps_vwPreAdjustmentSplits is unioned with bbps_vwNegativeAdjustmentSplits as well as a trivially extended cut of GiftSplit (where (giftId is null or not giftId = adjustmentId)) in bbps_vwAdjustmentSplits. bbps_vwAdjustmentSplits now links gifts that have been adjusted to their original GiftSplit amounts and information. Each adjustment-type gift is linked to negative values (by fund, campaign, etc) to cancel previous amounts as well as GiftSplit information in a more traditional “Gift to GiftSplit via GiftId” fashion, though now it’s a “Gift to bbpw_vwAdjustmentSplits via giftLinkId” fashion. Gifts that have not been adjusted nor are adjustment-type gifts continue to link, via giftLinkId, to bbps_vwAdjustmentSplits as they used to link to GiftSplit.
Get it? Got it? Good. rufwork@hotmail.com with comments, etc.