Linking tables with no common field

[petejigsaw]

I need to link two tables which have complimentary data, but no common field that will enable simple linking. To get the report to show the right data, they need to link on a composite field.

The two tables are {colour} and {stkmast}. I can create a formula that creates a common value in each table, but I do not know how I am going to be able to link them together??????

The two formulas are:

For {colour}:

{colour.cf_company} +
(IF (not(isnull ({colour.cf_supplier}))) then {colour.cf_supplier} else "??????")+
(IF (not(isnull ({colour.cf_range}))) then {colour.cf_range} else "????")+
(IF (not(isnull ({colour.cf_fabric}))) then {colour.cf_fabric} else "????")
+ {colour.cf_colour_code}


For {stkmast}:

IF( {stkmast.st_col_level} = 5) then ({stkmast.st_company} + "??????????" +
Mid ({stkmast.st_prod_code},1 , 4)+ Mid ({stkmast.st_prod_code},9 , 4)+ {stkmast.st_colour_cd})
else
IF( {stkmast.st_col_level} = 2) then ({stkmast.st_company} + {stkmast.st_sup_code} + "????" +
Mid ({stkmast.st_prod_code},9,4 )+ {stkmast.st_colour_cd})
else
IF( {stkmast.st_col_level} = 7) then ({stkmast.st_company} + "??????????" + "????" +
Mid ({stkmast.st_prod_code},9 ,4 )+ {stkmast.st_colour_cd})
else
IF( {stkmast.st_col_level} = 3) then ({stkmast.st_company} + {stkmast.st_sup_code} +
Mid ({stkmast.st_prod_code},1 ,4 ) + "????" + {stkmast.st_colour_cd})
else

IF( {stkmast.st_col_level} = 6) then ({stkmast.st_company} + "??????????" +
Mid ({stkmast.st_prod_code},1 ,4 )+ "????" + {stkmast.st_colour_cd})
else
IF( {stkmast.st_col_level} = 4) then ({stkmast.st_company} + {stkmast.st_sup_code} + "????" + "????" + {stkmast.st_colour_cd})
else
IF({stkmast.st_col_level}=1) then ({stkmast.st_company} + {stkmast.st_sup_code} + Mid ({stkmast.st_prod_code},1 ,4 ) + Mid ({stkmast.st_prod_code},9 ,4 ) + {stkmast.st_colour_cd})
else
"???????"

Can anyone help.......?

I have tried using Business Views etc to do this without success, and its now driving me crackers!!!!!!

Thanks for any assistance.

 

[lbass]

What is the purpose of all the question marks?

I think you should show us a sample of how the fields display that you need to link together. Please label each field.

-LB

 

[petejigsaw]

Sorry lbass - probably should have given more detail.

The '?????????' sections are simply placeholders, where an element of the concatenated field has no value to insert.

I've attached a link to a PDF of the {colour} output and the {stkmast} output which hopefully will help.

Put simply, the {stkmast} table contains all the main stock item records, but the colour that they are in is stored in the {colour} table using a combination of fields to determine the actual colour. I want to report stock based on colour, so need to link these two tables.

I hope the link helps, as it shows the concatenated output for each table and how it relates to the table fields.

 

[lbass]

I don't really see matching values in your sample, but if there are matches, one way you can link them is to use one table as a main report and the other in a subreport, where you use the two formulas to link the main report to the subreport.

-LB

 

[Cheerphilly]

Maybe I'm too simplistic, but using the Visual Linking Expert under Database, you can simply click on the field in table 1 and drag it to the appropriate field in table 2 that has a different name.