3 Replies Latest reply on Mar 26, 2015 2:38 PM by ryan@iiicareer.com

    php api - compound find with AND / OR

    jmires

      Title

      php api - compound find with AND / OR

      Your post

      Hi,


      I'm fairly new to Filemaker, but have a solid background in php. I'm using the php api to search against a members database. All works fine, except I'm having trouble with a find request that needs to limit the result set to only records that have their "Inactive Code" field set to the string "None" or a blank value, in addition to the other search parameters (first name, last name, etc.)

      I currently have all the search criteria in one find request ($findreq1), and then the parts for limiting the result set in separate find requests ($findreq2, $findreq3, etc.). I believe in order to get the AND part of my query to work I need to add my new criterion to $findreq1. This works fine for either checking blank or None individually, such as:

      $findreq1->addFindCriterion('Inactive Code', 'None');

      or

      $findreq1->addFindCriterion('Inactive Code', '==');

      but what I really want to do is something like:

      $findreq1->addFindCriterion('Inactive Code', 'None' OR '==');

      which clearly isn't the right syntax. If I put them in separate find requests, the separate find request is treated as an OR, and I end up getting all the records that match either the search critera (first name, etc.) OR anything with Inactive Code of "None".

      Does anyone have any suggestions on how to accomplish this? I'm sure I'm just either not thinking through the logic correctly, or I haven't been able to track down the right api method to do what I need. I'd be very grateful for any help!

      Full code (for creating the compound find command) is below.

      Many thanks in advance for any assistance!

      *****************************

      <?php 

      $findCommandSearch = $fm->newCompoundFindCommand('php-fields');
      $findreq1 = $fm->newFindRequest('php-fields');
      $findreq2 = $fm->newFindRequest('php-fields');
      $findreq3 = $fm->newFindRequest('php-fields');
      $findreq4 = $fm->newFindRequest('php-fields');

      $findreq1->addFindCriterion('First Name', $firstname);
      $findreq1->addFindCriterion('Last Name', $lastname);
      $findreq1->addFindCriterion('Organization', $org);
      $findreq1->addFindCriterion('City', $city);
      $findreq1->addFindCriterion('State', $state);
      $findreq1->addFindCriterion('Zip', $zip);
      $findreq1->addFindCriterion('Country', $country);

      // show only members who have Inactive Code of "None" or blank
      // @todo THIS NEEDS UPDATING TO INCLUDE BLANKS
      $findreq1->addFindCriterion('Inactive Code', 'None');

      // hide anyone with a blank Member Type from the search results
      $findreq2->addFindCriterion('Member Type', '==');
      $findreq2->setOmit(true);

      // hide "Free" members from regular search results (Admins still see them in results)
      if (!checkAdmin()) {
          $findreq3->addFindCriterion('Member Type', 'Free');
          $findreq3->setOmit(true);
      }

      // Admins have other search fields open to them
      if (checkAdmin()) {
          // add Member ID field to find request
          $findreq1->addFindCriterion('ID', $membid);
         
          // if the Admin has chosen to view only expired members, omit all the current members
          if ($include_expired == "expired") {
              $findreq4->addFindCriterion('Expire Date', '>= '.$current_cutoff_date);
              $findreq4->setOmit(true);

          }

      }

      $findCommandSearch->add(1,$findreq1);
      $findCommandSearch->add(2,$findreq2);
      $findCommandSearch->add(3,$findreq3);
      $findCommandSearch->add(4,$findreq4);
                                             
      $findCommandSearch->setRange($skip,$range);               
      $resultSearch = $findCommandSearch->execute();

      // code continues after this - chopped for ease of reading...

      ?>