Calculating discounts based on a header field

This example demonstrates:

®	Retrieving data from header fields in a target application, to use in other macro formulas

®	Subtotaling the Amount column

®	Appending calculated results to a text field

®	Using IF function logic to determine a discount rate from a customer code.

Application notes:

®	QuickBooks Online's header fields are accessible to FullSpeed and can be used as shown in this topic.

®	Xero's header fields are not accessible to FullSpeed at this time.

®	QuickBooks desktop editions' header fields are accessible to FullSpeed and can be used as shown in this topic.

This example is based on QuickBooks Online. It mostly builds on ideas from these topics: Using data from header fields and Line-item discounts, taxes, and special charges.

This is really "Part 2" of the prior topic, Using data from header fields, which provides the basics of what you need to know for accessing header fields. If you have not read that topic, please review it before continuing!

Calculating a Discount Using a Customer Code from a Header Field

This example is based on QuickBooks Online. In it, we will set up a macro that calculates a discount on a group of items on an invoice. The discount rate will be determined by reading a customer code from a header field, then using IF function logic to determine the discount rate.

If you use QuickBooks Online you may be aware that some versions can apply a discount to an entire invoice. This example, however, shows how to discount just part of the items on an invoice.

Getting Ready

Set up the items you will need

For this example we will need:

®	An item for triggering a formula that will access the header field, as discussed in the topic Using data from header fields. In this example it is named "Items...".

®	An item for triggering the discount calculation. We will use an item named Discount created in a prior topic, which will post the discount to an appropriate account.

Use the items on an invoice or other form

The item which triggers header field access must be used on the first line of the form, as shown below. We've added the Discount item on line 5, where it will calculate a discount on the items in lines 2 through 4.

Set up the macro worksheet formulas, trigger text, etc.

The screenshot below shows the formulas, trigger text, Begin field, Match column, and other appropriate worksheet settings.

See the Tutorial if you need basic information on these items.

Formulas

The formula in the Scratchpad column (K) on the Items... row (16) gets a code from the Customer Code field in the invoice header, and determines a discount rate based on the code:

=	Every formula begins with an equal sign (=).
IF(C16="A",0.02,	If the code in C16 is "A" use 0.02 (i.e., 2%) as the discount rate, otherwise...
IF(C16="B",0.05,	If the code in C16 is "B" use 0.05 (i.e., 5%) as the discount rate, otherwise...
0))	...return a discount rate of 0 (no discount).

The formula in the Amount column (J) on the Discount row (23) calculates the dollar amount of the discount:

=	Every formula begins with an equal sign (=).
=SUM(J8:J10)	Gets the amount eligible for discount by subtotaling the Amount column. This subtotal will be for all item lines present at this point on the invoice (or since the most recent prior subtotal, except that there isn't one on our example invoice).
*SUM(K8:K10)	Multiplies by the discount rate from Scratchpad column K. The discount rate is obtained by summing the entire range of column K. (That is how all formulas must access data retrieved from a form's header, as discussed in Using data from header fields.)
*-1	Multiplies by -1 to make the discount amount a negative number, so it will be deducted from the invoice total.

The formula in the Description column (G) on the Discount row (23) is "just for show". It is not necessary for the discount calculation but but is useful because it lets customers know the dollar amount that was eligible for discount.

=SUM(J8:J10)

Subtotals the Amount column for all item lines present at this point on the invoice (or since the most recent prior subtotal).

Format the formula cells

	General format.
	Numeric format, with 2 decimal places.
	Currency format, with the Append results to field contents option check marked.

Running the Macro

In QuickBooks Online:

1.	Click in the Begin field on the first row of the invoice.

In this example the Qty cell was selected as the Begin field (shown in bright green in the macro worksheet).

2.	Press the hotkey you assigned to the macro.

Here's the line items area of the invoice after the macro has finished. Notice the subtotal and the discount—which is a negative amount, as it should be.

Do I need to keep the Items... line in the invoice?

No. After the macro has calculated the discount you may delete that line if you wish.

However, if you make invoice changes which call for running the macro again, you'll have to insert the item on the first row, just as it is shown above, before running the macro again.