One to many

[coolstrike23]

Hi

I have a table like below

ITEMNO OPTFIE VALUE
1 ABC XYZ
1 DEF UVW
1 GHI RST
2 ABC OPQ
2 DEF STU

I need to get "VALUE" - RST for a subreport and place it as a column for each detail line of the sub.
I understand we cannot add a subreport to a subreport in Crystal

Any ideas?

Thanks

 

[lbass]

There isn't enough information here to respond. How is the main report structured? Where in the main report would you place the sub? What tables are being used in the subreport? Is there a field you can link to? Why do you mention that a sub cannot be used within a sub--how is that relevant to the issue at hand?

-LB

 

[coolstrike23]

LB

The existing sub is placed in detail b of main report and works as required.

Now need a new field in the sub report from above table.

I need to link the existing "Item No" field in the sub with the "Item No" in OPT table ( shown prev)and return the value when "OPTFIE" is 'GHI'

Exisiting sub is

ITEM NO ITEM NAME QTY USED SUPPLIER "NOW NEED THIS"
1 My Item 100 ABC RST

If I could add a sub report to a sub report this would be achieved by linking the "ITEM NO" in sub to "ITEM NO" in OPT Table an supressing all other "OPTFIE" values?

The sub report is linked to the main report by the "MASTER ITEM NO" Field.

Hope I have given sufficient info

Thanks

 

[lbass]

Link the OPT table to the table already in the sub using a left outer join, and then create a formula {@GHIValue}:

if {OPT.OPTFIE} = "GHI" then {OPT.Value}

This will still cause multiple rows, but you can suppress the detail section with a formula like this:

{@GHIValue} = ""

-LB

 

[coolstrike23]

lbass, Thanks for the reply. I don't want to supress rows which dont have a value. I want the value if {OPTFIE} if it exists and return null if there is no value.

 

[lbass]

If you don't add the suppression formula, then you will have an empty field using the first formula I gave you.

-LB