SBendBuckeye
Programmer
Client has a lease system with a payment record that holds payment schedule information. It is non normalized in that each record holds 30 months worth of data similar to below:
Mon1 Due Date, Mon1 Pmt Amt
Mon2 Due Date, Mon2 Pmt Amt
...
...
Mon 29 Due Date, Mon 29 Pmt Amt
Mon 30 Due Date, Mon 30 Pmt Amt
So a 5 year (60 month) lease would have 2 full records and a 4 year (48 month) lease would have 1 full record and a second record with buckets the first 18 buckets full.
I need to link these records to create an Excel extract which will contain information for remaining unpaid cash flow analysis.
The non normalized data is making the match up logic a little bit messy (normalizing the data is not an option as this is a purchased system). I have thought of 3 possible ways to accomplish this but wondered if there might be a better way. They are using Crystal Reports 8.5.
1. Create a subreport in the report header that would write out a text file of these records to normalize them. Basically I would have detail lines 1 through 30 and would suppress them if the field was empty. I'm not sure about a couple things here: 1) Can you have 30 detail sections and 2) If you write a text file out of a subreport in the report header, could I then turn around and reference it in the report or would I need 2 separate reports?
2. Do the same thing I mention in 1. above only in the main report with multiple detail lines suppressed as required.
3. Instead of creating multiple detail lines, create one long text string using Chr(13) to separate it into lines and then set the CanGrow property to true and then just print the one field in the detail record.
Also, any past due payments need to be rolled into the next payment due since this is a cash flow analysis. I would prefer to use scenario 1 above if possible since it would shove the ugliness into a subreport out of the way as much as possible. It would also make calculating various totals a lot easier as well.
Any ideas and/or suggestions from the folks out there? How did you handle similar situations? Are there any gotchas' lurking in the weeds? Thanks for any input you can provide!
Have a great day!
j2consulting@yahoo.com
Mon1 Due Date, Mon1 Pmt Amt
Mon2 Due Date, Mon2 Pmt Amt
...
...
Mon 29 Due Date, Mon 29 Pmt Amt
Mon 30 Due Date, Mon 30 Pmt Amt
So a 5 year (60 month) lease would have 2 full records and a 4 year (48 month) lease would have 1 full record and a second record with buckets the first 18 buckets full.
I need to link these records to create an Excel extract which will contain information for remaining unpaid cash flow analysis.
The non normalized data is making the match up logic a little bit messy (normalizing the data is not an option as this is a purchased system). I have thought of 3 possible ways to accomplish this but wondered if there might be a better way. They are using Crystal Reports 8.5.
1. Create a subreport in the report header that would write out a text file of these records to normalize them. Basically I would have detail lines 1 through 30 and would suppress them if the field was empty. I'm not sure about a couple things here: 1) Can you have 30 detail sections and 2) If you write a text file out of a subreport in the report header, could I then turn around and reference it in the report or would I need 2 separate reports?
2. Do the same thing I mention in 1. above only in the main report with multiple detail lines suppressed as required.
3. Instead of creating multiple detail lines, create one long text string using Chr(13) to separate it into lines and then set the CanGrow property to true and then just print the one field in the detail record.
Also, any past due payments need to be rolled into the next payment due since this is a cash flow analysis. I would prefer to use scenario 1 above if possible since it would shove the ugliness into a subreport out of the way as much as possible. It would also make calculating various totals a lot easier as well.
Any ideas and/or suggestions from the folks out there? How did you handle similar situations? Are there any gotchas' lurking in the weeds? Thanks for any input you can provide!
Have a great day!
j2consulting@yahoo.com