Re-format Text

[Oldfogey]

Does any genius out there have any idea how to solve this?

I have a database in which all names (people) have been entered with Caps Lock on. The result is far from satisfactory. I have tried just about everything to get rid of the capitals. I have succeeded in apparently getting the base fields back to title case. However, if I use them in a List, the original caps appear. In some case, the data has been entered in bold; this does not show up in the List but it does show up when the field is used in a related table. Furthermore, in the latter case, I have a report in which the surname field is formatted as bold. The only ones that show as bold are the ones that were originally in bold.

In short, it looks as though the base field has hung onto the original format, as entered and so, ignores the layout format.

By the way, I 'converted' the fields to title case by using an export/update-import which, I read here somewhere, should get rid of all formatting. It sort of did and after a lot more messing about things looked OK.

Re-keying the data works but I don't think my customer would be too happy about that.

Ideas please?

[mz123]

For capitalization changes...

Proper( field) will capitalize the first letter of every word

Upper(field) will make all letters uppercase

and

Lower( field ) will make all letters lowercase

For formatting, you can use the TextFormatRemove ( field ) to remove the formatting.

I suggest creating a field called "Unformatted Text" and setting up the calculation as "TextFormatRemove( fieldname )".

[color:red]

NOTE!!! BACKUP THE DATABASE. I'm recommending a data replace (same data, no format.... but things can go wrong...) so save a copy in case the Replace step doesn't work out as follows...

Then add that field to the layout and make sure that it looks right. If it does, copy the calculation. In browse mode, click on the field that is formatted incorrectly and go to menu Records > Replace Field Contents...

Paste the calculation and hit Replace.

That should work for all your fields as long as the function has the correct field name in it. I hope that helps! Let me know if you need a better explanation or whatnot!

[Lee Smith]

Hi Paul,

As Martha has suggested, you can change the Caps to Word cap using the Proper Function.

Since the data already exists in the field, you can replace it using the Script Step Set Field.

Set Field [ YourTO::YourField; Proper ( YourTO::YourField ) ]

After the script has been run, you can then use a Auto Enter Calculation on the field.

HTH

Lee

[Oldfogey]

Thanks, folks.

Martha, I have tried every text function I can think of, including setting it to Plain, Removing UCase, etc. They all (well, mostly) seem to work and then up comes this strange behaviour when I use the fields elsewhere.

I had forgotten about Proper (not my thing really!). I'll try it.

Thanks again.

[Genx]

Hi Paul,

Just to generally clear this up:

Edit: I seem to have been recalling my text Case dilemmas -- I apologize, should've tested my assumption first.

Read comment's post

||

||

||

||

||

/

Edited March 13, 2007 by Guest

[comment]

I don't think that's an accurate description.

The DATA in a text field can be either PLAIN TEXT, or STYLED TEXT. The text can become styled by applying one of the Text Formatting functions, or by user applying a style manually in Browse mode - for example by selecting a word and changing its color. You can also paste pre-styled text into a field.

Styling becomes part of the DATA, and it overrides any formatting attached to a particular field on a particular layout.

Layout formatting (the kind you do in Layout mode) does NOT modify the DATA in any way. And it only works on PLAIN text.

An interesting point: the result of a calculation =

TextStyleAdd ( Text ; Plain )

is STYLED text, i.e. a text with a "Plain" styling attribute. This too will override any formatting set in Layout mode.

Now, if the data was entered with Caps Lock on, then the problem is neither styling not formatting. Although the data could be styled or formatted to hide the problem, it will still be there and surface for example when exporting as text.

The correct solution is to modify the data by using one or more of the Text functions: Proper(), Lower() and Upper().

[Oldfogey]

Comment, a very nice succinct explanation. That was my understanding (?) more or less. Although I thought that formatting the data as it was entered also Styled it.

Anyway, I tried Proper() and yippee! BUT there is one record still causing trouble - there could be more, I'm not about to got through the lot. I've seen enough to know that most of the problem has gone away.

I use a related reference to display the name on a report. The surname is formatted on the layout as Bold, Underscored. The problem record displays as Bold Underscored; none of the others do! Anyway, I'm happy with what I've got so far and will no longer be losing sleep over it.

I'm going to start a 'Ban the Caps Lock Key' movement.

I just realised that this database has been copied from PC to Mac to PC .... I wonder?

Thanks again, you lot.

[HALBURN]

What function would you use for converting text strings formatted like these?

department of health >>> Department of Health

chris o'donnell >>> Chris O'Donnell

joe McClain >>> Joe McClain