Author: Dmitri Popov
Creating a data source
First, you need to connect OpenOffice.org to an address book that contains your customers’ contact data. OpenOffice.org can talk to numerous address book formats, including the address book module of the Mozilla Browser Suite, which is an open source software package available for a variety of platforms. Better yet, OpenOffice.org also includes a wizard that allows you to easily set up a Mozilla-based address data source.
Go to File > Wizards > Address Data Source. In the Address Data Source Wizard, choose Mozilla/Netscape and then select which address book you want to use (you can choose between Collected Addresses or Personal Address Book). Give the data source a name and choose a path where it will be stored. Make sure that you select the option, “Yes, register the database for me,” then click Finish to close the wizard.
Tip: You can also use the Wizard to connect to other data sources containing addresses. For example, to connect to the Thunderbird address book, select the Other data source option, press Next, click on the Settings button, and then select the Thunderbird Address Book from the Data type drop-down menu.
Now you can launch Writer. To connect to the created Address book data source, choose Data Sources from the View menu or press the F4 key. This opens the Data Sources window. Double-click on Addresses (or click on the plus icon next to it). Double-click on the Tables title and select Personal Address Book to view it as a table.
Creating an invoice template
An invoice must have at least two parts: the address fields and a table containing the invoice data. The address part consists of fields (think of them as placeholders), which will be filled in automatically by OpenOffice.org using the data from the created data source.
You can add fields to the document in two ways. The easier way is to click on a column title (e.g. company, first name, or last name) and drag it onto the desired location in the invoice document.
The second method is a bit more complicated, but it essentially does the same thing. Choose Insert > Fields > Other, click on the Database tab, and select Mail merge fields in the Type window. Choose Address > Personal Address Book, select the field you want to insert, and click the Insert button. Click Close after you’ve added all the necessary fields.
The next step is to add a table that contains invoice items such as description, prices, subtotal, VAT (or tax), and total sum. Choose Insert > Table to create a table. To define a calculation for the subtotal cell, place the cursor in the B7 cell and press F2. In the calculation field, enter the following calculation: =<b2:b6>.
This calculation means that the contents of the B7 cell is the sum of the cells from B3 to B6. The calculation for the B8 cell (Subtotal) is =sum(<b7>*25/100) (assuming that VAT is 25%). Finally, the total calculation is =sum(<b7:b8>). Check whether the calculations work correctly by entering a couple of invoice items and examining the subtotal, VAT, and total results.
If you want the invoice to automatically calculate the due date in the payment due field, you can use the following calculation: =days(<b1>+n), where B1 is the cell that contains the invoice date and n is the number of days.
You may also want to format numbers in the price column as currency. Select all cells in the column by clicking on them while holding down the Shift key. Choose the Number Format command from the Format menu. In the Number Format dialogue box, choose the appropriate currency and define formatting using the provided options. Finally, save the created invoice document as an .ott file (OpenDocument Text Template).
Creating and printing invoices
With all these pieces in place, you’re ready to create and print invoices. To create a new invoice, open the template. Press F4 to open the Data Sources window if you haven’t already done so. To fill out the fields with the address data you want, select the appropriate record row in the table and press the Data to Fields button. Save the resulting invoice as a Writer document.
When you press the Print button or choose File > Print, you are prompted to choose whether you want to print the form letter. Choose No, and if you don’t want to see the prompt window again, tick the “Don’t show warning again” check box. Don’t worry, you will still be able to print the invoice, you will just bypass the whole form letter printing procedure.
Finally, to make the solution complete, you need to create an invoice managing system that will help you keep track of invoices and their status.
Creating an invoice manager
Start by creating a new spreadsheet that will be your tool for managing invoices. To keep track of all invoices, create a column that contains links to the invoices stored on your computer.
To insert a link to a specific invoice, click on the A1 cell and choose Hyperlink from the Insert menu. Select the Document option in the Hyperlink dialogue window, then enter the path to the invoice in the Path field. In the Text field, enter a descriptive name for the invoice, such as the invoice number number and title. If you leave this field blank, Calc will insert the full path into the cell. Click Apply and then Close.
Now that you have all your invoices in one place, how do you know which ones have to be mailed or have already been paid? You need to add a status column with color codes. First, use the Stylist to create three cell styles: To be sent, with a red background; Sent, with a yellow background; and Paid, with a green background. You can, of course, choose whatever background color you like.
Click on the cell B1 and choose Conditional Formatting from the Format menu. In the Conditional Formatting dialogue window, define three conditions as shown in Figure 1. The specified conditional formatting will be used to apply one of the defined cell styles, depending on the cell’s contents.
Now that you’ve defined the conditional formatting for one cell, you need to do the same for all the cells in column B, or at least some of them. To do this, click on the formatted cell B1, click on the black handle in the lower right corner (the cursor changes to the hair-cross), hold the mouse button down, and drag the handle down. If the defined formatting works properly, the result should look like the one in Figure 2.
|
Figure 2 – Results of conditional formating |
Tip: Instead of entering the status manually each time, click on a status cell and press the Ctrl-D key combination. This gives you a drop-down menu containing all previously entered items.
To make the invoice manager even more useful, you can add a feature that will mark overdue invoices. To do this, simply add a Payment due row (column C) and add a function to the cells in column D.
Click on the cell D1 and press the Function Wizard button. Use the Function Wizard to enter the following function: =IF(AND(C2<TODAY()-5;B2="Sent");"OVERDUE";" "). This means that if the payment due date is less than the current date minus 5 days and the status of the current invoice is Sent, then the invoice will be marked as OVERDUE. Use the formatting trick described above to apply the function to other cells in column D, and your invoice manager is ready to go.
