Recently there has been discussion in the FileMaker community regarding a known problem with live development (i.e., modifying database schema while other users are connected to the system). The problem is that under certain circumstances, the “New Record” command and script step will fail.
The aim of this article is to identify some specific circumstances under which New Record will fail, and also identify some specific circumstances under which it will *not* fail, and is based on a series of tests conducted using FMS 13v1 and a couple clients running FMP Advanced 13v3.
Disclaimer: These are preliminary findings and do not claim to be exhaustive or conclusive. If you are doing live development, you should test for yourself and draw your own conclusions.
Methodology: A system with two tables, Sales and Employees, was used. For all tests, the developer opened Manage Database and then did something (or in the case of the first test, nothing), but then did not click OK — instead the Manage Database dialog was left open. In no case was the “OK” button clicked.
Tests and Results
Note 1: I think of the entry circled in red as “Pandora’s Box”… once you’ve opened it (field options or calc defs), you can’t undo the mischief by simply closing it again — the closest you can come is to quickly cancel all the way out so that Manage Database is no longer open, thereby minimizing the time of exposure.
If you must view this information, a safer approach would be to select one or more fields, and then click the “Print” button like so:
Note 2: for the “Failure” scenarios above, if your user a) runs a script with a New Record step and b) error capture has been set to “on”, the New Record step will silently fail with an error code 303, which according to FileMaker Help means…
…and it’s not hard to envision significant problems arising under this set of circumstances, for example, a supposedly “new” invoice not being created, but instead an existing one being overwritten. To be clear, the error 303 will happen whether error capture is on or not — error capture will merely cause the failure to happen silently, i.e., without the “this operation cannot be performed at this time” dialog appearing.
Bottom line: it is the developer’s responsibility to trap for errors and ensure the data integrity of the systems they design and maintain. Common sense would suggest that if a developer (even occasionally) does live development, then error trapping around New Record should be part of the process.
My favorite topic! Thanks, Kevin.
And the issue is more extensive than just New Record. Similarly, Delete Record will fail, as will Set Field or any other step which attempts to edit data.
I presented on this topic at the FileMaker Product Developers Conference last year and my recollection is that in some of my test cases the error returned was 302 and in others it was 301. Perhaps it doesn’t matter, but I’m curious why and when a particular error is returned. One difference is that I used a testing methodology of modifying the definition of a stored calculation field In a table that had hundreds of thousands of records… and then saving the change (closing Manage Database)… which, of course, took a long time to complete. While it was processing I made the edits (New Record, Delete Record, Set Field) from another client.
Hi Danny, I am kicking myself for missing your session. Thanks for the ongoing education.
I believe that all of the failed tests you described will succeed if the table does not contain a field with an auto-enter serial. I’m not suggesting we shouldn’t use auto-enter serials, but it’s an advantage for UUIDs for systems that require frequent live development.
Completely agree with your bottom line…trap for errors, regardless of how the primary key values are set. Thanks for sharing!
Hi Greg, thanks for taking the time to comment. It had not occurred to me that the auto-entered serial number itself might be the culprit.
Hi Kevin,
Nice job getting very detailed on this. I am sure I first learned about this from Danny Mack back in my days at New Millennium. I have been citing it as one reason why you need to use transactions for any serious scripted data processing ever since.
Although Greg may be right, that certain errors are avoided if there is no serial number, I am pretty sure that it does not avoid all the problems. It would be good to drill down on that a bit.
Thanks for posting this.
Todd
Hi Todd, completely agree about the transactional model. It neatly solves so many potential gotchas, and concentrates them into a single “moment of truth”. I appreciate the work you and Danny have done/continue to do around this, and also appreciate you taking the time to comment.
Hi Kevin,
We had done some testing around this very issue, and as Greg Lane previously indicated, the auto-enter serial field was the deal-breaker. Our theory was that the auto-enter serials – Get ( NextSerialValue) – is stored in schema. A table’s schema changes are saved back to the server en masse. Thus the server can’t let any client do anything that might alter that auto-enter serial while schema is being edited.
Hi Marc, thanks for that additional info… a huge argument in favor of using UUIDs that I had not previously considered.
Is any of this affected by using the table-view method? In table view, you do NOT need to enter the graph to define field changes or options or add a new field. OK, I just tested, it seems to be OK except in the case of tables which have auto-enter serial numbers. So: no difference.