Text and Data Conversions

fnConvertLengthUnits ( number; unitFrom; unitTo )

Converting data between various unit types is a common need in database systems. This function serves as an example of converting length units between the various Metric and Imperial forms.

Note that it uses recursion to save dozens of lines of code. In its first pass, it converts its input into meters, and then in its second pass, converts meters to whichever unit the calling calculation has requested. This technique saves the function from having to create a massive matrix of 15 *15 different options.

Examples:

fnConvertLengthUnits ( 9; "in"; "m" ) returns .2286.

fnConvertLengthUnits ( 2.33; "ft"; "in" ) returns 27.9599999714808.

fnConvertLengthUnits ( 5; "km"; "cm" ) returns 500000.

Code:

 // converts common length units
 // input:
 // number
 // unitFrom = specific text keyword
 // ( microinch | in | ft | yd | mile | league | league nautical |
 // µm | mm | cm | dm | m | dam | hm | km )
 // unitTo = same as above.
 // output: number

 Case (
 unitFrom = "microinch"";
 fnConvertLengthUnits ( number * .0000000254; "m"; unitTo );
 unitFrom = "in"";
 fnConvertLengthUnits ( number * .0254; "m"; unitTo );
 unitFrom = "ft"";
 fnConvertLengthUnits ( number * .3048; "m"; unitTo );
 unitFrom = "yd"";
 fnConvertLengthUnits ( number * .9144; "m"; unitTo );
 unitFrom = "mile"";
 fnConvertLengthUnits ( number * 1609.3; "m"; unitTo );
 unitFrom = "league"";
 fnConvertLengthUnits ( number * 4828.0417; "m"; unitTo );
 unitFrom = "league nautical"";
 fnConvertLengthUnits ( number * 5556; "m"; unitTo );

 unitFrom = "µm"; fnConvertLengthUnits ( number *.000001; "m"; unitTo );
 unitFrom = "mm"; fnConvertLengthUnits ( number * .001;"m"; unitTo );
 unitFrom = "cm"; fnConvertLengthUnits ( number * .01;"m"; unitTo );
 unitFrom = "dm"; fnConvertLengthUnits ( number * .1;"m"; unitTo );
 unitFrom = "dam"; fnConvertLengthUnits ( number * 10;"m"; unitTo );
 unitFrom = "hm"; fnConvertLengthUnits ( number * 100;"m"; unitTo );
 unitFrom = "km"; fnConvertLengthUnits ( number * 1000;"m"; unitTo );

 unitFrom = "m" and unitTo = "µm";number * 1000000;
 unitFrom = "m" and unitTo = "mm"; number * 1000;
 unitFrom = "m" and unitTo = "cm"; number * 100;
 unitFrom = "m" and unitTo = "dm"; number * 10;
 unitFrom = "m" and unitTo = "m"; number * 1;
 unitFrom = "m" and unitTo = "dam"; number * .1;
 unitFrom = "m" and unitTo = "hm"; number * .01;
 unitFrom = "m" and unitTo = "km"; number * .001;

 unitFrom = "m" and unitTo = "microinch";number * 39370078.7401575;
 unitFrom = "m" and unitTo = "in"; number * 39.3700787;
 unitFrom = "m" and unitTo = "ft"; number * 3.2808399;
 unitFrom = "m" and unitTo = "yd"; number * 1.0936133;
 unitFrom = "m" and unitTo = "mile"; number * 0.0006214;
 unitFrom = "m" and unitTo = "league"; number * 0.0002071;
 unitFrom = "m" and unitTo = "league nautical"; number * 0.00018;

 )

fnConvertTemperature ( number; inputUnit )

Function converts between Celsius to Fahrenheit.

Notice the fail condition for the Case() function. Given the specific values the inputUnit parameter requires, its always best to test for errors.

Examples:

fnConvertTemperature ( 65; "F" ) returns 18.

fnConvertTemperature ( 40; "C" ) returns 104.

Code:

 // Converts celsius to fahrenheit and vice-versa
 // input:
 // temperature = number
 // inputUnit = "C" | "F"
 // output: temperature in text with unit notation

 Case (
 inputUnit = "F" ; Round ( ( temperature - 32 ) * 5/9 ;0 );
 inputUnit = "C" ; Round ( ( temperature * 9/5 ) + 32 ;0 );
 "error - inputUnit not recognized"
 )

fnConverttoUSAbrvState ( text )

Theres nothing particularly magical about this function; it converts long state names for the United States into their abbreviated form. It is useful only because once written it never has to be written again.

