Skip to main content

Full text of "The FileMaker Report 032"

See other formats


The 

FileMaker 

Report 


A Newsletter For FileMaker™ Users 


Issue 32 

October 18,1990 

© 1990 Elk Horn Publishing All Rights Reserved 

Contents 


» t f 


^ t 






Page 


Equation du Jour: Calculate Volume Discounts 3 

FileMaker Q. & A: Calculating Summary Fields 5 
Managing Checking Accounts with FileMaker 6 

New FileMaker User Services 13 

FileMaker Quick Tips 14 


I 


Elk Horn Publishing 

PO Box 1300 ■ Freedom ■ California 95019 ■ USA 










Claris Corporation is the publisher of the FileMaker II and FileMaker Pro programs. Some Claris 
mail addresses are: 


Claris Corporation 
Software Registration 
M/S C-71 
PC Box 58168 
Santa Clara, CA 95052 


Claris Corporation 
Technical Support 
M/S C-12 
PC Box 58168 
Santa Clara, CA 95052 


Claris Corporation 
Customer Relations 
M/S C-11 
PO Box 58168 
Santa Clara, CA 95052 


The Claris Technical Support phone number is 408-727-9054. The hours for Tech Support 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 Customer Relations phone number is 408-727-8227. The Customer Relations hours are 
8:00 am to 5:00 pm Monday through Thursday and 8:00 am to 2:00 pm on Friday, Pacific time. 


The FileMaker Report is published by 

Elk Horn Publishing 
PO Box 1300 

Freedom, California 95019 USA 
Voice: 408 • 761 • 5466 
FAX: 408 • 761 • 5468 

Elk Horn Publishing is an independent business not affiliated with Claris Corporation or with Apple 
Computer, Inc. The Library of Congress ISSN number for The FileMaker Report \s 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 at the bottom of inside pages. The dote 
on the cover is the last date that editorial updates are made before going to the printer. The 
FileMaker Report is published ten times each year - every four to seven weeks. Subscriptions are 
based on a number of issues, not on a length of time. 

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

FileMaker is a registered trademark of Claris Corporation. The FileMaker logo and name are 
copyrighted by Claris Corp. Various additional trademarked names and symbols are used in this 
newsletter. Instead of adding a trademark symbol at every occurance of a trademarked 
name, we hereby state that we are using such names only in an editorial fashion and for the 
benefit of the trademark owner with no intention of trademark infringement. 


The latest version of FileMaker is 

FileMaker Pro, 1.0 

vyith o release date of 

October 12, 1990. 







Calculating Volume Discounts 


“Equation du Jour” 
By 

Joe Kroeger 


□ FileMaker includes many features for making an order-entiy ap¬ 
plication quite efficient. Once an Orders database has been created, 
lookups and calculations fill-in many of the order-form blanks. The 
customer name and address are looked up from a Customer file when 
a customer code is entered; the product description is looked up from 
a Products file when a part number is entered; the unit price can also 
be looked up from a Price file when the part number is entered. And so 
forth. In one example an Order file had 28 fields that needed to contain 
information in order to complete an order, yet only eight had to be 
manually entered to fill out the those remaining. 

But what if your products offer price discounts as the number of 
units purchased increases? Must you enter the discounted prices 
manually to over-ride the looked-up unit price? Of course not; File¬ 
Maker to the rescue, as always. 

For simple situations, you can create the price/volume discount 
table in an equation. Let the Base Price be looked-up from the Prod¬ 
ucts or Price files as a function of the part number, then apply a cal¬ 
culated discount that depends on the number ordered. Example: 


Unit Price = 

Base Price * (if(Quan < 25,1,if(Quan < 50, .94,if(Quan < 100, .90, .88)))) 

Then the calculated Unit Price (instead of the Base Price) is 
multiplied by Quan to arrive at the extended price for the product 
being ordered. 

As words, this equation says “The 
Unit Price is equal to the Base Price 
times 1 if Quan is less than 25, other¬ 
wise times .94 if Quan is less than 50, 
otherwise times .90 if Quan is less than 
100, otherwise times .88.” For ex¬ 
ample, if customer is ordering 75 Blue 
Special Washers and their base price is 
$1.29 each, then the unit price will 
turn out to be $1.16 (1.29 times .9). 

if Function commas in the if statements teU FileMaker the location of 

Parameters ^^ch parameter needed to make the if work. The general if function 
looks like if(Test,Resultl,Result2). The first statement following the 
parenthesis describes a test that will yield a 1 or a 0 response. In our 
case “Quan < 25” is either true (1) for the product being ordered or false 
(0). If it is a 1, the if function provides the result that follows the first 
comma; if a 0, the result after the second comma is provided. 

In the equation above, we have actually used three if statements 


If the Unit Price is not needed on the order entry form 
or the customer invoice/receipt, you can perform the 
multiplication right in the discount calculation, thus: 

Extended Price = 

(Base Price * (if(Quan < 25,1,if(Quan < 50, .94, 
if(Quan < 100, .90, .88))))) * Quan 


