Troubleshooting in Specific Areas: Performance, Context, Connectivity, and Globals

Troubleshooting in Specific Areas Performance, Context, Connectivity, and Globals

The individual troubleshooting sections in each chapter of this book cover particular isolated "gotchas" that we've wanted to highlight. In this section, we want to do two things: We want to talk generally about broad areas of potential FileMaker trouble and how to diagnose them, and we want to talk about a number of specific areas that don't pop up in the other chapters, or at least don't get a comprehensive treatment.

Performance

"The system is slow!" Performance is a critical part of the user experience. What can you do if things seem slow? Well, first of course, it's important to isolate the problem. Is just one area of the system slow, or one particular function? Or does the system generally seem sluggish? In general here, we're assuming that your solution is a multiuser solution hosted under FileMaker Server, but most remarks (except those entirely specific to Server) apply equally to Server and non-Server configurations.

General Slowness

If a FileMaker application seems generally sluggish and slow to respond, the leading candidates are insufficient hardware (client, server, or both), improper server configuration, or insufficient network resources. The last two apply only when the files are being hosted on FileMaker Server.

Hardware is a straightforward issue. Read and understand the hardware requirements for the client and server computers. If you plan on having many users on the server, please don't skimp on the server hardware! Get the very best machine you can afford. Likewise, on the desktop, try to meet or exceed the stated standards.

If you're not hosting the files on FileMaker Server, but rather have a peer-to-peer configuration, and things seem slow, you should seriously consider moving to Server.

If you're working with FileMaker Server, you also need to make sure that your server settings are configured correctly for your situation. In particular, you'll want to look at things such as the percentage of cache hits, the frequency with which the cache is flushed, and the amount of RAM dedicated to the file cache size.

If you're using Server and all the hardware seems reasonable, it's time to look at your network. You should know your network's exact topology. What connections are there, at what speeds? Where are the routers, switches, and hubs? What other traffic besides FileMaker traffic flows over the network? FileMaker is fastest over a LAN at speeds of 10 megabits and higher. In general it's better not to try to run FileMaker over a network link that's slower than T1 speed (1.544 megabits). All other things being equal, a fully switched network is better than not, and of course a 100-megabit LAN is better than a 10-megabit LAN, if they're both built correctly. FileMaker, like any networked service, also suffers from competition with other services running over the same wire.

You should also be familiar with your firewall situation. Does FileMaker traffic need to pass through any firewalls or packet filters? This can slow things down as well.

A last point is that FileMaker Server can be set to encrypt traffic between the client and the server. This encryption is somewhat processor-intensive and may impose a performance penalty. If you're experiencing slowness in a client-server environment, and you're using client-server encryption, you may want to disable the encryption and see whether that makes a difference. If so, you may need to consider investing in faster hardware.

Slowness in Searching and Sorting

Searching and sorting are among the operations that, in a Server configuration, are handled chiefly by the server. So it's possible the slowness in searching or sorting is symptomatic of some general networking issue of the type discussed in the previous section.

It's also possible that there's a problem with the search or sort itself. In terms of performance, the cardinal sin is to execute a search or a sort based on one or more unindexed fields. This, of course, means that there's no index for the field, which in turn condemns FileMaker to examining each and every record in the databasesomewhat akin to trying to find a word in a dictionary where the order of the words is random.

As you might recall, some fields in FileMaker can be unindexed merely because the designer chose to leave them that way, perhaps to save space. For certain fields, this setting can be changed, and FileMaker can be permitted to index the field. Other fields, though, such as globals, or any calculation that references a global or a field in another table, cannot be indexed under any circumstances. If your search or sort includes such a field, the operation will never go quickly, and in fact its performance degrades linearly as the database grows in size.

Note

You should allow an unindexed search only if you're sure that the set of searchable records is always going to remain fairly small, and there's no other way to achieve the result you need. In general, programming a search or sort on unindexed fields should be considered a design error and should be avoided.

Note too that "unindexable-ness" has a certain viral character to it, where calculations are concerned. Suppose that you have a calculation A, which references calculation B, which references fields 1, 2, and 3. For reasons of saving space, you decide at some point to eliminate the index on field 3. Immediately, calculations A, B, and C all become unindexed as well, for the simple reason that they now all depend on an unindexed field. Any searches or sorts that use these calculations will now potentially run quite slowly. Be aware of this issue of cascading dependencies when working with indexes.

Slowness in Executing Calculations

