Cannot insert summary on a formula

[Pete271]

Hello

Hoping someone can help me with this, I am on Crystal 8.5 using an Access 2000 database.

I am writing a report on donations my organisation has received this financial year (Aug 2008 to July 2009).

The report is grouped first by Gift Designation (sponsorship, annual fund, legacy, matched gifts)

and then by {GfCnBio.GfCnBio_Import_ID} so that people with multiple gifts during the year are grouped together.

I then need to display the gifts in four columns:

Cash (money in)
Pledge (promise of money to come)
TY Paid Pledge (a pledge made this financial year and paid in this financial year)
PY Paid Pledge (a pledge made in a previous financial year but paid this year)

The problem I have is with the last two columns. In the data file they both have the same gift type so I cannot simply do a formula of

if {Gf.Gf_Type} = "Pay-Cash" then ToNumber({Gf.Gf_Amount})

Instead I tried

if (Previous({GfCnBio.GfCnBio_Import_ID}) = {GfCnBio.GfCnBio_Import_ID}
and Previous({Gf.Gf_Type}) = "Pledge")
and {Gf.Gf_Type} = "Pay-Cash"
then {Gf.Gf_Amount} else $0.00

This is because if the donor is paying a pledge made this year they will have two records (the pledge and the payment). If they are paying a previous years pledge they will only have one.

But (as I found from reading this forum) I cannot insert a summary on this because I am using 'previous()'

Can anyone think of a different way I could approach this?

Hope it makes sense,

Peter

 

[lbass]

Don't you have a date field associated with these Gf_types?

-LB

 

[Pete271]

I do have a date field, but all gifts brought through in the export will have a date for this financial year.

Some of the original pledges are five or six years old and to bring those into the exported file would mean bringing all gifts in the database.

It is a problem with the software I'm using (Raiser's Edge) that it doesn't have an exportable field that links the pledge to the payment.

 

[lbass]

How are the GfCnBio and Gf tables linked? On what field? What is the {table.field} name for Gift Designation? What is your record selection formula?

-LB

 

[Pete271]

The Gf table links to GfCn which links to GfCnBio table that holds the {GfCnBio.GfCnBio_Import_ID}

Gift Designation is Gf.Gf_Type

I have no record selection formula (are you refering to the Select Expert?) - all records produced in the export file should displayed.

 

[lbass]

In your first post:

Gift Designation (sponsorship, annual fund, legacy, matched gifts)

This seems to be something other than "Pay-Cash" and "Pledge" which you also said was Gf.Gf_type.

-LB

 

[Pete271]

Sorry - my mistake

Gift Desigination - Gf.Gf_Gift_Subtype

 

[lbass]

Can you clarify whether there is a maximum of two gifts per {GfCnBio.GfCnBio_Import_ID}? If there can be more and this ID refers to a person, not a pledge/payment ID, then is there a field that is unique to the pledge/payment pair? It would really help to see some sample data.

Sorry for asking these questions piecemeal.

-LB

 

[Pete271]

LB - no problem with the questions, I knew when I wrote the original post that this wouldn't be straight forward.

There is no maximum number of gifts per {GfCnBio.GfCnBio_Import_ID}.

Unfortunately there is no exportable field for the pledge/payment pair. I have asked the company who supply the software and they have confirmed that this is the case.

Sample data:
Gift1 - Pledge

Gf.Gf_date: 08/01/2008
Gf.Gf_Subtype: Matched Gifts
Gf.Gf_Type: Pledge
Gf.Gf_Amount: $100000.00
Gf.Gf_Import_id: 30156-027-000012

GfCnBio.GfCnBio_Import_id: 0001-027-10009

Gift2 - PledgePayment
Gf.Gf_date: 10/15/2008
Gf.Gf_Subtype: Matched Gifts
Gf.Gf_Type: Pay-Cash
Gf.Gf_Amount: $100000.00
Gf.Gf_Import_id: 30156-027-15357

GfCnBio.GfCnBio_Import_id: 0001-027-10009

Let me know if need more examples - the above is for a pledge made and paid in the same financial year.

Peter

 

[lbass]

By sample data, I meant as they would display in detail rows, and for more than just two records. What I can't see is how you would order these records and know that they "go together" if there are more than two per ID. Do you match on subtype and amount?

I was thinking of another approach, but if you are getting the correct values using your original formula that uses the previous function, then you can use variables to collect these results and summarize in the group footer. Use formulas like this:

//{@reset} to be placed in the ID group header:
whileprintingrecords;
currencyvar sumtypaid;
if not inrepeatedgroupheader then
sumtypaid := 0;

//{@accum} to be placed in the detail section:
whileprintingrecords;
currencyvar sumtypaid := sumtypaid + {@TYPaidPledge};

//{@display} to be placed in the ID group footer:
whileprintingrecords;
currencyvar sumtypaid;

-LB

 

[Pete271]

Hi LB

Sorry for the delayed response I have been out of the office.

I did as you said above but I still cannot get the summary for the group.

What I can't see is how you would order these records and know that they "go together" if there are more than two per ID. Do you match on subtype and amount?

I do match by subtype but not amount - sometimes the paid pledge will only be a portion of the overall pledge.

You also mention another approach - I am happy to go back to the drawing board if it will get the entire report to work.

Thanks again for all of your efforts so far.

Peter

 

[lbass]

If you did what I said you would have a summary by group, so I need more information. Is it an incorrect amount? If so, in what way?

So how do YOU know what payments go with what pledges? Just matching subtype would not be sufficient. Are you still able to use the date field in some way?

-LB