What version of FileMAker are you using? If 10, then you can have a script trigger that runs upon committing the FM record, which will capture the feild values, go to a layout based on the SQL ESS Table occurrence, and set the values into the record that you want.
We are using Filemaker 10. Does the script trigger work with databases that don't have a submit option? The infomation is updated live without requiring a submit button to be pressed to update data. Also, is the trigger free? We are trying to develop a solution without requiring buying something.
FMP10 has script trigger built it. You can run your script onRecordCommit
Since this is my first experience writing scripts in Filemaker, is there anything special that needs to be included in my script to update the SQL table or can i just write a script that includes UPDATE SQL commands to update the table with the fields altered in Filemaker?
I would read up on ESS. Start with this brief.
Once you are familiar with it, then you can create a script that will capture the current values into variables, go to a layout based off your ESS Table occurrence and set the fields, and then go back to the orig layout.
Finally, you can have your layout script trigger call the script upon commit.
Thank you very much. This seems very helpful.
A followup question for mr_vodka, could you elaborate a little bit more on this script?
I would like to eventually create a two-way connection with filemaker and mysql.
First, I would like to take existing fm records and publish them to mysql whenever fm records change or are added.
You have laid out the skeleton of this script. Could you elaborate a little more, especially on the 'set the fields' step?
What about the very first round of getting data from fm to mysql?
Extending this, I would like to be able to eventually update fm records when a record is updated or added in mysql. What is the best way to sync this? Script? Lookup field?
This document seems to be here now: http://www.filemaker.com/downloads/documentation/techbrief_intro_ess.pdf
I am unsure why you would want to copy the same data when you can use ESS to just access the external table directly.
However, as in my pervious post you could have a script run OnRecordCommit of the FileMaker record, update the external table record using either a Execute SQL script step or if you have ESS set up you can go to that layout and set the values.
You would capture the values into variables and set the fields much when dealing with regular FileMaker tables.
Set Variable [ $field1; LocalTable::field1 ]
Go to Layout [ External Layout ]
Set Field [ External::field1; $field ]
Go to Layout [ Orig ]
BTW, if you are going to do heavy syncing, I probably wouldnt recommed that because you may have performance issues. You may want to look into a product such as:
Thanks. The main copy and front end for the database is in filemaker, hence I want to push (copy) data to mysql.
Your script steps should help.
I'm also looking into using sql statements to insert or update the mysql table via a script.
Eventually, I will need to add records via webform in mysql and push those to filemaker. I'm guessing I can still use your same script steps to do this.