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

A FileMaker Search-Only Field

-A-

New Pro Serials

Calculating Elapsed Times

An Estate Planning Template

Elk Horn Publishing

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

PO Box 1300

FileMaker Quick Tips & Techniques

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

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,

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

A FileMaker Search-Only Field

"The

Magic

Dowser"

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

)

( 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

Page 4 • Issue 36

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.

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

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

Figure

Serial Num

Record

—

1 888dd68d55

■

0296954297

■

0296954298

■

0296954299

■

0296954300

■□I

List

t t f "

I ♦ ♦ • n

Records:
121

Found;
5

looLliilBk^

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.

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

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

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

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.

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?

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-

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

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

Total for la Trust

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

Silver To X Coat

4,000

Tottd for Prolutt

15,500

; 6SAin> TOTALS

133,572

156,476 I

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

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.

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

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