February FileMaker Functions - Upper, Lower, Proper
This is another group of functions that can be learned as a group. Each takes text as an input, modifies it and returns text.
Upper ( text )
Lower ( text )
Proper ( text )
The Upper function returns the text as uppercase; the Lower function returns the text as lowercase; the Proper function returns the text with each word capitalised and all other characters lowercase. So if a field called suburb contains text such as st iVES,
Upper ( suburb ) = ST IVES
Lower ( suburb ) = st ives
Proper ( suburb ) - St Ives
None of the functions affect non-alpha characters such as numbers or punctuation.
Unlike text formatting on a layout object, these functions change the actual characters rather than just their display. So a field formatted on a layout to display in uppercase may still contain lowercase characters. This can be an issue when exporting text or displaying it on another layout without formatting.
Examples of Use
1. Ensuring Data is Stored in Uppercase
Some fields should always be entered as uppercase. An example is the state abbreviation in an address. Sometimes it is required that the city or suburb in an address is entered in uppercase.
This can be enforced easily with a field option that auto-enters a calculation and replaces the field contents:
Upper ( Self )
The Self function is used here to refer to the field where the option is defined. It can easily be copied and pasted for use in many other fields without editing. When set, this auto-entry will update the field text to be uppercase when the user exits the field.
2. Check for Case Usage
The Exact function can be used to test whether exclusively upper or lower case characters have been entered. Using this expression:
Exact ( codeField; Lower ( codeField ) )
This is comparing the data in codeField with an all lowercase version of the data. If it matches exactly, the function returns 1.
3. Mixed Case in Codes
Let's say we have a code field and the rules for entry of a code are that:
- codes are ten digits,
- any letters in the first five characters must be uppercase, and
- any in the last five must be lowercase
We can enforce this with an auto-entered calculation that replaces the field contents:
Upper ( Left ( Self; 5 ) ) & Lower ( Right ( Self; 5 ) )
