getRelatedSet How To?

[RCooke71]

Hello FM Forum,

I am new to FileMaker & FileMaker PHP API. I am however a longtime PHP/SQL user. I have a FM Server installed on my network as well as a Linux webserver with PHP. On the webserver I have dozens of PHP applications that access as many datasources including MySQL, MS-SQL, FoxPro, Access, and Oracle. I am surely expecting to have a need to access the FM data at some point. Right now I am teaching myself. I have come upon a problem where I need some expert advice.

I have an FM application in which I have created a "Header" layout. Inside of the "Header" layout I have created a "SubRecords". The standard one-to-many kind of relationship. Each header record can have many sub-records.

I have written a PHP script that connects to the FM and fetched a header record based on a value passed in via URL. I can't seem to figure out how to get that script to fetch all of the sub-records for the header. Here is the code I have so far, omitting any attempts I have made to fetch related records:

require_once ('FileMaker.php');

$fm = new FileMaker('MyFMApplication');

$fm->setProperty('hostspec', 'MyFMServer');

$fm->setProperty('username', 'MyUSername');

$fm->setProperty('password', 'MyPassword');

$findCommand =& $fm->newFindCommand('MyHeaderLayout');

$findCommand->addFindCriterion('MySearchField', $_GET['search_string'] );

$findCommand->addSortRule('MySortField',1, FILEMAKER_SORT_ASCEND );

$result = $findCommand->execute();

if (FileMaker::isError($result)) {

printf("Error %s: %s\n", $result->getCode());

"<br>";

printf($result->getMessage());

exit;

}

$records = $result->getRecords();

$myFormattedDataArray = array();

$record_count = 0;

foreach($records as $record){

$flds = $record->getFields();

$myFormattedDataArray[$record_count]['record_id'] = $record->getRecordId();

foreach($flds as $field_name){

$myFormattedDataArray[$record_count][$field_name] = $record->getField($field_name);

}

}

/*

Right here is where I would usually put a fetch sub-record routine if I were using an SQL database.

$myFormattedDataArray[$record_count]['SubRecords'] = fetchSubRecordsRoutine( $header_record_id );

*/

$record_count++;

}

echo "<pre>";

print_r( $myFormattedDataArray ); //Should print a pretty formatted array of my target data.

echo "</pre>";

Thanks

[doughemi]

When I ran into a similar situation, I was advised to have the php script extract the data directly from the child table with find commands. I have used that method since with good results.

[Genx]

Presuming your Header layout has a portal on it attached a Table Occurrance called "HeaderRelatedRecs":

.

.

.foreach($records as $record){

    $relatedRecs = $record->getRelatedSet("HeaderRelatedRecs");

    if( !FileMaker::isError($relatedRecs) ){

        foreach($relatedRecs as $relatedRec){

            $myFormattedSubRecDataArray = array();

            $myFormattedSubRecDataArray['record_id'] = $relatedRec->getRecordId();

            $myFormattedSubRecDataArray['my_field'] = $relatedRec->getField("my_field");

            $myFormattedDataArray[$record_count]['HeaderRelatedRecs'][] = $myFormattedSubRecDataArray;

        }

    }

}

.

.

.

[RCooke71]

Both are excellent suggestions. Thanks!

[RCooke71]

When I attempt the method suggested by Genx I get an error message that says something like 'Related set "SubRecords" not present. If I do a getRelatedSets() on the Layout object, the SubRecords related set is returned in the list.

Talk about confused!

[Genx]

Hard to tell without any code.

Most likely a security issue - does the account you're using have Full Access?

[RCooke71]

I am using the full access administrator account.

Here is the complete code:

require_once ('FileMaker.php');

$fm = new FileMaker('MyDatabase');

$fm->setProperty('hostspec', 'http://MyFMServer');

$fm->setProperty('username', 'MyAdminUserName');

$fm->setProperty('password', 'MyAdminPassword');

$findCommand =& $fm->newFindCommand('MyMainLayoutName');

$findCommand->addFindCriterion('MySearchField', $_GET['MySearchString'] );

$findCommand->addSortRule('MySortField',1, FILEMAKER_SORT_ASCEND );

$result = $findCommand->execute();

//If an error is found, return a message and exit.

if (FileMaker::isError($result)) {

printf("Error %s: %s\n", $result->getCode());

"<br>";

printf($result->getMessage());

exit;

}

$records = $result->getRecords(); //Get the records

//Show me the related sets available for the result...just so I can see what is available

$relatedSets = $result->getRelatedSets();

if( FileMaker::isError($relatedSets) ){

printf("Error %s: %s\n", $relatedSets->getCode());

"<br>";

printf( $relatedSets->getMessage( ) );

exit;

} else{

echo "<pre>\n";

print_r( $relatedSets ); //This shoes that 'RelatedSetName' is available as a related set

echo "</pre>\n";

}

$record_count = 0; //Start record counter

$mydata = array(); //Create holding array for formatted data

