Selecting Parent and Children of a Left Outer Join 1

[Axminster]

Hi there, my first post here!

I have 2 tables joined by a left-outer. The {part} table and the {part_comp} table.

What I'd like my report to do is, with the use of a groupable formula, list the parent part and its components (if any).
In their most basic, relevant form, the tables look like this:
[tt]
Part Part_Comp
Part Part Comp_Part
000001 000001 001001
000001 001002
000001 001003
000002 000002 002001
000002 002002
000003
000004
000005 000005 005001
[/tt]
All I have so-far is this:

//{@Compo}
If IsNull({part_comp.comp_part}) Then {part.part} Else {part_comp.comp_part}

I know this will only display the part OR its components, I simply can't get my head around how to display the part AND...

Can anyone solve this one?

 

[lbass]

What is the problem you are running into? What is the resulting display (using your sample data)? I'm assuming you have a left join FROM part TO PartComp, with no selection criteria on PartComp fields.

-LB

 

[IdoMillet]

To have the formula return both, use:

//{@Compo}
If IsNull({part_comp.comp_part}) Then {part.part} Else {part.part} & " " & {part_comp.comp_part}

Are you aware of the Crystal option to do hierarchical grouping? See Online help...

hth,
- Ido

view, export, burst, email, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[Axminster]

@lbass
Thanks for the reply. Yes, I've left-outer joined from part to part_comp; no selection criteria.
The problem I'm running into is that if a part has components, my formula only returns those components. What I need is the part AND its components. If referring to the table in my initial post, we choose part 000001, we should have:
[tt]000001
001001
001002
001003
[/tt]

@IdoMillet
Thanks for yours also. Unfortunately, your suggestion just appends the component to the part, so we would get:
[tt]000001 001001
000001 001002
000001 001003
[/tt]
Hah! Hierarchical Grouping! B****r me sideways. (Please, don't)
That fixes everything, I've never used it before. Now I don't need my formula OR the subreport, OR the shared variables.

Thanks to the both of you for your efforts!

 

[Axminster]

Annoyingly now, my reply of thanks is not being submitted...

 

[Axminster]

@lbass
Thanks for the reply. Yes, I've left-outer joined from part to part_comp; no selection criteria.
The problem I'm running into is that if a part has components, my formula only returns those components. What I need is the part AND its components. If referring to the table in my initial post, we choose part 000001, we should have:
[tt]000001
001001
001002
001003
[/tt]

@IdoMillet
Thanks for yours also. Unfortunately, your suggestion just appends the component to the part, so we would get:
[tt]000001 001001
000001 001002
000001 001003
[/tt]
Hah! Hierarchical Grouping! B****r me sideways. (Please, don't)
That fixes everything, I've never used it before. Now I don't need my formula OR the subreport, OR the shared variables.

Thanks to the both of you for your efforts!