Brian Dunning's FileMaker Custom Functions

SmarterProper ( inText ; prevCase ; textSoFar ; prefixList ; exceptions )

Similar to the Proper function, but handles names with prefixes better

  Average rating: 2.6 (211 votes) Log in to vote

David Tremmel   David Tremmel - Show more from this author

Share on Facebook Share on Twitter

  Sample input:
SmarterProper ( "MACDONALD"; 2; ""; "Mc¶Mac"; "ITT¶Macintosh" )
  Sample output:
MacDonald

  Function definition: (Copy & paste into FileMaker's Edit Custom Function window)

** UPDATED on 7/11/16
Updated and corrected the examples, and fixed the bug noted by Rob

** UPDATED on 6/24/13
finally got around to fixing the bug noted by Lindee - apologies to all who had problems


** UPDATED on 4/30/12 to:
added a way to enter an exception list of words that would be left exactly as entered


** UPDATED on 6/15/09 to:
(1) really fix a bug affecting formatting around apostrophes
(2) fix a bug that gave incorrect results if there were multiple spaces between words
**


** UPDATED on 6/3/08 to:
(1) fix a bug affecting formatting around apostrophes
(2) add the ability to capitalize after hyphens
(3) add the ability to specify that the word start with a lowercase letter
**

SmarterProper is designed to handle names better than the standard Proper function. It returns text with the first letter of each word capitalized - like Proper does - but unlike Proper, it also:
(1) keeps any letters in the middle of words capitalized (if they were preceeded by a lowercase character in the original);
(2) capitalizes letters after an apostrophe (for names like O'Malley) - but only if the remaining text is longer than one character (to correctly format words like Didn't and It's);
(3) capitalizes the first letter of the second part of a hyphenated name;
(4) capitalizes letters after any prefixes you specify (e.g., Mc, Mac).

Syntax: SmarterProper( inText; prevCase; textSoFar; prefixList )
inText = the original text
prevCase = a number representing the case of the preceeding character (for recursion); it should be set to 2 to capitalized the first character of the text, or 0 if you want the first character of the text to be lowercase
textSoFar = the text string at this piont in the recursion; it should be set to empty when the function is called
prefixLIst = list of prefixes that should be followed by a capital letter (a return-delmiited list)

Examples:

SmarterProper ( "MACDONALD"; 2; ""; "Mc¶Mac" ) returns MacDonald
SmarterProper ( "LaMond"; 2; ""; "Mc¶Mac" ) returns LaMond (rather than Lamond)
SmarterProper ( "o'shea"; 2; ""; "Mc¶Mac" ) returns O'Shea
SmarterProper ( "SMITH-THOMAS"; 2; ""; "Mc¶Mac" ) returns Smith-Thomas

 

Comments

Lee Smith   Lee Smith
Aug 13, 2010
Does anyone review the reviews to catch SPAMM?
 
Brian Dunning   Brian Dunning, BrianDunning.com
Aug 13, 2010
Yes.
 
Katy Butterworth   Katy Butterworth, New Zealand
Sep 15, 2010
This was an excellent quick fix to names that were being entered via a website. Many thanks for sharing.
 
Jeff   Jeff, Los Gatos
Dec 19, 2010
Have you ever handled acronyms which need to be all caps. This is in regards to our effort to account for known company names such as ITT?
 
Nik   Nik, Cygnet, Tasmania AU
Apr 29, 2012
Thanks so much for this.
 
David Tremmel   David Tremmel
Apr 30, 2012
This custom function was updated on 4/30/12. You can now include a list of exceptions - words/names that will be left exactly as entered.
 
Lindee   Lindee, Tucson, AZ
Oct 4, 2012
Good function however:

SmarterProper ( "o'shea"; 2; ""; "Mc¶Mac" ) actually returns O'shea

The problem appears to be in the formula for newCase. The third ( test ; result ) parameter appears to be missing a result. I don't think you intended it to be:

firstChar = "'" and Length ( inText ) > 2; firstChar = "'" and Middle ( inText; 2; 1 ) = " ";

A 2 seems to work:

newCase = Case ( prevCase = 0 and IsEmpty ( textSoFar) ; 0 ;
firstChar = " " or firstChar = "-" or not IsEmpty ( FilterValues ( textSoFar; prefixList ) ) ; 2 ;
firstChar = "'" and Length ( inText ) > 2 ; 2 ;
firstChar = "'" and Middle ( inText ; 2 ; 1 ) = " " ; 2 ;
Exact ( firstChar; Upper ( firstChar ) )
)
 
Tobias Sjögren   Tobias Sjögren
Jun 5, 2013
I've used this function for the past few years, it's great.
Now, I returned to this page to find that it has an update - the thing is that it doesn't work.
When I try to press OK from the Edit Custom Function window it says: "There are too many parameters in this function."
This is the line that it stops at: "prevCase = 2; Upper ( firstChar ) & SmarterProper ( newText; 1; textSoFar; prefixList; exceptions );"
 
David Tremmel   David Tremmel
Jun 24, 2013
Finally got around to fixing the bug noted by Lindee...
 
Annette   Annette, Ireland
Nov 4, 2013
I can't even get this to add to the custom functions. It is telling me it's missing operators when I copied and pasted exactly from the above.
 
Ryan   Ryan, NY
Jul 1, 2015
I've been trying to use LLC as an exception and it does not prevent it from improperly formatting it.
 
Peter   Peter, Bristol
Aug 1, 2015
WORKS NICELY - thanks
succeeds with Mcwhirter-o'shea to translate as McWhirter-O'Shea
but fails with o'shea-mcwhirter - translates as O'Shea-Mcwhirter
 
Rob   Rob, Monk Media
Jan 2, 2016
This function is great. Thank you!

I took it one step further and added a global field for prefix and one for exceptions. I've added each exception or prefix followed by a carriage return. Then reference the field in the calculation instead of typing out all the prefix and exceptions into the calculation.

This makes it easier and quicker to add new prefix and exceptions as you come across them, also if you use the function in several scripts you don't have to update all of the scripts.
 
Rob   Rob, Monk Media
May 8, 2016
I found an odd bug, it does correct the 's at the end of a word if there is another word after it.

Example:
SILLY SALLY'S returns--> Silly Sally's (which is perfect)
SALLY'S SILLY returns--> Sally'S Silly

Any thoughts? Other than this I love this function, thanks for your help.
 
Eric   Eric
Jun 15, 2019
This is great! Thank you!
 
Ann Hamel   Ann Hamel
Jun 10, 2021
This function is exactly what I am looking for. I have created the Custom Function, but being new to this, I am not sure about where to store the prefixList. Can someone point me in the right direction? My end goal is to apply this as Conditional Formatting for name fields that my team enters. I've already applied the Proper case and just now realized it wiped out all the capital letters inside the names with "Mc", etc.
 
David Tremmel   David Tremmel
Jun 10, 2021
Ann, the prefix list can be part of the function call (the "Mc¶Mac" in the example), or you could store it in a variable (local, or global as suggested by Rob) and reference that variable in the function call.
 
Ann Hamel   Ann Hamel
Jun 10, 2021
Maybe I need to back up a few more steps. I copy from above, paste it in the new Custom Function I create, list the parameters - what is my next step? I want to apply this in the Conditional Formatting for a Last Name field. I tried creating fields in the table to reference for the textSoFar, prevCase, prefixList, and exceptions, but I know that isn't right. Clearly I am missing something(s)!
 
Ann Hamel   Ann Hamel
Jun 10, 2021
Maybe I need to back up a few more steps. I copy from above, paste it in the new Custom Function I create, list the parameters - what is my next step? I want to apply this in the Conditional Formatting for a Last Name field. I tried creating fields in the table to reference for the textSoFar, prevCase, prefixList, and exceptions, but I know that isn't right. Clearly I am missing something(s)!
 
David Tremmel   David Tremmel
Jun 10, 2021
You cannot change case using Conditional Formatting, only things like color, fill, style, etc. You would need to use this function in a calculation. You could use it in the field's Calculated Value option (use the Options button in the field definition) to automatically change whatever was entered, like:

SmarterProper ( Self ; 2; ""; "Mc¶Mac"; "ITT¶Macintosh" ).

Alternately you can have a second, calculated field that you would use for display. For example, you could have a text field called "nameEntry" for data entry, and a calculated field called "nameDisplay" with the formula below for display:

SmarterProper ( nameEntry; 2; ""; "Mc¶Mac"; "ITT¶Macintosh" ).

In both cases you would want to use your own text for the prefixList and exceptions in the formula.
 
Ann Hamel   Ann Hamel
Jun 10, 2021
Maybe I need to back up a few more steps. I copy from above, paste it in the new Custom Function I create, list the parameters - what is my next step? I want to apply this in the Conditional Formatting for a Last Name field. I tried creating fields in the table to reference for the textSoFar, prevCase, prefixList, and exceptions, but I know that isn't right. Clearly I am missing something(s)!
 
David Tremmel   David Tremmel
Jun 10, 2021
You cannot change case using Conditional Formatting, only things like color, fill, style, etc. You would need to use this function in a calculation. You could use it in the field's Calculated Value option (use the Options button in the field definition) to automatically change whatever was entered, like:

SmarterProper ( Self ; 2; ""; "Mc¶Mac"; "ITT¶Macintosh" ).

Alternately you can have a second, calculated field that you would use for display. For example, you could have a text field called "nameEntry" for data entry, and a calculated field called "nameDisplay" with the formula below for display:

SmarterProper ( nameEntry; 2; ""; "Mc¶Mac"; "ITT¶Macintosh" ).

In both cases you would want to use your own text for the prefixList and exceptions in the formula.
 
Ann Hamel   Ann Hamel
Jun 10, 2021
@ David - Thank you for your response. Of course, that is precisely where I had set my Proper (last name) calculate value option. Apparently, I had first tried it in Conditional Formatting and never removed it. Additionally - I apologize for the repeated comments. Every time I refreshed my browser it submitted the comment again.
I will have to look at this fresh tomorrow. A quick test didn't even do Proper case which I will take as a starting spot in the morning. Again, thank you!
 

Log in to post comments.

 

Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.

Support this website.

This library has been a free commmunity resource for FileMaker users and developers for 20 years. It receives no funding and has no advertisements. If it has helped you out, I'd really appreciate it if you could contribute whatever you think it's worth: