How to automatically update a table with fields from different (external) tables?

June 17, 201114 yr

I have connected FM 11 Advanced to an external database (Zen Cart MySQL) which has a quite complicated structure. For different reasons I would like to make native FM tables as well that will get updated info from these external tables (one native FM table will have info from multiple external tables). Preferably the native FM-tables should be updated whenever the MySQL-database is updated (the MySQL-database might be updated from external programs as well, not only from FM).

One of the reasons is that I would like to have a local file of the most important tables and fileds that will work even if internet is down.

An other is that I have a value list problem.

I have the following tables:

tbl_Products
tbl_Product_attribute
tbl_Options_values
tbl_OptionsValues_to_Options
tbl_Options

I would like to make a native FMtbl_Options_values table that also contains the field fk_options_ID from tbl_OptionsValues_to_Options, so that I can make a value list based on FMtbl_Options_values in direct relation to tbl_Options.

Anyone knows how I can make a script that will run if a record in the MySQL-tables is created or updated (preferably automatically, but I can make a button as well, or perhaps make a calculation field that triggers the script if it is changed)?

June 25, 201114 yr

Author

Is it impossible to easily update a native FM-table with changes from external tables?

June 25, 201114 yr

I think that your idea of having duplication of tables will cause you data synchronization problems. How do you intend to keep the data between the two sets of tables in sync? You're really leaping from the frying pan into the fire.

Either use ESS or import from MySQL. A field's edits need to happen in only one environment. You can edit MySQL using ESS. If you really must have a FM table twin, then you can use a script trigger to update MySQL.

I find the ERD you attach here fairly straightforward, and yet the FM file you attached to the value list thread does not follow this structure. If you represent to us your actual situation, we'll do our best to help.

It seems to me that you are spinning your wheels, feeling that you're making progress. It's easy to do that with FM. Sit back and design the structure first (on paper, perhaps). Do you want a live connection to MySQL? What fields should be in shadow tables, etc.? What can realistically be expected of the system if the MySQL connection is lost?