Trying to avoid another subreport

[aaronburro]

I've got a really simple report I am running that is an Alternate Item report. It shows the items numbers and descriptions of alternate items for each item in our system. The AIs(alternate items) are stored in a table like so:

IM4_AlternateItem
|-----------------|
|ItemNumber |
|AltItemNumber1 |
|AltItemNumber2 |
|... |
|AltItemNumber8 |
|-----------------|

Linking Structure:
--------------------
IM1_InventoryMaster.Item# --> IM4_AlternateItem.Item# (*= LOJ)

I group by IM1_InventoryMaster.Item# and have 9 detail sections. The first section shows only if there are no AIs, and it says "No Alternate Items." *shocker* The next 8 detail sections are reserved for each potential AI, respectively.

Here is the problem: I'd like to show some basic info about the AIs in their respective detail sections, but I can't link back to the IM1 table on Item#, as that obviously creates a cycle, and, it'd be stupid anyway, because it would exclude all the IM1 records (no record has two different item#s, duh). My thought was to create 8 aliases to IM1 and link from the IM4 table to each alias IM1 via IM4_AI.AltItemNumber# to IM1_Alias_#.Item#. So I do that. I can then add the various info from the alias table, but only for one alias. Adding fields from any more than one alias causes the report to generate an error.

The only other method I can think of to make this report involves a subreport for each AIs detail section. That works flawlessly, but it leads to a LOT of subreports being opened (think: 8 subreports per item in our system, potentially).

Am I using the aliases correctly?

 

[aaronburro]

Extra note:

Yes, I am using fields from the correct alias table in the respective detail section for the AI

 

[Madawc]

What error do you get using extra aliases? I've used as many as four in Crystal 8.5 without problems. (You also need to say which Crystal version.)

I'm not sure I follow what you're after. But if the details are in a group, you can do a summary total with maximum and minimum values.

Right-click on a field and choose Insert to get a choice of Running Total or Summary.

Running totals allow you to do clever things with grouping and formulas. They also accumulate for each line, hence the name. The disadvantage is that they are working out at the same time as the Crystal report formats the line. You cannot test for their values until after the details have been printed. You can show them in the group footer but not the group header, where they will be zero if you are resetting them for each group.

Summary totals are cruder, but are based directly on the data. This means that they can be shown in the header. They can also be used to sort groups, or to suppress them. Suppress a group if it has less than three members, say. They default to 'Grand Total', but also can be for a group.

Madawc Williams (East Anglia, UK)

 

[aaronburro]

I'm looking for more simple stuff like Item Descriptions and other seemingly non-summarizable fields

 

[Madawc]

Sorry, I don't follow your comment. You haven't said if you've tried my solution.

Crystal does have rules that one has to accept and live with. Such as no summaries of summaries.

Madawc Williams (East Anglia, UK)

 

[aaronburro]

oh, sorry, I probably didn't say that correctly. What I meant to say was that I'm not really looking to use any kind of numbers or statistics on the Alternate Items in the detail section, but rather I am looking for more textual information from the AI's ItemMaster table, such as Item Description and that kind of thing. Thx for the suggestions, though, I'll give those a try if I can figure out how to utilize them in this situation...

 

[MrBillSC]

Either I'm not following your request, or you don't need to link back to the IM1 table. If you are linking from the IM1.Item# to IM4.Item# why would you want to link from the IM4.Item# back to IM1.Item# when you already have that record in your record complex. It's data is available to you. Also, I don't see why you need more than 2 detail subsections, unless you are reporting different record fields in each subsection. If you do need to link back to IM1, you should only need to do so once since you are reporting in the detail section.
MrBill

 

[aaronburro]

If you are linking from the IM1.Item# to IM4.Item# why would you want to link from the IM4.Item# back to IM1.Item# when you already have that record in your record complex.

I probably wasn't clear on my description of the IM4 record. Each record has an ItemNumber which is linked to IM1 on ItemNumber. Each record also has 8 AlternateItem#s in it, each of which are different than IM4.ItemNumber. (Why would an item be an alternate item for itself, you know?)

 

[MrBillSC]

Sorry, I didn't read your original post correctly. I think your solution of 8 alias' linking from IM4 to IM1 should work. I have used up to 5 alias' to get descriptions with no problems (vers 8.5). What is the error message you are getting?
MrBill

 

[aaronburro]

well, now I can't get it to recreate the error, of course. But, now it is just painfully slow. I'm fairly certain I am linked on all the keys, so...