• Status: Solved
• Priority: Medium
• Security: Public
• Views: 674

Crystal XI formula

I want to concatenate 10 field in Crystal XI, some time some of the field are null and I want to separate they by commas or any thing else, My issue is that I'm getting the following result

Name,lastname,,,,,Country

I want to get rid of the commas on the middle; I want the commas only if there are two consecutive fields.

Thank you in advance

0
amedexitt
1 Solution

Commented:
What is the formula you are using so far?
0

Author Commented:
field1 & ', '& filed2& ', '& field3& ', '& field4
0

Professor of MIS at Penn State Erie and Owner, Millet SoftwareCommented:
IIF(IsNull(field1) OR field1="", "" , "," & field1) &
IIF(IsNull(field2) OR field2="", "" , "," & field2) &
IIF(IsNull(field3) OR field3="", "" , "," & field3) & ...
0

Professor of MIS at Penn State Erie and Owner, Millet SoftwareCommented:
Actually, assuming the first field is always not empty, you should use:

field1 &
IIF(IsNull(field2) OR field2="", "" , "," & field2) &
IIF(IsNull(field3) OR field3="", "" , "," & field3) & ...

Otherwise the formula has to deal with a possible comma at the start of the result.
0

Commented:
Another idea

Local StringVar strMyField := "";
If (Not IsNull({Field1})) then
strMyField := {Field1};
If (Not IsNull({Field2})) then
strMyField := strMyField & ", " & {Field2};
If (Not IsNull({Field3})) then
strMyField := strMyField & ", " & {Field3};
If (Not IsNull({Field4})) then
strMyField := strMyField & ", " & {Field4};
If (Not IsNull({Field5})) then
strMyField := strMyField & ", " & {Field5};
If (Not IsNull({Field6})) then
strMyField := strMyField & ", " & {Field6};
If (Not IsNull({Field7})) then
strMyField := strMyField & ", " & {Field7};
If (Not IsNull({Field8})) then
strMyField := strMyField & ", " & {Field8};
If (Not IsNull({Field9})) then
strMyField := strMyField & ", " & {Field9};
If (Not IsNull({Field10})) then
strMyField := strMyField & ", " & {Field10};

strMyField

mlmcc
0

Featured Post

Tackle projects and never again get stuck behind a technical roadblock.