Skip to main content

Full text of "The FileMaker Report 036"

See other formats


A Journal For FileMaker^'^ Users 


The 

FileMaker 

Report 


Record-To-Record Information Transfers 


By Joe Kroeger 

IT is generally true in FileMaker that we want to 
structure files and define records in such a way that 
calculations (other than summaries) that use infor¬ 
mation from more than one record are not required. 
FileMaker provides only one general mechanism de¬ 
signed for record-to-record work: summary fields. 
Summary fields were created to solve certain specific 
types of problems and there are lots of things they 
cannot accomplish. 

In spite of trying to keep away from record-to- 
record calculations, there are cases where it would be 
very handy indeed to be able, from within a given 
record, to access a piece of data that lives in another 
record. You might attempt this in FileMaker Pro for 
some special problems by using a running total sum¬ 
mary, creating a Summary function to convert it to a 
calculable number, and then Sort on some value that 


is unique in each record to make the summary work. I 
have not attempted such a design. 

Let’s set up a specific problem of a different kind. 
Assume that I want to add the contents of the NumA 
field in the current record to the contents of the 
NumA field in an adjacent record. (“Adjacent” might 
be defined in several ways - for our purposes let’s say 
that an adjacent record is the next lower record num¬ 
ber after a Sort.) 

I had always assumed that this kind of problem 
could not be solved in FileMaker - or indeed in most 
other databases. Records, after all, are thought of as 
isolated containers of data, and non-summary equa¬ 
tions always refer only to data within a record. 

But if we could transfer data from one record to 
another, we could solve our problem without vio¬ 
lating the basic idea of a record. For such a task, it 
turns out that self-lookups can do the job. Self-look- 


1 Issue 36 

Page 

May 3, 1991 I 

Record-To-Record Information Transfers 

1 


A FileMaker Search-Only Field 

4 

-A- 

New Pro Serials 

6 

Calculating Elapsed Times 

8 


An Estate Planning Template 

11 

©1991 

Elk Horn Publishing 

To B(utton) Or Not To B(utton) 

11 

PO Box 1300 

FileMaker Quick Tips & Techniques 

14 

Freedom, CA 95019 USA 




ups are exactly the same as ordinary look¬ 
ups except that the lookup file is defined 
to be the same file as the destination file. 
FileMaker lets you enter the same file 
name into both sides of the file specifica¬ 
tion in the lookup definition dialog box. 

To solve the problem outlined above 
using self-lookups, the five fields shown 
in Figure 1 are needed in each record. 

Figure 1 

NumA (A plain numeric field) 

NumB (A self-lookup field defined below) 

NumC (A calculation field with NumC ■ NumA + NumB) 

SerialNum (Must be unique, numeric, sequential with no gaps, 
and with an offset of 1 from one record to another) 

LookupKey (Calculation with LookupKey » SerialNum -1) 


The ‘trick’ is to cause the information 
in NumA of, say, record 214 to be entered 
into NumB of record 215. It is the self¬ 
lookup that NiunB performs that does 
just that (see the lookup definition in 
Figure 2). All FileMaker lookups require. 


logically enough, that you specify the (a) 
source and destination files, (b) source 
and destination records, (c) source and 
destination fields in order for the lookup 
to be possible. With this in mind. Figure 2 
is less formidable. The top row indicates 
the files already specified, the next row 
lets you designate the source field, and the 
third row defines the key values that spec¬ 
ify the records. 

Once the fields and the lookup have 
been defined, it is important to set up 
SerialNiun data correctly. Since we’ll 
need to Sort on SerialNum and since the 
entries in SerialNimi must reflect the 
sorted sequence of the records, you 
should arrange for SerialNum to se¬ 
quence properly after you’ve done a Sort 
based on some other desired value. The 
SerialNum record-to-record difference 
can be any convenient value as long as it 
is consistent and as long as you adjust the 
LookupKey accordingly. When in doubt, 
use 1. 

There are several ways you can assign 
values to SerialNum. It should be an 


Figure 2 



Self-lookups help solve 
a variety of problems. 
They are just as easy as 
ordinary lookups and 
are worth investigating. 


Lookup Ualue for Field “NumB” 
Lookup File 

“Lookfldjacent” _ 

Copy the contents of: 

NumH I 

...when the ualue in: 

SerialNum | 

If no enact match, then 
(i) don't copy 
O copy nent lower ualue 
O copy nent higher ualue 
O use I 


Current File 

“Lookfldjacent” _ 

...into the field: 

“NumB” 

...matches a new entry in: 
LookupKey | 


[ Set Lookup File... ] 