If you have a calculation that seems to execute very slowly, there are a few avenues you can explore. In general, the greater the number of fields and other calculations that your calculation references, the slower it'll be. It's possible to build up quite lengthy chains of dependencies, or to have dependencies with a non-obvious performance impact. Consider the previous example, with a calculation C that references a calculation B that references a calculation A. Every time A or B changes, C gets re-evaluated as well. So the calculation contains more work than you might expect. It's very easy to create elaborate chains of such dependencies, so watch out for them. If you find such chains, see whether there are ways to restructure the chain, perhaps in a way that allows some of the intermediate data to be stored, or set by a script.

Likewise beware if your calculations reference any custom functions with recursive behavior. A recursive function is like a little looping script. How long it loops for any case all depends on the inputs. If you're referencing these in your calculations, be aware of this fact.

Finally, if your calculation references related fields, it will likely be slower than a calculation that looks only at fields in the same table.

Slowness in Performing Lookups

One issue that has come to light over the course of many conversions from FileMaker 6 or earlier to FileMaker 7 or 8 is a difference in the behavior of the Lookup auto-entry field option. To refresh your memory, a lookup is an auto-entry field option that copies data from a related table into the current table. Lookups might be used, for example, to copy a unit price from a product record into an order line item record.

In FileMaker 7 and 8, it's possible, and indeed advisable, to replace the Lookup option with a different auto-entry option that gives exactly the same results and behavior but is much faster with large record sets. The preferred method in FileMaker 7 and 8 is to use the "Calculated value" auto-entry option instead. The calculation should simply make a direct reference to the related field you intend to copy.

As an example, suppose that you have a table of order line items related to a table called Product. In the order line item, you have a field UnitPrice, defined as a lookup, where the data copied is simply the UnitPrice field from the Product table. A better choice is to define the field with a "Calculated value" auto-entry option, where the calculation simply reads Product::UnitPrice. The behavior is exactly identical to the behavior of the Lookup, but when the related record set (Product, in this case) is large, the performance is potentially much faster.

Slowness in Scripts

You can do almost anything in a script, so in some sense a slow script could be caused by anything that could cause slowness elsewhere in FileMaker. But there's one additional point we want to make here: You can often speed things up by using some of FileMaker's complex built-in functionality from a script, rather than building things up from simpler script steps.

Use Replace Rather than Loop

Assume you have a batch of records representing library books. Once a day you want to scan the book list, find all those that were due yesterday and are still checked out, and mark them overdue. Well, you could solve this with a loop: Find the right records, go to the first one, loop through the whole set, and perform a Set Field on each record. But there's a quicker and easier way: Use the Replace Field Contents script step. This step lets you specify a field and a value to put into the field. The value can be a hard-coded value, or the result of a calculation, possibly quite a complex one. (The latter technique is called a calculated replacean essential tool in a FileMaker developer's toolkit.)

In any case, a Replace Field Contents, calculated or otherwise, has almost exactly the same effect as a Loop/Set Field combination, and is often much faster. In simple tests that we've performed, Replace seems to run about twice as fast as Loop.

Go To Related Records Versus Searching

The Go To Related Records script step is one of FileMaker's most powerful tools. Using this step, you can navigate from a starting point in one table to a related set of records in some other table, via the relationships defined in the Relationships Graph. (Technically, you are navigating from one table occurrence to another via the graph.) This navigational "hop" can be much quicker than running a search in the desired table.

Creating Records

Under certain circumstances, creating records can be a slow process. Specifically, record creation will be slower the more indexes you have on a FileMaker table. Indexes on a table are updated every time a table record changes, and each index on that table may potentially need to be updated. As a general rule, indexes cause searches to run faster, but may cause record creation to be slower.

Connectivity and Related Issues

There are many scenarios in which FileMaker's behavior may be affected by network and connectivity considerations. Unless you are working alone with a FileMaker database that lives on one single computer, and is used on only that computer, you're likely going to find yourself in a situation where FileMaker data is being distributed over a network. This situation offers a number of potential problems.

Inability to Contact the Server

What happens if you're running FileMaker Server and your users can't see your files? There could be any number of reasons for this turn of events, but this list contains a few of the most common reasons.

