Multi-User Design Issues - File Maker

Because FileMaker databases are made for scores of other users to share over a network, designing a database to be multi-user friendly might seem a no-brainer.

It just works, doesn’t it?
Well, it isn’t a complete “piece of cake” due to a few of the unique properties of shared databases. The main issues to worry about are record locking and global fields. Here you’ll learn what you need to be aware of so that the data integrity of your database system is preserved.

Record Locking Issues
Earlier you learned what happens when one user is editing a record and another tries to access it. Essentially, the second user gets a message like this:

Record Locking Issues

“Fine,” you say, “Good. I don’t want two people to be able to edit the same record at the same time. If they did, whose changes would win?”

You’re right, generally speaking; this kind of record locking is a good thing because you only want one user at a time modifying a record. Everyone else must wait until that first user is done and has committed his or her changes to disk.

But imagine the following scenario: Eileen is looking at an invoice record in Invoice.fp5, checking to see if the customer had paid an overdue amount of $500. Suddenly, she’s called away from her desk to deal with an irate client on the phone. Once she’s done with that, she goes to lunch and then into afternoon meetings until she goes home. In the meantime, the invoice record she was looking at has been locked the whole time she was away from her desk, preventing other users from editing it.

This might not seem a big deal, but what if another user logs on to the database system that runs a scripted routine in Invoice.fp5 that finds all unpaid invoices, prints them, and then marks the Status field in all of the found records as “Printed Overdue” using the Replace script step? Well, when the script is run all the records are found and printed fine; even the replace on the Status field will appear to be going fine until the end, when the user gets the following error message:

Multi User Design Issues

Translation: One record is in use and can’t be modified. Unfortunately, FileMaker doesn’t tell you which record is in use, nor does it give you the opportunity to try again. Too, any subsequent script steps are still run even though the Replace failed on one or more records.

So now the data integrity of the database system has been slightly damaged. Sure, the user could run the routine again tomorrow, at which time the record would probably be unlocked and the second printout would have one record on it. But there will be cases when this is not acceptable, like when replacing a record’s status with “Do Not Open” today prevents the very important “sulfuric acid” valve in your factory from being opened tomorrow.

This may seem to be an exaggerated example, but it highlights the fact that you’ve probably got to do some things in your scripts where you want massreplace or record updates to happen without presenting the users with insurmountable errors. Here are a few possible solutions for the above mentioned problem.

Kick Users Out
You could set up FileMaker Server (and the database in question, under the Passwords dialog) to kick idle users out of a database after a certain number of minutes. This will eliminate 95 percent of your record-locking problems with a few clicks. However, there’s still a small chance of the problem occurring, so you’ve got to do a bit more.

Ask Other Users to Log Off
This is probably not that practical if your office has thousands of employees, but if your office is small you could ask everyone else to log off first before running a script with a Replace script step in it. However, since you can design around using Replace, why inconvenience other users in this way?

Trap for Errors and Use “Set Field” Instead of Replace
Using error trapping (which suppresses FileMaker’s standard error dialogs) in conjunction with the Set Field script step (instead of Replace) should eliminate the rest of the problem.

To see how this works, launch the example database Contact.fp5 . The Replace button runs a regular replace on the Status field; the Set Field button runs the error checking/Set Field routine.

To get the gist of this, log in to the database on one workstation and put your cursor in any field on one of the records. Now log in to the same database via the Hosts dialog from another workstation and try the scripts. The Replace script gives you the same error message as before. The Set Field script does something a little different though. Here’s what that script looks like (with comments interspersed, explaining the code):

The gErrorRecords global text field will be used to make note of records that were locked and could not be updated.

Instead of the Replace, you’re just going to loop through the records and update them one by one.

Remember that you can trap for specific error codes, too . . . see FileMaker’s built-in help system to find out what they all mean. For instance, error #301 is the “Record is in use by another user” error.

An error is returned if the Set Field doesn’t work, which it won’t if the record is locked. Thus if there’s an error here, the record’s ID is copied into a global field along with any other records that had errors.

Via a self-join relationship, you’ll now see any records that could not be updated due to a record locking error and can take action on them as needed.

