Category: FileMaker Tips

For all practical purposes, FileMaker 7 took away the limits for how complex and large a FileMaker database can be. What it didn’t do is massively increase performance so that FileMaker 7 or 8 will always run at a acceptable speeds when processing and working with very large amounts of data.

Since you can now create big databases that run slower than you like, FileMaker Inc. wants you to know ways you can improve performance when you are using big files.

At Devcon 2005, FileMaker Inc. guru, Andy LeCates took some of his limited presentation time to advise us to turn off unnecessary indexing. Indexing a field means that every time something is done to modify the data in that field – such as entering, editing, replacing or importing – extra time will be taken to update that field’s index.

By default, fields are set to Automatically create indexes as needed. And the easiest way to create your databases is to just leave that setting that way. Quick and dirty development that focuses on getting working versions up and running as soon as possible to get early user feedback, should leave this setting alone.

However, once you are in the polishing stages of development, if you have a reason to optimize performance, indexing is one useful place to look. You can go through any table and look to see which fields have indexing turned on. And turn some of those indexes off by clicking None and unchecking Automatically create indexes as needed.

If you look at the field definitions list, you may see a group of similar fields such as 8 rates fields and only one of them is indexed. You can probably safely assume that indexing rates is a rare need and just turn off indexing for the rate that had indexing turned on (automatically). It is better to avoid the performance hit of indexing than to make this rarely done find faster.

Technorati Tags: filemaker, filemaker7, filemaker8

In my first really large-scale FileMaker 7 development project, I have created over 600 scripts. This is not something I am proud of. I would love to have 200. The more I use 7, the more often I consolidate script functionality into generic scripts driven by script parameters. You can save yourself a lot of scripting time by writing one script instead of 10 or 20.

Print Setup. Here’s a quick example. In FileMaker 6 and earlier, I always had a script for each print setup option. At minimum that would be Vertical Letter, Horizontal Letter and #10 Envelope. I then put a script step into each script that is supposed to print that performs one of these scripts.

No more. Now I use one print setup script and reference the single script with a script parameter to select the print setup option I want. In my single script, I use an If – Else If construction. Once you have an Else If section including the Print Setup step built, you can just highlight it and duplicate it as many times as you need Else If sections. Then just modify the script parameters and print setups right there.

