3 Replies Latest reply on Jun 12, 2013 6:27 AM by mbraendle

    Does FileMaker API support multi-key fields

    davidrcrowe

      The FileMaker client supports what are called "multi-key fields" by FileMaker:

       

      http://www.filemaker.com/help/html/glossary.html#1027937

       

      This is simply a single field which contains multiple values separated by carriage returns, which is part of a relationship with another table.

       

      So if one field in one record contains:

      red

      blue

      green

       

      That one record could match multiple records in another table which contain, in the relationship field, red, blue or green.

       

      My question is whether the FileMaker API supports this?

       

      I have used an HTML <textarea> field, and it retrieves no records if there is more than one line in it. I have changed the carriage return (ASCII 10) to the FileMaker line delimiter (ASCII 13), to no avail.

       

      Am I using the wrong character? Does this work with the PHP API?

        • 1. Re: Does FileMaker API support multi-key fields
          RobWestergaard

          I think what you need to do is turn the contents of the textarea field into an array using the PHP explode() function. Then, when you create your FileMaker PHP find, use the newCompoundFindCommand(), loop through the array values and create an addFindCriterion() for each array value. This should create a find that searches for each value in the textarea field as a logical "OR". The following is off the top of my head, so check the syntax (and the value of the delimiter used in the explode() function):

           

          <?php

               $layoutName = 'yourSearchLayoutName';

               $fieldMultikeyContents = $record->getField('yourTextareaFieldName');

               $findArray = explode("\r", $fieldMultikeyContents);

           

               $request = $fm->newCompoundFindCommand($layoutName);

               foreach($findArray as $key => $value) {

                    ${'findreq' . $key} = $fm->newFindRequest($layoutName);

                    ${'findreq' . $key}->addFindCriterion('nameOfFieldToSearch', $value);

                    $request->add($key,${'findreq' . $key});

               }

           

               $result = $request->execute();

          ?>

          • 2. Re: Does FileMaker API support multi-key fields
            davidrcrowe

            Rob;

             

            Thanks, but it's not so simple. If I have multiple multi-key fields, then I have to do a nested loop of find commands, so using the built-in filemaker capability would be very helpful ... if it works.

             

            I am already using compound find commands for other reasons, so using that capability is not the problem.

             

             

            Imagine if one field has Red/Yellow/Blue and another has New York/Dallas/San Francisco and another has Coffee/Pizza/Sandwich ... that's already 9 separate queries that would be necessary. If multi-key finds were supported, that would be one query.

            • 3. Re: Does FileMaker API support multi-key fields
              mbraendle

              You posted the question in another thread and obtained from both threads the same, valid answer with a viable solution. Which you decline. And you didn't show your PHP code, as I demanded, so that we can better help you.

               

              Another viewpoint on this:

               

              1. Reconsider the data structure in your file. It is generally advisable to use join tables instead of multiline keys and numeric IDs instead of named keys.

              2. textarea is used normally to submit or display large bunches of text and less for searching. Consider using <input type="text" ...> instead.

              3. setLogicalOperator(FILEMAKER_FIND_OR) might be your friend, but I'm not sure whether the PHP API  translates this to a correct XML query if the same field is used for all find terms.