NEED HELP. COMBINING TWO RECORDS FROM THE SAME FIELD 2

[ss7415]

i have a field called reason code, a record can have more then one reason code. if i want to comibine my reason codes on the same line as the record. so for example if record 111 has reason code a,d, e. Instead of 3 rows of records i want one record with a,d,e on one line

 

[ss7415]

i was told to use a WhilePrintingRecords and varible. but not sure how to do that.

 

[ss7415]

ID number reason code
111 a
111 d
111 e

this is what i need

ID number reason code
111 a,d,e

 

[ss7415]

i was told to do a WhilePrintingRecords and a variable, but not sure how to do it, please help

 

[lbass]

Create three formulas:

//{@accum} to be placed in the details section:
whileprintingrecords;
stringvar x := x + {table.reasoncode}+",";

//{@reset} to be placed in a group header for ID number (insert the group if you haven't):
whileprintingrecords;
stringvar x;
if not inrepeatedgroupheader then
x := "";

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

Then drag the group name into the group footer and suppress the group header and details section.

-LB

 

[ss7415]

MY FIELD IS A NUMBER NOT A STRING

 

[lbass]

Change this formula to:

//{@accum} to be placed in the details section:
whileprintingrecords;
stringvar x := x + totext({table.reasoncode},0,"") +",";

You must be patient. People who contribute to these forums generally have jobs and can't necessarily respond when you want them to.

-LB

 

[ss7415]

thanks bro, i totally understand

 

[ss7415]

thank you very much for your help and your tips on the forum

 

[elsenorjose]

BTW...lb's not a bro and she's more l33t then 90% of the bros here

 

[Kendel]

Here is a star for you LB. Really appreciate your good work.

 

[brendanchris]

Hi there

Lbass your solution works really well for a problem I also face, however I was wondering whether you could go a step further and combine the results of the @display field in the id group footer onto one line.

At the moment, assuming some more data, I belive it would display in the following way in the id group footer:

111 a,b,c,d
112 a,b,c
113 b,c

What I would like to know then, is how to display this on one line as follows:

111 a,b,c,d; 112 a,b,c; 113 b,c

Would this be possible.

Thanks in advance

BC

 

[lbass]

You could change {@display} to:

//{@display} to be placed in the ID group footer:
whileprintingrecords;
stringvar x;
stringvar y;
y := y + totext({table.ID},0,"") +" "+ left(x,len(x)-1)+ "; ";
left(x, len(x)-1);//used 1 since you appear to be using no spaces after commas

Then create a display formula for the report footer:

//{@displayrf}:
whileprintingrecords;
stringvar y;
left(y, len-2)

-LB

 

[brendanchris]

Thanks lbass. Worked beautifully!!

BC