How Do You Concatinate Duplicated Fields?

[wildsharkuk]

Hi,

I want to concatenate 4 fields together (easily done) but some of them have the same data so want to exclude duplicated data but concatinate all the rest - See Example below.

Field1 Field2 Field3 Field4 Concatinate Wanted
134 135 156 134/135//156 134/135/156
135 135 123 135/135/123/ 135/123
101 102 101 102 101/102/101/102 101/102
195 145 195 /195/145/195 195/145

I am using CR10.

Any help would be appreciated.

Thanks

Wildsharkuk

 

[Madawc]

I use Crystal 8.5. But I'd do it as four formula fields, the first saying

if isnull({Field1}) or {Field1} = " " then ""
else {Field1}

the next

if isnull({Field2}) or {Field2} = " " then ""
else "/" & {Field2}

and likewise 3 and 4. Use a fifth formula field to lump them, or put them all in a text box.

Madawc Williams (East Anglia)

 

[Madawc]

Sorry, I didn't read carefully enough. You'd also need to test for previous duplicated. THis could be done as code, as as 'boolians', say Dup2

{Field2} = {Field1}

and do not show Field2 if this is true. 3 would check 2 and 1, 4 would check 3 and 2 and 1.

Madawc Williams (East Anglia)

 

[goranm]

Hi !

This is a solution that almost is as Madawc suggested:

Create a formula:

Stringvar Concat;

if (isNull({Field1}) or {Field1} = '' )then
(
if not isNull({Field2}) then
Concat := {Field2}
else
if not isNull({Field3}) then
Concat := {Field3}
else
if not isNull({Field4}) then
Concat := {Field4}
)
else
Concat := {Field1};

if (not isNull({Field2}) and {Field2} <> '') then
(
if not(InStr (Concat,{Field2} ) <> 0) then
Concat := Concat + '/' + {Field2}
);

if (not isNull({Field3}) and {Field3} <> '') then
(
if not(InStr (Concat,{Field3} ) <> 0) then
Concat := Concat + '/' + {Field3}
);

if (not isNull({Field4}) and {Field4} <> '') then
(
if not(InStr (Concat,{Field4} ) <> 0) then
Concat := Concat + '/' + {Field4}
);

Concat;

Hope it will help you.

/Goran

 

[BobSuruncle]

You could use an array to accumulate the strings and then use the JOIN command to concatenate.

Formula to add to array:

stringvar array n ;
numbervar x ;
if (Not IsNull({Customer.Customer Name}) or Trim({Customer.Customer Name}) <> "" )
    and  Not({Customer.Customer Name} in n) then 
(
x := x + 1;
redim preserve n[x];
n[x] := {Customer.Customer Name};
)

and then, a formula to display

WhileReadingRecords;
stringvar array n ;
join (n,"/")

Bob Suruncle

 

[wildsharkuk]

Thanks Guys,

I'll take a look at these this afternoon + finish the report.

Thanks again.

Wildsharkuk