Mirroring two tables...
I've have partly build a database for a photographer shooting kindergarten children. One local table in the database stores customers info (one customer no per child) and another local table stores referenced images (6-12 pictures per customer). Customer_ID in customer table relates to customer_ID in pictures table. When all info been added correctly for a whole kindergarten (15-100 children) all data are copied to a webshop database (MySQL) using ODBC-connection (referenced picture files are uploaded using FTP). The web shop server are housed by the ISP. The same data (customer, pictures) is therefore stored in two places: in the local Filemaker db and in the web shop db). Another company has created the web shop and it's db.
When a customer login to the web shop and make an order, the table photo_shoppingcart is populated with new records. Now to the question: how do I best copy the data from this web shop table photo_shoppingcart to a local table? The local table should be the same as the photo_shoppingcart. The best would be to automatically copy new generated records in the photo_shoppingcart table to the local table. Is this possible to do? How do I do this? Can it be done in Filemaker using some kind of script/table relations magic?
The main task is to show what a customer has ordered directly in the "customers layout", which mean I probably will be using portals, won't I? Why can't I directly show the data in a portal from the photo_Shoppingcart, you might ask? For two reasons:
1) Performance will be bad (just browse customers will probably be slow, if a lookup most be done over the already slow internet-connection to the web shop db) I presume (please correct me if I'm wrong, or better give me on solution how to make this fast enough).
2) My customer for whom I building this db, demands that they most be able to use the database even if internet connection is down (of course they can't upload any new customers and pictures if internet is down).
All info and tips are appreciated!
They use: Filemaker 10/11 Pro on windows 7 / Windows XP