Once you've created your QuickBooks data source in Klipfolio (as described in How to connect Klipfolio and QuickBooks Online), you'll see that the data structure is more complex than tabular (XLS/CSV) data or even most hierarchical (JSON/XML) data. This article gives a high-level overview of this QuickBooks data structure and describes how to access the data in your QuickBooks data source according to column name.

This article assumes you are familiar with building basic formulas. 

QuickBooks Report Response Structure

Working with QuickBooks data requires an understanding of the structure of a QuickBooks Report Response as well as how to work with XML/JSON data sources. A QuickBooks Report Response has three sections:

1. Header describes the report (for example, report name, start and end dates of report period)
2. Columns describes the data contained in each column of the report (column title and type)
3. Rows contains the report data, there are 2 types:
• Section describes a sub-report (contains these same three report sections)
• Data contains report data by column

The structure of the Columns and Rows sections depends on the QuickBooks report. The following description is based on a Customer Income report.

Access QuickBooks Data by Column 

To access QuickBooks data for use in a formula, you must identify the column to use:

1. Expand the Columns element until the ColTitle objects are visible and identify the column required. This example will use column 3, Expenses.

2. Expand the Rows element until the ColData object is visible and select the first value.

The formula bar will display @/Rows/Row/ColData/value. Evaluating this object will list all /Rows/Row/ColData/value elements.

3. To select data from a specific column, you must specify the column number; for example, to select the value in column 3, you must specify 3 as the index (typed between square brackets) into ColData:

@/Rows/Row/ColData[3]/value

This hard-coded approach is sufficient if you know the data in column 3 will never change. However, if this cannot be guaranteed, a more robust solution is to calculate the column number based on the title of the column you need. In this example, the QuickBook Response columns are

<blank>
Income
Expenses
Net Income

Calculate the column number of the Expenses column by counting the columns preceding 'Expenses' and adding 1:

[count(/Columns/Column/ColTitle[.='Expenses']/preceding::ColTitle)+1]

This calculation breaks down as follows:

/Columns/Column/ColTitle[.='Expenses']/preceding::ColTitle
returns
Income, <blank> (the columns preceding Expenses)

count(/Columns/Column/ColTitle[.='Expenses']/preceding::ColTitle)
returns
2 (the number of items returned by the above)

count(/Columns/Column/ColTitle[.='Expenses']/preceding::ColTitle) +1 
returns
3 (the number of the Expenses column)

4. Use this column number calculation as the index (typed between square brackets) into ColData:

@/Rows/Row/ColData[count(/Columns/Column/ColTitle[.='Expenses']/preceding::ColTitle)+1]/value