Skip to Content
author's profile photo Former Member
Former Member

Lookup Fields

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.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • author's profile photo Former Member
    Former Member
    Posted on Jan 31, 2008 at 12:37 PM

    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).

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.