Questions about inserting data into related tables...???

[angelleye]

I'm a little confused about how to handle adding data to related tables in FileMaker via PHP API.

I see that I can easily add data to Portals, but if the related table isn't displayed as a Portal, then what?

For example, I've got an invoice layout. On this layout is basic information like shipping address, cc info, etc. This layout also has a portal on it display records from the related InvoiceItems table. So adding items to this invoice would be easy.

Here's what I don't get, though. Our customer data is also stored in separate related tables but this information is not on a Portal. The fields are simply added to the layout. So if we create a new customer record that gets ID 1234 and then we populate the related field in invoices called CustomerID with 1234 then the other fields display data based on that relationship.

So, with SQL solutions, I would add a new customer record and then grab the value of the new record's ID so that I can then insert that into the new invoice record when I create it.

I don't see any easy way to grab the last inserted record's ID the way I can with other databases. Can I just use getField() on that same object after running createRecord()? Something like..

$InsertOrder = $FMInvoices -> createRecord('Web Checkout Details', $DataArray);

$InsertOrderResult = $InsertOrder -> commit();

$OrderID = $InsertOrderResult -> getField('RecordID');

Would something like that work?

The same thing happens again with our customers table related to an addresses table. The addresses table actually stores the addresses and then a related ID field between customers and addresses binds them together.

Any information on what I'm not getting here would be greatly appreciated. Thanks!

Edited July 29, 2008 by Guest

[Genx]

$InsertOrder = $FMInvoices -> createRecord('Web Checkout Details', $DataArray);

$InsertOrderResult = $InsertOrder -> commit();

$OrderID = $InsertOrderResult -> getField('RecordID');

Use a command instead, it returns a filemaker result object (or error object if the command failed - note that this will not contain any records) which will contain the resulting record:

$InsertOrderResult = $FMInvoices -> newAddCommand('Web Checkout Details', $DataArray)->execute();

if( !FileMaker::isError($InsertOrderResult) ) 

$OrderID = $InsertOrderResult->getFirstRecord()->getField('RecordID');

[angelleye]

Great, thanks! I'll play with that.

So you mean this is basically just like doing a find and then browsing through the records...but it will just return the one record I just entered, right? Do you really need getFirstRecord in that case?

Also, where do you even see the use of getFirstRecord? I've got the FileMaker Server 9 Custom Web Publishing with PHP PDF documentation that was provided and it seems very vague. I don't even see getFirstRecord mentioned anywhere in it.

[Genx]

It returns a result object - which contains an array of record object(s):

$first_record = $result->getFirstRecord() is the same as:

$first_record = array_shift($result->getRecords());

Even if there is only one record, $result->getRecords() still returns an array. $result->getFirstRecord() simply returns the first record object directly.

As for its initial use I dont recall... just use starky's ref here: http://jonathanstark.com/filemaker_api_for_php_documentation.php

[angelleye]

Nice, thanks!

[angelleye]

What tips can you give me about ensuring special characters get handled correctly? Do I need to urlencode the values I send in the calls to FM or does the FM class handle that for me?

[angelleye]

This worked great, thanks! Now I'm having a little trouble moving forward with this method and the portal commands, though. Here's what I've got based on what you showed me...

$CustomerData = array(

						'Email_Address' => $_POST['PayerEmailAddress'], 

						'fmName' => $_POST['PayerFirstName'] . ' ' . $_POST['PayerLastName'], 

						'Phone_Number1' => $_POST['PayerPhoneNumber']

					  );

					  

$InsertCustomerDataResult = $FMCustomers -> newAddCommand('WebCheckoutCustomers', $CustomerData) -> execute();

if(FileMaker::isError($InsertCustomerDataResult))

