Gift Adjustments, "Previous Amount" field not available for output or export

Debbie Pokornik

In exporting information for our Accounting department so that they could do bank reconciliations, it came to our attention that when I zeroed out a gift via an RE gift adjustment, I could not report to them via an output field in a query, or via an export field in export, the "Previous Amount" of a gift adjustment.  I had to give them this amount manually, when it could have been done quickly and easily with the information already in RE.


​This field is available in an built-in RE Financial Report (Gift adjustments)


In chatting with RE support to find where I can access this output/export they suggested I bring up this subject for vote.


​Also I just took Crystal Reports training thinking I would be able to find that field.  The trainer also advised that I should start this post in the Community for others to vote on it.  Another attendee at the session agreed with me that this was a problem in their organization, and they wish they had access to this field.


Thank you.

Ryan Hyde

Debbie Pokornik:

In exporting information for our Accounting department so that they could do bank reconciliations, it came to our attention that when I zeroed out a gift via an RE gift adjustment, I could not report to them via an output field in a query, or via an export field in export, the "Previous Amount" of a gift adjustment.  I had to give them this amount manually, when it could have been done quickly and easily with the information already in RE.


​This field is available in an built-in RE Financial Report (Gift adjustments)


In chatting with RE support to find where I can access this output/export they suggested I bring up this subject for vote.


​Also I just took Crystal Reports training thinking I would be able to find that field.  The trainer also advised that I should start this post in the Community for others to vote on it.  Another attendee at the session agreed with me that this was a problem in their organization, and they wish they had access to this field.


Thank you.

I think what you meant to do was post this in the "Ideas" tab (toward the top of this page, just below the giant Raiser's Edge banner). 


I just did a quick search and found this suggestion already posted: https://community.blackbaud.com/products/raisersedge7/ideas


Of course, this was originally posted 4 years ago and only has 14 votes (including my own, just now). Unless there's suddenly a lot more interest in this functionality, I don't think it's likely to receive any attention from Blackbaud. The reason this may be is that if you have RE integrated with FE and you post your gifts (and adjustments) to FE, then accounting already has the entire history of the gift in the ledger, which makes this type of export unnecessary. 

Jen Claudy

This is why I have, at various times/orgs, used a Gift Attribute to record the original amount in an accessible field, added a Gift Note including the original amount in the Note Description or Note Title, or entered a Reason on the Adjustment itself that gives the original amount.  Currently, I use the last...the Reason field would say something like less $500 (from $2000) and then any actual reason explanation or see GiftNote for details and a longer explanation there...when reconciling, I look at the first part of that that tells me how much the adjustment is (and which direction) and how much the original amount was.  It's clunky no matter how you do it, but in my case here, this works.

Debbie Pokornik

Just a bit further, I was advised by two different support contacts that there are a few fields that users don't have access to, and this was one of them.  The field exists, but hasn't been made public.  So, the advice was to bring it to the community (apparently I did this in the wrong area), and have it voted on, so that programmers may consider a fix.  It would be such a time-saver, not to mention there would be less human error that would creep in from manually entering it into other fields.

James Andrews

Debbie Pokornik:

Just a bit further, I was advised by two different support contacts that there are a few fields that users don't have access to, and this was one of them.  The field exists, but hasn't been made public.  So, the advice was to bring it to the community (apparently I did this in the wrong area), and have it voted on, so that programmers may consider a fix.  It would be such a time-saver, not to mention there would be less human error that would creep in from manually entering it into other fields.

The field exists, but it's the amount field in another gift record. It's really pretty interesting how RE stores this stuff -- basically when you do an adjustment, RE writes the original gift info out to another gift record, and then by linking the gifts table to itself via a different table than it normally does, it's marked as the previous gift.


OK that is interesting to me. Maybe not to everyone else.  The point is there's a little more to it than just storing the data in a field, it's a whole other record.

 

Ryan Hyde

That's interesting to me too! That definitely explains a few things.

James Andrews

Ryan Hyde:

That's interesting to me too! That definitely explains a few things.

Totally. Most of the gift information (date, amount, donor, etc) is stored in GIFT, but the campaign, fund, appeal, package and amount are all stored in a table called "GiftSplit". When you do an adjustment, RE creates a new gift record in GIFT with the old gift info, and moves all the associated information from GiftSplit to GiftPreviousSplit. The adjusted gift and the previous gift are linked with each other in another table called GiftAdjustment.

John Heizer

James Andrews:

 

Ryan Hyde:

That's interesting to me too! That definitely explains a few things.

Totally. Most of the gift information (date, amount, donor, etc) is stored in GIFT, but the campaign, fund, appeal, package and amount are all stored in a table called "GiftSplit". When you do an adjustment, RE creates a new gift record in GIFT with the old gift info, and moves all the associated information from GiftSplit to GiftPreviousSplit. The adjusted gift and the previous gift are linked with each other in another table called GiftAdjustment.

 

So, let me see if I get this right ... With that information on the data structure it would be possible to get at the needed information using Crystal Reports and grabbing the data via an ODBC connection directly to the database, but you can't get at it with a standard Export.  You could get the Gifts you want via a standard Blackbaud Report Writer Database (MDB) export and use the Gift System Record ID to then link back to the other needed tables.

James Andrews

John Heizer:

So, let me see if I get this right ... With that information on the data structure it would be possible to get at the needed information using Crystal Reports and grabbing the data via an ODBC connection directly to the database, but you can't get at it with a standard Export.  You could get the Gifts you want via a standard Blackbaud Report Writer Database (MDB) export and use the Gift System Record ID to then link back to the other needed tables.

 

I'm not sure exactly what you mean by that last part.   Export has an adjustments section but I'm not sure how useful it is, or how it relates the current and previous gift info. I've never really used it. Probably worth looking into!


I do have an adjusted gift report that pulls directly from the db via an ODBC connection. I got some of the SQL off the KB but figured out some of it myself.  I'd be happy to send it along if you're interested - just DM or email me. 

Debbie Pokornik

Some good news.  Someone in my (Crystal Reports) class found that the previous amount is listed (gift query) under Adjustments – Gift Adjustment Previous Funds – Split Amount. 

That being said, I do not know how to output this field in an export.  If anybody finds out I would be happy to know.