Link tables using a formula 1

[Deano1976]

CR v8.5

I want to be able to link part of a field in one table with the full field in another table. i.e.

I have a field which contains Root Cause for various sites:

Root Cause
SITE1
SITE2
SITE3

I have another field in a different table which contains the details of these sites, but the field I require to link to has a number and no "SITE" preceding it:

Link Desired field
1 Northern Region
2 Midlands Region
3 Southern Region

I need to be able to split the Root Cause field so it only shows the number, using a formula (I can do this) and use the resulting number from this formula as my link to the number link field in the other table which contains my regions.

 

[lbass]

Place the description table in a subreport. Create a formula in the main report {@siteno}:

right({table.site},1)

And then use the formula as the linking field to the subreport ({@siteno} (in the main report)->{table.siteno}(in the subrpt)).

-LB

 

[synapsevampire]

You'll notice significant performance degradation using a subreport.

If possible, create a View or QUery (Access) on the database to do the join, and then use the View as the data source.

Were you to have posted your database type, someone might even supply the SQL to do so.

-k