Note that to save space and avoid belaboring the obvious, we didn include its partner, fnUSAStateConverttoLong(). That function can be found in the electronic files included with this book.

One could argue that this kind of lookup table is a good candidate for solving with a database structure. But it requires more work to reuse a database structure, and this list is closed-ended, meaning it is going to change very slowly, if at all. If there were hundreds of data pairs and they changed frequently, a custom function might not be the ideal choice.

Examples:

fnConverttoUSAbrvState ( "California" ) returns CA.

fnConverttoUSAbrvState ( "Ican spell" ) returns Ican spell.

Code:

 // Converts long US State names to 2-char abbreviations

 Case (
 text = "Alabama"; "AL";
 text = "Alaska"; "AK";
 text = "Arizona"; "AZ";
 text = "Arkansas"; "AR";
 text = "California"; "CA";
 text = "Colorado"; "CO";

 ...

 ...
 text = "Wyoming"; "WY";
 text // default do nothing
 )

Note: List trimmed to save space. Please refer to the electronic files included with this book for the complete code.

fnExplodeText ( text )

Exploded text allows developers to create multiline keys within FileMaker and then to use those keys in relationships often established for filtering portal contents.

Examples:

"Zaphod" becomes:

Z

Za

Zap

Zaph

Zapho

Zaphod

To learn more about using multiline keys and how to construct filtered portals, see Chapter 16, "Advanced Portal Techniques," in our companion book, Special Edition Using FileMaker 8.

Note that exploded text will significantly increase the size of an index for a given field. If you are concerned about performance or file size, consider adding a limiter to this function: an integer that controls how many characters deep the function should extract text.

Code:

 // returns a delimited list of all the possible left substrings
 within a text string
 // input: text
 // output: delimited text
 // note: if a field containing this data is indexed, it can result
 in very large storage blocks.
 // note: 49999 iterations is the maximum permitted by FileMaker
 Let ([
 textLength = Length ( text )
 ];
 Case (textLength > 1;
 fnExplodeText ( Left ( text; textLength - 1 ) ) & "¶"& text;
 text
 )
 )

 /* Alternate function for character limited results:
 // requires a second parameter: characterLimit
 Let ([
 newText = Left ( text; characterLimit );
 textLength = Length ( newText )
 ];
 Case ( textLength > 1;
 fnExplodeText ( Left ( text; textLength - 1 ); characterLimit )
 & "¶" & newText;
 newText
 )
 )
 */

fnFormatDollars ( number )

The following function uses the fnInsertRepeatingCharacters() function to format a number into U.S. currency.

Note the isolation of logic: This function manages how to handle negative numbers and decimals, along with to what degree to round. The fnInsertRepeatingCharacters() function only takes care of comma placement. This preserves flexibility and reusability in both functions.

Examples:

fnFormatDollars ( 111 ) returns $111.

fnFormatDollars ( 33222111 ) returns $33,222,111.

fnFormatDollars ( -4333.222 ) returns -$4,333.22.

