for those of you that are into transactional scripting, are you getting a record lock on records you are only reading from?
The argument for locking the record seems to be that the record data could change from under your feet, if you reed field A on your script line 17 and read again field B on line 55, the record may no longer be in the same state it was when you were on line 17 of your script. As a result: your observations about field A may no longer be valid, or the value you got for field B may be inconsistent with what you read from field A.
I am not locking records I am reading from, but I see how this can be an issue.
At the same time, FileMaker's tool to gain a record lock is to do 'open record' on the layout where the record resides (not really an option since transactions are happening over relationships) and setting a value in a field (turning the read-only action into a writing action with the consequence of altering last modification date / time / timestamp if you use field with these auto-enter options).
Of course, if all your transaction is doing is reading records, then you can end with a 'revert records' step and everything will get back the way it was. My issue is with transactions that combine reading from one record and writing into another: reverting records is no longer an option.
I'm not a fan of changing the record (and leaving a trace) simply for reading from the record. That said, I would like to close that loophole where data may change from under my feet.
There are approaches to Transaction like @steve_ssh brilliant demo.
However, FM does not know the concept of Transaction.
I have a simple and robust approach to making sure that records are exclusive to a script/user dialog:
Records have a ‘lock’ field that is set to 1 when a record’s content shall not be altered by other scripts/users. This comes with an overhead since all concerned scripts must check the lock field’s status at each launch.
Agreed transactional scripting is not an easy thing in FileMaker, but some people are making use of it and some of that ends up documented. I'm thinking about work from Todd Geist, Chris Irvine, Fabrice Nordmann, ...
Having a flag in a record is closer to a 'check-out / check-in' concept. A design that can have value, but here I'm trying to avoid marking the records I'm reading from at all and I'm seeking input about that.
I should un-take-credit for the transactional aspects of that demo file.
With respect to the transactional part, I was pretty much just trying to simulate what @Bobino was already doing -- techniques championed by folks such as Geist, Irvine, and more quietly by some of my colleagues.
It was just the part about nudging the relationship that I was calling out.
FWIW: I've seen non-FMP systems where this check-in/check-out approach was implemented with lock records that exist in a separate dedicated lock table.
I don't recall ever implementing anything like that in FMP, but I imagine that someone has done so, and perhaps can speak to it. The reason, of course, that I mention it is because it leaves your data records clean from being touched unnecessarily.
Would the lock table only have keys or a full copy of the record?
If it only has keys, it means everything else writing to a table needs to check if the record has a matching entry in the lock table. Such a convention would work, but only if the system is designed this way from the ground up.
If it has a full copy of the record, I still need to figure out a way to read the information to create the copy and make sure the record state is not changing as I am reading from it.
What I have seen in a non-FMP system, and what I had in mind was a dedicated table that does not copy/mirror any record data.
It would have something along the lines of:
An identifier for the Table in question
The primary key of the locked record
Something that identifies who/what requested the lock
Probably a status flag
Timestamp fields
Extra:
In addition to the other ideas mentioned on this thread, one thing that I don't believe I've seen mentioned yet would be leveraging Get( RecordModificationCount ) to ensure that an individual record had not changed between point A and B in time. This is not to say that this would be a substitute for transactional scripting, but rather something that may help in addition to the transactional approach.
What concerns me most are scenarios where you may need to ensure that an entire batch of records remains unmodified as your script executes. I'll definitely be interested to read approaches/solutions that others have for such cases.
I like the Idea of get (recordModificationCount). For it to be used in addition to transactional scripting, it would need to work across a relationship. Maybe someone could create an unstored calculation field, that would let us bypass the relationship limitation.
For the time being, I submitted a product idea on the community. (Please vote for it if you like it, it is titled 'open record: make optional input for table::field').
I'm glad someone finally mentioned Get(RecordModificationCount). It is an extremely simple and effective way to ensure that a record has not been modified. You can create an unstored calculation field with this function and then use List( ) to obtain the modification status of related records. That allows you to test the status of the whole group in a single action.
On a semi-related note: If you're using the Data API, you can pass the (optional) modId parameter to protect your PATCH calls from writing to back to a record whose state has changed since being fetched.
In our environment, users often 'sit' on a record for quite some time, phone-calls, boss is calling ('just a second...'), etc.
Therefore, locking a record when reading doesn't work there. Further on, when reading a record with wrong data (wrong assurance, wrong city,), it needs to be actualized - no matter if somebody is reading that record or not
I demonstrated a solution that allows people (customers of our customer) to enter data via webdirect - viewing records during that demo and watching how the data was changed in real-time was one of the 'keys' that kept our customer going further with that project (sure, it was for demo purpose to show that)
IMHO, transaction is something really basic and it is part of the core database-functions
Like Markus, I’m late to this one, but unlike Markus we have the opposite need to lock records.
We’re making extensive use of card windows, for instance an order or quotation parent record would be in the background while a user is in layouts within the card window possibly working on line items, bill of materials or pricing. During this time, we do want the parent record locked, hence we always use an ‘open record’ before opening the card window.
This presents a problem if we then want to update the (locked) parent record, say with a summary of the line item totals, but this is set within global fields within the card window then the parent record equivalent fields are updated at anytime the card window is closed.
However, this is purely our interface choice for this type of data entry and the dynamic updating of data has always been a selling point for us for FileMaker, which is especially impressive when being demonstrated across different continents.
I'm not too sure what to understand from observations from @Markus and @AndyHibbs: they seem to imply transactions are at the other end of the spectrum from dynamic updates. My vision is more that dynamic updates happen no matter what, but when modifications are 'packaged' in a transaction, the updates all happen at once after the transaction is posted.
Please let me know if I misunderstood what you were talking about, thanks for the input.
Wow, I've never thought about this before. I think the chances of this happening are minuscule, but obviously if the script runs for a long time and reads from fields at various times then it sounds technically possible.
A couple things that can help:
save field values to variables at the beginning of the script. You can even do so in one big Let calculation if you're super paranoid. If you write the vars one by one, then you technically face the same issue you described, but again very small chance of it happening.
Create a field that contains a JSON object of field values. It can be unstored and you just read that field once up front.
The revert idea is interesting, but I agree it only applies if you can safely do so. You could Open Record, read fields, revert record, then do any write operations after that.
I share your hesitation to modify the record and then commit the edit. That's wasteful IMHO.
FM dynamic updates only happen after a commit (leave record).
Dynamic update itself is oblivious to what happened to a record before commit. It is my understanding that ‘dynamic update’ and ‘transaction’ are unrelated.
We’re not applying any transactional methods in the example provided, it was a balance to Markus’ post to provide a converse example to the point he made.
Very simply, the user is retaining ownership of the source record while, often lengthy, changes are being made to data that will eventually update the source record. This will prevent any dynamic updates being applied before the user’s data is committed.
I just tested and it is indeed possible for a dynamic update to happen on one user's data while they're running a script. So I think there are basically two options: gather the data all at once for a "snapshot" at that point in time, or lock the record open while the user's script is gathering data.