Incomplete Lines on Form Body - Invoice Manager for Excel
Why is this option introduced?
This document was written for Excel Invoice Manager. If you are using Invoice Manager for Excel, the "Incomplete lines on invoice body" option was enabled by default. So you are no longer need to care about it.
Free invoice form, as the name implies, gives you more flexibility in filling in product lines on invoice body. With the sample invoice form provided by Excel Invoice Manager, you can enable this feature by simply clicking an option.
Before enabling the support for free invoice form, if you save an invoice by clicking the Save To DB button, Excel Invoice Manager tries to find out how many valid lines on the invoice body by looking at the Product ID, Product Description and Line Total cells. A line is valid only when all the 3 key fields (Product ID, Product Description, Line Total) are filled in. Invalid line, and all the following lines after the invalid line, are discarded.
If you enable the support for free invoice form, incomplete lines are valid. Excel Invoice Manager looks for the last non-empty cell on the invoice body, and all lines before the non-empty cell are saved to the database when you click theSave To DB button.
In the following figure, if the support for free invoice form is not enabled, only line #1 is saved to the database when you click Save To DB, since the line #2 is empty. However if you enable the support for free invoice form by allowing incomplete lines, all lines are saved - that is, all the 5 lines are saved, including those empty lines before the non-empty cells.
Enable support for free invoice form
It's easy to enable the support for free invoice form.
- Click the Settings button on the Invoice worksheet.
- On the Workbook tab, check the Allow incomplete line on invoice body option. The Line Totals can be zero option is checked and disabled automatically - this means that, line totals must be allowed to be zero if you allow incomplete lines to be saved.
- Click Apply.
- Click the Close button to close the Settings window.
- Save the template file (workbook file) to make the changes permanently, by clicking menu File -> Save in Excel 2003, or by clicking the Office button -> Save in Excel 2007, or by clicking File -> Save in Excel 2010 and later versions.
How incomplete lines are processed
To successfully process incomplete lines, Excel Invoice Manager takes certain steps and applies certain rules to make the invoice form work with it.
- Looking for non-empty value
When determining the last line that must be saved, Excel Invoice Manager tries to find out the last non-empty cells. A cell is seen as non-empty if it is a text field and contains at least one character, or it is a numeric cell and the value is not zero.
Note: A numeric cell containing a value of 0 (zero) is empty.
- Saving empty cells
If the Product ID and Product Name cells are empty, and the line must be saved to database because there are non-empty cells in the following lines, the cells will be filled with a placeholder automatically. The default place holder is " ---" (3 hyphens). Likewise, all numeric cells are filled with 0 (zero).
The Invoice Edit window shows these placeholders. To open the Invoice Edit window, click the Invoices button on the Invoice worksheet, and then double click the invoice you want to open.
However, if you click the Edit In Excel button to display the invoice in the Excel template, the placeholders are removed.
- Reporting
The Product Report offers two options for working with free invoice form or incomplete lines on invoice body.
Option Description Exclude lines with empty Product ID The report excludes those invoice body lines that don't have a Product ID. Exclude lines with empty Product Description The report excludes those invoice body lines that don't have a Product Description. These two options are disabled if you select a product to generate a product report for only the specified product.
- Cell names on the invoice body
Generally you don't need to bother with cell names, since the default invoice template already defined all the names.
However, if you want to redesign the invoice body - merge/unmerge cells, move out a column or add new columns by using custom fields, it is necessary to understand how Excel Invoice Manager provides such a level of flexibility by utilizing cell names.
Cells without a name won't be saved to database. In order to have the program save the value of a cell, the cell name must be defined in the Invoice Body (InvBdy) database table. This is true for both the standard fields defined by Excel Invoice Manager, and custom fields created with Database Designer. A cell name has the following format:
Prefix Database field name Underline symbol Line number okn For example, ProductID _ For example, 5 For example, the cell on the Price column/third line must have a name " oknPrice_3".
Note: In order to enable the free invoice form feature, the Invoice worksheet must define the name Print_Area.
The support for incomplete lines is disabled by default. Although it's easy to enable the feature, it saves empty lines to the backend database. For this reason, it's better to enable free invoice form only when needed.