The Index Indexes makes finds and other features fast. Think of looking for a word in a book by flipping through each page. It’s much quicker to go to the index where it will tell you each page where the word is contained. This is exactly how an index works, it only stores each word once per field across all records. There are two types of indexing: Minimal and All. Minimal means only one index type has been created while All means two index types have been created. The most commonly used index type is a Word index and can only be created for text fields. Up to 100 characters of each word are indexed. Words are defined most often by spaces but can also be determined by commas, periods, colons and other characters. A word index does not differentiate between uppercase and lowercase. Searching for “FRED” also finds “fred”. A Value index is used for searching number, time and date fields as well as relationship key field matching. Up to 100 characters of each return-separated value are indexed. While searching can be done on partial text in a Word index, only complete values can be searched for in a Value index. When you think about it, why would you want to search for a partial number or date. Indexes are utilized in a variety of features other than finds such as value lists based on the contents of a field and unique validation. Understanding how an index works can help you better design a database.
One of the most important concepts to grasp hold of tightly, when moving from the design of single-user to multi-user FileMaker solutions, is record locking. It's a fairly simple concept to understand. It just prevents two people from editing the same record at the same time. The complex part of record locking is programming your scripts to manage record locking. In this article, I'll start by defining record locking, demonstrate what causes record locking, show you how to test for record locking without hosting your solution and finally how to trap for record locking. I'll also throw in a few examples for good measure so you can measure your newly found skills in the context of a real life scenario.
What is Record Locking? There are many types of record locking but for this discussion, let's define three basic types:
If no record locking is employed, it's possible for two users in a multi-user system to overwrite each other's changes. Let's say user A decides to edit a record for John Osborne and updates his mailing information. During the process of data entry, along comes user B who decides to update John Osborne's phone number (this example uses a flat contact manager where all the information is contained in a single table.) So... who's information is saved? It would be nice if the database could keep track of each field individually and keep both user's changes but it doesn't work that way. It's far too complicated, especially when you start considering what happens if the users are modifying the same field(s). Therefore, databases keep track of changes at the record level. The question is, does the database keep the record changes from user A or user B? It comes down to who saved or committed their record last (and has nothing to do with who started editing first.) If user B saves his record last then his changes overwrite user A's changes.
You can see why record locking is so important in a database, in order to protect valuable data. But, there are two different ways to lock records. Let's consider optimistic record locking first. It allows user A and B to edit the same record at the same time and only warns the users that two people are editing the same record once one of the users tries to save the record. I don't know about you but that's seems like pure craziness to me. What if I spent ten minutes editing a record only to find out I have to start over again because my changes have been overwritten by another user. Despite the idiocy of optimistic record locking option (some technologist don't even call it record locking), Microsoft Access still allows for optimistic record locking... LOL. To be fair, this model should only be implemented when few concurrent record updates are expected. Even so, I still don't get it. Why not just implement pessimistic record locking and be done with it?
Pessimistic record locking is really the only way to go and thankfully, what FileMaker supports. As soon as a user starts editing a record, it is locked and no other users can edit it until the record is committed. This allows the changes from the first user's editing to be available to the second user before they start making changes. Clearly, this is the only sane way to approach editing of records in a multi-user scenario. Pessimistic record locking protects your data from being destroyed even if you don't expect many records to be concurrently edited. Why take the chance.
SIDETIP: In FileMaker, editing a record is constituted by typing into a field. Some people believe clicking into a field is enough to lock a record, and they'd be correct if they were using FileMaker 6.
From a FileMaker perspective, basic record locking is really easy to understand. When a FileMaker file is hosted to multiple users via FileMaker Pro or FileMaker Server, only one user can edit a record at a time. If a second user tries to edit a locked record, FileMaker will display the record locking message.
You as a developer don't need to do anything. Record locking just happens. Easy, right? Yes and no. As you read on, you will begin to understand that record locking requires no additional effort on your part unless you start writing scripts. Before we write a record locking aware script, let's delve deeper into the nuances of record locking.
SIDETIP: When a record is locked, a script cannot edit or delete the locked record.
Sharing Locally I always find it beneficial, for better retention, to try out the concepts being discussed in an article using a real FileMaker file. That's why I want to show you how to share a file locally without using FileMaker Server or even another computer. Instead of copying a new version of your file to the FileMaker Server or working on a hosted file, this little known sharing method allows you, as a developer, quicker testing of multi-user scripts or, in this case, testing of the record locking concepts that will be discussed below.
The first step is to open your FileMaker file in FileMaker Pro or FileMaker Advanced. Then, choose Share with FileMaker Clients... from the Sharing sub-menu under the File menu.
SIDETIP: Sharing a FileMaker file locally doesn't require any additional licenses. However, there were versions of FileMaker in the past that erroneously resulted in licensing error messages when attempting to share a file to yourself on the same computer. This bug was fixed long ago.
When the FileMaker Network Settings dialog appears, set the Network Sharing option to "On" and make sure the file you are trying to share locally is set to "All users" or "Specify users by privilege set". I find it easier just to specify "All users" since this is just for testing record locking scenarios.
The last step is to use the Open Remote... menu item under the File menu to open your shared file. Remember, this is all being done from the same machine. Once you have opened the file, the easiest way to tell the difference between the two versions of the file is to look at the title bar of the window. The original file will simply be the name you assigned the file in the operating system, unless the Set Window Title script was utilized to change the window name. What's important is that the new window will have the same name followed by " - 2" and the name of the machine where the file is being shared. For example, mine appeared as:
PhilosophyOfFileMaker - 2 (John Mark's Mac Mini)
Now you have two users of the same file on the same machine. This will make testing record locking concepts much easier. Let's try a basic record locking scenario. You can use any file you have on hand but the example described here will be a flat contact manager with a single table since it's easy to follow. In either window, click into a field and start typing. Move to the other window, locate the same record and try to type into any field in the contact manager file. If you're file has multiple tables, just make sure the two fields are from the same table being used as the base table for the layout. If all has gone correctly, you should get a similar record locking error message.
How is a Record Unlocked? We already know that record locking occurs when two users attempt to edit the same record at the same time. If you are reading the italicized tips, you also know that a record only becomes locked when a user starts typing into a field on that record. Pretty straightforward. So, what unlocks a record so another user can edit it? The committing of a record is what unlocks a record. Commits can happen many ways including:
Typing the Enter key on the keypad
Clicking anywhere on a layout where there are no fields
Closing the window
Quitting or Exiting FileMaker
SIDETIP: There's a feature in FileMaker Server to disconnect inactive users after a specified period of time. This prevents records from being locked too long while a user is out to lunch or gone for the day.
What Does and Doesn't Cause Record Locking? Any field on any layout will lock a record from that table no matter where it is being displayed. It doesn't matter if the record is being locked from a list view layout and someone else is trying to edit it from a form view layout. Once it is locked, it is locked everywhere. Even related fields lock records. For example, let's say you have an invoicing layout and are displaying customer fields. Those related customer fields, when edited from the invoice layout, will lock the customer record from a layout showing records from customers.
In fact, a related field will also lock the parent record. In the invoicing example, or any multi-table solution, editing a related field from customers on a layout showing record from invoices will also lock the invoicing record. Crazy, huh? That's because FileMaker considers related fields on the current layout to be part of the parent record when it comes to record locking. However, editing the customer fields from a layout showing records from customers will not lock any invoicing records containing the customer data being edited. Of course, the related fields on the invoicing record will be locked, just not the invoicing fields. Try all these different record locking scenarios in an example file to make sure you really understand them well.
Deleting a record also falls under the restrictions of record locking. It's not possible to delete a record that someone else is editing. A lot of people forget about this limitation but it makes sense when you think about it. Here's an example of a script that tests for record locking when deleting a record.
The "Allow User Abort" and "Set Error Capture" sub-scripts simply test for "[Full Access]" to determine whether Allow User Abort and Set Error Capture should be turned on or off. If you want a longer discussion about these sub-scripts, see the article Ultimate Find. There's also some dynamic coding in the Show Custom Dialogs that is also discussed in the Ultimate Find article. What you really want to focus on for this article is the If statement after the Delete Record/Request step. First it checks if the user has privileges to delete a record and then determines if the record is locked. Both scenarios prevent the deletion of a record. And, since Set Error Capture is on, custom messages need to be displayed.
Interestingly, global fields don't cause record locking. Since global fields are local to each user in a multi-user scenario, they can be edited without locking the record where they are being shown. This allows for the possibility of cool interface techniques such as merge letter creation in browse mode, which I will discuss in a future article.
But, what happens to record searches, reports, etc. while a record is being edited? Until the record is committed, the previous data will be searched and displayed in a report. If a record is committed after a report is displayed in Preview mode, a refresh needs to occur. Just enter Browse mode and then return to Preview mode, or use the Refresh Window menu item under the Records menu.
Scripting the Opening and Closing of a Record There are many ways to script the locking of a record. You can simply use the Set Field step and attempt to write a value to a field on a record. If the value gets written an error of 0, or no error, is generated and the record is locked until it is committed. If the value isn't written then an error of 301 will generate (record in use by another user.) This is easy enough to test for using an If construct but what about the value that was written to the field? Was it a value you wanted written? Was it simply a test to see if the record was locked? What if the script is lengthy with many Set Field steps?
There's a much better way to test the status of locking on a record. The Open Record/Request script step doesn't require the modification of a record to gain control of it. The same errors are generated but there's no need to attempt a write to the record to test for locking. Open Record/Request simply locks a records without modification, if possible, until it's committed. Think of Open Record/Request and Commit Records/Requests as opposite sides of a coin.
Here's a simple example of opening a record with a script. Let's say you are trying to mark an invoice with the current date to designate it as paid. Seems simple enough but what if someone else is editing the invoice? As discussed previously, you could just try writing the current date to the field using Set Field and test for errors. While this works as well as opening the record in this simple example, it's shortsighted. If there are multiple fields that need to be edited then all of the Set Field steps will generate an error. It's better just to do one test with Open/Record Request, IMHO.
This simple example is the basis for all record locking scripts. Put as many Set Field, Insert Calculated Result or any step that modifies a field in the Else portion of the script. That's as complicated as it gets when working with a single record. Also, I like to end any script that employs Open Record/Request with Commit Records/Requests. I don't like leaving records open, especially when I locked it with a script.
Record Locking on Multiple Records Now for the tricky part! It's pretty easy to open and close a single record with scripting. But, what happens when the script tries to modify multiple records, such as occurs with Replace Field Contents or a looping script? For example, let's say you are looping through one hundred records and the first fifty get written but the fifty-first is locked. Do you code some logic into your script that remembers that record and tries to write it later? What if the record doesn't become unlocked for twenty minutes? Do you tie up the users machine while it continues to test? Maybe you pass off the script to the server so the user can continue to use their computer. Sounds complicated and unreliable to me. Maybe you decide to use a different approach and remove the data you wrote to the first fifty records and tell the user to try running the script later. The only problem is it's possible one or more of the first fifty records are now locked. It's a difficult issue to overcome, so much so that I almost never try to write to more than one record in a multi-user scenario.
So, what's the solution? Well, you could save the process to run server side in the middle of the night when there are no users locking records. It all depends how quickly you need the results. If you need the results immediately, it's time to think creatively. Unfortunately, any discussion of clever workarounds requires complicated example which require an entire article. Don't worry, I plan on writing about these techniques but you'll need to wait a bit for me to publish them. Thanks for your patience.
This blog is completely free. Please support it by clicking on one of the advertisers at the left side of the window or becoming a patron. Thanks so much!
What I have been using in my solutions is when performing updates on multiple records (i.e. updating invoice status on multiple invoices) is to use FM built in transactional abilities. When looping, I create a relationship to the target record from a global field set to the targets primary key, update that record, then go to the next one. Only commit at the end. If any errors Ii.e. 301) are thrown, revert record and all changes to all records are reverted. I let the user know that the process could not complete since 1 or more records are open by another user.
Yes, transactional processing is a great way around record locking. Invented by Todd Geist, it's definitely a tool I keep on my belt but it shouldn't be the only tool. Every unique record locking scenario should be looked at with a fresh perspective rather than just applying the same solution every time. I think it's about time I got to that followup article. Thanks for the reminder!
you wrote"...they'd be correct if they were using FileMaker 6." FM6? it must be 16, right?Now, if I understand the step indicating how to work on two copies of a file from a single computer, I share the file; no problem here. I can open the file on my iPhone (making sure sharing works). I have the original file open, then i use 'Open Remote' to open the same file but I do not get anything, the original file remains open but no second file's showing. Am I doing it wrong?
I actually meant FileMaker 6 because record locking only required clicking into a field in this old version of FileMaker. Since FileMaker 7, record locking has required typing into a field. Also, the ability to open a locally shared FileMaker file is not supported on iOS. You'll need to use a copy of FileMaker Pro or Advanced on OSX or Windows. And, just to be clear, there is no need for a second device. All this occurs on one desktop machine. Happy FileMaking!