This article originally appeared in our old Get ( Answers ) newsletter. I recently heard again from Marcelo, announcing his new consulting company and FileMaker 11 certification (congrats!), and thought I should update this. With some new features introduced in FileMaker 10, this technique is even easier to implement.
The Challenge
In April 2007, our reader Marcelo asked a question regarding the best way of handling checkbox sets in Find mode. Many users will interpret checking multiple items as an “OR” search. For example, let’s say you need to search for sales leads in preparation for a trip to the West Coast. You might instinctively enter Find mode, fill the status field with lead and then, in the state field, check California, Oregon, and Washington. What you really need to do is create three separate requests, one for each state.
Well, we came up with a scripted solution for creating all the combinations with two checkbox sets, and Marcelo ran with it, sending us back a sample script that could be easily extended to encompass three, four, five, or more checkbox sets. Merely copy and paste a set of script steps inside a loop, tweak some calculations, and repoint a few Set Field steps, and Marcelo’s script could create a record or find request for any number of combinations of values (paragraphs).
And that got me thinking about how to script it for any number of possible fields with checkboxes. Here is the solution I came up with, updated for the Set Field by Name script step in FileMaker 10 and 11.
The script will contain no references to actual tables, fields, layouts, or custom functions, working entirely with local variables so that it’s completely portable. It can be pointed at a layout with the following fields and values:
The script will contain no references to actual tables, fields, layouts, or custom functions, working entirely with local variables so that it’s completely portable. It can be pointed at a layout with the following fields and values:
| Color | Size |
|---|---|
| Red¶ Blue¶ Yellow |
Small¶ Medium¶ Large |
to create the nine records with the values:
| Color | Size |
|---|---|
| Red | Small |
| Blue | Small |
| Yellow | Small |
| Red | Medium |
| Blue | Medium |
| Yellow | Medium |
| Red | Large |
| Blue | Large |
| Yellow | Large |
The same script, without any modification, can be run on a layout with the fields and values:
| Beverage | Size | Strength |
|---|---|---|
| Coffee¶ Latte¶ Cappuccino |
Small¶ Large |
Regular¶ Decaf |
and result in twelve records, one for each combination:
| Beverage | Size | Strength |
|---|---|---|
| Coffee | Small | Regular |
| Latte | Small | Regular |
| Cappuccino | Small | Regular |
| Coffee | Large | Regular |
| Latte | Large | Regular |
| Cappuccino | Large | Regular |
| Coffee | Small | Decaf |
| Latte | Small | Decaf |
| Cappuccino | Small | Decaf |
| Coffee | Large | Decaf |
| Latte | Large | Decaf |
| Cappuccino | Large | Decaf |
Here’s how it works:
Getting the Fields
While the script can be copied and pasted into any database and do its thing, there is a requirement that the fields to be used to create combinations exist on the current layout. We’re going to inspect every field that appears on the layout; if it contains two or more values, we’ll store the name of the field (and associated table), its contents, and the number of values in that content.
We start our script by storing the name of the current layout’s underlying table and the names and number of fields on the layout.
Set Variable [$currentTable; Value: Get ( LayoutTableName )]
Set Variable [$allFields; Value: FieldNames ( Get ( FileName ) ; Get ( LayoutName ) )]
Set Variable [$numberOfFields; Value: ValueCount ($allFields)]
Using $i as our counter, we then loop through each of the values in $allFields, checking if it has more than one value.
Set Variable [$i; Value: 0]
Set Variable [$numberOfMultiValues; Value: 0]
Loop
Set Variable [$i; Value: $i + 1]
Exit Loop If [$i > $numberOfFields]
Set Variable [$thisField; Value: GetValue($allFields; $i)]
Set Variable [$thisData; Value: Evaluate ( $thisField )]
If [ValueCount ( $thisData ) > 1]
Set Variable [$numberOfMultiValues; $numberOfMultiValues + 1]
#
# record the field and its contents
#
End If
Exit Loop
Okay, if the field should be an input for our combinations, we’ll record the name of the field, its content, and the number of values in the content. These will be store in three repeating variables, each using the current count of fields as the repetition number.
Set Variable [$fieldNames[$numberOfMultiValues]; Value: $thisField]
Set Variable [$fieldData[$numberOfMultiValues]; Value: $thisData]
Set Variable [$fieldDataCount[$numberOfMultiValues]; Value: ValueCount($thisData)]
Putting it all together, our script looks like this:
Set Variable [$currentTable; Value: Get ( LayoutTableName )]
Set Variable [$allFields; Value: FieldNames ( Get ( FileName ) ; Get ( LayoutName ) )]
Set Variable [$numberOfFields; Value: ValueCount ($allFields)]
#
Set Variable [$i; Value: 0]
Set Variable [$numberOfMultiValues; Value: 0]
Loop
Set Variable [$i; Value: $i + 1]
Exit Loop If [$i > $numberOfFields]
Set Variable [$thisField; Value: GetValue($allFields; $i)]
Set Variable [$thisData; Value: Evaluate ( $thisField )]
If [ValueCount ( $thisData ) > 1]
Set Variable [$numberOfMultiValues; $numberOfMultiValues + 1]
#
# record the field and its contents
Set Variable [$fieldNames[$numberOfMultiValues]; Value: $thisField]
Set Variable [$fieldData[$numberOfMultiValues]; Value: $thisData]
Set Variable [$fieldDataCount[$numberOfMultiValues]; Value: ValueCount($thisData)]
#
End If
Exit Loop
To finish this section of the script, we’ll make sure we found at least one field with multiple values, and if not, abort the script.
If [$numberOfMultiValues = 0]
Exit Script []
End If
The Combination Martix
Once we know how many fields we have to process, and the number of values in each, we need to create a matrix of possibilities. Take a look back at the coffee example above. Given three different beverages, two sizes, and two strengths, we can say that the first combination is the beverage 1, size 1, and strength 1. Second possibility is beverage 2, size 1, and strength 1. And so on. If we look at it as columns and rows, where each row is a possibility, each column is a repetition in out variable $fieldNames, and each entry is the number of the value to be used, we get:
1 1 1
2 1 1
3 1 1
1 2 1
2 2 1
3 2 1
1 1 2
2 1 2
3 1 2
1 2 2
2 2 2
3 2 2
Here’s the section of the script to create our variable, $matrix:
Set Variable: [$matrix; Value:¶]
Set Variable: [$col; Value:0]
Loop
Set Variable: [$col; Value:$col + 1]
Exit Loop If [$col > $numberOfMultiValues]
#
# stop if we've processed all fields
#
Set Variable [$temp; Value:""]
Set Variable [$i; Value: 0]
Set Variable [$N; Value:$fieldDataCount[$col]
#
# looping $i from 1 to the $N number of values, replace each cariage return
# in the current $matrix with $i, appending each time in $temp
#
Loop
Set Variable [$i; $i + 1]
Exit Loop If [$i > $N]
Set Variable [$temp; Value:$temp & Substitute ($matrix ; ¶ ; $i & " " & ¶)]
End Loop
#
# finally, replace the current $matrix with $temp
#
Set Variable [$matrix; Value: $temp]
End Loop
Huh? Essentially, the innermost loop builds up our matrix one column at a time. When it runs for the first repetition in $fieldDataCount, which has a value of 3, we’re in effect setting $temp to:
Substitute ($matrix ; ¶ ; 1 & " " & ¶)
&
Substitute ($matrix ; ¶ ; 2 & " " & ¶)
&
Substitute ($matrix ; ¶ ; 3 & " " & ¶)
which give us:
1 ¶
2 ¶
3 ¶
For the next repetition in $fieldDataCount, which in this case has a value of 2, the combined loops set $temp to
Substitute ($matrix ; ¶ ; 1 & " " & ¶)
&
Substitute ($matrix ; ¶ ; 2 & " " & ¶)
which gives us:
1 1 ¶
2 1 ¶
3 1 ¶
1 2 ¶
2 2 ¶
3 2 ¶
For the final inner loop, for $fieldDataCount[3], which again has a value of 2, we get again:
Substitute ($matrix ; ¶ ; 1 & " " & ¶)
&
Substitute ($matrix ; ¶ ; 2 & " " & ¶)
which gives us:
1 1 1 ¶
2 1 1 ¶
3 1 1 ¶
1 2 1 ¶
2 2 1 ¶
3 2 1 ¶
1 1 2 ¶
2 1 2 ¶
3 1 2 ¶
1 2 2 ¶
2 2 2 ¶
3 2 2 ¶
With this final $matrix, we are ready to create the combinations.
Creating the Records (or Requests)
We start by duplicating the original record for as many total combinations exist, which is the number of rows in$matrix.
Set Variable [$i; Value:ValueCount($matrix)]
Show All Records
Omit Record
Show Ommitted Only
Loop
Set Variable [$i; Value:$i - 1]
Exit Loop If [$i = 0]
Duplicate Record/Request
End Loop
Next, we loop through each record, using its current record number to choose a row from our $matrix.
Go to Record/Request/Page [First]
Loop
Set Variable [$row; Value:GetValue( $matrix ; Get(RecordNumber) )]
#
# here we'll process the fields in each record
#
Go to Record/Request/Page [Next; Exit after last]
End Loop
When we’re on each record, we’ll loop through each field that contained multiple values, getting the name of the field from $fieldNames and the original content of the field from $fieldData. We’ll extract a value from $fieldDatausing the appropriate entry in our matrix row using the MiddleWords function, then set the field using the Set Field by Name step. When we use the Set Field by Name step, we need to make sure that we use as the target field the fully qualified field name, with table name. The original FieldNames function we used doesn’t include the table name if it comes from the current table, so we add that in where necessary.
Set Variable [$i; Value:0]
Loop
Set Variable [$i; Value:$i + 1]
Exit Loop If [$i > $numberOfMultiValues]
Set Variable [$targetField; Value:$fieldNames[$i]]
If [PatternCount ( $targetField ; "::") = 0]
Set Variable [$targetField; Value:$currentTable & "::" & $targetField]
End If
Set Variable [$targetData; Value:Let ( [entry = MiddleWords ( $row ; $i ; 1 ) ; data = $fieldData[$i] ] ; GetValue ( data ; entry ) )]
Set Field by Name [$targetField; $targetData]
End Loop
Here’s that formula for $targetData, formatted more clearly:
Let (
[
entry = MiddleWords ( $row ; $i ; 1 ) ;
data = $fieldData[$i]
] ;
GetValue ( data ; entry )
)
Putting these two routines together to process each record, we get:
Go to Record/Request/Page [First]
Loop
Set Variable [$row; Value:GetValue( $matrix ; Get(RecordNumber) )]
#
# here we'll process the fields in each record
Set Variable [$i; Value:0]
Loop
Set Variable [$i; Value:$i + 1]
Exit Loop If [$i > $numberOfMultiValues]
Set Variable [$targetField; Value:$fieldNames[$i]]
If [PatternCount ( $targetField ; "::") = 0]
Set Variable [$targetField; Value:$currentTable & "::" & $targetField]
End If
Set Variable [$targetData; Value:Let ( [entry = MiddleWords ( $row ; $i ; 1 ) ; data = $fieldData[$i] ] ; GetValue ( data ; entry ) )]
Set Field by Name [$targetField; $targetData]
End Loop
#
Go to Record/Request/Page [Next; Exit after last]
End Loop
The Combinations Script
Whew! Let’s put it ALL together now, adding a Freeze Window step to make sure it runs more quickly:
Freeze Window
Set Variable [$currentTable; Value: Get ( LayoutTableName )]
Set Variable [$allFields; Value: FieldNames ( Get ( FileName ) ; Get ( LayoutName ) )]
Set Variable [$numberOfFields; Value: ValueCount ($allFields)]
#
Set Variable [$i; Value: 0]
Set Variable [$numberOfMultiValues; Value: 0]
Loop
Set Variable [$i; Value: $i + 1]
Exit Loop If [$i > $numberOfFields]
Set Variable [$thisField; Value: GetValue($allFields; $i)]
Set Variable [$thisData; Value: Evaluate ( $thisField )]
If [ValueCount ( $thisData ) > 1]
Set Variable [$numberOfMultiValues; $numberOfMultiValues + 1]
#
# record the field and its contents
Set Variable [$fieldNames[$numberOfMultiValues]; Value: $thisField]
Set Variable [$fieldData[$numberOfMultiValues]; Value: $thisData]
Set Variable [$fieldDataCount[$numberOfMultiValues]; Value: ValueCount($thisData)]
#
End If
Exit Loop
If [$numberOfMultiValues = 0]
Exit Script []
End If
#
#
# create the matrix
#
Set Variable: [$matrix; Value:¶]
Set Variable: [$col; Value:0]
Loop
Set Variable: [$col; Value:$col + 1]
Exit Loop If [$col > $numberOfMultiValues]
#
# stop if we've processed all fields
#
Set Variable [$temp; Value:""]
Set Variable [$i; Value: 0]
Set Variable [$N; Value:$fieldDataCount[$col]
#
# looping $i from 1 to the $N number of values, replace each cariage return
# in the current $matrix with $i, appending each time in $temp
#
Loop
Set Variable [$i; $i + 1]
Exit Loop If [$i > $N]
Set Variable [$temp; Value:$temp & Substitute ($matrix ; ¶ ; $i & " " & ¶)]
End Loop
#
# finally, replace the current $matrix with $temp
#
Set Variable [$matrix; Value: $temp]
End Loop
#
#
# duplicate records for each combination
#
Set Variable [$i; Value:ValueCount($matrix)]
Show All Records
Omit Record
Show Ommitted Only
Loop
Set Variable [$i; Value:$i - 1]
Exit Loop If [$i = 0]
Duplicate Record/Request
End Loop
#
#
# loop through each record and set the fields
#
Go to Record/Request/Page [First]
Loop
Set Variable [$row; Value:GetValue( $matrix ; Get(RecordNumber) )]
#
# here we'll process the fields in each record
Set Variable [$i; Value:0]
Loop
Set Variable [$i; Value:$i + 1]
Exit Loop If [$i > $numberOfMultiValues]
Set Variable [$targetField; Value:$fieldNames[$i]]
If [PatternCount ( $targetField ; "::") = 0]
Set Variable [$targetField; Value:$currentTable & "::" & $targetField]
End If
Set Variable [$targetData; Value:Let ( [entry = MiddleWords ( $row ; $i ; 1 ) ; data = $fieldData[$i] ] ; GetValue ( data ; entry ) )]
Set Field by Name [$targetField; $targetData]
End Loop
#
Go to Record/Request/Page [Next; Exit after last]
End Loop
Conclusion
But the beauty of this script we never need to write it again. We can copy this script into any file, create a layout with proper fields, and it should just work, for any table and any number of fields.
| Download Sample File |
