Printing excluding null detail record 1

[CalgaryCR9]

I am using Crystal Reports 9 on an Oracle DB with SQL.

Crystal Report Group is equal to SERVICE_CALL_ID - this is the only group in my report.

Table SERVICE_CALL.SERVICE_CALL_ID is linked to table SERVICE_CALL_XREF.SERVICE_CALL_ID.

I am pulling a field from the "SERVICE_CALL_XREF" table called "XREF_ID" only when the associated "XREF_LABEL" is equal to "Part #". See formula {@ Part#}:

if {SERVICE_CALL_XREF.XREF_LABEL}="Part #"
then {SERVICE_CALL_XREF.XREF_ID}

If the SERVICE_CALL_ID have an SERVICE_CALL_XREF.XREF_ID with a SERVICE_CALL_XREF.XREF_LABEL not equal to "Part #", a detail record prints for that instance with a null entry for my {@Part#} formula.

What I want to achieve is a Group Footer formula that lists all part numbers shown by the {@Part#} formula, comma separated, excluding the null entry.

Sample Data:
Group Service Call ID record is 8500
XREF Label = Cisco with a corresponding XREF ID = 8000000
XREF Label = Part# with a corresponding XREF ID = 350-88888
XREF Label = Part# with a corresponding XREF ID = 5990000

I want the summary in the group footer to read "350-88888, 5990000"

I am stuck at how to write such a formula.

 

[lbass]

Create three formulas:

//{@reset} to be placed in the group header:
whileprintingrecords;
stringvar x;
if not inrepeatedgroupheader then
x := "";

//{@accum} to be placed in the detail section:
whileprintingrecords;
stringvar x;

if {SERVICE_CALL_XREF.XREF_LABEL}= "Part #" and
instr(x, {SERVICE_CALL_XREF.XREF_ID}) = 0 then
x := x + {SERVICE_CALL_XREF.XREF_ID} + ", ";

//{@display} to be placed in the group footer:
whileprintingrecords;
stringvar x;
left(x,len(x)-2);

This assumes you only want an xref_ID to appear once in the list.

-LB

 

[CalgaryCR9]

Thanks LB. This worked perfectly.