Chapter 8. Useful Custom Functions
This chapter presents a sampling of custom functions that we’ve found useful, or that we feel serve as good examples of functions we use in our day-to-day practice. They’re broken into groups according to their purpose, much as Filemaker’s calculation functions are.
Please note that these functions are meant as examples—we’ve not tested them exhaustively, nor is this collection meant to be comprehensive. We hope you’ll find some of them helpful directly, or that reading and analyzing others might provide a springboard for your own ideas.
We use a couple of icons to denote specific types of custom functions:
This icon shows that the function participates in a “sub-function” relationship: the function in question either calls or is called by other custom functions.
This icon shows that the function is recursive: in other words, under certain circumstances, it calls itself.
Number Calculations
fnHypotenuse ( leg1Length; leg2Length )
Although FileMaker includes many common mathematical operations and formulas, no list can be exhaustive. This function applies the Pythagorean Theorem (a2 + b2 = c2) to generate the length of a hypotenuse (the leg of a right triangle opposite the right angle).
Example:
fnHypotenuse ( 3; 4 ) returns 5.
fnNthRoot ( number; root )
FileMaker provides a built-in function for calculating the square root of a number, but not the nth root.
Example:
fnNthRoot ( 8; 3 ) returns 2.
fnNthRoot ( 256; 4 ) returns 4.
fnPolyAreaFromRadius( numberOfSides; radius )
This function computes the area of a regular polygon, given the number of sides and the radius of the polygon. (A regular polygon is a polygon in which all sides are of equal length.) The radius is the distance from the center of the polygon to any vertex. In other words, the radius of the polygon is the radius of a circle that exactly circumscribes the polygon.
Examples:
A pentagon with a radius of three meters would be evaluated like so: fnPolyAreaBySide ( 5 ; 3 ) which returns 21.399 (rounded) square meters.
An equilateral triangle with a radius of 4 inches: fnPolyAreaByRadius ( 3 ; 4 ) returns 20.723 square inches (rounded).
fnPolyAreaFromSideLength( numberOfSides; sideLength )
This function computes the area of a regular polygon, given the number of sides and the length of each side.
Examples:
A hexagon with sides of length 3: fnPolyAreaBySide ( 6 ; 3 ) returns 23.382 (rounded) units squared.
An equilateral triangle with sides of length 4: fnPolyAreaBySide ( 3 ; 4 ) returns 6.928 (rounded) units squared.
fnRandomInRange ( lowNumber; highNumber )
The Random function in FileMaker returns a value from 0 to 1, but developers almost always need a random number within a range of numbers. For example, if you need a number between 10 and 50, the formula would be
Int ( Random * 41 ) + 10
This makes code somewhat difficult to read and requires that you think through the formula each time you need it. The fnRandomInRange() function hides this logic in an easy-to-use function.
fnRandomInRange ( 3; 7 ) might return 4.
fnSphericalDistance ( lat1; long1; lat2; long2; units )
This function computes the distance between two points on the surface of the Earth, given in terms of decimal latitude and longitude. The coordinates must be decimal—in other words, 45.5, not 45 degrees 30 minutes—and must be given in degrees.
The function can return results in miles or kilometers. Any “units” value beginning with “m” will yield miles; otherwise, the function will return kilometers.
The computation is based on the “haversine formula” and assumes a reasonable degree of mathematical precision in the software, which FileMaker possesses.
See http://en.wikipedia.org/wiki/Haversine_formula for further details.
Example:
The distance between San Francisco and Chicago in miles is fnSphericalDistance ( 37.799; 122.461; 41.886; 87.623; "miles" ), which returns 1856.62.
Date Calculations
fnAgeInYears ( dateOfBirth )
This function calculates an age in years.
The GetAsDate() function is used to ensure that whatever is passed in—a date, an integer, or raw text—gets converted to a date and thus accounts for data type issues.
Example:
fnAgeInYears ( "6/6/1967" ) returns 38.
fnDateMonthEnd ( calendarDate )
This particular function determines the last day of the month for a given date. Note that in FileMaker, subtracting one from the first day of a month results in the last day of the prior month.
Example:
fnDateMonthEnd ( "1/1/2005" ) will return 1/31/2005.
fnDateQuarter ( calendarDate )
This function returns numerals; if you wanted it to return text along the lines of “1st Quarter”, “2nd Quarter”, and so on, you’d need a formula that might make use of the Choose() function.
You could also use this function to calculate fiscal quarters by adding a number at the end of the custom function’s formula for whatever month begins the fiscal year for a given company.
Example:
fnDateQuarter ( 12/1/2006 ) returns 4.
Here’s an alternative that returns text:
fnDateRepetitions ( calendarDate; period; numberOfRepetitions)
While we’d never recommend trying to replicate the full functionality of a calendaring program like Outlook in FileMaker, we do often need to create date ranges in list form. This function will generate a delimited list of dates; you can then extract individual dates using the GetValue() function.
Note that the function requires specific keyword inputs and returns an error message if it does not recognize the value passed for its period parameter.
Example:
fnDateReptitions ( "1/1/2005"; "quarterly"; 6 ) returns
1/1/2005
4/1/2005
7/1/2005
10/1/2005
1/1/2006
4/1/2006
fnNextDayOfWeek ( calendarDate; numDayOfWeek )
This function returns a future next date based on a day of week provided. For example, from a starting date of 11/2/2005, the next Friday is 11/4/2005. We often need this sort of function for reporting based on a “standard” week (week starting Friday, week starting Saturday, and the like).
The second parameter is an integer that corresponds to a day of the week. 1 = Sunday, 2 = Monday, and so on through to 7 = Saturday.
Examples:
fnNextDayOfWeek ("6/28/2006"; 7) returns the Saturday following 6/28/2006 = 7/1/2006.
fnNextDayOfWeek ("4/1/2006"; 2) returns the Monday following April Fools’ Day = 4/3/2006.
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.
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, it’s always best to test for errors.
fnConvertTemperature ( 65; "F" ) returns 18.
fnConvertTemperature ( 40; "C" ) returns 104.
fnConverttoUSAbrvState ( text )
There’s 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’t 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'tspell" ) returns Ican'tspell.
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.
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.
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 thing—allowing 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 (123) 456-7890 ×111.
fnFormatPhone ( "1234567890"; "usa_dot" ) returns 123.456.7890.
fnFormatPhone ( "1122aabb"; "usa_dot" ) returns error - too few numerals: 1122aabb in red colored text.
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 you’ve passed it: It will simply iterate through N number of characters.
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.
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 we’ve 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.
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 (123) 456-7890.
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).
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.
fnRecordMetaDisplay ( Create_Name; Create_Timestamp; Modify_Name; Modify_Timestamp )
We recommend that for every table in a database, developers create what we’ve 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.
fnRepeatText ( text; numberOfRepetitions )
This is a great function to tinker with if you’re 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.
fnTrimCharacters ( text; trimCharacter; side )
FileMaker’s 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.
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.
Email Tools
fnEmailIsValid ( text )
This function checks for a few common problems with email addresses and returns a 1 or 0 depending on whether or not a submitted block of text passes its tests.
Note that the function makes use of the fnEmailTLDs (Top Level Domains) function.
The function isn’t meant to be exhaustive: It is still entirely possible to enter an invalid email address; we encourage you to add further conditions to the case function that handles testing.
Also note that the function as written returns a 1 or 0. Using the fnErrorHandler (discussed later in this chapter), you could derive more information from the function when an error condition existed. We wrote this as a Case() test rather than one long concatenated series of tests joined by and operators to explicitly test for each error and allow for the possibility of adding more error handling logic.
Examples:
fnEmailIsValid ( "[email protected]" ) returns 1.
fnEmailIsValid ( "kathielsoliantconsulting.com" ) returns 0.
fnEmailIsValid ( "kathiel@soliant@consultingcom" ) returns 0.
fnEmailTLDs ()
This function serves as a system constant (we discuss that term later in the chapter) and holds simply a value list of top-level domains. It is easy to keep the list up-to-date in this form and prevents developers from having to enter this rather unwieldy block of information in more than this one place (or within a larger, more complex function).
Notice that the fnEmailIsValid function requires that a return carriage follow each domain.
List Handlers
In FileMaker a list is defined as a return-delimited group of values. Most often developers will encounter them in value lists, but they are also often used as simple one-dimensional arrays of data. FileMaker has a set of functions for manipulating list data ( GetValues(), LeftValues(), RightValues(), MiddleValues() ) and the following represent some useful additions.
fnBubbleSort ( list )
This function sorts a list of values using the “bubble sort” algorithm. It isn’t an efficient sort (we’ve included an implementation of the “merge sort” algorithm, fnMergeSort(), which is generally much quicker), but it’s a good example of some useful programming techniques.
This function is actually just a wrapper around a “helper” or “auxiliary” function that does the real work. The internal function is a recursive function that keeps track of some extra information, which it passes to itself as a parameter. In some cases FileMaker will need to pass information to itself in recursive functions that otherwise might confuse developers and need not be exposed to them. For that reason this master function was created so that the actual sort function, fnBubbleSort_iterations(), can be called with the proper parameters.
Example:
Consider a field, list, with the following values:
fish
goat
bird
dog
fnBubbleSort ( list ) will result in
bird
dog
fish
goat
fnBubbleSort_iterations ( list; values )
This function is called by the master fnBubbleSort() function. It is this function that performs the sort and results in a final sorted list. The purpose of the master function is to avoid having to pass the iterations parameter directly.
Consider a value list that contains
fish
goat
bird
dog
fnBubbleSort_iterations ( list; 4 ) will result in
bird
dog
fish
goat
fnBubbleSort_shift ( list; shift )
This function performs the inner comparison for a classic bubble sort. It should be called by fnBubbleSort_iterations().
Consider a list that contains:
fish
goat
bird
dog
fnBubbleSort_shift ( list; 1 ) will result in
fish
bird
dog
goat
fnMergeSort( list )
This function sorts a return-delimited list of values using the “merge sort” technique. The function is recursive. It operates by first splitting the list in two, and then sorting each sublist and merging the results back together into a single sorted list. It relies on the helper function fnMergeLists(), which is responsible for merging two sorted lists into one.
fnMergeSortedValues (list1; list2)
This function merges two sorted, return-delimited lists of values into a single sorted, return-delimited list. It’s probably useful in its own right but is provided here as a necessary “helper” function for the fnMergeSort function.
fnValuesBackwards (list )
List arrays can store simple data in cases where a database table would be overkill or inappropriate, they can be manipulated by a range of functions in FileMaker, and they’re fairly easy to decipher. This function takes a list and flips the order of its values.
If the concept of recursion is a little opaque to you, this is a nice, “clean” recursion that would serve well for study purposes.
Example:
fnValuesBackwards ( "red¶green¶blue¶yellow¶" ) returns
yellow
blue
green
red
fnValuesCrossProduct ( list1; list2 )
This function creates a cross product between two functions. It will output concatenated combinations of each value. For example, take one list ( hello¶goodbye¶ ) and ( 1¶2¶3¶ ). The result of the function will be ( hello1¶hello2¶hello3¶goodbye1¶goodbye2¶goodbye3¶).
This function is a master function that calls a subfunction. The subfunction is a recursive function that requires an initialized counter, and rather than have developers guess on what that initial value needs to be (or better yet, refer to documentation), we created this parent function that takes care of that for them.
Consider this the “public” version of the function. The other is a “private” subfunction and is meant to stay in the background to be called only by this controlling function.
fnValuesCrossProduct_sub ( list1; list2; counter )
The cross product of two sets is a set containing all the two-element sets that can be created by taking one element of each set. For example, if Set1 contained {A, B} and Set2 contained {P, Q, R, S}, then their cross product would consist of {AP, AQ, AR, AS, BP, BQ, BR, BS}. The number of elements in the cross product is the product of the number of elements in each of the two sets.
The fnValuesCrossProduct_sub() function “loops,” incrementing a counter as it goes, until the counter is no longer less than the number of elements expected in the result set. With Set1 and Set2 from the previously mentioned scenario, the function would iterate 8 times. If it were on iteration 5, the function will work with the second item from the first list (because Ceiling (5/4 ) = 2) is “B,” and the first item from the second list (because Mod (4; 4) + 1 = 1) is “P.” “BP” becomes the fifth element of the result set.
Notice also that this function, besides recursively calling itself, also calls the fnTrimCharacters() function available also in this section.
Examples:
fnValuesCrossProduct_sub ( "A¶B¶C"; "1¶2¶3¶4"; 1 ) returns A1¶A2¶A3¶A4¶B1¶B2¶B3¶B4¶C1¶C2¶C3¶C4¶.
fnValuesCrossProduct_sub ( "One¶Two¶Red¶Blue"; " fish" ) returns
One fish
Two fish
Red fish
Blue fish
fnValuesRemove ( list; valuesToRemove )
This function extends what developers can do with lists. It allows a developer to remove one or more values from a given list.
Example:
Consider a field, myList, holding the following list:
Black
Green
Yellow
Pink
Purple
Black
White
fnValuesRemove ( myList; "Pink¶Black¶Yellow" ) will return
Green
Purple
White
Developer Tools
The following functions are those that tend not to do anything for users directly but represent some of our most used behind-the-scenes tools.
fnErrorHandler ( errorCode )
When adding error checking to functions—and we strongly recommend that you do—you will need to consider a few issues. First, is it necessary in every function to test for valid or non-Empty parameters? FileMaker’s standard functions don’t, so in some cases we will opt to follow that approach and assume that the developer in question will test his or her code and be able to detect any error conditions in such cases.
However, for functions that veer off the beaten path—for example, by requiring specific keywords or employing complex logic—we will often add some reasonable level of error checking.
One then needs to decide how to manage the error results themselves. You might, if you wish, return an error message as the result of your function. In all the examples we’ve provided in this book, that is the path we’ve chosen. It is the most straightforward to write into the code of your functions and it reduces the complexity of the material we’re presenting; however, some software developers may argue that error results belong in a different memory space than function results. We tend to agree.
In solutions that use more than a handful of custom functions (which, naturally, comprise almost all the solutions we produce), we add this fnErrorHandler() function in order to keep the error clutter in other functions to a minimum and to centralize the implementation of what a given system should do once it discovers an error.
Notice that this function contains no error checking logic (other than its own)—in other words, it will not test other functions. Instead it is meant to manage an error once another function encounters one.
Note also that this function sets a global variable to the error code passed into it, including zero (no error). This allows developers to check for errors after they’ve run routines that reference functions that in turn make use of this error handler.
For an example of how one might use this error handler from another function, please refer to fnErrorHandlerExample().
fnErrorHandlerExample ( text )
This function serves as an example of using a central error handler instead of passing error messages directly from your functions. Instead of intermingling error messages with all your functions (and being faced with maintaining consistency across possibly dozens of functions), this approach places the logic for presenting errors in a single place.
Notice in the case of an error, this function returns an error message derived from fnErrorHandler and also sets a $$errorHandler global variable to 90101.
Note also that you need not return the results of fnErrorHandler() if you wish. This function does so, but the purpose of setting $$errorHandler is so that a developer can opt to not commingle error messages with function results if he or she wishes.
Please refer to fnErrorHandler() for more detail on error handling with custom functions.
fnModifierKeys ()
This function returns a return-delimited list of text descriptors for the modifier keys held down whenever it is evaluated. This is useful for those of us who can’t remember which numbers correspond to which key combinations. Developers can use this function in scripts or elsewhere by testing for the presence of “shift” in fnModifierKeys instead of having always to use Get(ActiveModifierKeys) = 1.
Examples:
In the case where a user has the Shift key pressed, fnModifierKeys returns Shift.
In the case where a user has both the Shift and Ctrl keys pressed, fnModifierKeys returns
Shift
Control
System Constants
A system constant is a value that developers place in a custom function that then becomes permanent. It doesn’t “evaporate” at the end of a session (as global field values and global variables do), nor does its value vary from user to user. Developers can count on them being persistent and can easily edit them as needed.
fnSolutionVersion ()
We find it valuable to track version numbers of our systems and at times need to use logical (script) routines that reference or make use of those version numbers. Rather than adding a field to the data schema of a solution, use a custom function.
We recommend also creating a similar fnSolutionNamespace() function.
Examples:
In all cases, fnSolutionVersion returns 1.003. Developers will want to update the hard-coded value as appropriate.
fnTab ()
FileMaker uses a pilcrow character (“¶”) to represent carriage returns but there’s no analog for tab characters. This custom function is simple but vital if you need to do formatting with tab characters. The alternative is seeing blank space within your formulas and being left to wonder what is in the space: Space characters? Tabs? Odd characters that can’t display?
Note that Ctrl-tab inserts a tab character on Windows and Opt-tab on the Mac OS. The two are cross-platform compatible.
fnTextColor ( text; color )
With this function you can save yourself a bit of hassle by allowing the use of familiar terms for colors, rather than being forced to look up RGB numbers, but the real value here is in making use of the central nature of custom functions: If ever you need to change a color in your system you have one single place to do so. Tweak one of the RGB numbers in the function and your database will reflect that change throughout (however, some calculation results may be indexed). This custom function also ensures consistency. By virtue of having only one instance of a color, you never get confused on which red you’re using in a given system.
Examples:
fnTextColor ( "hello world"; "blue" ) returns "hello world" as blue text.
fnTextColor ( "hello world"; "purple" ) returns "color not recognized".
Toolkit for Complex Data Structures
This last group of custom functions requires some explanation, and perhaps some evangelism as well. These functions are complex; our intent was to demonstrate, with a set of real examples from our own work, how it’s possible to use custom functions and the power under the hood of this often under-appreciated feature.
Data API Within FileMaker
This fnXML*** set of functions all exist to help manipulate a block of XML data, stored in a text field or variable. Together they represent a kind of small API (Application Programming Interface), and support some specific programming methodologies. In that way, this suite of functions is less general purpose than the earlier ones in this chapter.
The strength of these functions lie in their capability for manipulating complex data structures. You can add, delete, update, and extract data from a tree of nested data.
An API is a term borrowed (perhaps with a degree of liberty) from other programming environments like C# and Java. It refers to a set of instructions that are largely independent and allow programmers to accomplish some set of functions without having to know how the API itself was constructed. Both Apple and Microsoft provide a large range of APIs with their operating systems, there’s an Apache API for extending its capabilities as a web server, and there’s even a FileMaker API for writing plug-ins. The idea here is that an API provides a framework and hooks into some set of functionality that you as a developer intend to leverage and reuse, without necessarily needing to understand all the details of how it works internally.
Data Tree
A data tree is a powerful programming concept also borrowed from other environments. Abstractly, it is essentially a data structure that can hold multiple values. These values can be referenced by their positions within the tree (or perhaps array, if you accept a loose interpretation of the term) and related in similar ways to a set of FileMaker tables. In crude terms, this data structure can be thought of as a database within a database.
Data trees and arrays are useful for a variety of things, including storing a simple list of values, efficiently moving multiple values as a single block from place to place in a system, and for dealing with variable length data structures where it would be impractical or impossible to define fields or variables enough to hold them.
Perhaps an example would help; this is a simple one-dimensional array, and represents a simple one-level hierarchy of tiered data:
[ red | green | blue | yellow ]
In this scenario, most FileMaker developers would choose—rightly—to work with either a repeating variable or a return-delimited list and the GetValues() function. (We’ve included list handlers in this book as well.)
But there are times when a one-dimensional array isn’t enough for your needs. Consider a scenario where you need to store colors and, say, shirt sizes and quantities. Your pipe-delimited array will need some new delimiter characters:
[ red; large; 20 | green; large; 10 | blue; medium; 15 ... and so on ]
As you can see, even a two-dimensional array can start to feel complex.
This is where XML can come in. How does one describe an N-dimensional data structure in a way that can be interpreted by both humans and across multiple platforms?
Note that the issue is twofold: XML holds information about its data, as well as providing a structure in which to store it.
Using XML As a Data Structure
Entire books have been written on XML and a complete discussion of it extends beyond the scope of this book; however, suffice it to say we chose XML for three reasons. First, it’s self-documenting: Instead of identifying something by its position within an array, XML uses tags to clearly label data. <quantity>10</quantity> is a far more clear description than the preceding example using pipe and semicolon delimiters. Second, XML is an industry standard and has emerged as a leading means to transfer and express data between platforms and applications. Third, XML allows for “deep” data structures. Note that the following example represents a four-dimensional tree:
Imagine trying to represent that data structure in a flat, delimited text list, and the mind boggles. Note too that even if you’re unfamiliar with XML or with the data that this block is meant to express, you can infer a great deal by simply reading it.
Path-Based Selections
Having now decided on an API to manage a data tree, and having selected XML as a data format, this now brings us to FileMaker. We’ve worked with various sorts of arrays and trees for many years, but working with return-delimited lists or temporarily shoving things into makeshift data tables has never completely fit the need.
FileMaker 7 introduced script parameters and FileMaker 8 introduced script variables. Neither of them support anything other than one value (which can admittedly be a repeating value); if you want to pass more than one piece of data by either of these two features of FileMaker, you’ll need to use a block of text and delimit it somehow. Then the second part of the process will be writing a parsing routine that extracts your multiple values from this data block.
This problem is a good candidate for one or more custom functions. Rather than writing a series of parsing routines throughout a database, we suggest building a set of array handlers that can abstract and centralize the entire set of functionality you need.
The approach we’ve developed here is admittedly complex. We wanted to get more than just a simple container of one or two dimensional data: We wanted to be able to name the values in our data and to hierarchically organize them to N layers deep. We have created a path syntax (inspired by XPath, for those of you familiar with it—http://en.wikipedia.org/wiki/Xpath) that can pull a variety of structured data from an XML source.
The six main functions do the following:
fnXMLselect— Extracts a block of data from an XML source.
fnXMLupdate— Replaces a block of data within an XML source with a new value.
fnXMLinsert— Inserts a new block of XML into an XML source.
fnXMLdelete— Deletes a block of XML from within an XML source.
fnXMLclean— Strips an XML block of extraneous characters and formatting.
fnXMLformat— Adds tab characters and return carriages to a block of XML for easy display/reading.
fnXMLselect() is the most powerful of the four functions and is used by three of the others. It takes two parameters, xmlSource and path, and returns a block of XML extracted from the value passed into xmlSource.
The path syntax is specific and is the key to understanding how to use all four functions. An example (referring to the preceding shirt inventory example) might be:
inventory/shirt/color
This path would return the value for the first color element of the first shirt of the first inventory it finds. Think of this path exactly like a tree or a file directory structure. In the preceding case, this path would return
red
The path inventory/shirt/sizes would return from the XML on pages 240-241:
The path syntax here is specific and drives the logic of what data you manipulate within the block of XML. Valid syntax includes
node/subnode
(a tree of any depth comprised of simple path nodes)
node/subnode/
(function will strip trailing slashes)
node[2]/subnode
(specify an integer to take the Nth occurrence of a node; 1-based)
node[attribute="foo"]/subnode
You can specify finding an occurrence of a node where a child attribute node contains specified data.
Note: This syntax does not support XML attributes.
Supported: <tag><name>foo</name></tag>
Not supported: <tag name ="foo">value</tag>
This syntax also does not support the empty/close shortcut style: <tag/>.
This syntax supports the use of carriage returns, tabs, and spaces within XML values. It will not strip them out, and shouldn’t produce bugs when encountered; however, using fnXMLinsert() and fnXMLdelete() in combination with XML formatted with such characters may end up looking fairly ugly. It should retain functionality, however.
Imagine a scenario where you want to create an audit trail. (Using auto-enter by calculation functions, you can trigger a second field to update itself when a given field is updated by the user.)
Rather than having to create double the amount of fields in your database, you’ll want to store the audit information likely in one field. Likewise, you need to be able to store multiple values for a single field: who edited a field, what the old value was, what the new value is, and so on.
These functions would be perfectly suited for just such a scenario: use fnXMLinsert to store information into the audit trail field, and then use fnXMLselect to extract it for a rollback if necessary. The data structure might look something like this:
You can then extract the old value with a query like so:
fnXMLselect
( auditTrail; audittrail/record[id=12]/field[name=Address]/oldValue )
This function will return: 123 Main Street.
The Functions
The following, finally, are the functions that comprise the suite of tools within this API.
fnXMLclean ( xmlSource )
This function calls the fnXMLclean_sub() function and is used to supply default initial values to that function.
Example:
Assume a field exists, xmlSource, that includes XML data formatted with return characters and tabs or spaces:
fnXMLclean ( xmlSource ) returns
Note that all text formatting (font, size, style, and color) will be removed as well.
fnXMLclean_sub ( xmlSource; afterStartTag )
This function takes a block of XML with some amount of extraneous characters sitting between end tags and start tags in the form of spaces, tabs, and carriage returns, and returns a block of XML stripped of all such detritus.
Note that it uses a subfunction to determine white space characters. If ever one’s logic needed to be extended, this would easily allow for such.
Assume a field exists, xmlSource, that includes XML data formatted with return characters and tabs or spaces:
fnXMLclean ( xmlSource ) returns
fnXMLdelete ( xmlSource; path )
This function, along with its sibling functions fnXMLselect, fnXMLupdate(), fnXMLinsert(), exists to help manipulate an XML data structure.
This particular function will remove a block of data as controlled by the path parameter.
Examples:
fnXMLdelete ( xmlSource; "first_name" )
The result would be that within xmlSource, the <first_name> block would be removed, including the enclosing tags.
Consider the following source XML:
for fnXMLdelete ( xmlSource; "root/branch" ) the result would be
fnXMLformat ( xmlSource )
This function calls the fnXMLformat_sub() function and is used to populate default initial values.
Example:
Assume a field exists, xmlSource, that holds
fnXMLformat_sub ( xmlSource ) returns
fnXMLformat_sub ( xmlSource; afterStartTag; indent )
This function is used to format a block of XML into an easy-to-read form. It inserts tab and carriage return characters, and colors the XML tags.
Notice that it uses subfunctions for the color choice, tab character, and for determining if there is already some whitespace (spaces, tabs, and return characters) in the block of XML.
Further, this function uses two parameters for keeping track of its recursions. This is a sub-function that should never be called by anything other than its enclosing fnXMLformat() function.
Example:
Assume a field exists, xmlSource, that holds
fnXMLformat_sub ( xmlSource; 1; "" ) returns
fnXMLinsert ( xmlSource; path; value )
This particular function will create a block of data as controlled by the path and value parameters.
Examples:
fnXMLinsert ( xmlSource; "first_name"; "Alexander" )
The result would be that within xmlSource, a new <first_name> block would be created and given a value of "Alexander".
Consider the following source XML:
for fnXMLinsert ( xmlSource; "root/branch; "<data>999</data><date>11/20/2005</date> ) the result would be
fnXMLselect ( xmlSource; path )
fnXMLselect() serves as both a subfunction for three of the other XML-parsing functions and as the means by which developers can extract data from an XML block.
Its primary mission is to take a path parameter (discussed in detail in the preceding pages) and return a block of XML extracted from a larger XML data source.
It also makes use of two global variables to keep track of where within the source XML a given block starts and ends.
Examples:
fnXMLselect ( xmlSource; "last_name" )
which might return Smith.
Another call might look like this:
fnXMLselect ( xmlSource; "new_record_request/invoice/fkey_customer" )
and might return C_10012 as a customer ID.
Another approach can make use of filtering, using a square bracket construction similar to an XPath predicate:
fnXMLselect ( xmlSource; "new_record_request/invoice[date="11/11/2005"]/fkey_customer" )
This might return a different customer ID.
Consider the following source XML:
for path root/branch:
result =
<num>100</num>
<text>foo</text>
for path root/branch/num:
result =
100
for path root/branch[2]:
result =
<num>200</num>
<text>xyz</text>
for path root/branch[num="200"]:
result =
<num>200</num>
<text>xyz</text>
for path root/branch[num="200"]/text:
result =
xyz
fnXMLupdate ( xmlSource; path; value )
This particular function will replace a block of data within the source XML with the contents of the “value” parameter.
Examples:
fnXMLupdate ( xmlSource; "last_name"; "Smith" )
The result would be that within xmlSource, the first <last_name> block encountered would be given a value of “Smith”.
Consider the following source XML:
for fnXMLupdate ( xmlSource; "root/branch/num"; 500 ) the result would be
Code:
