Thursday, November 1, 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:

1/1/12
01/01/2012
01/12
01/2012
etc...

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;

{@frmDateMath}
CURRENTDATE - {CONTACT.CONTRACTDATE}

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
...etc

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 "*Signed*" THEN "01/01/1980" ELSE
IF {CONTACT.CONTRACTDATE} LIKE "*Estimate*" THEN "01/01/1980"
ELSE {CONTACT.CONTRACTDATE}

...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 :)

7 comments:

Patricia Pabustan said...

It is really nice. Thanks for sharing.............

Crystal Custom

Dhiman Sarkar said...

Awesome stylish. It is very nice to me. I want to get it . How?

Crystal Custom

jannati jahan said...

I love this blog.

Crystal Custom

jannati jahan said...

There's nothing like doing assignments and writing lots of code. C# is the most important emerging language.

http://www.dotnetobject.com/

amar jan said...

Once the command has been created, you can store it in the BusinessObjects Enterprise Repository so it can be shared between many users.

Crystal Custom

Nomita Biswas said...

Everything is made of energy. Our bodies are made of energy and filled with energy. Energy is constantly flowing through us.

Crystal Custom

Dharmendro Bhuyan said...

We offer many different styles of custom imprinted sunglasses so that everyone is satisfied.

Customized sunglasses