Cleaning up Smart Quotes in FileMaker Pro v10
One of my pet hates is the use of “smart quotes” in FileMaker Pro. Smart quotes are the curly quotation marks and curly apostrophes and are usually entered in 2 ways:
1. the setting for smart quotes is enabled by default for the creation of new .fp7 database files on Mac OS X but not for Windows XP, so anytime you enter an apostrophe or a quote mark you will be using smart quotes.
2. pasting in text from Microsoft Word, which also has a preference setting for smart quotes and which is also enabled by default (you can get instructions for how to turn this off on Windows here)
Here are some examples of how they appear:
Single Left Smart Quote: ‘
Single Right Smart Quote: ’
Double Left Smart Quote: “
Double Right Smart Quote: ”
The smart quotes setting in FileMaker Pro can be found by opening a database file and going to the File menu > File Options, and click the Text tab (note that you must log in using the Full Access privilege set to edit items in the Text tab). Here you can toggle smart quotes on and off- if you enable smart quotes when it was previously off this will only affect new typing as any existing data will not be edited automatically.
For many users smart quotes are not problematic, but when it comes to exchanging data with other database platforms (e.g. SQL) and systems (particularly web based systems) their presence will often cause data exchange processes to break or for text not to be displayed correctly, which means you then have to deal with any smart quotes in your data. I was recently working on submitting FileMaker data to on online system and was required to URL encode my data. Everything was working well until one day data was being truncated for no obvious reason. After many tests I realised that some of the apostrophe and quotation characters were not being URL encoded correctly (I was using this handy custom function to URL encode the data before submitting it via HTTP POST). Once I zoomed in on the text to 400% and changed the font to a sans serif font all was clear – the text was using smart quotes which the customer has enabled as this was the default setting for a new .fp7 file on Mac OS X. I quickly turned that off which meant any new data entered directly into the fields would not be using smart quotes, but all the existing data needed to be cleaned up. I created a new custom function RemoveSmartQuotes (text) which uses one of the new functions in FileMaker Pro v10 to reference a character without having to actually type/enter the character into a calculation.
The following calculation uses the Char function to identify any instances of the single and double smart quotes/apostrophes and replace them with plain (straight) quotes and apostrophes:
Substitute (
text ;
[ Char ( 8216 ) ; “‘” ] ; // left smart quote
[ Char ( 8217 ) ; “‘” ] ; // right smart quote
[ Char ( 8220) ; “\”” ] ; // left double smart quote
[ Char ( 8221) ; “\”” ] // right double smart quote
)
I’ve dcreate a custom function that uses this calculation that you can copy/paste into your file which should appear shortly on Brian Dunning’s custom function site. This custom function can then be used in a number of ways to clean up text that cannot have any smart quotes in them, for example as an auto enter calculation on a field or in a script that transforms data. If you’re using a version of FileMaker Pro before v10 you simply need to change the use of the Char function with the actual smart quotes themselves and remember to escape the double quotes.