• Server is down Verify that the server is running via inspection or a network utility such as ping.
• Server is up, but FileMaker service is not responding Verify that both the FileMaker Server and FileMaker Server Helper processes are running. (Without the Helper process, clients cannot connect to the server.)
• The server machine is working and the processes are running correctly, but the files have not been correctly set for network hosting Make sure that you have granted network access to the files for at least some users.
• The files have been placed on the server but are not opened for sharing on the server Even if the files are on the server, with appropriate network hosting, it's still necessary to instruct FileMaker Server to open the files for sharing. If the files are marked Closed in the Files area in the Server Administration Tool, they are not open for hosted sharing.
• Users may not have appropriate permissions to see the hosted files In the FileMaker Network Settings dialog, it's possible to specify that the file will be visible for network sharing only to users with certain privilege sets. Users with insufficient privileges could in theory have no privileges that would allow them to see any of the hosted files.

  Making files available via network access is covered fully; see Chapter 25, "FileMaker Server and Server Advanced," p. 779.

• Firewall problems If there's a firewall between your server and any of your users, the firewall needs to pass traffic on port 5003. If this port is blocked, users will probably not even be able to see the server, much less access any files on it.

Crosstalk

If a user comes to you and says that all of last week's sales data has disappeared, there are a number of possible causes for this effect. It's possible, of course, that last week's sales data really is gone (in which case you'll want to price tickets to Nome). But it's also possible you've been bitten by a case of crosstalk.

Crosstalk had the potential to be a serious problem in previous versions of FileMaker. The File Reference feature that debuted in FileMaker 8 has taken a lot of the sting out of the problem, but the potential for trouble remains.

The trouble stems from the way FileMaker Pro resolves external file references. To access any content from another file, you must first create a file reference to it. You need to do this to add tables from an external file to the Relationships Graph, or to call a script from that file, or to use a value list from that file. In FileMaker 8, you create and manage file references explicitly. In versions of the product prior to FileMaker 8, FileMaker managed them for you, behind the scenes, making it easy to lose track of what you were doing. FileMaker also had an automatic search routine, so that if it couldn't find a file in the first place it looked, it might look elsewhere, and come up with the wrong copy of the file.

FileMaker 8 does not perform this sort of automated search for your files. Instead, it looks to the file references you've set up. If it exhausts the search path for a file, it stops looking. However, if you specify a multi-element search path for a file, you could still get into trouble.

Suppose that you're developing a system that has one file for Customers and another for Orders, each containing a cluster of tables to support its function. You're developing the files locally, on your own computer, so to bring the Order file into the Relationships Graph in the Customers file, you'd create a local reference in the Customers file, as shown in Figure 17.10.

Figure 17.10. A file reference with one local entry.

Now suppose that you deploy the files to FileMaker Server. Now you'd like to add an element to the Order reference's search path that points to the copy of the file on the Server. You place it before the local reference, so that FileMaker looks to the server first, as shown in Figure 17.11. This has become a bit dangerous! If FileMaker can't find that file on the server, it looks for it on a user's local hard drive, and if it finds it, it opens it up and uses it. At the very least, you the developer need to be quite careful in this scenario about which version of the file you're working with.

Figure 17.11. A file reference with multiple search locations defined.

Other users aren't likely to have a copy of the file locally. But suppose that you had made a reference instead to a shared network drive? That could cause a real headachethe server goes down, the network drive is still in the search path, and clients begin to access and enter data into the nonserved copy.

Again, the elegance of file references makes this danger much less severe than in the past, but it still pays to be aware of the issue to avoid any pitfalls.

Tip

If you've got a system that's being hosted on a network, and you are still doing development work on another copy of the system, there are several anti-crosstalk precautions you can take. In the first place, make sure to work in single-user mode. One useful practice is to add scripts to every file you create, to toggle between single-user and multiuser mode. In the case of multifile (not multi-table) solutions, it's possible to create a single master script that flips between single-user and multiuser mode for a whole file set. And the other precaution is always to archive your old working copies by some form of compression, so that they can't be accidentally opened and hosted on the network.

Context Dependencies

The idea of context covers a lot of ground. Speaking generally, it refers to the fact that many actions that occur in FileMaker don't happen in a vacuum. The effect of certain script steps, calculations, or references can vary depending on where you are in the system. Where you are means specifically what layout you're on, what window you're in, what mode you're in (Browse, Find, Layout, or Preview), and what record you're on in the current table. Each of these dependencies has its own pitfalls, and each one is discussed in the sections that follow.

Layout Dependencies

