need help writing a formula (or two?)

Well, we've all been there--having to deal with a bad database design that was created by people who don't know the impact of their choices. We have an off-the-shelf software package that has a FieldContact table. The record doesn't have fields to hold the start-contact and end-contact times.

SO...our management decided that the users can enter the start- and end-times in a free-form memo text field!! A how-to document was sent out to let users know how to enter the data. But, just doing a visual check, not very many records have been correctly entered.

Here's what the users are supposed to enter in the memo field:
Start Time:{space}mm/dd/yyyy{space}hh{colon}mm{space or newline}
End Time:{space}mm/dd/yyyy{space}hh{colon}mm
Total Time: 1.75hrs

What I need to do is to...
- edit the start and end times and create bad-date-text if a date was bad
- compare start and end times; if endTime<startTime create endTime<startTime error text
- if both dates are valid and endTime>=startTime, do a DateDiff between them in seconds

Please HELP!!!

I'm not a Crystal Reports expert, but the complexity of the problem will probably depend upon how incorrectly according to the given spec the data has been entered. This will determine how much work needs to be done to try to make sense of what has actually been entered.

If this came across my desk, I would be strongly tempted to do it outside of Crystal Reports. Produce an exported text file from the software package containing the data, write a filter that scans the text file, manipulates the data as required and then writes the adjusted data to an export file. If needed, then re-import this modified text file back into the software package or just use it 'as is' for reporting purposes. This has the advantage of not directly working on 'live' data so any problems with the filter can be sorted out without changing any 'live' data. You don't say what you are going to do with DateDiff - just show this figure in a report or ??

I would suggest that the contents of the memo field are first examined to see what formats of data have actually been entered so the algorithm for the filter can be designed and then coded.

To paraphrase, to err is human but to really mess things up takes talented management!:D

Quote:

---

Originally Posted by 2kaud

I'm not a Crystal Reports expert, but the complexity of the problem will probably depend upon how incorrectly according to the given spec the data has been entered. This will determine how much work needs to be done to try to make sense of what has actually been entered.

If this came across my desk, I would be strongly tempted to do it outside of Crystal Reports. Produce an exported text file from the software package containing the data, write a filter that scans the text file, manipulates the data as required and then writes the adjusted data to an export file. If needed, then re-import this modified text file back into the software package or just use it 'as is' for reporting purposes. This has the advantage of not directly working on 'live' data so any problems with the filter can be sorted out without changing any 'live' data. You don't say what you are going to do with DateDiff - just show this figure in a report or ??

I would suggest that the contents of the memo field are first examined to see what formats of data have actually been entered so the algorithm for the filter can be designed and then coded.

To paraphrase, to err is human but to really mess things up takes talented management!:D

---

Since it's a CrystalReports report, I don't have to worry about accidentally modifying live data.

You wouldn't believe what the users have actually entered! The edit algorithm needs to assess whether the start time and end time info was entered in the format that I gave in the original post of the problem. One of my problems is that I don't know how to define and assign values to multiple variables in a single formula. Eventually, I'll take the "seconds" that were calculated from valid-record DateDiff values and sum them up as a report grand total. The bad-record text will serve to indicate which records should be repaired.

IMO I would still be looking to do the grunt of the work outside of CrystalReports. I would be thinking about getting a text file produced that contained the required fields and then writing an external program (c++/perl/ruby?) to actually perform the analysis - considering the quality of data to be analysed.

Quote:

---

You wouldn't believe what the users have actually entered!

---

I would! In the past I've done a lot of work with extracting, analysing and normalising into a standard form data from various databases. It's amazing how many different ways various people can come up with to enter what should be the same data!