foreach($records as $record){

$flds = $record->getFields();

$mydata[$record_count]['record_id'] = $record->getRecordId();

foreach($flds as $field_name){ //I loop through all available fields and buid a simple array

$mydata[$record_count][$field_name] = $record->getField($field_name);

}

//Try to get related records

if( $record_count == 0){ //I only want to get related records on the first pass

//Establish a holding location for related records

$mydata['RelatedRecords'] = array();//Establish the array element so that it shows up regardless of success

$relatedRecs = $record->getRelatedSet( "RelatedSetName" );

if( !FileMaker::isError($relatedRecs) ){

foreach($relatedRecs as $relatedRec){

$relRecords = array();

$relRecords['record_id'] = $relatedRec->getRecordId();

$relRecords_fields = $relatedRec->getFields();

foreach($relRecords_fields as $relRecords_field){//loop through related set fields and build another simple array

$relRecords[$relRecords_field] = $relatedRec->getField( $relRecords_field );

}

$mydata['RelatedRecords'][ $relRecords['record_id'] ] = $relRecords;

}

} else{

printf("Error %s: %s\n", $relatedRecs->getCode());

"<br>";

printf($relatedRecs->getMessage()); //This says that 'RelatedSetName' is not present ???

exit;

}

}

$record_count++;

}

//Show me what we have accomplished

echo "<pre>\n";

print_r( $mydata ); //This displays the formatted data array from my primary search, including the empty 'RelatedRecords' element

echo "</pre>\n";

[Genx]

... Super hard to read the above - would be helpful if you used the code tags provided in the editor.

1) Why is your related recs loop outside your record loop? In the above you're getting the related records only for the very last record... is that what you want?

2) What's the actual error code being returned... i.e. $relatedRecs->getCode() ?

[RCooke71]

require_once ('FileMaker.php');

$fm = new FileMaker('MyDatabase');

$fm->setProperty('hostspec', 'http://MyFMServer');

$fm->setProperty('username', 'MyAdminUserName');

$fm->setProperty('password', 'MyAdminPassword');

$findCommand =& $fm->newFindCommand('MyMainLayoutName');

$findCommand->addFindCriterion('MySearchField', $_GET['MySearchString'] );

$findCommand->addSortRule('MySortField',1, FILEMAKER_SORT_ASCEND );

$result = $findCommand->execute();

//If an error is found, return a message and exit.

if (FileMaker::isError($result)) {

printf("Error %s: %s\n", $result->getCode());

"<br>";

printf($result->getMessage());

exit;

}

$records = $result->getRecords(); //Get the records

//Show me the related sets available for the result...just so I can see what is available

$relatedSets = $result->getRelatedSets();

if( FileMaker::isError($relatedSets) ){

printf("Error %s: %s\n", $relatedSets->getCode());

"<br>";

printf( $relatedSets->getMessage( ) );

exit;

} else{

echo "<pre>\n";

print_r( $relatedSets ); //This shoes that 'RelatedSetName' is available as a related set

echo "</pre>\n";

}



$record_count = 0; //Start record counter

$mydata = array(); //Create holding array for formatted data

foreach($records as $record){

$flds = $record->getFields();

$mydata[$record_count]['record_id'] = $record->getRecordId();

foreach($flds as $field_name){ //I loop through all available fields and buid a simple array

$mydata[$record_count][$field_name] = $record->getField($field_name);

}

//Try to get related records

if( $record_count == 0){ //I only want to get related records on the first pass

//Establish a holding location for related records

$mydata['RelatedRecords'] = array();//Establish the array element so that it shows up regardless of success

$relatedRecs = $record->getRelatedSet( "RelatedSetName" );

if( !FileMaker::isError($relatedRecs) ){

foreach($relatedRecs as $relatedRec){

$relRecords = array();

$relRecords['record_id'] = $relatedRec->getRecordId();

$relRecords_fields = $relatedRec->getFields();

foreach($relRecords_fields as $relRecords_field){//loop through related set fields and build another simple array

$relRecords[$relRecords_field] = $relatedRec->getField( $relRecords_field );

}

$mydata['RelatedRecords'][ $relRecords['record_id'] ] = $relRecords;

}

} else{

printf("Error %s: %s\n", $relatedRecs->getCode());

"<br>";

printf($relatedRecs->getMessage()); //This says that 'RelatedSetName' is not present ???

exit;

}

}

$record_count++;

}

//Show me what we have accomplished

echo "<pre>\n";

print_r( $mydata ); //This displays the formatted data array from my primary search, including the empty 'RelatedRecords' element

echo "</pre>\n";

Ahh...thanks for the Code Tag info...I don't post in forums often.

Here is what I am wanting to do. The FM application I am using as a datasource is a custom manufacturing information system ( MIS ) for a production plant. The plant receives orders from customers. The orders are broken down into separate production operations that we call Parts. For example, if we receive an order for 10,000 widgets ( order number ExampleA ) and each widget requires four separate production processes there will be four records in our MIS that contain, or are linked to, relevant production information. The parts would be enumerated thusly: ExampleA-1, ExampleA-2, ExampleA-3, and ExampleA-4. These four records are fetched in the primary records loop in my PHP code.

In relation to the part records there are also shipment records. Once we have manufactured the 10,000 widgets via their four step production process we need to ship them to various locations as directed by the customer. There may be as few as one related shipment record or they may number in the hundreds.

I only need to grab the shipment records once, for the work order as a whole. This is why in my PHP code I only have the getRelated method being called once when my generic record_counter is zero.

I hope I am making sense with all this.

Thanks for any advise in advance.

[Genx]

Makes sense - there's a cleaner way to do it but we can discuss that later.

In the mean time to the actual issue: what does getCode return from the error object - will be eaiser to debug if it's an actual code rather than a paraphrased message.