Data Model

The CData Excel Add-In for QuickBooks models QuickBooks data as an easy-to-use SQL database. There are three parts to the data model: tables, views, and stored procedures.

Tables

The Tables section, which details standard SQL tables, and the Views section, which lists read-only SQL tables, contain samples of what you might have access to in your QuickBooks account.

Common tables include:


Table	Description
Accounts	Manages accounts, allowing users to create, update, delete, and query account data.
Customers	Handles customers, supporting creation, updates, deletion, and queries for managing customer data effectively.
Vendors	Manages vendors, supporting creation, updates, deletion, and queries for supplier relationship management.
Invoices	Handles invoices, supporting creation, updates, deletion, and queries for billing and receivables tracking.
Bills	Handles the management of bills, including creating, updating, deleting, and querying data for expense tracking.
ReceivePayments	Manages receive payment transactions, supporting creation, updates, deletion, and queries for customer payments.
Items	Manages items, including creation, updates, deletion, and queries for inventory and service tracking.
JournalEntries	Handles journal entries, supporting creation, updates, deletion, and queries for accounting adjustments.
Employees	Manages employees, allowing creation, updates, deletion, and queries for employee records and payroll data.
PurchaseOrders	Handles purchase orders, supporting creation, updates, deletion, and queries for supplier management.
SalesReceipts	Handles sales receipts, supporting creation, updates, deletion, and queries for tracking point-of-sale transactions.
CreditMemos	Manages credit memos, supporting creation, updates, deletion, and queries for issuing and tracking customer credits.
Estimates	Handles estimates, including creation, updates, deletion, and queries for tracking potential sales.
TimeTracking	Manages time tracking events, supporting creation, updates, deletion, and queries for employee or job time entries.
Deposits	Manages deposits, allowing users to create, update, delete, and query financial deposits.
Transfers	Manages transfers, supporting creation, updates, and queries for tracking movement of funds between accounts.
CreditCardCharges	Manages credit card charges, allowing users to create, update, delete, and query financial transactions.
CreditCardCredits	Manages credit card credits, allowing users to create, update, delete, and query credit transactions for accurate accounting.
SalesOrders	Handles sales orders, supporting creation, updates, deletion, and queries for tracking customer orders.
PaymentMethods	Handles payment methods, supporting creation, updates, deletion, and queries for payment categorization.

Note: During bulk insert operations, the add-in only supports multiple values for aggregate columns. Other columns allow single values.

The add-in uses the QuickBooks API to process supported filters. The add-in processes other filters client-side within the add-in.

Stored Procedures

Stored Procedures are actions that are invoked via SQL queries. They perform tasks beyond standard CRUD operations, including exposing QuickBooks reports as tables, merging accounts, and voiding transactions.