Data Requisition: All Constituent Pledge's and Installments with Payment ammount and Payment date

John Bozek · July 14

I am unsure whether this would be a query or an export and I lack experience working with the financial data in RE. From my little bit of experimenting, I don't think all of the tables exist that I need in the query module and have been working through export methods. I would like to produce a report like table though formatting is not a big issue; I can use other tools to reformat the data.

The data I need is essentially a replication of, in DBview when viewing a constituent record and select the “Gift” tab, then filter to pledges and installments. Critically I the inclusion of the corresponding payments and payment dates is where I am getting hung up.

really appreciate any help or insight!

Dariel Dixon · July 14

@John Bozek I'm not really certain what specific data you are looking for, but an export should help here. You can set the number of installments and export the fields per installment. It's probably going to be a bit messy but the data to create the table will be there.

At my org, we use a lot a couple of different queries to pull in and match up payments to pledges for reconciliation with finance, as well as some import files.

John Bozek · July 14

@Dariel Dixon unfortunatley im being called in as a pinch hitter to fingure this out so im not entirely sure either. The reconcilliation you described sounds very similar to what my stakeholders have described to me. Could you offer a bit more insight into how you allign pledge installments with payments. I was thinking the query format might be more useful in this case and using the xor sub or the like merge queires might help me get to my end goal.

JoAnn Strommen · July 14

@John Bozek Not sure but this might provide what you need. Have you looked at REPORTS > Pledge and Recurring Gift Reports > Activity Report? You can select the individual or a query and it can show pledge and payments. Not quite as neat as db view display but might give you what you need.

John Bozek · July 14

@JoAnn Strommen This was a great direction to look in. I got very close with reports>Financial reports> Gift Details and Summary Report. I found it difficult to line up pledges with payments toward those pledges. Appreciate the help!

Dariel Dixon · July 14

@John Bozek Here's the gist of our process. We use a simple query of Gift Type = Pledge Payment and Gift Date = Last Month. We then use 2 queries; one has the gift information for the payment, and the other has the original pledge information. We also use a import date file and merge the files together in Power Query in Excel.

It sounds more convoluted than it actually is in practice. But we don't create a table per se outside of the natural nature of the spreadsheet. You have to use different queries for it because you're looking for different gift types; one will be looking at payment types while the other is on the pledges.

Alex Wong · July 15

@John Bozek
not sure what level of expertise you have with RE and SQL and/or API, but to get exactly all the data needed:

if you have access to the underlying RE database table (meaning you are self-hosted, not Blackbaud hosted), OR you request a database backup of RE and load into your own MS SQL Server
- then you can query the table needed to get all the details you want
  - GIFT table for basic pledge info (pledge amount, pledge date, etc), payment info (payment amount, payment date), write off info (write off amount, date)
  - INSTALLMENT table contains each installment details of the pledge (ONE pledge can have ONE or more INSTALLMENT): installment date, installment amount, etc
  - PLEDGEPAYMENTS table (or view, not remembering now), providing details on the connection between a pledge gift to it's payment or write off gift. (i.e. payment gift id linking to pledge gift id, and how much was paid for that pledge/installment)
if you are hosted by Blackbaud and is on RE NXT, you can use Gift v2 SKY API to obtain all of above information, however, it is done through one pledge at a time.

Mark Bezanson · July 15

@Alex Wong

We are looking for the same thing as John. We have the nightly backup but do not have the expertise to create the query. Is this something that you may be able to help us with to at least get a start on creating the query.

Thank You

Alex Wong · July 15

@Mark Bezanson

SELECT
g.id AS 'Gift System ID',CAST(g.dte AS DATE) AS 'Pledge Date',g.amount AS 'Pledge Amount',
paywo.pay AS 'Total Payment',paywo.wo AS 'Total Write Off'
FROM gift AS g
LEFT JOIN (
SELECT
pp.pledgeid,
SUM(CASE WHEN g.type<>21 THEN pp.amount ELSE 0.00 END) AS pay,
SUM(CASE WHEN g.type=21 THEN pp.amount ELSE 0.00 END) AS wo
FROM pledgepayments AS pp
LEFT JOIN gift AS g ON g.id=pp.paymentid
GROUP BY pp.pledgeid
) AS paywo ON paywo.pledgeid=g.id
WHERE g.type=8 AND g.amount>0

Mark Bezanson · July 15

@Alex Wong
Thank you so much! This definitely gives us something to work with.

Data Requisition: All Constituent Pledge's and Installments with Payment ammount and Payment date

Comments

Categories