Finding Current Year Payments on Pledges from last Fiscal Year

Tom Klimchak

Hi...


I seem to stumble upon this problem every few years:  Is there any way find payments in THIS fiscal year for pledges that were started in PRIOR fiscal years?


So I want to find all the PAYMENTS in July for Pledges that were entered/started before July 1.


The best I've been able to do it make a Gift Query of all the Pledges created prior to 7/1 and then create a Pledge Status Report for all the activity after July 1.  


Any other ideas? Am I missing something simple?

John Heizer

Tom Klimchak:

Hi...


I seem to stumble upon this problem every few years:  Is there any way find payments in THIS fiscal year for pledges that were started in PRIOR fiscal years?


So I want to find all the PAYMENTS in July for Pledges that were entered/started before July 1.


The best I've been able to do it make a Gift Query of all the Pledges created prior to 7/1 and then create a Pledge Status Report for all the activity after July 1.  


Any other ideas? Am I missing something simple?

This is a scenario where having separate Funds for each FY can be an advantage.  You could create a Gift Query where Fund is one of {previous FY Funds} AND Gift Type is one of {Pay types} AND Gift Date >= {this FY}.

Michelle Tribe

Would a Constituent Query using "gift summary" help?


1. Criteria

• Summary for Gift > Total Number of Gifts equal to or greater than 1
  • Gift Type = Pledge
  • First Instalment Due (date range, for example: last fiscal year; or Jan 1, 2012 - Dec 31, 2012)

2. Output

• Summary for Gift > Total Number of Gifts
  • Gift Type = Pay-Cash
  • Gift Date = (date range)
• Summary for Gift > Total Amount of Gifts
  • Gift Type = Pay-Cash
  • Gift Date = (date range)
• [constituent ID, name, etc etc.]

JoAnn Strommen

This report is one that would sure be nice to have as a 'canned' report. Not the first post about need for this info.


Michelle, I don't think query could be guaranteed to be accurate as "first installment due date" may be the same as year pledge made or a future date. You would need gift date for the pledge. Nice idea though.

Christine Cooke 2

Tom Klimchak:

Hi...


I seem to stumble upon this problem every few years:  Is there any way find payments in THIS fiscal year for pledges that were started in PRIOR fiscal years?


So I want to find all the PAYMENTS in July for Pledges that were entered/started before July 1.


The best I've been able to do it make a Gift Query of all the Pledges created prior to 7/1 and then create a Pledge Status Report for all the activity after July 1.  


Any other ideas? Am I missing something simple?

How about Query 1 Looks for for all pledges prior or within a certain date range.  and Query 2 looks for all the Pay-Cash, -Stock etc.... within this FY.  Then Merge the two with the AND operator.  That should give you a list of payments.

Christine Adams

We use campaign codes and gift dates for this.  Each year we have two campaign codes - one for tax-deductible gifts and one for the non-tax deductible (events, etc.)  For last year our campaign code was 18GIFT and this year 19GIFT, etc.  - so our query for pledge payments received on prior year pledges would be campaign code is one of (all years except current) and pay-cash (or stock) with the July gift dates.


We don't include these payments in our dollars raised for the year so it's important for us to have the different campaign codes.

Alex Wong

Hi,


2020 and seems to be still an issue?


I am seeing some suggestion on using "year" base campaign code, and then you can query on payment gift type with campaign code of previous year(s). However, this is subjective to data entry error to make sure that the payment applying to a given pledge must have the same campaign code as the pledge. Question then becomes, how do you check for errors for all payment gifts of the year are correctly having the pledge's campaign code. Problem balloon quickly when pledge and/or payment is split gift, and payment is paying multiple pledges.


For anyone with access to the RE database to query it directly (can't be hosted by Blackbaud, so meaning any other hosting where hosting company allow you that access or self-hosted), Here's a SQL i have, modify as needed which shows you all payment gifts in 2019 that is applied to pledge before 2019.

 

SELECT
 CAST(paymentgift.dte AS DATE) AS 'Payment Date',
 CASE paymentgift.type
  WHEN 1  THEN 'Cash'                         WHEN 2  THEN 'Pay-Cash'              WHEN 3  THEN 'MG Pay-Cash'
  WHEN 8  THEN 'Pledge'                       WHEN 9  THEN 'Stock/Property'        WHEN 10 THEN 'Stock/Property (Sold)'
  WHEN 11 THEN 'Pay-Stock/Property'           WHEN 12 THEN 'MG Pay-Stock/Property' WHEN 13 THEN 'Pay-Stock/Property (Sold)'
  WHEN 14 THEN 'MG Pay-Stock/Property (Sold)' WHEN 15 THEN 'Gift-in-Kind'          WHEN 16 THEN 'Pay-Gift-in-Kind'
  WHEN 17 THEN 'MG Pay-Gift-in-Kind'          WHEN 18 THEN 'Other'                 WHEN 19 THEN 'Pay-Other'
  WHEN 20 THEN 'MG Pay-Other'                 WHEN 21 THEN 'Write Off'             WHEN 22 THEN 'MG Write Off'
  WHEN 27 THEN 'MG Pledge'                    WHEN 30 THEN 'Recurring Gift'        WHEN 31 THEN 'Recurring Gift Pay-Cash'
  WHEN 34 THEN 'Planned Gift'
 END AS 'Payment Type',
 paymentgift.amount AS 'Payment Gift Amount',ip.amount AS 'Payment Gift Amount Applied to Pledge',
 CAST(pledgegift.dte AS DATE) AS 'Pledge Date',pledgegift.amount AS 'Pledge Gift Amount'
FROM gift AS paymentgift
LEFT JOIN installmentpayment AS ip ON ip.paymentid=paymentgift.id
LEFT JOIN gift AS pledgegift ON pledgegift.id=ip.pledgeid
WHERE
 paymentgift.dte BETWEEN '1/1/2019' AND '12/31/2019' AND
 paymentgift.amount>0 AND
 paymentgift.type IN(2,3,11,12,13,14,16,17,19,20) AND
 pledgegift.dte<='1/1/2019'