Be aware, when writing scripts, that a number of script steps might not function as you intend, depending on what layout is currently active. Most of these steps require certain fields to be present on the current layout. These include the Go To Field, virtually all the editing functions (Undo, Cut, Copy, Paste, Clear, Set Selection, Select All, Perform Find/Replace), all the Insert steps, Replace Field Contents, Relookup Field Contents, and Check Selection. These are all script steps that act on a field on the current layout. You can run each of them without specifying a field, in which case they run on whatever field is current. They can also be run with a particular field specified. If you specify a field, and for some reason the script is invoked on a layout that doesn't contain the field, the desired action doesn't take place. Even if you don't specify a field, the odds are very strong that you have a specific layout on which you intend that script to be run. In general, these script steps are somewhat fragile and you should use them with care. If you do use them, you should be sure that your logic guarantees that the correct layout will be current when the script step runs.

Table Context

You're certainly familiar with table context if you've read much of the rest of this book. The topic was introduced in Chapter 6, "Working with Multiple Tables," and it plays an important role in most other chapters as well.

FileMaker 8 databases can contain multiple tables. For many actions in FileMaker, then, it's necessary to specify which table is the current one. For new records, to what table does the new record get added? When I check the current found count, for which table am I checking it? And so forth.

Table context introduces a new kind of layout dependency, and one which, in our opinion, dwarfs the old layout dependencies of earlier versions of FileMaker. If you're not aware of table context and don't handle it correctly, your FileMaker solutions may appear to be possessed. They will almost certainly not behave as you expect, unless your system is extremely simple.

There are quite a number of areas in FileMaker 8 where table context comes into play. A brief recap of each of these is provided here.

Caution

As with other kinds of dependencies, it's important to make sure that the context is correct for an operation before trying to perform that operation. This is a special pitfall for scripts, which can easily change context during script operation via a Go To Layout step. If your script steps are context sensitive, make very sure to establish the proper context first!

Table context is probably one of the trickiest areas of FileMaker: powerful, but full of pitfalls for the unwary.

Layouts

A layout's table context is determined by the table occurrence to which it's tied. Table context governs which records the layout displays. Note that the link is to a table occurrence, not to a base tablethis is significant if you'll be working with related fields, or navigating to related record sets (via the Go to Related Record step). In that case, the choice of table occurrence can make a difference in the contents of related fields.

Importing Records

When you import records into FileMaker, the target table is determined by the current table context, which is of course determined by the current active layout. Before importing records, manually or via a script, be sure to go to the appropriate layout to set the context correctly.

Exporting Records

Exporting records is also context dependent. Furthermore, if you're exporting related fields and you're exporting from a base table with multiple table occurrences, the choice of table occurrence from which to export might also make a difference. As in the case of importing, make sure that you establish context before an export.

Calculations

Calculations can also be context dependent, in very specific circumstances. If a calculation lives in a base table that appears multiple times in the Relationships Graph (that is, there are multiple occurrences of that table in the Graph), and the calculation references related fields, then the table context matters. The Calculation dialog in FileMaker 8 has a new menu choice at the very top, where you can choose the context from which to evaluate the calculation. If the calculation matches the criteria just mentioned, you should make sure that you get the context right. In other cases, you can ignore it.

Value Lists

Like calculations, value lists can also access and work with related data, via the options to Also Display Values From Second Field and/or Include Only Related Values. Here again, if the value list lives in a base table that appears with multiple occurrences, and it works with related data, the table context will be an issue and you should make sure it's set correctly.

Scripts

Every script executes in a particular table context, which is determined by the table context of the current layout in the active window. (FileMaker 8 can have several windows open within the same file, and they might even display the same layout.) A large number of script steps in FileMaker 8 are context dependent. If you fail to set the context correctly, or change it inadvertently during a script (by switching layouts or windows), you could end up deleting records in the wrong table, to take an extreme case. Interestingly, FileMaker 8 currently doesn't offer a Set Context script step. You need to establish your context explicitly by using a Go To Layout step to reach a layout with the appropriate context.

Mode Dependencies

A variety of actions in FileMaker depend on the current mode. In other words, things taking place in scripts (which is where these dependencies occur) don't happen in a vacuum; they depend on the current state of the application and the user interface. To take an easy example, some script steps don't work if the application is in Preview mode, including especially the editing steps such as Cut, Copy, and Paste, and others such as Find/Replace and Relookup. If you have a script that's trying to execute a Relookup step, and some other script has left the application in Preview mode, your Relookup won't happen.

Note

The Copy command does actually have one meaningful and useful behavior in Preview mode. If no target field is selected, a Copy command executed in Preview mode copies the graphic image of the current page to the Clipboard.

