Gift Detail and Summary Report in Database View - Pay-Cash on previous year's pledge

Cathleen Zinn

When I run the Gift Detail and Summary Report for this fiscal year, it is giving me payments on pledges that came in this fiscal year, but the pledge was from last fiscal year. We don't want these payments showing up on the report. Any ideas on how to make that happen?

Austen Brown

@Cathleen Zinn - I recommend removing “payments” or “pay-cash” from the gifts included in the output.

Cathleen Zinn

@Austen Brown I tried that, but it took out all pay-cash payments. We just don't want to see the pay-cash payments from pledges from last year. We want to see them for this fiscal year.

Austen Brown

@Cathleen Zinn - Instead of including payments, could you include the original pledges? What you're aiming to do is complex when it comes to querying and reporting—there isn't an easy way to achieve this. It is possible, but it will require some effort, and the process may not be entirely straightforward.

Cathleen Zinn

@Austen Brown The pledges are already included in the report.

Alex Wong

@Cathleen Zinn
not sure the intention of your report, can you specify?

Basically at first glance, it seems you only want to get payment to pledge when both payment and pledge is this fiscal year? (fiscal year as in 7/1 - 6/30?)

Cathleen Zinn

@Alex Wong You are correct. We want to see just payments and pledges for this fiscal year.

Alex Wong

@Cathleen Zinn
I don't use the gift detail and summary canned report, I almost always do custom for my org, so can't speak to that.

However, you can just run a gift query condition on date range for your fiscal year and gift type you want, this will only give you gift that falls in the date range

Unless you need additional “calcuation” from a pledge perspective:

• how much of the pledge (within date range) is paid (it can potentially be payment from before the date range, during date range, or after date range).
• how much of the payment (within date range) is paying pledge from before the date range, during date range, after date range.

if these calcuation is desired and needed for your report, that's going to be difficult for the specific canned report to do. May want to consider using the reports under the Pledge and Recurring Gift Reports category.

Cathleen Zinn

@Alex Wong Thanks for your suggestion. I will review this with my manager and see what we want to do.

Cathleen Zinn

@Alex Wong We decided to remove the pay cash from the Gift Detail and Summary Report and then run a pledge balance report for just this fiscal year. Thanks for your help!

Bernard Nemis

@Cathleen Zinn
Hi, Cathleen. Your best best would be the Pledge Status Report. It provides a field for the date of the pledges, and fields for the data range for any pledge activities (to corral your pledge payments).

Cathleen Zinn

@Bernard Nemis okay, thanks!

Graham Getty

@Cathleen Zinn You can exlcude payments that correspond to pledges in previous years. It's a bit of a long walk and involves using Query then Admin > Import > Pledge Payments. Start with dynamic gift query of all pledges made in previous year(s) - you'll need their Gift Import ID. Then build a dynamic query of all pledge payments received during the reporting period (the GF Link of a pledge payment is the Gift Import ID of the original pledge but you can't see that in query / export). Go to Admin > Import > Gift > Pledge Payment and click New > Select Create import file and under options select the dynamic pledge payments query you just built. Be sure to include the key fields (GFImpID, FundID, GFDate, GFType, GFTAmt, GFLink [remember GFLink of a pledge payment = GFImpID or original pledge]). Outside of RE get the GFImp ID from the dynamic query you built of all pledges made in previous years. Then in excel or access or whatever tool you like do a VLOOKUP to smash together any pledge payments from your Import file where the GLink from Import File matches the GFImpID from the dynamic query of pledges and voila - you've got the pledge payments that correspond to pledges from previous years. Now all you have to do is globally add a Gift Attribute like “exclude previous year pledge payments” to the GFImpID of the pledge payments where the GFLink matches the GFImpID then re-run your gift detail and summary reports and exclude gifts with those attributes. It's a lot to setup, but easy to maintain each month once you build the right sets of queries and get used to the Admin > Import > Gift > Pledge Payment. I'm sure @Anthony Gallo has a better way to do this in RE NXT web view cooking, but right now, this is the only way I know of to exclude only some pledge payments IF their corresponding pledge was made earlier than the reporting period that captured the payment (and other payments).

Alex Wong

@Graham Getty
nice method.

though depends on gift entry policy a little bit. A single pledge payment gift can be applied to multiple pledges, which can be pledges from a previous year (period) and current. Using a gift attribute to tag a pledge payment being for previous year or not might be flawed. This will work only if pledge payment when it is applied to different year's pledge is separated as separate gift record.

Cathleen Zinn

@Alex Wong Thanks for the suggestion. I have resolved the issue.

Cathleen Zinn

@Graham Getty Thanks for the suggestion. I have resolved the issue.