[ Cancel I I I 


Page 2 • Issue 36 The FileMaker Report ©1991 Elk Horn Publishing 















auto-enter serial number field for entry of 
new records. For existing records, you can 
add serial numbers without much trouble 
- see the Quick Tips section in this issue. 
For some applications, you may want to 
update the SerialNum information with 
each new situation for which the multi¬ 
record calculation is needed. 

When you are ready to proceed and 
SerialNum is properly in place, here is the 
operating procedure: 

(a) Find the desired set of records, 

(b) Sort on SerialNum, 

(c) click in SerialNiun in one of the 
records, and 

(d) execute the Relookup command. 
Once the lookup has been completed, 

NiunC computes the desired sum. The 
process works when all records are found 
and also with found subsets of records. 

Figure 3 shows a working example 
using eight records. Notice that the value 
in NiunA has been transferred into the 
NumB field in the next record. 

There is one record to watch out for: 
the first record when no record has a 



lower SerialNum; there is no preceding 
record from which a lookup can be made. 
You’ll need to ignore the first record or 
insert an appropriate value in NiunB by 
hand in that record, or create a dummy 
record to act as the first one. 

Please let us know if this technique 
solves a problem for you and how you are 
making use of it. Do you have another 
approach that accomplishes this task? 


Welcome To The New FileMaker Report 


WE have redesigned The FileMaker 
Report, as you can see. We wanted to in¬ 
crease readability, provide a better pre¬ 
sentation, take advantage of new type 
faces, and create room for additional 
material in each issue. 

The original design for The FileMaker 
Report vfzs created in late 1986 and has 
changed only modestly since then. The 
present major revision reduces and con¬ 
solidates the overhead information and 


packs more than 35% additional informa¬ 
tion into each issue. Further, we are mov¬ 
ing most of the news-oriented articles into 
a separate publication so that even more 
tips and techniques can be brought to you 
in these pages. 

Please tell us what you like and what 
you don’t like about the new FileMaker 
Report. And continue to let us know the 
FileMaker topics that interest you. - JK 


Annex 


Look for a new free 
newsletter called The 
FileMaker Annex News¬ 
letter. It will be out 
three or four times a 
year and will contain 
news about FileMaker, 
Claris, templates, con¬ 
sultants, and so forth. 


The FileMaker Report ©1991 Elk Horn Publishing Issue 36 ’ Page 3 






















A FileMaker Search-Only Field 


"The 

Magic 

Dowser" 


More 


For additional informa¬ 
tion about using re¬ 
peating fields, see The 
FileMaker Report, issue 
29, page 11, "Flaws Into 
Features" by Mike 
Harris. 


By Joe Kroeger and Marc Henry 

WE came up with an interesting - if not 
earth-shattering - trick that turned out to 
be handy for one of the Library databases. 
It is a field designed especially for search¬ 
ing, rather than disp/ayzng information. It 
allows the user to search for a wide range 
of data that has been entered in a repeat¬ 
ing field, yet does not encumber the lay¬ 
out with long, space-consuming repeating 
fields. 

The opportunity to play around with 
such a field arose when Elk Horn was 
putting together a database of U.S. Presi¬ 
dents. One field showing the beginning 
and ending years of the term of each pres¬ 
ident was included. But we thought that it 
would be nice to be able to reverse the 
mental orientation and search the file for 
any specific year in order to locate the 
president who was in office in that year. 

In order to do so we could have created 
(possibly) a complex set of equations to 
extract a series of years from the term 


field, and we might have found it neces¬ 
sary to restructure the term information. 
Instead, it was decided to leave the term 
field as-is since it presents the informa¬ 
tion in a simple and useful way, and cre¬ 
ate a new field to act as a locus for the 
year-finding function. 

One of the characteristics of a repeat¬ 
ing field is that using it in a Find request 
locates all records that contain the re¬ 
quested value in any of the repeating field 
positions. Sometimes this works against 
you, but there are cases like this one 
where it comes in handy. 

A repeating field with 12 repeats was 
defined and added to the layout. In each 
record, each individual year of the term of 
office was entered into one of the repeat¬ 
ing positions. See Figure 1. Then a Find of 
any year using the Year Find field locates 
the president in office on that year. (As a 
bonus, note that when there is more than 
one president in office in a given year, this 
method locates them all.) 


Figure 1 


!□! 


US Presidents 



t 

) 

1 

