FileMaker Pro 9: The Missing Manual

Going Beyond Basic Calculations

In the last chapter you created two very simple calculation fields using numbers: Extended Price and Invoice Total. But those calculations were very simple. Now, you build on those basic concepts to see how you can make a set of calculations that fit an upcoming sales promotion.

Preparing the Data

You’ve decided to start reselling personal security products to your customers. To help you keep track of things, you need to add a Products table to your database. The table should have these fields:

• A text field called SKU

• A text field called Description

• A number field called Cost

• A number field called Price

Note

See Creating a Relational Database for advice on creating tables.

Once you’ve created this new table, FileMaker automatically creates a layout— Products. Switch to that layout now and create a few product records. To help drive sales to your larger clients, you want to implement a volume discount scheme; they should get a 5 percent discount if they buy enough. But some of the products you sell don’t have enough mark-up to justify these discounts. You want to be sure the discount never reduces your mark-up below 20 percent.

First, you add a line to your marketing materials: “Volume discounts not available for all products.” You can make this line as small as humanly possible and hide it way down in the corner. Next, you need to fix your database so it tells you the discount price for each product.

Building the Calculation Fields

To implement this discount ...