FM Function of the Week: Lower, Upper, and Proper

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 ( “” ) =

Email addresses aren’t case-sensitive, which is to say that emails sent to,, 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.

This entry was posted in FileMaker 13, Function of the Week. Bookmark the permalink.

4 Responses to FM Function of the Week: Lower, Upper, and Proper

  1. alistair says:

    anther useful one is in this general area is
    returns the word Plaid without any text formatting applied.

    often when you cut and past into a field , style does not work because its formatting is from the cut and past.

  2. Jean-Luc Brousseau says:

    I just found this and it interests me.
    But I would like to know how you manage the following name
    Jean-Luc and other composed first names.
    If I use Proper, I get Jean-luc.
    Any help?

  3. Stuart Gripman says:

    Jean-Luc, there’s no easy way I’ve found to deal with hyphenated names. You can calculate around them by parsing out the individual words and changing the case for them. One way to structure that calc is below, but keep in mind that my example would have to exist in the context of other logic that first checks for a hyphen in the name. It also can’t cope with more than two words. Did I mention I haven’t found an easy way? 🙂

    Let (
    theName = "jean-luc";

    Proper ( LeftWords ( theName ; 1 ) )
    & "-" &
    Proper ( RightWords ( theName ; 1 ) )


  4. Jean-Luc Brousseau says:

    Thank you Stuart,
    I have asked for help also on FMQC list and altogether, we have decided to use the following:
    Substitute ( Proper (FirstName) ; ” ” ; “-” )
    with Replace existing value Activated and Systematic Evaluation (I use French FileMaker 13)

    So, I enter ”jean luc” and the field is showing ”Jean-Luc”
    And it works also with any number of words.

    Hope this will help others.
    Have a nice day.

Leave a Reply

Your email address will not be published. Required fields are marked *