If ( Get (ScriptParameter) = “Vertical Letter”
Print Setup [Restore; No Dialog]
Else If (Get (ScriptParameter) = “Horizontal Letter”
Print Setup [Restore; No Dialog]
Else If (Get (ScriptParameter) =#10 Envelope”
Print Setup [Restore; No Dialog]
End If

Go to Related Records. Here’s another example. In my FileMaker 6 solution, I had about 10 scripts in my Contacts file that would go to related records in other files. In my 7 solution, I replaced these 10 with a single script using script parameters with If – Else If again. I run this single script with the parameter as my code to tell the script which Go To Related Record step to run.

Reports usually have similar construction, so I can often lump several report scripts into one again using the If-Else If construction with script parameters. My FileMaker guru friend, Ted Fehlhaber, has been known to do all reporting with a single reporting script. I’m not that rigorous, but maybe he’s on to something.

I do have one *extra* script called Preview-Print-Return that I run at the end of my report scripts so that I can avoid entering the same steps over and over again. I also have an *extra* script that saves my place at the beginning of the report script and an *extra* script that checks to see if the user has enough privileges to run the script at all.

Extra generic scripts are generally scripts well spent. It’s the unique, single-use scripts that I look for ways to eliminate or consolidate. It’s worth pausing for a couple minutes now and then to see where generic scripts using script parameters can get the job done more economically.

I’ve been working with FileMaker 7 a lot these last 6 months. That’s every work day, several hours a day. And, I recommend that you go all out with the automatic audit fields that FileMaker 7 will keep for you. Put 10 fields into every table just to automatically track record creation and modification. Here is the creation half:

It’s often helpful to a user to see when a record is created and when it was last modified. You might see, for example, that this is a long-time customer. Or, an old customer who hasn’t had a new order in years. The magic word these days is context. And this kind of information provides context.

So much for the average user’s needs. As a developer, I also care a lot about trouble-shooting. Coming to the rescue in the rare cases (seriously) that my customers have problems they can’t solve themselves. These audit fields come in very handy when you are trying to figure out what happened. What went wrong?

Yes. User error is often the explanation – especially after a system has been working just fine for a long time. With audit fields, you can narrow down very specifically when something went awry and you can probably tell who was at the wheel when it happened.

In 7, you can not only get the user name of the person who created and last modified the record, you can get the account name. I like using the user’s full name as their account name. That way I can match the user’s account name to their employee record full name and set up their session with things like the contacts they’ve marked and can go considerably further than that if I want.

I want both user name and account name in case a user is at a different computer than usual or is signing in as an account name other than their own. Assuming an identity of sorts. Just as well have the extra info when it comes time to sorting things out. The date and time comes in handy also. It might be that at that particular date and time, a temp was in working. All part of the sometimes fun detective work that comes with the territory.

The New Timestamp field. I still haven’t figured out too many uses for this timestamp field type, but my guess is that it is great for calculating elapsed time that may cross over between days. The thing I don’t like is that I can’t get the field to be very readable in a list. The two sub-fields – date and time, don’t line up. For most purposes, I like the good, old-fashioned date and time fields better. But since it’s automatic, why not get it all?

Make sure to set those audit fields to Prohibit modification of the value during entry. You want to be able to rely on this information and don’t want it arbitrarily changed. Whenever you need a user-modifiable date, time or timestamp field, just create those separately even if you use the auto-enter creation field options for them.

One last thing. This is still just a thought, but I’m planning to try it out the next time I create a FileMaker 7 database from scratch. Create one or more template tables just for the field definitions piece perhaps. Then crank up FMrobot (on a PC) and have it create the 10 or so tables that you need – all with the one or more of your little templates. The basic template will have your 10 audit fields in it and a serial number field and a global for the serial number for starters. Until we can duplicate a table with the Define Database dialog, we can use FMrobot to help us out with the field definition piece at least.

If you have a database with multiple tables in a single file, you will want to do things differently in terms of scripting. Here are some of the nuggets of advice I have on the subject:

Group your scripts by table. This isn’t quite as simple as it sounds because scripts in 7 can easily do work in several tables. So, a reasonable rule is – put your script in whatever table group it starts in.

All of a sudden, you have a very long script menu. It’s not fun dragging a script from the bottom to the top or even half way in one direction or the other. I hope a few keyboard shortcuts get added so we can move something quickly to the top. Or so that the scripts I import go in where my cursor is. Or so I can drag a group of scripts around and quickly put one table’s scripts in a different position in the list. Some sort options would be interesting. But that’s later.

Create a blank script with about 30 asterisks in it as your divider between tables. A bullet or 2 or the little hyphen divider isn’t good enough when you are speeding up or down the list. You need something very easy to see.

Eliminate a lot of scripts such as all the scripts that individually go to a particular table (used to be an Open File script) or individually go to particular layouts. You can use script parameters in buttons and in scripts themselves to give a parameter to a subscript

Combine scripts. You used to have a script in file A that did a little work in A and then called a script in file B to finish the job. Why bother creating 2 scripts? Just pick the script among the 2 that is longest and add the script steps from the other script in and delete the shorter script you started with. Cleaner. Shorter script list. You’ll have to decide how much of this demodularizing you want to do.

Modular is good. But long script lists aren’t fun. I might wind up taking 8 scripts and condensing them down to 2-4. I don’t have a modular script for its own sake – just because it does a discrete, logical part of the work. I l only create a separate script when that module gets used at least twice. And if it is 2 steps long, I’ll probably just put those 2 steps in twice.

Modules really work when they are used a lot or they are long. You fix it or change it once and you are done. That’s why I use single step scripts for page setup. If the script moves to a different environment with different printing requirements, I only have to fix the generic page setup scripts, not every report script I have.

Create generic scripts. With just a little thinking, you’ll quickly find ways to use script parameters to create generic scripts. Generic – most used scripts – go to the top of your list. It saves dragging.

Give up on showing scripts in the menu for your users to use. You only get 10 keyboard shortcuts. And it’s not going to be pretty if you show a lot of scripts. You’ll need to narrow down to some most important scripts for that menu if you use it at all.

Consider using a two-letter table prefix in front of script names. Try this: “CT New Job”. This script starts in the Contacts table and creates a new job. If you convert from 6 and import a lot of scripts from various files that are now all in one file, you’ll find that some of your script names duplicate. You get New Job, New Job2 etc.

Once you add a table Prefix, you don’t need to worry about duplicates anymore and get to use the name without the number at the end. Nicer. But do you really want to junk up all the script names with prefixes? I’m still just putting them in when I think it will help. Now the only problem is that my naming convention for scripts isn’t consistent. Oh well…

Here’s a tip from highly respected Marin County FileMaker developer, Ted Fehlhaber:

Finding and Opening the Relationship Dialog
Anyone whose tried working with a crowded Relationships Graph has run into the problem of finding the right relationships icon (those little boxes between Table Occurrences). Once found, it can be difficult to select and open the right one.

Here’s the secret: If you select a TO that has only one relationship in a particular direction and hit the arrow key in that same direction, the first hop will select the Relationship icon. From there, Cmd-O (Mac) or Cntrl-O (PC) will open up that Relationship dialog. No more clicking around to try to select the right one.