Auto-Update MySQL Table when FileMaker Table is Updated

Question

I'm trying to figure out if it's possible to have an external MySQL table automatically update its field values when users update a FileMaker table's fields.

I have identical tables with 10 fields (basic contact info, a status field) with one in MySQL and one in FileMaker.

Is it possible that when a user updates the FileMaker table's Status field, that it automatically updates the MySQL table's Status field with the same data?

Or is it required to use some sort of script trigger/other method to force the update to keep the tables synchronized?

Thank you so much! -Dan

Is there a reason why you cannot use the MySQL table as an external data source in Filemaker and let users interact with it directly? — michael.hor257k
Yes, it is being used as a middleman table to copy data from FileMaker into the MySQL table in real-time and used in an application integration with Salesforce Marketing Cloud. The FileMaker system is what the users work in and where all our data is stored and is a 15+ year old solution that has hundreds of thousands of records across several tables and hundreds of layouts for different functionality. The MySQL is going to be a copy of one of our tables and only 10 or so fields from that table and needs to be as close to real-time as possible on updates. — Daniel Phillips
You can add "Script Triggers" to the field on the layout that will call a script with a "set field" script step. Otherwise if you are importing data via a script you can script that. — CSchwarz

bigtheo bigtheo · Accepted Answer · 2021-06-10T19:50:55

it is possible by using triggers

this is an example of creating a simple trigger :

mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account
       FOR EACH ROW SET @sum = @sum + NEW.amount;
Query OK, 0 rows affected (0.01 sec)

for more information https://dev.mysql.com/doc/refman/8.0/en/trigger-syntax.html

Auto-Update MySQL Table when FileMaker Table is Updated

1 Answers