Page 3 Issue 32 


The FileMaker Report 


© 1990 Elk Horn Publishing 







Volume Discounts 

(continued) 


Discounts that 
Vary by Product 


Indexed 

Lookup 


Discounts for 
Total Order 
Volume 


“Equation du Jour” will 
become a regular column 
in The FileMaker Report If 
you have a calculation 
you're trying to conquer 
or a neat equation you 
can contribute, please let 
us know. 


Page 4 Issue 32 


that are nested into each other. That is, “Result2” is generated by an if 
function that includes additional results. 

When you use FileMaker Pro you can construct a volume/price 
table right in the calculation with about as many steps as you need. 
Just keep adding nested if functions. With FileMaker II there is a 
limitation of 250 characters total in one equation. 

One limitation of this approach is that the discount schedule 
needs to be the same for all products if repeating fields are being used. 
This is because the calculation applies universally to all the part 
numbers entered into the set of repeating fields. If discounts are the 
same, the calculation above is a neat and simple approach. If not, 
there are alternatives: 

(A) Create a few groups of repeating fields with different discount 
schedules, then enter certain products in certain groups. This is awk¬ 
ward at best and prone to error as well. 

(B) Create a series of product entry fields each dedicated to a specific 
product. This may have side benefits, but is practical for only a small 
number of product types. 

(C) Don’t use a calculation for the discount at all - create a lookup 
table Instead that can have as many price point entries as desired and 
different prices and discounts for different products. Then calculate a 
lookup key that combines the part number and quantity information 
to select the correct price from the lookup table. Example key for 
quantities less than 1000: 

PriceKey = (Part Number * 1000) + Quan 

This equation calculates a unique number for the price table that 
accesses via a lookup a unique part/quantity price. It does take a bit of 
work to construct the lookup table, but it is also easy to modify later as 
prices and products change. 

But what if your volume discount is based not individually on the 
quantity of each item, but on the total quantity of all products ordered? 
Once again, FileMaker allows us to automate a solution. And it works 
for both cases: a calculated discount or a lookup table. Assuming that 
you are using repeating fields for entering products ordered, simply 
add up the quantities being ordered using the repeating field Sum 
function: 

Order Quan = Sum (Quan) 

Then Order Quan is substituted for Quan in the Item Price or the 
PriceKey equations above. You’ll probably need to expand the lookup 
table when using this approach since the quantity parameter may vary 
over a wider range. 


FMR 


The FileMaker Report ©i 990 EiKHom publishing 





FileMaker Q & A: Calculating with Summary Fields 


By 

Joe & Mike 
The Answer Guys 


Remember our motto: 
“If It can’t be done 
with FileMaker, it’s 
not worth doing!” 


□ ^ I understand that FileMaker Pro allows me to include sum¬ 

mary data in calculations. Is it easy? How do I do it? 

A Yes, Pro can do summary data calculations, which will be in¬ 
valuable for tasks like comparing year to year sales. But the process is 
not as straightforward as being able to include summary fields in con¬ 
ventional equations. Pro allows you to bring summary field data into 
record-based calculation equations, which work pretty much as al¬ 
ways, using a simple conversion. 

In Filemaker II summary fields require that three things be speci¬ 
fied: the name of the field to be summarized, the kind of summary to be 
performed, and, when placed in a subsummary part, the name of the 
field used to sort the records for the subsummary. The third parameter 
(called the break field in Pro), is not defined in the summary field 
definition, but in the subsummary part as it is defined in layout mode. 
This allows the same summary field to be used in different subsum¬ 
mary parts to generate information about records grouped in many 
different ways. Example: you could summarize sales by calendar quar¬ 
ter, by state, and by salesperson, yet use the same TotSales summary 
field in each case. 


Break Field 


Conversion Equation 


Browse SummarlesI 
Export SummarlesI 
Graph SummarlesI 


In Pro Claris carries the break field concept forward as they make 
summaries calculable. To bring the data from a summary field into a 
calculation, use the function called, logically though confusingly. Sum¬ 
mary. The new Summary function requires two specifications, sum¬ 
mary field and break field. Summary field is the field you originally 
defined as a summary and that you will likely continue to use in 
summary parts and other conventional places. Break field is the field 
formerly selected with the subsummaty layout part to specify the 
group of records being summarized. 

In a simple conversion-only case it looks like: 

TotSalesState = Summary (TotSales, State) 

The Summary function can be a parameter in another equation 
that manipulates the resulting summary value. 

The first thing to note about this new function Is that it has to be 
defined for each group to which it applies (three equations in the 
example above instead of one definition). Also, the summary field val¬ 
ues are stored in every record in the group, taking up additional 
memory. However, it also is now possible to see summary values for a 
group as you view individual records. And with summsiry values now 
in real fields, they can be exported and used for lookups. Bar graphs in 
FileMaker can now be more useful since summary data can be used to 
build the bars. 


We tmst we have made ourselves obscure. 


FMR 


Page 5 Issue 32 


The FileMaker Report 


© 1990 Elk Horn Publishing 






