Merge field separators

[benwiggy]

I'm using merge fields to display a series of data in one line.

Ideally, I would like to separate each field with a comma. However, when there is no data in a given record, I don't want a bunch of ",,,,,,,".

Even using spaces can look a bit odd, if there are five spaces before the first item in one record.

I'm hoping there's an easy fix?

Many thanks

[efen]

Create a calculation field [result text] to use as the merge field comprising all the fields that you want to show. Make this calculation on the lines of:

pseudo code

If(not IsEmpty (field1); field1 &",";"") & If(not IsEmpty (field2); field2 &",";"") etc

[Raybaudi]

This is simpler:

Substitute ( List ( Field1 ; Field2 ;... ; FieldN ) ; ¶ ; ", " )

[benwiggy]

This is simpler:

Substitute ( List ( Field1 ; Field2 ;... ; FieldN ) ; ¶ ; ", " )

As I understand it, this searches for a carriage return (empty field) within a list of fields and replaces it with a comma? Isn't that the opposite of what I want?

Create a calculation field [result text] to use as the merge field

So create a field with a calculation (that produces the list of fields with commas), and then stick it in a Merge Field?

Sorry if I'm being dense.

[Raybaudi]

No, that caculation will do what you asked for.

Ideally, I would like to separate each field with a comma.

Try to believe

( each single field must contain no return )

[benwiggy]

Thanks for that.

I have to say, that I have seen loads of examples of fomulae with the ¶ operator, and I just haven't a clue how it seems to be used. I'm sure I'll get there in the end.

[Raybaudi]

The ¶ operator solves infact many parsing problems.

BTW: if Field1 contains 123 and Field2 contains 345, List ( Field1 ; Field2 ) will contain:

123

345

IOW: 123¶345

Now, substituting the ¶ with ", " will return: 123, 345

[benwiggy]

Eureka!

Many thanks for that, this is going to be very helpful.