So I'm doing a pledge report in crystal, kind of a combination of pledge status report and a cashflow report. Balance as of 6/30/11, payments in FY12, balance as of 6/30/12, payments due FY13, 14, total outstanding, etc.

And despite the fact that my crystal formulas are relatively simple, it keeps coming up with odd numbers when I'm summarizing what has been paid so far.

It turns out that what happens is that if a gift is applied to more than one installment, RE exports the gift multiple times, once for each installment that it's applied to. And if an installment has more than one gift applied to it, RE will export the installment multiple times.

In Export, there's no 'apply this amount' in the payment gift section, and there's no date in the payment section to correspond with amount.

So how do YOU guys work around this? Say you want pledge balance as of 6/30/11? I created a group based on concatenating the Installment Amount and Date, so unless someone has the unlikely situation of two installments due for the same amount on the same date, that's cool.

But what next? If I just add up the Payment Amount, I don't have a date available. If I go with the Payment Gift Amount, I get the whole gift amount instead of what's applied to the installment. There's an 'installment schedule' field in the payment gift but I"m not sure what that does; I'm checking now. EDITED TO ADD: this exported as an empty field.

Anyone? Is this just impossible? It's so frustrating; I'm so close to having this pretty awesome report working.

In your export the Installments/Payments/Amount should be the amount applied (I think?)

The Installments/Payments/Payment Gift/Gift Amount is the full amount of the gift (as you know).

Installments/Payments/Payment Gift/Gift Date should be both the gift date and the date of the amount applied. Is this the date you are looking for?

The installment date is available under Installments.

Should have asked: are you using a custom report or exporting and linking to Crystal? The trick may be in your joins (if using Access). Without actually setting this up I'll guess that you are getting a table for the pledge, a table for Installments, maybe a table for the full payment and another table that links payment to installments? Could be confusing to get the joins just right.

SEVERAL MINUTES OF WRITING DELETED, EDITED AGAIN BECAUSE I'M AN IDIOT.

If {payment gift date} in (date range) then {payment Amount}


is the formula I'm looking for. I understand now. Hopefully this works now.