To bulletproof this script even more you could:

  • Add another loop outside the first one that tries again to update the locked records a certain number of times (say three), in case a non idle user has only temporarily locked them. Or you could loop inside the first loop, trying each record a certain number of times (tracked in a gCounter field, say) before writing the record off as locked and continuing on to the next one.
  • Add an initial loop that only checks to see if any of the records in the found set are locked before taking any action at all. While this wouldn’t prevent a user from locking a record just after the “lock check” part of your script runs, the chance of this happening is quite small.

Other Script Steps to Watch Out For
In addition to the Replace script step, there are others that require write access to a record (i.e., no one has it locked by having her cursor in one of its fields) in order to take action on it. These including but are not limited to:

Therefore, appropriate error trapping (like what you did previously for/ instead of Replace) would be required in any script that takes one of these steps.

Exit Record
Another way to prevent records from being locked when they don’t have to be is to put the Exit Record script step at the end of just about every script. This helps get users out of a record after a routine is run so the record isn’t locked when someone else needs to update it.

Edit/Browse Screens
Hand in hand (sort of) with the above Exit Record suggestion, you would be wise to create separate browse and edit screens (a la the “Darn Good Security System”), because this way users are only potentially in a field (on the Edit screen) and therefore locking a record if they really need to. The browse layouts should not contain even one field that a user can enter that will inadvertently lock a record.

To prevent record locking even further, even on edit screens, you could use only global fields on edit screens that then “push” the data to the “real” fields when leaving the edit screen, locking the record for a mere millisecond.

Global Field Issues
As you know, global fields contain values specific to each user. Yes, a global field takes on the values set by the developer the last time the database was started (hosted) with a copy of FileMaker Pro. This is good for such things as system settings and global icons and graphics that are used across all the databases, but for the rest of that user’s session the user can use, update and change the global field.

This is useful, for instance, in the login system of the “Darn Good Security System” where a user’s ID and privileges are stored in globals in the main menu database for the duration of their session and govern where that user can go and what they can see or do. They’re also useful for storing temporary groups of record IDs, counter values, or other temporary values. You can even use globals to allow users to mark records in a multi-user environment. (A standard “Mark number” field would result in one user marking a record, causing that record to be marked for everyone else; everyone would not have their own unique marked set).

Other Controllable Multi-User Issues
When you’ve got many users accessing a database system, especially a fairly complex one, you should make sure that everyone has the same (or compatible) combination of hardware and software in order for them to run the databases properly. For instance, everyone needs to have the required plug-ins as well as compatible versions of FileMaker Pro installed. If you’re using ODBC connectivity, everyone must have his or her ODBC control panel set up in the same way.

And, if FileMaker writes anything out of a database onto the network, like with the Troi File plug-in, you must make sure that all users have access to that network drive and that the drive is mapped properly for the “write” to work.

Other Mostly Uncontrollable Multi-User Issues
There are some multi-user issues that, for the most part, you can do nothing about, but that might make users edgy.

For instance, say a user is previewing a report with summary values and then he goes to print it. But when he looks at the printout, it has different totals than he saw on screen! Sure enough, when he previews the report on screen again, the totals match the report.

What happened? What probably happened was that another user added a record to that database while this user was looking at the first summaries, which were frozen on screen in Preview mode. Then, just before the report was printed the summary values were refreshed to include the most recently added record. The printed report isn’t incorrect, but a user might be caught off guard by the printed results.

Similarly, there are times when you may be looking at a database and a record seems to appear or disappear in your found set! What’s happening here is that another user has deleted a record, or created a new one, which is then displayed to all other users who are logged in. This can mess up your found set by plopping the new record, unsorted, at the end of your now semi-sorted set. Or say you’re browsing a record and someone goes in and changes the data in it; suddenly, everything on your screen changes though you’ve done nothing but sit there staring at it.

These are a couple of examples of quirks you should caution your users about, because they might observe them while using a shared FileMaker database. You want them to know what’s going on when that quirk occurs.

How about some more? Say you’re logged in to a live database system and you make changes to layouts. As soon as you leave Layout mode (and commit the layout changes to disk) the layout instantly updates for everyone else in the system. This can throw people for a loop, especially if you’ve changed the background of a layout from soothing gray to a barber pole esque red and white stripe!

Or, if you’re logged in as a developer to a database served by FileMaker Server and you’re modifying a database (like defining fields or re-ordering layouts), other users who try to get into the database system won’t be able to open the database you’re working on and could get a “This database can’t be found” message while they’re firing the database system up, which can make them very nervous. Get the idea?

All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd Protection Status

File Maker Topics