Table linking problem 1

[ajdesigns]

Hi
I am running cr 8.5 with sage line 500 / MS 2000 server backend.
I need to write a report containing 2 tables. The problem is table 1 "stock" has fields warehouse, product, qty, price, etc. and table 2 Stock dets has fields Warehouse/product, commodity code, country etc. Table 1 has individual fields for warehouse and product whereas in table 2 these fields are combined into warehouse/product.
How then can I join these tables eg product(table1) to warehouse/product(table2) thus enableing me to get the commodity code for a product in table 1.
Thanks in advance for any help.

 

[GJParker]

You won't be able to do this directly using th elinking expert, but there are a number of options open to you.

The best way woudl be to do this on the database end using a view to split the warehouse/product field in Table 2 and then use this in your report instead of Table 2.

I would suggest this as the best option, but if you don't have access to the database to do this, then you could also create your join as part of teh Record Selection Formula i.e.

{Table1.Warehouse} = //formula to split {Table2.WarehouseProduct}

If you need help working out a way to split the field into the parts you require then post back.

HTH



Gary Parker
MIS Data Analyst
Manchester, England

 

[lbass]

You could use the stock table in the main report and use stock details as the table in a subreport. In the subreport, create a formula that splits the warehouse/product field and then link the subreport to the main report using this formula to match the product field in the main report.

-LB

 

[ajdesigns]

Thanks Lbass
I have done this and I now have a sub report attached to the main. the trouble is I need to take the commodity code field out of the sub and show it against the relevent product code in the main.
What I forgot to mention is that the main report is based on an order which contains only products ordered, I can supress the sub but how can I show the commodity code from the sub,against the relevent product in the main.

 

[GJParker]

If you had used the formula in the main report to link the tables rather than linking subreports, the commodity code would be available in the main report.

By going the subreport way you will now have to either display just the commodity code in your subreport and align it in the correct position on your main report

or

use shared variables to pass the commodity code value back to the main report

either way you cannot suppress the subreport as this will cause the subreport not to execute.





Gary Parker
MIS Data Analyst
Manchester, England

 

[ajdesigns]

Hi Gary
How do I get the table2 into the report if I cannot link it with table1.
And if I cannot add it, it is not available in record select.

 

[GJParker]

If you are creating your report by adding tables in the designer yuo will be able to go into the database expert and add table 2 from the list of database tables, do not try to create a link and click ok.

The fields from table 2 will now be available to your report.

HTH

Gary Parker
MIS Data Analyst
Manchester, England

 

[ajdesigns]

I have added table2 (stockem)where table1 is (opdetm)and then created the formula as follows{opdetm.product}=Mid ({stockem.whproduct},3 ,10 ), to split wh/prod of table2 and say the product part is = to the prod in table1.if I add the formula in the report I get false returned in the details field and although the commodity code is available if I add it to the details section of the report in order to get the commodity for the product code I just get a blank commodity field for every product.

 

[GJParker]

try adding a formula Mid ({stockem.whproduct},3 ,10 ) and the opdetm.product} into the details section, do the fields match ?

make sure there are no leading or trailing spaces, use the Len() function to check the length of the fields.

HTH



Gary Parker
MIS Data Analyst
Manchester, England

 

[ajdesigns]

The opdetm.product shows the correct product but the formula field is blank. I checked the length and the stockem.whproduct was 22 char long so I changed the formula accordingly still the same blank.I also got a message when I added the stockem table saying "your current link config contains multiple starting points , this is not generally accepted. I assume this is because the tables are not linked.

 

[GJParker]

The tables will be linked when you add {opdetm.product}=Mid ({stockem.whproduct},3 ,10 ) into the Record selection formula.

The indication that you got false when you added this formula into your report shows that at the moment they do not match.

Once you sort out the formulas required to make them match then this will work.






Gary Parker
MIS Data Analyst
Manchester, England

 

[ajdesigns]

I added the formula I am now getting 2 blank fields in my report. and in select expert it say's "Composite expression please edit in formula editor"

 

[GJParker]

what do you now have as the record selection formula for your report ?

waht formulas are you using on your report ?

Gary Parker
MIS Data Analyst
Manchester, England

 

[ajdesigns]

only the one splitting the wh/prodcode

 

[GJParker]

maybe this is my impatience showing, but I do not want to play 20 questions with you.

I will just refer you back to my earlier post

Once you sort out the formulas required to make the 2 fields match then then your report will work.


Gary Parker
MIS Data Analyst
Manchester, England

 

[ajdesigns]

Thank you for being so Patient.

 

[lbass]

GJParker,

I cannot add two tables to a report without linking them and get results, even if I do "link" them in the record selection formula. If you are able to do this, maybe this is database dependent?

ajdesigns,

If you are willing to try the subreport method again, all you need to do is create a formula within the subreport:

mid({stockem.whproduct},3 ,10 )

I'm not sure this is the correct formula though. Could you give an example of the field results? If there are no characters after the product segment (only before), you could just use mid({stockem.whproduct},3). Anyway, place this formula in the detail section of the subreport so you can see if it is displaying correctly. When you have it displaying correctly, you can suppress it (the formula). Add the commodity code field to the details section of the subreport as well. Then suppress all subreport sections except the details section.

Place the subreport in the details section of the main report, and in the linking screen, add {opdetm.product} as the field from the main report (move it to the right) and then in the left bottom corner, you should see something like {?pm-opdetm.product, and in the right bottom corner, use the drop down list to find the mid formula you created, and choose that as the field from the subreport to link on.

Now you should be able to see the code in the main report. You don't need to use a shared variable to see it.

-LB

 

[GJParker]

LB

I don't know if this is database dependant or version dependant but I am using V9 and V10 developer versions and have just tested this with

MS SQL 2000
Access 2000
Flat File Database

and the link is created and used in the cRystal SQL for each of these.


Gary Parker
MIS Data Analyst
Manchester, England

 

[lbass]

I tested with 8.0 Professional using the Xtreme database, and the link in the record selection formula does not appear in "Show SQL Query", and no fields appear. I used a nonsensical formula that is accepted without error in the record selection formula:

totext({Orders.Customer ID},0,"") = left(totext({Orders_1.Order Amount},0,""),1)

...where Orders_1 is an alias table unlinked to Orders in the visual linking expert. Customer ID ranges from 1 to 270, and so customer IDs in 1 to 9 should appear.

-LB

 

[GJParker]

LB

the order amount field in the extreme database contains a currency symbol therefore

totext({Orders.Customer ID},0,"") = left(totext({Orders_1.Order Amount},0,""),1)

will return a $ or the currency sumbol set against currency fields in crystal.

I amended your formula to

totext({Orders.Customer ID},0,"") = mid(totext({Orders_1.Order Amount},0,""),2,1)

and this returned records corrcetly,

N.B. This worked with both ODBC and direct connection.



Gary Parker
MIS Data Analyst
Manchester, England