Linking tables based upon formula result

[stoopers]

I am attempting to write a report to categorize our revenue data. I have 2 tables: Revenue Data & Customer File, which are linked by the Customer Code.

Revenue Data:
Customer Code
Transaction Text (Customer Code + Invoice#)
Invoice#
Amount
Account
Dept

Customer File:
Customer Code
Customer Name
Category (ie. Coal, Oil, etc..)

I have the 2 tables linked by customer code (Left Outer Join). The problem arises when we book revenue via a journal entry. All of the fields on the Revenue Table are completed,except for the customer code. But I can get to the customer code on the journal entry records by using the Left(Transaction Text),10 formula. How can I relate this "formula" cust code to the Customer File to grab the Category field?

We are using Crystal 11 and iScala.

 

[IdoMillet]

If you can't create a View that presents the computed column as customer code, then use a linked subreport instead of a join.

Cheers,
- Ido

Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com