FileMaker Fever

Address Clairvoyance and List Controls in FileMaker 8

Even though I knew in theory that clairvoyance and value list controls in FileMaker 8 were cool, it wasn't until I started tinkering with my address entry fields in Studio Manager that I found out how cool.

The thing about address fields is that everyone has them. Which of those fields can benefit from clairvoyance and list controls? I experimented with it and submitted my findings on my Studio Manager Bulletin blog. These checkbox features are just the kind of thing my clients can implement for themselves.

I'm sure you couldn't justify putting a value list on the zip code field in a lot of situations. But in a place where most business comes from just a few zip codes like in a major city, this might have some value.

By the way, the arrows and clairvoyance disappear seamlessly when you access the file using FileMaker 7. These 2 features are 7-Safe.

Technorati Tags: Clairvoyance, FileMaker 8, FileMaker8, List Controls

How to Speed Up Your FileMaker 7 and FileMaker 8 Databases

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

FIleMaker 8: New Debugging Tools

Besides an improved debugger in FileMaker 8 Advanced, you now have two new tools in your debugging toolbox: Script Disabling and Data Viewer.

Script Disabling. I frequently have wanted something like this in longer scripts. When I'm working on a problem in one part of the script, I want the script to just run the part I am working on. Now that's easy by just disabling the lines I don't want to run with the Disable button in the Script Edit dialog box.

Data Viewer. This viewer makes it super easy to see what your script is doing - exactly. You can view the contents of any fields you want as the script goes through its paces.

Good stuff. FileMaker 8 Advanced is a must have for anyone who is developing a FileMaker database with any significant level of complexity at all. I consider the upgrade price of $299 to be a steal. But if you are someone who is developing databases, the very best way to get it is to become a FileMaker Solutions Alliance member and get it plus the regular version of FileMaker 8 as a benefit of membership.

Technorati Tags: filemaker8

The Economics of FileMaker 7 Scripting

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.

FileMaker 7: Recommended Audit Fields

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.

Adventures in Data Protection with FileMaker 7

One of my clients just called today about a simple error that caused a lot of upset. Someone had accidentally erased the first and last name out of an employee's record. My client thought that the employee's data had disappeared completely because a Find didn't turn up the employee anymore.

The erasure might have occurred when a user thought they were in Find mode and overwrote the name and then realized they were in Browse mode and erased their mistake not quite putting it together that they needed to retype the original name which they may have not remembered.

At least that is how we guessed the problem occurred. In FileMaker 6 and earlier, you needed to either leave data editable so that Finds could be done on those fields, or you needed to create a calculated field for each of the fields and display the data with the calculations except on a protected layout where data entry could actually occur.

There's still an issue here, but we now have a new and very handy tool in our toolbox. The Field Behavior command. We can now turn off the ability to Browse in a field while allowing the ability to Find. All those layouts where data entry is not needed should have the fields set with the "Allow field to be edited in Browse mode" unchecked. When you do that, the user can't click into that field in Browse mode anymore.

The issue I have is that I don't want to have to lock all fields on non-data entry layouts or create special data entry layouts that are unlocked to avoid this problem. It seems like a lot of extra work. Probably the best bet is to reserve this extra work for situations where there are many inexperienced users using a particular screen and likely to use that screen to do finds as well as data entry. In those cases, you could lock the user out of data entry and display a custom message directing them to another screen for data entry only.

Here's what I tried: In Layout mode, I selected the approximately 25 fields that I wanted to lock and set the field behavior to not allow modification in browse mode. Then I made all those same fields into a single button by choosing "Button..." from the Format menu. I then created a script with one step: Show Custom Dialog and worked a bit on wording. Then I tried it in browse mode. When I clicked in any of the protected fields, they all highlighted at once and the message appeared. Very impressive.

In Find mode, the button effect was non-existent and the Find worked like usual. Just what I wanted. By accident, I discovered the drawback of the button with the custom message when I tried to move one of the fields on the layout. I was told that the field was part of a button and couldn't be moved without removing the button. Assigning a button to a set of fields like this isn't very flexible. You'll probably want to avoid this technique with a layout that you expect to modify fairly frequently.

FM7: Scripting in a Single File Environment

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...

eMacs as FileMaker Servers

You may want to see my post on eMacs over at my Studio Manager Bulletin blog.

Keyboard Shortcut: Opening Hard to Find Relationship Icons

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.

Saying Goodbye to Global Glitches

Now that there is no longer a global field type in FileMaker 7, I find that I often forget to go to the storage tab and click the Use global storage check box. Then later I find out the hard way. Something doesn't work and after all sorts of investigations I figure out that my global field isn't really a global. I feel pretty silly and have wasted valuable time.

Something that helps me avoid this is naming my global fields so that they all group together. In FileMaker 6, I was happily putting an underscore in front of each of my global fields like this: _InvoiceNum. That conveniently sorted my globals to the bottom of the alphabetical sort order where my clients wouldn't stumble over them very often. In FileMaker 7, the underscore sorts to the top, so I've chosen to put a zg in front of the field name like this: zg_InvoiceNum. Not pretty, but it accomplishes my purpose.

I now can periodically just go to the bottom of my table field lists as I am developing and scan through the Options column of my globals to make sure they are all set as globals. Such a scan would be a great checklist item before turning over a solution to your client. I put it on my checklist.