Skip to main content

QuickBooks Online channel

See the Channel Catalog to see which Plans have access to this channel.

QuickBooks Online is an accounting software package for small to medium businesses that operates in the cloud and is accessed through a secure login in a web browser. This channel does not work with QuickBooks Desktop or QuickBooks for Mac.

Please note that terms used in the QuickBooks Online channel categories are QuickBooks Online specific terminology.

How to Connect

  1. On the My pipelines page, click Create a pipeline. Enter name, description, and tag fields and click the Create Pipelines button. The pipelines wizard displays:
    wizard.1.png
  2. From the wizard you choose the connections for the first steps of your pipeline. When you choose a type, you'll add the channels and steps for your first steps. You can always add more steps later. A Triggered pipeline is started by a specific event in real-time. A Scheduled pipeline will start according to a schedule. A Manual pipeline only starts manually. Once you've completed the wizard, you'll be prompted for connection detail, if necessary. If you are an experienced builder, click the Start from scratch button and then from the right side of the page, choose the All to list all available channels.

Connect to QuickBooks Online

  1. Expand QuickBooks Online in the list of channels on the right side of the page and click Connect to QuickBooks Online.
  2. You now have the option to connect to a sandbox company in order to run pipelines against these test environments. In the pop-up window, you now have the optionto connect using sandbox companies allowing you to run your pipeline(s) against a test environment. Select the checkbox and select a company to connect click on Connect to QuickBooks Online.
  3. Enter your credentials and click Sign In.
  4. Follow the QuickBooks Online screens and fill out the required information, then click Connect.


  5. You will be brought back to Pipelines with categories expanded for the Quickbooks Online channel.

How to reconnect

You may need to reconnect your account to a channel. Reasons may be (but not limited to):

  • If you need to connect a different account.
  • Authorization updates, such as a changed password.
  • Editing the access rights that Pipelines has to the channel.

To reconnect:

  1. Select a pipeline that already has this channel in it.
  2. Open a step that contains this channel.
  3. Under account, select Connect (or reconnect) and follow the process above, How to connect.

Steps

The steps you can use with QuickBooks fall into a seven categories:

  • Customers

  • Vendors

  • Time Activities

  • Bills

  • Items

  • Invoices

  • Expenses

Note: You cannot use jinja expressions in trigger conditions unless explicitly specified as possible.

Type Name Description
Customers
Trigger Customer Created Triggers when a new customer is created in the selected account.
Trigger Customer Updated Triggers when a customer is updated in the selected account.
Action Create a Customer Creates a new customer.
Action Update a Customer Updates the selected customer.
Query Search Customers Searches for customers.
Action Fetch a Linked Customer Fetches a linked customer.
Action Look Up a Customer Looks for a customer and returns a single item.
Vendors
Trigger Vendor Created Triggers when a new vendor is created in the selected account.
Trigger Vendor Updated Triggers when a vendor is updated in the selected account.
Action Create a Vendor Creates a new vendor.
Action Update a Vendor Updates the selected vendor.
Query Search Vendors Searches for vendors.
Action Fetch a Linked Vendor Fetches a linked vendor.
Action Look Up a Vendor Looks for a vendor and returns a single item.
Time Activities
Trigger Time Activity Created Triggers when a new time activity is created in the selected account.
Trigger Time Activity Updated Triggers when a time activity is updated in the selected account.
Action Create a Time Activity Creates a new time activity.
Action Update a Time Activity Updates the selected time activity.
Query Search Time Activities Searches for time activities in the selected account and returns a list.
Action Fetch a Linked Time Activity Fetches a linked time activity.
Action Look Up a Time Activity Looks for a time activity and returns a single item.
Bills
Trigger Bill Created Triggers when a new bill is created in the selected account.
Trigger Bill Updated Triggers when a bill is updated in the selected account.
Action Create a Bill Creates a new bill.
Action Update a Bill Updates the selected bill.
Query Search Bills Searches for bills.
Action Fetch a Linked Bill Fetches a linked bill.
Action Look Up a Bill Looks for a bill and returns a single item.

Items
Trigger Item Created Triggers when an item is created in the selected account.
Trigger Item Updated Triggers when an item is updated in the selected account.
Action Create an Item Creates a new item.
Action Update an Item Updates the selected item.
Query Search Items Searches for items.
Action Fetch a Linked Item Fetches a linked item.
Action Look Up an Item Looks for an item and returns a single item.
Invoices
Trigger Invoice Created Triggers when an invoice is created in the selected account.
Trigger Invoice Updated Triggers when an invoice is updated in the selected account.
Action Create an Invoice Creates a new invoice.
Action Update an Invoice Updates the selected invoice.
Query Search Invoices Searches for invoices.
Action Fetch a Linked Invoice Fetches a linked invoice.
Action Look Up an Invoice Looks for an invoice and returns a single item.
Expenses
Trigger Expense Created Triggers when an expense is created in the selected account.
Trigger Expense Updated Triggers when an expense is updated in the selected account.
Action Create an Expense Creates a new expense.
Action Update an Expense Updates the selected expense.
Query Search Expenses Searches for expense.
Action Fetch a Linked Expense Fetches a linked expense.
Action Look Up an Expense Looks for an expense and returns a single item.

