Filemaker and mysql - need to mirror database online
I have a filemaker database that I need to mirror online in a mysql database.
1. Need the mysql database to have the same data as filemaker (filemaker contains the master data).
2. Whenever I update a record in filemaker, I need to have it update in mysql.
3. I know how to set up External Data Sources already, so I don't need help on that.
I am thinking of the following:
a. create matching tables in mysql (not sure how to best index the tables in mysql)
b. write a script to loop through filemaker and then use an sql insert command to insert into the matching mysql field. Can I easily use calculated fields in filemaker to insert to mysql?
Once that's done, when I update a filemaker record, I need a 'commit' script to update the mysql field that has been changed. Not sure how to do this. Also not sure how to match the proper record in filemaker with the mysql matching record.