Dynamic Compound Finds With FileMaker and PHP

The default behavior for FileMaker’s PHP API find requests is “AND.” Enter multiple criteria in the find request, and FileMaker treats this it as a “Find records where A = ‘this’ and B = ‘that’ in the request. But what happens if you need to search using OR? The PHP API documentation states that you can override the default AND behavior by setting the logical operator:

$find->setLogicalOperator( FILEMAKER_FIND_OR );

The Problem

However, each time I’ve tried this, the OR action didn’t seem to work the way I wanted. To make it more interesting, I didn’t know how many requirements would make up my OR search, as the criteria could change with each request. There might be two conditions, or six, or more. All criteria were stored in an array, and I need to loop through this array and add my criteria, but when I ran it through the loop it only remembered the last option, not each option in the array.

foreach( $itemList as $theItem ) { 
   $find->addFindCriterion( 'field', $theItem ) 
}

The documentation for FileMaker’s custom web publishing points to another option, the compound find. This takes separate find requests and stitches them together into one find. However, every example of the compound find showed static criteria, including FileMaker’s own documentation. I needed my criteria to be more fluid.

Page 35 of the documentation shows the compound find goes somewhat like this (shortened here to core elements):

// Create the Compound Find command object 
$compoundFind = $fm->newCompoundFindCommand('Form View');

// Create first find request
$findreq1 = $fm->newFindRequest('Form View');

// Create second find request
$findreq2 = $fm->newFindRequest('Form View');

// Specify search criterion for first find request
$findreq1->addFindCriterion('Quantity in Stock', '<100');

// Specify search criterion for second find request
$findreq2->addFindCriterion('Quantity in Stock', '0');
$findreq2->setOmit(true);

// Execute compound find command
$result = $compoundFind->execute();

My circumstances required that I find a set of child records, extract unique parent IDs from these, and then perform a find on the parent records based on these IDs. However, I didn’t have just two IDs, but an unknown number. In some instances the search might look at two or three records. In others it might be six, or ten.

Mission Accomplished?

First I ran an initial search to give me the parent IDs in the child table. Given a criteria in the child table I pulled up a list of all parent IDs. I looped through this to get one array with the parent IDs.

foreach ( $records as $record){
  $idList[] = $record->getfield('id_parent');
}
$idList = array_unique($idList);
$idList = array_values($idList);

I needed a unique list since there might be duplicated parent IDs in my found set, so I ran the $idList through the PHP function array_unique. I also needed to reset the keys in the array so they would go back to 0,1,2,3,etc instead of 0,3,5,33,etc. which was accomplished with the PHP function, array_values.

Once I had the final and clean list, I performed my search on the parent layout. In the FileMaker documentation example (see above) the “findreq” variable is numbered 1 and 2 (with additional requests taking numbers 3,4,5 and so on). I needed my $findreq_n to grow based on the array count. You can concatenate numbers to PHP variables, but it requires a little extra effort. What this now allowed was a more fluid and less limiting way to build compound finds. When built to match the documentation example, I ended up with two loops to construct the compound find.

// the parent array
$myList = array('id1', 'id2'); // etc

// Create the Compound Find command object
$compoundFind = $fm->newCompoundFindCommand('parent_layout');

$i = 0;
while ($i <= count($myList)):
  ${'findreq' . $i} = $fm->newFindRequest('parent_layout');
  ${'findreq' . $i}->addFindCriterion('id', $myList[$i]);
  $i++;
endwhile;

$j = 0;
while ($j <= count($myList)):
  $compoundFind->add($j,${'findreq' . $j});
  $j++;
endwhile;

// Execute compound find command
$result = $compoundFind->execute();

The use of such “variable variables” is sometimes discouraged, given potential problems. However, in this case the variables worked, whereas previous attempts to get the logical operator OR to work either within a for each loop or without any loop failed.

While this also allows for unlimited OR criteria, for performance issues this isn’t always a good idea. However, if you know the count of the parent array, you can always limit the action based on the count. One choice might be to check if it exceeds a certain number and then re-think the search options.

Let’s Optimize!

The above while loops seems acceptable, but redundant. Furthermore, what if we want multiple criteria in the find? It seems that a cleaner solution is offered by the foreach loop.

$i = 0;
foreach($myList as $myItem ){
  ${'findreq' . $i} = $fm->newFindRequest('parent_layout');

  // add the criteria to the request
  ${'findreq' . $i}->addFindCriterion('Location', 'Some Condition'); // second criteria
  ${'findreq' . $i}->addFindCriterion('id', $myItem); //original ID

  // Add find requests to compound find command	
  $compoundFind->add($i,${'findreq' . $i});
  $i++;
}

This method removes the need for two while loops that separate the addFindCriterion() and add() methods, reads much better and ends up with cleaner code.

Conclusion

Using compound finds provides an alternative to the set logical operator method, and even though there are more lines of code, it appears to work fairly quickly. Hopefully, if you ever have run into the need for compound finds, you might this dynamic method useful, especially as a result of form-submitted data.