Q: We often add discounts to different items that we sell to various customers. It is not always the same for each product or each customer. We use the percentage discounts amount in each detail line of the invoice, but we would like to advise the customer how much of a discount we have given them in total and in dollars. I’ve modified my invoices using the forms designer but how do I add a total discount line on my invoices?
As you are aware MoneyWorks Express and Gold allow you customize invoices. Open the invoice that you modified in forms designer. In this example we are going to add a calculation field at the bottom of the invoice that will calculate the total percentage discounts given on all the detail of the invoice in dollars.
Using the Calculation Box Tool [Blue Arrow] draw a box at the bottom right of the invoice form [Blue box area].
The Calculation window will open:
Assign an unique Result name (no spaces allowed) [blue Arrow}, Set the Apply Format to numbers with 2 digits [Green Arrow] and enter the formula as shown in the Calculation box [Red Arrow] and click OK.
Or copy the formula below and paste in your calculation field.
“$” + Sum(ExpandList(“List”,”Detail.StockQty*Detail.UnitPrice-Detail.Net”))
The sum expand list function is calculating the qty of each detail line in the object “List” times the full Detail unit price (before discount entered) in the invoice in the object “List” and subtracting the resulting Detail Net (already calculated net amount after discount and before taxes). The “$” at the beginning is just adding the dollar sign to the resulting calculation.
The one caveat to using this formula is the object list must be be named “List” which is the default name assigned to this object on our templates. So if you created your own list object when customizing your invoice, you should name or rename that object list to List. Highlight the list box on your form by clicking the border. It is selected when the small square appears in each corner [Blue Arrows].
If you did not create your own list from scratch, the formula will work to the default name. Click OK on the List Options window.
Now add a Text Field to the left of the calculation field, that will appear on the Invoice using the Text Tool [Blue arrow] and type in a name e.g. Discount Given.
We now a calculation field that will show the total discount given in dollars and a field label.
However, you might not want to show these fields if you did not give a particular customer a discount or told them no such things were not available. Why rub their face in it?
So let’s make these two fields (the label and the calculation field) conditional in that they should only appear on the invoice if a discount was applied in the transaction.
Highlight the two fields (hold your shift key, select one then the other) [Blue Arrow] and go to the Edit menu > Forms Designer > Visibility as shown below…
Sum(“$” + Sum(ExpandList(“List”,”Detail.StockQty*Detail.UnitPrice-Detail.Net”))) >= “1”
Note: The visibility calculation is stating that if the the sum of the calculation to determine the discount is equal to or greater than the value “1” the object is visible. Therefore if zero, it not visible.
Click OK [Black Arrow] and the calculation will be entered in the Object Visibility condition, click OK to the Object Visibility window.
Now test you form against some invoices:
In this invoice we are selling Widgets with a discount applied to two of the items.
The Invoice shows the discount given at the bottom of the invoice in dollars.
The Discount Given is not displayed on the invoice.
For more information about MoneyWorks Support in Canada, in the USA, or in the UK contact www.moneyworkssupport.wordpress.com