Whenever you have multiple tables in your report you need to specify the linking fields that connect the tables (the join). Crystal will often take a guess at the correct links using “auto-linking”. In recent versions of CR you can auto-link two different ways, “by name” or “by key”. CR tries “by name” as the default.  This works when the linking fields are the same name in both tables and when there are no other fields that have the same name between the tables. In my experience it correctly links the tables one out of three times. It more often adds extra links that break the join.  Just because Crystal has created a link you can’t assume that the link is correct.

But some databases are configured with primary and foreign key relationships that are visible through the database connection.  These databases allow Crystal to auto-link “by key” pretty reliably.  I tested it recently while Continue Reading »
Auto-linking tables by key.

One of my customers was reading data from XML. He ran into an amount field stored as a string and noticed that some of the values had been converted to scientific notation. They looked something like “1.234E+6”. He asked if there were a way to convert those back to the original number. After a bit of playing around I came up with the following, which works with most styles of “E” notation that I have seen.

Local StringVar Input := Replace ({@input}, '+', '');

if "E" in Input
 then val(Input) * 10^ val( Split (Input, 'E')[2] )
 else val(Input)

If the field doesn’t have an “E” in it the formula returns the value of the number. It should also work if the E is followed by a plus, a minus or nothing or even if the plus or minus is at the end instead of right after the E.

When my customers send me report problems they sometimes feel the need to send me a photo album. I will get a screen shot of the linking window, a screen shot of the preview tab, a screen shot of some key formulas, etc.  It is much more efficient to send me the RPT file. With the RPT I can open the linking window myself, open any formulas, and open anything else that might help me troubleshoot the report. An RPT can replace most of the screenshots I get and gives me much more information. If the RPT can’t be saved with data I may Continue Reading »
Troubleshooting from Screen shots

One of my customers asked me if there was a way to group records by product code. The tricky part was that each record contained several product codes entered as a list like this:

A101, B201, C301

Fortunately, I have run into this requirement before. One time it was primary and secondary sales reps stored in two separate fields in the same record. Another was a list of characteristic keywords stored in a “multi-value field”.  The challenge is getting Crystal to show one record in multiple groups at the same time. Normally this isn’t possible. But I have found a technique that allows this to happen by duplicating the original record as many times as needed and then Continue Reading »
Grouping by multiple values in one record (multi-value field)