Managing Checking Accounts with FileMaker 


□ 


By S. C. Kim Hunter 
Acropolis Software 


Why Not With 
FileMaker? 


Rather than buy special software products, I always try to solve 
problems with FileMaker. Managing my checking account is a good 
case in point. One of the first things many new users try to do with a 
computer is balance their checkbook. There are several dedicated 
checking account applications for the Macintosh - some are awkward, 
some are expensive and some are frustrating. As long as I was using 
FileMaker for a bunch of other tasks, I figured I would tiy to use it for 
my checkbook as well and save the extra time and expense needed to 
buy and learn a new application. Now FileMaker handles my p)ersonal 
and business accounts nicely so that I don’t need any other accounting 
or tax preparation software. At the end of the tax year, I simply print 
out sub-account totals and I’m ready. 


I’ve never used the “1040” type of tax preparation software which 
prints the IRS forms directly. It has always seemed to me that collect¬ 
ing the information was the lengthy part and I would spend more time 
learning to use the tax software than I did filling in the forms by hand. 
Before I had a computer (you remember the dark ages), the IRS had 
never bounced my hand-lyped return for math errors. For a few years 
I used a spreadsheet to simulate the tax forms and do the math. Every 
year I used a spreadsheet, I made some dumb mistake, the return got 
bounced and I paid a penalty. I learned my lesson. 


Makes Tax 
Summaries 
as Well 


So I have settled on using FileMaker to record my checking and 
cash transactions, then I take the printout of the results to my tax 
preparer who does the proper form incantations. Many advances have 
been made in tax software which you may want to investigate, so don’t 
take my comments as a rejection of currently available tax software. 
But my FileMaker files work well for me. 


Indeed, my checking account file has worked so well that I submit¬ 
ted it to the Elk Horn Library as a template named Make..,Checks^'^. 
The basic fields are described in this article, but the template has more 
in it than described here, including printing check amounts as words 
using formulas previously published in The FileMaker Report (issues 
17 & 24). This article describes how I use the template. 


Field Definitions □ 


Page 6 Issue 32 


As with any database, the first thing to do is define the fields. Here 


are the ones I 

use: 


Field name 

Field type 

Entry Options 

Id 

Text 


Cleared 

Number 

Auto-enter “8" 

(8 = month, change every month) 

Date 

Date 


Payee 

Text 


Memo 

Text 

Table continued next page 

The FileMaker Report 

© 1990 Elk Horn Publishing 





Entry Options 


Check Accounts 

(continued) 


Note 1: The Account field 
has a long list of account 
names that are entered in 
the Value List. You'll have 
your own set. In the 
Library template the 
accounts are oriented 
towards IRS Schedule C 
categories. 

Note 2: In the Make... 
Checks template, BankNet 
is named Bank Balance 
and BankBal is named 
Total Bank Balance. 

These fields were renamed 
for this article to clarify 
their usage and similarity 
to other fields. Also in 
Make...Checks, BankBal is 
a running total but 1 never 
used it that way. 


Field Functions □ 


Current Month 
for Cleared 


Field name 

(continued) 

Main Account 


Account 

Check 

Deposit 

CheckNet 

BankNet^ 

Expenses 

Receipts 

BonkBoi^ 

CheckbookBoi 


Field type 

Text Auto-enter: "Checking" 

Required voiue 
Voiue List: BusiChk 

Checking 
Cosh 

Text Voiue List' 

Number 

Number 

Caicuiation(Num) = Deposit - Check 

Caicuiation(Num) = if(Cieared>0,l ,0) * CheckNet 

Summary = Totoi of Check 

Summary = Totoi of Deposit 

Summary = Totoi of BankNet 

Summary = Totoi of CheckNet(running) 


One columnar layout set to View as list is sufficient to do the 
transaction entry and monthly balancing. The fields in the Body of this 
layout are: Id, Cleared, Date, Payee, Memo, Main Account, Account, 
Check and Deposit. The CheckNet and BankNet fields never need to 
be seen; they are used just for calculations. Below the Body I put a 
Sub-summary part based on a sort by the Account field and, below 
that, a Grand Summary part. In the Sub-summary and the Grand 
Summary parts I put the four summary fields Expenses, Receipts, 
BankBal and CheckbookBal. The number, calculation and summary 
fields need to be set with the Format menu to Align right and with 
Format Number to show two decimal places. 


In the Id field, I enter the check number for checks. For deposits I 
enter “dep”. For cash, 1 enter “cash”. TTie auto-enter option can be used 
to enter sequential check numbers in the Id field but I don’t use auto¬ 
enter because the check numbers are in my check book and because I 
enter many transactions that are not checks: deposits, bank charges 
and cash. With the auto-increment characteristic of the serial num¬ 
bering that FileMaker does, every time you enter a non-check the 
numbering is off and has to be reset in the define entry options dialog. 
I find it easier to enter the numbers by hand. 

