Improperly formatted data is one of the scourges of database housekeeping. For a mature system, it’s like cleaning out the pantry. You don’t have to do it every week, but you will be faced with it a few times each year. When migrating information from an old system to a new FileMaker database, the volume of malformed data can approach plague of locust proportions. This week we’ll look at a triad of functions that can help with one kind of formatting headache: incorrect capitalization.
Some of the most common ways to abuse capitalization include leaving the first letter of a proper noun lower case, trying to capitalize email addresses, or invoking the dreaded ALL CAPS ALL THE TIME! To correct these infractions, FileMaker offers three case correcting functions. Lower, Upper, and Proper.
Lower ( “MyCleverEmail@SomeDomain.com” ) = mycleveremail@somedomain.com
Email addresses aren’t case-sensitive, which is to say that emails sent to jill@email.com, Jill@Email.com, and jIlL@eMaIl.CoM will all get to the same inbox. The informal standard for emails, however, is to write them in lowercase. The quick and simple way to make that happen is to run that address through the Lower function. It will demote all the upper case characters to their lower case version.
Upper ( “Nasa” ) = NASA
The obvious inverse to Lower, is Upper. This is something you can bring to bear on an acronym of lower or mixed case. That’s about the only time I encounter text that should be made upper case. Netiquette has taught us that regular words and sentences in upper case are considered to be shouted and generally impolite. While a database isn’t necessarily subject to internet norms, uppercase should be used sparingly.
Proper ( “john jacob jingleheimer schmitt” ) = John Jacob Jingleheimer Schmitt
When it comes to correcting proper nouns, the proper function is Proper. It capitalizes the first letter of every word in its parameter (the result is often called Title Case). For 98% of the proper nouns you encounter, this does the trick with no unwanted side effects. But there are a some nuances with which Proper can’t cope. Take that Silicon Valley favorite, no not inane and utterly meaningless name-like arrangements of letters (we’re looking at you Telegent), we’re talking about InterCapping. Take two capitalized words, let’s just randomly select the words “file” and “maker”, push them together into a kind of pseudo compound word, but capitalize the first letter of each component work. You’ll get something like “FileMaker”. Run that through Proper, however, and you’ll lose the second capital.
Proper ( “fileMaker” ) = Filemaker
Proper will also muck up perfectly good acronyms.
Proper ( “NASA” ) = Nasa
Some proper nouns just don’t follow capitalization norms. Take just about any Apple product that starts with the letter i. Or poet e.e. cummings. He just got to decree that his name would not be capitalized and people obeyed.
Lower, Upper, and Proper make for a quick and easy first pass at malformed capitalization, but proper nouns being what they are, you’ll still need a human to find and fix those outliers.
Extra Credit
This week’s extra credit is actually trivia. In the days of moveable type presses, where printers would line up pages one lead letter at a time, the letters were stored in wooden cases with partitions for each letter of the alphabet. Standard practice was to store the capital letters in their own wooden case, just above a second case with the regular letters. If you needed a capital, you could always find it in the upper case.
That’s what they told me in Colonial Williamsburg, anyway.
Subscribe to our blog feed