[Novice]...by Jon Rosen / Intellitec
Clean Printing Invoices in 3.0
The new deal with printing well in FileMaker
Nearly every business needs to print invoices. The ability to print invoices with FileMaker Pro 3.0 is greatly enhanced over FileMaker Pro 2.1. With FMP 2.1, the most common way to print invoices was to define a repeating field with as many line-items as you would need. Sometimes this would work out all right. But, if you had a variable number of line-items, say anywhere between 5 and 1,000 line items, it was a problem. You could define a repeating field with 250 line items and cause it to be reduced as necessary, but it takes FileMaker a long time to image a page with that many repeating items. The layout is also very awkward to create.
But enough said about the old, what about printing invoices with the new FMP 3.0? Is it greatly improved? You've probably guessed that if there weren't much difference, this article wouldn't be necessary. The new techniques, while much more capable, aren't necessarily intuitive.
In FMP 2.1, you would create an invoice file with a repeating field for the line items. With FMP 3.0, you would typically have an invoice file and a separate line-items file. You may also have a company file, a products file, and other files, as necessary.
In order to avoid building too much suspense, there are four basic steps to printing an invoice:
A. Define an invoice file with all of the invoice header info.
B. Define a line item file for each of the invoice line items.
C. Create a relationship between the two files. Your invoice number will probably be the key field.
D. The invoice is simply a report. Create a script in the invoice file to print the invoice from line item file. Each line item is a record in the body section of the report. The invoice fields are the fields from the related invoice file.
If Claris is listening, the way this should work is that an invoice should print from the invoice file itself. There should be a special body part that would print records from a related file the same way a layout can print records from its own file.
[Editorial Note: The reason for this happening, if you have not deduced already, is that when in the invoice file you have a portal looking into the line-items database. It may only extend to show, say 5 records. If, for example, there are 10 line-items, only the first 5 of the unsorted portal will print from the Invoice file.]
Now, a little background explanation. If you aren't used to relational database design, it will take some practice to integrate the concepts. Once you do, you will probably find it a more intuitive and flexible way to design your project's file structure. With flat-file FileMaker, you had to guess at the maximum number of line items you would have and create a repeating field to hold those items. There are ways to create flat file databases using separate invoice and line item files; there are also techniques involving exporting records and importing the repeating fields into a print file. But it can take quite a bit of work to get the desired results. With relational FileMaker, you would use a separate file where you would have used a repeating field previously.
Now that we have these separate files, how do we tie them together? A unique identifier, referred to as the primary key, relates the records from the various files. For example, each line item record for an invoice will have a copy of the invoice number so you will know which invoice it is related to.
One way to think about printing records is to remember that you should print from the “many” file. There are three types of possible relationships between files: 1-to-1, 1-to-many and many-to-many. Normally, you will have a 1-to-many relationship. In this instance, there are many line-items for a single invoice. So, we print the report from the line-item file.
You may ask why we don't print invoices from portals. That's a good question and the answer is sometimes we do. If you're not familiar with the term portals, you will be soon because it is one of FMP 3.0's best new features. A Portal is a window into a related file that simplifies data entry and allows you to change and view data in related files directly, without lookups. If you know that you don't have more line items than would fit on a single page (portals can't run over a page boundary), you can print your invoice from the invoice file itself.
Using the techniques described above, you can easily print invoices of any length with FileMaker Pro 3.0. Let me know what else you would like to know about FMP 3.0 and I will cover these issues in future articles.
- END -
Jon Rosen is President of Intellitec, a consulting firm that specializes in FileMaker development and in Macintosh computer consulting. Intellitec has offices in Boca Raton, Florida and in New Canaan, Connecticut.
Email: <jonro@aol.com>