The Cleared field is set to auto-enter the number of the month in 
which the bank has cleared the check: “8” for August for example. 
When 1 first created the file, 1 set auto-enter “n" for not cleared, but 1 
found that I didn’t put the checks into the computer until the bank 
statement came so I spent a lot of time changing from “n” to the current 
month for all the checks that were already cleared. By auto-entering 
the current month, 1 only have to change the few uncleared checks to 
“n” as I enter them. 


For checks that have cleared, I could put just “y” or “yes" in the 
Cleared field instead of the current month. TTie reason for entering the 
current month is that a Find can be done for any given month; then 
the Expenses and Receipts summary fields in the grand summary 


Page 7 Issue 32 


The FileMaker Report 


© 1990 Elk Horn Publishing 






Check Accounts 

(continued) 


Optional 

Payee 

Pop-Up 


Memos for 
Printing and 
not Printing 


Posting? 


Why Two 
Account Fields? 


show tiiat month’s totals for checks and deposits which I can compare 
with the same totals printed by the bank on my statement. I always 
make sure I have the monthly totals for the cleeired transactions cor¬ 
rect before I go on to reconcile the checkbook balance with the bank 
balance. If either the E 2 q)enses or Receipts don’t match the bank, it is 
fairly easy to find my data entry error - or the occasional bank error. 

In the Date field I enter the date the check was written or the 
deposit was made. I don’t use these dates to find the cleared transac¬ 
tions in a given month because checks don’t always clear in the month 
that they were written. I prefer to use the Cleared field with the month 
number for this purpose. 

The Payee and Memo fields don’t need much explanation; I don’t 
do anything with them other than fill them in. I do try to keep the 
Payee field identical for recurring monthly payments so that I can 
Find all the transactions for a given payee. I use either Find or View 
Index commands to make sure I’m keeping the payee names the same. 
I could output the payee data to a text file, then copy it and paste it 
back into a pop-up value list, and some users will want to do so. 

The Memo field is for any notation I feel necessary. Memos are 
usually printed on the check as a notice to the payee - there may be an 
account number or an indication of what was bought, and so forth. 
You can elect to not include the memo on the check, allowing private 
comments to be included ("This guy is a Jerk!"). Or you might want to 
create a Memo2 field to use for such purposes. 

I have two fields that specify accounts: Main Account and Ac¬ 
count. You might prefer to name them Account and Sub-accoimt. In 
the Main Accotmt field I defined a value list that includes each check¬ 
ing account I have (one personal and one business) and a “Cash” 
account. All cash I keep track of is for business expenses. I’m just 
using this file for tax purposes but you could also include a savings 
account, and I suppose you could also include assets and liabilities to 
make it a more complete system. 

People complain that FileMaker won’t “post” to the accounts, but 
I can’t see any difference between just finding one account and sorting 
the records for totals, as opposed to the accounting scheme of copying 
records to an account ledger (called posting the accounts). Any com¬ 
ments from you Accountants out there? 

I use the Account field like a Chart of Accounts list which includes 
all the various categories the IRS uses on Schedule C for business 
expenses and also the other IRS categories for itemized deductions. I 
also throw in a few catch-all categories like “groceries" and “personal” 
for things that the IRS doesn’t care about. The template contains the 
list I've compiled. 

With this two-field account system, a Find All, then a Sort by 
Accotmt gives me totals for all expenses in each account category. But 
when I’m balancing a checkbook, I do a Find for the selected Main 
Account checkbook so the totals all apply to just that account. Some 


Page 8 Issue 32 


The FileMaker Report 


© 1990 Elk Horn Publishing 






Check Accounts 

(continued) 


Two Calculation 
Fields 


Four Summary 
Fields 


Page 9 Issue 32 


users will prefer to have separate files for each account. That’s fine. 
But by having everything in one FileMaker file, 1 can sort by account at 
year end and get totals of, for example, the “office supplies” account for 
both cash and checking main accounts. (Of course, if you decide to 
keep separate files, you could always merge them at year end by 
making a copy then inputting from all the other flies.) Keeping all main 
accounts in one file has one disadvantage: I frequently scratch my 
head over an out-of-balance account only to find that I forgot to do a 
Find for that account. 

In the Check and Deposit fields I enter the doUar amounts. I 
could use positive numbers for deposits and negative numbers for 
checks in which case only one field would be necessary. However, 
accounting is traditionally based on technology developed at the time 
of the Romans. Negative numbers hadn’t been invented then, so nega¬ 
tive numbers are not often used in accounting math to this day. 

There are only two calculation fields: 

CheckNet = (Deposit - Check) {numeric} 

BankNet = if(Cleared > 0,1,0) * CheckNet {numeric} 

Recall that “n" is entered in the Cleared field for transactions not 
cleared. Cleared is a number field. When number fields are included in 
a formula, all letters typed in the field are evaluated as zero, so “n” 
appears as a zero in the Cleared > 0 expression. Since Cleared is not 
greater than zero for an “n” entry, the function result is the value 
after the second comma: 0 (zero). When a month of 1 to 12 is entered. 
Cleared evaluates as greater than zero. Therefore, the result of the 
i«..) function is the value after the first comma: 1. The if(„) result (0 or 
1) is multiplied by CheckNet. The final result is that the BankNet field 
is either zero if the transaction isn’t cleared, or is equal to CheckNet if 
it is cleared. 


