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 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 for dealing with variable-length data structures where it's impossible to define separate fields for each 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 7, a new "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 box “formatted 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 (tabs separate the elements within a record).

Working with Return-Delimited Data Arrays

FileMaker Pro 7 has four new functions that greatly facilitate working with return-delimited data arrays such as the ones just described. These are ValueCount , LeftValues , MiddleValues , and RightValues . 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 , Right ).

Briefly, the syntax of these functions is as follows :

• 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 ending 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 or not; 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.

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 box “formatted 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 that 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 box “formatted 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 re-enter 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 global fields, navigate to a layout based on the CamperSport table, create records, and populate data from the global fields. 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 that the user checked them. There are two ways you can step from element to element in such an array. First, you can 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 scratch field 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. I had a preference for the first method in earlier versions of FileMaker Pro because it was simpler syntactically, but the new "value" functions 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

 Go to Layout ["CamperInfo" (CamperInfo)] Go to Record/Request/Page [First] Loop     Set Field [Globals::gSports; CamperInfo::SportArray]     Loop         Exit Loop If [ValueCount (Globals::gSports) = 0]         Go to Portal Row [Select; Last]         Set Field [CamperSports::Sport; Let (sport = LeftValues (Globals::gSports; 1) ;  Substitute(sport; ""; ""))         Set Field [Globals::gSports; Let (count = ValueCount(Globals::gSports);  RightValues (Globals::gSports; 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 CamperSports table. In the very next line, the gSports field 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 Field [Globals::gCounter; 1]     Loop         Exit Loop If [Globals::gCounter > ValueCount (CamperInfo::SportArray)]         Go to Portal Row [Select; Last]         Set Field [CamperSports::Sport; Let (sport = MiddleValues (CamperInfo:  :SportArray; Globals::gCounter; 1)) ; Substitute(sport; ""; ""))         Set Field [Globals::gCounter; Globals::gCounter  + 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 global counter field.

NOTE

In both cases, notice that the Substitute function has been used to remove the trailing return character generated by the LeftValues and MiddleValues functions.