Databases aren't
just for storing information anymore. By taking advantage of the mathematical
capabilities of FileMaker Pro, you can expedite the billing process
by creating forms directly from your database. One
of the simplest forms to create from a database is an invoice. By
using a couple of calculation fields, you can create an invoice
like the one shown in Figure A.
Figure A: You
can create an invoice like this one from your database.
![[ Figure A ]](images/fmp9924a.gif)
In this article, we'll
show you how to create an invoice right from your database by using
a few calculation fields that determine subtotals, sales tax, and
the amount due.
Creating an invoice
Suppose you have a company
that sells office supplies. When customers call to place orders, the
purchase information is entered into a database. Then, someone has
the job of either typing or handwriting an invoice for each order
that details the information of the request, including such things
as a description of the products ordered, the quantity and price of
each item, and the amount due. Your
goal is to combine these two jobs to calculate this information
from your database and avoid the manual step of creating the invoice.
By simply creating a couple of calculation fields that calculate
subtotals, sales tax, and the amount due, you can create your own
invoices.
To create the invoice,
first we'll create a database and define the fields, including a
few calculation fields to calculate subtotals, sales tax, and the
amount due. Then, we'll enter some data and, finally, we'll print
the invoice.
Defining the fields
Let's begin by opening a
new file and naming it Office. Next, define the following text
fields: Company Name, Contact Name, Address, City, and State.
Then, define the two number fields ZIP and Phone. Now,
define a date field named Order Date and click the Options
button. When the Edit Options For Field dialog box appears, click
on the Auto-Enter tab (if it's not already selected). Then, select
the first check box, choose Creation Date from the adjacent dropdown
list, and click OK. This will automatically enter the date that the
request is created into the Order Date field. Now,
let's define some more fields. Define the following text fields:
Product Ordered, Product2 Ordered, Product3 Ordered, and
Product4 Ordered. Also, define the following number fields:
Quantity, Quantity2, Quantity3, Quantity4, Price, Price2, Price3,
and Price4. These fields will accommodate the information
that's entered regarding the office supplies being ordered.
You're finally ready
to define the calculation fields. Create a calculation field named
Subtotal that uses the calculation shown in Figure B.
Figure B: Use
this calculation for the Subtotal field.
![[ Figure B ]](images/fmp9924b.gif)
Then, choose Number from
the Calculation Result Is dropdown list. The Subtotal field returns
the total of the quantity ordered multiplied by the price for all
of the products ordered. This is the total before taxes are added.
Next, create a second
calculation field named Sales Tax that uses the calculation
Round(Subtotal*.06,2)
Choose
Number from the Calculation Result Is dropdown list. This field multiplies
the subtotal by the applicable sales tax and then rounds the result
to two decimal places. For this example, we'll assume that the sales
tax is 6 percent and we'll multiply the subtotal by .06. Also, notice
that we've included the Round() function in this calculation. You're
rounding the result of this calculation because you don't want to
have customers who owe tenths, hundredths, or thousandths of a cent.
Now, create a third calculation
field named Amount Due and use the calculation
Round(Subtotal+Sales Tax,2)
Again,
select Number from the Calculation Result Is dropdown list. This field
adds the subtotal to the sales tax and returns the amount due. This
is the total including taxes. Again, you'll want to include the Round()
function to round the result of this calculation to two decimal places.
Finally, create a number
field named Amount Paid and another calculation field named
Balance that uses the calculation
Round(Amount Due-Amount Paid,2)
Here,
too, choose Number from the Calculation Result Is dropdown list. This
field subtracts the amount paid from the amount due. If the bill is
paid in full, the balance will be zero. If the amount paid is different
than the amount due, a number will appear in this field. Again, you'll
want to round the result to two decimal places by using the Round()
function. When you finish defining these fields, click Done to close
the Define Fields dialog box.
Organize the layout
Once all of the fields have
been defined, notice that the layout is crowded and not very attractive.
Let's organize the layout a little better and make it less crowded,
as shown in Figure C. Figure
C: This is an example of how you can organize your layout to
make it look more attractive.
![[ Figure C ]](images/fmp9924c.gif)
This will make the layout
easier to read. To eliminate some of the clutter on the layout,
switch to Layout mode and delete the following field labels: Product2
Ordered, Product3 Ordered, Product4 Ordered, Quantity2, Quantity3,
Quanty4, Price2, Price3, and Price4.
Entering some data
Once you've organized your
layout and all of your fields fit on the screen, you're ready to enter
some data. To do so, switch to Browse Mode and enter the data from
Table A. Table
A: Enter this data into the Office database.
| Company
Name |
ABC
Marketing |
| Contact
Name |
Nick |
| Address |
11
Westley Rd. |
| City |
San
Antonio |
| State |
TX |
| ZIP |
55656 |
| Phone |
555-567-8989 |
| |
| Products
Ordered |
Quantity |
Price |
| White
Paper (8.5x11) |
5
cases |
$29.99 |
| Pens
(black) |
10
boxes |
$3.99 |
| Folders
(assorted colors) |
8
boxes |
$9.99 |
| 3-Ring
Binders (white) |
12
each |
$4.00 |
| |
| Amount
Paid |
$215.00 |
As you're entering the
quantity and price information for the products ordered, notice
that the subtotal, sales tax, and amount due fields are automatically
calculated, as shown in Figure D.
Figure D: Your
layout should look like this after you've entered the information
from Table A.
![[ Figure D ]](images/fmp9924d.gif)
Printing the invoice
Now that you've entered
the information, you're ready to print the invoice. To print the invoice,
open the File menu and choose Print. When the Print dialog box appears,
click the Print dropdown arrow, select Current Record, and click OK.
A note about adding
graphics
If you look at Figure A,
you'll notice that we've placed a graphic in the header of our invoice.
To do this, all you have to do is select a graphic, such as your company
logo, copy it, and then paste it into the header of the layout. You'll
probably need to resize it so that it fits in the header. Conclusion
If you want to expedite
the billing process, you can use calculation fields to create forms
directly from your database. In this article, we've shown you how
to create an invoice right from your database. download
.PDF file
Copyright © 2001 MacLane Nova New
Media - DO NOT REPRODUCE IN ANY FORM
|
