Custom Price Precision and Scale

This template demonstrates how to change the precision and scale of the unit price, unit cost, line total, subtotal, paid, and total due cells. For example, if you are a graphics business in New Zealand and want to calculate to a tenth of a penny (i.e., $000,000.062), you need to modify the default database fields.

To do the required calculations, the following database fields should be modified:

Note that if you use the Purchase Order and Quote worksheets, the related database tables, i.e., PurchaseOrderHdr, PurchaseOrderBdy, QuoteHdr, and QuoteBdy, should be modified in a similar way too.

To modify a field:

1. Click "Customers" on the "Invoice" ribbon tab.
2. Click "Custom Field".
3. Click "Advanced...".
4. Click "Backup Database" to backup your database. This ensures you can easily return to a working database in case something goes wrong during the modifications.
5. Choose "Customers" from the "Database table" list.
6. Click to select the "Account Balance" field.
7. Modify the field definition so that the Precision section shows "12", and the "Scale" section shows "4". The field type "decimal" is not modifiable.
8. Click "OK". This modifies the "AccountBalance" field to be "Decimal (12,4).
9. Repeat the steps from 5 to 8 for all the fields shown in the table.

Once you have modified the database fields, the next step is to modify the invoice form so that it shows the required digits after the decimal point. To do this:

1. Go to the "Invoice" worksheet if it is not already the active sheet.
2. Push down the "Design Mode" button on the "Invoice" ribbon tab.
3. Push down the CTRL key on your keyboard, and then without releasing the CTRL key, click all the cells that you want to modify the display format, for example, all the price cells, subtotal cells, and total cells.
4. Right-click one of the selected cells and choose "Format Cells" from the shortcut menu.
5. Go to the "Number" tab.
6. Choose "Currency" from the "Category" list and enter "4" into the "Decimal Places" box.
7. Select whether you want to show the currency symbol.
8. Click OK to modify the cell display format.
9. Once you have done setting all the cell formats, click the "Design Mode" button again to exit design mode.
10. Save the template.

You can also format the reports to show four digits. To do this:

1. Go to a report worksheet, for example "Sales Report".
2. Push down the CTRL key on your keyboard and click each column heading for which you want to set the format to select all the columns.
3. Right-click one of the selected column headings and choose "Format Cells".
4. Set the number format as described above.
5. Once done, save the template.

Note that as of Invoice Manager for Excel version 7.12, the setting of database fields does not affect how the decimal numbers are shown on the data list windows and various "Edit" windows, such as the "Invoices" data list window and the "Customer Edit" window. However, the program does offer a hidden option that allows you to show four digits for the price fields on the data list windows. Contact us to find out how you can use the registry file to enable this.