Filemaker publish to online MySQL database

[AndyCan]

Evening all,

I'm trying to figure out the best setup for an independent estate agent (realtor to our American friends) with an office based Filemaker db and a web based MySQL DB for serving the website via PHP.

I would like to have all the new property details put into the Filemaker db and then 'publish' this info to the server based MySQL DB.

The question is how do I manage a Filemaker->MySQL workflow or will i have to forego Filemaker and try and manage everything online via the MySQL/PHP web application or maybe enter the info twice; once for the office and once for online?

As for the images, I'm thinking that whenever anyone adds a new property to the database, the pointer/URL can be automatically created to point to a hard-wired folder in the filesystem that uses the same ID. For example, if a new property ID = PRO_00034, then the field PropertyImagesURL is populated with 'resources/images/PRO_00034'. The subfolders would then be something like 'thumbs' and 'fullsize'. The folder would be manually created by the user and the images dropped into it.

I don't know if Filemaker allows Images to be stored on the filesystem like this, but from what i've read it's the best option for the server-side MySQL setup.

--------

Just had an idea... Coming at the problem from a different point of view... Is it possible to have Filemaker 'plugged into' to MySQL DB? So by editing in Filemaker, we are actually editing the MySLQ DB? Of course Filemaker would have to also function when not connected to the internet with a local copy of the records and update when it's reconnected to the server. Am I thinking ODBC?

I guess i could have purely local Filemaker tables with data that doesn't need to be online (Client details, etc.) and just plug into the property table on the MySQL server, thereby having one seamless DB with tables in different locations... Am i crazy or could that work?

If anyone can help point me in the right direction it would be greatly appreciated!! Thanks in advance.

Andy.

[AndyCan]

After my mini-revelation in the second part of my post, I've just found this very helpful screencast that might be of interest for anyone else looking to do something like this:

http://examples.oreilly.com/filemaker9tmm/screencasts/ess/screencast.html

I'm still interested to hear what the forum members have to say and i haven't yet been able to find out if it's possible to do offline edits when using ODBC that then synch to the server upon reconnection. Fortunately, the idea is to manage all updates via Filemaker, so the synch will always be one-way: Local(Filemaker)->Server(MySQL).

Cheers,

Andy.

[AndyCan]

I've setup a test ODBC connection between Filemaker and MySQL on the internet and i realise that things get a bit freaky when i kill the internet connection, so no editing of the shadow table while offline.

I'm thinking that the solution is maybe a combination of both my initial ideas. That would be to have a PROPERTY table locally in Filemaker and then have an exact duplicate table on the server in MySQL - same fields 'n' all.

Then i would connect to the MySQL PROPERTY table via ODBC and have it editable in Filemaker (shadow table). At the moment i want to 'publish' a new property on the website, i could make sure i'm connected to the internet and then click a button to run a script that copies the records data from the local PROPERTY table to the shadow PROPERTY table, all from within Filemaker. From what i've read, an import into the shadow table is the way to go.

There's no danger of the remote database getting out of synch because the only changes are made via Filemaker 'publishes'. This way i could have my full database offline in the office and at the same time have an easy way to 'publish' new properties. And i can always have a layout that lets me directly edit the shadow table just for admin purposes if ever necessary.

Any thoughts? Anyone?? Anyway, i'll let everyone know how it goes.

Thanks!

[DIGITAGE]

I think that's a very valid question. To me it seems like it has been asked before, but never really been answered.

Let me rephrase the question and tell me it's still what you're asking:

What is the best way to connect a filemaker database to a MySQL database on the web? I want to keep the local FM database, but I also want a recent, periodically updated copy of my data accesible on a remote webserver. I also want to filter which tables are being transfered.

Here's what I got so far, but I warn you, I'm a FM noob:

We want to have FM write its data not only to its own local database, but ALSO to a LOCAL MySQL db first. I have yet to figure out the steps how to do that.

Next step should be easy: Syncing a local MySQL db with the one on the remote webserver can easily be done by MySQL REPLICATION.

Thanks.

[John May - Point In Space]

Have a look at FileMaker's External Datasource feature, which lets it connect to a MySQL database and treat its tables as FileMaker tables.

- John

[DIGITAGE]

Hello John,

thank you for the answer, but I do want the data to be stored in the FM database. Is it possible to have the same set of data also stored in a MySQL database?

I am aware that FM can use a MySQL database as data source, but that's not what I'm asking. I don't want to expose my filemaker database to the web, that's the problem.

Thank you.

[John May - Point In Space]

You'd need to manually push your data to the MySQL database in that case, I believe. You could use the external datasource feature and FileMaker scripting to duplicate any data changes.

- John

[DIGITAGE]

You'd need to manually push your data to the MySQL database in that case, I believe.

What do you mean by manually?

You could use the external datasource feature and FileMaker scripting to duplicate any data changes.

What is a common way to do that?

[John May - Point In Space]

I don't have any specific examples, sorry.

- John

[DIGITAGE]

Maybe I'm asking the question wrong. Thank you for your help, I'll try again.

[liltbrockie]

Did you get anywhere with this digitage ?

[DIGITAGE]

Nope. Any comments appreciated. Can't believe I'm the only one.

[liltbrockie]

Well I assume you have an obdc data source set up and viewable from filemaker. Next step is to sort out a script which will import your local table into the mysql table.

[DIGITAGE]

Yes, that's exactly what I want to do.

The question is, has anyone of you ever done this who can point me to the specific documentation on how to do this? Or point me to a script that does this? I have read the (sparse) official documentation, what I'd like is specifics from somebody who hasn't just read the documentation but actually done it.

[mormorcat]

Did Digitage or AndyCan ever get a response to this? I too would like to find specific documentation on how to do this. I have searched everywhere and it always stops just short of demonstrating how to update MySQL shadow tables with FMP data in order to keep them in sync. I understand how to get my MySQL data into FMP but not the other way around. Is import the best way and if so can it be automated using a script?

[bcooney]

If you flag each record that needs to be pushed, you can script a Find>Import.

Import from one table to another, whether the table is a native FM or a mySQL table, the procedure is the same. In fact, this could be a FMS script that is scheduled as often as you'd like.

So, my question is, where are you stuck?

[mormorcat]

I'm stuck on something that may be very simple but I just don't see it. How do I import from one table to another if my source database is hosted remotely? My clients update FMP at their workstations and I need to import (or copy) the data from FMP to my shadow table daily so that my web content is up to date. Before I create a script I just want to understand how to do this manually. I am going to File > Import Records > and I have to choose a file to import from but I can't choose my current db because it is hosted remotely. Since I have the database open, it seems that this should be very easy but I just don't get how to do it. Am I on the right track or is there some other copy or import utility that I should be using? I've looked everywhere for a specific example using a remotely hosted database but can't find one.

[bcooney]

It's as if you're importing from one table to another in the same file, since FM sees the mySQL table as a FM table when you use the ESS feature.

So set the source import path to $importPath = Get (FileName), just as you would a native FM table.

You start at a layout based on a source TO, with the proper found set. Then, go to a layout based on the destination TO (your shadow table), and Import Records.