"No Holding Back FileMaker Blogging"


Navigation:


Support this site by clicking on a sponsor below or becoming a patron!



$10.00 Beginner Video Training



Become a patron of this FREE web site!


Recent Blogs:

Window Locking
Window Locking

Everything Changes
Everything Changes

Subsummary Unique Count
Subsummary Unique Count

Scripted Change Log
Scripted Change Log

Abstracted Log
Abstracted Log


Meta-Consulting
Meta-consulting is a service Database Pros offers to help other developers over programming hurdles via GoToMeeting. Email John Mark Osborne or call John Mark at (909) 393-4664 to find out more about this service.


The Philosophy of FileMaker recommends PCI!


Quick Tip:

Toggling Parts
While in layout mode, clicking on a Part with the Control key (Windows) or Command/Apple key (Macintosh) held down will toggle between their vertical and horizontal positions. This is sometimes faster than using the toggle control in the lower left corner of the window.



Beginner, Intermediate and Advanced Video Training


Fun Stuff:

Hall of Fame
I started working with FileMaker 25 years ago and a lot has changed. Here are the folks I think need to be recognized for their effort in shaping the early FileMaker (in no particular order):



Create a FileMaker Calendar







RSS Feed
Removing Text Formatting
Level: Beginner
Version: FileMaker 19
Category: Calculations
Tuesday, November 3, 2020
Almost every single solution I develop requires a method for dealing with text in fields that is purposely or accidentally formatted in browse mode. For better or worse, FileMaker allows users to change text formatting in browse mode via the Format menu. FileMaker also doesn't automatically remove formatting that's been pasted or drug from another application like a web browser. This leads to all kinds of issues with displaying field text on multiple layouts as well as output. This article will cover a variety of methods for discouraging, preventing and fixing text formatting issues in fields.

Removing Text Formatting

BTW: From my days in technical support, the reason FileMaker allows text formatting in browse is due to a large customer that wants the feature. At least that is what I was told by agents who had been there for years before I joined.

One of Many Examples
How can formatting text in browse mode cause a problem? Imagine you have a label printing program and folks are grabbing data from various sources like emails, web browsers and other third party applications. Through copy and paste or drag and drop, users populate the records and then print contact lists. If the text is formatted, FileMaker won't remove it. That means the formatting in browse mode will override any field formatting options specified in layout mode. The result is mismatched text on print out, making your output look very unprofessional, if not unreadable.

Removing Text Formatting

Removing Text Formatting

On the upside, formatting text in browse mode allows for cool features like form letters in browse mode. This technique uses a global field and the options under the Format menu to allow users to construct letters in browse mode and save them. Having the ability to bold, italicize or even change the color and size of the font do have a place in output from FileMaker, just not most of the time.

What this means for the developer is they have to consider how users will interact with their system and the potential side effects. With Custom Menus, it's pretty easy to remove access to the Format menu so users don't knowingly change the text formatting. That just leaves the more difficult problem of how to deal with text coming in from other sources other than the keyboard.

Paste Without Style
I call this feature "Paste without Style" because of the option on the Paste script step. It's actually referred to as "Paste Text Only" when performed manually from the Edit menu. What this feature does is allow you to remove text formatting before pasting. It doesn't help with drag and drop but most people are more comfortable with copy and paste.

Removing Text Formatting

For me, it's hard to teach users to use this feature. They already have the keyboard commands for copy and paste ingrained in their mind. Trying to get them to add the Option and Shift keys (Macintosh) or Shift key (Windows) to the already familiar Command-V (Macintosh) and Ctrl-V (Windows) is an uphill battle that will never be won. I've toyed with replacing the paste menu item with a script that only pastes plain text but I'm fearful it will cause trouble when users actually need to keep the formatting.

Keyboard equivalents to paste plain text for Macintosh and Windows:

Option-Shift-Command-V

Shift-Ctrl-V


FYI: Some versions of FileMaker on Windows may not have the menu item for Paste Text Only but the keyboard equivalent works on all versions.

While you could create a button with a script to paste without style, that requires a button next to every potential field. That's a lot of work and will clutter up the interface as well as the Script Workspace if any steps in addition to paste are required. In my experience, it's not just one or two fields but most of the fields on the layout that need stylized text blocked.

Expecting users to always remember to manually paste text without style or click your button to paste is really asking too much. Users just want to get their job done so they are bound to stick with the good old copy and paste they learned decades ago. The result is a database solution with inconsistent text formatting which leads to customer dissatisfaction. I don't blame customers pinning the issue on the developer. It's his job to make sure the system works smoothly even when the users won't follow instructions.

