The case you are describing is very similar to one i have working since 2011.
In my case i have to handle sales, inventories, all kinds of stock checking, attendance etc. all information has to be available at all branches and the main office as well in real time
This is the case of a cell phone store with 24 branches around Arizona, and they also have mobile stores which are oftenly connected via 3g/4g broadband dongles.
When you have a case like that, you must expect to be handling several thousands of records in no time.
based on past experience (personal), the particular scenario you have is something that in my opinion will be slow, however fmpro is excelent as far as client side in that particular case.
The solution i'm talking about is a combination of MySQL server as DB engine and FMPro as front end, and the reason why this combination works effectively is that you only transmit a tiny query every time and all main tasks are processed in the server side which also returns smal amounts of information with just the results of complicated and heavy tasks.
Yes this can be done with FMP and is especially sucessful if you leverage a sync tool such as GoZync from Seedcode or Mirror Sync from 360works
How do you ensure that the client's data are received and updated on the server side and for the server to acknowledge that the data as been processed so that the client does not resend that dataset the next time it tries to upload again?
Let's say that i'm receiving inventory in a location, the program must have a form that contains all reciived items and of course that form has an ID that is linked to all items.
Once the tranfer is comlpeted you retreive a copy of the form containing all items and the "transfer ID" so you confirm that all information is in place.
Now... if your question si about making sure that the information is not lost, MySQL returns aknowledge of number of records processed and errors if they occure.
I recommend the simplicity of having temp tables on the server side. Import into that then have the server process the data (moving it into the correct tables, performing lookups and calculations, etc).
You can either import directly from client to server or export the data to a csv file and upload that to the server machine.
With this setup, I would not recommend a direct connection to the database and fms server. Instead I would give each location/user a local copy of the database and use MirrorSync ( or another Sync utility ) to upload and download data. If you use MirrorSync it has a web utility that will let them download a new copy of the database with a web link. The link can be used any number of times, and loads the current file. So the initial sync can be a small amount of data. If the local copy of the db is lost or damaged, just download a new one.
The result of this would be a better user experience. Very quick access to the data and FIleMaker pro. No issues or problems with corruption caused by dropped network connection.
Direct access over 3G would result in lags while data is being downloaded.
It does bring up the question, in this day and age, why is the client limiting is connectivity to 3G dongles?
1. Thanks for all the inputs! Really helpful in helping me sourcing for a solution to the problem
2. The client's warehouses are located in area where network connection can only be achieved using 3G modem. hence the limitation to be using 3G as a network source
3. I would think the item of a temp DB (or MirrorSync) would be an appropriate solution. Will have to research more!
Thanks guys once again!