Crystal Reports: database table lookup in formula

I'm using Crystal Reports for VS 2010. In a CR formula I'd like to be able to lookup a database table. The function would be something like this:
   lookup(tablename, pk_col, result_col).

For example, lookup("States", "StateCode", "StateName") would look up a state code such as "AK" from table "States" and return "Alaska".

Is there any such thing?
LVL 1
BlearyEyeAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
GJParkerConnect With a Mentor Commented:
This isn't practical in CR without using subreports which would have an impact on performance. Why can't you simply add the states table to the report and link on the statecode field.
0
 
mlmccCommented:
The full versions of Crystal have what os called a SQL Expression.

Check the field explorer to see if you have that.
If so create a new one.  It is just basically SQL but it can only return a single VALUE.

Something like

SELECT FullStatename FROM States WHERE StateCode = {StateCodeField}


mlmcc
0
 
BlearyEyeAuthor Commented:
The SELECT expression doesn't seem to work in the VS version. That's too bad, since it would be nice if it were available. But adding the extra tables did the trick.
0
All Courses

From novice to tech pro — start learning today.