5 Replies Latest reply on Dec 15, 2013 1:28 PM by jmw

    related table fields data not displaying - filemaker php api

    WelbyObeng

      Title

      related table fields data not displaying - filemaker php api

      Your post

           I have layout called (studentinfo). Studentinfo layout show records from "STUDENTS" table from database. when I run the code below I get all fields data expect:
            
           inq_FAM Family 1 [c]::BB_studentID
           inq_FAM Family 1 [c]::BB_ParentA_ID
           inq_FAM Family 1 [c]::Pa_first
           inq_FAM Family 1 [c]::Pa_last
           inq_FAM Family 1 [c]::BB_ParentB_ID
           inq_FAM Family 1 [c]::Pb_first
           inq_FAM Family 1 [c]::Pb_last
           inq_FAM Family 2 [c]::BB_ParentA_ID
           inq_FAM Family 2 [c]::Pa_first
           inq_FAM Family 2 [c]::Pa_last P2b_relation
           inq_FAM Family 2 [c]::BB_ParentB_ID
           inq_FAM Family 2 [c]::Pb_first
           inq_FAM Family 2 [c]::Pb_last
            
           On filemaker pro I noticed the ones that are showing have data displaying from:
            
            
           STUDENTS::NameFirst
           STUDENTS::NameLast
           STUDENTS::Sex
           STUDENTS::Grade_current
           STUDENTS::Adm_enr_status
           STUDENTS::P1a_relation
           STUDENTS::P1b_relation
           STUDENTS::P2a_relation
           STUDENTS::P2b_relation
            
            
            
           How do I get those fields to show? I am using filemaker pro server 11
            
      <?php
      include('config.php');
      
      ?>
      <style>
              .dataTable td{
                      border-bottom:1px solid #ccc;
                      border-right:1px solid #ccc;
                      padding:1px;
              }
      </style>
      <?
      
      $findCommand =&$fm->newPerformScriptCommand('studentinfo','Find Enrolled');
      
      // Execute find command
      $result = $findCommand->execute();
      
      
      if (FileMaker::isError($result)) {
          echo "Error: " . $result->getMessage() . "\n";
          exit;
      }
      
      
      // Get array of found records
      $records = $result->getRecords();
      
      echo  '<table width="100%" border="0" class="dataTable" cellspacing="0">';
      echo "<tr><td>BB_studentID</td><td>NameFirst</td><td>NameLast</td><td>Sex</td><td>Grade_current</td><td>Adm_enr_status</td>
      <td>P1a_relation</td><td>BB_ParentA_ID</td><td>Pa_first</td><td>Pa_last</td>
      <td>P1b_relation</td><td>BB_ParentB_ID</td><td>Pb_first</td><td>Pb_last</td>
      <td>P2a_relation</td><td>BB_ParentA_ID</td><td>Pa_first</td><td>Pa_last</td>
      <td>P2b_relation</td><td>BB_ParentB_ID</td><td>Pb_first</td><td>Pb_last</td>
      </tr>";
      
      foreach ($records as $record) {
      
                  echo "<tr style=''>";
                  echo "<td> " . $record->getField('BB_studentID') . "</td>";
                  echo "<td> " . $record->getField('NameFirst') . "</td>";
                  echo "<td> " . $record->getField('NameLast') . "</td>";
                  echo "<td> " . $record->getField('Sex') . "</td>";
                  echo "<td> " . $record->getField('Grade_current') . "</td>";
                  echo "<td> " . $record->getField('Adm_enr_status') . "</td>";
                  echo "<td> " . $record->getField('P1a_relation') . "</td>";
                  echo "<td> " . $record->getField('inq_FAM Family 1 [c]::BB_ParentA_ID') . "</td>";
                  echo "<td> " . $record->getField('inq_FAM Family 1 [c]::Pa_first') . "</td>";
                  echo "<td> " . $record->getField('inq_FAM Family 1 [c]::Pa_last') . "</td>";
                  
                  echo "<td> " . $record->getField('P1b_relation') . "</td>";
                  echo "<td> " . $record->getField('inq_FAM Family 1 [c]::BB_ParentB_ID') . "</td>";
                  echo "<td> " . $record->getField('inq_FAM Family 1 [c]::Pb_first') . "</td>";
                  echo "<td> " . $record->getField('inq_FAM Family 1 [c]::Pb_last') . "</td>";
                  
                  echo "<td> " . $record->getField('P2a_relation') . "</td>";
                  echo "<td> " . $record->getField('inq_FAM Family 2 [c]::BB_ParentA_ID') . "</td>";
                  echo "<td> " . $record->getField('inq_FAM Family 2 [c]::Pa_first') . "</td>";
                  echo "<td> " . $record->getField('inq_FAM Family 2 [c]::Pa_last') . "</td>";
      
                      echo "<td> " . $record->getField('P2b_relation') . "</td>";
                  echo "<td> " . $record->getField('inq_FAM Family 2 [c]::BB_ParentB_ID') . "</td>";
                  echo "<td> " . $record->getField('inq_FAM Family 2 [c]::Pb_first') . "</td>";
                  echo "<td> " . $record->getField('inq_FAM Family 2 [c]::Pb_last') . "</td>";
      
                  echo "</tr>";
                  $row++;
      }
      echo "</table>";
      
      echo '<pre>';
      //print_r($records);
      echo '</pre>';
      
      ?>

            

        • 1. Re: related table fields data not displaying - filemaker php api
          jmw

               First off; what exactly is not working? Do I understand correctly that in the PHP generated code you do get several students but for each student the parent info comes from the first record of the STUDENTS table, regardless of the actual database-relation? If so, does the relation info display correctly in FileMaker itself for each record if you use that script? Are all related fields present on the layout? If it does display correctly on the layout itself then I would expect the same results using the PHP code.  Something I noticed using scripts is that it is (sometimes) important to end the script on the same layout it started.

               I also noticed that the record set is generated using a script, any particular reason for this instead of a standard PHP find request? The reason I ask is that I use the PHP find request often and that would display the related records (from both portal and fields on the layout), so I would expect that to work. 

                

          • 2. Re: related table fields data not displaying - filemaker php api
            WelbyObeng
                      Look at it this way...
                       
                      I went to filemaker pro, connected to the server and created a layout called (studentinfo). It asked me what table to select record from so I select to show records from "STUDENTS" table.
                       
                      When I run my script in the above post,  the php return  all students who are enrolled.
                       
                       
                      For the fields below I see the data
                       
                       
                      echo "<td> " . $record->getField('BB_studentID') . "</td>";
                      echo "<td> " . $record->getField('NameFirst') . "</td>";
                      echo "<td> " . $record->getField('NameLast') . "</td>";
                      echo "<td> " . $record->getField('Sex') . "</td>";
                      echo "<td> " . $record->getField('Grade_current') . "</td>";
                      echo "<td> " . $record->getField('Adm_enr_status') . "</td>";
                      echo "<td> " . $record->getField('P1a_relation') . "</td>";
                      echo "<td> " . $record->getField('P1b_relation') . "</td>";
                      echo "<td> " . $record->getField('P2a_relation') . "</td>";
                      echo "<td> " . $record->getField('P2b_relation') . "</td>";
                       
                       
            For the fields below I DONT see the data in my php script. However In filemaker pro I see all fields with data….what am I doing wrong in my script? 
                       
                      echo "<td> " . $record->getField('inq_FAM Family 1 [c]::BB_ParentA_ID') . "</td>";
                      echo "<td> " . $record->getField('inq_FAM Family 1 [c]::Pa_first') . "</td>";
                      echo "<td> " . $record->getField('inq_FAM Family 1 [c]::Pa_last') . "</td>";
                      echo "<td> " . $record->getField('inq_FAM Family 1 [c]::BB_ParentB_ID') . "</td>";
                      echo "<td> " . $record->getField('inq_FAM Family 1 [c]::Pb_first') . "</td>";
                      echo "<td> " . $record->getField('inq_FAM Family 1 [c]::Pb_last') . "</td>";
                      echo "<td> " . $record->getField('inq_FAM Family 2 [c]::BB_ParentA_ID') . "</td>";
                      echo "<td> " . $record->getField('inq_FAM Family 2 [c]::Pa_first') . "</td>";
                      echo "<td> " . $record->getField('inq_FAM Family 2 [c]::Pa_last') . "</td>";
                      echo "<td> " . $record->getField('inq_FAM Family 2 [c]::BB_ParentB_ID') . "</td>";
                      echo "<td> " . $record->getField('inq_FAM Family 2 [c]::Pb_first') . "</td>";
                      echo "<td> " . $record->getField('inq_FAM Family 2 [c]::Pb_last') . "</td>";
                  
                  

                  

            • 3. Re: related table fields data not displaying - filemaker php api
              jmw

                   Okay, couple of remarks here. First off, the PHP code can only return fields which are on the layout. So: are the related fields listed on the layout? And again: if you run the script in FileMaker, does each student record correctly display the related parents fields on the layout studentinfo? If it does then your script seems to be okay. If it does not, make the script work in FileMaker. Again, make sure that all fields the PHP needs to access are on the layout, otherwise it will not work. 

                   Secondly (just to make sure), be aware that the PHP code does not know (or care) on which layout you work using FileMaker; as far as FM Server is concerned the both of you are two entirely different users each accessing different records. Also: unless you have specific reasons for having a script to select enrolled student records, I would also recommend the standard PHP findrequest to select records. Maybe if only to see if the PHP code then does work. 

                   In my experience it is most often that I don't have a field listed on the layout if it shows unexpected data using PHP. Well, apart from relationship errors that is. 

                    

              • 4. Re: related table fields data not displaying - filemaker php api
                WelbyObeng

                     1. yes fields are on the layout.

                     2. when i run the script i see  student record correctly displayed with related parents fields on the layout

                3. i use the same login on filemaker pro and php script

                4. what do you mean: I would also recommend the standard PHP findrequest to select records

                • 5. Re: related table fields data not displaying - filemaker php api
                  jmw

                       Ok, from that setup I would expect the parents-info to correctly display using the PHP code. 

                       I would like to suggest to use a standard PHP find request to display a student, if only just for testing purposes. With this I mean the FileMaker PHP command newFindCommand instead of the performScript commandThe code will look something like this, please adjust for your table and field names. Have a look the  PHP reference and tutorial for more detailed info on the function

                  $findCommand = $fm->newFindCommand('studentinfo');
                       $findCommand->addFindCriterion('BB_studentID', 1234); // use something specific to select just one known student for easy testing
                       $result = $findCommand->execute();

                       This should give you a record of a student using the layout studentinfo. All fields on that layout will be returned to the PHP code with valid data, especially since you use the same login credentials. I use this quite a lot and it does work, also on related records whether in a portal or not. I am not sure if that is the case for the records returned by the performScript command, which I suggest this method.

                       If you could try this and let me know what happens we can work from there.