Most of these mode dependencies are really "Browse mode dependencies," because in general it's Browse mode that's required. But a few other mode-based quirks are also important to remember. A few script steps have different meanings in Find mode than in Browse mode. In Find mode, the Omit script step causes the current Find request to become an Omit request, whereas New Record and Delete Record create and delete Find requests respectively. These three steps work differently in Browse mode, where they respectively omit a record from the current found set, or create or delete a record.

If you're using such script steps, the answer's the same here as elsewhere: Explicitly set the context if you're using script steps that depend on it. In this case, you should use an explicit Enter Browse Mode script step when using steps that depend on this mode.

Note

Some FileMaker dogmatists go so far as to suggest that every script you write should begin with an Enter Browse Mode, unless otherwise required. We think this policy may be a bit much in practice, but the spirit of it is dead-on: Be aware of these dependencies, like others, and handle them explicitly.

Finally, there are also mode dependencies that occur outside the context of scripting. A number of FileMaker's presentation features are dependent on Layout mode. These include the capability to display data in multiple columns, the capability to show the effects of any sliding options you may have set, and the capability to show summary parts and summary fields.

The Record Pointer

In addition to all the other elements of context, there's one other important one. Quite a number of scriptable actions depend on what record you're currently on. You might remember that this is a function of two things: what layout you're on (which in turn translates to a table occurrence, which in turn translates to a base table), and which window you're in. FileMaker 8, you may remember, supports multiple windows open onto the same layout, each with its own found set.

Within each found set, FileMaker keeps track of something called the record pointerin other words, on which record of the set you actually are. This is indicated both by the record number in the status area, and possibly by the small black bar that appears to the left of each record in list views. Figure 17.12 shows both of these indicators.

Figure 17.12. The small black bar in a list view indicates the record pointer's position. The status area also shows the current record number.

Some script steps are affected by the record pointer, whereas others affect it. Obvious cases of the former are Delete Record and Set Field. The record that gets deleted, and the field that gets set, depend on which record you were on to start with. These kinds of cases are clear and trivial.

Less clear are the steps that affect the record pointerin other words, that move it. Say you have a found set of 7 found records and you navigate to number 5 and delete it. Which record do you end up on? Old number 6, or old number 4? Old number 6 is the answer: Deletion advances the record pointer (except, of course, when you delete the last record of a set). The omission of one or more records from the found set is treated like deletion as far as the record pointer is concerned.

What about adding or duplicating a record? Is the additional record created immediately after the current record? Just before it? At the end or beginning of all records? Well, it turns out to depend on whether the current record set is sorted. If the record set is unsorted, new or duplicate records are added at the very end of the found set. (More exactly, the set is then sorted by creation order, so of course the newest records fall at the end.) But if the record set is sorted, things are different. New records are created right after the current record. A duplicate is created at its correct point in the sort order, which could be immediately after the current record, or possibly several records farther along.

The bottom line is that you need to be aware of which script steps move the record pointer. This is a particular pitfall inside looping scripts that perform these kinds of actions, such as a looping script that deletes some records as it goes. If, on a given pass through the loop, you don't delete a record, you need a Go to Record/Request/Page [Next] to advance the record pointer; but if you delete a record on one pass, the pointer advances automatically, and unless you skip the "go to next record" step this time around, you'll end up one record ahead of where you want to be.

Globals

Global fields (which in FileMaker 8 are more exactly called "fields with global storage" because "Global" is no longer really a field type) have long been a powerful feature of FileMaker Pro. But there are a few nonobvious facts about globals that can cause problems and confusion.

Unlike data values that are placed in record fields, the values of global fields are specific to each database user (if the databases are being run in a multiuser configuration). That is, if you have an invoicing system with an Invoice Date field, every logged-in user sees exactly the same invoice date for invoice record number 1300. By contrast, if you have a globally stored field called gFlag, it's possible that every single user could see a different value for that global field. If a global field gets set to a value of 1300 by one user, that value isn't seen by other users. They each have their own copy of the field, unlike a nonglobal data field.

It's helpful to remember that when a file containing globally stored fields is first opened, all global fields are set to the last values they had when the files were last open in single-user mode. This means that users in a multiuser environment can't save the values of global fields. When a user closes a file, all global fields associated with that file's tables are wiped clean. (In effect, they disappear.) If the same user reopens the file, all the globals will have reverted to the server defaults. This is an important troubleshooting point. If you are relying on global fields to store important session information such as user preferences, be aware that if the user closes the file containing those globals, all those session settings disappear, and reopening the file does not, by itself, bring those stored global values back.

From a troubleshooting perspective, it's important to remember that globals are volatile and session-specific.