Code:

 // converts number data into data with commas and $ symbol
 // input: number
 // output: text
 // dependencies: fnInsertRepeatingString
 Let ([

 positiveInteger = Abs ( Int ( number ));
 decimal = Abs ( number ) - positiveInteger
 ];

 Case ( number < 0; "-$"; "$" ) &
 fnInsertRepeatingString ( positiveInteger; ","; 3;"right" ) &
 Case ( decimal  0; Round ( decimal; 2 ); ".00 )
 )

fnFormatPhone ( text )

Based on how much it consumes the attention of developers, one might assume phone number formatting to be a favorite pastime. This function represents an attempt to put the functionality to bed, once and for all.

This function is most often used in conjunction with the Auto-Enter by Calculation field option. If you turn off the "Do Not Replace Existing Value" checkbox associated with that option, the field in question will automatically reformat itself whenever someone enters new data or edits its contents.

You can extend this function in a variety of ways: You could add recognition of an override character (say, for example, a "+" character) that would leave data entered exactly "as is" if users prefix the input with that override. Another modification could be to change the mask or style attributes of the function to refer to one or more preferences fields on a user record, if your system has a such a thingallowing users to control phone formats dynamically.

This is where custom functions show their strengths: by abstracting the logic of this functionality into a central location, developers can efficiently modify and expand upon it.

Examples:

fnFormatPhone ( "1234567890111"; "usa_standard" ) returns [phone] x111.

fnFormatPhone ( "1234567890"; "usa_dot" ) returns 123.456.7890.

fnFormatPhone ( "1122aabb"; "usa_dot" ) returns error - too few numerals: 1122aabb in red colored text.

Code:

 // reformats phone numbers based on a mask style
 // dependencies:
 // fnTextColor() & fnMaskReplace()
 // input:
 // text = text string assumed to contain at least 10 numeral digits
 // style = specific keywords to allow for different
 styles(international) within the same database
 // output:
 // text string
 // note: error returned in red if < 10 digits
 // note: strings assume no more than 20 characters

 Let ([
 minimumDigits = 10;
 digitsOnly = Filter ( text; "0123456789" );
 digitCount = Length ( digitsOnly );
 errorCheck = Case ( digitCount < minimumDigits;
 fnTextColor ( "error - too few numerals: " & text; "red" );
 0 );
 formatText = Case (
 style = "usa_standard"";
 fnMaskReplace ( "( *** ) ***-**** x***********"; text; "*" );
 style = "usa_dot"";
 fnMaskReplace ( "***.***.**** x***********"; text; "*" );
 style = "japan_alternate"";
 fnMaskReplace ( "* ** ***-**** x***********"; text; "*" );
 style = "japan_standard"";
 fnMaskReplace ( "** ****-**** x***********"; text; "*" );
 fnMaskReplace ( "*** ***-**** x***********"; text;"*" )
 ); // end case { formatText }
 finalBlackText = fnTextColor ( formatText; "black" )
 ]; // end variables
 Case ( errorCheck ? 0; errorCheck; finalBlackText )
 ) // end let

fnInsertRepeatingCharacters ( text; insertString; numberOfCharacters; startDirection )

Converting data into currency, or formatting a number with commas, requires some function that can make character insertions at regular intervals. Rather than write a function that only manages a specific currency or situation, this function is more generic. It allows you to specify what character set you wish to insert into some other body of text, the interval at which you need it inserted, and finally from which direction to begin counting. This function will then be used by others when setting up specific cases of, for example, a number formatted with commas or dealing with currency.

Note that FileMaker can display numbers with commas and with currency symbols, but these displays do not manipulate the actual data in question. This function operates at a data level, not a display level. It actually changes your data.

Note also that this function does not make any logical assumptions about what sort of data youve passed it: It will simply iterate through N number of characters.

Examples:

fnInsertRepeatingText ( "Azeroth"; "*"; 2; "left" ) returns Az*er*ot*h.

fnInsertRepeatingText ( "Ironforge"; "*"; 3; "left" ) returns Iro*nfo*rge.

fnInsertRepeatingText ( "Darnassus"; "*"; 4; "right" ) returns D*arna*ssus.

fnInsertRepeatingText ( "1222333"; ","; 3; "right" ) returns 1,222,333.

fnInsertRepeatingText ( "1222333.444"; ","; 3; "right" ) returns 12,223,33.,444.

Code:

[View full width]
 // converts a number into currency text complete with commas.
 // input: a number
 // text = source string
 // insertString = text to insert at intervals
 // numberOfCharacters = interval
 // startDirection = "right" or "left"
 // output: text string

 Let ([
 lengthText = Length ( text );
 remainder = Mod ( lengthText; numberOfCharacters )
 ];

 Case (
 startDirection = "left" or remainder = 0;

 Case ( lengthText > numberOfCharacters;
 Left ( text; numberOfCharacters ) & insertString & fnInsertRepeatingString (
 Right ( text; lengthText - numberOfCharacters ); insertString; numberOfCharacters;
 startDirection );
 Right ( text; lengthText )
 );

 startDirection = "right";

 Case ( lengthText > numberOfCharacters;
 Left ( text; remainder ) & insertString & fnInsertRepeatingString ( Right (
 text; lengthText remainder ); insertString; numberOfCharacters; "left" );
 Right ( text; lengthText )
 );

 "error - startDirection not recognized"
 )
 )

fnIsWhitespace ( text )

This function looks for "filler" characters in a block of text and returns a 1 or 0 if that block of text is only comprised of filler characters. In this example weve used a tab, return carriage, and space for filler characters, but you could add whatever other characters to the Filter() function as you wish.

Examples:

fnIsWhitespace ( "hello " ) returns 0.

fnIsWhitespace ( " " ) returns 1.

Code:

 // determines if a block of text contains nothing other than spaces,
 tabs and pilcrow characters
 // input: text
 // output: 1 or 0
 // dependencies: uses fnTab

 Let ([
 filtered = Filter ( text; " ¶" & fnTab )
 ];
 If ( filtered = text; 1; 0 )
 )

fnMaskReplace ( maskText; replacementText; wildcardCharacter )

This function is often called by other functions like fnFormatPhone() and fnFormatSSN(). It allows developers to create a character mask of some sort and insert characters into that mask.

Note that this function is recursive and passes two altered bits of data back into itself in each pass:

pass one("***hello***"; "123456"; "*")

pass two("1**hello***"; "23456"; "*")

pass one("12*hello***"; "3456"; "*")

pass one("123hello***"; "456"; "*")

...and so on.

Examples:

Where a field, myPhone, contains 1234567890, fnMaskReplace ( "(xxx) xxx-xxxx"; myPhone; "x" ) would return [phone].

Another example might derive from a product name: AB12301Widget (pack of 10). In that case, fnMaskReplace ( "**-**-*** ******************************"; productSKU; "*" ) might return AB-12-301 Widget (pack of 10).

Code:

 // replaces wildcard characters within a text string with the characters
 in a replacement string
 // input:
 // maskText = text string with some number of wildcard characters
 // replacementText = text string meant to replace wildcard
 characters one for one
 // wildcardCharacter = the specific char used as a wildcard
 // output:
 // text string
 // note: if there are too many wildcard characters, they will be stripped out
 // note: if there are too many replacement characters, the excess will be ignored
 // note: recursive stack can manage up to 49,999 characters.

 Let ([
 charReplaceCount = Length ( replacementText );
 charWildcardCount = Length ( Filter ( maskText; wildcardCharacter ));
 firstWildcardPosition = Position ( maskText; wildcardCharacter; 1; 1 );
 firstReplaceChar = Left ( replacementText; 1 );
 remainingReplaceChars =
 Right ( replacementText; Length ( replacementText ) - 1 );
 oneCharReplaced =
 Replace ( maskText; firstWildcardPosition; 1; firstReplaceChar );
 returnText = Case ( charReplaceCount > 1 and charWildcardCount > 1 ;
 fnMaskReplace ( oneCharReplaced; remainingReplaceChars;
 wildcardCharacter );
 oneCharReplaced
 ); // end case
 cleanText = Substitute ( returnText; wildcardCharacter; "" )
 ]; // end variables
 cleanText
 )

 /* Example:
 fnMaskReplace ( "***hello***"; "123456"; "*" )
 returns: "123hello456"

 fnMaskReplace ( "***hello********"; "123"; "*" )
 returns: "123hello"

 fnMaskReplace ( "***hello*"; "1234567"; "*" )
 returns: "123hello4"
 */

fnPadCharacters ( text; padLength; padCharacter; side )

We often face situations where a given text string, or more often a number, needs to be a fixed number of characters in length. This function will allow a developer to pad a string of data with some sort of pad character.

It makes use of the fnRepeatText function. This simplifies the function significantly and is a good example of using a subfunction effectively.

Notice also that the side parameter requires specific values.

Example:

fnPadCharacters ( "999"; 8; "0"; "start" ) returns 00000999.

Code:

 // function adds characters to either the right or left of a text string.
 // dependencies: fnRepeatText()
 // input:
 // text
 // padLength = total characters the string should reach
 // padCharacter = character to use in padding
 // side = "left"|"right"
 // output: text
 // note: in the case that text > padLength, function will truncate

 Let ([
 textLength = Length ( text );
 padString = fnRepeatText ( padCharacter; padLength - textLength )
 ];
 Case (
 textLength > padLength; Left ( text; padLength );
 side = "left"; padString & text;
 side = "right"; text & padString;
 "error: side not recognized."
 )
 )

fnRecordMetaDisplay ( Create_Name; Create_Timestamp; Modify_Name; Modify_Timestamp )

We recommend that for every table in a database, developers create what weve referred to as housekeeping fields: meta information stored about when a record was last created and/or modified and by whom. These four fields, fed by auto-enter field options, track this information for all records.

Once this information is available for a given table, we find it useful to place it somewhere innocuous on a layout. Often users benefit from knowing when something has been edited, and so on. To that end, this function creates a display that is easy for users to read.

Example:

fnRecordMetaDisplay ( "slove"; "11/10/2005 6:45:22 AM"; "slane"; "11/10/2005 4:15:02 PM" ) will return Created November 10, 2005 (6:45am) by slove; modified November 10, 2005 (4:15pm) by slane.

Code:

 // creates the record housekeeping field display
 // input: creator name, created timestamp, modifier name, modified timestamp
 // output: display text

 // create portion
 "Created" &

 MonthName( Create_Timestamp ) & " " &
 Day( Create_Timestamp ) & ", " &
 Year( Create_Timestamp ) & " ( " &

 // format time
 Case (
 Hour ( Create_Timestamp ) > 12 ;
 ( Hour ( Create_Timestamp ) - 12 ) & ":" &
 ( Right ( "0" & Minute ( Create_Timestamp ) ;2 ) ) & "pm" ;
 Hour ( Create_Timestamp ) & ":" &
 ( Right ( "0" & Minute ( Create_Timestamp ) ; 2 )) & "am"
 ) // end case
 &") by " & Create_Name &
 //modify portion
 Case(
 not IsEmpty ( Modify_Timestamp );
 "; modified " & MonthName ( Modify_Timestamp )& " " &
 Day ( Modify_Timestamp ) & ", " & Year ( Modify_Timestamp )
 & " (" &
 // format time
 Case (
 Hour ( Modify_Timestamp ) > 12 ;
 ( Hour ( Modify_Timestamp ) - 12) & ":"&
 ( Right ( "0" & Minute ( Modify_Timestamp ) ; 2 ) ) & "pm" ;
 Hour ( Modify_Timestamp ) & ":" &( Right ( "0" &
 Minute ( Modify_Timestamp ) ; 2 ) ) & "am"
 ) // end case
 & " ) by " & Modify_Name ; ""
 ) // end case
 & "."

fnRepeatText ( text; numberOfRepetitions )

This is a great function to tinker with if you e new to recursive functions. Notice that it simply stacks its own results on top of each other, decrementing the numberOfRepetitions parameter until it reaches a numberOfRepetitions of 1.

Examples:

fnRepeatText ( "|"; 5 ) returns |||||.

fnRepeatText ( "hello"; 3 ) returns hellohellohello.

Code:

 // duplicates a text string n times
 // input:
 // text
 // integer
 // output: text

 text & Case ( numberOfRepetitions > 1;
 fnRepeatText ( text; numberOfRepetitions - 1 ); "" )

fnTrimCharacters ( text; trimCharacter; side )

FileMakers TRim() function strips leading and trailing spaces from a block of text, but there are times when we need it to recognize other characters as well. This function allows a developer to define what padded character he or she needs stripped away, and whether or not to strip from the start, end, or both sides of a text string.

Note that this function is not case sensitive. To make it so, use the Exact() function when comparing leftChar or rightChar to trimCharacter.

Examples:

fnTrimCharacters ( "xxxMarzenxxxxxx"; "x"; "both" ) returns Marzen.

fnTrimCharacters ( "00001234"; "0"; "start" ) returns 1234.

Code:

 // removes leading and trailing character multiples
 // input:
 // text
 // trimCharacter = character to be trimmed away
 // side: "left"|"right"|"both"
 // output: text
 // note: this function is NOT case sensitive
 Let([
 leftChar = Left ( text; 1 );
 rightChar = Right ( text; 1 );
 remainderLength = Length ( text ) - 1
 ];
 Case (
 ( side = "left" or side = "both" ) and leftChar = trimCharacter;
 fnTrimCharacters ( Right ( text; remainderLength );
 trimCharacter; side );
 ( side = "right" or side = "both" )and rightChar = trimCharacter;
 fnTrimCharacters( Left ( text; remainderLength );
 trimCharacter; side );
 text
 )
 )

fnTrimReturns ( text )

This function is a common tool for doing data cleanup, especially when involving email. Text that has been hard-wrapped can sometimes end up formatted poorly. This function removes single line breaks but preseves double line breaks.

Note that this function does not insert or remove spaces. If a line ends with a carriage return but then does not include a space before the next word, the two words on either side of the line break will be concatenated.

Example:

Consider a field, originalText, with the following:

Hello. This is my

raw text. Notice that

it wraps poorly.

It also has two

paragraphs that

should be on two

lines.

fnTrimReturns ( originalText ) will return

Hello. This is my raw text. Notice that it wraps poorly.

It also has two paragraphs that should be on two lines.

Code:

 // removes single line breaks but preserves double line breaks
 // input: text
 // output: text with ¶ line breaks removed

 Substitute ( text ; ["¶¶";"*#*#*#*#"];["¶";""];["*#*#*#*#";"¶¶"] )