calc field with commas

[pc302]

Good Day to all

Creating a web store data management input screen. to select product categories on the export the result is a calculated field with commas in them

the field categoryids equals the fields CatagoryID1 CatagoryID2 CatagoryID3

the first attempt:

CatagoryID1 & "," & CatagoryID2 & "," & CatagoryID3

gives me

21,,

I need the verbiage for if it is empty don't add the commas.

[LaRetta]

Here is one way ...

Let (

concat = Category1 & " " & Category2 & " " & Category3 ;

Substitute ( TrimAll ( concat ; 1 ; 0 ) ; " " ; [color:red]"," )

)

I notice you don't have spaces as 22, 34, 27 but rather 22,34,27. If you decide you want the space after the comma, just add it after the comma in red.

UPDATE: Using Let() here isn't required but 1) it makes it easier to read what is happening and 2) it will be easier if you wish to add fields to the concat.

Edited October 14, 2009 by Guest
Added update

[Lee Smith]

Hi LaRetta,

I like your calculation better than the one I use;

Hi pc302, and Welcome to the Forum,

Here is another way,

categoryids (calc, Text result) =

Case(not IsEmpty(CatagoryID1); CatagoryID1 [color:red]& ", "; CatagoryID1) &

Case(not IsEmpty(CatagoryID2); CatagoryID2 [color:red]& ", "; CatagoryID2) &

Case(not IsEmpty(CatagoryID3); CatagoryID3 [color:red]; CatagoryID3)

[color:blue]Note the change in the calculation for the CatagoryID3.

I only posted this so that the new users can see a different way.

Lee

[pc302]

Thanks for the help both of you. Both answers were very creative.

Take Care

[bruceR]

Substitute( List ( CatagoryID1; CatagoryID2 ; CatagoryID3); "¶"; ", ")