Skip to main content

Full text of "The FileMaker Report 040"

See other formats


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