GoldMine Tip: Crystal Formula for converting CONTSUPP.CITY to true DATE format #goldmine #crystalreports

If you have configured a 'Detail' record in your GoldMine system and then want to do some reporting on the CREATION DATE of the entries with the Crystal Reports tool (the reporting tool provided with GoldMine), you'll need to know how to define a Crystal Formula to convert the CONTSUPP.CITY Field to a true DATE format.

 

One of the idiosyncrasies of GoldMine is that the Creation Date of a GoldMine Detail Record is actually stored in a Text Field called CITY (even though you see it as Date in GoldMine)!  The extra challenge is that the data is in the format:

USERNAME (8chr) YYYYMMDDHH:MMam

So this is the formula to convert this Field, to extract the date and convert to a true date in Crystal, from where you can do more date manipulation (e.g. group report by Creation Month, etc.)

Create a new formula in Crystal (call it what you want) and paste in this syntax:

numberVar Truemonth := If  NumericText ((Mid ({CONTSUPP.CITY},13 ,2 )))= True
then ToNumber (Mid ({CONTSUPP.CITY},13 ,2  ))
else 0;
numberVar TrueYear :=If  NumericText ((Mid ({CONTSUPP.CITY},9 ,4 ))) = True
then ToNumber (Mid ({CONTSUPP.CITY},9 ,4 ))
Else  0;
numberVar TrueDay :=If  NumericText ((Mid ({CONTSUPP.CITY},15 ,2 )))= True
then ToNumber (Mid ({CONTSUPP.CITY},15 ,2 ))
Else 0 ;

If TrueYear = 0
then Date (1980,01 ,01 )
else
If TrueMonth = 0
then Date (1980,01 ,01 )
else
If TrueDay = 0
then Date (1980,01 ,01 )
else
Date (TrueYear,Truemonth ,TrueDay )

...You may also have a Record Section Formula in your report to ONLY report on these Detail Records, e.g.

{CONTSUPP.CONTACT}="Your Detail Record Name" and {CONTSUPP.RECTYPE}="P"