How to use QuickBooks Online Projects in a pipeline

Turn on the Projects feature in QuickBooks Online system:

  1. Select Settings, then select Account and Settings
  2. Go to the Advanced tab.
  3. Find the Projects section and select Edit to expand it.
  4. Switch on the Projects toggle.
  5. Select Save and close your settings.

Create a project:

  1. Select the Projects menu then select Project.
  2. Give your project a memorable name, i.e: Application Development.
  3. Select the customer you are working for from the drop-down menu and add any notes or details about the project.
  4. Click Save.

Track income and expenses example:

  1. In Pipelines, go to QuickBooks Online channel and from the Expenses category drag the Create Expense action onto the pipeline builder.
  2. Populate the required fields and in the Category details section. In the Customer or Project dropdown, select the project you created in the previous steps.
  3. Run the pipeline.

Go into your project dashboard in QuickBooks Online and you should notice that COSTS are increased with the Amount value. You can also track invoices, bills, time activities for projects in a similar way by selecting your project from the customer or project dropdown.

Use case example

In this common use case for invoice creation we want to iterate through line items in Quickbase, and then populate them in the QuickBooks channel.

  1. Starting with a Line Items Table in Quickbase, we search those records and the Search pipe returns a list of records.
  2. The Invoice has a “Lines” section that is a “List”. We pipe the Search Records -> This section, and Pipeline creates a list of line items with individual records.

Using Pipelines to create QuickBooks invoices from data in Quickbase

This is a high-level overview of the steps you would need to create Invoices with multiple line-items in QuickBooks based on data stored in a Quickbase App. To illustrate the implementation, we made a simple demo App containing key elements of the pattern. The implementation relies on a few key tables to describe customers, invoices, and line-items. Then, we use Pipelines to move data FROM Quickbase TO QuickBooks. Most of this design pattern is straight forward, but the creation of multiple line-items in an invoice employs a non-standard design element that you'll need to understand.

Demo App

The demo App is based on data provided by QuickBooks when you create a sample instance in the QuickBooks sandbox. This app relies on a few key tables and table-to-table relationships to function properly. Without these three tables, the design would become much more complicated. The key tables are:

  • Customer: This table has the usual fields for a customer, which include Customer-Name, Customer Address, Customer-Billing-Address, Customer-Ship-to-Address, etc.

  • Invoices: This is a list of all invoices that we have in the system. This table contains typical Invoice- Header information, such as Billing-Address, Invoice-Date, Terms (e.g., Net-30, Net-60), etc.

  • Invoice-Line-Items: This table contains data for each line-item that exists in an Invoice. It contains many fields that are used in a QuickBooks line-item – most of which are optional.

    The entity-relationship diagram for these three tables:.

The entity-relationship diagram .png

 

The Invoices Table

The following Invoices table is shown with a few sample data records.

invoices.jpg

  • INTERNAL Invoice ID: This is just an internal ID used by the App. It has no effect on the implementation.

  • Invoice Date: The date when the invoice was issued. It will be sent to QuickBooks for the given invoice.

  • Customer-QuickBOOKsID: The customer ID used by QuickBooks.

  • Customer-NAME: The name of the customer in QuickBOOKs.

  • # of Line Items: A summary field from the relationship between the Invoice and Line-Items tables

  • Inv Total: A summation of sub-totals from Line-Items for this invoice.

  • Terms: This is the payment terms for this invoice. It must match the terms used in QuickBooks.

  • Send to QuickBOOKs: The is the flag we use to trigger a pipeline that will export data FROM Quickbase and create a new invoice in QuickBooks.

    Not shown in this report are two fields that will be used to associate invoices in QuickBOOKs to invoices in Quickbase. They are QuickBooks Invoice ID and QuickBooks Doc Number. These values are obtained from the response of the “Create an Invoice” step in the pipeline and will be described below.

The Invoice Line Items Table

A sample report listing the line-items for invoice INV_0002:

line items.jpg

  • my_Line_Item_ID: The line-item number that will be used in the given QuickBooks invoice.

  • LI_Type: The product type that will be used in the QuickBooks invoice. This is an optional field, but if it is used, the value must match the available type fields used in your instance of QuickBooks.

  • LI_Product_Category: The product category that will be used in the QuickBooks invoice. This is an optional field, but if it used, the value must match the available category fields used in your instance of QuickBooks.

  • LI_Product_Name: The product name that will be used in the QuickBooks invoice. This is an optional field, but if it used, the value must match the available name fields used in your instance of QuickBooks.

  • LI_Qty: The count of the number of items sold for this line-item. (We will discuss this field more below).

  • LI_Rate_Per: The selling price per item for this line-item that will be used in the QuickBooks invoice. Currently, the Pipelines channel for the QuickBooks “Create an Invoice” step does not support this field. (We will discuss this field more below).

  • LI_Sub_Total: The sub-total for this line-item that will be used in the QuickBooks invoice. Essentially sub-total = qty * rate. (We will discuss this field more below).

 

