This month’s article is going to be a bit of a departure. Rather than being just about FileMaker Pro (FM), it is going to include some AppleScript (AS) as well. They work quite well together, and if you like working in FM, you may find that AS can lend a helping hand for tasks that FM can’t or is not well-equiped to handle natively.

Continue reading »

This article tackles a logical problem and demonstrates a technique that is useful in other “date-time” scheduling or equipment tracking situations.

The logical problem it attempts to solve is, “How do you keep track of things when they are not being used?” It is fairly easy to enter when things are used, but in many situations it is also useful to have an entry for when they are not. This is especially true if you want to later build calculations to analyze the usage of large numbers of entries, percentages, etc..

It does, however, bend one of the basic common sense rules of database construction, so it should be used only when necessary. That rule is to not create a structure where records are going to be primarily empty. A bunch of empty records is a waste of disk space, at the very least. In this case, however, only a small percentage would be empty, and their usefulness outweighs the wastage (IMHO).

There are a couple of techniques with portals. The sections are related, but independent, so “take what you need and leave the rest,” as a wise man once said.

Continue reading »

Creating interactive help system navigation within your databases.
When a database becomes more complex and powerful, there is often a need to provide some kind of help messages or a full-blown help system to explain some of the choices.
There are basically two kinds of underlying structures that you can use. Each one has its advantages and disadvantages.

Which you use depends mostly on what it is that you want to put on the layout.

Continue reading »

Walking the Relationships
Example file: Splits

[How to use relationships to move between and perform operations on different sets of records during scripts.]

I don’t know what others call this, but it is a fairly common task in any kind of scripting. I think they call it “walking the lists” in AppleScript. By “walking the relationships” I mean using the power of relationships (often self-relationships) to move between defined groups of records during a script.

Each group can then be operated on independently, while preserving not only the current found set, but returning to the same record. As far as the user is concerned, all (s)he did was click a checkbox or something; several operations occurred, but the screen just blinked for a second.

A situation where this is needed is when something happens to one record, but the change then requires running an operation on several records, a set that is different from the current found records.

Continue reading »

Multi-keys with Portals
Example file: Storess (replaces last month’s file of the same name)*

["How to use the different lines in a multi-key to filter a portal in a variety of ways."]

Portal vs. List

In the last month’s article, with the example file, Storess Ÿ, I showed how to create the small window list. Its purpose was to allow you to choose from a “filtered” list of stores (in this case), showing only the stores that a person had already shopped at.

Continue reading »

>Multi-Key Value List Window
Example file: Storess

One of the under-documented features of FileMaker Pro is its ability to use separate entries in a field as if they were actually separate records. Combined with relationships, this unlocks the power of many-to-many linking, without even having to use a join file.

In fact, a few otherwise knowledgeable writers have criticized FileMaker for not having this ability, when it fact it does; it’s just hidden.

The entries have to be separated by paragraph returns, which is only allowed in text type fields (incl. global text fields), and there is the usual text limit of 64,000 characters.

Continue reading »

Mysterious Button Apparitions and Log Histories
Example file: ButtonRows

Quite awhile ago, back in an example file on duplicates, I included an extra layout at the back named “Magic Portal.” It was pretty wild.

It demonstrated a useful technique: how to display buttons only when you wanted them. I first saw it used in a file by John Mark Osborne, called “Visibility.” It’s available at his website (along with other great examples): http://www.best.com/~jmo

The main difference between the technique, using an invisible portal, and one using a button which is simply calculated to not be visible, is that these buttons are not active when invisible.

Continue reading »

>Auto-Enter or Lookup?
Example file:Constant.fp3

This month I’m going to revisit the problem of marking the first entry of a unique value in a field. There are several reasons you might want to do this. A common one is to show only unique entries in a portal, by relating a “1″ constant field to the marking field. Otherwise you often have duplicates showing, making the portal useless as a value list for making choices. I’ve recently discovered another method, using an Auto-enter by calculation, one of the Auto-enter Options available in Define Fields.

Continue reading »

It’s About Date & Time!
Example file: DatelTime

First I’d like to point out a few curious facts about our old friends Date and Time.
We take a great deal for granted when we talk about them, they are so much a part of our everyday lives. There are few things to take into consideration when using them in FileMaker, however.

It is really very good about understanding what we mean when we type in a date or time. If we type “5/2,” it supposes that we must mean May 2, 1999 (US date format). You can type just “1″ in a time field and it will put 1:00 am.

Continue reading »

>Loops, Numbering Subsummaries
Example file: LoopLoop.fp3

Loops are a special subset of scripts. They begin with Loop and end with End Loop; whatever steps are in between will be repeated on each record of the found set (unless otherwise instructed).

They are used whenever you need to “walk” through a set of records, usually making a comparison, then a decision whether to execute an operation on each record, before moving on to the next, exiting after the last.

