Combining address fields with commas (not if empty)

[VirtualBob]

I want to combine multiple address fields into one field to copy and paste. It has fields like;

 

Address line 1: Unit 5

Address Line 2: Chess Building

Address line 3: 23 Cherry Tree Lane

Address line 4: Home Town

 

I want the result to look like Unit 5, Chess Building, 23 Cherry Tree Lane, Home Town.

 

So I've tried using Address line 1 & "," & Address Line 2 & "," & etc

 

But some fields don't have data, so if the field is empty I get two commas ,,

 

Is there are workaround to stop this or is there some way of nesting a function that strips the ,, or ,,, etc occurrences if they exist?

[LaRetta]

You can try calculation (result is text) =

Substitute ( List ( Address Line 1 ; Address Line 2 ; Address Line 3 ; Address Line 4 ) ; ¶ ;  ", " )

Added:  List() will drop fields without a value so when we substitute comma/space for carriage return, the comma appears as desired.  But also, depending upon its purpose, you might not need a calculation field at all.  If only for display on a layout or report, you could use merge variable. 

[VirtualBob]

That's just the ticket. Thanks

[kevybaby]

I too thank you LaRetta - you helped solve a pickle I was having. If I may, I wanted to add another layer to LaRetta's solution.

 

I had some fields that were actually checkboxes and the field names (from a predecessor: it's always fun to blame the last guy) wouldn't be what I wanted to populate. Also, in some cases, I needed more than what could be incorporated into a field name.

 

For example, there is a checkbox field for finishingTrim and if checked, there would be a numeric value entered in the finishingTrimSize field. The function for this one item would look like:

If( finishingTrim = "" ; "" ; "Trim to " & finishingTrimSize)

This would then populate as Trim to 8-1/2" x 11"

 

I would then put the entire function in (brackets) and substitute it for (as example) for "address1" in LaRetta's formula. However, this then introduced an issue where I was getting multiple commas again. This is fixed by using multiple substitute parameters. From the FMP description (http://www.filemaker.com/11help/html/func_ref3.33.84.html#1031764):

Substitute(text; [search1; replace1]; [search2; replace2]; ... [searchN; replaceN])

Make sure to use the square brackets!

 

So, for me, the final code  was:

Substitute ( List ( (If( finishingTrim = "" ; "" ; "Trim to " & finishingTrimSize)) ; (If formula 2) ; (If formula 3) ; (If formula 1) ) ; [¶ ;  ", "]; [", , "; ", " ])