Export basic pledge info to CSV or Excel?

Contributions, Faith Promises

Export basic pledge info to CSV or Excel?

Postby UUUSOffice » Mon Jan 09, 2017 2:26 pm

Good morning!

All I want is a row of data with name, pledged amount for this fiscal year, and payments made toward that pledge -- all three of these items on the same spreadsheet row.

I have looked at the reports and saving them as a data file, but all of the reports have these multi-line records for each name that are not useful on a spreadsheet to be used for a simple mailmerge.

From reading around the board, it looks like I might have to create a query and custom report just to export such a simple set of data?

I'm thinking this is going to be a pain, but hoping I'm wrong. Hope to hear about a simple way to extract this basic pledge data to use in a nice mailmerged letter! Anyone?
UUUSOffice
 
Posts: 30
Joined: Tue Apr 14, 2015 10:09 am

Re: Export basic pledge info to CSV or Excel?

Postby NeilZ » Mon Jan 09, 2017 3:17 pm

craburn86 wrote:Good morning!

All I want is a row of data with name, pledged amount for this fiscal year, and payments made toward that pledge -- all three of these items on the same spreadsheet row.

I have looked at the reports and saving them as a data file, but all of the reports have these multi-line records for each name that are not useful on a spreadsheet to be used for a simple mailmerge.

From reading around the board, it looks like I might have to create a query and custom report just to export such a simple set of data?

I'm thinking this is going to be a pain, but hoping I'm wrong. Hope to hear about a simple way to extract this basic pledge data to use in a nice mailmerged letter! Anyone?


Actually, its not that hard.

Go to Contributions Custom Reports. The first screen will ask if you want to run reports against Posted,Unposted, or Pledge data. Select Pledge.

The next thing will say this is your first report (perhaps) and you need to create one. So proceed, and enter a title for the report, click OK.