Creating a Pipeline

Next, we created a Pipeline that will trigger on an update event in the Invoices table. The pipeline then creates the invoice with multiple line-items in the QuickBooks system. But before that Pipeline is triggered, we need to have all the records in place for the given invoice and related line-items. There are many ways to build an invoice with line-items, as well as the triggering the pipeline. For the purposes of this use case, we’re using a simplified process. We created a Check-Box field (Send to QuickBOOKs) in the Invoice record that will trigger the Pipeline when set to TRUE. Once we have completed building the invoice and line-items, the checkbox is manually set, triggering the Pipeline.

QuickBase Processing in The Demo App

We begin the process at a given Customer record. In our App, we implemented a button called “Create Invoice” that creates a new Invoice record in the Invoice table. The Invoice record is auto-populated with data from the Customer table, such as Customer Name, Billing-Address, Ship-to-Address, Invoice-Date, Terms, etc. 

Next, the user goes to the new Invoice record and adds one or more Line-item records. Each Line-Item has a few key fields necessary for this implementation. They are the line-item-number, product-name, quantity, and sub-total.

At this point, we need to highlight one key element about these fields. Currently, the Pipelines channel for QuickBooks does not support the “rate” field. However, we can send the quantity and the subtotal. One way or another, you will need to have values in the quantity and subtotal fields that make sense for your business prior to triggering the pipeline. If you MUST have the rate field entered on a given Line-Item in QuickBooks, you’ll need to resort to using low-level REST API calls.

Once the Invoice has been created and all the line-items have been added to the invoice, we’re ready to trigger the pipeline that sends data to QuickBooks. In our case we have a checkbox called “Send to QuickBooks” which will trigger the pipeline when it gets checked.

The Quickbase to QuickBooks Pipeline Control Structure

For the most part, this pipeline is rather simple, but as mentioned earlier there is one key element that you'll need to understand. In most pipelines, if you wanted to traverse a list of records and perform an operation on each record (e.g., update a record), you would use a Search Records step, followed by a For Each Loop that looks similar to the following screenshot. But you can't use this pattern to insert multiple line-items onto an invoice in QuickBooks.

Control Structure.png

In the case of the QuickBooks channel, you still use the Search Records step, but you do NOT use the For Each Loop step. Rather, you place the “Create an Invoice” step immediately after the Search Records step.

The Create an Invoice step includes an embedded loop as part of its functionality. This is the non- standard part of this pipeline step. So how do the each of the line-item objects get created in QuickBooks?

Create an Invoice.png

The Create Invoice Step

The following screenshot shows the Create an Invoice step. It has standard fields used for an invoice header, followed by a “block” of fields, labelled as “Lines”.

create 2.png

The Create an Invoice step expects a list of records as input to that step. That’s why you need a Search Records step preceding this step.

The key to this step is that the Lines block contains a list of fields used for each line-item. Note the gray “box” that surrounds the fields inside the Lines block. Each of the fields in the Lines block will be a part of a given line-item. This is where you enter the information for each line-item. There is an implicit loop that operates on fields in the Lines block.

The Line Number field is a key element in this step. In the screenshot to the right, the Line Number field has a value of {{b.my_line_item_id}}. That data came from the previous Search Records step (i.e., Step B). The Line Number field is required, but most of the others are optional. All fields in the Lines block pertain to the given line-item.

Persisting the Invoice Id

Once the invoice has been created, the Create an Invoice step will return two data objects that identify the invoice. They are “id” and “Doc Number”. The “id” field is identical to a Record-id in Quickbase. The “Doc Number” is an identifier of the invoice number from an accounting point of view. For example, you can go into QuickBooks and change the invoice number for a given invoice from “1984” to “2001”. The “id” will remain the same.

It’s important to note in the context of QuickBooks, the “Doc Number” is more than just a name, as it is used to track payables and other related accounting / journal entries. We recommend that you persist these values in the invoice record in Quickbase, which will be helpful if you ever need to do accounting actions from Quickbase.

Other Information

If you need to create QuickBooks Sub-Customers or QuickBooks Rate fields in an invoice, you will need to use low-level REST APIs via a Make Request step, as they are not currently supported in Pipelines. In this scenario you will need to set up OAuth2 in a manual fashion. We also have information regarding the Pipeline steps necessary to implement these use-cases.

 

Related articles

Top