In the example file I’ve created several scripts with Loops to show some common uses, as well as an unusual one. Someone asked me for a method to number Subsummaries within a found set. There is no built-in way to do that.
I extended the method to create an outline-like effect with a couple of levels, Harvard style. I’ve often wondered if I could do that, as I use outlines often (to write these little articles, for example).
As far as FileMaker is concerned, I think it makes a useful report layout, especially for information which has no intrinsic numbering (ID #’s, etc.) We like long lists to have labels, such as A., B., and 1., 2., etc.. It makes it easier to comprehend and navigate.
The actual scripts and their explanations are in the file itself. I think that makes easier reading, and they’ll make more sense after you see them in action.

There are other looping scripts in the file, which demonstrate some of the common pitfalls which we all fall into. Each step in a Loop has an effect on it, and even the order of the steps can make a huge difference.

Omit or Delete
One of the most common problems with Loops is how and when to move to the next record. It’s especially tricky with Omits and Deletes.
The tip is simple: Omit does not require a “Go to Next Record” step.
After omitting or deleting the current record, you are automatically on the next record. Try it manually (Cmd-M) on a list and you’ll see. Thus:
Loop
Omit
End Loop
will omit all the records, one at a time.

The trouble is, it won’t stop (short of “Cmd-.”). That’s the second problem with loops, affectionately known as an “endless loop.” So we add a step, after Omit:
Exit Loop If ["Status(CurrentFoundCount) = 0"]

In other scripts, the step,
Go to Record/Request/Page [Exit after last, Next]
will usually stop the loop. But sometimes it won’t. For example, if a step within the loop deletes a record that matches certain conditions, and the last record gets deleted, the “Exit after last” can’t evaluate. The loop will continue, beginning with the first record again. Eventually it will stop, when the last record doesn’t meet its conditions.

Order of Steps
The order of the steps within a loop can be important. When you are passing values back and forth between global holding fields and the regular fields in each record as it passes through the loop, it’s pretty easy to put the wrong one first, or leave out a step, especially since they tend to have similar names.
This is one place where adding the letter “g” to the field name of globals is almost mandatory.

There are a few Omit scripts in the example file that show what happens when you switch two steps.

Counters
One way to keep track within Loops is to set a counter, a global (number) field that gets incremented with every pass of the loop.
A database has the ability to set a field by the value of the same field. In this case:
Set Field ["CounterNum g", "CounterNum g + 1"]
Don’t forget to set it to 1 before the loop begins.

The Number Subsummaries script in the example file uses a counter, not to exit the loop, but to enter into a regular number field. It only increments when there is a new value in a field.

The Omit w/Counter script shows how to use the Found Count and a decrementing count (-1) to exit a loop.

New or Duplicate Record
Another problem to watch out for is the effect of the Sort Order when creating New or Duplicate records with Loops. This is critical for the position of the new record.

When records are unsorted, a New record is created as the last record.

When records are sorted by a field with only unique values, such as a Serial ID field, the New record will be the next record, just after the current record.

But, and this is the “gotcha,” when records are sorted by a field that has several equal values in several records, resulting in “blocks” of records, the New record will go at the end of the block, not necessarily the next record.
[Thanks to Ilyse Kazar for this important tip.]

The fastest Loop for Duplicating Records comes from Eric (the genius) Sheid:
Unsort
Go To Record/Request/Page [LAST]
Loop
Duplicate Record/Request
Set Field ["Contact ID", "Field1::Temp ID"]
Omit
Omit
Exit Loop If ["Status (CurrentFoundCount) = 0"]
End Loop
Find All

He begins at the end, rather than the beginning. The Unsort ensures that you’re on the last record. The ingenious double-omit Omits the duplicate and its parent record, ready for the next in line (backwards), ending when all records in the found set have been duplicated.
The Set Field ["Contact ID", "Field1::Temp ID"] is for setting the new records via the Contact ID relationship to a temporary ID in another file. I’m not sure exactly what he was doing there; I didn’t save that part. You could set a field for many different reasons at this point.

Relationships and Replace
For some final craziness, I attempted to duplicate the results of the Outline and Omit loops by using self-relationships. I was generally successful, but with some difficulties.

I found that the “Replace” via calculation operation can be used similarly to Loops, but with distinct differences. Overall, I’d say that it’s more limited in its record-selection and decision-making.
It’s faster at changing values in fields to some other set or calculated value, especially in large found sets, and should be used for that. Loops are often much slower for these simple tasks.
But it’s dismal at sorting. I was unable to get second-level lists numbered properly by sort orders when using the relationship method, while it was fairly easy with the Loops.
Sorting the relationship worked, and was necessary to get the first level numbered, but it could not sort the 2nd level. A concatenated ID and second relationship for the second level, sorted, would probably work. You’d have to go to each “block” separately, Replace, then go to the nextŠ Aargh.
Way too much overhead to do something that the Loop did easily.

Relationships and Omit
I had much better luck with the Omit scripts and relationships. Especially in the scripts finding Duplicates, either a loop or a loop with a relationship seemed to work equally well.
You could also do it using a Replace with just relationships, no loop, Replace Earlier Dupes script.
The Replace script would tend to be faster on 1000+ records, but otherwise they’re pretty equal; take your pick.

But, for “walking” your records, making complex decisions, creating new records, omitting, deleting or numbering–especially when sorting is needed–Loops are the tool to try.

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

Layout Design

In this installment, “Layout Design” Fenton “lays out” his theory of screen design and tells where to find all those nice buttons.

I would like to describe a method for layout design that I use. It’s nothing special really, kind of conservative, but attractive and efficient.

The main idea is to begin with the data, and work outward from there. That seems fairly natural, as that’s what you have to work with.

The overall theory is this: Use the least amount of layout elements to clearly convey the message. Be reasonably consistent within a type of element. Use a gradual contrast to separate elements, with small touches of dramatic contrast reserved for focusing attention.

My theory is based on a simple discovery. I found, by experimentation, that every layout element adds, by its contrast, a small amount of distraction from the data.

Contrast of Elements Explained
By contrast I mean all the ways that make elements distinct: shades of black; white or color of text or lines; relative to the background; size and position.

Some contrast is needed of course, to allow the data to stand out quickly from the background. It is a balance. Thus the “least amount” must be found.

It’s not that hard. Simply increase the contrast until the data looks good and is easy to find. Then increase it further, look away, then look back. You will notice a slight confusion, even irritation from the extra. It will be slightly more difficult to quickly read the data. Try it. Use this test on every element you add, while viewing the layout as a whole.

Often, after adding many fields and elements, you’ll run into the opposite problem, wherein you can’t quickly find the main data that you want to see first, such as a name in the header or a few primary fields. In this case, I’ve found a couple of good ways to add contrast, as long as they aren’t overdone.

Text Color
One method is to change the text color to a slightly brighter shade. If the other text is black, change that field to very dark blue (it looks almost the same, but is a tiny bit more readable). I often use dark blue for most of the fields, highlighting the few critical ones with one step lighter blue. Bright blue stands out better than any other color.

Red stands out too, but it’s irritating, so I reserve it for warnings.

Dark green doesn’t stand out quite as well, but it’s also good, especially for labels; it’s sort of tranquil.

Dark gray, rather than black, can be used for labels, which, although necessary, don’t need to be as obvious as the data in the fields. This is especially true for things like address or phone number labels, which we all recognize by their format anyway.

As I said, every element adds some distraction. Tone down the labels and the data will stand out more clearly. Do keep them readable, however.

Backgrounds
The other way to add contrast is to change the background of the secondary fields to lightest gray, while leaving the main fields white. That way your eye is quickly drawn to the main ones, and the others are still easy to read.

I usually use a Body background of light gray. (Set the background color by clicking once on the “body” tab, then choosing the fill color.) In this case though, you’ll probably want to change the overall gray background one shade darker than the fields, to preserve some contrast.

By the way, this is generally for Form views, or header or footer elements. Field backgrounds in List views are done differently (see below).

Borders
For the border of the fields, I usually use a slightly darker gray, maybe two shades darker. This is what I mean by “gradual contrast.” If you use the default black it looks “boxy,” and it becomes more difficult for your eye to move quickly to the data in the other fields.

3D
If you feel you need more separation between the elements, or to group certain elements together, use a soft 3D box, or put it around the group.

The 3D effect is made by covering two sides of the box with one or two light colors (white, lighest gray) on two adjacent sides, and one or two dark colors (dark gray) on the others to create the illusion of 3D. I’ve found that if you use two shades for each it’s more realistic.

Layout Purpose
It is essential to have a good idea of what the layout should show, and what the purpose and focus of the layout is.

It’s necessary not only to know what data to put on the layout, but also to know what to leave off. If it seems there is more than one distinct group and there are a lot of fields, give those fields their own layout. That’s what makes a database better than a spreadsheet for displaying sets of information.

There’s also no need to cram everything on one big screen, forcing users to scroll all over looking for things. A simple button, or tab, to jump back and forth is much better.

It is whether you want to see lots of data from one record at a time, or certain fields from many records, that will determine whether you choose a Form view or a List view type layout.
If there is a need for both, you could probably do what you want with a Form view and a portal or two to show lists.

List Views
In the case of List type views I almost always use the alternating background row technique in white and lightest gray behind transparent fields. (See my article from March, “Layout with Global Containers,” and the example file “AltRows” for this simple technique.) Not only does it look good and allow you to easily see what row you’re on, it also enables “the least contrast.”

You can dispense with contrasting lines between the rows. This usually allows you to make the body one pixel shorter, saving at least a pixel per row (and that’s a lot per screen), without cramping the data in any way.

List View Borders
I don’t really like the “grid” look of spreadsheets for list views. Once again, all those black lines cut up and distract from the data.

Sometimes you can leave out borders entirely. But they are often needed to separate the columns and group them with their header label. In that case an elegant solution is to give them a border on just one side and a line color of light gray (a shade or two darker than the background).

Take Sides
To make it look really good, choose the side for the border that interferes least with the data. In the case of two text fields, give the left field a border on the right. In the case of number fields, give them a border on the left. Nudge the fields a couple of pixels apart.
What you are doing is using the adjacent field to create the border, and what it avoids is having the border crammed right up against the data. It appears to float between the fields, lifting and separating (oops, that’s a bra commercial).

You Can Push My Buttons
Buttons are a bit of a problem. I mostly use the built-in 3D buttons. But I try to add a few embellishments when possible.

Buttons should be as clear as possible while remaining a reasonable size. They should be labeled with text unless the graphic is dead obvious (or repeated throughout the database after having been clearly labeled on the main layout).

One way you can customize text buttons to make them more recognizable is by using color. Most buttons I make have dark blue text with dark blue lines around them. The fill is one shade lighter than the background when possible, one shade darker when there is no lighter color (I avoid white buttons usually; too glaring).

The lines would also be dark blue. This allows the buttons to stand out, but draws no particular attention to any one of them.

Buttons for actions that really change the records get a lighter color. For New Records and navigation scripts to other files I use a lighter, more electric blue color, and lighter blue text. This says, “I do something!”

For Deletes I always use dark red text and brighter red line–”Be careful!”

For the button that returns to the main control panel for the whole database I use bright blue text and neon green line, hard to miss if you’re lost or in a hurry to get out. I try to put it in the same place on every layout that has it, which in most layouts is on the far bottom right.

Consistent Locations
“Be reasonably consistent within a type of element.” This is very important with buttons. We associate them with the look and location we saw them last. While this is not a hard and fast rule, it’s a good idea to try and put the same button in the same place on different layouts where it is needed.

This is not to say that it should be on all layouts. Once again, the “least amount of elements” would mean not to put things on a layout that aren’t useful there, unless your overall design requires it.

I sometimes bend the “consistency” rule, especially in cases where the layouts are so different that you can’t reasonably put the button in the same place. A Form view Main Menu type layout and “letter” or report view have little in common.

Alignment and Balance
This is another mysterious area. Suffice it to say that some kind of alignment is better than none. There is no need, however, to have everything line up exactly on their edges.
One thing I’ve noticed is that even unrelated objects should line up with something if possible. This is especially true if an element is just a little out of alignment, but not enough to be considered as part of some other arrangement.

For example, there are some horizontal fields, lined up along their bottoms, with a small graphic button on the end, at the right edge of the screen. Up higher on the layout, there is another similar, though not identical graphic by itself. It really looks better if the upper graphic is moved to line up with the lower, on its outer edge, even if it’s a different size, so the overall layout has a right edge.

Or there is one wide field, with two smaller ones under it. If possible, the first of those should line up on the left, and the second on the right of the one above. Either the fields and/or the space between them should be adjusted to make it so.

Sometimes center-alignment is a good choice when neither right or left will do. These are small things, but they add up.

Spaces between objects should be given roughly the same care as objects themselves. That is, they should be the appropriate size to separate the data fields with their respective labels, uniform within groups of fields and roughly uniform between groups (a pixel or two doesn’t matter).

Overall, the layout should have a pleasing balance. This is something that you just have to sit back and adjust. Move things around. Group the data fields that belong together. Balance the data with the space.

Make a copy of the layout to try out real different arrangements (as it’s a pain to recreate a messed up layout).

So that’s my method. I’m not saying that you should do yours the same. But do give it some thought, look at other files and develop a style of your own.

Databases can be aesthetic; they will not only look better, but they make it easier to find what you’re looking for, which is what it’s all about, right? They don’t have to look like a spreadsheet or a bunch of bare black boxes on a white background. Neither should they look like a piece of abstract modern art (unless it looks good and works), nor like a peacenik’s vest covered with buttons.

I like to think of mine as simple control panels for personal data-space craft

Sliding Rows
OK, here’s an actual useful tip, for those of you still awake. To achieve the ultimate soft 3D look with a list-type view, draw (in Layout mode) a 1 pixel horizontal line across the bottom of the Header for the whole width of your layout, right over the black default line. Do the same for the Footer (if you have one), but nudge the line down one pixel below the dotted line (elements touching a dotted line belong to the part above it).

Make the top line a shade or two darker than the Header and the bottom one white.
The body now appears to be inset beneath the other parts. When you scroll down, the list view rows appear to be sliding underneath. It’s classier looking than the black lines and besides, it adheres to the “least contrast” rule (OK, I’ll never say it again).

Moving, Deleting Parts
Sometimes you want to remove a part (Header, Body, etc.), but keep the fields that are in it. Normally if you press Delete, you’re asked if you want to delete the part and everything in it. If you hold the Option key, just the part will be deleted, without dialog, and all the objects will remain just where they were.

Other times you want to adjust a part, moving its line up or down past existing objects; it stops when it hits one and won’t go further. Once again, hold the Option key and it will glide right past. If you slide a Header line all the way to the top, it’s the same as deleting it (above).

There is a reason for the default “stopping” behavior. It lets you move the part line snug to the bottom of fields or labels without having to look hard to get it pixel perfect, very useful in list views. Remember the Option key to get the best of both worlds.

T-Square
Sometimes one or both of the T-square tool’s lines are difficult to grab quickly, either far away or off the visible screen. Rather than trying to scroll and grab the one you want and then drag it back, simply hold the Command key and hit “T” twice. The first hit makes them disappear, the second brings them back centered on the screen.

Duplicating fields and Value Lists
Once you get one field all fixed up like you want it (formatting and lines), you can Option-drag it to create another. The Field dialog will conveniently pop-up so you can reassign it. Hold the Shift key to keep it lined up.
Caution: When you duplicate a field that has a Value List attached or Auto-enter options, the duplicate will have them, too. Usually you won’t notice this until you click in one in Browse mode and the wrong list drops down.

Field Format Dilemma
You can select several fields at once to strip or add a value list to all of them. This can happen even if you don’t intend it to.

If you select several fields–one of which has a value list–and trigger the Field Format dialog and change the value list options (or any of the other options), their entry status (entry or non-entry, select contents), or change the number of repetitions, they will all change.
If no value list is showing in the dialog box, all selected fields will lose their value lists. If you click on the value list radio button to add or modify one when they’re all selected, they’ll all get it.

It is difficult to predict whether a value list will be showing when you open the dialog. If a field has a pop-up list, it tends to show. If it’s a pop-up menu, it doesn’t show. It probably has something to do with pop-up lists being the default.

In any case, you have to pay attention if you select groups of fields with value lists or they will get changed behind your back. If you have any doubts at all, hit cancel and check.

Button Templates
I have a little Layout file to store small graphics that I use, most borrowed, some modified, some homemade. Everyone needs “Sort, Print, List, Find” graphics.
For these common things I’ll use a small graphic, perhaps with some small text, perhaps alone.

You can also make your own graphics in a drawing or painting application. ClarisWorks is good for simple ones. Keep in mind that complex graphics can add file size and slow screen redraw considerably.

Drawing graphics for buttons is probably alot like drawing icons, that is, they’re so small that you must draw in a magnified view, but then they look quite different at 100% view.
I suggest having the two views open at once on the screen, so you can see what your changes are doing; it’s often surprising.

It’s also a good idea to set the “Rulers” to have more divisions. I put 72 per inch. This gives more control when nudging with the arrow keys.

Buttons on the Web
I searched my FileMaker sites and assembled the following list of graphics templates.

The king of FileMaker buttons is undoubtedly John Krische. His “1000 Buttons” series, up to Volume IV now, has hundreds of layout objects at a reasonable price ($5-$10 file). I had a little trouble with expanding the Mac (.bin) files; I ended up downloading the PC (.zip) files, which worked fine. Perhaps you’ll have better luck
.
http://www.krischesystems.com

Another nice set of buttons is by Parker Bennett of Mogulsoft (databases for the entertainment industry). It’s generous of him to give these away free. (His download server was down, so this is his main page.)
http://www.mogulsoft.com

This is a basic set of buttons and other layout objects released by FM itself (long ago), also on the FileMaker CD.
ftp://ftp.claris.com/pub/USA-Macintosh/Templates/FileMakerPro4/ButtonsAndDialogs.bin
(that’s all one line, folks).

A multi-volume set of free buttons by Charles Lunsford is available at this address. It has a lot of buttons, most with an unusual pastel colored interface theme.
http://home.HiWAAY.net/~cullman

See you next month!

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

Documentation: External

Oops. There was a small mistake in last month’s article. Near the beginning I had a few field names. Underneath I explained that one of them was a related field; but I got the name of the relationship wrong. It said:
*(Self_Service being the name of a self-relationship of the Job ID)
That sounds more like a car wash. It should be, and hopefully you figured this out:
(Self_Job ID being the name of a self-relationship of the Job ID)

Last month I wrote about how to “comment” your work within FileMaker Pro. Now I’m going to continue with a discussion of various ways to document your work outside the database files themselves.

There a few reasons why you would want to do this:
If you were developing for someone else, they’d certainly want some external map of what you’d done. In an extreme case, it might be the only way to rebuild badly damaged and unrecoverable files.

For me, the main benefit of external documentation is that it is “searchable.” In the case where I just couldn’t find what a certain field did, or whether it was obsolete, I could read and compare printouts or search for it.
You can print the Field Definitions (all of them) and the Scripts (all or individually) directly from the Print dialog box. You can’t, however, print the Relationships.
If I went to the trouble of creating a spreadsheet of all these items, I could then search for a particular name to see all instances where it occurred. More on this later.

I must admit that I rarely do this. For one thing it’s time-consuming to create the documentation. I usually just bip madly between the layout, Field Definitions, Define Relationships, and the Script I might be writing until I somehow figure it out.

Another problem is the old adage that you can’t write the documentation until the development is finished; because as soon as you change anything the text is no longer accurate.

Despite all that there are some times when having a comprehensive external record is the only solution to clearing up a complicated mess, or for finding the needle in the haystack.
There are programs out there which are designed to help, most even create a separate database detailing the structure of your files. The easy-to-use ones cost money, but there are free methods available.

Commercial Programs
(I haven’t used most of these programs, but I’ve heard good things about them. Check out the websites for extensive screenshots and demos.)

The first program I’ll mention is Analyser by WavesInMotion http://www.wmotion.com It has a very clear tabbed interface, with all the various parts listed at the top of the main window: Files, Fields, Relationships, Scripts, Passwords, etc.. You can get a great deal of info about items, especially Fields; its definition, other fields that refer to it, relationships that refer to it, scripts that have it in them, etc. It appears to get everything, even the steps of each script. The cost of the program is $159; it requires version 4.

They also have a smaller program, DocuScript, that just works on scripts, allowing you to perform searches for a word. It’s $59.

ClickStats by ClickWorks is similar http://www.clickware.com. It has a “versioning” feature that allows you to compare one session to another. You can run several “sessions” on the same file(s). Then you can choose any two and ask for a comparison to see a general count of what’s different. It appears to lack some detail on this, however. It doesn’t get script steps, for example, just the names.
It is similarly priced at $149, and also requires v.4.

SmartTools, at http://www.cetacean.com is a little different. It is not so much a file analysis tool as it is a souped-up template library. It can save your commonly used layouts, fields, relationships and scripts, ready to use again at the touch of a button.
You can choose from the list of fields, then click a button to have them all created in your new file, complete with definitions.
It also has a “redirect” relationships tool that fixes broken relationships, due to file name changes, etc.

It costs $99. It doesn’t require v.4, but it does require a clean version which has not been altered with Developer Dialogs to have larger windows (which mine have).
It includes a OneClick runtime engine extension, unneeded for OneClick users.
The odd thing about it however is that only a beta version is available. The final version is promised in September–of 1998! I imagine it works fine, since FileMaker’s file format hasn’t changed since v.3, but I’d make an inquiry before buying.

Free (or dirt cheap) programs I have used

The first is a free AppleScript, FileMaker DB Lister v2.0, by Tony Williams, http://www.moreinfo.com.au/tonyw/fmp
It goes through the front database file and gets its Layout names, Fields & Definitions and Script names.

It’s main use, for my purposes, is to get the list of which fields (including ones you may have made invisible) are on each layout; there’s no easy way do this within FileMaker (other than one at a time in the Overview submenu, File Menu).
It outputs a text file in tabbed format, which can be open/pasted into a spreadsheet. It’s a good start.

Next is Print2Pict by B. Raoult (that’s all he has in the ReadMe; hard to find). ftp://ftp.amug.org//pub/amug/amug-files/publish/o-p/print2pict-3.7.1.sit.hqx
This is an amazing tool, and many of the other programs listed here use it. It’s actually a Chooser Extension. It shows up in the Page Setup dialog box just like a printer driver. It “prints” to a file from any application, and allows you to choose the format of the file: PICT, GIF, TIFF, or, in this case, text.

I use it to “print” the Field Definitions directly from FileMaker to a text file. FM does a great job on these, including all the info, especially for lookups.
Unfortunately, the output is just text. The parts are separated by spaces, not tabs; or even worse, by returns. There’s no way to paste it into a spreadsheet as is, so some time must be spent in a good text editor (BBEdit Lite is an example) to clean it up into something more useful.
Then paste them into the earlier spreadsheet, over the ones the AppleScript did; these are better.
You can also print the Script definitions in the same fashion, at least the tricky ones; then clean them up and paste into the SS. (The script name is printed after the script steps, for some inane reason).
The last item to get is the Relationship definitions. Though it’s small, it’s the hardest to get, as it can’t be printed. You need another tool.

Text Capture FKey by James Walker (author of the original Text Editor Patches, the very useful OtherMenu and others) http://www.jwwalker.com
This tool is also amazing. It allows you to do something that you intuitively know you should be able to do, but otherwise can’t.
With a key combination, much like the screen capture FKey, you can copy practically any text and put it on the clipboard–as text. This includes non-editable text that is inside dialog boxes, which is where most of FileMaker’s scripting happens.
You can set it to capture only “selected” text, using a cross-hair to make a rectangular selection, or the entire window (which gets text off the right of the screen; neither gets text below the screen, however; you have to do it again, after pasting).

You can either put the FKey in the system (not so easy), use something like ATM or Master Juggler to load it, or use the free application FKeyDragger, which makes adding FKeys as simple as drag-and-drop. http://hyperarchive.lcs.mit.edu/HyperArchive/Archive/cfg/fkey-dragger-10.hqx
(It comes with some cool ones, including Desktop Notes, which lets you type or copy/paste a quick note from within any application and then save it as a text clipping.)
Caveat: I don’t know how all of this works with the latest OS versions. I’ve also read that the next Mac OS will include this FKey functionality. It’s about time.

What we need Text Capture FKey for in this case is to select and copy the Define Relationships dialog. You may not be able to get them all at one go if you have a lot. Paste the ones you get into the spreadsheet, then go back and get the rest.
You’ll still have to add tabs, one after the name of the relationship, and one after the name of the fields. This allows you to adjust the columns and see it all aligned properly.

Search the Spreadsheet
The whole point of all this is that now you have everything in the spreadsheet. You can use the Find function to search for a particular field name. Then search again, until you see every place that it occurs or is referenced.
If you have commented your scripts thoroughly (read last month’s article), then you can also search for external script names from other files.

Troubleshooting a Problem
If this all seems like too much trouble (and it is) and you don’t want to spend the $150 for an analysis program, there’s a lesser approach which I usually use:
You can use Text Capture Fkey to just copy the relevant info into a text editor, then analyze or refer to that.

It’s often confusing when there are too many fields to look at, so just copy the ones involved straight out of the Define Fields dialog.
Rearrange them however you want to compare them.

You can copy a short script the same way, or you can use Print2Pict to print it as a text file.
If there is an external script involved, you can paste it directly after the local script that calls it, to really see where things are going. If you’ve commented it, the name will be there; otherwise label it or you’ll lose the jump between the scripts.
Print out the script now and read it over, like a story.

I’ve sometimes gone so far as to create a special layout and paste some of the field definitions, relationships, and scripts on it, just to have them handy when I come back with the same confusion later (duh).

Copying Scripts
Once you’ve got a script as a text file, it’s a lot easier to recreate in another file.
I just keep the text file open, then bip back and forth between that and the ScriptMaker dialog box (as you can’t have two ScriptMaker dialogs open at the same time).

There are also small commercial programs for copying scripts.
Both use Print2Pict to get the scripts out, then recreate them as best as possible. There are some limitations. Both are inexpensive.

One is ProMaker Utils, by Peter Baanen of Troi Automatisering (also a prolific plug-ins author), http://www.troi.com
Another is AutoScript.FM, by Gregory Charles Rivers,
http://www.gnb.com.hk/autoscript.fm/index.html

It also uses a OneClick runtime engine. He’s just come out with an updated version with “interactive” field naming and drag-and-drop script step replication for full or partial scripts.
Now that would be useful. I’ve put off using these tools primarily because without the ability to re-target script steps to new fields or extract only parts of a script, they wouldn’t be much faster than recreating the script from my text file copy.

If you’re upgrading soon, FileMaker Pro version 5 will include the ability to import scripts from another file. It also will have a few of the layout template library features; but not many. I only know this from rumors (screenshots at AppleInsider http://www.appleinsider.com). By the time this article is out, there may be a public announcement.

Entity Relationship Diagrams
Say what? Known to their few friends as E-R diagrams, these can be fairly simple drawings, with boxes for files (tables in other programs), and lines joining them (relationships). They afford a visual overview of your entire database.

If you are serious about databases, I highly recommend the book Database Design for Mere Mortals by Michael J. Hernandez. It’s not about FileMaker. But he writes about relational database design in a way that transcends all applications, in plain everyday language.
He also has some instruction on drawing these diagrams. Altogether a great book, especially for those with little knowledge of relational design (my hand’s up).

It’s all in your head
Let me just say in conclusion that even though some of these tools are useful and occasionally essential to making databases, it is your mind that creates the fields where the data resides, the connections between them, and the layouts to display the results, transforming raw data into useful information.
You have to think about them intensively while you are working, and often in the background when you’re not. The solutions to problems will often come to you when you least expect it: while taking a shower, taking a walk, in the middle of the night (not so good). But they will come.

It is also essential to learn from others. Examine example files others have made available on the Web. Read a FileMaker mailing list:

Dartmouth List: Very friendly, a good place to start; somewhat archaic List Server
Put this in the Subject line (I think?) of an email:
SUBscribe FMPRO-L
email to: LISTSERV@LISTSERV.DARTMOUTH.EDU)

Blueworld FileMaker Pro List: Developers, all levels. Just click this when online:
fmpro-on@blueworld.com
>
To search the list archives (awesome):
http://listsearch.blueworld.com/fmprotalksearch.lasso

To search TechInfo for articles go to the FMI website
http://www.filemaker.com
select Support button
Select Search TechInfo from the list on the left side of the screen

Websites with example and utility files:
jim40er FM http://www.halcyon.com/jim40er Mailbrowser, Eudora2Text, Archive files
Database-systems http://www.database-systems.com Example files
FileBits Pro http://www.elmapp.com/fmp/fmbits.html
DW Data Concepts http://members.home.net:80/dwieland/dwdctips.htm
FileMaker Power! http://www.Filemaker-power.com/info2.html
FILEMAKER PRO ADVISOR Magazine http://www.advisor.com/wHome.nsf/wPages/CFmain
>
FileMaker Pro Examples/Tutorial http://www4.ncsu.edu/unity/users/j/jpscott/www/bin/FMPSlides/index.html
FileMaker Pros: John Mark http://www.best.com/~jmo Lots of examples, Scriptology
FileVille http://www.fileville.com
HABMarketing http://www.habmarketing.com/HOME.HTM
ISO FileMaker Magazine http://www.iso-ezine.com/magazine.phtml
Jeff Hopkins http://www.amug.org/~hposoft (Tutorial files)
Steve Wilmes Consulting, Inc. http://www.swconsulting.com

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

Labelling & Troubleshooting Fields, Calculations, Relationships & Scripts

In this article I’m going to give you a few tips on how to organize your database. In the rush of creation we often neglect these simple steps to document our own work. Then later we pay the price, as we’ve forgotten exactly what all those darn things are and what they do.

The first step in keeping track of what things are is to name them, and the first things to name (other than the files themselves) are the fields.
There are, as far as I can tell, two main systems for naming fields.

Alphabetical Field Names
The first is plain old alphabetical. If you don’t have too many fields this may be all you’ll need. The key to this system is to name all fields that you want to keep together with a similar beginning to the name, adding bits to the end for further identification.

*(Self_Service being the name of a self-relationship of the Job ID)

As you can see, there are several fields beginning with “Serv”. Choosing view by Field Name in the Field Definitions will instantly bring them together.
You can adjust the order somewhat by using different punctuation. Sticky spaces between the words will also work, adding the advantage of keeping the words together in calculations. I often use that for IDs, since the extra space helps them stand out in lists. (But I’ll spare our webmaster and just use regular spaces here.)

I also use the sticky space and a short suffix to identify types of fields. For example; ” g” for global fields, ” c” for calculation, ” gc” for concatenated globals.
That way I can quickly tell them apart from a regular field with the same name.

One problem I’ve found is to keep the distinction between actual Summary fields and Total fields which are based on aggregate functions. They are not always the same; the Summary depends on the found records, while an aggregate using a self-relationship does not. So I always use the letters “Sum” for Summaries and “Total” or “TOT” for Sum() aggregates. You could do it either way, but…

It is important to be consistent. If you name some fields carefully, but just slap on the first thing that comes to mind on others, you will soon have little idea what does what. It can get pretty sticky if there are a lot of fields with similar names. If you find it getting out of hand it’s time to move up the next level of organization.

Custom Order Field Names
FileMaker remembers the field name order. So all you have to do is decide which fields go together and drag them into place. Choosing “Custom order” from the drop-down box in the Field Definitions dialog will restore them later if you add new ones.

A further refinement to this method is to name fields with a prefix to show the type of field, much like I did before with suffixes. “g Job ID”, “g Service”, etc.. If you do an alphabetical sort it will sort by “Type” of field as well.

Obsolete Fields
You can use prefixes and alphabetical sorting to get obsolete fields out of your way. Any field that you think you no longer need can be renamed with “`” (accent) in front. It will sort to the bottom.

Never delete a field if there is an Import order in a script. It will screw up the Import in a way that is very difficult to fix. Just change it to a global field and leave it. It will then be empty and unstored. If you need a new field later, just rename and redefine it. You could also use the “False Calculation” method (two paragraphs down) to tell yourself just why you “retired” the field.

Conversely “#” and “•” prefixes will sort to the top. This is also a great way to track a field that may be referenced in other fields’ definitions. Temporarily rename it, then scan the Field Definitions. It will stand out like a sore thumb in other fields’ calculations.

Custom Separators
To really use the Custom Order method you should create custom labels for each of your carefully grouped fields, to create a “table of contents” look for the Field Definitions list. It’s pretty easy. The quickest way is to just create a new global field. Use a few underline characters, then a descriptive name, eg. “_________Service”, up to about 17 characters long. Drag it to be above your Invoice fields.

[Warning: the next two techniques are geeky.]

“False” Calculations
To get even more info in, you can create a “false” calculation field. Give it the name as above, then write more in the calculation options.
_________Service, Calculation= If (1=0, “Service Rates and a bunch of stuff”, “”)
The entire above sentence will display in Field Definitions.
This same trick can be used within real calculations, to remind you what they do.

For example:
Case (1, If(Max(DONATE::Date) = Date, Donation, “”), “Date is the latest”)
In this “case” the first argument, Case(1) is always true, so the last comment is never evaluated. (Thanks to Steve Cassidy for this exercise in minimalism.)

Disabled Auto-Enter Options
Of course, the above could only be used in a calculation field. What about regular text and number fields? Well, believe it or not, someone (not me) has discovered a way. Click Options, then enter your descriptive text in the Auto-Enter Data box. Then uncheck the box. Your text will be grayed out, but will remain stored and readable. You just have to remember to look there for it.

Relationships

Now that we’ve got our fields under control, let’s move on to relationships. When I was beginning, I tried to name relationships with names that described what they did, their function in the file. But I would forget what the necessarily short name meant. And then, to make things worse, some relationships were used for several different operations, or a similarly named relationship went to a different file.

So now I name the relationships according to where they go (files) and what fields are involved, trusting that I can figure out what it might do by looking at the context of how it is used…and by reading my Comments in the scripts.

Relationship Names
The first part is the name of file, or part of it. The second part is at least one of the fields. Often relationships are between two identically named fields anyway. So a relationship between a client (Contacts file) and one of his jobs (Jobs file) would look like: from the Contacts file, “Job_Client ID”, and from the Job file, “Contacts_Client ID”.

Rather than name relationships within the same file with the file name, I prefix them with “Self”, as in “Self-Client ID” (Contacts file), or “Self-Job ID” (Jobs file); whatever the field involved is named.

One of the great advantages of naming clearly like this is so you can pick the right one when choosing a relationship in the drop-down box for a field, in either a field definition, or in a calculation dialog. If you still can’t tell what a relationship does, go ahead and choose it, then click again, drop down to the bottom of the list box, to “Define Relationships,” and let go. The relationship dialog will open, already scrolled to that one, so you can see which fields are actually involved. If it’s what you wanted, just close the dialog. (An excellent piece of software engineering.)

If you still can’t tell, it may require a trip to the Field Definitions and a study of the field(s) involved. Try to remember where you are before going that route, as you’ll need to get back.

Relationship Order
When you’re in the relationship dialog box, you may as well tidy up that list of relationships. If you group them according to which file they go to (easy to do now), then group them roughly according to what they do and relative importance, then it’s a lot easier to find them in that list. Just drag them into place.
Remember to click on the one you wanted if you were in the middle of doing something, so it remains selected in the relationship drop-down box.

Scripts

Now we get into the really fun stuff. Carefully naming scripts and documenting script steps is the only way to preserve your sanity. The problem is not too awful at first, but wait a little while, then come back and try to modify a complex script. You won’t remember exactly what’s happening, much less the unforeseen effects on other scripts–and I don’t think it’s only me that has this problem.

Fortunately FileMaker has a few ways to help. The first is to use a system to name your scripts. The names can be pretty long, and there aren’t really any restrictions on characters you can use, so there’s less need to be cryptic.

Script Groups
First I’ll tell you how to “group” scripts, so you can find them on the list in the ScriptMaker dialog, and how to create a label with a script name. The first simple scripts that deal with such universal things as Sorting and navigation to Layouts I generally put at the top of the list. Scripts and buttons that are only called from one particular Layout, but do different things, will be grouped together. If several scripts operate within one particular area or function of the database, then I group them together.

Above each of these groups is an easy-to-see label. The label is basically just an empty script; just delete all the steps. Name the scripts with a few dashes or underlines then the group name.
“————- Navigation” would be scripts that just deal with moving around the file.
“————–> External”, scripts that go to, or call scripts in other files.
“————-< External”, scripts that get called from other files.
“————– Printing”, “———– Help Messages”, etc..

The dashes set the names off to the right of the regular script names, so they are more visible. Drag the label above the groups. Duplicate one to make another. Often a single dash is used as the name of an empty script to create a separation line in the Scripts Menu list visible to the user. Just click the “Show in Menu” checkbox for those. Don’t check it for these other ones.

Script Names
As you may have noticed above, I use arrows to show that there is an external script within a script, either going out or coming in. These can be tough to troubleshoot, so name them all.

To make them easy to see, I use a long arrow, “–>”, or “<–” for external scripts (sometimes “<–>” for one that goes then returns). I also include at least one of the file names of the external file(s). Eg., –>JobEntry, Job Choice Lay
This tells me that this script just goes to the JobEntry file, to the Job Choice layout.

If a script calls another script within its own file, I just use a single arrow after it. I try to put the script it calls directly under it, with an arrow in front.

New Record>
>Empty Fields Check

This tells me that the New Record script runs a custom scripted check for empty fields before allowing me to create a new record. Another way to write the first, if several scattered scripts called the same subscript (very likely), would be, New Record >w/Empty Check. Another script might be, Main Menu >w/Empty Check.

This makes your life much easier if you ever modify a script. You can see that there will be effects on other scripts. It’s especially useful when you are checking scripts between files, when the name is all that you can see easily from the other file.

Comment Step
There is a built-in mechanism to document within scripts. It is the Comment [""] step, down near the bottom of the Script Steps list. Use it liberally; you’ll be glad later. It can be put immediately after steps which are otherwise difficult to decipher, complex Sorts, Finds, and Perform Script [External].

External Scripts
This last is especially important to comment after. The step only shows the name of the file, not the name of the external script. You can see which script it is by clicking on the file name box and letting go, triggering the dialog box; but this gets old in a hurry when you’re trying to track down a problem.
Put the external script’s name in the Comment, and you’ll be able to see in a glance what is going on. Be sure to keep it updated if things change.

By the way, if the script you see in the dialog doesn’t have its “<–” arrow, then you have either neglected to name it properly, or you’re calling the wrong script. This is especially important in simple scripts that have slight variations but similar names. Sometimes the externally called one has only a single difference (such as a Halt Script step); unless it’s labelled you can’t pick it easily from the other file.

Troubleshooting Scripts
There are a couple more steps you can add to help find out where a script is screwing up (also must happen to other people). The first one I turn to is the Pause/Resume Script [] step. Put one of these before each critical juncture in a complex script. If the problem is in fields not being set properly, you should have a demo layout that has all of the fields involved. Add a step to go to this layout if needed. Then you can walk through the script, watching what happens after each big step, hitting the Enter key to continue. Remember to take the Pauses out when you’re done.

Another quick way to “hear” what’s going on is to add a Beep step. It’s especially useful to see “if” something is happening at all. Add two beeps or three beeps to check other possible branches of a conditional script.

If you think that a step (or series of steps) are the problem, and you would like to run the script without them (assuming it could), there is a way to “comment them out.”

Put a false “If” step before them, and an “End If” after.
If [1=0]
Script step
End If
The rest of the script to run.

Obsolete Scripts
A combination of techniques can help identify unused scripts before taking the drastic step of deleting them. In order of intensity:
1. Name them with a prefix like “###”. You’ll be able to spot them more easily if they are called by other scripts. Check for it in suspected calling External scripts, too.
2. Put a Beep in. If you have no other beeps, this will alert you that a marked script is still being used.
3. Display a custom message, “Script name is marked for deletion”.
4. Create a global field, text, “ScriptName g”, and add the step:
Set Field ["ScriptName g", Status(CurrentScriptName)]
This will capture the name of the script so you can see it afterwards.

If all fails and you’re still not sure whether it’s being used, leave it in. The possible damage from removing it is worse than the small disk space being wasted by leaving it in. Speaking of wasting space, I’ll see you next month.

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

New Records via a Constant and globals

There are a few ways to create a new record and get the proper ID into it. The following is a fairly universal scripted method, using a “Constant” relationship and a global field to temporarily hold the ID value. It doesn’t require that the relationship be defined to “allow creation of related records.”

It also doesn’t require any other relationship other than the Constant one. So it can be used when there’s no other obvious way to pass data from one file to a new record in another file. Constant is a calculation field, equal to 1, hence it is always one in every record. A relationship from a Constant field (or any field equal to 1) in one file to a Constant (or 1) field in another relates to all records in the other file.

A global field can only hold one value for all records in a file, and it is accessible from all records in the file. (Strangely enough, it can hold a value even if there are no records; it is independent. It can also hold many items, if they are separated by carriage returns. But this is still only one value.)

Two files, Parent and Child.
Parent fields:
ID, text, auto-enter serial ID, increment by 1
IDg, global (text)
Constant, calculation, =1 (number)
Child fields:
ID, text (not auto-enter)
Constant, calculation, =1 (number)

Relationship: Parent –>Child; ID relationship, ID =::ID
Relationship: Parent<– Child; Constant relationship, Constant =::Constant

There are two scripts, created separately in the two files. The first one in the Parent file calls the second one in the Child file as an external script, so that they run as one script.

I added Comment steps, like FileMaker shows them; but you can delete them.
New Record Script
Set Field ["IDg","ID"]
Comment ["Puts the current ID into a local global field."]
Exit Record/Request
Perform Script [External, "Child"]
Comment ["New Record2 <--Parent Script"]
New Record2 <–Parent Script
New Record/Request
Exit Record/Request
Comment ["Allows Constant =1 to evaluate"]
Set Field ["ID","Constant::IDg"]
Comment ["Reaches back and gets the ID from Idg in Parent. It can use the Constant relationship because there is one and only one IDg value in either file, available to all records."]
Exit Record/Request
Comment ["Allows the ID relationship to evaluate. Other script steps using the ID relationship will now work."]

Check for Records First (slight detour)
If you don’t necessarily want a new record every time; if you just want to go to the records if there are any, only creating a new one if there aren’t, then add these steps first, in the New Records script:

Exit Record/Request
If [ not IsValid (ID relationship::ID)]*
Perform Script ["New Record"]
Else
Go To Related Record [Show, "ID Relationship"]
Perform Script [External, "Child"]
Comment ["List View <--Parent"]
End If

Add this short script in the Child file, to be called from the above script.
List View <–Parent
Go To Layout ["List View"]
Halt Script
Comment ["Halt is needed to stop all scripts; otherwise you'd be returned to the Parent file by just the End If step."]

*The ID field could be any field that you’re sure has data. I often use the Constant field, which would look like, ID relationship::Constant. But I didn’t want to confuse you using the name twice.

If you wanted to do something else in Child, you could set the ID into a global field, then use that in a different relationship to go to other “filtered” records.

Setting Fields between Files without an ID Relationship
Even if there is no ID relationship possible between two files, you can use the Constant relationship and global fields to set any field between two files. Just create a “mirror” global field for any field that you want to set into the other file, much like in the ID example above.

You can create this field in either file. This is because, as we said before, there is one and only one value in a global field for one file, and the Constant relationship can access it from any record in the other file, in either file. So you can set it in either file and retrieve it from either file.

We used the first method, setting a local global, in the New Record script above. It is the safest way to create a new record if there is a possibility of no records in the other file.
But to set other fields, there is another similar method you can use:
Create another Constant relationship, this time from Parent –>Child;
Constant =::Constant

Create a text field, Tools, in Parent.
Create a text field, Tools, in Child.
Create a global field, Tools g, (text) in Child.

Set Fields Script
Set Field ["Constant::Tools g","Tools"]
Comment ["Sets the tool into the global in Child"]
Perform Script [External, "Child"]
Comment ["Set Tool Script"]
Set Tool Script
Exit Record/Request
Set Field ["Tool","Tool g"]

As you can see, it’s fairly simple once the fields are there. And there can be many fields in each script. They all just need their mirror global field in the Child file. You can set them one after the other, all with the same script.

One advantage of this method is that all the extra global fields can go in the Child file, where they make more sense, and don’t clog up the Parent file with fields. Remember that a global field takes up very little space; it is not stored. To make it really clean, you can clear out all the globals after running the script, with a series of:
Set Field ["Tool g",""]
(for each global field).

Just make sure that you’ve set the global into a regular field first. (I forgot once that I’d moved the Set Field ["Field","Field g"] step later in a script. I couldn’t figure out why Tools was always empty; I’d emptied Tool g already!) It’s not really necessary, as Set Field replaces whatever was in the field every time anyway.

Other uses
The global via Constant method is also useful when you are setting things temporarily into fields during scripts, or performing a series of calculations or validations before setting a value into the other file.

Other methods
New related records can be created directly if you check “allow creation of related records” in the relationship definition box. All that is needed is to populate a related field (not the ID key field) with data. This can be done either manually, by typing into a portal based on the relationship, or by populating a related field via a script.

Lookups
Another way to set the fields, after using the first Global method to set the ID, or importing just the ID field, would be to simply define the related fields as Lookups, in the Options dialog, using the ID relationship.

If, however, you have fields that depend on there not being a Relookup on the relationship (such as First Mark fields, which would lose their mark), then you wouldn’t want to do that. Remember that a Relookup refreshes all fields that use that ID field in their lookup definition (including ones you’ve forgotten about and those pointing to other files the ID has a relationship to). Both of these are also good methods; they each have their uses, limits, and difficulties (which are more than I can go into right now, even if I knew them all, which I don’t).

The great thing about using the global method is that you can read the steps in the script and see what is happening and when. I often use it first, to get things working. Then I consider whether to use a lookup for some of it instead. It’s easy enough to change for just a few fields. For a lot of fields, however, such as when moving practically a whole record, I would go with lookups whenever possible; importing only an ID field, then Relooking up if necessary. This method is slow, but it’s a safe way to go.

I will try to go into all the ins & outs of these other methods in a later article. Next month, though, we’re taking an extensive look at documenting your database.

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

Globals & Filtered Portals
Example file: Eats

Filtered Portals
The basic problem with Portals is how to get the records you want to show in the portal. The difficulty is that we tend to think of the ID, or key, of the records we want to see rather than the “encompassing” ID of the portal, which will be quite different. In other words, we tend to think of what identifies each record rather than what identifies the group of records we want to see. The key for the portal will have to be something that is the same for every record we want in the portal, but not shared by any records not in the portal.

We may even have to define a calculation field and/or set a field, often a global, to get a custom key for the portal, in either the current file, the portal’s records file, or both.* This is a “filtered” portal.
* The global would only be set in the current file; you usually can’t have a global, or any unstored field, as the target of a relationship. Regular fields can be set in either file; though modifying a key in the records of the second file would require a Replace operation, and is beyond the scope of this article.

Remember that portals are based on relationships; and that relationships are pretty much independent of the Found set of records in any file. They are only dependent on what the key is for the relationship. This key can either be a regular field in the current record or a global field, which is, by definition, the same for, and available to, all records in its file. The portal contains the records that match the key on the other side of the relationship, either in another file, or, in the case of a self-relationship, within the same file, no matter what records are currently found.

The rest of the article will attempt to illustrate filtered portals using examples in last month’s sample database, Eats, beginning with the main file Food, on the layout Food Choice.

Groups Portal
The first portal shows a list of Food Groups. They are the records from another hidden file, Groups, which has one record for each group.
The portal is not based on the relationship Group ID::Group ID; it is based on the Constant relationship (basically 1=1 for every record). This relationship relates any record in the Food file to all of the records in the Group file. Hence it is the “encompassing” relationship, allowing all the food groups to show. It would be an “unfiltered” portal (if there was such a name).

Foods Portal
Clicking on a group name in the portal sets a global field in the Food file (using the portal Constant relationship), the Group ID g field.

This global field has a relationship to a Group ID field that has been manually entered in every Food item just after record creation.*
So setting the global, then exiting the record, refreshes the list in the second portal, which shows the food items that are in that food group.

In this case the Group ID is the encompassing ID, and the food items are the records showing within it.

I’ve also created a relationship between the global Group ID g field and the Group ID field in Groups. This allows me to show the Group name in a field by itself just above the Food portal, so you get immediate feedback on what choice you made in the first portal. The thing to remember here is that by using globals to make the choices, you are able to change the view of the records, but as yet you haven’t either made any actual changes to any of the records, or even moved away from the current record. That is what makes globals and portals a good match.

Food Self-relationship
*There is a wrinkle. Foods is actually a “join file.” I’ve allowed multiple entries of each food, one entry for each occurance in a “meal,” e.g., butter is entered 3 times, Breakfast, Lunch, and Dinner. I’ve had to create a self-relationship and use it to “lookup” a mark (1) in the field FoodIDMark for the first entry of each food (see earlier articles).

Filtered Group ID
A calculation, If (FoodIDMark, Group ID, “”), puts the Group ID in only those first ones. The portal uses that field for the Group ID.
It is needed so there aren’t duplicate food items showing in the portal. Otherwise you would just use the Group ID.

You could create another file to hold the multiple instances of each food. But I found that all the fields I wanted for a food would work with this self-relationship. The number fields, Amount (on hand), Threshold (below which it was time to buy), and the calculation field Buy; If (Amount<=Threshold, 1, “”), are the same for all instances of each food. If I had 1 cube of butter, I’d have 1 no matter whether it was breakfast or dinner. You can modify the number field Amount, but you can’t modify Buy; it is a calculation.

One of the advantages of portals, versus other value list choice methods, is that not only can you show several fields at once (vs. 2 at most for defined Value Lists), but you can combine navigation (clicking on the name), data input and visual feedback all in the same portal. The disadvantage is that they take up a lot of space on the layout.

Constant=::Buy Portal
The last portal is an example of a Constant=::Mark relationship. The Constant=1 field is related to a Mark or Flag field (any field which contains 1 only for certain records, entered either manually, or by calculation or script). In this case it’s the Buy field. It shows, independently of the other portals, food items that are below their threshold–a shopping list.

Refresh Problem
You’ll notice a “Refresh” button below the portal. There is often a problem with calculations and portals. A recalculation doesn’t always show until somehow the portal is forced to refresh. There are clever ways to do this when defining the calculation, but in this case none of them worked.
If you change the Amount of a Food in the Food portal so that it falls below the threshold, the Buy checkbox will be checked. But it won’t show up in the Buy portal until it refreshes.

A simple way to force this is a script with two steps:
Go to Preview Mode [] <–no pause
Go to Browse Mode
It just takes a split second and always refreshes the portal, so the food shows up, or goes away (if the Amount is over). But it does require a button if you’re not already running a script.

Concatenated Keys with Portals
So far we’ve been using a single ID or value field as the key for the portal relationship. It is possible to filter further, using a concatenated key of 2 ID fields put together, to show only those records where both ID fields are present.

Meals File
This is what I’ve done in the Meals file. The first portal is just another Constant relationship, showing all the meals. A choice of meal sets the global Meal ID g. This is related to the Meal ID in Foods, and the fields in the portal are the Group ID and Group name in Foods. The Group name is actually a calculation, =::Group name in Groups, since the name isn’t in the Foods file.

The 3rd portal uses a concatenated global field, Meal-Group IDg
,
=Meal IDg & ” ” & Group IDg

It is related to a field in Foods, Meal\If\MealGrpMark
, which has a few steps in its creation (as you might guess from its name):

The first step is simple, but has a twist: Meal-Group ID
= If(IsEmpty(Meal ID) or IsEmpty(Group ID), “”, Meal ID & ” ” & Group ID)

The reason for the “IsEmpty” is to correctly “lookup” a first entry mark (below). Otherwise the lookup occurs as soon as you exit the first field, Meal ID in this case; it doesn’t wait for you to enter the Group ID.

The second step is to mark the first entry of every unique value in Meal-Group ID. Meal\GroupID\Mark, number, is a lookup. Using a self-relationship on Meal-Group ID it looks up a 1 for a unique entry. Here are some examples of unique entries; breakfast dairy, breakfast grain, lunch dairy, etc.

The last step puts the Meal ID in a field for those unique entries. Meal\If\MealGrpMark= If (MealGroupIDMark, Meal ID, “”) The reason for all this is simple actually. The Meal is the encompassing ID. You choose the Meal in the Meal portal. But for the correct groups to show in the next portal, you need to have the Meal ID in only those records in Foods that are the first unique entry for that Meal ID and Group ID. If the Group does not have any entries for that Meal, you don’t want it in the portal. If the Group has more than one entry for that Meal, you only want one of them.

There is probably a way to do this by using another “join” file for Meals & Groups. But I tend to keep things in the fewest number of files. In this case I started with the list view in Foods, keeping all the data entry there, even for the Meal and Group files. You can experiment with it.

Global Concatenated Key
When you choose from the first portal, you set the Meal g field. That filters the choices in the Groups portal. When you choose a Group, it sets the Group g field. This completes the Mealg-Groupg field. It matches the encompassing field for the Foods portal, which in the Foods file is just the Meal-Group ID.

[Correction: In the example file it's Meal\Grp\If\FoodMark, =
If(FoodIDMark, Meal\Group ID, "")
This works, but is overkill. A particular food is never entered twice for the same meal. Duh! I forgot. You can edit the relationship to point to Meal-Group ID in Foods instead, then delete the above field. It works just the same and saves disk space.]

Clear the Portal, Matey
The script for the first Meals portal has a step at the end to clear the Group IDg field; Set Field ["Group IDg",""]
This empties the Food portal on the right (since nothing matches just the Meal ID g).

Use this technique for the first portal whenever you have three or more linked choice portals, otherwise you’ll have values still showing in the third one from the last time it was used; but they won’t be correct, and it won’t be obvious that they aren’t.

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

Self-Relationships with ID Fields
Example file: Eats

This month I’m going to go on a bit more about self-relationships. They are one of the most useful tools for identifying records within a file. You can use these self-relationships for many things that would normally require a Find operation (which can be slow, especially with many records), do many things that would require a summary report (also slow, and requiring a change to Preview mode), and easily do things that would be very difficult otherwise.

If you haven’t done anything much with relationships, these are a good way to start. The “self” part of the name just means that both sides of the relationship are fields within the same file.

Self-ID Relationship
The most basic is a self-relationship relating a field to itself. Just click the same one on each side in the dialog box. I like to name them “Self” and then the name of the field (duh). Usually it’s an ID field, so one for a Job ID would be “Self-Job ID,” and look like: Job ID =::Job ID. That keeps it separate from relationships to other files that use the Job ID; they would have the other file name first, as in “Materials Job ID.”

There are several uses for self-relationships. There are two basic possibilities for an ID field in a file. The first is that it is unique for each record in the file. The other is that it isn’t unique, that there are of several instances of it in the file.

In the first case, you may not even use a self-relationship on the ID itself. There’s not much point, since there’s only one; it would usually be an auto-entered serial number and can be validated by the built-in “unique” checkbox.

Duplicates (again)
It would be useful in that case to have a self-relationship between another field that should be unique, but which wouldn’t be if there was a duplicate record. This is usually a concatenated field, put together from pieces of several others. An example:
Left (FirstName, 3) & Left (LastName, 3) & Left (Address, 3)
A self-relationship on that field could find duplicates. There are a few ways to set that up; I’ve written about in my article on Duplicates, so I won’t go into it now.

What I haven’t written about before (at least not very clearly) is how to use self-relationships in files where there are several instances of an ID field.

Pseudo Design Theory
In this case, some other ID would be the “primary key” defining each record. The ID used in the self-relationship would often be a “foreign key,” also related to another file where it was the primary key.
If you look at it structurally, you’ll often see that the self-relationship on the ID in the file where there are several mirrors the structure of the file where the ID is unique and only in one record.

Go To All Instances
There are many uses for a self-relationship on the ID. Since there are several records containing it, scattered all over the file, it would be nice to be able to quickly bring all instances of it together in a list view. A single script step using the self-relationship could do that:
Go To Related Record [Show, "Self-ID relationship"]

Calculations
The self-relationship can also be used in calculations. It is available for use with the Aggregate functions, such as Count (Self-relationship::field). Sum(Self-relationship::number field) will give you a total for all records with that ID. These values will be reliable and independent of the Found records, so they can safely be referenced from other related files (which Summary fields, dependent on the found set, cannot).

The only disadvantage is that they are unstored, and any further calculations based on them will become unstored upon closing the Define Fields dialog. Not only that but they will remain so forever, unless you either remove the related field from the calculation or change it so that it can be indexed; and then also go into the Storage Options of each calculation and manually uncheck the “Do not store results” box!

Not being stored can be a feature in some situations. A field with the count of the relationship can show you how many records there are of that ID without even having to perform a find, and it will update if you add or delete records.

Global Self-relationships
A similar kind of relationship that is extremely useful is what I’ll call a global self-relationship. In this case the two sides of the relationship are not quite the same field, but they are pretty close.
Create a global field, of the same type as your ID. Name it the same, but with a “g” as a prefix or suffix. Choose that as the left side of the relationship, and the regular ID field as the right side.

What this relationship does is to free you from having to use the ID field itself in operations. This becomes essential when you are not actually on the ID’s record. Two common cases of this are when you change the found set within scripts, and when you choose records in a portal.

In both cases, all you have to do is to get the real ID value into the global ID field while it is the current record. Then at any later time use these two steps:
Exit Record/Request
Go To Related Record [Show, "Self-Global ID relationship"]
[The Exit Record/Request isn't always needed, if you've exited the record after setting the global. But you'll get unreliable results if you haven't.]

Global Self-relationships and Portals
Portals are kind of an illusion, in that the records can appear in it, but the fields are related fields, based on whatever relationship the portal is based on.
So you see what looks like your regular ID field (or a text name associated with it) in there, but it’s difficult to go to the those records with a script using just the “Go To Related Record” step. The reason is that the ID field in the portal is not quite the ID field; it’s a related field, based on the same relationship the portal is based on.

For example, if the portal is based on a relationship between a Constant=1 field and a First Entry Mark field (both being number 1, in an earlier article), showing one entry for each ID, the ID in the portal would actually be “Constant::ID.” Because you don’t have a relationship between this field and your ID field you can’t go there directly.

But you can use the global ID field as a go-between. Just set the Constant::ID field into the global first.
Set Field ["IDg","Constant::ID"
]
Exit Record/Request
Go To Related Record ["Self-IDg relationship"]
Go To Layout ["List View"]

Warning
If you set the ID field rather than the global ID field, so you can just use the Self-ID relationship, you will be changing the current record’s ID field to your choice. Since you may not even see the current record on the portal layout, this could be disastrous. (Of course, if you are using it to set the ID for a new record, that may be exactly what you want to do.)

Logical Calculations on the Self-Relationship
You can use calculations on the self-relationship itself to check certain conditions. The most useful is to see if it is valid; using (guess), IsValid (self-relationship::ID). The field in the last part can be any field that you are sure has data. I often use my trusty Constant=1 field.

You can use the same check with Self-Global relationship.
This is great to use before the Go To Related Record [Show, "Self-Global relationship"] step. One of the great problems with Go To Related Record [""] is that it will go even if there are no matching records, leaving the user with no records, often on a different layout (or even file), with no error message or clue as to what went wrong. It’s even worse than the built-in Find when there’s no records.

But you can use the following simple steps:
Set Field ["IDg","Whatever relationship::ID"]
Exit Record/Request <–Don’t forget this baby
If [not IsValid, "Self-IDg::ID"]
Exit Script <–Use Halt Script if there are Subscripts later
End If
Go To Related Record [Show, "Self-IDg relationship"]
Go To Layout ["List View"]

If the script is attached to a button, when the user clicks it and there are no matches nothing will happen. You can put a message if you want, but I like the simple zilch.

It is very useful if they are looking at a portal that is based on another file, since there may or may not be records for that ID in this file.

The If [IsValid, "Self-ID::ID"] or, alternatively, If [IsValid, "Self-IDg::ID"] check can be used within scripts, whenever you need to check to see if there are records. It is completely independent of the Found set; but it does depend on being on the record with the correct ID or getting the ID into the global ID field (and using the Exit Record/Request step).

Counting a Global Self-Relationship
You can count the global self-relationship just like you can count a self-relationship, using the aggregate function;
Count (Self-IDg::ID)
This can be used during scripts if you need to make a decision based on how many records there are for any ID (which would first be set into the IDg field).

Self-Relationships vs. Finds
In some cases you could perform the same operations, isolate the same records, by doing a Find operation. However, my experience is that Finds become slower when the number of records become very large, whereas operations using relationships seem to increase much less slowly. It is always good to consider just how big the database might become over time. It may be worth going to a little extra trouble to create a self-relationship for an operation that will performed all the time on a large database.

In many cases, especially during complex scripts, self-relationships are invaluable, because of the extra control they give you.

So, next time you get in a tight spot trying to bring things together (or keep them apart), consider whether one of these small relationships could help get a handle on that pesky data.

My Mac Turns 50
Happy Anniversary to us all!

Back in the early days of my Internet adventure (about three years ago), it wasn’t so easy to find much material on the web. It was even more unstable than it is now (if you can believe that), and at 2400 baud it was like watching an old movie in slow motion.

So I was very happy to find a little magazine devoted to Macs. I could read about the little shareware programs that were (and still are) so essential to make your Mac easier and more fun to use. I spent a lot of time trying them out and learning quite a bit about the computer in the process.

I remember sending an email to Tim, probably pointing out a little program that I thought he should review. I was quite surprised at his response, which was basically, “You write pretty well, why don’t you write a review?”

I began with URL Manager, which I still use daily. I then followed that for several months with all the little programs I loved, BBEdit Lite, Eudora Light, Snap-To.

That was over two years ago, and I haven’t stopped.

A little while later I fell for FileMaker Pro, and after a review and an introductory column, I was off on my tutorial series, FileMaker 101.

It has been a labor of love (though it isn’t always an easy application to love). I write about what I’ve learned by hard effort (trial and lots of error), as well as the helpful tips I’ve picked up in many places on the web, especially the FileMaker mailing lists.

I write not so much to create a textbook or manual but to share the little tricks and techniques that allow you to go beyond step one. I try to stay focused and be clear, but I also allow myself to write about what interests me, and in my own style, rather than formal “technical writing.” It is an informal column, though its contents are pretty geeky.

I want to thank Tim, Russ, Adam, and all the other writers who spend so much time making this all happen. Their dedication and openness makes it possible. We all do this simply because we want to do it, and we want to share it with you, our readers.

I know that if I write sincerely I will be supported. And that if I manage to pull together the various bits of knowledge into a coherent article it will help someone, and save them some time and frustration, and maybe occasionally make them jump up and say, “Ah ha! That’s how you do it.” (I do that anyway.) It also helps me; by explaining it to others I’m forced to get it clear for myself. I even remember most of it : )

The last thing I want to say is that you who are reading this are part of the My Mac team. Anything you have to say on the subject is important. So write to the authors with your praise, criticism, or questions. In the future we hope to be able to include you even more directly. We are a community, we Mac users.* We’ve seen some low times and some high times. But we’ve always helped each other.

*FileMaker users are another community, reaching across platform lines; I’m sure many of my readers use PCs. I look forward to the day when all documents and applications are fully interchangeable. Computers have become too important to the world to be limited by incompatibilities.

See you next month with more on relationships and some fun with portals.

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

Marks, If’s, and Flags
Example file: Phone2 (Part of Phone.fp3)

Value Lists Using Related Files
Before getting started I wanted to fill in an omission from last month’s column. I left out an obvious point about using related fields for value lists.
I’d talked about creating a calculation field in the local file to mirror a field in a related file that you wanted to use as part of a Value List, as the second field, using the “Also display values from” checkbox. You’d use this when you have an ID field and you want to show names to identify the ID numbers.
Another way to do this, without having to create the field, is to just use the ID field in the other file for the Value List, choosing the file with “Specify File,” choosing the ID field, and also showing the Name field.

The difference between these methods is that the first way will show you only ID#s which you’ve entered in your local file. This is good if you’re using it in Finds, etc. The second method will show all the ID#s in the other file. This is better if you’re choosing IDs for new records, as you’ll often want an ID# which doesn’t yet exist in the local file.

Marks, If’s, and Flags
This month I’m going to write about a few little fields. They normally have only a “1″ or an ID number in them, but they give FileMaker much of its power and flexibility.

Marks and Flags are fields, usually type number, that generally have either a 1 or nothing in them. They are very similar, but I’ll treat them separately because they are used differently. I don’t think there are rigid definitions, but this is how I think of them and use them.

A Mark field is often set during Field Definitions, as the result of a calculation or lookup, and seldom changes. A Flag can also be set via a calculation, but is usually more dynamic, changed back and forth from “off” to “on” as conditions change. It can also be set via a script.

They can both be indexed or non-indexable. I’ll deal with the indexed first. If they are indexed calculations or operations using them will be many times faster. You can Find them quickly, even in large files, as it’s pretty easy for FileMaker to search for either 1 or nothing. They can also be used in further indexed calculations, such as Counts. Only indexed fields can be the target of relationships, and can be used to create other fields that are the target of relationships.

A Mark can be the result of either a calculation or a lookup. The first is very flexible. Using the simple calculation; If (something, 1, “”) you get your Mark.
The calculation can be practically anything, based on the contents of other fields, so there’s often a way to mark records according to your needs.

Mark First Entry (again)
Another way to get a Mark is to use a lookup. In this way you can get a Mark auto-entered into a record when it is first created, when perhaps the conditions are only true at that moment.

An example of this which I use all the time is to lookup a Mark for the first entry, and only the first entry, of each unique ID#. For example, if I have a file that is storing daily hourly entries for work–for all different jobs–I need a way to filter those entries for only the first entry of each job, so I can easily see which jobs are in there. It would have to be fast and reliable, as only a relational match would be. A Find would be too slow and dependent on the found set. The way I do that is to lookup a Mark, 1, for the first entry, and nothing for subsequent entries. I can then use it in a relationship (later).

I’ve already written about how to do this, but it’s important, so I’ll cover it again quickly. First you’d need a Self-Relationship on the ID field, Job ID in this case.
You’d also need a strange global number field, Null.g. It never has anything in it. The lookup (Options, Auto-Enter, Lookup) for the JobMark field is based on the self-relationship. It is set to lookup the Null.g field if there is a match. If there isn’t a match, it is set to enter data, 1. Be sure to uncheck, “Don’t copy contents if empty,” as that’s exactly what we want it do.

The way this works is this: there isn’t a self-relationship when the first record for that ID# is created. FileMaker must exit the record before establishing a self-relationship, whereas the auto-enter lookup occurs immediately upon creation. But every record after that is. So a 1 is entered the first time, but for all subsequent entries of that ID the empty Null field is looked up.

It works on imports too, as long as the auto-enter checkbox is left on. It will be lost though if you either delete that first record, or perform a Relookup based on the ID field. It works fine though in files whose purpose is archiving, where individual records are never deleted. The ID# must be protected from editing and Relookups, but these are not often used. You can provide proper data import scripts so they aren’t needed, or run a Replace to fix them afterwards.

Marks and Relationships
Once you have a 1 in your Mark field, there are a number of things you can do with it. One of the easiest is to make a relationship from a Constant field (calculation, =1) to your Mark field.

This relationship will give you instant access to those records:
GoToRelatedRecords [Show, "Constant-MarkField Relationship"]

You can show those records in a portal, using that same Constant-Mark relationship. This is especially useful for files like my example where you have marked the first of several different IDs. The portal will show one (the first) of each unique ID#’s records. Put the name field in the portal and you’ve got the basics for a navigational system. Since there should be a Constant field in all of your files, you can relate back to a Mark field in this file to set up the same portal in any of the files, for a more universal tool, to choose which job, or whatever the main choice is.

I use a simple checkbox mark, “Active,” in one of my main files (auto-entering a 1 on record creation). Creating constant relationships back to it allows me to display only active jobs in a portal in any of the other files. When I’m done with the job I click the Active checkbox, which I also display in the portals. This turns it off in the main file, so the job no longer appears.

So far we’ve been able to relate to the first of each ID group within a file, but the next step takes you one level further.

If-ID Fields
I call these “If-ID” fields. They are defined by an If (or Case) calculation using either a Mark field (any Mark field, not just the first entry) or just the calculation itself. The concept is pretty simple. When the conditions are met, the ID# exists, if not, it doesn’t.
If (Mark = 1, ID, “”)
To do it without the Mark field:
If (Calculation, ID, “”)

So there will only be the ID in the records which have the Mark in the one case, and when the calculation is true in the second case.

You can use this record rather than the ID field itself as the target field in certain relationships. A relationship to this field will match the regular ID field in the file (or any file with the same ID field) only once for each ID, on the record that has the Mark.

If you are basing it on a FirstMark, you can use this relationship in a lookup to bring in data from another file only once for each ID. This can save a lot of disk space, especially when dealing with long text name fields or summarized totals, data that would be the same in every record for that ID. There’s no need to waste space having it in every record. It’s kind of a special case, probably most suited for archive-type files, but useful if you need it.

Because the data is in the first record of any ID’s set of records, it will be the value found by using a self-relationship on the ID (which goes to the first record in order of creation). So you can reliably get or display it using the self-relationship (more on this next month).

By using Marks and calculations using any number of conditions and fields, you can construct an If-ID field for practically anything. It is independent of the found set, so relationships, portals, and calculations (unstored) based on it will also be independent, as well as usable across file boundaries (which found sets are not).

Flag Fields

Flag fields are like Mark fields, but are generally used to “toggle” between a value (usually 1) and no value. The simplest way to set a flag is to a create a checkbox to click on the layout. Create the checkbox by making a checkbox value list, with only one value, 1. Format the field on the layout as a number (via Field Format menu); click “Boolean,” enter 1 for yes values, clear the no value. A click on the checkbox will either turn it on or off.

They can also be set during scripts.

Set Field ["Flag", "1"]
This sets the Flag in the current record at that moment. It can be used any time during a script, to either temporarily or permanently flag that record.
You can have different Flags for different reasons, using different calculations in the Set Field. It’s a great way to mark records that have taken part in an operation, such as an export, so that they can be ignored the next time, either by being left out of the found set during a Find, or checking for the Flag with a looping script and omitting each during the operation’s script.

They can be used to check records during a Replace script step on the entire found set, by including a check for the Flag in the calculation, as in:
Replace [If (Flag <> 1, something, "")]

You can set the Flag in the found set by using a Replace on the Flag itself:
Replace ["Flag", "1"] or conversely,
Replace ["Flag", ""]

One use for this would be to temporarily mark a found set, so that you could come back to it later after it was lost.

Another way to use a Flag is to make it a global field. It would be unstored, but would be the same for all records. One use would be a simple way to simulate opening preferences. You can make it a checkbox on the first layout so that the user can make choices, on or off; but other value lists with more choices are possible.

I use it on the introductory layout to allow you to click the checkbox saying, “Bypass this introduction,” after reading once or twice. An Opening script is written, which checks to see if the Flag is on, then goes to another layout if it is. In Preferences (Edit Menu), Document, Introduction is set as the layout to normally open to. But the Opening script is chosen to run on the document’s opening (just below in the dialog box), which can then override and skip the Introduction layout.

Another more serious use would be to set a global flag as 1, and then check it every time to see if the file had been cloned. Cloning removes all data including globals. If you have several global container fields holding buttons, etc., this can be a disaster. It can be mitigated somewhat by checking for a global upon opening, then going to a layout with “spare” buttons. Paste a version of the buttons as simple layout objects right next to their global container fields, with instructions how to paste them in. Other users would need access to Layout mode to do this (which could become problematic).

Relationship Flags
You can also use relationships to define fields which function like Flags. One of the simplest and most useful is:
Count (Self-relationship::ID field)

This will tell you how many records there are of that ID (it will equal one if there is only one). You can create a concatenated (put together) calculation field of more than one ID field, e.g., ID & ” ” & ID, to count how many there are of records that have both of the IDs in them. You can add a Mark field onto the end to find how many of those are marked. Even though these are non-indexed, FileMaker seems to be able to calculate them fairly quickly. And because they are unstored, they will give an immediate feedback. This is useful for such things as duplicate warnings, which must appear before the user leaves the layout. They do, however, need to either “tab” or “click out” of the field being used before it can recalculate.

Use these simple fields to create seemingly complex operations without adding very much space in your files. Everyone will be impressed.

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

Imports & Lookups

I want to talk a little about a small problem with Imports. It’s not quite a bug. FileMaker admits it’s a problem, but apparently it would be difficult to fix, so it remains. It has to do with the Import order. The Import dialog box is pretty intuitive and even amazing, the choices and flexibility you are given.

Import Order and Disorder

What they don’t tell you is that the order is rather fragile. Not fragile in the sense that FileMaker forgets it. Quite the opposite. The problem is in the inflexible way that it assigns and remembers the order. Everything works great as long as all the fields originally assigned are there. But if you delete one of those fields, you’re suddenly in big trouble. Likely as not, all the fields created after the one you deleted will be re-serialized, which means they’ll no longer match those of the source database file you are importing from. You’ll get the data, but in the wrong places.

Not only that, but unless you resort to drastic measures, it will never match again! You can drag the field names in the dialog box so they seem to match, but the order will still be off when importing. You’ll notice the problem if you scroll down to the bottom of the list then back up to the top, everything will be one off. You fix it and scroll down again; now the bottom’s off… I’ve found that after this disaster, which usually happens to a scripted Import, I can’t even get it to import correctly manually.

There is one easy prevention: Don’t delete fields. Change ones that are unneeded or mistakes to Global fields, then rename them beginning with a mark, such as “`”, so that they sort to the bottom alphabetically. They’ll take up very little space in the file and be out of the way. If you need a new field, use one of them and change the name. It will keep the import order intact.

The safest and easiest way to create an import order is to try and use the same field names in both files. Set both Define Fields lists to creation order. Then in the Import dialog choose Matching Names. Everybody should line up pretty close. Drag the few misaligned into place. Try hard to get them all.

Turn off the arrows for any fields that don’t have to be imported, calculation fields, globals, etc. They will be recreated in the new file anyway, and it will save time.

Reorder Fields (you’d rather not)
There is a cure for a wrecked order, but you’re not going to like it. If you deleted a field in the file that you’re importing into, then all the fields above where that one was got moved up to close the gap. You’ll need to move them all down.

One way to do that is to create the new field that you need to match the old one in the source file, to fill the gap so to speak. Then delete the field after where you want it go (in creation order). Recreate the deleted field, so it’s now last in creation order. Repeat for each field after, until you have a new order, with the new field in place and the rest after. Everyone should line up again.

If you do all this without closing the Field Definitions dialog perhaps the field names will be preserved in Layouts and Scripts. I don’t know; I’ve never tried it, and hope to never have to.

Another perhaps easier way to change the field name order is to rearrange it, then export the file as a FileMaker file. It will have the new order as creation order. But it will also drop all of the layouts and scripts. The layouts can be recreated easily enough, with copy and paste. But the scripts can’t, unless you use a third party tool to copy them over (I haven’t tried this, but there are tools available).

Imports with Lookups

Besides all of the above, there is another problem with Imports. If you have a lot of fields they are painfully slow. FileMaker assumes that since a field was indexed in one file, you’d want it indexed in the other. So it pauses to recreate the index of those fields when importing.
You could turn off the option to perform indexing in the second dialog box that appears when importing, but then you wouldn’t get any serial numbers or other auto-enter functions either, which are usually important.

Importing with Lookups
In any case, I’ve switched to using a combination of Importing and Lookup options to get the best of both worlds.
I import only one field, the defining ID field of the records I’m bringing in. It may be an auto-entered text serial number in the source file, or it may be a concatenated text field of two ID fields put together, but it will be a regular text field in the target file that I’m bringing them into.

By the way, if this field is created early on, you are pretty safe from the deleted-field disaster mentioned earlier.

Now I can get the main ID field safely and quickly into the new file. The other fields can all be referenced from this ID. What I need to do this is to open Define Fields and redefine them as auto-enter Lookups.

There should be a relationship to the other file, between the two ID fields. I redefine each field by clicking on Options, Auto-enter, Lookup. I choose the ID relationship, then the same-named field in the old file as the field to copy.

Relookup Script Step
It is usually necessary to run a Relookup script step, based on ID field, as a second step after importing the ID field, so that FileMaker can register the new relationship before doing the lookups. This takes a little time, but it is still several times faster than importing all the fields.

Imported Records are Found Set
An important point to remember is that immediately after importing, the imported records are the “found” set. This means that any extensive operation, such as a Relookup or a Replace, only occurs on those records.

For me this is critical, because I use a different lookup (mentioned in previous articles) to mark the first entry of a group of similar records (first person, first job, etc.).

First Mark (short version):
Basically, it uses a self-relationship between the ID field and itself (ID= ::ID).
There is a MarkFirst field that is defined as an auto-lookup based on that relationship.
It looks up a “Null g” field, a global field that has nothing in it. If there is no match it is set to use 1. The box for “Don’t copy if empty” is unchecked. This allows it to copy the empty Null.

A self-relationship isn’t valid until there are two records with the same data in the field. So, for every first entry it looks up a 1. For all later duplicate entries it looks up the Null g field and there is nothing in it.

Now, the problem is that once the self-relationship is valid, a Relookup command on those records using that same ID field will wipe out all those First Entry marks. So it can only be run on new records. This works fine. If they are a first entry they’ll get a 1, if not, they won’t; but the marks on the earlier records, which are not in the found set, will be left alone.

Any other operation, such as Replace, that changes the ID field would do the same, because changing the field automatically triggers a Relookup. Fortunately, there are very few reasons to change an ID field once it’s entered.

Replace on Imports
You can take advantage of the imported records being the found set to run Replace script steps. You wouldn’t need to do this often, as records would generally have the data they need in the other file already. But I can think of one case where I do it.

I have a template file, that stores a base set of data, job-costing amounts for making estimates. After filling in the number of units I move all those records into an actual Estimate file, which has several different estimates, one for each separate job.

The problem is that there are no such things as “jobs” in the template form. But the estimates entries must be identified by job ID; and the job IDs come from a third Jobs file. So, just before the Import, I go to a layout that has the active jobs from the third file displayed in a portal. The script pauses until I click on one.

The Job ID value from the portal row is set into a global field, which can then be easily set, via a Constant (1=1) relationship, to a global, Job ID g, in the other file (or even set directly via the relationship). Right after importing, this local global, now holding the Job ID, is used in a Replace script step to enter it into all the found records.

(Remember that Replace is like paste. The field has to be on the current layout. I won’t say how many times this has screwed me up.)

Lookups vs. Relationships

This is a big topic (and this article is already long), but I’ll just make a couple of points.

Layout with Global Containers
Example file: AltRows

This month we’re going to have some fun. We’re going to look at how to use global container fields to create layout effects, such as alternating background color and highlighting.

It’s difficult to look at a full-screen list view of rows (records) when they’re all the same color. You can leave darker spaces or stick lines beneath each record to separate them. But my experience is that it not only takes up a little extra room, but is actually distracting, making it harder to focus on the data.

It also violates a general rule of layout that I’ve discovered, which is, “Show the minimum of layout objects that are needed to distinctly display the relevant data.” Something is needed to “distinctly” show the data; just plopping it on a background isn’t enough. But too many layout elements can be worse than too few. Every line draws some attention, so make sure it is needed. You can also lessen the confusion of interfaces by reducing contrasts, e.g., using a light gray next to a slightly darker one rather than too much black and white. Notice also that you only want the “relevant” data. If there is too much information it also becomes difficult to quickly see what you need.

Alternating Row Background for Lists
The best way to show lists I’ve found is to have alternating rows of light gray and white, or similar colors. The data is clearly visible and distinct from the adjacent records. This capability is built-in as an option in Portal views, and is easy enough to create for other lists.

It uses a global container field to hold the alternate color(s) and a calculation field to display it. Remember that a global field is available to all records. So all that is needed is a calculation that is true for every other record.

Layout steps
First create a global field, RowBack g, type Container. In Layout Mode, create a new blank “Graphics” layout. Click on the Body tab and choose a light gray fill, so you can see the next steps. Put RowBack g on it; most any size will do.

Give it a line but make the fill transparent. Choose Graphics from the Format Menu, then check Reduce or Enlarge image and uncheck the Proportional box. This will allow whatever you paste in–regardless of its size–to completely fill the box.

Select the Rectangle tool over in the Tool Bar and draw a box on the layout. Fill it with white. Copy it. Now switch to Browse Mode, click RowBack g and Paste. It should fill with white. (Having to copy in Layout, then paste in Browse is annoying, but unavoidable.)

Calculation
In order to only have this color appear on every other record we need a calculation field, also with a container result; RowBack c=
If (Mod(Status(CurrentRecordNumber), 2) = 1, RowBack g, “”)

The Mod function divides by a number (2 in this case), then returns the remainder. This returns 1 for the 1st record, 0 for the 2nd, 1 for the 3rd, 0 for the 4th, etc. Since Status(CurrentRecordNumber) is recalculated for every found set, it will always be this pattern no matter what records are showing.

List View Layout Steps
Go to the List View layout, in Layout Mode. Put RowBack c across the whole body, a little wider than the last field on the right. Use the Size info box to make it the same height as the Body. Put it right up against the top (Header), but not into it. You can tell if you can just see the little dotted line.
(I set my Rulers to pixels with 1-pixel increments.)

A tip (from Beverly Voth) makes it fill the Body perfectly. Use the Field Borders dialog, Format Menu (Cmd-Opt-B) to set the border lines on for all sides. Then in the line width set it to none. This allows the background color to completely fill the size you’ve set.
Choose Graphic from the Format Menu, check Reduce or Enlarge, no Proportional for RowBack c.

Send RowBack c to the back of the layout (Cmd-Shft-Opt-J). Choose the lightest gray for the Body color (use the Body tab). Switch to Browse Mode and voila, alternating row backgrounds.
If it doesn’t work, drop RowBack c down a pixel. It often gets sucked up into the Header.
Once you get it just how you want it, you might want to lock it, so that you can select fields on top of it easily without accidently missing them and selecting RowBack c.
Another minor tip, whenever you need to do any layout work in the body, click on a row in Browse Mode that has the background color first before switching to Layout Mode; otherwise you get the default graphic picture, making it impossible to see anything else.

Set all your fields on the row to be transparent. Borders are optional. I find that the top and bottom ones aren’t needed. A clean way to show off columns of right-aligned numbers is to set them to have only a left border, with a medium gray line color. Put a pixel or two between fields. It looks clear but uncrowded.

Easy Row Outliner
Another great side-effect of the RowBack c field is that it can function as a fast and easy row outline highlight. For best display the other fields should be non-entry. Rowback c must be enterable (Field Formats, Format Menu, or Cmd-Opt-F).
When you click on a row, the edge of RowBack c turns black, instantly outlining the current row. It’s very useful in wide column layouts that go off the screen, so you can still be sure which record you’re on, without having to always glance over at the little vertical bar on the left.

Highlight with Color
There is another more graphic way to highlight the edge with color. It involves a little more effort and has one serious drawback. It only involves a simple script, but it causes the screen to redraw. Still, if you want to really highlight a wide record for important editing or viewing, it may be worth it.

Define another global field, RowBox g, type Container. Go to the Graphics layout, Layout mode, and put it on the layout. This time stretch it to about the same size as you’d want it. Set it to Reduce or Enlarge, just to be safe. Now draw a another box with the Rectangle tool, the same size as RowBox g. This time set the fill to transparent and the line color on and colored. Copy the box. Switch to Browse Mode and Paste it into RowBox g. It should look the same.

Define a new calculation field, RowBox c, with a container result.
If (Serial ID = Serial ID g, RowBox c, “”)
[You'll also need the usual fields, Serial ID, auto-entered serial number (type text), and a global, Serial ID g, type text. You almost always need these.]
Now you need a Script:

If ["Serial ID <> Serial ID g"]
Set Field ["Serial ID g", "Serial ID"]
Exit Record/Request
End If

Put RowBox c on the List View layout, on top of the fields. The easiest way is to Option Shift-Drag RowBack c a little, then double-click it and change it to RowBox c (since they’re the same height). Hit Cmd-Shft-Opt-F to bring it to the front. I like to make RowBox c a little narrower than Rowback c, so I can select either.

If the fields below are not editable, you can attach the script directly to RowBox c (Format Menu, Button). Then just clicking on a row will highlight it.
But if they are editable, you’ll need a small button to trigger the highlight. It can be in the box, on the left. Or the highlight could be the result of any navigational script, by tagging the above script at the end.
Set RowBack c to be non-enterable if you’re using the small button method, so it doesn’t trap the cursor if you click somewhere else first.

Background Highlight
You could use pretty much the same setup to do a background highlight color. Then you wouldn’t need the RowBox g field with the box. Just redefine RowBack g to be repeating (3). On the Graphics layout, Format it to show the 3 repetitions. Paste a background highlight color in the second one (while you’re at it, paste a darker gray into the third repeat now too).

Get it by changing the RowBox c calculation a little. RowBack Hc =
If (Serial ID = Serial ID g, GetRepetition(RowBack g, 2), “”)
Put it on the layout just above the RowBack c field.

Conditional Background Highlight
One more possibility. You can have more than one calculated background color. Since this doesn’t depend on a script, it won’t cause the screen to redraw.
I found that I needed this in a case where I wanted to show that some records were different from the others, while still keeping my regular alternating background on the rest. The first part of the calculation could be any condition you want; it will override the alternating color when it is true.
For the darker gray in the 3rd repetition of RowBack g, use this calculation in place of RowBack c.
RowBack Alt c =
If (Mod(Status(CurrentRecordNumber), 3) = 1, GetRepetition(RowBack g, 3), “”)

Option-Drag RowBack c to make this new field, just above it on the layout. This will show the darker shade of gray on every third record.
I used a simple variation of the first calculation, but it could be anything.

To be efficient, you could put the calculations together, so that you only needed one calculation field instead of two to do both backgrounds.
RowBack c=
If (Mod(Status(CurrentRecordNumber), 3) = 1, GetRepetition(RowBack g, 3),
If (Mod(Status(CurrentRecordNumber), 2) = 1, RowBack g,
“”))

This is known as a “nested” calculation. Notice that the Mod, 3 argument goes first, so it can override and give us a different color. Also, the default is written last, on a line by itself. This not only makes it easier to read, but easier to count those darn parentheses. Two If’s requires two parentheses at the end. You could even put each argument and result on a different line, as the calculation dialog ignores typed carriage returns.

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

Calculated keys & portals
Example files: Phone.fp3,Import-Transit

This month is going to be mostly catch-up, for me to go back and explain some things in the example files. There were a couple of layouts and operations that got left out. Maybe you’ve already figured them out.

Alphabet Layout with Calculated Key
Keys do not have to be plain text fields or number fields. You can also use calculated fields on either side of a relationship, as long as they can be indexed, and always on the left side..

In the Phone.fp3 file there is a layout named Alphabet. There is a global field, Alpha g, on the left, formatted as radio buttons. It is also formatted to use the calculation field Alphac as its Value List. As soon as you have enough records, it will appear as the letters of the alphabet (you could type in the alphabet yourself, but why bother)..

Alpha c is defined as the simple formula, = Left (Last, 1)
Last is the last name field. This will give you the first letter of the last name..

There is a relationship between the two fields, Self Alpha;
Alpha g= ::Alpha c.

The portal is also based on the same relationship. So if you click one of the letters, a relationship will be established between that letter and first letter of the last name field (in the portal) instantly showing the related records. To go to that record, a transparent button over the names is attached to the script Go Main Alpha g:.

Set Field ["Serial ID g","Self Alpha ::Serial ID"]
Exit Record/Request
Go To Related Record [Show,"Self Serial ID g"]
Go To Layout ["Main View"] .

The global Serial ID g temporarily holds the value of the Serial ID field, which is an auto-entered text serial number; most files should have these two fields. The Self Serialg relationship between them will always be able to identify a record(s). You have to use these steps to get out of the portal, which is considered as if it’s a separate file..

Clairvoyance Layout
The Clairvoyance layout takes this idea a step or two farther. It uses a simple multi-key calculated field to achieve a many-to-many relationship. We’ve seen a similar relationship before in the Copy All Records trick (which I’ve already written about twice). This time though the multi-key is built by a calculation field rather than by the copy/paste trick. It’s still the same general principle, which I would state as:.

For relationships FileMaker considers each line of an entry in a field to be a separate record..

So, the only thing that is needed is to put paragraph returns between them. In this case we’ll just expand on the earlier calculation field (text result) to create a new one;
Match c = Left(Last, 1) & “¶” & Left(Last, 2) & “¶” & Left(Last, 3)
(“¶” is the paragraph return symbol in case html mangles it)
The first line will hold the first letter, the next the first two letters, etc…

A global text field, Last g, will hold user-entered letters to match up to this. Make a relationship between them, Match Last g;
Last g= ::Match c.

As you type letters into the Last g field, the records that match it will narrow down to the correct name(s). There is one problem: the portal records don’t refresh unless you either hit the Enter key or click somewhere outside the fields. But then you’re not in the Last g field anymore. To type another letter you have to click back into the field. Clunky..

There are two methods I know of to fix this. Both use scripts. The first begins a loop, pausing and resuming every second to refresh the portal. This works, but the screen refreshes are unnerving, and you still have to click to end the script. I came up with an alternative script, Clairvoyancer, which is a little smoother, but still requires the Enter key..

Loop
Exit Record/Request
Go to Field ["Lastg"]
Pause/Resume Script []
End Loop.

It requires a button, Begin, to start it. You enter a letter with your left hand then hit the Enter key with your right. This feels fairly natural. It triggers the Pause/Resume step, the portal refreshes and the cursor is in the Last g field, ready for the second letter (in case just one wasn’t enough). The loop continues..

Now we come to the fun part. Because of the paragraph returns, the two letters in Last g
will match the Left(Last, 2) of the Match c field. If that’s not enough, enter a third letter, and hit the Enter key again. Last g will now match the Left(Last,3) line of Match c. .

There is also a transparent button over the names in the portal, this time based on the Match Last g relationship, to set the Serial ID and go to that record..

The one caveat is that the loop is still continuing. It can be stopped by choosing the Exit Script option in the little drop-down menu that appears when you attach the following script to the transparent button:.

Set Field ["SerialIDg", "Match Last g::SerialID"]
Exit Record/Request
Go to Related Record [Show, "Self Serial g"]
Go to Layout ["Main View"].

Just to be safe, in case there is no match and the user leaves the layout, every navigation button on the layout should also have this option chosen. You’d want to stop them using the Window Menu also; but that’s a whole new kettle of fish..

Limiting Menus
On that topic, let me just say, without going into detail, that you should provide buttons for most functions for three reasons:
1. You can add script steps and parameters, such as above, that are unavailable to the standard FileMaker menus and command keys.
2. The user can get used to finding what they need on the layout, so they’ll use the buttons rather than the standard menus.
3. Eventually you may have to lockout certain menu commands or even the entire Menu Bar to keep control of things, so you may as well begin adding buttons now..

Multiple Checkbox Choices in a Global Field
In another situation, with a non-alphabetical field, the global field could be formatted as a checkbox to take advantage of the multi-line matching ability as well, without needing a calculated field. Relate it to a regular text field. Multiple choices in a checkbox field (or radio buttons if you use the Shift key) are also considered as separate entries, so all records relating to any of the choices would show up in a portal based on the same relationship.
Remember that the fields in the portal have to be defined as belonging to that relationship as well or they won’t show up..

Reviewing Duplicate Records Before Importing.

I added a routine in the Import example file to allow you to review duplicate records in Transit before deleting them or using them to update the original entries in Import. The operations are fairly simple, but they hop back and forth a little..

Checking Related Records
I think I’ll put the script first, Check Duplicate Names:.

Perform Script [Sub-scripts, "Copy All Records"]
Go to Layout [original layout]
If ["IsValid(Concat g::Last)"]
Show Message ["There are new records in Transit with the same name.
Do you want to check them before overwriting the originals in Phone?"]
If ["Status(CurrentMessageChoice) = 1"]
Go to Related Record [Show, "Concat g"]
Perform Script [Sub-scripts, External: "Transit"]
Comment ["Halt Script <--Import"]
Else
Exit Script
End If
End If.

The new steps begin with If ["IsValid(Concat g::Last)"]. I love this step. In general terms, If [IsValid("the relationship::any field with data)"]. Its beauty is that it checks to see if there are related records before having to go to them.
That is much better than using the Go to Related Record [Show, "Concat g"] by itself and then having to trap for Status(CurrentFoundCount)=0. You can use this step to check for related records first in either scripts or calculations..

The way it is used in this script if there are no related records the first If isn’t fulfilled and nothing happens.
If there are related records the message above is displayed. “Yes” is the default choice, which is trapped by If [Status(CurrentMessageChoice)=1].
A simple script with a single step, Halt Script, is then called as an external script in Transit to stop everything. It’s in Transit so that its window is brought to the front and you can see the duplicate records..

Halt Script vs. Exit Script
The reason that I used Halt here is because this whole script is called as a subscript by the main Relookup script. Halt stops all scripts, whereas Exit only stops the script it’s in. If it’s in a subscript like this one, then subsequent steps in the main script that called it will continue. It’s sometimes a big difference.
If the “No” button is clicked in the dialog then the Else step happens, the Exit Script stops this script but the Relookup script continues..

Review Button in Transit
To make it easy to review records a script was added to Transit, attached to a small button that shows up on each row in list view..

If ["IsValid(Import concat::Concat)"]
Go to Layout ["Form"]
Go to Related Record [Show, "Import concat"]
Perform Script [Sub-scripts, External: "Import"]
Comment ["Form View <--Transit"]
End If.

The first step checks if there is a related (duplicate) record. If so it displays it in form view in both files, so you can see all the information and make a decision. I sized the layouts and windows so they can be tiled horizontally and still see everything..

Finally, there is also a button to delete the record in Transit. There is no need to delete the one in Import, as it will be overwritten by the Relookup script..

You’ll have to run Relookup again manually after reviewing, since I halted it. Another way to do it might be to use a different combination of Exit script steps between the files, so the script would only be paused. But this way seemed good enough, and less prone to user (and creator) error..

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