I use four summary fields: 

Expenses Summary = Total of Check 

Receipts Summary = Total of Deposit 

BankBal Summary = Total of BankNet 

CheckbookBal Summary = Total of CheckNet(running) 

The Expenses and Receipts summary fields are used for two 
purposes: 


(1) For bank statement reconciliation using copies of the Expenses 
and Receipts summary fields in a Grand summary part. After finding 
a given month in the Cleared field. Expenses shows the total of the 
checks for that month to compare with the bank statement total for 
debits. The Receipts summary shows the total of the deposits to 
compare with the bank statement total for credits. 

2) For year-end tax category totals using copies of Expenses cind 
Receipts in a Sub-summary part based on the Account field. After 
finding the appropriate Main Account or aU accounts, then sorting on 


The FileMaker Report ©i 990 Eik Horn Publishing 








Check Accounts 

(continued) 


Balancing 


Getting Started 


Special Cleared 
Designation 


The 

Clever Spouse 
Problem 


Page 10 Issue 32 


the Account field, Expenses and Receipts will show the totals for 
each account category. 

The BankBal summary is for compailsion with the bank state¬ 
ment ending balance. I put a copy of this field in the Grand summary; 
I don’t use it for a running total. Bank statements rarely have a trans- 
action-by-transaction running total. If there are no errors in entry, 
finding or sorting, BankBal will equal the bank statement ending 
balance. 

The CheckbookBal summary field is a running balance I compare 
directly with my checkbook. A copy of this field in the Body can be 
viewed check-by-check and compared with my checkbook balance. If 
the layout is a View-As-List layout, this field will be greyed in Browse 
mode but can be seen in a Preview. 

To use the checkbook file I suggest that you begin at some point in 
time by creating the first record as a deposit equal to the current 
checkbook balance. In the Payee field enter “Balance forward”. You 
should start at a time when you have just completed manual balancing 
and are confident that you agree with the bank. In Cleared you can 
enter 0.5 which makes it a cleared transaction (not “n” and not zero), 
but one that won’t be found as a monthly bank transaction (months 1 
through 12). Yet. when sorting by Cleared in order to group by months, 
this opening transaction will be listed in a separate grouping at the 
beginning of the report. 

This way of handling the beginning balance may seem a bit strange 
but it points out a basic difference between general data managment 
software, like FileMaker, and specialised checking account software in 
which beginning balances are treated separately from the transac¬ 
tions. When you create a new account you have to enter the beginning 
account balance, usually in a separate “create account” function. Our 
beginning record takes care of this. 

Now I have a slightly annoying subject to discuss. For my personal 
checkbook, over the years my spouse has corrected any errors that 
were in the bank’s favor but left minor ones in our favor uncorrected 
because so there is always a pad to avoid bouncing checks. It is re¬ 
warding to have the bank’s balance slowly gain in value over the 
checkbook. Who am I to thwart this wisdom? 

Rather than cancel that pad out when I started using the File¬ 
Maker file, I decided to keep it in - but I had to figure out how to 
account for it. After entering the beginning balance as a deposit, I 
created two new records, a deposit and a check, both for the amount of 
the error. I entered 0.5 in Cleared for the deposit and “n” (not cleared) 
for the check. In the calculation of CheckbookBal summary, this pad 
cancels out so the checkbook balance stays the same as the balance 
forward. But the BankBal summary field shows a total that agrees 
with the bank and is greater than the CheckbookBal field by the 
amount of the pad. Amazingly, it works perfectly and doesn’t confuse 
the balancing. Perhaps you don’t have this problem but at least it is 


The FileMaker Report ©1990 Elk Horn Publishing 






good to know that FileMaker easily takes care of our foibles. 


Check Accounts 

(continued) 


Entering Transactions 


□ I might enter the checks and deposits every day if I were using the 
FileMaker file as an actual checkbook log. You may also do so if you are 
printing checks from the file, which I’m not talking about in this 
article. I prefer to continue to use the normal paper checkbook log 
because it is portable and goes with me to the point of purchase so that 
I am therefore less likely to miss entries. There’s also the backup prob¬ 
lem. No matter how often you back up your disk, you need a paper log 
for safety. 


Each check gets assigned to an account category. It is best to not 
skip this detail. If necessary, create a “MisceUaneous" category that 
you can return to later for better assignments. I made “account" a 
required field. 


I enter the transactions into the FileMaker file in exactly the same 
order as they are in the checkbook so the running total CheckbookBal 
Running match the balance in the paper log. Every few checks, I press 

Balance the enter key to bring the summary fields up-to-date and to compare 
the CheckbookBal field with the balance in the paper log. If I find it 
doesn’t agree, I use Preview to look at the running totals so I can 
quickly track back to the point where the CheckbookBal field stops 
agreeing with the checkbook balance: there is where the mistake was 
made either in the checkbook or the file. 


