using same table twice

[mehlerak]

I need to link separately to the same table. For example: sales detail has link to part number table via primary PN link. It also has a link to the part number table via alternate PN link. When linking sales detail to part number table, I get the right PN. When I add another alias (part number1) and link that to sale detail via alternate PN link, both the PN and the alternate PN in the report now show the alternate PN. What is wrong here?
CR Pro v9.
Thanks

 

[lbass]

What are the "rules" about the PN and alternate PN fields? Is there an alternate PN field for all records, or is it null in some cases? Is the PN field null sometimes? You might want to try changing the joins to left outer (FROM sales details->PartNo and From Sales->alias PartNo).

If this doesn't address the issue, it would help if you provided some sample data and then show how the data should be displaying.

-LB

 

[chelseatech]

Alias tables should solve your problem.

Just put the table in your report more than once. The second occurance requires an "Alias". So you have to give it a difference internal name. There is still only one table in your database but Cr can use it twice.



Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[mehlerak]

The rules are as follows: if there is an alternate PN, the link to the alternate will show up in the alternate field and the link to the primary will show up in the primary field. If there is no alternate PN, the link to the primary part shows up both in the primary and the alternate fields.
Say sales detail has primary part 'A' that has no alternate. link to 'A' would be in both the primary and thet alternate fields. In that case, the report shows (correctly) part 'A' both as the primary and the alternate.
If part 'A' had alternate 'B', the link in the primary field is to 'A' and the link in the alternate field is to 'B'. The report should show part 'A' as the primary, part 'B' as the alternate, but it shows part 'B' as both.

My sales detail table is linked once to the parts table (via the primary key) and also to parts_1 (an alias) via the alternate key. Both links are left outer joins, which are required for something else. I don't think this is affecting anything (I tried with inner joins as well).