Simple Sample: Discount Amount Field

If your business applies discounts frequently, it is better to list the discount at the bottom of the form. This simple, customized template demonstrates the steps required to add a Discount (Amount) field.

This tutorial is based on the Simple Invoice Template: Moving Item# Column (c4049).

Before beginning, first make sure you have backed up both the Excel template file and the Access database file (Or the SQL Server database if you are running our invoicing app with a SQL Server database).

The process of adding the discount field consists of two steps: modifying the Excel template and updating the database.

Modify the Excel template

1. Open the template.
2. Click the Design Mode button on the ribbon tab.
3. Right-click the row heading of the first tax row at the bottom of the form. In this example, it is R35.
4. Choose Insert from the shortcut menu. This inserts a new row above the first tax row.
5. Enter "DISCOUNT (AMOUNT)" below the "SUBTOTAL" label, i.e., $J$35.
6. Click to select the cell where the discount amount value should be entered. In this example, you click $K$35.
7. Enter the cell name "oknDiscountAmount" into the name box on the formula bar. All cell names that work with Invoice Manager for Excel should start with the "okn" prefix, except "Print_Area" and "Print_Titltes".
8. Right-click the cell named "oknDiscountAmount" and choose Format Cells from the shortcut menu.
9. On the Format Cells dialog box, go to the "Protection" page.
10. Clear the "Locked" property.
11. Click "OK" to close the dialog box.
12. Click to select the cell next to the label "SUBTOTAL", i.e., the cell named "oknSubtotal" at $K$34.
13. Modify its formula to be:

    =SUM(oknLineTotal_1:oknLineTotal_12) - oknDiscountAmount

14. To deduct the discount amount from the taxable amount, modify the first tax formula to be:

    =ROUND(IF(oknTaxType=0,0, oknTax1Rate*(oknLineTotalTaxable - oknDiscountAmount+IF(oknTaxTotalIncludingShippingCost=0,0,oknShippingCost))),2)

15. Modify the second tax formula to be:

    =ROUND(IF(oknTaxType<>2,0,oknTax2Rate*(oknLineTotalTaxable - oknDiscountAmount+IF(oknTaxTotalIncludingShippingCost=0,0,oknShippingCost)+IF(oknTax2IsAppliedToTax1=0,0,oknTax1))),2)

16. Click the "Design Mode" button again to exit design mode.
17. Save the template.

Update the database

1. Open the template.
2. Click "Invoices" on the ribbon tab.
3. Click "Custom Fields".
4. Make sure the "Database Table" field shows "Invoice Header".
5. Click "Add Field".
6. Enter field information as below:
   • Field name: DiscountAmount

     Note that The field name should match the cell name defined in the template. In this example, we defined the cell name as "oknDiscountAmount" and thus the field name should be "DiscountAmount". It is this name that creates the relationship between an Excel cell and a database field. In other words, the name maps the cell name to a database field.

   • Field type: Decimal
   • Precision: 10
   • Scale: 2
7. Click "OK" to close the "Add New Custom Field" dialog box.
8. Click "Close" to close the "Custom Field Manager" dialog box.

That it! Now you have a simple template that has a Discount field at the bottom of the sheet. Feel free to download and test it to see if it meets your requirements.