How to lookup description from a code 1

[sjh]

Hi,

I'm using Crystal Reports 11.5. In my database, all the data descriptions are stored in one lookup table called "tblLookup". This table is composed of following columns: table_name, field_name, code, and description. For example, the contents of this table looks like this:

tblCustomer tblState 'AL' 'Alabama'
tblCustomer tblState 'NJ' 'New Jersey'
tblProduct tblCategory '1' 'CD'
tblProduct tblCategory '1' 'DVD'

The report that I'm trying to create has more than 100 fields, which needs to be 'translated' using the lookup table.

Please recommend the most efficient way to implement this. I'm new to Crystal Reports and I'm not sure if creating 100 subreports is the best way to do it. Or, whether I have to create a monster SQL with complicated SQL. I'd like to avoid using hard coded values in the formula as new lookup records will be added and edited.

Thank you in advance!

S

 

[lbass]

Add the lookup table multiple times, once for each field it needs to decode, and add an extension that helps you keep track of the table, e.g., change the table name to "tblLookup_cust". I'm guessing you would link on as many fields as the lookup table shared in common with the table you are decoding, so for the customer table you would link on the field name and code in order to get the correct description field.

-LB

 

[sjh]

Thanks for your advice, LB! When I link the lookup tables, does it matter what join I use and whether the arrows points to or from the lookup table?

 

[lbass]

I would use left joins FROM the main table TO the lookup table in each case (the arrow would be pointing toward the lookup), just in case there are some records that are not decoded in the lookup table.

-LB