I also keep getting the error "This record was modified by another user since you started editing it. Do you want to save your changes anyway and possibly overwrite their changes?"
There are no other users modifying the record. The last modification came from the user that is being warned. Is there a way to stop this warning from appearing?
The refresh window script step has a "flush cached external data" checkbox that should force a reload of ODBC data.
The error message is related to data-binding. A lot of people will preferentially not allow direct editing of ODBC data, making their database "transactional". IE, load a record into a mirror table with global fields and one record, and when the editing is complete, a "save" button will write the changes back to the external data source.
I forget off the top of my head what exactly can cause that error in MySQL, but I think even viewing the records elsewhere can cause that error (IE even if it's another filemaker user loading the same data in a found set on a different system).
Flush cached external data works great but takes a lot of time. Using it wisely now.
I forgot to update the record modification timestamp and was maybe viewing the record in both places. Seemed to have trouble if the modification timestamp was not at a later time than the value already in the field.
This is my first test setup with OBDC. I will give the transactional setup a look.
I am not exactly seeing how I make this transactional. I need live updates two ways between the two databases. I can see setting it up one way when changes are made in Filemaker. New records are pushed from Filemaker to the other database.
The solution does not have the layouts abstracted from the data currently. This is mostly because I have not figured out a way to get a global auto enter serial number to work with multiple users entering data at the same time. The serial number needs to be established before the record is committed and it cannot be duplicated. There are ways to get this working but it seems like a lot of time spent in scripts just to create new records. I guess I can build a test solution and see how slow it really is.
I understand the dangers of the direct connection, but it is working quite well.
FYI, I regularly use FileMaker Server to directly read/write/update/delete MySQL tables direclty in FileMaker. It works best if both servers are on the same local area network. Basically you are using FileMaker as the GUI for MySQL, which works pretty well all things considered. It'll never work as well as FileMaker stored data. I end up doing this a lot with databases used by web developers who are afraid of connecting to FileMaker but have no problem using MySQL.
Thanks for the info on your experience.
The servers are in different locations, but as far as I can see it is still really fast and reliable.
I am currently using scripts to update the MySQL database tables keeping the original filemaker data slightly separated from the other database. This is a test envoronment for me to figure all of it out before any sort of deployment.