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

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
Asked:
amedexitt
1 Solution
 
natlozCommented:
What is the formula you are using so far?
0
 
amedexittAuthor Commented:
field1 & ', '& filed2& ', '& field3& ', '& field4
0
 
Ido MilletProfessor 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
 
Ido MilletProfessor 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
 
mlmccCommented:
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

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

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