Export definition of pledge payments

Emily Harvie

Hi again,


This board was super helpful with my last question so I thought I would see if anyone had ideas for this one.


I'm trying to build an export definition where we are exporting monthly payments made on pledges. This data will be used for a donor letter that will list all payments made to a pledge within a certain year. However, I am struggling because the export is pulling in seemingly random pledge payments, not payments with the date that I am specifying in my selection. Further complicating this is I need the pledge payment information to display horizontally in Excel, not vertically.


For various reasons, we are not using the built-in Acknowledgement or Receipt export definitions. I am using a selection with this export definition that pulls a constituent communication preference, the application, and the payment dates (Jan 1. 2019 to Dec 31, 2019).


Does anyone have any ideas on a better way to approach this task or any advice for the export?


Thanks!!

Em

Lois Smith

In the Marketing Acknowledgement export definition, try going to Revenue Marketing-->Revenue-->Pledge Payments-->Payments.  For date, go to Revenue Details under that.  I think that the payments returned would be for the revenue defined in the query.  You can set the criteria to export as many as you want and each would appear in a separate column.  In the criteria box for the payment, be sure to include criteria of amount is greater than 0 to account for payments that were applied and then adjusted off.  


The output fields in your selection don't impact the results in the export definition, only the criteria in the selection.  So, if you are starting with a revenue selection that asks for pledges with payments in X time frame, a regular export from the administration area would start at Revenue instead of Revenue Marketing and follow the same as above.


However, if you have all the information you want in your selection and are comfortable with a little Excel manipulation, you can install the user defined function found here:  http://www.excelfox.com/forum/showthread.php/345-LookUp-Value-and-Concatenate-All-Found-Results   This takes multiple rows for a record and concatenates them into one cell.  So, if I create a revenue selection and ask for all pledge payments for the pledge, I get one row in my output for every payment and the pledge appears multiple times, once for each payment.  I can then use the Excel concatenate function to combine the payment date and amount into once cell.  Convert that column to values.  Then, using the user defined function, take all the rows for a single pledge and concatenate that into a single cell.  Copy that column, paste special values.  Then remove duplicates.  End result is a file to use in a merge with one row for each pledge and all pledge payments for that pledge in one cell to use in my merge.  While it seems like a lot of work, in reality it only takes a few minutes once you are used to the process.  I'm not afraid of some manipulation before the merge so this option would work for me.  Your mileage, of course, may vary.


Logically, any of these options would work.  Queries and export definitions are not always logical, however.    Good luck!

Aaren Ballinger

If you are not using the acknowledgement functionality in CRM, I would recommend building a constituent selection and pairing with a constituent export definition. This will ensure results with one row for each constituent and then multiple columns showing their giving history. You can always tackle it this way and then run a process to mark the records as having received a communication.

When you build a selection, it simply stores in CRM those constituent records that fit the criteria "pledge payments made 1/1/19-12/31/19". The export definition will pull those constituents as the “rows” in your output, but to get the specific giving history that you are looking for you must basically re-apply the correct filters within the export definition. Without specifying your desired pledge dates / gift types again in the export definition criteria, you will get the results that you experienced where it is surfacing seemingly random gifts for each constituent that are outside of what you specified in the selection criteria.

I find this can be one of the trickiest types of export definitions to build and can require a lot of trial and error. Feel free to reach out if you need more in-depth assistance!

 

Emily Harvie

Thank you both for your help! For the sake of time, I ended up going with Lois' recommendation of using a macro in Excel to combine all payments into one line and doing some manual intervention from there. That gives me until next year to figure out the export definition - it seems like I may need all twelve months!