Array Functions

Arrays are a powerful and extremely useful programming concept. If you've done any programming in languages such as C++, Perl, PHP, or Visual Basic, you're probably very familiar with both the concept of arrays and some uses for them. We think it likely, however, that most FileMaker Pro developers out there haven't had much experience with arrays and will benefit from both a formal and a practical discussion of them.

Abstractly, an array is essentially a structure that can hold multiple values. The values are ordered within the structure and can be referenced by their position or index number. Figure 14.17 shows a representation of a simple array. The array has been defined to hold up to seven values, but only four values are present. The first element in the array is the value red.

Figure 14.17. An array is a structure that can hold multiple values. Each value can be identified and referenced by an index number.

Arrays are useful for a wide variety of things, including storing lists of data, efficiently moving multiple values through a system, and dealing with variable-size data structures in which it's impossible to define separate fields for each individual data element.

FileMaker Pro doesn't have an explicit "array" data type, but fields defined to hold multiple repetitions can be regarded as arrays. More commonly, if you want to use arrays in FileMaker, you can create your own by placing into a text field multiple values separated by some delimiter.

Note

In FileMaker Pro 8, an "array notation" can be used to refer to data in a repeating field. myField[3], for instance, refers to the data in the third repetition of myField. It's really just a shorthand notation for Getrepetition(myField, 3), but it makes formulas much easier to read.

Return-delimited lists pop up all over the place in FileMaker Pro. Many functions and operations in FileMaker generate return-delimited lists, including most of the Design functions and the Get (ExtendedPrivileges) function. When a user selects multiple values in a check boxformatted field, FileMaker stores that data as a return-delimited list of the selections. Additionally, the Copy All Records script step generates a return-delimited list of the data elements on the current layout for the current found set (elements within a record are separated by the tab character).

Working with Return-Delimited Data Arrays

FileMaker Pro 8 has five functions that greatly facilitate working with return-delimited data arrays such as the ones just described. These are ValueCount, LeftValues, MiddleValues, RightValues, and GetValue. Syntactically, they are very similar to the four "word" functions (WordCount, LeftWords, MiddleWords, and RightWords), as well as to the four "character" functions (Length, Left, Middle, and Right).

Briefly, the syntax of these functions is as described here:

• ValueCount (text) Returns the number of items in a return-delimited list. Unlike its cousin the WordCount function, which interprets sequential word delimiters as a single delimiter, if you have multiple carriage returns in a row, even at the beginning or end of a list, ValueCount treats each one as a delimiter. For example, ValueCount ("¶¶Red¶Blue¶Green¶¶White¶") returns 7. It's immaterial whether the list contains a single trailing return; the ValueCount is not affected by this. Multiple trailing returns affect the ValueCount.
• LeftValues (text; numberOfValues) Returns a list of the first n elements of a return-delimited text string. The list always has a trailing return, even if you are requesting just the first item of the array.
• MiddleValues (text; startIndex; numberOfValues) Returns a list of n elements from the middle of a return-delimited array, starting from the position specified in the second parameter. As with LeftValues, the output of this function always contains a trailing return.
• RightValues (text; numberOfValues) Returns a list of the last n elements from a return-delimited array. This function, too, always generates a trailing return at the end of its output.
• GetValue (listOfValues; valueNumber) Returns a single value from a return-delimited list of values. This value will not contain a trailing carriage return. This function is useful in cases in which you want to loop through a set of values and perform some operation using each value in turn.

If you ever use arrays that use delimiters other than return characters, see "Working with Arrays" in the "Troubleshooting" section at the end of this chapter.

To demonstrate how you might use these functions in a solution, we present an example of iterating through a user's selections in a check boxformatted field and creating records for each selection in another table.

Imagine that you have a table containing information about kids coming to your summer camp, and that one of the pieces of information you are capturing is a list of sports in which the child wants to participate. When you originally set up the table, you simply created a check boxformatted field in the CamperInfo table for this information. You now realize that it's impossible to run certain reports (for example, a subsummary by sport) with the data structured this way, and that you should have created a separate table for CamperSport data. You'd like not to have to reenter all the data, so you want to create a script that loops through all the CamperInfo records and creates a record in the CamperSport table for each sport that's been checked for that camper.

There are many ways you can approach a challenge such as this. You might, for instance, temporarily set data from CamperInfo into variables, navigate to a layout based on the CamperSport table, create records, and populate data from the variables. You've chosen instead to use a portal from the CamperInfo table to the CamperSport table that allows creation of related records. This way, you avoid having to navigate between layouts for each camper, and the CamperID field is automatically set correctly in the CamperSport table.

Stepping Through an Array

A user's selections in a check box field are stored as a return-delimited array, in the order in which the user checked them. There are two ways you can step from element to element in such an array. One method is to iteratively "lop off" the first element of the array until there's nothing left to process. This requires first moving the data to be processed into a temporary location where it can be cut apart without harming the original data. The other method is to use a counter to keep track of what element is being processed. You continue processing, incrementing the counter as you go, until the counter has exceeded the number of elements in the array. To some extent, it's personal preference which method you use. Some developers had a preference for the first method in earlier versions of FileMaker Pro because it was simpler syntactically, but the newer "value" functions (introduced in FileMaker 7) make the second method very appealing now. Both versions of the script are presented here in Listings 14.1 and 14.2 so that you can decide for yourself which is preferable.

Listing 14.1. Method 1: "Lop off" the Top Element of the Array

[View full width]

Go to Layout ["CamperInfo" (CamperInfo)]
Go to Record/Request/Page [First]
Loop
 Set Variable [$sportArray; Value: CamperInfo::SportArray]
 Loop
 Exit Loop If [ValueCount ($sportArray) = 0]
 Go to Portal Row [Select; Last]
 Set Field [CamperSport::Sport; GetValue ($sportArray; 1)
 Set Variable [$sportArray; Value: Let (count = ValueCount($sportArray);
 RightValues ( $sportArray; count-1))
 End Loop
 Go to Record/Request/Page [Next; Exit after last]
End Loop

Notice here that in line 8, the first element of the SportArray is pushed through the portal, where it becomes a record in the CamperSport table. In the next line, the $sportArray variable is then reset to be everything after the first line. It gets shorter and shorter with each pass through the loop, until finally there aren't any more items to process, concluding the inner loop.

Listing 14.2. Method 2: Walk Through the Elements One by One

Go to Layout ["CamperInfo" (CamperInfo)]
Go to Record/Request/Page [First]
Loop
Set Variable [$counter; Value: 1]
 Loop
 Exit Loop If [$counter > ValueCount (CamperInfo::SportArray)]
 Go to Portal Row [Select; Last]
 Set Field [CamperSport::Sport; GetValue (CamperInfo::SportArray; $counter)
 Set Variable [$counter; Value: $counter + 1]
 End Loop
 Go to Record/Request/Page [Next; Exit after last]
End Loop

Again, the main difference with this method is that the inner loop steps through the elements of the SportArray field based on a counter variable.

The Filter ing Functions