Handling Blank Fields in Merge Field Bulleted List

[Evan Luke]

I have 5 different "fact" fields that I am outputting in a merge field.

The challenge I have is when one of the fact fields is blank, I get a orphaned bullet point with no content.

Is there a way to build conditionality into the merge field so that bullets are omitted if the following field is blank?

[hbrendel]

Make a calc field with the formula:

Case ( Trim ( FieldA ) = "" ; "" ; "• " & FieldA ) 

& "¶" & 

Case ( Trim ( FieldB ) = "" ; "" ; "• " & FieldB ) 

& "¶" & 

Case ( Trim ( FieldC ) = "" ; "" ; "• " & FieldC )

& "¶" & 

Case ( Trim ( FieldD ) = "" ; "" ; "• " & FieldD )

& "¶" & 

Case ( Trim ( FieldE ) = "" ; "" ; "• " & FieldE )

The result will be a list without 'empty bullets', but with 'empty returns'. You can strip these out with the custom function RemoveEmptyValues

If you don't want to use a custom function you might try to use the Substitute function to get rid of the superfluous returns.

Success,

Henk

[LaRetta]

Or you can use:

"• " & Substitute ( List ( fieldA ; fieldB ; fieldC ; fieldD ; fieldE ) ; ¶ ; "¶• " )

... which eliminates the blank lines if any of the fields are blank. It's just another approach. You might also consider placing it inside a web viewer which would eliminate the calculation field alltogether.

Oh Henk! You modified your post! Ah well, I've leave mine anyway.

Oh! And Evan? Welcome to FM Forums!! :laugh2:

Edited May 4, 2009 by Guest
Added second Oh!

[hbrendel]

Oh Henk! You modified your post! Ah well, I've leave mine anyway.

??

BTW LaRetta: Nice formula (using the list function) ...

Edited May 4, 2009 by Guest
Added credit to LaRetta