[ggs]

I have discussed this with Black Baud and they had no solution, so I will see if this is something anyone here has tried to do.

I want to see all my Pay-Cash received from Pledges made in the same time period as the Master Pledge only, no Pay-Cash from pledges made in previous time periods.

Example:

We have
Year 1 Pledge of $500.... and two $50 payments on that pledge in Year 1. that leaves $400 pledge bal.

Year 2 Pledge of $100... and two $50 payments on that pledge in Year 2. Also two $50 payments on Pledge from Year 1 received.

So what my query/export/report should show is that cash received in year 2 was $100.00 (even though i really received $200.00). I dont want to see the payments made on old pledges out of the time frame i specify.

I realize i could just look at pledges received in year 2 - pledge balance in year 2 and get the total paycash received, but i would like to look at the gifts individually as well.

Any ideas?

Thanks,

Gerhard

[Matthew]

you can do this with SQL in query analyzer or RODBA if you have that. it is not possible using the Query module of RE.

You can export all the pledges for a year and their linked payments and then analyze and weed out the results in Excel. You may be able to write formulas in excel to compare the data.

[SandyWilder]

Gerhard,
First, do a Gift query to group your Master pledges in the time frame you want. For example,
Gift type=Pledge
Gift date= This calendar year
Then, go to export, do a new gift export, and base it on your query of TCY Pledges.
From the list of gift fields, pull the Gift Date (just to prove to yourself you got the Master Pledges you wanted) and the Pledge balance.
Next, open the Installments node, then the Payments node, then the Payment Gift node. Selelct Gift amount. The system wil ask you
a:How many installments you want from each pledge
b:how many payments you want from each installment
As you continue to pull additional fields from each pledge payment gift (like gift type and amount), the system will continue to pull the same number of field values for each Pledge.
The only part of your issue this doesn't address is that it's pulling all pledge payments, not just Pay-Cash. I am not aware of anyway to get only pay-cash under these circumstances. Obviously, your could export it to Excel and delete anything not Pay-Cash.
This should work for you b/c you're pulling payment information only from Master pledges that meet your date criteria, so it's only pulling payments from those pledges, not all Pledges.
Let me know if for some reason this doesn't work for you.
Hope this helps!

[DrewAllen]

You can't do this in Query, because the RE front end to Query does not provide any method to compare two fields. As Matthew said, you can do it using SQL code.

If this is something that you plan to do frequently, I would recommend creating a custom Crystal Report. You can use formulas in Crystal to only display payments in the same time period as the master pledge. With Excel, you would have to re-create this logic every time you wanted to run the report.

Drew

[ggs]

Sandy's method works well for getting the numbers out (Thank You). I have not exported to an Access Database yet, but i am sure i can manipulate them as needed there, and get it a little easier than Excel, as exporting creates a large spreadsheet due to multiple payments and installments. Ultimatley I will probably hit the back end with Crystal and do a custom report that way. i just need to play with the tables and fields and figure out which ones to link for pledges and installments.

Thanks for everyone's help!