Lookup Fields

[Former Member]

Post Author: Blivet

CA Forum: Formula

I am using some complex tables that have one-to-many relationships. In addtion, many fields that need to be grouped are coded. These codes are kept seperately in another table that translates them into Human readable. For example:

MainTable.circumstance = 1;

CodeTable.CodeKey = "CIRC"; (this tells which category of records to read from)CodeTable.KeyCode = 1; (This is the same as the main table)CodeTable.Desc = "While Running"; (This is the human readable correlation to the number 1;

When I do the main query, it just shows the numeric code of couse, but I need the human readable. The problem is the CodeTable has multiple entries with same code, but the "CIRC" determines which CodeKey I need.

The logic I need, as the sql runs, is to go out and get the proper description:

SELECT description FROM CodeTable WHERE CodeKey="CIRC" and KeyCode = 1;

That would be the select statement, but I need this to happen to populate one field. It's almost like I need to create a link between CodeTable.KeyCode and MainTable.circumstance, but I only want it to give me the description from the codeTable where CodeKey = "CIRC". There may be other records in the codeTable that have a KeyCode of 1, but the CodeKey will be "TYPE".

Any help appreciated.

[Former Member]

Post Author: bettername

CA Forum: Formula

I think you just need to join them correctly...

If you are getting your data with a SQL query, then it should look like one of the following:

--if the MainTable doesn't have a 'CodeKey' field...

select mt.something, ct.desc from MainTable mt

left join CodeTable ct on ct.KeyCode = mt.circumstance

and ct.CodeKey = 'CIRC'

--if the MainTable does have a 'CodeKey' field...

select mt.something, ct.desc from MainTable mt

left join CodeTable ct on ct.KeyCode = mt.circumstance

and ct.CodeKey = mt.CodeKey

However, if you are doing all your linking in Crystal, you'll want to do the following:

Link your two tables using the KeyCode field.

If you can also link with the CodeKey, then that should be it. Otherwise, in the Select Expert, use the following: isnull({CodeTable.CodeKey)) or {CodeTable.CodeKey} = "CIRC"

If you have other fields that link to your lookup table, you'll have to add another reference to the lookup table in the database expert, and replicate the above step(s).