Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Table Lookup Post Reply Post New Topic
Author Message
sjs0916
Newbie
Newbie
Avatar

Joined: 04 Oct 2012
Location: United States
Online Status: Offline
Posts: 3
Quote sjs0916 Replybullet Topic: Table Lookup
     Posted: 04 Oct 2012 at 3:16am

Visual Studio 2008 - created a crystal report with the wizard.  I have a couple of tables - a main table and several code tables.

I am trying to look up the description field in the code table by a field in
the main table and haven't had any luck.
 
Example: Main table: field: Product ID
                code table: Product ID
                                   Product Description
 
I want to retrieve the Product Description using the Product ID from the main table.
 
Any ideas?
Thanx,
Scott
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet Posted: 04 Oct 2012 at 3:21am

You need to link FROM the main table TO the code table.  If there is any chance that the main table will have a blank field instead of a value, you need to then righ-click on the join in the Database Explorer and make the join a "Left Outer" join.  That will return all of the records from the main table regardless of whether they have a corresponding value in the code table.  Then put the Production Description on your report.

 
-Dell
IP IP Logged
sjs0916
Newbie
Newbie
Avatar

Joined: 04 Oct 2012
Location: United States
Online Status: Offline
Posts: 3
Quote sjs0916 Replybullet Posted: 04 Oct 2012 at 4:50am
I left out that I have 2 fields in the main table that have ID's for the code table: ex. main table MachineCode1 and MachineCode2  both are ids to code file:  machinecode-MachineID, MachineDescription
Thanx,
Scott
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet Posted: 04 Oct 2012 at 5:00am

So a record can have two machine codes, each of which connects to a different record in the lookup table?  If so, they you'll need to add a second copy of the table to the report.  When you try to add a table that already exists in the report, Crystal will throw up a warning and ask if you want to "alias" the table.  It will then add the table with "_1" at the end of the name.  You then link from MachineCode1 to the original version of the lookup table and from MachineCode2 to the "_1" version of the lookup table.

 
-Dell
IP IP Logged
sjs0916
Newbie
Newbie
Avatar

Joined: 04 Oct 2012
Location: United States
Online Status: Offline
Posts: 3
Quote sjs0916 Replybullet Posted: 04 Oct 2012 at 5:28am
That worked great.  In the app I have, i do have 12 fields that refer to the same table, it will be a little cumbersome but it will work with the extra alias tables.
 
Thanx
Thanx,
Scott
IP IP Logged
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum