Remove trailing commas from string 1

[elsenorjose]

Using CRXI Professional on Windows XP with MS SQL Server 2005 backend.

I have a column which returns 1 of 2 values if a project has a purchase order or not. If it has 1 or more POs (up to 4 total) it displays like this:

Has PO?
Yes (3054186, , , )

If there is no PO, it displays:
No

The field is a CASE and concatenation:

Case When ofd.proj_num is null then
     'No'
     else
	 'Yes'+' '+ '('+ ofd.po_num_1 +', ' + ofd.po_num_2+ ofd.po_num_3 +', ' + + ofd.po_num_4 +', ' + ')'
End 'Has PO?'

Ugly database design, but it's what I have to work with.

Basically, what I need help with is trimming any trailing commas. If there is 1 PO, I'll end up with 3 trailing commas; if there are 2 POs, I'll end up with 2 commas, etc.

How can I write a formula to strip only trailing commas but not remove commas if they are really separating different POs?

Thanks

 

[dunlop1975]

Someone will for sure have a more elegant answer:

local stringvar OUTPUT;

if not(isnull(ofd.po_num_1)) then OUTPUT := ofd.po_num_1;
if not(isnull(ofd.po_num_3)) then OUTPUT := OUTPUT & "," & ofd.po_num_3;
if not(isnull(ofd.po_num_4)) then OUTPUT := OUTPUT & "," & ofd.po_num_4;

Case When ofd.proj_num is null then
'No'
else
'Yes'+' '+ OUTPUT
End 'Has PO?'

 

[dunlop1975]

sorry forgot about the brackets:

local stringvar OUTPUT;

if not(isnull(ofd.po_num_1)) then OUTPUT := ofd.po_num_1;
if not(isnull(ofd.po_num_3)) then OUTPUT := OUTPUT & "," & ofd.po_num_3;
if not(isnull(ofd.po_num_4)) then OUTPUT := OUTPUT & "," & ofd.po_num_4;

Case When ofd.proj_num is null then
'No'
else
'Yes'+' '+ '(' + OUTPUT + ')'
End 'Has PO?'

 

[elsenorjose]

Sorry dunlop, the CASE statement I posted is the CASE in the database; all I have to work with in the report is the field {HasPO?} which can have either 'No' or a string similar to 'Yes (3054186, , , )'

So what I'm trying to do is just trim commas from the field in CR if they're just trailing and not actually separating different PO numbers.

Thanks and sorry for the confusion.

 

[dgillz]

Have you looked at the replace() function?

Software Sales, Training, Implementation and Support for Macola, Synergy, and Crystal Reports. Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[lbass]

stringvar x := {table.string};
numbervar i;
numbervar j := len(x);
stringvar y;
stringvar array z := "";
for i := 1 to j do(
if x = "," and
(
x[i+2] = "," or
x[i+2] = ")"
) then
y := y + "" else
y := y + trim(x)
);
z := split(y,"(");
z[1]+" ("+ replace(z[2],",",", ")

-LB

 

[elsenorjose]

Thanks LB. Worked perfectly, as always.