FileMaker 101
Part 5

Indexing fields, uses of global fields

Last month I started explaining fields. So I thought I’d continue, as they are the building blocks for everything else.

One of the basic choices, after creating a field, is whether to “index” it. If a field is indexed, it means that FM stores a special list of its contents, ready to use quickly. It then uses this list to do Finds and other operations rather than having to go back and read the field again.

You can reach the indexing dialog by hitting the button at the bottom of the Options dialog box, labelled Storage Options. There are usually three choices: Indexing on; indexing off, but available if needed; and indexing permanently off. The underlined one is the default. It happens automatically if you don’t specify one of the others, so usually you don’t have to do anything. The first time you run an operation such as a Find on that field, FM takes a little time to index it. But then the next time you do a Find, it’s very fast.

There are a few situations where you would want to check the “indexing off” box, as well as some when FM won’t let you index a field anyway. One would be if you have file storing very long text files, and you don’t want searches performed on the text itself. The reason would be that such an indexing would require a lot of disk space. It would be better to add a small “keywords” field for indexing and searching.

The main use of not indexing is to force a field to update. This allows you to use the Status functions to give you current values, useful for such things as the found record count. Normally status functions only calculate once. FM does this operation itself, displaying the value in the status bar on the left of the window. But that status bar takes up a lot of real estate. You can create your own fields to display the same info. Then you can put them wherever you want and hide the status bar, allowing a much wider layout.

To do this first create a Calculation field, with the formula:
Status (CurrentFoundCount). Hit the Storage Options button and check, “Do not store calculation results.”

If you want to see the relative number of each record as well, just type @@ with the text tool while in layout mode. The reason I say “relative” is that it changes, depending on its position in the found set. It only tells you where it is in relation to the other found records (for an unchanging record serial number see my previous month’s article). The unstored calculation, Status(CurrentRecordNumber) gives you the same thing as @@ (as far as I can tell).

Another number you might want to see is the total number of records in the database. This would also be an unstored calculation: Status(CurrentNumberRecords). It will remain the same unless you add or delete records.

To duplicate the navigation controls, draw a couple of arrow objects (or just type “”), then change them to Buttons (Format Menu). The Specify Buttons dialog will appear. Under Navigation, choose Go to Record/Request/Page, and Next (in the drop-down box). Choose Previous for the left arrow. You could also add little bars for Go to Record, First and Go to Record, Last.

Here’s a picture of what it could look like.
The @@ would display the “relative” current record number.

I’ve found one small kink relating to indexing: If, for some reason, you’ve caused indexing to be turned off for a field, then you have to go in and turn it back on again. Say you have a indexed calculation field, and you change one of the referenced fields in such a way that it can no longer be indexed. If you make it a global field or based on a relationship, then FM will turn indexing off.

You’ll notice, because even though everything still works, and displays the correct data, any operation using the field slows way way down. Even if you fix the problem, by changing the referenced field back to a valid “indexable” type, the “indexing off” box will remain checked until you manually uncheck it. You’re not likely to run into this problem if you always know what you’re doing; but if, like me, you often find solutions by trying possibilities until one works, it may happen.

You can view and paste values from the Index directly by choosing Paste Special, From Index (Edit Menu), or Command-I. All the indexed values drop down. Type the first letter or couple of letters to scroll, then just hit return or enter to paste a value. You can view them as individual words or entire entries (to keep certain words together when viewed by individual words, type an Option-space instead of a space).

Global Fields:

Global fields are quite different from the others, mostly because they remain the same for, and are accessible to all records. They can be used to store things which are static, such as the owner’s name or logo, or to temporarily hold values that are changing quickly, such as during script steps.

One great advantage of using global fields for holding temporary info is that it doesn’t use the clipboard. This allows multiple values to be stored in separate globals (or in one with repeats). It’s also faster in Scripts to Set a field to a global than it is to copy/paste data into one.

One thing globals are not, however, is indexable, but this isn’t too big a problem. If you only have one value, and it’s available to all records, there’s little need for indexing. It does limit their use, however. Any field that references a global can’t be indexed, therefore can’t be used as the destination of a relationship (though a global can be used as the originating field). A calculation using a global will have to recalculate whenever it appears on a layout, as will any calculations that depend on it. Everything will slow to a crawl (as in above paragraph on kinky indexing).

Globals are indispensable in Scripts, to store values which would otherwise become unavailable during operations, such as data, layout location, found records, etc.. They can be also be used to enter data for operations, while in Browse mode, to use for Find or Replace operations, without changing data in the records.

Here’s how to make your own Search and Replace operation using globals. There isn’t a regular one, like in a word processor, or even a spreadsheet. That’s just as well, as you can imagine the havoc an undoable Replace affecting the entire database could cause.

We’ll make one that works on one field at a time, on one or multiple records, depending on the found set. Most operations (even Delete All) only take place on the found records.

First you need to make a blank layout to work on and create the following global fields:
FieldToSearch g
FieldToReplace g
(I put an option-space then g after global fields, so I can recognize and select them easily. Many people put the g in front, but then it messes up the regular alphabetical view.)
Put them on the layout, labelled Find and Replace “Field Name.”
Make a button in your original layout to go here.

Now you’ve got to make a script. Go up to ScriptMaker and create a new script. Delete all the steps. Choose the following steps:

Go to Layout [“SearchandReplace”]
Set Field [“gSearchFor”, “”””]
Set Field [“gReplace”, “”””]
Set Field [“Field Name,”Substitute(Field Name, FieldToSearch g,
FieldToReplace g)”]

The first two Set Field steps just clear out whatever was in the boxes from the last time. Substitute is a calculation function, available under Text functions in the Specify Calculation dialog box, which appears after hitting Specify. Select the generic text, then double-click the field names to fill them into the formula instead. It’s easier than it sounds.
Now make a button to run the script.

If you want it to work on the same field in multiple found records you would use the Replace step instead, with the “by calculation” radio button, using the same substitute formula as above. The whole thing would then look like:

Replace [“Field Name”, “Substitute(Field Name, FieldToSearch g, FieldToReplace g)”]

You can check the “No dialog” after running once to test it. Remember, it’s not undoable.

If you want to use it on other fields, just duplicate the script, substituting the new field name in both the Specify field places. You can use the same global fields and layout; just make new labelled buttons for each script. You could also add them and run them simultaneously; but be careful.

Another great use of a global field, which is otherwise unavailable, is to create an “undo” script. I use it to store the text of a record while running a more complex text calculation (which searches for and deletes characters, such as “” marks and unnecessary hard returns, from imported email messages). Since it sometimes makes things worse rather than better, I wanted to add my own undo step.
So I have the script set the text into a global field first (only one record, one field). Then I have a script (an undo button) to set it back if things get messed up. Even if you do other copying in the interval, the data is still available. You do have to be on that same record, but it doesn’t have to the only found record.

Well, that should be enough to keep you busy for awhile. See you next month with the first of many Relationships.

---

Fenton Jones

Fenton Jones is a FileMaker database designer and consultant, based in San Diego, CA. FileMaker is a cross-platform rapid-development tool for affordable relational databases. If you have need of a FileMaker Pro expert, please be sure to visit his home page at http://www.fentonjones.com