TextFormatRemove to the Rescue
This is my favorite way to remove text formatting from a field. It's straightforward and easy to implement, taking just a minute to apply to dozens of fields. It's also schema level in Manage Database so there's no need to apply it to every instance of a field on every layout, as would occur with any scripted solution. The solution is an Auto-Enter Calculation with the TextFormatRemove function.

The formula I use is:

TextFormatRemove(Self)

I use the Self function to make it adaptive. In other words, I can copy and paste this formula anywhere in Manage Database and it will adapt to the current field. It's also necessary to uncheck the option to "do not replace value in field (if any)" or the auto-enter will never happen.

Removing Text Formatting

FYI: The Auto-Enter Calculation option to "do not replace value in field (if any)" allows a calculation to reevaluate whenever any of the fields referenced in the formula are modified. If the option is left checked, the auto-enter will only occur when the field is empty.

For Fun
The TextFormatRemove function was introduced in FileMaker 8.0. Prior to it's advent, I used some crazy auto-enter calculation formulas to perform the same action as the TextFormatRemove function. Here's one I saw from a file I published back in the FileMaker 7.0 days:

TextSize(TextFont(TextColor(TextStyleAdd(text3; 0); 0); 0); 0)

/* Setting the TextStyle, TextColor and TextFont to 0 changes the text to the default so it obeys layout settings. Unfortunately, TextSize does not reset with a value of 0. Also, TextFont resets to a generic font, not necessarily the font attached to the field. */


Take note of the comment following the formula indicating limitations. Yikes! Thanks goodness there was a better solution when I discovered the Evaluate function would remove formatting:

Evaluate(Quote(text1))

/* The Evaluate function does most of the work of removing font, size and style but doesn't work with quotes unless the Quote function is employed. */


Again, pay attention to the comment at the end. Not really a limitation but certainly something that could catch you off guard.

Prior to FileMaker 7.0, all text formatting was removed when a field was calculated which made it easy to remove text formatting from a field by simply feeding it through a calculation formula. Unfortunately, the Auto-Enter Calculation option to "do not replace value in field (if any)" didn't appear till FileMaker 7.0 so you had to use a script with a Set Field to fix the input.

FYI: When FileMaker, Inc. made a decision to change the FileMaker 7 behavior to have text formatting retained when fed through a calculation formula, they also needed introduce formatting functions like TextFormatRemove.

Fixing Existing Data
You may not discover users are formatting data, whether it's purposely or accidental, until it's too late. If you have more than one field on more than one record that you need to fix, my first suggestion would be to employ the Replace Field Contents feature along with the TextFormatRemove function. The only difference is you can't use the Self function since you are outside Manage Database. All you have to do is match the target field to the field in the TextFormatRemove parameter:

TextFormatRemove(CONTACTS::name_first)

Removing Text Formatting

You can copy and paste the formula from one Replace Field Contents implementation to the next but you'll need to change the referenced field inside the TextFormatRemove function.

If your FileMaker solution is overridden with formatted text, you can consider exporting the data to a format like tab-separated text and then importing back into the file. This can get a bit tricky if your solution contains container fields, since they can't be exported to a text file. In this situation, you'll need to import with the Update option.

Removing Text Formatting

Also, pay attention to the auto-enter options or you may get all new serial numbers and break your relationships.

Your Responsibility
It's your responsibility as a developer to understand how the users of your solution will be using it. Ask the right questions and if they say they will be gathering data from other applications, make sure to setup this simple guard against stylized text. It really does only take a few minutes.

Author:
John Mark Osborne
jmo@filemakerpros.com
www.databasepros.com

Example File (available to patrons at $5.00 per month): Download here if you are a patron

This blog is completely free. Please support it by clicking on one of the advertisers at the left side of the window or becoming a patron. Thanks so much!

Comments:

Bernard Miserez 01/22/2022
  Very useful article - I have to import data from Excel-files. Also I import some data from a search-website (www.search.ch) with copy and paste (I don't have a API license and I don't have the knowledge for implementing this API...). Thank you very much from Switzerland, Bernard (intermediate user)
Luc Thomaere 04/29/2021
  Using "Undo" (ctrl-Z on Win and cmd-Z on MacOS) immediately after ctrl-V (normal paste) also removes the style in the pasted text.

It is the second ctrl-Z/cmd-Z that will remove the pasted text itself
Response by:   John Mark Osborne 05/07/2021
Yes! Good point. Thanks for sharing.
Joao Coutinho 11/04/2020
  Great Article.

I create Custom Functions to combine multiple clean ups in formatting, spacing and special characters.

Add Comment:

First Name: *
Last Name:
Email: *
Web Site:
Comment: *
 Email Addresses will not be shared on the web site!