How do I tie a string parsed from a field back to a different DB table

[krpurcel]

I am using Crystal XI R2 against an Oracle DB.

The string in a DB field contains an ID that looks like "uswknp09". I parse the field to get just that ID. Now I need to convert that ID to a full name based on the DB table fields FIRST NAME and LAST NAME.

How do I create a formula that ties the ID string I parsed to the FIRST NAME and LAST NAME fields so I get the analyst's name instead of their ID? I think I need to do this as a string var, but am not clear on how to accomplish it.

I realize I will need to run this as a "while printing records" formula so that the ID string is parsed before the names get pulled.

Thanks

 

[Turkbear]

Hi,
How are you parsing the ID string and can you post the table structure?





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[HowardHammerman]

The problem you are running into is endemic to any SQL-base database and, of course, to Crystal. You are trying to link one table to another using a formula field. You can't do it. However, you can use a formula field as a link to a sub-report containing the info you want.
1) Create a report from the table that holds the fields for FIRST name and LAST name. Include the two fields plus the ID field. Save the report.
2) in your current report create a sub-report using the formula that has the id numbe as the link and pick the id number from the new report.

You may have to manipulate both reports to get the look and feel you want.

Howard Hammerman,
Crystal Training and Crystal Material
On-site classes and one-on-one coaching
Low-cost telephone/email support
FREE independent Crystal newsletter
howard@hammerman.com

http://www.hammerman.com

800-783-2269

 

[satinsilhouette]

That would be correct, you can join the parsed field to a field you get as a variable passed up from the subreport and get your results.
Or - how about working with your dba and making a quick view that would do the work for you, and crystal picks it up and presents it? Let the database do the heavy lifting.

Thanks so much!
satinsilhouette

 

[krpurcel]

Thanks all. Turkbear I am parsing it with the following formula: right({ACTIVITYA1.DESCRIPTION},8).

Table structure. There are five tables that reside in the same Oracle DB, and one Excel spreadsheet that lives on a network share. The 5 tables are:
ProbSumM1
ProbSUmM2
ActivityM1
ActivityA1
ContactsM1

ProbSumM1 is the primary table. It links to ProbSumM2 and ActivityM1 by TicketNumber. ActivityM1 links to ActivityA1 by ActivityNumber. The FIRST NAME and LAST NAME fields are in CONTACTSM1 and tie back to ProbSumM1 by ContactName. Finally ProbSum ties to the Excel spreadsheet by AssigneeName.

I will try to make it work using Howard's suggestion. Thanks to you all for your replies. I will let you know how it goes.

Thanks!