( V 








Records: 





US Presidents 


FName 

Grover 


MI LName 


ILName 


Cleveland 


Grover Cleveland 


41 
Found: 
2 


No. 

Born 

Died 


m Browse 




r24"] 

l i i ^ 

iliwiB 


Term 
Year Find 


1893-1897 j 


1893 

1894 

1895 

1896 
1897 


2 


Page 4 • Issue 36 


The FileMaker Report ©1991 Elk Horn Publishing 


































Next we changed the Year 
Find field format to a single 
repeat only (Figure 3). This 
cleaned up the layout nicely. 
Note that FileMaker, thank 
goodness, does not throw away 
data already entered in repeat¬ 
ing fields when they are subse¬ 
quently collapsed to a single 
repetition. And FileMaker Pro 
allows repeating fields to have 
different numbers of repeats on 
different layouts. 

But then we noticed that in 
the found records in Browse, 
the Year Find field shows the 
first entry in the repeating set 
and that year may or may not 
be the one requested. Yes, the 
year requested in the Find does 
occur within the span repre¬ 
sented in the Term field. But a 



Figure 2 



Figure 3 


casual user might not notice that and A variation of this approach is to cre- 

would likely be used to having FileMaker ate a two-position repeating field (instead 
Find values appear in the requested field of 12). Leave the first line blank and enter 
as a mental cross-check. all the years in the second, with a soft 

Our solution was to make it a little space between each year. Then collapse 
more mysterious: we formatted the Year the field back to one line and use as 
Find field as repeating again, moved the above. 

first entry in each record to the bottom of Note that, as described, this is particu- 
the repeat set, left the first position blank, larly suited for relatively static data collec- 
and changed the field back to a single tions. If you have an application for 

repeat. Now, following a Find or at any something like this in a more dynamic 

other time, the field displays as blank, yet situation, it should be easy to have sepa- 

it properly locates the requested record(s) rate specialized layouts for data entry and 
when used in a Find. for data query. The data entry screen can 

Some fun, huh? While it was not our have the expanded search field available 

intent to purposely stump buyers of the for entering the strings of search data. The 

U.S. President template who have not query screen can have the collapsed ver- 

read this article, nonetheless I secretly sion of the field, 
relish the thought of the quizzical looks 
on some of their faces. 


The FileMaker Report ©1991 Elk Horn Publishing Issue 36 • Page 5 















Figure 1 


New Pro Serials (Better Than Oat Bran!) 


By Marc Henry 

FOR some time FileMaker has allowed us 
to auto-enter serial numbers into selected 
fields. This process is quite straightfor¬ 
ward in FileMaker II. After defining the 
desired serial number field, click on Entry 
Options and the dialog box in Figure 1 
appears. Simply enter the serial number 
you wish to start with and FileMaker 
automatically increments it and each 
subsequent serial number by one as each 
new record is entered. 


If you delete a record, the serial num¬ 
ber assigned to it when the record was 
entered is deleted as well and a gap in the 
serial numbers for the file will exist. This 
will rarely be a problem. When necessary 
it is always possible to go back and 
renumber records. 

New records that may be imported 
into the file will not have serial numbers 
inserted in the serial field - only records 
created within the file get auto-serialized. 
This will often be a problem. Appropriate 
serial numbers should be 



added either before the 
import takes place, or after. 

With the advent of File¬ 
Maker Pro we now have an 
extra level of style and ver¬ 
satility for serial numbering. 
After defining the serial 
number field and clicking 
on Options, Pro opens up 
new avenues with which 
serial numbers may be ma¬ 
nipulated more thoroughly. 
We now have the option to 
use letters and punctuation. 


And we can now increment 


The initial value may be either positive 
or negative. No letters or punctuation are 
allowed in this serial number except for a 
negative sign. Simple but helpful and it 
has served us well. 

When using the file, you can return to 
the dialog box in Figure 1 to find out the 
next serial number that will be assigned. 
The number in the box is always the 
number to be inserted into the next new 
record. 


the serial number by a value we specify. 
See Figure 2. 

However the Pro serial number uni¬ 
verse is not without bounds: there are 
limitations. The specified increment must 
be a positive - we can’t decrement. The 
specified increment must be a whole 
number between 1 and 32767. And even 
though the serial ‘number’ can be alpha¬ 
numeric, only the rightmost numbers 
within the serial number are increased by 


Page 6 • Issue 36 The FileMaker Report ©1991 Elk Horn Publishing 








the increment specified. Using 
the serial number 91EH123 as 
an example, only the 123 is ■■•fluto 

incremented; the 91 and the O t*’ 

EH remain untouched. S a ; 

Pro can handle some heavy newt 
duty serial numbers. For ex- jncre 

ample, consider ^ 

101ABC-XXX391/76543YZ. . 

Pro is able to increment this 
beast by other astounding 
numbers (10203 works), but ^ 

the only digits that will be DU* 

affected are 76543 since they □ Lo 

are the numeric figures far- ^ 

thest to the right. Figure 3 is a 
list view of a series of serial numbers 
using these example parameters. 

Note that in the Options dialog in Pro, 
the box that accepts your initial serial has 
room for up to 14 characters. You can put 
in more, as the example in Figure 3 dem¬ 
onstrates, but when you are through en¬ 
tering, it, the number slides over and only 
the first 14 characters show. You can still 
edit it by clicking and dragging toward the 
right, but it is awkward. And you can’t 


Entry Options forTewt Field “SERIAL #” _ 

-fluto-enter a ualue that is . -Uerify that the field ualue is 

□ the i | □ not empty 

S a serial number: □ unique □ an enisting ualue 

nent ualue 1 11 □ of type j Number | 

□ from I 


increment by 
□ data i 


Numoer 


□ Prohibit modification of auto-entered ualues 

□ Repeating field with a manimum of |2 ! ua 

□ Use a pre-defined ualue list: [ Edit 

□ Look up ualues from a file: ( Uyl lookup... 


ualues 


lookup... 


Cancel 


Figure 2 



Records: 
51 

looUtdII 


Serial Num 101ABC-XXX391/76543TZ 
Serial Num 101ABC-XXX391/86746TZ 
Serial Num 101ABC-XXX391/96949TZ 
Serial Num 101ABC-XXX391/107152TZ 
Serial Num 101ABC-XXX391/117355TZ 


view easily the whole value destined for 
the next new record. 

It would be nice to have an alpha¬ 
numeric serial number that automatically 
changes both letters and numbers and 
remains in the format we’ve specified. It 
would, hopefully, produce the serial 
‘number’ AABOOO after incrementing 
AAA999 by 1. (Rumor has it that Claris 
may be already at work on just such an 
improvement.) For simple versions of 
such ‘numbers’ it is also possible to calcu¬ 
late the desired result. 

There may be a few cases where it 
would be nice to have a decrementing 


serial number. Pro does not provide this 
capability. But you can often do it for 
yourself with a simple equation: 

DownSerialNum = 10000 - SerialNum 

Figure 4 (next page) shows the results. 
The field DownSerialNum can be used 
where needed. SerialNum need not 
appear on any layout. 

For all you insect collectors out there, 
it looks like we’ve found a strange little 
bug. FileMaker II and Pro both have trou¬ 
ble doing the arithmetic on some serial 
numbers which have more than nine 


Figure 3 


The FileMaker Report ©1991 Elk Horn Publishing Issue 36 • Page 7 































Figure 

Figure 



4 

5 


Serial Num 

Record 

— 

1 888dd68d55 

1 

■ 

0296954297 

2 

■ 

0296954298 

3 

■ 

0296954299 

4 

■ 

0296954300 

5 




m 


■□I 


List 


t t f " 

I ♦ ♦ • n 


Records: 
121 


Found; 
5 


looLliilBk^ 


a 


digits. Figure 5 is the series of serial num¬ 
bers that are produced by FileMaker Pro 
when you start with the number 
8888888888 and increment it by 1. (Per¬ 
haps it is just new wacky math that has 
me baffled?) 

Evidentiy FileMaker decided, reason¬ 
ably enough, that none of us would be so 
silly as to try to use serial numbers with 
more than 9 digits. It appears that num¬ 
bers under about 2111111111 are handled 
properly. It does not seem to matter if the 
serial field is defined as text or numeric. 
Basic serial number guidelines: 

(a) keep numeric sequences at nine digits 
or less. 

(b) keep total character count under 14. 

(c) specify the serial number field as 
Unique to help enforce exclusivity. 

-V* 


JSSSTK 

Time Calculator 


Calculating Elapsed Times 


By Sheila Kiiewer and Joe Kroeger 

THERE are a few new time-oriented 
functions available in FileMaker Pro that 
provide opportunities to simplify the 
many convoluted calculations needed in 
FileMaker II when computing elapsed 
times. Fields and calculation results can 
now be specified as having Time results as 
well as Text, Numeric, or Date. 

Four new built-in functions bring lots 
of power and convenience: 


Hour (time) 

Minute (time) 

Seconds (time) 

Time (hours, minutes, seconds) 

(Why did Claris elect to use singular 
“Hour” and “Minute” in the first two 
functions and plural elsewhere? It is hard 
to guess.) 

There are other functions that will be 
useful as well when working with time. 


Page 8 • Issue 36 The FileMaker Report ©1991 Elk Horn Publishing 







































like TextToTime and TimeToText. 
Date operations like Today, 
WeekOfYear, Year, Month will 
also often be useful. 

This article discusses an elapsed 
time solution that uses the new 
time functions. The problem: to 
compute the total elapsed time 
when given a start time and date 
and a stop time and date. 

We set up six data-entry fields 
for specifying the start and stop 
information: 

Start Date (date field) 

Stop Date (date field) 

Start time (time field) 

Stop time (time field) 

Start am\pm (text field) 


Time Calculator 


Elapsed Time Calculation 


Enter Start Time 

Start Date 2/26/91 
Start Time 3:00:22 
Start am/pm OAM ®PM 


Enter Stop Time 

Stop Date 3/10/91 
Stop Time 3:10:33 
Stop am/pm ®AM OPM 


Elapsed Time in Hrs:Min:Sec 276:10:11 
Elapsed Time in Days/Hrs:Min:Sec 11/ 12:10:11 


s 




Figure 1 

Date calculations like this provide a 
result that is simply an integer number of 
days. WeTl make use of this value in later 
calculations. 

Next we need the starting and ending 
hours in 24-hour time: 


Stop am\pm (text field) 

(The backslash is between am and pm 
in the field definitions because FileMaker 
does not like the frontslash character in 
field names.) A straightforward layout 
(See Figure 1) makes it easy to enter the 
data and to see the results. The two 
am\pm fields make good use of the new 
radio button option for value lists in Pro. 
Start and stop times are entered in regular 
12-hour notation. 

With the Start and Stop information in 
place, a series of calculations figures out 
the elapsed difference. (Some of the fol¬ 
lowing equations can be combined, if 
desired, but are presented in this form for 
simplicity.) 

The first step is to determine the gross 
number of days elapsed by simply sub¬ 
tracting the two date fields: 


Start Hour 12 = Hour (Start Time) 

Start Hour 24 = 

If (Start am\pm = "pm", Start Hour 12 
+ 12, Start Hour 12) 

Stop Hour 12 = Hour (Stop Time) 

Stop Hour 24 = 

If (Stop am\pm = "pm". Stop Hour 12 
+ 12, Stop Hour 12) 

The results are all numeric values. 

Next step: convert both start and end 
times to seconds: 

Start Seconds = 

(Start Hour 24 * 3600) + (Minute (Start 
Time) * 60) + Seconds(Start Time) 

Stop Day eSecs = 

(Stop Hour 24 * 3600) + (Minute (Stop 
Time) * 60) + Seconds(Stop Time) 


Time Cards 


These calculations 
provide the basis tools 
for building time-card 
files and other similar 
applications. 


Gross Days = Stop Date - Start Date 
{numeric result) 


(The lower-case “e” is used in these 
equations to represent “elapsed”.) The 


The FileMaker Report ©1991 Elk Horn Publishing Issue 36 • Page 9 





Stop Day eSecs value represents the por¬ 
tion of the time elapsed that occurred on 
the last day. But the Start Seconds on the 
first day should not he included in the 
elapsed time - it transpired before the 
Start time. We derive the elapsed seconds 
on the first day by subtracting Start Sec¬ 
onds from the number of seconds in a full 
day (86400 = 24*60*60): 


Start Day eSecs = 
86400 - Start Seconds 


Now we are ready to obtain the total 
elapsed time. The Time function needs 
hours, minutes and seconds as input and 
we can provide them: 


first and last days are added. The result 
(in time format) shows the total elapsed 
hours, minutes and seconds as shown in 
Figure 1. This result (or a variation suited 
to your circumstances) can be embedded 
in other calculations you might like to 
perform, such as billable hours worked, 
or gross pay, or experiment duration, or 
time cards, or so forth, 

A variation of the displayed elapsed 
time extracts the number of days and 
shows them in addition to hours, minutes 
and seconds. One way to break out the 
number of days is simply to find the inte¬ 
ger value of the number of elapsed hours, 
divided by 24: 


eTime Total = {time result} 

Time ((Gross Days -1)*24, 00, Start Day 
eSecs + Stop Day eSecs) 

The fractional days have already been 
converted into seconds so subtracting one 
firom the elapsed days provides the num¬ 
ber of whole days in the interval. The days 
are then converted to the hours (multiply 
by 24) needed by the Time function. 

Notice that we did not have to calcu¬ 
late the minutes portion and used ‘00’ 
instead. FileMaker saves us work by con¬ 
verting into minutes any time in the sec¬ 
onds section (the section after the last 
comma) that is greater than 60. 

Finally, the seconds portions of the 


Format 


The Time function has this generic format: 

Time (hours, minutes, seconds). 
When numeric values are included within 
the parenthesis - with field names or 
calculations or both - a time-formatted 
result is displayed. 


eDay Part = (numeric result} 

Int (Hour (eTime Total) / 24) 

Then subtract the hours that have been 
extracted as days and reconstruct the 
Time equation: 

eTime Part = {time result} 

Time (Hour (eTime Total) 

- (eDay Part * 24), 

Minute (eTime Total), 

Seconds (eTime Total)) 

In was necessary in this equation to 
explicitly include the minutes portion 
since the seconds part does not include 
any minutes information this time. Then 
with a little attention to the layout, we can 
display the Day and Time portions to¬ 
gether, as shown in Figure 1. We used a 
slash between them, but you could put in 
a colon if desired. 

There are other ways to accomplish 
this task. Please let us know if you come 
up with elapsed time calculations that are 
different. 


Page 10 • Issue 36 The FileMaker Report ©1991 Elk Horn Publishing 



An Estate Planning Template 


By Michael J. McLane 
Creative Software Design 

ESTATE planning is a complex subject. 
For large estates, or those with special 
distribution requirements, both a lawyer 
and a professional estate planner may 
have to work with you to establish the 
“proper” plan. “Proper” is in quotes be¬ 
cause changes in laws and personal cir¬ 
cumstances make even the best of plans 
only “close approximations” of the ideal. 


FileMaker Pro to the rescue! You can 
now have a tool which automates the 
sorting, processing and presentation of 
your asset information. The Estate Plan¬ 
ning template can be useful to the lawyer 
or estate planner who wishes to establish 
an easily-updated system to track a com¬ 
plex estate. Also, you can save money by 
having already gathered initial asset infor¬ 
mation. Asset information you’ve collect¬ 
ed is much cheaper than answering 


Tip 


Yes, you too will (pro¬ 
bably) die. But planning 
now can make things 
easier for those who 
survive you. 


To B(utton) Or Not To B(utton).. .That Is The Question! 

When developing templates with FileMaker Pro, there’s a temptation to put in lots 
of buttons just because you can! We must use this new capability with restraint. Can 
you remember your first Mac word processing document - the one with 20 or so fonts? 
It looked terrible and so will your FileMaker Pro templates if you don’t design for effi¬ 
cient and pleasing use of the button capability. 

I base my design philosophy on using buttons only when they enhance template 
operation. I designed the Estate Planning template discussed in this article using this 
philosophy. Scripts for infrequent operations are displayed under the Script menu only. 
As a rule, I don’t list scripts invoked by buttons under the Script menu. This reduces 
“menu clutter” and permits rapid script selection. Note in Figure 1 that only some of 
the scripts shown in Define Scripts are available in the Scripts menu. 

I used another new feature of FileMaker Pro in this template. The ability to tag a 
layout as the opening screen provides a small “commercial” for my firm each time the 
template is opened. This feature is in the Layout Options item under the Layout menu. 

When you use the improved color capabilities of FileMaker Pro, be conservative. 

Use colors to improve readability, to increase comprehension and in limited amounts. 
Yellow type on a red background may seem dramatic but it will soon irritate a user who 
works with a screen more than a few minutes. 

Be consistent. If you use a color to make buttons stand out fi'om the background, 
think about having all buttons in the same color. That way the brain associates that 
color with getting an action done by clicking. This will speed up user actions. If every 
button is a different color (and in a different location) on different screens, operations 
slow down. The user must now read button labels and look more closely when doing 
mouse operations. Color, like other graphic layout elements, should improve the ease 
and speed of template use. - MM 


Template 

Design 

Philosophy 


Color 


Anyone for a nice dark 
maroon background 
and light pink text? 


The FileMaker Report ©1991 Elk Horn Publishing Issue 36 • Page 11 





Figure 1 


Note 


Please understand that 
the discussion of estate 
planning elements in 
this article is from the 
viewpoint of a layman. 
Only a lawyer or pro¬ 
fessional estate planner 
can tell you exactly how 
this applies to you and 
your situation. 


Taxes 


Probate Fees 


Page 72 • Issue 36 



questions for a planner to whom you’re 
paying $150 an hour! 

To understand what the template must 
do, it is necessary to understand some 
information elements and information 
requirements of the estate planning pro¬ 
cess. Don’t use these explanations or 
definitions as anything other than a back¬ 
ground understanding of why the File¬ 
Maker template has various features. 
Legal Jargon 

Estate planning is a set of choices you 
make while living. These choices deter¬ 
mine how your assets will be transferred 
to others after your death. Another aspect 
in such planning is taxes paid on such a 
transfer when you die. Often more im¬ 
portant are taxes paid when the second-to- 
die spouse dies. 

Today’s laws often permit unlimited 
asset transfer to a surviving spouse un¬ 
taxed. When that spouse dies, however, 
large tax burdens fall due if the total estate 
is above minimum limits for federal con¬ 
siderations. Some states (Connecticut for 
example) impose probate-associated fees 
based on the value of assets transferred in 
the probate estate (under the provisions 
of your will). It makes sense to minimize 
the value of assets transferred this way. 
The Estate Planning template will help 
you make good choices for your situation. 


Property is transferred on death in 
several different ways. (Various types are 
in the pop-up menu for the Xfer at Death 
field in our management template.) Some 
assets, like life insurance proceeds, annu¬ 
ities, or retirement plans (IRA, Keogh, 
etc.) are transferred by the contract which 
established these assets. Each of these 
plans has a named beneficiary who gets 
the assets when you die. (They are often 
part of your estate for tax considerations 
but don’t transfer under your will.) In our 
FileMaker Pro template we’ll call these 
items “By Contract” transfers. 

When you are the sole owner of real 
estate or other property, these items usu¬ 
ally enter your probate estate and are 
distributed by the terms of your will. Such 
items are “Probate” tagged in our data¬ 
base. 

A person can have one or more trusts 
established, usually by a lawyer. A trust is 
like a “paper person” because it can own 
things and pay out the trust earnings and 
principle as you wish. It’s important that 
you identify assets owned by a trust. Our 
template permits tagging assets as belong¬ 
ing to one of several trusts for each 
spouse. 

Lastly, assets may transfer because of 
certain types of joint ownership. Though 
there are many variations of joint owner- 


The FileMaker Report ©1991 Elk Horn Publishing 





















ship (with a wild array of names for each 
type) every state has one form which 
means that: 

1. Each spouse owns half of the asset, and, 

2. Upon death that half immediately be¬ 
comes the property of the surviving 
spouse. 

I call this “Joint (half)” ownership. For 
assets owned this way, half the value is 
subject to tax consideration in the estate 
of the first spouse to die. All the value 
may be in the estate of the second to die. 
Template Features 

Now that we have a better understand¬ 
ing of data elements by which assets may 
be processed, let’s look at the template 
which aids in asset analysis. 

The data entry screen, shown in Figure 
2, provides all critical information for 
each asset. Each asset record has two 
fields containing value amounts. The one 
labeled Value represents the entire value 
of the asset. This amount is calculated 
fi-om the Shares and Share Price fields so 
the value of stocks and mutual funds can 
be easily updated. For other assets, such as 
an insurance policy benefit, you enter “1” 
into the Share Price field and the death 
benefit into the Shares field (or vice- 
versa). 

The Estate Value field is calculated to 
show one half of the value, when owner¬ 
ship is tagged as “Joint (half)” in the 
Ownership field. In other cases the Estate 
Value field contents are the same as the 
Value field. 

The top row of buttons, at the bottom 
of the screen, provides controls to add 
and delete records. These are a conve¬ 
nience item, supplied for persons un¬ 
familiar with keyboard shortcuts for these 
functions. 


Asset Name XEROX StOCk 


Type 

Value 

Estate Value 

U.S. Stock/Mut. Fnd. 
$95,168.12 
$95,168.12 

Xfer at Death In Trust 

VHO OVNS Her Trust B 
LIQUID SI Yes ONo 

VHERE? 

Merrill Lynch 880 

-77888 



Phone 

Remarks 

Marital Trust. Husband and 1 
are co-trustees 


Appraise Req? OYes Si No 
Appraised Date 

Shares 

845 

112.625 

55,000.00 

73% 



. 


Cost Basis 
Grovth 


_ 

Record Created 12 Nov, 90 
Record Modified 12 Nov, 90 



Figure 2 

Two buttons on the lower row provide 
estate reports for each spouse. Part of an 
example estate report is in Figure 3. It 
shows the value of the estate and the sub¬ 
total of assets grouped by their method of 
transfer. At a glance you can see just how 
much is passing via probate (your will) 
and other means. Lastly, one button sim¬ 
plifies entering new stock and mutual fund 
information to update asset values. 

Figure 1 shows less-frequent activities 
confined to the Script Menu. Reports 
always print to the screen only. If a paper 
report is needed, use the “Command-P” 
key combination or change the script to 

print paper Figure 3 

copies. - 

Her Estate ^ 

■•A"* Assets Grouped bymenner of transf erst death 


CK MctKod 

Estit< Vilut 

yaluf 

b IVvst 



XZPOX Stock 

95,168 

95,168 

Total for la Trust 

95,168 

95,168 

OvMrsU]^ 



1989 llcre<dc5 ScaaiL (Black) 

16,283 

32,566 

Hytticx Stock 

6,621 

13,242 

Totiil for OvFstrsliip 

22,904 

45,808 

BroBat# 



1985 lifteola.TovmCar’(Blu«) 

11,500 

11,500 

Silver To X Coat 

4,000 

4,000 

Tottd for Prolutt 

15,500 

15,500 

; 6SAin> TOTALS 

133,572 

156,476 I 


The FileMaker Report ©1991 Elk Horn Publishing Issue 36 • Page 13 



















FileMaker Quick Tips 

Field Definition Box 

Unlike FileMaker II, in Pro the field 
definition dialog box opens scrolled to the 
bottom of the existing list of fields. Pro 
does so no matter which of the field views 
is active. 

This makes it easier to add new fields 
and to see the recent entries at the bottom 
of the list when arranged in 
creation order. It does not 
help when moving back to 
examine or modify older 
fields closer to the top of the 
list, but manual scrolling to the desired 
list location is no more of a burden than 
before. When doing a lot of work with an 
equation, you can move it to the bottom 
of the list where it will show up each time 
the dialog box is opened. - JK 


Uieui by creation order 


Uieui by field name 



Uieiu by field type 

-| 


v/Uieiii by custom order 




C( » Curly Quotes in Equations? 

Typographer’s quotes (" ”) do not 
work where quote marks are needed for 
syntax in Pro equations. Straight quotes 
("") are needed instead. However, within 
a piece of text (instead of as calculation 
syntax) curly quotes are handled just like 
any other characters. The equation 

ABC = lf(Status < Type, Up3, ""Help!"") 

puts “Help!” in the ABC field when 
Status is greater than Type. - JK 


Print Faster 


with pre-printed forms. 


Simulating Pre-printed Forms 

If you have a complicated graphic 
layout that takes a long time to print and 
you print many copies frequently, you 
can be more productive using separation 
printing. This involves making two dupli¬ 


cates of the layout. 

On the first duplicate, delete all the 
fields, leaving only the layout graphics 
which always stay the same. On the sec¬ 
ond duplicate, remove the layout graphics 
leaving only the fields. Now print a mas¬ 
ter of the layout with no fields and take it 
to a printer for copies. In effect you now 
have a custom pre-printed form. 

Before you have the printer run thou¬ 
sands of forms, you might want to have a 
few done, then test print them to check 
for alignment. Some printing processes 
can shrink or enlarge the copies. 

When you’re ready to do a regular 
printing, put the forms in your printer 
and print using the layout that has only 
the fields on it. You end up with the same 
information on the page as before, but 
printing is much faster. 

Be sure to preserve the original layout. 
If you want to make changes, do so on the 
original, then repeat the duplication pro¬ 
cess. If you try to edit each of the dupli¬ 
cates, it will be too easy for something to 
get out of alignment. The original may 
also find use as a data-entry screen. 

The technique can also be used for 
making multi-color printing masters. 
Create a complete color layout, then make 
one duplicate for each color. On each 
duplicate remove all the objects except 
those that are to print in one of the colors. 
Now you have one layout for black ob¬ 
jects, one for red objects, and so forth. By 
starting with a single layout that has all 
the objects, alignment is preserved. This 
process is not quite the same as “color 
separation” used in printing color photos 
or ads because you will not be able to 
overlay colors, mixing then to obtain 
another color. - S.C. Kim Hunter 


Page 14 • Issue 36 


The FileMaker Report ©1991 Elk Horn Publishing 








Invisible Fields 

In Pro you can now create a field you 
can’t see! Just put white text on a white 
background (or red text on a red back¬ 
ground). Really hard to read. And easy to 
do if you created the white text for anoth¬ 
er reason for another field. If you lose a 
field this way, try Select All to spot where 
it is. - Mike McLane 

Adding serial numbers 

Record serial numbers are important 
in many FileMaker files. Here is a method 
for adding them to existing records. 

1. Decide on the starting serial number 
and the total number of records to be 
serialized. 

2. Create a text file containing that 
number of records and starting with that 
desired initial serial number. This can be 
done using FileMaker or a spreadsheet. 
(Commercial: The Elk Horn Library in¬ 
cludes a set of files called Sequences that 
contains a large number of sequenced 
records that can be used for this purpose.) 

3. Make a copy of the file to be serial¬ 
ized and open the copy. 

4. Add a Serial Number field to the file 
if none exists. If necessary, Find and Sort 
the records to be serialized. 

5. Import (Input From in FileMaker 
II) the text file created in step 2, being 
very careful to specify Update Records 
option instead of Add Records. Specify 
the Serial Number field as the on/y field 
to be updated. 

When OK is clicked, FileMaker takes 
each number from the text file and puts it 
in the Serial Number field of each record 
in the found set, and does so in the sorted 
sequence of the records at the time of the 
update. If everything works as intended. 


use the file to replace the original. This 
process may sound more complicated 
than it really is. A little practice makes it 
quite smooth. Just observe the cautions in 
step 5. The intermediate text file is needed 
because FileMaker does not make the 
update option available when importing 
from another FileMaker file. - JK 


^ 




V ^ i 



Assigning Invoice Numbers 

Let’s say that you have a file that is 
used to enter orders. Many of the orders 
are prepaid with a check or a charge card, 
but some are paid with purchase orders. 
You need to generate corresponding in¬ 
voices for the purchase orders. One of the 
fields in this file is Invoice Number. You 
find it desirable to issue invoice numbers 
for only those orders that are paid with a 
purchase order, so auto-entering Invoice 
Niunber does not work. 

There are a variety of ways to insert the 
desired data, but here is an easy one. Click 
in the Invoice Number field, execute 
View Index, scroll to the highest existing 
number, double-click it, and then man¬ 
ually add 1 to it (usually a simple back¬ 
space followed by keying one digit). - JK 

Function Case 

In Pro Claris has changed the case of 
some of the calculation functions. What 
used to be if(„) has now become If(„) in 
both the manual and the program. I like 
the new style a little better. You can use 
eitlier version to write equations in Pro. 
When you click on the function name in 
the calculation dialog box, it puts the new 
style into the equation. When you convert 
a FileMaker II file to Pro format, the old 
equations are left as-is; that is, the case is 
not changed. - JK 


Problem 


Don't try to print more 
than 9,999 pages - or a 
range that extends 
beyond 9,999 - to an 
ImageWriter. (This is 
easy to do when print¬ 
ing labels.) Either 
FileMaker or the Image- 
Writer Driver gets 
confused. 




The FileMaker Report ©1991 Elk Horn Publishing 


Issue 36 • Page 15 





The FileMaker Report 

A Journal for FileMcdcer™ Users 

Published by 

Elk Horn Publishing 
PO Box 1300 
Freedom, CA 95019 USA 

Voice Phone: 408-761-5466. Fax Phone: 408-761-5468 

Publishen Joe Kroeger 

Managing Editor for this issue: Joe Kroeger 

Journal designed by: Kathy Tomyris and Joe Kroeger 


About Subscriptions 

All subscriptions to The FileMaker Report are delivered 
via first class mail (U.S.) or air mail (outside the U.S.). 
Ten issues are published each year. Subscription lengths 
are based on a number of issues, not a length of time. 
Subscription rates are subject to change without notice. 
Rates shown are in US dollars. 

Number of Issues 


Destination 

5 

10 

15 

20 

USA 

$36 

$65 

$92 

$116 

Canada & Mexico 

$37 

$67 

$95 

$120 

Other Locations 

$44 

$80 

$115 

$146 


The Library of Congress ISSN number for The FileMaker 
Report is 0896-0313. Issue number 1 was published in 
January, 1987. Issues are numbered sequentially with no 
volume divisions. The issue number is printed on the 
cover and on inside pages. The date on the cover is the last 
date that editorial changes are made before going to press. 
The FileMaker Report is published ten times each year - 
every three to eight weeks. 

Unless otherwise noted, the entire contents of The 
FileMaker Report are copywrited by Elk Horn Publishing 
and may not be reproduced in any form without explicit 
written permission. All rights reserved. 

Other publications from Elk Horn Publishing: 

Elk Horn Library Catalog 
FileMaker Annex Newsletter 
FileMaker Application Notes 
RISC Management Newsletter 

Elk Horn Publishing is an independent business not 
affiliated with Claris Corporation or with Apple 
Computer, Inc. Various trademarked and copyrighted 
names and symbols are used in this newsletter. Instead of 
adding a trademark symbol at every occurrence, the 
publisher hereby states that it is using such names only in 
an editorial fashion and for the benefit of the trademark 
or copyright owner with no intention of infringement. 


About FileMaker 

The latest version of FileMaker is FileMaker Pro 1.0 v 2 
with a release date of April, 1991. Previous versions of 
FileMaker include: 


Version _ 

FileMaker 1.0 
FileMaker Plus 
FileMaker 4 
FileMaker II 
FileMaker Pro 


First Release Date 

May 1985 
August 1986 
July 1988 
October 1988 
October 1990 


About Claris 

Claris Corporation is the publisher of the FileMaker II 
and FileMaker Pro programs. The FileMaker logo and 
name are registered trademarks of Claris Corporation. 
The main address for Claris is (see below for mail stops): 
Claris Corporation 
M/S X-YY 
PO Box 58168 
Santa Clara, CA 95052 

Customer Relations is M/S C-11. Technical Support is M/ 
S C-12. Software Registration is M/S C-71. 

The Claris Technical Support phone number is 408-727- 
9054. Tech Support hours are 6:00 am to 6:00 pm Mon¬ 
day through Thursday and 6:00 am to 2:00 pm on Friday, 
Pacific Time. 


Elk Horn Publishing 

PO Box 1300, Freedom, CA 95019 USA