{

	$ErrorCode = $InsertCustomerDataResult -> getCode();

	$ErrorMessage = $InsertCustomerDataResult -> getMessage();

	exit($ErrorCode . '

' . $ErrorMessage);

}



$CustomerRecord = $InsertCustomerDataResult -> getFirstRecord();

$CustomerID = $CustomerRecord -> getRecordID();

The customer record is getting created and $CustomerID is coming back just fine.  So far so good (thanks again).



At this point I try the following...

$TableNames = $InsertCustomerDataResult -> getRelatedSets();

And I get back what I expect...



Array ( [0] => WebCheckoutAddresses ) 



So then I try this...

$RelatedAddress = $CustomerRecord -> getRelatedSet('WebCheckoutAddresses');

And I get an error back saying "Related set "WebCheckoutAddresses" not present."  How can that be, though?  The previous call successfully returned the related data set with that name.



I can't seem to get past that so the rest isn't working either of course...

$NewAddress = $RelatedAddress -> newRelatedRecord('Addresses');

$NewAddress -> setField('WebCheckoutAddresses::Address1', $_POST['PayerStreetAddress1']);

$NewAddress -> setField('WebCheckoutAddresses::Address2', $_POST['PayerStreetAddress2']);

$NewAddress -> setField('WebCheckoutAddresses::City', $_POST['PayerCity']);

$NewAddress -> setField('WebCheckoutAddresses::Company', $_POST['PayerCompany']);

$NewAddress -> setField('WebCheckoutAddresses::Country', $ppSession -> GetCountryName($_POST['PayerCountry']));

$NewAddress -> setField('WebCheckoutAddresses::State', $_POST['PayerState']);

$NewAddress -> setField('WebCheckoutAddresses::Zip', $_POST['PayerPostalCode']);

$NewAddress -> setField('WebCheckoutAddresses::zLeadRecordID', $CustomerID);



$NewAddressResult = $NewAddress -> commit();

I know I must be close. Can you help me find my problem?

Thanks!!!

[angelleye]

After a whole bunch of trial and error I've finally gotten this to work for me.

$CustomerData = array(

						'Email_Address' => $_POST['PayerEmailAddress'], 

						'fmName' => $_POST['PayerFirstName'] . ' ' . $_POST['PayerLastName'], 

						'Phone_Number1' => $_POST['PayerPhoneNumber']

					  );

					  

$InsertCustomerDataResult = $FMCustomers -> newAddCommand('WebCheckoutCustomers', $CustomerData) -> execute();

if(FileMaker::isError($InsertCustomerDataResult))

{

	$ErrorCode = $InsertCustomerDataResult -> getCode();

	$ErrorMessage = $InsertCustomerDataResult -> getMessage();

	exit($ErrorCode . '

' . $ErrorMessage);

}



$CustomerRecord = $InsertCustomerDataResult -> getFirstRecord();

$CustomerRecordLayout = $CustomerRecord -> getLayout();

$CustomerRecordPortals = $CustomerRecordLayout -> getRelatedSets();

$PortalName = array_shift($CustomerRecordPortals) -> getName();



$CustomerID = $CustomerRecord -> getRecordID();



$RelatedTableNames = $InsertCustomerDataResult -> getRelatedSets();



$NewAddress = $CustomerRecord -> newRelatedRecord($PortalName);

$NewAddress -> setField('WebCheckoutAddresses::Address1', $_POST['PayerStreetAddress1']);

$NewAddress -> setField('WebCheckoutAddresses::Address2', $_POST['PayerStreetAddress2']);

$NewAddress -> setField('WebCheckoutAddresses::City', $_POST['PayerCity']);

$NewAddress -> setField('WebCheckoutAddresses::Company', $_POST['PayerCompany']);

$NewAddress -> setField('WebCheckoutAddresses::Country', $ppSession -> GetCountryName($_POST['PayerCountry']));

$NewAddress -> setField('WebCheckoutAddresses::State', $_POST['PayerState']);

$NewAddress -> setField('WebCheckoutAddresses::Zip', $_POST['PayerPostalCode']);

$NewAddress -> setField('WebCheckoutAddresses::zLeadRecordID', $CustomerID);



$NewAddressResult = $NewAddress -> commit();

I'll probably be back for more, though, cuz I'm not exactly sure I understand how I made this work yet. LOL.

[angelleye]

Sure enough, immediately following this I'm trying to grab the ID of the record that was just inserted into the portal and now I'm getting that same WebCheckoutAddresses not present error I was before.

So I've simply added this after what I've got already...

$RelatedAddresses = $CustomerRecord -> getRelatedSet($PortalName);

if(FileMaker::isError($RelatedAddresses))

{

	$ErrorCode = $RelatedAddresses -> getCode();

	$ErrorMessage = $RelatedAddresses -> getMessage();

	exit($ErrorCode . '

' . $ErrorMessage);

}

And I'm getting back "Related set "WebCheckoutAddresses" not present."

: I obviously have no idea what I'm doing here. LOL.

[Genx]

Best bet is that no related records exist...

[angelleye]

Yeah, that seems to be why I'm getting the error. But that's the whole deal...the related record is getting added at the line...

$NewAddressResult = $NewAddress -> commit();

When I look in FileMaker the new Customer record does indeed contain an address in the addresses portal just as it should. But for whatever reason the class still doesn't see it..??

I thought the commit() line would actually "save" the new record an then right after that I could pull the data out. All I need is the new address record's ID.

I actually had this all setup and working with 3 separate calls to FileMaker. One insert into the customers table...grab that ID. Then another insert into the addresses table...grab that ID. then move on and insert the order information into the Orders table including both the new CustomerID and AddressID.

I started reading more about utilizing layouts and portals and all of that and I figured this would be a better way to handle but this last little piece is stopping me up now. Should I even worry about it? Is this any better than doing it the way I initially had it? I haven't had much luck with web applications and FileMaker (see my other posts) so with this one I want to make sure I've done absolutely everything I can to ensure the least amount of server load and get maximum performance out of FileMaker so we know if it's even going to work for us.

Edited August 2, 2008 by Guest

[Genx]

Okay, what I think you're trying to suggest is that "adding a new related record" to your FileMaker record object should update your current FileMaker record object without you having to make a new call to FM?

Why don't you just track it on the server (php) side instead?

[angelleye]

I'm not following you...?? Track it on the server???

[Genx]

Just keep a track of what records you've added... e.g.

$myNewRecords[] = array("firstName"=>"John","lastName"=>"smith");

Anyway, you still haven't been very clear about WHY exactly you're trying to do it this way, a FileMaker query shouldn't really extend past a second in duration anyway, so if its a 4 second vs 3 second page load and you're causing yourself a huge pain as a result, don't worry about it.

[angelleye]

The tables in FileMaker are all related based on RecordID's. That's why I'm doing it this way.

Table 1: Customers

Table 2: Addresses

Table 3: Invoices

Table 4: InvoiceItems

Address records are related to Customer records based on the customer RecordID.

Invoices are related to Customers based on the customer RecordID. Invoices is also related to Addresses based on the address RecordID. This is all because customers may have multiple addresses they use at different times.

So then I haven't gotten this far yet because of my issues, but InvoiceItems are related to Invoices based on the invoice RecordID the same way.

So the flow of inserting a new order into the system is this:

Add new record to Customers table, then grab the value of the new RecordID for that customer.

Then create a new address record which includes the new customer RecordID. Grab the ID of the new address RecordID so that you can include it in the creation of the new Invoice record later.

That's the part that's not working for me. The new customer record gets created, the new address record gets created, but then when I try and grab values from that new address record via the portal it can't find it for some reason and returns this error as if there weren't any related records. I can clearly see in FM the related addresses getting added to the portal, though.

Because of the troubles I've been having I went back to my original plan and this is what I currently have that is working just fine:

$ppSession = new PayPalSession($sandbox);



/* Add new customer record into FileMaker Leads and Clients WebCheckoutsCustomers */

$CustomerData = array(

                        'Email_Address' => $_POST['PayerEmailAddress'], 

                        'fmName' => $_POST['PayerFirstName'] . ' ' . $_POST['PayerLastName'], 

                        'Phone_Number1' => $_POST['PayerPhoneNumber']

                      );

                      

$InsertCustomerDataResult = $FMCustomers -> newAddCommand('WebCheckoutCustomers', $CustomerData) -> execute();

if(FileMaker::isError($InsertCustomerDataResult))

{

    $ErrorCode = $InsertCustomerDataResult -> getCode();

    $ErrorMessage = $InsertCustomerDataResult -> getMessage();

    header("Location: filemaker-order-entry-error.php?ErrorSource=CustomerDataEntry&ErrorCode=" . $ErrorCode . "&ErrorMessage=" . $ErrorMessage);

}

else

{	

	$CustomerRecord = $InsertCustomerDataResult -> getFirstRecord();

	$CustomerID = $CustomerRecord -> getRecordID(); 

}





/* Add new address record into FileMaker Leads and Clients WebCheckoutsAddresses */

$AddressData = array(

						'Address1' => $_POST['PayerStreetAddress1'], 

						'Address2' => $_POST['PayerStreetAddress2'], 

						'City' => $_POST['PayerCity'], 

						'Company' => $_POST['PayerCompany'], 

						'Country' => $ppSession -> GetCountryName($_POST['PayerCountry']), 

						'State' => $_POST['PayerState'], 

						'zLeadRecordID' => $CustomerID

					);



$InsertAddressDataResult = $FMCustomers -> newAddCommand('WebCheckoutAddresses', $AddressData) -> execute();

if(FileMaker::isError($InsertAddressDataResult))

{

	$ErrorCode = $InsertAddressDataResult -> getCode();

    $ErrorMessage = $InsertAddressDataResult -> getMessage();

    header("Location: filemaker-order-entry-error.php?ErrorSource=AddressDataEntry&ErrorCode=" . $ErrorCode . "&ErrorMessage=" . $ErrorMessage);

}

else

{

	$AddressRecord = $InsertAddressDataResult -> getFirstRecord();

	$AddressID = $AddressRecord -> getRecordID();

}





/* Add new order into FileMaker Invoices Web Checkout Details */

if($shoppingCart->DisplayInfo("eBayItemID") != '' && $shoppingCart->DisplayInfo("eBayItemID") != 0)

	$WebOrderNumber = $shoppingCart->DisplayInfo("eBayItemID");

else

	$WebOrderNumber = $shoppingCart->DisplayInfo("ID");



$OrderData = array(

					'Web_Order_Number' => $WebOrderNumber, 

					'Shipping_Name' => $_POST['ShipToFirstName'] . ' ' . $_POST['ShipToLastName'], 

					'Shipping_Company' => $_POST['ShipCompany'], 

					'Shipping_Address1' => $_POST['ShipStreet1'], 

					'Shipping_Address2' => $_POST['ShipStreet2'], 

					'Shipping_City' => $_POST['ShipCity'], 

					'Shipping_State' => $_POST['ShipState'],

					'Shipping_Zip' => $_POST['ShipPostalCode'], 

					'Shipping_Country' => $ppSession -> GetCountryName($_POST['ShipCountry']), 

					'Shipping_Phone' => $_POST['ShipPhoneNumber'], 

					'Terms' => $_POST['CreditCardType'], 

					'Credit_Card_Number' => $_POST['CreditCardNumber'], 

					'Credit_Card_Exp' => $_POST['CreditCardExpMonth'] . $_POST['CreditCardExpYear'], 

					'Credit_Card_Security_Digits' => $_POST['CreditCardCVV2'], 

					'Lead_Source' => $_POST['LeadSource'], 

					'Ship_Via' => $_SESSION['ShippingService'], 

					'customerNotes' => $_POST['CustomerNotes'], 

					'Shipping_Override' => $shoppingCart->GetRuleValueByName("Shipping", urldecode("Shipping")), 

					'Shipping_Override_Check' => 'Yes', 

					'Misc_Charge_Override_Amt' =>  $shoppingCart->GetRuleValueByName("Charges", urldecode("Handling")), 

					'Misc_Charge' => 'Yes', 

					'Misc_Charge_Override' => 'Yes', 

					'zBillingAddressRecordID' => $AddressID, 

					'LeadClientRecordID' => $CustomerID

					);



$InsertOrderResult = $FMInvoices -> newAddCommand('WebCheckoutInvoices', $OrderData) -> execute();

if(FileMaker::isError($InsertOrderResult))

{

	$ErrorCode = $InsertOrderResult -> getCode();

	$ErrorMessage = $InsertOrderResult -> getMessage();

	header("Location: filemaker-order-entry-error.php?ErrorSource=OrderDataEntry&ErrorCode=" . $ErrorCode . "&ErrorMessage=" . $ErrorMessage);

}

else

{

	$InvoiceRecord = $InsertOrderResult -> getFirstRecord();

	$InvoiceID = $InvoiceRecord -> getRecordID();

}





/* Add invoice items into FileMaker Invoices WebCheckoutInvoiceItems */

if(isset($_SESSION['SalesTax']) && $_SESSION['SalesTax'] > 0)

	$Taxable = 'T';

else

	$Taxable = '';

	

while (!$shoppingCart->EOF())

{



	if($shoppingCart->DisplayInfo("eBayItemID") != '' && $shoppingCart->DisplayInfo("eBayItemID") != 0)

		$WebOrderNumber = $shoppingCart->DisplayInfo("eBayItemID");

	elseif($shoppingCart->DisplayInfo("ScanID") != '' && $shoppingCart->DisplayInfo("ScanID") != 0)

		$WebOrderNumber = $shoppingCart->DisplayInfo("ScanID");

	else

		$WebOrderNumber = $shoppingCart->DisplayInfo("ID");

	

 	$OrderItemsData = array(

							'zInvoiceRecordID' => $InvoiceID, 

							'Item_Number' => $shoppingCart->DisplayInfo("ID"), 

							'Price' => $shoppingCart->DisplayInfo("Price"), 

							'Quantity' => $shoppingCart->DisplayInfo("Quantity"), 

							'Scan_Code' => $shoppingCart->DisplayInfo("ControlNumber"), 

							'Taxable' => $Taxable, 

							'Web_Order_Number' => $WebOrderNumber

						);

						

	

						

	$InsertOrderItemResult = $FMInvoices -> newAddCommand('WebCheckoutInvoiceItems', $OrderItemsData) -> execute();

	if(FileMaker::isError($InsertOrderItemResult))

	{

		$ErrorCode = $InsertOrderItemResult -> getCode();

		$ErrorMessage = $InsertOrderItemResult -> getMessage();

		header("Location: filemaker-order-entry-error.php?ErrorSource=OrderItemDataEntry&ErrorCode=" . $ErrorCode . "&ErrorMessage=" . $ErrorMessage);

	}

						

	$shoppingCart->MoveNext();

	

} /* end looping through shopping cart */

$shoppingCart->MoveFirst();

In all of the reading I've been doing about FileMaker and web apps, though, it looks like the portal route would be the best way to do it.

Basically what it boils down to is that I've had a horrible time trying to build worth-while web apps with FileMaker as my back-end. I've spent a good 2 years (literally) trying to get answers on what I'm doing wrong from FM technicians, friendly forum users like yourself here and on other forums, and from people who used to work with companies like Soliant and Moyer Group who are FileMaker pro's like yourself.

Nobody has ever been able to give me an answer on how to ensure these apps will actually be usable by more than 1 or 2 people at a time on our web server.

I have made a lot of progress with creating custom layouts that only consist of the fields I need for the app as well as ensuring non-indexed fields aren't including in those layouts.

The only answer I ever really got from people is "you're doing it wrong" because I come from a SQL database background and working with FileMaker and layouts is completely different.

In the past I had been working with the XML API directly because the PHP API wasn't available yet. This is the first time I've actually tried an application using FileMaker's PHP API so I just want to make sure I'm doing absolutely everything "the right way" so that if it doesn't work for my client because of too much traffic I can stand confident behind the fact that I did everything correctly.

[Genx]

The first three records should take < 3 seconds to insert.

My main concern is this part, this part I would put in a portal depending on the number of times its likely to loop. Either that, or you can send the data in an encoded array of some description and execute a script on the server to process the array of data into new records.

while (!$shoppingCart->EOF())

{



    if($shoppingCart->DisplayInfo("eBayItemID") != '' && $shoppingCart->DisplayInfo("eBayItemID") != 0)

        $WebOrderNumber = $shoppingCart->DisplayInfo("eBayItemID");

    elseif($shoppingCart->DisplayInfo("ScanID") != '' && $shoppingCart->DisplayInfo("ScanID") != 0)

        $WebOrderNumber = $shoppingCart->DisplayInfo("ScanID");

    else

        $WebOrderNumber = $shoppingCart->DisplayInfo("ID");

    

     $OrderItemsData = array(

                            'zInvoiceRecordID' => $InvoiceID, 

                            'Item_Number' => $shoppingCart->DisplayInfo("ID"), 

                            'Price' => $shoppingCart->DisplayInfo("Price"), 

                            'Quantity' => $shoppingCart->DisplayInfo("Quantity"), 

                            'Scan_Code' => $shoppingCart->DisplayInfo("ControlNumber"), 

                            'Taxable' => $Taxable, 

                            'Web_Order_Number' => $WebOrderNumber

                        );

                        

    

                        

    $InsertOrderItemResult = $FMInvoices -> newAddCommand('WebCheckoutInvoiceItems', $OrderItemsData) -> execute();

    if(FileMaker::isError($InsertOrderItemResult))

    {

        $ErrorCode = $InsertOrderItemResult -> getCode();

        $ErrorMessage = $InsertOrderItemResult -> getMessage();

        header("Location: filemaker-order-entry-error.php?ErrorSource=OrderItemDataEntry&ErrorCode=" . $ErrorCode . "&ErrorMessage=" . $ErrorMessage);

    }

                        

    $shoppingCart->MoveNext();

    

} /* end looping through shopping cart */

[angelleye]

Ok, thanks for the tips. I'll probably go ahead and use the portal method here. I don't need to get ID's back for each individual invoice item so I shouldn't run into the same problem as before.

That being said, do you not find that odd that I can't grab data from a portal immediately after inserting it? Is that normal?

[Baloo]

Sorry to jump in late; I've been busy lately.

The best thing to wrap your head around is that with the API you're still interacting with the XML feed (via cURL) not the database directly so "odd" things are going to happen. Reading this and a few of your other posts I go back to Genx's comment here http://www.fmforums.com/forum/showpost.php?post/295631/ and ask again why you're not not using MySQL, or rather why your client (assuming it's the same project) is insisting you do?

I've found the API great for grabbing single record data or short simple lists of data from FMP to put up on the web, but I only develop the backend in Filemaker if the heavy lifting is going to be done via the Filemaker client. If the entire app is going to be web based, there are just better tools for the job. If your client just wants to make his/her edits via the Filemaker client put the tables in MySQL and use MyODBC to set up a DNS on the Filemaker server. Then build your Filemaker client based solution on top of that. If your client just wants to use a Filemaker backend for a purely web-based frontend because he/she thinks it's neato, I'd consider firing the client (if you can afford to).

[Genx]

Yeh, what he said I said :

[angelleye]

Sorry, I didn't see these last responses in my email for some reason.

Right now this client is one of my primary clients and I can't really afford to dump em at the moment.

I'm just gonna keep playing with what I've done and when it doesn't work they'll either fire me or have me go ahead and use MySQL.

It really would be nice if I didn't have to mess with syncing a 3rd party database, which is essentially what I'm doing now and it does work, it's just a lot more work on my part to make it all work correctly and accurately. And almost impossible to keep it in real-time.

That being said, got any tips for that? say I've got the MySQL database with a products table that matches the products table in FileMaker and I just need to sync them in real-time. How would you go about doing that? Everytime something like that comes up (which is a LOT) I have to attach a script to a button and then hope that every user actually clicks that button. Updating HOT items on the web site, for example. When they check the HOT box on an item in FileMaker I currently have a script that runs to update the web site. It switches to a web viewer layout, runs a web app to update Access, then switches back to the original layout. It works most of the time, but sometimes the script hangs slightly and the pause I have in the FM script isn't long enough so the web viewer part doesn't actually complete. If I add another second to the delay in the script the users start to complain.

Another option would be to use the Scdigo PHP Plugin, which I absolutely love, but then of course they have to buy a big license for that.

Then I think, well, I'll just start to migrate everything so it actually lives in MySQL and then just FileMaker as a front-end with layouts that simply attach to the MySQL fields...that job quickly gets put on the back-burner when I begin to realize exactly how much I have to migrate over. Like I said, 12 years worth of developing a DB isn't exactly an easy thing to just move over into another solution.

So this is the circle I've been wandering around in for the past couple years. I appreciate the advise and everything you guys have offered up here. In the very least I'll have some more "other developers agree with me" evidence to show them. :

[Genx]

Meh just use FM... doesn't suck that much as long as you don't try and sort a found set over 250-300 records.... at that point you'll be waiting a LONNNGGGG time.

If you record page, and deal correctly with FM's weaknesses, you can get some decent out - don't worry if you're executing less than 6 or 7 FM queries, you notice the difference when you execute 20 or 30 SQL queries vs 20 or 30 fm... If you really do worry, then have a play with sending data to fm via a script parameter and execute the script on the server side to do your data processing...

[Baloo]

There's no need to sync a MySQL table with Filemaker. You can ODBC directly into it and add it as a table reference via the client. You can build relationships between FMP tables and MySQL tables. About the only thing you can't do is build a value list off of a field in a MySQL table (don't ask me why).

Just grab MyODBC (available under GPL) http://dev.mysql.com/downloads/connector/odbc/3.51.html

Install and set up a system DSN connection to your MySQL database on your Filemaker Server.

File -> Manage-> external data sources

New

Click the ODBC radio button then the "Specify" button and you'll see the DSN

Don't forget to check the "Views" check box in the lower right hand corner if you want to be able to add a table reference to a view.

We have a couple of databases in production using this method. They've been up for ~6 months and we've had no problems.

If you absolutely need to sync a MySQL table with FMP (e.g. sync a reference table for a value list) I'd recommend doing it nightly via a php script set up as a cron job (or the Windows equivalent). Definitely avoid Filemaker server side scripting for this kind of stuff. http://fmforums.com/forum/showtopic.php?tid/197288/

Edited August 13, 2008 by Guest