Exporting Pledge ID Number

Lindsay Godman 2

I am needing to export the ID number for the corresponding pledge for each payment. Is there a way to do this?

James Andrews

Not easily, sadly. You could export all the pledges with all their installments and payments, then query for your payments with the pledge ID outside of RE. Otherwise it's relatively easy to do in SQL if you have back end access to the DB.

Karen Diener 2

What ID? The gift import ID of the pledge itself, or something else? Why do you need that ID?

I only ask because if this is an ongoing process or procedure, I think the answer is that you cannot. If it is a one-time thing because you are converting data to a new system, you can get it through an import process.

If you can provide some more context, we should be able to arrive at an answer!

Karen

Lindsay Godman 2

I was looking at the actual Gift ID that is listed on the Misc tab of the gift record.

This is going to be a one time thing. We are converting to a new system and are wanting to make sure pledge payments are matched up with the correct pledges.

James Andrews

Lindsay Godman:

I was looking at the actual Gift ID that is listed on the Misc tab of the gift record.

This is going to be a one time thing. We are converting to a new system and are wanting to make sure pledge payments are matched up with the correct pledges.

If it's a one-time thing I think it's possible. It's kinda fiddly and complicated but I THINK I could walk you through it. Let me know if you want me to delve into it.

Just know that the Gift ID isn't how the payments are actually linked to the pledge. There are two tables in between, and the system record ID is what you're actually going to be looking for. The Gift ID is just there for user convenience.

Anyway, let me know; I haven't had to do this but I think I could figure it out, especially if you have some power query experience and can figure out unpivot…

Karen Diener 2

Lindsay Godman:

I was looking at the actual Gift ID that is listed on the Misc tab of the gift record.

This is going to be a one time thing. We are converting to a new system and are wanting to make sure pledge payments are matched up with the correct pledges.

OK - that's good, actually!

The end result will not have a lot of data to view, but what you can do is use an import to create your data file. I use this when I'm converting clients from RE to something else.

It is a Gift import and you specifically want Pledge Payment. Have a query ready of your pledges, and use that in the “Include” section. That will only appear after you have selected the radio button of “Create import file”.

I'm not sure how familiar you are with importing, but on the “Fields” tab, you'll list all of the fields you need for your new system. Make sure that you keep the required fields in place too:

On the left are the fields that will show - by default - in the final file. GFImpID is the import ID of the pledge payment, but the equally important field is “GFLink”. That is the import ID of the original pledge, and that is how you can tie a pledge to a payment.

Hopefully that is enough to get you started. Do some testing first to make sure you understand what is going on, and even try a test run in your new database if at all possible. Send me a direct message if you run into a huge snag and I'll see if I can help out.

Karen

Isaac Comer

I'm not sure how helpful this is, but fyi: internal to the database the actual “gift id" that doubles as the pledge's id is: the Pledge Gift's System Record ID (not the “gift id” on the misc tab, rather it's the one you see when you click on the pledge's Properties).

The pledge and its payments (and write offs) are linked through an intermediary table which IIRC is named InstallmentPayments. There is also another table that lists all the installments themselves.

Unfortunately, I'm not sure if there's a good way to get to that data without direct access to the database tables.

James Andrews

Karen Diener:
On the left are the fields that will show - by default - in the final file. GFImpID is the import ID of the pledge payment, but the equally important field is “GFLink”. That is the import ID of the original pledge, and that is how you can tie a pledge to a payment.

Oh great, that's an awesome idea! I didn't realize that field was available in import.

Alex Wong

Our org is moving from RE (self hosted) to RE NXT and is looking for better pledge and payment reporting for financial purposes. (apparently it is important to know if a payment is paying current year's pledge or prior year's pledge).

Currently being self hosted, we are able to issue SQL query to get all the information we need, as pledge, payment, and write is a messy many to many relationship. RE application itself's capability has been very limited. This has serve our purpose very well.

However, moving to RE NXT will mean we will lose the ability to do SQL querying. So I have been seeking what's the best route and came across your method and gave it a try. The finding though seems off. So I want to confirm if I may have done something wrong.

The data file produced in your method seems to not show all pledges the payment is applied against. Example: Payment A ($25K) is applied to Pledge 1 ($2K) and Pledge 2 ($23K). In the data file, GFLink only show the import id of Pledge 1. There is also no data for how much was applied to Pledge 1.

Any thought?

Thank you

Becca Montjoy

I tried this technique, but end up with an empty data file when I click Create Now. What step might I be missing?