Removing unnecessary carriage returns and spaces in field data

[Beloved Marketing]

I am having problems with sending e-mail through FMP and I think I discovered the problem. Some fields have unnecessary spaces or carriage returns which translates into some ASCII boxes that Outlook doesn't like. As a result, the e-mails don't go out.

Can anyone help me with a way that I can cure this problem without having to check each record one by one? Can I define the field so that these carriage returns or spaces are eliminated also ensuring that it doesn't happen during data entry?

Thanks in advance for any help on this.

[faaslave]

You can use the trim function

make a copy of your database first before trying this

Show all the records

click in the field you want

select the records menu

select the replace field contents

click on calculated result

type in:

Trim (fieldname)

click ok

click replace

That should fix it

Or you can make it happen automatically in the field options

select the file menu

define-->database

find the field

highlight the field and click on options

click on calculated value

then put in this:

Trim (fieldname)

that should do it anytime you enter in extra spaces

Dave

[Orlando]

I do something similar to this using a Recursive Custom Function as an Auto Enter Calculated Value.

You will need FileMaker Advanced to create a Custom Function and set it up like this:

Function Name:

CF_Remove_Space_Carriage





Parameters:

text





Calculation:

Let( [ Format = Trim ( If ( Right ( text ; 1 ) = "¶" ; Replace ( text ; Length ( text ) ; 1 ; "" ) ; text ) )



];



If ( Right ( Format ; 1 ) = "¶" ; CF_Remove_Space_Carriage ( Format ); Format ) )

And then in your text fields options specify Calculated Value under auto enter and use this custom function calling your text field.



However if you do not have FileMaker Pro Advanced then you will need to set up a calculation field to do roughly the same thing and then use the calculation as your email.

Name:

TextReformat



Calculation:

Let( [ Format = Trim ( If ( Right ( Address ; 1 ) = "¶" ; Replace ( Address ; Length ( Address ) ; 1 ; "" ) ; Address ) )



];



If ( Right ( Format ; 1 ) = "¶" ;TextReformat ; Format ) )

I hope this makes sence and helps.

Orlando