Multiple transaction records in one table

[rrtraverse]

I have created a CR that pulls all loans with a trans type number 1 and 2, with trans type 1 eq "Loan Added" and trans type 2 eq "Loan Paid Off". Both of these trans types will have a date tied to the transaction which is stored in the transaction date field. How do I subtract the transaction date tied to the "Loan Paid Off" transaction from the "Loan Added" transaction date to create a "Number of Days" field.
Every loan will not have a "Loan Paid Off" transaction, so my formulas thus far are -
REPAID DATE:
if tablename.transtype} = 1 then {tablename.transaction_date} else Date(0000,00,00)

LOAN ADDED DATE:
if tablename.transtype} = 2 then {tablename.transaction_date} else Date(0000,00,00)

These two formulas are giving me a separate transaction date for these two tranactions, but then when I try to subtract LOAN ADDED DATE from REPAID DATE to get number of days, I get 0 days for all loans.

I'm sure this is something I should know, but I've tried everything I know, and can't get this to work.

Can someone help?

 

[Turkbear]

Hi, Try this:

@NUMBER OF DAYS
If @REPAID DATE <> Date(0000,00,00) AND 
@LOAN ADDED DATE <> Date(0000,00,00)
then
REPAID DATE - LOAN ADDED DATE 
else
0

Then you could, if you wanted, create a text object that says 'Loan Not Paid Off' and place it on your report in the same location as you place your NUMBER OF DAYS Formula.
Use the Suppress options on each field to show one or the other based on the formula's result.

 

[rrtraverse]

Still getting zeros.

 

[lbass]

The problem is that dates are coming from the same field. If you always have only two records per account, then you could use a formula like:

if {table.transtype} = 2 then {table.date}-previous({table.date})

...if transtype = 2 is for "loan paid off"--your post reverses the numbers in different places.

You could also use your formulas in your initial post in the details section and then create a third formula, assuming there are only one trans type 1 and one trans type 2 per account, but possibly some other trans types. The formula would look like:

maximum({@repaid date},{table.acct})-maximum({@loan added date},{table.acct}) //where {table.acct} is your group field

If an account can have more than one record of each type, please display a sample that shows various possibilities.

-LB

 

[synapsevampire]

You'll get much better results if you provide:

Crystal version
Database/connectivity
Example data
Expected Output

I understand why your formulas will often fail, you have an else, so the first row sets the loan added, the next one clears it because of the else (assuming 2 rows)...

LB's approach seems close, try grouping by the loan, and then use:

maximum({tablename.transaction_date},{table.loan})-minimum({tablename.transaction_date},{table.loan})

If this doesn't resolve, provide what I'd suggested. Note that there have been several attempts to resolve the issue, which is not because it's hard for these folk to do, it's because they don't understand the environment.

-k

 

[rrtraverse]

Using Crystal v.8.5 to connect to an SQL database through an ODBC connection. All transactions (actions) for a particular loan are stored in a table called Log, which is actually a history file. There are many transaction codes available for a loan, but I am only interested in the Loan Added and Loan Repaid transaction. There should never be more than one of each of these transactions for each loan in the database, since one tranaction adds the loan to the table and one closes the loan. This is a monthly report so some loans will only have transaction as of a month end date.

I have grouped the report by loan id, and minimum({tablename.transaction_date},{table.loan}) will work to pull the Loan Added tranaction since it will always be the first tranaction for every loan, but maximum({tablename.transaction_date},{table.loan})-minimum({tablename.transaction_date},{table.loan}) will not necessarily provide the Repaid tranaction for the loan if the loan has not been repaid or has not been repaid within the reporting period.

When the report runs, the results are two detail lines if both transactions have occured within the reporting period. So if I have created one formula called Repaid date that says if tranaction code = Repaid then Date and I have another formula that says if transaction code = Loan Added then Date, why can't I subtract one date from the other to get Number of days.

Your help is appreciated!

 

[synapsevampire]

OK, I understand.

In the Group Header place the following:
Whileprintingrecords;
datevar LoanDate := cdate(1950,1,1);
datevar RepaidDate := cdate(1950,1,1);

In the details use:
Whileprintingrecords;
datevar LoanDate;
datevar RepaidDate;
if tablename.transtype} = 1 then
RepaidDate := {tablename.transaction_date}
else
if tablename.transtype} = 2 then
LoanDate := {tablename.transaction_date}

In the Group Footer use:
Whileprintingrecords;
datevar LoanDate;
datevar RepaidDate;
If RepaidDate <> cdate(1950,1,1)
and
LoanDate <> cdate(1950,1,1) then
"Repaid in "+totext(RepaidDate-LoanDate,0,"")+" days."
else
"Not Repaid, it's been "+totext(currentdate-LoanDate,0,"")+" days."

Should get you there.

-k

 

[lbass]

Please try either of my suggestions. I think they either one will work.

-LB

 

[rrtraverse]

I used synapsevampire's solution and everything works great now except if there is Repaid Date and not a Loan Added Date. I think I understand why this isn't quite working, but I don't know how to fix it.
The group footer formula says if Repaid Date <> cdate(1950,1,1) then Repaid Date - Loan Added Date
else Current Date - Loan Date.

What if there is a Repaid Date <> cdate(1950,1,1) but no Loan Added Date?

And one more question. Can I sum on the GrpFooter formula. The users want the average number of days before a loan is repaid, by Customer. Would a manual Running Total work?

I can't say Thank You enough!

 

[IdoMillet]

I believe a completely different approach, based on an aliased copy of the Log table, would be the best choice.

In Crystal, you already joined "Loan" to "Log".
Now use the menu option of
Database, Add Database to Report...
and select the "Log" table again. Give it an alias of "Log_Repaid" and join it to "Log" based on Loan_ID.

Now include in your record selection formula the conditions of:
------------------------------------------------------
({Log.transtype} = 1) AND ({Log_Repaid.transtype} = 2)
------------------------------------------------------

From this point on, the rest should be simple and obvious.

Note: if you need to keep cases where a loan has type 1 but not type 2, you would need to use either an outer join (with a check for null values as an alternative value to '2') or a UNION approach.

Cheers,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com