Removing a field if no text is there.

[ChangeAgent]

I have a database (FMP 6) where I pull a number of fields together to get a layout for an envelope. I do this through a defined calculation field.

The programming is:

Title & "¶" & First Name & " " & Last Name & "¶" & Company & "¶" & Street 1 & "¶" & "¶" & Postal Code 1 & " " & City 1 & "¶" & Country 1

Now if there is no company I have a gap between the texts. Is there a way of avoiding this? Like having the text move up one line?

Thanks

[bcooney]

Title & "¶" & First Name & " " & Last Name & "¶" &

if ( not isempty (Company), Company & "¶") &

Street 1 & "¶" & "¶" & Postal Code 1 & " " & City 1 & "¶" & Country 1

[ChangeAgent]

Thanks Barbara!

[ChangeAgent]

Barbara I did that and it does not work. here is what I did:

Title & "¶" & First Name & " " & Last Name & "¶" & if ( not isempty (Company), Company & "¶" & Street 1 & "¶" & "¶" & Postal Code 1 & " " & City 1 & "¶" & Country 1

message I get is: 'There are too few separators in this function'

could this have to do with FMP 6?

[Vaughan]

"could this have to do with FMP 6?"

No it's because there are too few separators in the function. It's missing the closing ")" before " & Street 1 ....".

Copy and paste Barbara's function from the web page. What she has posted is correct.

[Raybaudi]

Hi

you forgot a ")" !!

Title & "¶" & First Name & " " & Last Name & "¶" & if ( not isempty (Company), Company & "¶" [color:red]) & Street 1 & "¶" & "¶" & Postal Code 1 & " " & City 1 & "¶" & Country 1

[ChangeAgent]

OK, raybaudi and Vaughan

I have tried both your suggestions and neuutehr one works!

I still get the message: 'There are too few separators in this function'

So I solved it as follows with a new formula:

Case( not IsEmpty( Title ) , Title ) & " " &

Case( not IsEmpty( First Name ) , First Name ) & " " &

Case( not IsEmpty( Last Name ) , Last Name & "¶" ) &

Case( not IsEmpty( Company ) , Company & "¶" ) &

Case( not IsEmpty( Street 1 ) , Street 1 & "¶" ) &

Case( not IsEmpty( Postal Code 1 ) , Postal Code 1 & "¶" ) &

Case( not IsEmpty( City 1 ) , City 1 & "¶" ) &

Case( not IsEmpty( State Province 1 ) , State Province 1 & "¶" ) &

Case( not IsEmpty( Country 1 ) , Country 1 & "¶" )

Thanks for your help!

[Vaughan]

Sorry to say, but "Case( not IsEmpty( Title ) , Title )" is redundant because you are saying "if the field is not empty display it otherwise do not display it". It's redundant because if it's empty there is nothing to display.

Instead of changing the calculation, try to work out what the problem is. You'll learn more.

[ChangeAgent]

Thanks for that tip Vaughan, good thinking and I see what you mean.

as I was unable to find what was wrong in my original code I be interested if you can spot it. I am all for learning! so why does

Title & "¶" & First Name & " " & Last Name & "¶" &

if ( not isempty (Company), Company & "¶") &

Street 1 & "¶" & "¶" & Postal Code 1 & " " & City 1 & "¶" & Country 1

not work?

I get:

'There are too few separators in this function'

while people here say it is correct.

i am puzzled...

[comment]

In version 6 and below, the syntax for the If() function is:

If (test, result one, result two)

The 'result two' parameter is NOT optional (as it is in later versions). Therefore you must add an empty result, or - preferably - use the Case() function instead:

Title & "¶" &

First Name & " " & Last Name & "¶" &

Case( not IsEmpty(Company), Company & "¶") &

Street 1 & "¶¶" &

Postal Code 1 & " " & City 1 & "¶" &

Country 1

[bcooney]

My apologies, I didn't see you had FM6.

[ChangeAgent]

no worries, I know it is ancient -)) just do not have the money to get a higher version.