A Journal For FileMaker^*^ Users
The
FileMaker
Report
FileMaker Pro Security Features
By Glenn Brewer
Claris Corporation
XHE FLEXIBILITY and powct of the security capa¬
bilities in FileMaker Pro have been dramatically en¬
hanced compared to the features available in
FileMaker II. In addition to more levels of password
control (for example, the ability to prevent users from
modifying scripts or printing data), there is a new fa¬
cility that allows the database designer to control ac¬
cess to individual layouts and fields. Because the
controls provided are comprehensive and extremely
flexible, there is a level of complexity and power that
may be unfamiliar to existing FileMaker users. Once
the fundamental concepts behind the design are
understood, however, maintaining security for data¬
bases will become easy and natural.
Design Philosophy
When we started working on enhanced security for
FileMaker Pro, we realized that there were two funda¬
mental things that we needed to provide control over:
Activities and Resources.
Activities-Control over activities was first provid¬
ed in FileMaker II through the password mechanism.
Each password can be allowed access to various activi¬
ties in the database such as the ability to create or edit
records or the ability to design layouts or edit scripts.
This fundamental design has not changed since File¬
Maker II, except for the addition of new activities.
Passwords can be assigned to protect the database so
that particular users can only perform authorized
activities in the database. Furthermore, passwords can
be assigned and used completely independently of
groups; entire security schemes can be designed that
only involve passwords.
Resources — In FileMaker II, control over resources
was provided at the global level through the mecha¬
nism of Confidential layouts. Unfortunately, this
scheme suffered from weaknesses which ultimately
Issue 40
Page September 18,1991
FileMaker Pro Security Features
Q&A: Add and Update Records at the Same Time
DBF Format Reveals Maximum Length of Field Data
Quick Tips & Techniques
1
8
10
14
-Ar>
© 1991
Elk Horn Publishing
PO Box 1300
Freedom, CA 95019 USA
Important
Passwords specify
activities.
Groups specify
resources.
limited its usefulness. These included the
inability to make different layouts confi¬
dential for different users, and the inabili¬
ty to control access to specific fields. With
FileMaker Pro, we have attempted to
overcome these limitations by providing
complete access control over all layouts
and fields. In the new scheme, controll¬
able resources now include layouts, fields,
and even passwords. In addition, access to
these resources can be completely restrict¬
ed (not accessible) or can be made not
modifiable (view only).
Rather than associate control of lay¬
outs and fields with individual passwords,
we decided to add the idea of groups, to
provide maximum flexibility. Groups
seemed like a fairly natural way to limit
access to a database because they mirror
the structures in typical organizations.
Also, without groups (or some similar
mechanism) it would be difficult to assoc¬
iate similar access with more than one
password. For example, we have two
users. Bob and Mary, that work in the
same department and need to access the
same basic information. In this case. Bob
Figure 1
can only create new records - he cannot
edit existing records and he cannot delete
records. Mary on the other hand, can
perform all of these activities. Further¬
more, Bob can use layouts one, three, and
five and can view every field except for
Salary. How can we easily specify that
Mary should have the same access to fields
and layouts as Bob? Moreover, if we were
limited to passwords only and we decided
to limit further Bob’s access in the fiiture,
we have to remember to make the same
modifications to Mary’s password (and
any other passwords that share Bob’s
access). In a complicated database, main¬
taining this information could be very
time-consuming and prone to error.
As another example, let’s say that Mary
needs temporary access to some addition¬
al fields and layouts for a special project
she is working on. If access to fields and
layouts was directly linked to passwords,
we would have to painstakingly record
Mary’s current access so that we can re¬
store her password to its previous state
when the project is over. Also, once we
later remove the additional access re¬
quired for the project, it is gone forever;
we have to recreate it from scratch if we
need to do this again in the future.
Why Groups?
Groups solve the problems described
above while providing a high degree of
flexibility. Groups are completely separate
from passwords, allowing users to create
simple security schemes that do not in¬
clude group assignments. It solves the
problem of inheritance because we can
now assign Bob and Mary to the same
group and freely change the group’s access
without the worry of forgetting to update
every person’s permissions. It solves issues
Page 2 • Issue 40 The FileMaker Report ©7997 Elk Horn Publishing
related to temporary access because we
can create a temporary group that has the
appropriate permissions and link that to
Mary’s password for the duration of the
project. And when the project is over, we
can simply remove the link between her
password and that group, and we don’t
lose any information.
Groups also align nicely with struc¬
tures in typical organizations. For exam¬
ple, a company will generally have several
department functions such as Finance,
Operations, Marketing, and Human Re¬
sources. Usually, these groups will need
similar access to fields and layouts in the
database. Moreover, each password can be
associated with multiple groups allowing
an individual password to see the data
available to several different departments.
One way to look at the relationship of
passwords, groups, fields and layouts is
shown in Figure 1 (on page 2).
When a user opens the database using
their assigned password, their identity and
association with one or more groups is
established for that session. User passwords
control what activities they can perform in
the database (e.g., Delete Records). User
group affiliation defines what resources
(fields and layouts) they can access during
the session. This is the essence of the File¬
Maker Pro security system in a nutshell.
Notice that each password can be
linked to multiple groups. Also notice that
passwords are indirectly linked to fields
and layouts through groups. Understand¬
ing that passwords can “participate” in
multiple groups and that they are only
indirectly linked to fields and layouts
through groups are important concepts
when designing a security scheme with
FileMaker Pro.
Note
A user password
controls available
activities. The group
affiliation for the
password controls
available resources.
Figure 2
Access Priuileges for File “personnel”
Groups
Passmords
Layouts
Fields
Operations
•
Master
Data Entry
Department No.
Finance
Bob
a
Personnel Report
Name
Marketing
Mary
Compensation Am
Address
Human Resources
Catherine
Mailing Labels
City
Frank
State
Zip
$
Home Phone
Social Security N
Last Review
s
Salary
1
1
Stock Grant
1
a
1
a
1
a
1
1
SI
• Accessible ® Not accessible o View only
A These links are locked until a group is selected.
tUymyri
Done
The FileMaker Report ©1991 Elk Horn Publishing Issue 40 • Page 3
Note
Unlike many FileMaker
dialogs, the Overview
dialog is packed with
information. It rewards
detailed study.
The Overview Dialog
The Overview dialog is the mechanism
that associates passwords, groups, fields
and layouts. See Figure 2 on the previous
page. The dialog has four lists that contain
the name of each group, password, layout
and field in the database. On the left side
of each of the four lists is a column con¬
taining small “bullets” where the database
designer can change the association or
linkage between the selected (highlighted)
item, whether that be a layout, field, or
password, and any group.
The basic method for using the Over¬
view dialog is described in three basic
steps:
1) Select a row in any of the lists so that it
becomes highlighted. All of the bullets at
the left of the other lists will change their
state to indicate their relationship to the
selected item.
2) By toggling the bullets on the left side
of each list, you can change the relation¬
ship between other items and the selected
item. If the row you have selected is in any
list other than groups, padlocks will ap¬
pear over the other lists indicating these
lists are locked until a group is selected.
3) Once you have made changes in any of
the lists (i.e., toggled any of the bullets),
both the Revert and Save buttons become
enabled. If you decide that the changes are
incorrect, you can undo your most recent
actions (i.e., any changes made since you
last saved) by clicking the Revert button.
If you wish to save your changes at any
point, you can click the Save button. If
you have made changes and attempt to
change your selection before saving
changes, a dialog will come up which will
force you to either Cancel, Save or Disre¬
gard your most recent changes.
Once you understand the basics of the
Overview dialog, there are several tech¬
niques to using it effectively:
Setting General Group Access
This is the most common method for
re j
Fielrl'^ Visible hut
All Layouts but
Not Modifiable by
Currently Selected
Compensation Anal ysis
the Operations
Group
are access! ble
Group
Access Priuileges for File “personnel’
Groups
UjOGISSIQS
PassuLiords
Finance
Marketing
Human ResourcesI
Master
Bob
Mary
Catherine
Frank
o
Passwords Master |-«
and Bob are linked
to Operations
Layouts
Fields
Data Entry
Personnel Report
Compensation Am
Mailing Labels
Fields Not Visible
to the Operations
Group
Department No. IK>1
Name
Address
City
State
Zip
Home Phone
Social Security N
Last Review
Salary
Stock Grant
Page 4 • Issue 40 The l^ileMaker Report ©1991 Elk Horn Publishing
using the Overview dialog because access
to all resources (passwords, layouts and
fields) are controlled by their links to one
or more groups. Select the desired group
in the left-most list. Then toggle the bul¬
lets in front of each password, layout, and
field to define its association with the
highlighted group. Solid (black) bullets
indicate that a password, layout or field is
fully accessible to the highlighted group. A
dim (gray) bullet indicates that the pass¬
word, layout, or field cannot be accessed
by the selected group. (Such layouts and
fields are displayed in Browse mode with
gray masks to prevent access.) A hollow
bullet, which is only relevant for layouts
and fields, indicates that the layout or field
is view-only and cannot be edited by
members of the selected group. (Note;
Master passwords are shown in BOLD
and always have access to every Field,
Layout and Group.)
In the example shown in Figure 3, the
Operations Group can only view a subset
of the personnel information. Sensitive
information like salary or number of
shares of stock granted are not visible.
Operations can also view any layout but
the compensation analysis layout which
will be completely grayed out. Further¬
more, they can view the information in
any of the visible fields but they cannot
change it.
On the other hand, the Finance group
does have access (see Figure 4) to infor¬
mation such as salary and stock grants and
can update that information if necessary.
Because they can also view (but not modi¬
fy) the name and address information,
they can produce reports such as compen¬
sation analysis by person or by department.
Changing Access for a Specific
Layout, Field or Password
Select a layout, field, or password by
highlighting the desired cell in the list and
Figure 4
Currently Selected
Group
All Layouts are
accessible to
Fi nance
Fields Visible but
Not Modifiable by
the Finance
Group
Access Priuileges for File “personnel’
11 1 » [Operations
Groups
PassLUords
O
Marketing
Human Resources
Master
Bob
Mary
Catherine
Frank
Passwords Master
and Mary are
linked to Finance
Layouts
Fields
Data Entry
Personnel Report
Compensation An.
Mailing Labels
K>
Fields Modifiable
by Finance
Department No
Name
Address
City
State
Zip
Home Phone
Social Security |J|
Last Review
Salary
Stock Grant
K>
The FileMaker Report ©1991 Elk Horn Publishing Issue 40 • Page 5
then to^e on or off a bullet associated
with a group.
Whenever you have selected something
other than a group, for example a pass¬
word, the other columns appear with
locks over them. See Figure 5. Layouts and
fields are only indirectly associated with
the password and those links cannot be
changed except for associating this pass¬
word to other groups. Since everything is
controlled through groups, changing links
in other lists can produce ambiguous
results. Therefore the information is
displayed but cannot be edited.
For example, if you select a field in the
fields list, the passwords list will update to
show which passwords can access that
field. Because each password might be
linked to one or more of the groups,
disabling a password’s access to that field
may indirectly remove its link to several
groups which is undesirable. Therefore, in
this case, changes in the links are only
allowed between the field and any groups
that can access it.
Determining Which Fields are on a
Layout
In addition to their obvious benefits
for workgroup solutions, the new security
controls in FileMaker Pro have significant
single-user benefits as well. For example,
using the Overview dialog, users can
highlight a layout and see which fields are
used on that layout. Conversely, they can
highlight a field and see which layouts
contain that field. See Figure 6. Users that
need to find all occurrences of a field
before modifying it or deleting it will find
these new capabilities invaluable.
In the example shown in Figure 6, the
field Stock Grant is only included on the
Data Entry and Compensation Analysis
layouts and is accessible to the Finance
and Operations groups. Notice also, that
the passwords Master, Mary, and Frank
Figure 5
The Human Reaources
group is linked to the
selected password
The locks i ndicate that these
pinks cannot be changed unless
a Group is selected fi rst.
Access Priuileges for File “personner
Groups
Passiuords
1
Operations
a
□
Master^
M
Data Entry
a
'm
Department No.
1
m
Finance
■
P
Bob X
■
P
Personnel Report
■
m
Name
■
1
P
Marketing
P
Mary ^
P
Compensation An<
m
Address
H
D
Human Resources
■
Catherine ^
■
P
Mailing Labels
■
•
City
■
■
•
1 Frank
■
P
R
•
State
■
1
1
■
P
IP
\ /
1
•
Zip
P
1 *
■
■
P
•
•
Home Phone
Social Security N
1
ft Layouts
ft Fields
These two passwords
are linked to the group
Human Resources
Each Field and Layout
displays its relationship
to the selected password
ast Review
talary
btock Grant
Page 6 • Issue 40 The FileMaker Report ©1991 Elk Horn Publishing
can access this field (indirectly because
they are associated with the Finance and
Human Resources groups).
Major Benefits of Groups
• Simple security schemes can be de¬
vised that only prevent access to unautho¬
rized activities. Access to layouts and fields
can be completely left out of any security
scheme if desired.
• Multiple users with different pass¬
words can easily “inherit” the same per¬
missions when they are linked to the same
group.
• The database designer does not have
to worry if Bob and Mary from Personnel,
who can perform different activities in the
database (e.g., only Mary can Delete
records), have consistent access to layouts
and fields as changes are made to the
database. The designer only has to change
the group’s access; everything else hap¬
pens automatically.
• Each password can quickly be associ¬
ated with multiple groups providing
access to information that is relevant to
several different organizations. For exam¬
ple, permissions could be set up for
groups in Operations and Marketing, all
of which could be made accessible to a
password belonging to the Finance group.
• Temporary access to layouts and
fields can be added and removed easily by
creating new groups that are linked to a
password. By removing a link to any
group, other access rights for the field are
not affected.
♦V
Fringe Benefit
The ability to see at a
glance which fields are
used on a given layout
and which layouts use a
given field is a nice by¬
product of the Pro
security design.
The Groups Finance and
Human Resources are
linked to Stock Grant
The field Stock Grant
is on the Data Entry and
Compensation Anal ysis
layouts.
Figure 6
Access Priuileges for File “personner
Groups
i PassLuords
Operations
o
•
Master
o
Finance
Bob
it
Marketing
•
Mary
Human Resources
a
Catherine
U-J
----
—
•
Frank
stock Grant is accessi ble
to the passwords Master,
Mary, and Frank
i Layouts
Fields
Data Entry
Personnel Re
Compensation An
Mailing Labels
Currently
selected field
it
Department No.
a
Name
it
Address
it
City
it
State
it
Zip
Home Phone
it
Social Security N
s
Last Review
it
Salary
r 1 _
The FileMaker Report ©1991 Elk Horn Publishing Issue 40 • Page 7
Add and Update Records at the Same Time?
By Mike & Joe
"The Answer Guys"
Note
Solutions to problems
like this one are often
combinations of
operating procedures
and database
techniques.
Q & A Motto
"If it can't be done with
FileMaker, it's not worth
doing."
^ “My problem is that there seems
to be no way to update existing
records and add new ones at the same
time when importing data from a text file.
The FileMaker file to be updated is a
product description database - Products
- with one record per product and a
unique stocknumber for each record. The
text file with changes - Updates .txt - is
downloaded from a mainframe and in¬
cludes complete records for a subset of
products.
“The update procedure should read
one new record at a time and determine
whether it is already in the product file. If
so, the FileMaker record should be up¬
dated with the info from the text file. If
not, a new record should be created. The
text file contains only records which have
changed (or were added) over a given
period of time, a number far less than in
the Products file. Downloading the com¬
plete database and replacing all records in
the FileMaker file would be too time
consuming to be practical.
“I have considered a Relookup scheme
with an intermediate file, but that doesn’t
solve the problem of the new records.
Splitting the text file into two is less con¬
venient. The whole thing should be as
user-friendly as possible since it is going
to be used by unqualified personnel.
“Is there an expert there who could
come up with a work-around? If you have
a solution on hand, don’t hesitate to let
me know about it. Eternal gratitude will
come your way. Or perhaps some Belgian
Beer?” - K. G., Belgium
.55 There are several approaches to
this difficulty. None may be per¬
fect for you - just pick the one that is least
imperfect. (The last one looks good to us.)
1. While it may not work for you, the
download of the complete product data¬
base from the mainframe will be fine for
some users faced with this situation. But
don’t replace or update records in the
existing Products file. Just clone Products
and import into it the full dataset from the
mainframe text file. Then throw away the
old file. (You may have to rename the new
version if it is being used for lookups.)
2. Try harder to take the data from the
mainframe as two text files: one with new
records and one with modifications to old
records. If the mainframe can discrimi¬
nate between these two types, it maybe
easy to get two appropriate files. Then two
simple scripts would make the import/
update operations straightforward for
inexperienced operators.
3. If the mainframe can’t distinguish
between the two types of records, perhaps
there is internal information that allows
you to do so. If so, you could import the
text file into a clone of the Products data¬
base, Find the records that are new ones,
based on internal information, import
them into the original Products. Then use
the remainder of the records to modify
information in the original file by export¬
ing to a text file and importing-with-
update into Products.
4. If in option 3 there is no indication
in the clone file of which records are new
and which are for modifying, perhaps you
can create this information for yourself.
Page 8 • Issue 40
The FileMaker Report ©1991 Elk Horn Publishing
We assume that the difference between
the two types is the presence or absence of
a matching stocknumber in the original
Products file. Then a relookup from
within the clone file can set a field to indi¬
cate which stocknumbers already exist in
Products and you can then proceed as in
option 3. Note that, in this case, instead of
a simple clone as the intermediate file,
you’ll need to create a special modified
clone of Products that has the needed
extra field and that has the proper lookups
defined. Lookup into the extra field any¬
thing at all from Products; when the
lookup fails, tell FileMaker to insert some¬
thing unique that will make it easy for you
- perhaps the word “mod” would do.
5. Once the special clone in option 4
exists, you don’t have to go back to option
3. An alternative is to do a Relookup from
within Products to modify all the existing
records. With stocknumber as the lookup
key, only those records in Products that
have a corresponding stocknumber in the
special clone will be changed. That leaves
only the records with new stocknumbers
(those not marked “mod”) to be imported
into Products. No additional export to a
text file is needed.
6. This option may be the expert work¬
around you are looking for. (Send beer to
the newsletter office.) Note that the results
are what you asked for, but the process is
different. Actually the idea is quite simple.
This approach assumes that the Up¬
dates .txt file from the mainframe con¬
tains complete records for both the stock
items to be added to the existing Products
file and the stock items to be changed. It
also assumes that you need to update
Products no more often than once a day.
This technique arose out of clues devel¬
oped in the preceeding solutions. Try this:
(a) Import all of the text file records
from the Updates .txt file as new records.
. (b) Use the Replace command to put
today’s date into all the records just im¬
ported. The imported set will be the found
set at the end of the import operation; the
Replace should be done immediately and
before doing anything else. If a suitable
date field does not already exist, add an
Import Date field to the Products file
before doing the first import.
(c) Find stocknumber duplicates and
at the same time specify that you only
want duplicates
with an Import
Date earlier
than today. (See
the figure for
the Find re¬
quest.) This will
provide you
with a found set
of only those
old records that
duplicate the
newly-entered
ones.
(d) Delete this found set. The Products
database then contains one record for
every currently-active stocknumber.
Further, there is only one record for each
stocknumber and that record has the
latest information from the mainframe.
If the Products file is used as a lookup,
the sequence of stocknumbers in the file
will not be relevant. But if for some reason
the file needs to be in stocknumber se¬
quence, just Sort Products by stocknum¬
ber, make a clone of Products and import
the sorted records into the clone.
Products
[Lagout *8
Requests:
1
[ Find ]
□ Omit
CD (ZD
CD CD
(~= Exact 1
DulHRIislSO
StockNumber
.i
i
Import Date
<9/17/91
a
The FileMaker Report ©1991 Elk Horn Publishing Issue 40 • Page 9
DBF Format Reveals Max Length of Field Data
Note
Even if you never use
the dBASE program,
this article shows how
to use Pro's DBF export
format to find the
maximum length of
data in FileMaker files.
Note
The use of fixed-length
records is a main reason
other applications have
to resort to complex
relational file tech¬
nology which isn't so
important for RIeMaker
- but that's a subject for
another article.
Page 10 • Issue 40
By S.C. Kim Hunter
Did you ever need to know what the
longest data was in a FileMaker field?
Depending on your expertise with the
ASCII code, the method discussed here
will be either very helpful or useless to
you. This article also documents the for¬
mat of DBF export files, not explained in
the FileMaker Pro manual (because you
don’t need to know the format to perform
normal data transfer operations).
The new import/export options in Pro
provide expanded capability to exchange
data among Mac, MS-DOS, and main¬
frame applications. In the MS-DOS world,
dBASE, published by Ashton-Tate, is the
primary standard for databases. Unlike
FileMaker, dBASE files must be defined
with maximum field lengths and the data
is stored in fixed-length records so search¬
ing can be faster. FileMaker stores each
piece of field data as a variable length item
so it doesn’t waste space for fields that are
not filled with data.
The dBASE format is used by other
applications, such as FoxBASE-F/Mac, in
both the Mac and MS-DOS worlds. Many
other Mac database applications (and
most of those on mainframe computers),
while not using dBASE format, also re¬
quire defining maximum field lengths. If
you wish to transfer data from FileMaker
to another database or to a mainframe,
you have to specify in the other database
definition what the maximum length of
each field is. There is no quick way to find
the length of the longest data in any given
FileMaker field so, if you guess or scroll
through records looking for the longest
data, you risk either truncating the data or
allotting more length than necessary.
Until Pro, the only way to handle this
was to create one FileMaker calculated
field for every FileMaker data field using
the Length function to calculate the length
of every piece of data in every record, then
create one summary field for every data¬
base field specifying the Maximum of each
calculated Length field. Using a grand
summary part, the summary fields would
show the maximum data length for each
field. This is tedious if the database has
many fields and time/disk consuming if
the file is large.
Using FileMaker Pro’s export function,
there is now a way to obtain maximum
data lengths by dissecting a file exported
using the DBF format. Unfortunately, this
is not an easy process to understand un¬
less you are familiar with the ASCII code
and have a text editor or utility that dis¬
plays characters that are normally invisible
on the Mac. If you have that capability,
getting maximum field lengths is a snap.
The simplest method to find maxi¬
mum field lengths is to export FileMaker
Pro data using the DBF option, then open
the exported file with the FoxBASE-f/Mac
application. The file structure can be
displayed and will show the resultant field
lengths. But you wouldn’t buy FoxBASE-t-/
Mac just to do this because FoxBASE is
expensive and pretty archaic technology
compared with FileMaker.
A less-expensive method is to view the
DBF file with an application you may
already have that can display ASCII codes
The FileMaker Report ©1991 Elk Horn Publishing
in a disk file, such as
Norton Disk Editor in
Norton Utilities published
by Symantec Corp,
QUED/M published by
Paragon Concepts, or
MacTools/FileEdit pub¬
lished by Central Point
Software. There are also
shareware applications
and DAs that will work.
Word processors like
MacWrite or Microsoft
Word can show some
invisible characters but
not all and are clumsy to
use for this purpose.
Table 1 lists the struc¬
ture of a dBASE III PLUS
file. I won’t explain each
item, just point out that
the field descriptor arrays
start at byte 32 and there
is one 32-byte descriptor
for each field. (Note: the
first byte is called 0, so
byte 32 is actually the 33rd
byte in the file, but 32
bytes counting from the
first. In computer jargon,
this is called an “offset
from the first byte”).
Table 2 lists the struc¬
ture of the field descriptor
array and shows that the
field length is the 16th
byte. So 32-H6=48 bytes
from the start of the file is
the length of the first field.
Every 32 bytes beyond
that is the length of the
next field.
Structure of dBASE (DBF) Data Files
Byte
Contents
Meaning
0
1 byte
Valid dBASE III Plus file ID (03H without a
memo (.dbt) file; 83H with a .dbt file)
1-3
3 bytes
date of last update; formatted as YYMMDD
4-7
32-bit number*
number of records in data file
8-9
16-bit number*
number of bytes in header
10-11
16-bit number*
number of bytes in record
12-14
3 bytes
reserved bytes
15-27
13 bytes
reserved for dBASE III Plus on local area network
28-31
4 bytes
reserved bytes
32-n
32 bytes each
field descriptor array
(see Table 2, field descriptor bytes)
n+1
1 byte
ODH return as the file header terminator
* 32 & 16-bit numbers are in Intel/MS-DOS format, least significant byte first
(Motorola/Mac format has least significant byte last)
Table 1
Table 2
dBASE Field Descriptor Byte Format
Byte
Contents
Meaning
0-10
11 bytes
field name in ASCII, zero filled
11
1 byte
field type in ASCII (C, N, L, D or M)
C = Character (text, up to 254 chars.)
N = Number (max 19 digits, 15 decimals)
L = Logical (.T./.F., no FileMaker equivalent)
D = Date
M = Memo (text more than 254 chars.)
12-15
32-bit number field data address (dBASE memory only, not disk file)
16
1 byte
field length
17
1 byte
field decimal count (for decimal number type)
18-19
2 bytes
reserved for dBASE on network
20
1 byte
work area ID
21-22
2 bytes
reserved for dBASE on network
23
1 byte
SET FIELDS flag
24-31
8 bytes
reserved
The FileMaker Report ©1991 Elk Horn Publishing Issue 40 • Page 11
dBASE III PLUS restricts maximum
field lengths as shown in Table 2. For
example, character fields can have up to
254 characters. Memo fields can be creat¬
ed for longer text which is stored in a
separate .DBT file. FileMaker Pro exports
only the first 210 characters of a text field
and does not automatically create a .DBT
memo field file. It is not clear why only
210 instead of 254 - perhaps there was an
older standard with that limit and Tve lost
track of it. In view of these differences,
you need to be cautious if your data items
are long.
The dBASE format spec defines nu¬
meric fields as having a field decimal
count, set to 0 for integers and up to 15
for decimal numbers. The FileMaker Pro
DBF export option outputs only a zero for
the field decimal count regardless of the
number data stored so Pro can’t be used
Table 3
Sample DBF File
Sample file was created in FileMaker Pro by defining one text
field named AARDVARK, creating two records with "123"
and "abcdefghij" entered, then exporting with the DBF
option. Below is the complete ASCII code dump, edited for
publication to insert returns at main data breaks.
[03I5AI0B][0E][02I00I00][00I41I00][0BI00]
[00I00][00]
[00100][00100100][00100100100][00100100][00]
[OOIOOKOOIOO]
[41141 ][52I44I56][41152I4B][00I00I00]
[43IOO][OOIOOIOAIOO]
[00I00][00I00I00][00]
[00I00][00I00I00][00][00][00]
[OD]
[20I31][32I33I20][20I20I20][20I20I20]
[20I61][62I63I64][65I66I67][68][69I6A]
[1A]
to find the maximum decimal count.
Exporting to a DBF file will only out¬
put data in the first row of a repeating
field. Also the field length will be based
only on the data actually output. If you
need to know the length of data in other
repeating rows, make a clone, input data
with repeating fields split into separate
records, then export a DBF file from the
clone.
Table 3 shows a sample DBF file creat¬
ed in FileMaker Pro by defining one text
field named AARDVARK, creating two
records with “123” and “abcdefghij”
entered, then exporting with the DBF
option selected. The length of the text
field appears at the 48th byte as OA which
is the ASCII code for ten and is the length
of “abcdefghij”.
Table 4 is a more detailed breakdown
of the information in this sample DBF file.
This table shows each ASCII code element
of the sample, the text or number equiva¬
lent and the meaning.
Summary
The steps to obtaining the maximum
length of values entered in fields of a
FileMaker Pro file are:
1. Export the data using the DBF option
on the file naming dialog.
2. Open the file with an application that
shows all ASCII codes.
3. Count to the 33rd byte (offset of 32) at
which the name of the first field starts and
takes 11 bytes.
4. Count 11 more bytes to reach the
length of the first field.
5. Continue counting 32 bytes to reach the
length of the next field.
Page 12 • Issue 40 The FileMaker Report ©1991 Elk Horn Publishing
Annotated Sample DBF File
Hex value
Contents
Meaning
[03]
03H
file ID, no .dbt file
[5A][0BI0E]
90/11/14
date of last update
[02I00I00I00]
2
2 records in file
[41100]
41H=65
65 bytes in header
[0B][00]
0BH=11
11 bytes in record
[00I00][00]
0
3 reserved bytes
[00I00][00I00I00][00I00I00I00][00I00I00][00]
0 13 reserved bytes
[00I00][00I00]
0
4 reserved bytes
[41141 ][52I44I56][41152I4B][00I00I00]
AARDVARK name of first field
[43]
C
field type = character
[01I00][00I00]
1
field data address (no meaning for Pro)
[OA]
0AH=10
field length = 10 characters
[00]
00H=0
field decimal count = 0 (>0 for numbers only)
[00100]
0
2 reserved bytes
[00]
0
1 reserved byte
[00100]
0
2 reserved bytes
[00]
0
1 reserved byte
[00I00][00I00I00][00][00I00]
8 reserved bytes = 0
[OD]
ODH
ODH (return) file header terminator
[20]
20H=space
record deleted marker (space = not deleted)
[31132][33I20I20][20I20I20][20I20I20]
123+ 8 spaces field data, record 1, field 1
[20]
20H=space
record deleted marker (space = not deleted)
[61I62][63I64I65][66I67I68][69I6A]
"abcdefghij" field data, record 2, field 1
[lA]
1AH
MS-DOS end of file mark (Control Z)
Table 4
When printing equations here in the journal, we use to mean quotes with
nothing in between, as quotes with a soft space, and as quotes with
a hard space. This is done so it is easier to see how many spaces are needed
and what kinds. When entering one of our equations you should make the
appropriate substitutions.
The FileMaker Report ©1991 Elk Horn Publishing Issue 40 • Page 13
FileMaker Quick Tips
By Joe Kroeger
Note
As discussed in the last
issue, Claris has a new
toll-free automated
telephone service for
answering FileMaker
questions. It is open 24
hours a day. The number
is 800-735-7393.
Claris FileMaker Fax Service
In addition to the new Claris toll-free
automated answer line described in issue
#39, Claris now has a new fax support
service. You can use the Fax system to
receive technical briefs that discuss specif¬
ic FileMaker problems. The voice number
to access the fax service is 800-800-8954.
When you call from a touch-tone
phone, the first step is to ask for an index
of the items available. When you enter
your fax number, the index is faxed to
you. Then with the numbers of the items
of interest, you make another call and
receive up to four of the documents per
call. I think Claris intends to unload some
activity from the regular support lines
while at the same time distributing useful
information, thus making it nicer for us
all. Let us know how you like the service.
i'tm i Urulo
i: uf
:»:h
s: oi)i|
:*:c;
Paste
seu
i:
Select Rll
3gR
New Record
9eN
Duplicate Record
seD
Delete Record
SSE
Delete Found Set
_ □
Paste Special
Replace...
Relookup
Check Spelling
§§=
View Index RIP
In FileMaker Pro Claris has re¬
named the View Index command
and hidden it away. It used to be
right in the Edit menu in FileMaker
II. Now in Pro it is buried down in
the Paste Special menu as one of the
hierarchical options. This is mislead¬
ing. I use the View Index command
... er, that is, the Paste Special From
From Indeu...
981
Frorn Iasi
Current Date
36-
Current Time
36;
Date Symbol
Tima Symbol
Paqa Nombor
Bacorb Numbar
Index command a lot and I don’t always
paste from it. To me it is a viewing com¬
mand not a pasting command; but some¬
one at Claris thinks of it the other way
around. Indeed, if you invoke the com¬
mand it does not execute a paste at all, but
simply brings up the index viewing dialog.
Fortunately, they did not change the
keyboard command assignment; com-
mand-i still brings up the index window.
And you can still paste from it with a
double-click when desired. If you remem¬
ber command-i you’ll never have to struggle
to locate the lost View Index command.
Note that the same thing is true about
the old Paste Ditto command. But at least
it is a real paste operation. The old key¬
board shortcut still works for it as well.
Fixing Date Data
In a date field, the difference between
4/12/91 and 4/12/1991 is transparent to
FileMaker; it treats both as the same value
and calculates properly with either format
as input. The results of date calculations
are always provided by FileMaker in the
short format.
You maybe doing some date manipu¬
lations that rely on the assumption that
the short format is always present. If you
then import some new records that con¬
tain a mixture of the two date styles, it will
be important to convert the long format
into the short version.
I recently ran into such a problem. I
figured I could be clever with a few text
equations and pull apart the dates, trun¬
cate the year and then re-assemble them
the way I wanted. But as I got into it, the
problem became more difficult. It
wouldn’t work to detect the two formats
by counting characters, since the counts
Page 14 • Issue 40 The FileMaker Report ©1991 Elk Horn Publishing
can overlap; 11/11/91 is eight characters
long but so is 1/1/1991.1 couldn’t just
count characters to locate positions since
the number of characters in a date is not
constant; 1/1/91 is six characters long and
11/11/1991 is ten characters. Further,
some dates may have leading zeroes and
some may not; 01/1/91 is a fine date and is
seven characters long.
I was thinking about ways to avoid
counting altogether, perhaps by detecting
the second slash using the Position func¬
tion, when a much simpler approach
bounced off my forehead. Since FileMaker
already knows about date data, I should be
able to let it worry about the details. I
could use the Day, Month and Year func¬
tions to extract the components of the
given date and then re-assemble them.
Something like this might work (I have
not tried it):
NewDate = {date result}
Month (OldDate) & Day (OldDate) &
Year (OldDate)
Note that this approach is independent of
the actual number of digits residing in the
OldDate field.
But wait! If I am just relying on File¬
Maker’s date calculation results to make
91 out of 1991, then I might as well do it
even more simply:
NewDate = OldDate {date result}
It works! Simplicity wins again.
The procedure is: (a) create the equa¬
tion above; (b) change NewDate from a
calculation to a date field (FileMaker
retains the calculation result in the field);
(c) delete the OldDate field; (d) change
the name of NewDate if desired and place
it where OldDate used to be on all lay¬
outs.
O There may not be enough disk space to
complete this operation. FileMaker mill
not be able to open the file if this
operation fails. Proceed anyiuay?
[ OK ) |^_^ancelj
Space for Save a Copy As ...
When you execute the Save a Copy As
command there are three options: save as
a clone, save a full copy and save a com¬
pressed full copy. Since the clone is quite
small there is hardly ever a problem with
space. But the other two options can
generate large files if your original is large.
FileMaker checks the remaining hard
disk capacity when you request a full copy
and issues a warning (see illustration
above) if it thinks there may not be
enough space. But as you make copies of
larger and larger original files, FileMaker
seems to get more and more conservative
in its judgements about how much space
may be required. This is especially the case
when the destination disk is different than
the source disk.
I was trying to compress a 24 MByte
file to another disk that had more than 39
MBytes of space remaining. FileMaker
issued the warning even though I knew, at
least, that the compression would make
the resulting file smaller than 24 MBytes.
(Files larger than about 9 MBytes seem to
stimulate this extra concern on the part of
FileMaker.) I elected to proceed and had
no further problems.
If this is happening to you, I suggest
that you (a) make all direct file copies
from the Finder if you can; (b) be aware of
how much space is really on your hard
disk and make your own judgement about
the safety of the requested operation.
Tip
FileMaker Pro can only
Omit up to 32,767
records at once. While
the need to do so may
not come up very often,
when necessary you'll
have to Omit in two
stages.
The FileMaker Report ©1991 Elk Horn Publishing
Issue 40 • Page 15
The FileMaker Report
A Joumcd for FileMaker™ Users
Published by
Elk Horn Publishing
PO Box 1300
Freedom, CA 95019 USA
Voice Phone: 408-761-5466. Fax Phone: 408-761-5468
America On Line address: ElkHornPub
Publisher and Editor: Joe Kroeger
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 and include postage.
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 I 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 at variable intervals ranging from about three to
abovtt eight weeks.
Unless otherwise noted, the entire contents of The File¬
Maker Report are copyrighted by Elk Horn Publishing
and may not be reproduced in any form without explicit
written permission. All rights reserved.
Other publications fi'om Elk Horn Publishing:
Elk Horn Library Catalog
FileMaker Annex Newsletter
FileMaker Application Notes
R7SC Management Newsletter
Elk Horn Publishing is an independent organization not
affiliated with Claris Corporation or with Apple Com¬
puter, Inc. Various trademarked and copyrighted names
and symbols are used in this newsletter. Instead of add¬
ing a trademark symbol at every occurrence, the publish¬
er hereby states that it is usii^ such names only in an
editorial fashion and for the benefit of the trademark or
copyright owner with no intention of infi’ingement.
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 First Release Date
FileMaker 1.0
FileMaker Plus
FileMaker 4
FileMaker II
FileMaker Pro 1.0 v1
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 (see below for mail stops) is:
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 Monday through
Thursday and 6:00 am to 2:00 pm on Friday, Pacific
Time. The Claris recorded help line is 800-735-7393. The
Claris Support Fax help line is 800-800-8954.
Elk Horn Publisliin^
PO Box 1300, Freedom, CA 95019 USA
