CODEWITHSUNDEEP
crystal-reports
Hasin Mahjabeen
Hasin Mahjabeen

Reputation: 11

Concatenate multiple column values into one string- Crystal Repot formula

Suppose, I have this records below (have 4 fields)- 

.---------.---------.------.---------.
|  Col1   |  Col2   | Col3 |  Col4   |
:---------+---------+------+---------:
| Value A | Value B | null | Value C |
'---------'---------'------'---------'

Now I have to combine them into one string using formula in crystal report, like this-

Value A, Value B and Value C

Note: It should be ended with "and"

Upvotes: 0

Views: 2560

Answers (2)

MilletSoftware
MilletSoftware

Reputation: 4026

Use the IsNull() function to detect which columns are null and skip them. For example:

Local StringVar result;
Local StringVar connector := "";

IF Not IsNull({Col4}) Then
(
result := {Col4};
connector := " and "
);

IF Not IsNull({Col3}) Then
(
result := {col3} & connector & result;
IF connector = "" Then connector := " and "  Else connector := ", " 
);

IF Not IsNull({Col2}) Then
(
result := {col2} & connector & result;
IF connector = "" Then connector := " and "  Else connector := ", " 
);

IF Not IsNull({Col1}) Then
(
result := {col1} & connector & result;
);

Upvotes: 0

Spencer Randall
Spencer Randall

Reputation: 11

Go to your field explorer and right click on formula and click new formula.

You can then enter the equation. Your equation should look like {YourDataSource.Col1} & ", " & {YourDataSource.Col2} & " and " & {YourDataSource.Col4}. Click save and close and just drag this formula onto your report.

I am not sure what you mean by your note, but I hope this will help. Also, look at Concatenate two fields if my answer was not sufficient.

Upvotes: 1

Related Questions