Using data from header fields

Top Previous Next

This example demonstrates:

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

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 builds on ideas from these topics: Subtotaling the Amount column (QBOL) and Line-item discounts, taxes, and special charges.

Using Header Field Data in Macro Formulas

Header fields are those in the top part of a form, above any line-item detail area.

In many of the applications FullSpeed works with, macros can access data in header fields to use in calculations. Some examples:

®	The invoice date, for doing date calculations. Example: to have a macro provide a customer message like "$23.85 discount if paid by April 7, 2015" on an invoice.

Data stored in customer- or vendor- specific custom fields. In QuickBooks desktop editions for instance, you can have a customer code, discount percentage, location code (shipping zone), etc., entered for each customer, in a custom field. When you select a customer on a sales form that data is brought into the form automatically (if you've customized the form to include the custom field), where it will be accessible to FullSpeed macros. This allows macros to automatically customize calculations specifically for each customer.

®	Manually entered data. QuickBooks Online supports custom fields in the header of invoices. Their contents are not filled automatically when you select a customer, but you can type data directly into them, and FullSpeed can use it to customize calculations for the entire form.

Setting up a macro to access header fields takes a bit of additional planning, but it's easy to do. You need to:

1.	Treat the header fields as if they in were columns in the line-items part of the form.

2.	Put a trigger item on the first line of the line items area on the target application's form. Its purpose will be to trigger a formula that retrieves data from a header field(s) and stores it in a Scratchpad column.

3.	Let other macro formulas refer to the Scratchpad column to use the header data in calculations.

This example: just the basics

The goal for this example is to show in the simplest way possible, how to retrieve data from a header field and make it accessible to formulas in a macro. The macro we will set up retrieves a number from a header field in a QuickBooks Online invoice, then writes it to the Description column in the line items area of the invoice. You can adapt the techniques shown here to any situation where you need to create a macro which bases some calculations on data from a form's header.

Getting Ready

Set up a macro worksheet with enough columns for the job

1.	Count the number of columns you'll need.

The easy way is to click in the header field you will want to access, then press the Tab key, and keep pressing it until you've reached the last column in the line items area of the form, counting fields as you go.

2.	In FullSpeed, go to a blank macro worksheet by clicking on one of the unused tabs at the bottom of the spreadsheet, then click the Set up button in the main toolbar.

When asked how many columns you want in the worksheet, specify at least the field count from step 1. You may also need a Scratchpad column or two, depending on the calculations your macro will do.

Set up a special-purpose item to use for "grabbing" header field data

In QuickBooks Online you would set up a Products and Services item for this purpose. It won't be used to record income ( the Price/Rate should be 0) and doesn't need a Description. The item will be used on the first line of any form (invoice, etc.) in which you want to access header fields, so give it a name which won't confuse customers when they see it there. Here's an example item we have named "Items...":

Other names you could use...

Because the special-purpose item will be used on the first line of a form, it may appear (to your customers) to have no purpose there. So choose a name which either seems to "make sense" in that location, as "Items..." does, or which doesn't seem like a name at all! Some examples:

>
-->
&&

Your name choices will be limited to the kinds of names allowed by the target application. (Each of the names shown above are "legal" in QuickBooks Online.)

Use the item on the invoice or other form

As mentioned, the special-purpose item must be used on the first line of the form. For this example we've also included an item named ShowItHere, which will trigger a formula that writes the number obtained from the header field into the line items area of the form

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

The screenshot below illustrates 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 on the Items... row does the simple job of copying data from the header field (which corresponds to cell C16) into Scratchpad column K.

The formula on the ShowItHere row copies the Scratchpad data from column K to the Description column on that row.

=SUM(K8:K10)

Why isn't this formula simply =K16?
Because FullSpeed does not allow formulas on text-triggered rows to directly reference cells on other text-triggered rows. (Doing so would not produce the results you expect.)

Running the Macro

In QuickBooks Online:

1.	Click in the Begin field of the invoice

In the macro worksheet above, the Qty cell is marked as the Begin field (shown in bright green). So click in the Qty column on the Items... row.

2.	Press the hotkey you assigned to the macro.

Here's a view of the invoice after the macro has finished....the "2" from the Discount % field in the header has been copied to the ShowItHere row.

Key Points for Accessing Header Fields

The key things to remember are:

1.	Macro formulas can only access header data that has been copied to a Scratchpad column, and

2.	Text-triggered formulas can only access Scratchpad data using an =SUM() formula which refers to the Scratchpad column (represented by the Sandbox rows in that Scratchpad column, at macro design time).