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.
In FileMaker Pro 8, an "array notation" can be used to refer to data in a repeating field. myField, 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:
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
Part I: Getting Started with FileMaker 8
Using FileMaker Pro
Defining and Working with Fields
Working with Layouts
Part II: Developing Solutions with FileMaker
Relational Database Design
Working with Multiple Tables
Working with Relationships
Getting Started with Calculations
Getting Started with Scripting
Getting Started with Reporting
Part III: Developer Techniques
Developing for Multiuser Deployment
Advanced Interface Techniques
Advanced Calculation Techniques
Advanced Scripting Techniques
Advanced Portal Techniques
Debugging and Troubleshooting
Converting Systems from Previous Versions of FileMaker Pro
Part IV: Data Integration and Publishing
Importing Data into FileMaker Pro
Exporting Data from FileMaker
Instant Web Publishing
FileMaker and Web Services
Custom Web Publishing
Part V: Deploying a FileMaker Solution
Deploying and Extending FileMaker
FileMaker Server and Server Advanced
Documenting Your FileMaker Solutions