In the entry process, I am not yet trying to reconcile with the bank. 
I just want to get the file entries to agree with the checkbook paper log. 

Split Transactions □ If a check or deposit applies to more than one account, the typical 

checking account program will allow entry of the amounts for each 
account and will automatically show the total. In my scheme, I just 
create two or more records, one for each account and manually check 
the total with a calculator. This is awkward in a file that is used to print 
checks because you don’t have one record for the whole amount from 
which to print the check. 

Repeating fields can be used to enter the separate accounts and 
can be totaled for the check amount. But the account amounts could 
not be separately extracted for account summary totals: each account 
amount must also be in a separate record. 

Add Transactions To make the accounts balance correctly takes four records for a 

for Splitting check that is split between two accounts and two additional records for 

each additional account. I enter the entire amount in a record with 
“split” entered in the Account field. This record is used to print a check 
if necessary. Then I create two records for the two accounts with the 
appropriate amounts in the Checks field. These are not used to print 
checks. Last, I create one record with “split” in Account and the total 
entered as a negative amount, canceling out the check printing record 
for balancing totals. This record is not used to print a check. 

To do this more simply, FileMaker would need a pop-down repeat¬ 
ing field with each repeating row able to be sorted like separate records 
independent of the other rows. This is a good long-range wish list item. 


1 r 


Page 11 Issue 32 


The FileMaker Report 


© 1990 Elk Horn Publishing 










Check Accounts 

(continued) 


Reconciling with 
the Bank 


Differences with 
the Bank 


Error Hondiing 


Reversing Entries 


Page 12 Issue 32 


In addition to the checking account management, I also use this 
file for cash transactions. When entering cash transactions, I make 
sure the Cleared field contains “n” so they are never cleared. I never 
have to balance the cash account. You might want to if you have an 
expense account which has to be reconciled Avith a cash advance. 

When I receive the bank statement I check off the cleared transac¬ 
tions in the paper log and circle any transactions that are on the 
statement but not in the log — those I forgot to enter (tch, tch). As 
mentioned earlier, the Cleared field is set to auto-enter the month of 
the statement, so now I go back and mark the Cleared field “n" for 
checks and deposits not cleared. Next I enter the missed transactions 
into both the file and the paper log. 

Now I start reconciling with the bank. The first step is to do a Find 
for the current Main Account and for Cleared records from the cur¬ 
rent statement month. I look at the Grand Summary at the end. The 
Expenses field should show the same total as the bank shows for 
checks (debits). Likewise the Receipts field should show the same 
total as the bank shows for deposits (credits). 

In addition, depending on how you handle account splits, the 
number of transactions shown by the bank can be the same as the 
number of records shown as found by FileMaker. If there are differ¬ 
ences in the number of transactions, I check to make sure they are aU 
entered in the file. I subtract the summary value shown by FileMaker 
from the bank total. The difference may be exactly equal to one of my 
transactions which I may have forgotten to enter; or I may have marked 
something cleared when it wasn’t, or vice versa; or the handwritten 
amount may not be what the bank has stamped with its magnetic ink 
at the bottom of the check - you may have to argue with the bank 
about that one. I identify any errors but I don’t modify records to 
correct them yet - see next paragraph. 

Now I come to an important problem: errors. If you find an error in 
your checkbook, relative to the bank, half way through the month, do 
you go back and correct it where it was made, then erase all the totals 
in the paper log and correct them? How have you handled this before 
the computer came on the scene? If you are erasing, you are violating 
one of the old fundamental rules of accounting: errors are never cor¬ 
rected, they are “reversed”. 

To reverse an entry, I don’t change the original transaction. In¬ 
stead I create a new entry which cancels out the original erroneous 
one, then create another entry for the correct amount. Using this 
method, my checkbook paper log never has to be erased and I don’t 
have to go back to an old record in the file to change it. Adhering to this 
rule is hard to do when using a computer: it is so easy to just click on 
that bad old number and fix it because aU the mnning totals are 
automatically corrected. I make an exception to this anti-correction 
mle during original data entry as I mentioned above: I correct any 
mistakes I make in the file entries compared to my paper log since I 
want the computer file to exactly match the paper log. 


The FileMaker Report ©1990 Elk Horn Publishing 





Now assuming I finally have the transactions verified for the cur- 
CIlGCk Accounts month, I do a Refind and clear the month number from Cleared 

field, leaving only the current Main Account for the find so I can 
(continued) reconcile the running balance, BankBal. If the previous month was in 

.— ~ balance and I have correctly entered all the current month’s transac¬ 
tions, the BankBal field in the Grand Summary at the end should 
equal the ending balance on the bank statement. If there is a differ¬ 
ence, I use Preview to examine the running balance and compare it 
with the paper log. I look for the point where the running balance 
ceases to agree with the paper log; that is most likely the source of the 
error. If necessaiy I go back to finding Just the current month or even 
a prior month to recheck that reconciliation. 

Year-End Reports □ When tax time rolls around, all I have to do is Find All then Sort 