Then you start to base your query filters. You want to pull the pledge data for this year, for a certain contribution fund (I'm assuming), for a either a range of envelope numbers, or just members, whatever.

So, the first thing you're going to see is the filter dialog. The first field you will probably want to select is the Contribution Fund, so go down the list of data items until you find the Contribution Fund Number. Highlight that in the list, then click Next Step.
The default on Step2 (Pick an operator) is Equal a number[/] which is probably what you want. So click on [i]Next Step. On Step 3, you'll enter the Contribution Fund Number you want. Then click on DONE.

You'll then see a screen with this filter listed, you now want to add additional filters.
The next one would be to limit the search to a range of envelopes, or just to members. I'm using envelopes as we issue members a certain range, then give visitors another range so we can do these types of reports easily.

So, you click the ADD button, a popup will appear asking that you Select a Connector. You want to select AND as all these filters MUST be fulfilled before the data is selected. Once you do that, you'll be brought back to the filter setup screen. Step 1 is find the data item, you would use envelope number, In Step 2 you would select the Between two Numbers, in Step 3 you would enter the envelope range, for example between 1 and 100.

Finally, you need to select the Pledge Period Start, then the Pledge Period End. The procedure is the same, you would Add another filter, with AND as the connector, find the data item for Pledge Start Date in Step 1, Step 2 you would want Greater Than or Equal to, and Step 3 would be 01/01/2017. End date is much the same, except in Step 2 you want Less than or Equal To, and the date of 12/30/2017. FWIW your pledge period should end this year on 12/30/2017 as if you make it 12/31/2017 you create 53 weeks for pledges, which throws off pledge amount totals since there are only 52 Sundays.

Once all this is done, a pop-up will appear asking if you want to run this query now. Say OK. Then you'll see a screen which gives you an explanation on how to pick output fields, click OK.

Then you can select the fields you want to see. I'd select Mailing Name, Total Pledged, and Amount Received. Then run the query and see if that's what you want to see. If it is, close the DIsplayed data window, and a popup will appear asking if you want to create a report. Say no at this time. You'll be back at the screen that shows you the filters, and the data items.

Select the PICK OUTPUT TYPE button at the top, you'll get a screen listing the type of outputs available. Select the ASCII test file with comma separated fields, click on the button with three dots next to the file name ( ... ) and select the output location and enter a filename, the filename MUST begin with [b]QMF_[/b} its required. Click OK, then you're back to the Output Screen, then click DONE.

You can now run your query. You'll get the data review screen ( or uncheck the BROWSE QUERY RESULTS to bypass this). Close that window if necessary, and you'll get a popup that says it found XX number of records, do you want to create a text file with comma separators? Click yes, and you'll get a confirmation popup that the file was created, and do you wish to view. You can click YES to view it, or NO to finish this process.

Then click DONE on the query parameter screen, then DONE on the Query List Manager. You can now use that file for your mail merge.

Does this help?

Neil Zampella

Using PC+ since 1999.
NeilZ
 
Posts: 9108
Joined: Wed Oct 08, 2003 12:20 am
Location: Connellsville, PA

Re: Export basic pledge info to CSV or Excel?

Postby UUUSOffice » Mon Jan 09, 2017 3:21 pm

Oh, Neil, thank you -- you do know that the sheer length of that explanation makes it *appear* to be a pain, even though I trust you that it actually isn't. I did see this procedure back on an old thread but was hoping there was a new magic button for it! :)

So, I'll do this when I have a couple of quiet moments. Thank you so much for your time and talent. I will let the forum know how it goes!
-- Sandy
UUUSOffice
 
Posts: 30
Joined: Tue Apr 14, 2015 10:09 am

Re: Export basic pledge info to CSV or Excel?

Postby Zorak » Mon Jan 09, 2017 3:24 pm

[Edit, just noticed Neil's post... It might even be easier than you think...]

There is an option in Contributions > Other Output > Create a Mail Merge File. Choose Selected Pledges, then enter your selection as you would when running a report. That will give you contact info, pledge start and end date, pledge period, amount per period and total amount pledged to date (calculated as of the ending date you choose in the selection screen).
Zorak
Tech Support
Tech Support
 
Posts: 2571
Joined: Thu May 13, 2004 8:59 am
Location: PowerChurch Software

Re: Export basic pledge info to CSV or Excel?

Postby UUUSOffice » Mon Jan 09, 2017 3:28 pm

[quote="Zorak"][Edit, just noticed Neil's post... It might even be easier than you think...]

Hey, thank you, I have tried that but it doesn't show the amount that has come in on the pledge so far, just the pledge amount -- at least that's what it is doing for me!
UUUSOffice
 
Posts: 30
Joined: Tue Apr 14, 2015 10:09 am

Re: Export basic pledge info to CSV or Excel?

Postby NeilZ » Mon Jan 09, 2017 3:35 pm

craburn86 wrote:
Zorak wrote:[Edit, just noticed Neil's post... It might even be easier than you think...]

Hey, thank you, I have tried that but it doesn't show the amount that has come in on the pledge so far, just the pledge amount -- at least that's what it is doing for me!


Have you posted Contributions lately ?? Unless you do, it won't show anything as coming in.

Neil Zampella

Using PC+ since 1999.
NeilZ
 
Posts: 9108
Joined: Wed Oct 08, 2003 12:20 am
Location: Connellsville, PA

Re: Export basic pledge info to CSV or Excel?

Postby UUUSOffice » Mon Jan 09, 2017 3:36 pm

NeilZ wrote:
craburn86 wrote:
Zorak wrote:[Edit, just noticed Neil's post... It might even be easier than you think...]

Hey, thank you, I have tried that but it doesn't show the amount that has come in on the pledge so far, just the pledge amount -- at least that's what it is doing for me!


Have you posted Contributions lately ?? Unless you do, it won't show anything as coming in.


Thanks, yes, I post them every week right after I make the bank deposit. That report Zorak mentioned just doesn't seem to have a column for receipts on the pledge at all, it seems to me.
UUUSOffice
 
Posts: 30
Joined: Tue Apr 14, 2015 10:09 am

Re: Export basic pledge info to CSV or Excel?

Postby NeilZ » Mon Jan 09, 2017 3:39 pm

Zorak wrote:[Edit, just noticed Neil's post... It might even be easier than you think...]

There is an option in Contributions > Other Output > Create a Mail Merge File. Choose Selected Pledges, then enter your selection as you would when running a report. That will give you contact info, pledge start and end date, pledge period, amount per period and total amount pledged to date (calculated as of the ending date you choose in the selection screen).


I thought about this one, but it really doesn't show how much has been contributed to the pledge to date. Perhaps in future you may want to add that to the output as an additional field for use.

Neil Zampella

Using PC+ since 1999.
NeilZ
 
Posts: 9108
Joined: Wed Oct 08, 2003 12:20 am
Location: Connellsville, PA