BoostExcel main content

Custom Price Precision and Scale

Template#: c5152

Download

DescriptionSizeDownload

The template works with both the Microsoft Store edition and the desktop edition of the app. Make sure you have installed one of them.

How to use:

  1. Download the EXE package.
  2. Double-click to extract the files into a folder. Make sure you have full access privileges to the folder, such as a subfolder inside “My Documents”.
  3. Open the template.
395 KBDownload

License: Private Use (not for distribution or resale). See our Terms of Use.

Screenshot

The screen shot for Custom Price Precision and Scale

Detail

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:

Database TableFieldField Precision, Scale
CustomerAccountBalanceDecimal(12,4)
CustomerCreditDecimal(12,4)
InvHdrBalanceDueDecimal(12,4)
InvHdrShippingCostDecimal(12,4)
InvHdrPaymentsDecimal(12,4)
InvHdrTotalDecimal(12,4)
InvHdrSubtotalDecimal(12,4)
InvHdrTax1Decimal(12,4)
InvHdrTax2Decimal(12,4)
InvHdrEmailBalanceDecimal(12,4)
ProductPriceDecimal(12,4)
ProductCostDecimal(12,4)
InvBdyPriceDecimal(12,4)
InvBdyCostDecimal(12,4)
InvBdyLinetotalDecimal(12,4)

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. Set cell format dialog 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. Set column format for reports
  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.

Format and Specification

Template#c5152
Belongs to
Format (XLS or XLSX).xlsx
Columns5
Lines12
Line Height (Points)18.00
Print Area$D$4:$L$47
Papaer OrientationPortrait
Default Margins (Points)
Left18.00
Right18.00
Top75.60
Bottom75.60
PriceFree

Related

Advanced Sample: Macro-Enabled Invoicing Template Thumbnail
Simple Sample: Discount Percentage Field Thumbnail
Simple Sample: Discount Amount Field Thumbnail
Advanced Sample: Invoiced Total in English Words Thumbnail
Simple Sample: Total Quantity on Sales Report Thumbnail
Advanced Sample: Email Is Required Before Saving Invoice Thumbnail
Law Firm Invoice Template (Indian INR Currency) Thumbnail
Advanced Sample: Print One Invoice in Two Different Formats Thumbnail