by Account and print a report which shows the total for each account. 
I created a layout which has only the Sub-summary and Grand Sum¬ 
mary parts, no Body. This layout shows each account total which 
usually is all my tax preparer needs. But I always print out all the 
transactions because invariably there is a question regarding the size 
of one or more of the account totals. And, of course, I always have the 
receipts to back up any expense I claim as a tax deduction. 

Some of this may sound more difficult than it really is. But the 
process of writing checks and reconciliation actually takes very little 
getting used to and is quite fast and efficient. 


FMR 


New FileMaker User Services - 

Watertechnics now provides two new interest¬ 
ing services for Fllemaker users and consultants. 
The first is a purchased telephone support line. 
Just call 408-761-3987, arrange to buy some time, 
and ask your questions. 

The second service is custom Filemaker tem¬ 
plates for users with limited budgets. Custom files 
are biillt from the large collection of standard tem¬ 
plates Watertechnics has accumulated over many 
years of Filemaker consulting. Users send sketches 
of layouts, screens and reports they need and the 
templates will be delivered and refined by mail or 
through AppleLink. 

Mike Harris, President of Watertechnics, and 
FileMaker Report columnist says about the new 
services: “Claris Tech Support does a good job. but 
many customers really need consulting help. At a 
certain point, Claris has to decline to hand-hold a 
customer through the entire development of a File- 
maker application. Also, we have access to real 
world solutions unavailable to Claris, even with its 
unrivaled expertise on Filemaker’s inner workings. 


Our phone support really complements rather than 
competes with Claris’ work." 

“We get a lot of calls from users around the 
country looking for ready-to-go Filemaker tem¬ 
plates for their specific businesses. Many don’t ap¬ 
preciate that their needs are unique in many ways, 
even though the underlying function, like receiv¬ 
ables or order entry, may be common. We have a 
collection of many basic Filemaker ‘engines’ from 
past consulting that we can usually modify for very 
reasonable prices - say from about $400 to $1200. 
We generally do the “rough” work and leave layout 
details and fine tuning to the customer. It makes a 
very cost effective partnership.” 

“Of course, we really want to encourage File- 
maker consultants and In-house support people to 
use us as a resource. We have solved some dozen 
really tough types of problems (like sorting repeat¬ 
ing fields or making spreadsheet layouts) that users 
are undoubtably bumping into often. Like the work 
we did for the new Claris Pro ads, the tougher the 
problem the more fun we have with it.” 


Page 13 Issue 32 


The FileMaker Report 


© 1990 Elk Horn Publishing 






FileMaker Quick Tips 


By 

Joe Kroeger 


-□ In the Line Width submenu in FileMaker II, v 1.1, a selected item 

is highlighted with a color on a Mac that displays color. It is the only 
menu I’ve discovered that is that way. It is hard to call this an actual 
Quirk bug. Let’s just say it is an interesting quirk. 


Store Addresses 
as Upper or Lower 
Case? 


In most name and address files, it proves to be satisfactory to store 
data as all upper-case characters. The postal services prefer upper¬ 
case characters for mail labels and the resulting labels are easier to 
read. And, for some operators, it is somewhat faster to enter all upper¬ 
case addresses. 


Mixed Case 
Usefui 


But some applications need access to mixed-case names and 
addresses. Perhaps an address file is used not only to print labels but 
also to print an address on an invoice or to generate a mail-merge letter 
where all upper-case addresses are Inappropriate. Can we manipulate 
addresses so that both styles are available as needed? Not easily if they 
have been entered as all-upper-case characters. 

When I use the lower function on upper-case information in 
FileMaker all the characters are converted to lower case and that is not 
what I want. The proper function capitalizes just the first letter of each 
word, but that is not what I want either. It might be possible to create 
a series of equations that will un-capitalize all the characters desired, 
but there should be a better way. Even an external word or text proc¬ 
essor won’t provide the correct conversion for an upper-case address. 
Example: 

Original WriteNow capitalize words 

ELK HORN PUBLISHING Elk Horn Publishing 

PO BOX 1300 Po Box 1300 

FREEDOM, CA 95019 Freedom, Ca 95019 


FileMaker lower 

elk horn publishing 
po box 1300 
freedom, ca 95019 


Desired 

Elk Horn Publishing 
PO Box 1300 
Freedom, CA 95019 


A big difficulty is that some abbreviations need to be all caps, even 
in an environment of mixed-case words. Examples: PO, CA, IBM, M/S. 
Yet some need to be treated conventionally: Apt, Dr, Dept. 

