Monday, January 30, 2012

Crystal Reports : Catching Value Exceptions in Formulas

Here is a typical situation; you're working with a plain text date, by which I mean someone has just been entering in "loose" dates into a text field. So our values could be any one of these combinations of formatting:


The problem here was that I had to calculate number of days that had passed since the Date {CONTACT.CONTRACTDATE} in question, so I created a simple formula;


Which would return the number of days passed. Now, Crystal is pretty forgiving when it comes to plain text dates. It will actually "catch" all of the examples given above.

But when we started to run the report, it kept blowing up on "bad date values". Further inspection of the database revealed that users had been entering in values like:

01/01/2010 Signed
Estimate 01/2012

Which is to say they were mixing both text and date formats within the same field. So, the natural thing you'd want to do is add an exception to {@frmDateMath} like this; (kicking out 01/01/1980 whenever a date failed)

IF {CONTACT.CONTRACTDATE} LIKE "*Estimate*" THEN "01/01/1980"

...which would prevent the formula from ever trying to perform date math on a malformed date. (Assuming all malformed dates have the words "Estimate" or "Signed" in them)

ProTip: You may find yourself providing more exceptions than logic (depending on end users, in which case it is highly desirable to go to the source and start cleaning that up :)

No comments: