Formating report condionally by date and type

[omega1983]

Using crystal 8.5, I have a financial report that looks similar to the following:
date trans amount acct#
01/05/02 Gift 100 1
01/10/03 Pledge 50 2
01/15/03 Gift 75 3
01/22/03 Pledge 50 3
01/25/03 Pledge Payment 20 2
01/26/05 Pledge 50 4
01/30/05 Pledge Payment 50 4
02/01/05 Gift 50 4
The objective is to show a pledge transaction first, the accompanying payment second, followed by a gift, taking into consideration the date and account number.
The last three transaction are exactly what I want. The 01/10/03 Pledge is interrupted by a gift of 75, a pledge of 50 on 1/22/03 then back to the pledge payment of 20 on 1/22/03. Therefor the transactions I just described should look something like this:
01/10/03 Pledge 50 2
01/25/03 Pledge Payment 20 2
01/22/03 Pledge 50 3
01/15/03 Gift 75 3
01/26/05 Pledge 50 4
01/30/05 Pledge Payment 50 4
02/01/05 Gift 50 4

Essentially I am trying to format by date, but the transaction sequence must be pledge, pledge pay, then gift, even if the date is interrupted. Is this possible?

 

[lbass]

First insert a group on {table.acct#} (you can suppress the group header and footer if you wish). Then create a formula {@sort}:

if {table.trans} = "Pledge" then 1 else
if {table.trans} = "Pledge Payment" then 2 else
if {table.trans} = "Gift" then 3

Then go to report->sort records and add {@sort} as your field to sort by. For the report display, continue to use {table.trans} (don't display the sort formula).

-LB

 

[omega1983]

lbass,
Thanks for the information, however this is the same as sorting the transaction.type field in descending order. Let me rephrase this and demonstrate exactly what I want the report to look like. The field name is in parathesis

date(gift.date) trans(trans) amt(table.amt) acct#(acct.num)
01/15/05 Pledge 50 1
01/16/05 Payment 10 1
01/18/05 Gift 10 1
01/20/05 Pledge 100 2
01/25/05 Gift 50 2
This is idea sequence. However, since I have the data on the detail line, sometimes a payment will show before a gift or something like this:
date(gift.date) trans(trans) amt(table.amt) acct#(acct.num)
01/15/05 Pledge 50 1
01/16/05 Pledge 10 2
01/18/05 Payment 10 1
01/20/05 Gift 100 2
01/20/05 Payment 10 2
01/20/05 Pledge 100 2
The main issues here are grouping by date and account number and keeping the data together in th acct#. The problem is you can only group by one field

 

[lbass]

Actually, in your original post, it was not the same as descending order; in your second post, you have changed the value of transtype to "Payment", instead of "Pledge Payment", which then makes it in descending value. I thought you wanted to order the records by the transaction type within each account, regardless of date.

If this is the case, I stand by my original suggestion--group on account number and suppress the group header and footer, and then depending upon your actual result for transaction type, either sort descending by transaction type or use my earlier formula. This should work correctly even in the case when the transactions occur on the same date.

If this still doesn't meet your needs, you need to explain why in more concrete detail. For a particular account, do you want the sequence to "reset", i.e., do you expect to see a series of "Pledge, Payment, Gift" followed by another sequence of the same?

-LB

 

[omega1983]

LLBass your last statemet is exactly right. Lets say a pledge comes in on 1/15/05 under account number 1. Conceivably, a gift could come in on another account number, say accT#2 before the first payment on acct#1 even comes in. What I want to see is a series of pledge, payment, then gift in ascending date order under acct1. Then I want to see another series of pledge, payment, gift for acct#2. The reason why it is cimplicated is because the data shows up as the transactions come in, not in a series even though logic says that a pledge should come first, followed by a payment etc.

It would not make sense to sort on acct# since the acct# is alphanumeric and therefore having nothing to do with the date. I wish there was a way I could send you a screen print to make this more clear. Thanks for your help thus far

 

[lbass]

Please try my solution. I can tell that you have not.

You are still describing only the need to sort within an account, regardless of date. You did not answer my question about what happens within an account. What would you want to see in the following case all from the same account:

01/15/05 Pledge 50 1
01/16/05 Payment 10 1
01/18/05 Gift 10 1
01/20/05 Pledge 100 1
01/25/05 Gift 50 1

Would you like it as is, or would you expect to see:

01/15/05 Pledge 50 1
01/20/05 Pledge 100 1
01/16/05 Payment 10 1
01/18/05 Gift 10 1
01/25/05 Gift 50 1

-LB

 

[omega1983]

Tell you what, let me take you inside the actual report itself. I may not be explaining myself correctly.
Two groups: Gr1 grouped by gift.effdate. Gr2 grouped by gift.acct#. Formula named @sort created with the following:if {table.trans} = "Pledge" then 1 else
if {table.trans} = "Pledge Payment" then 2 else
if {table.trans} = "Gift" then 3. Formula added to sort order using the acsending/descending button. On detail line, I have the actual data. Here is an example of a problem transaction:
date acct trans amt
7/1/94 M3088 pledge 100
8/25/94 M6022 gift 50
9/1/94 M6111 pledge 250
12/31/95 M3088 payment 50
So the problem here is that the pledge on 7/1/94 appeared, but the payment did not appear until 2 other transactions. What I need is this:
date acct trans amt
7/1/94 M3088 pledge 100
12/31/95 M3088 payment 50
8/25/94 M6022 gift 50
9/1/94 M6111 pledge 250
So essentially, I want a date order but to extent that all related data appear for that acct# in the transaction order. So it is not a TRUE date order report

 

[lbass]

Well, that was key information. You need to remove the group on effective date, so that your report is grouped only by account number. I would use {@sort} for the first sort, and then add effective date as the second sort in case of ties.

-LB