But the reverse process works much more smoothly. If the infor¬ 
mation originally stored in the database is in the mixed-upper-lower¬ 
case format you desire, then it is easy to convert to all-upper-case 
where needed. Let’s say that the example above called ‘desired’ Is in a 
single field called NandAl (“NameandAddressl"). To make an upper¬ 
case version simply create a calculation field - called perhaps NandA2 
- that has this equation: 


Page 14 Issue 32 


The FileMaker Report ©i990EikHornPubiishing 







NandA2 = upper (NandAl) 

You may already be creating a combined Name-and-Address field 
as an aid to copy/paste operations or for address labels. Or you might 
like to create one. Otherwise, you can use an equation like this one for 
each field of the address. 

If you will ever have a need for an address format that is not all 
upper case, I suggest that you store all your names and addresses in 
the case format you desire and then make them upper-case for print¬ 
ing mail labels. 

Files □ More on the file size problem mentioned in issue #31 (page 13): in 
Too spite of the report that even just browsing files with more than 30,000 
Big? records can be a problem, sometimes it does work. One example is the 
ZIP code database sold by our template library which contains more 
than 42,000 (!) records. I use it without problems as a lookup table. It 
may be that with no calculations and only three fields it does not reach 
some internal limit. 

In addition, it appears that you can browse files with more than 
32,000 records as long as you are browsing a subset that is smaller 
than that number. 

Network^ As reported in the May, 1990 issue of MacUser (page 333), the 
Messaging network-file sharing capability of FileMaker means that you can also 
PilQ pass messages between machines. While any FileMaker file can serve 
as the message vehicle, it will often be convenient to create a file with 
one record, one layout and one text field. Then each user on the 
network can open the message file and place it at the bottom of their 
screen. Any messages typed in the file on one screen will then appear 
on all screens with the file open as soon as the enter key is pressed. 
Any active user can change the message or add to it. This clever tip was 
sent to MacUser by Andy Shaw who worked with the publisher of the 
original versions of FileMaker and who once wrote an article for this 
newsletter. 

Partial □ Sometimes you may want to duplicate a record, but you only want 

Record some of the fields. In such a case you might be tempted to make 

Qup a new layout that contains just the fields to be duplicated. But it does 
not work: a duplicated record contains all the fields in the record no 
matter how many are showing on the current layout. 

If it is really important to duplicate only some of the information in 
a record, you can: 

(A) execute an Output to... command, select the fields to be exported, 
output to a text file, then Input from..., being careful to specify exactly 
the fields exported in exactly the same order; 

(B) create a clone of the existing file, open the clone and delete the 
fields not wanted, then Input from... the original. 


Quick Tips 

(continued) 







All subscriptions to The FileMaker Report are delivered via first class mail (domestic) or air mail (outside the 
US). Ten issues are published each year. Subscription rates are subject to change without notice. Rates 
shown are in US dollars. Current subscription rates became effective February 1,1989. 

Subscription rates: 


Subscription Length (number of issues); 

5 

10 

15 

20 

United States 

$27 

$49 

$68 

$85 

Canada and Mexico 

$28 

$52 

$73 

$91 

Other locations worldwide 

$34 

$63 

$89 

$ 113 


A back issue order form and index are included with each new subscription. Call the office if you would 
like the latest form. Some back issues are no longer available; call for details. Back issue prices in US$: 

Number of issues ordered; 1-9 10 or more 

$6.00 $5.00 

Binders for The FileMaker Report are available. Each holds 10 to 12 issues. Binders are US$8 each 

For back issues and binders local sales tax applies for California residents. For back issues and binders the 
shipping and handling is $3.00 for the US, Canada and Mexico and $7.00 for other locations. 

Mailing labels for each issue have a pair of numbers in the upper right-hand corner that indicate the first 
and last issues of the subscription. Subscriptions may be cancelled at any time and a refund will be issued 
for the unused portion. 

Elk Horn Publishing is on Connect and America On-Line. In both cases our ID is ElkHomPub. Feel free to use 
these services (in addition to the phone, the FAX and the mail) to ask questions about the newsletter, 
about articles, about back issues, about templates, about your subscription, even about FileMaker itself. 

Subscribers are invited to submit writeups, notes, articles and article ideas for publication in 
The FileMaker Report. In addition to feature articles, the newsletter is looking for notes about bugs, oper¬ 
ating suggestions, neat calculations, nice designs, clever layouts, solutions for application problems, and 
so forth. Readers can also contribute to any of the regular columns and sections in the newsletter. Rease 
contact the office if you have questions or contributions or comments or suggestions. Authors are paid 
109 per word for published material. 

Unless otherwise noted, the entire contents of the The FileMaker Report are copyrighted by Elk Horn 
Publishing. The FileMaker Report may not be reproduced or transmitted in whole or in part, in any form or 
by any means, electronic or mechanical, including photocopying or transcription, without the written 
permission of the publisher, except for brief quotations in news items or reviews. It is not iegal to make 
copies of this publication to distribute for others to read. 

The information contained in The FileMaker Reporthas been carefully written, prepared and edited and 
has been obtained from sources believed to be reliable; nonetheless, no representation is made as to its 
accuracy or completeness. No warranty, expressed or implied, is offered for any losses due to the use of 
any material published in The FileMaker Report. Opinions expressed herein represent the views of the 
individual authors based on information available at the time of publication and are subject to change 
-"-without notice. 


Elk Horn Publishing 

PO Box 1300 ■ Freedom ■ California 95019 ■ USA