# QuickBooks Online style data dictionary

Sample database `qbo` on learnsql.cpetoday.com (SQL Server login: CPE Today course). Each table lists its columns with the type, keys, what the column means, and, for codes and statuses, the values seen in the sample copy (with row counts when the dictionary was written).
20 of 47 tables are described. Undocumented tables are listed at the end.

## Accounts

The chart of accounts. Use Classification and AccountType to group accounts for financial statements.

Grain: one row per general ledger account

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `Id` | nvarchar(255) | PK | Unique account ID. |  |
| `SyncToken` | nvarchar(2000) |  |  |  |
| `MetaData_CreateTime` | smalldatetime |  |  |  |
| `MetaData_LastUpdatedTime` | smalldatetime |  |  |  |
| `Name` | nvarchar(100) |  | Account name. |  |
| `SubAccount` | bit |  |  |  |
| `ParentRef` | nvarchar(255) |  |  |  |
| `ParentRef_Name` | nvarchar(100) |  | Parent account, for sub-accounts. |  |
| `FullyQualifiedName` | nvarchar(2000) |  | Full name including parent accounts, such as Checking:Operating. |  |
| `Description` | nvarchar(100) |  |  |  |
| `Active` | bit |  | True while the account can be used in new transactions. | True (89), False (1) |
| `Classification` | nvarchar(25) |  | Accounting class: Asset, Liability, Equity, Revenue or Expense. | Expense (49), Revenue (22), Asset (10), Liability (7), Equity (2) |
| `AccountType` | nvarchar(100) |  | Broad account type, such as Bank, Accounts Receivable, Accounts Payable, Income, Expense or Equity. |  |
| `AccountSubType` | nvarchar(100) |  | Finer type under AccountType, such as Checking or Services. |  |
| `AcctNum` | nvarchar(2000) |  | Account number from the chart of accounts. |  |
| `CurrentBalance` | decimal |  | Current balance of the account. |  |
| `CurrentBalanceWithSubAccounts` | decimal |  |  |  |
| `CurrencyRef` | nvarchar(255) |  |  |  |
| `CurrencyRef_Name` | nvarchar(2000) |  |  |  |
| `TaxCodeRef` | nvarchar(255) |  |  |  |

## BillLineItems

One row per bill line: the expense account or item, class and amount. Join to Bills on BillId.

Grain: one row per bill line

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `LineId` | nvarchar(255) | PK | Line number within the bill. | 1 (15), 2 (1) |
| `BillId` | nvarchar(255) | PK FK to Bills | Bill this line belongs to (Bills.Id). |  |
| `SyncToken` | nvarchar(2000) |  |  |  |
| `MetaData_CreateTime` | smalldatetime |  |  |  |
| `MetaData_LastUpdatedTime` | smalldatetime |  |  |  |
| `DocNumber` | nvarchar(21) |  |  |  |
| `TxnDate` | datetime |  |  |  |
| `PrivateNote` | nvarchar(4000) |  |  |  |
| `LinkedTxnAggregate` | text(2147483647) |  |  |  |
| `Line_Id` | nvarchar(2000) |  |  |  |
| `Line_Amount` | decimal |  | Amount of this line. |  |
| `Line_Description` | nvarchar(2000) |  | Description of the line. | (blank) (10), Building Lease (1), Fountain Pump (1), Lumber (1), Monthly Phone Bill (1), Repairs on the truck (1), Rock Fountain (1) |
| `Line_DetailType` | nvarchar(2000) |  | Kind of line, such as AccountBasedExpenseLineDetail or ItemBasedExpenseLineDetail. | AccountBasedExpenseLineDetail (14), ItemBasedExpenseLineDetail (2) |
| `Line_AccountBasedExpenseLineDetail_ClassRef` | nvarchar(255) |  |  |  |
| `Line_AccountBasedExpenseLineDetail_ClassRef_Name` | nvarchar(255) |  |  |  |
| `Line_AccountBasedExpenseLineDetail_CustomerRef` | nvarchar(255) | FK to Customers |  |  |
| `Line_AccountBasedExpenseLineDetail_CustomerRef_Name` | nvarchar(100) |  | Customer or job the expense is billable to. | (blank) (15), Travis Waldron (1) |
| `Line_AccountBasedExpenseLineDetail_AccountRef` | nvarchar(255) | FK to Accounts |  |  |
| `Line_AccountBasedExpenseLineDetail_AccountRef_Name` | nvarchar(100) |  | Expense account charged on the line. | Miscellaneous (3), (blank) (2), Legal & Professional Fees:Accounting (2), Utilities:Gas and Electric (2), Utilities:Telephone (2), Insurance (1), Job Expenses:Job Materials:Decks and Patios (1), Legal & Professional Fees:Lawyer (1), Maintenance and Repair:Equipment Repairs (1), Rent or Lease (1) |
| `Line_AccountBasedExpenseLineDetail_TaxCodeRef` | nvarchar(255) |  |  |  |
| `Line_AccountBasedExpenseLineDetail_TaxAmount` | decimal |  |  |  |
| `Line_AccountBasedExpenseLineDetail_TaxInclusiveAmt` | decimal |  |  |  |
| `Line_AccountBasedExpenseLineDetail_BillableStatus` | nvarchar(2000) |  | Whether the expense can be passed on to the customer, such as Billable, NotBillable or HasBeenBilled. | NotBillable (13), (blank) (2), HasBeenBilled (1) |
| `Line_AccountBasedExpenseLineDetail_MarkupInfo_Value` | decimal |  |  |  |
| `Line_AccountBasedExpenseLineDetail_MarkupInfo_Percent` | float |  |  |  |
| `Line_AccountBasedExpenseLineDetail_MarkupInfo_PriceLevelRef` | nvarchar(255) |  |  |  |
| `Line_ItemBasedExpenseLineDetail_ItemRef` | nvarchar(255) | FK to Items |  |  |
| `Line_ItemBasedExpenseLineDetail_ItemRef_Name` | nvarchar(100) |  |  |  |
| `Line_ItemBasedExpenseLineDetail_ClassRef` | nvarchar(255) |  |  |  |
| `Line_ItemBasedExpenseLineDetail_ClassRef_Name` | nvarchar(255) |  |  |  |
| `Line_ItemBasedExpenseLineDetail_UnitPrice` | decimal |  |  |  |
| `Line_ItemBasedExpenseLineDetail_Qty` | float |  |  |  |
| `Line_ItemBasedExpenseLineDetail_CustomerRef` | nvarchar(255) |  |  |  |
| `Line_ItemBasedExpenseLineDetail_CustomerRef_Name` | nvarchar(100) |  |  |  |
| `Line_ItemBasedExpenseLineDetail_TaxCodeRef` | nvarchar(255) |  |  |  |
| `Line_ItemBasedExpenseLineDetail_BillableStatus` | nvarchar(2000) |  |  |  |
| `Line_ItemBasedExpenseLineDetail_MarkupInfo_Value` | float |  |  |  |
| `Line_ItemBasedExpenseLineDetail_MarkupInfo_Percent` | float |  |  |  |
| `Line_ItemBasedExpenseLineDetail_MarkupInfo_PriceLevelRef` | nvarchar(255) |  |  |  |
| `Line_ItemBasedExpenseLineDetail_MarkupInfo_PriceLevelRef_Name` | nvarchar(2000) |  |  |  |
| `VendorRef` | nvarchar(255) | FK to Vendors |  |  |
| `VendorRef_Name` | nvarchar(100) |  |  |  |
| `APAccountRef` | nvarchar(255) | FK to Accounts |  |  |
| `APAccountRef_Name` | nvarchar(100) |  |  |  |
| `TotalAmt` | decimal |  |  |  |
| `SalesTermRef` | nvarchar(255) | FK to Terms |  |  |
| `SalesTermRef_Name` | nvarchar(31) |  |  |  |
| `DueDate` | datetime |  |  |  |
| `Balance` | decimal |  |  |  |
| `DepartmentRef` | nvarchar(255) |  |  |  |
| `DepartmentRef_Name` | nvarchar(100) |  |  |  |
| `TxnTaxDetail_TxnTaxCodeRef` | nvarchar(255) |  |  |  |
| `TxnTaxDetail_TotalTax` | nvarchar(2000) |  |  |  |
| `TxnTaxDetail_TaxLineAggregate` | text(2147483647) |  |  |  |
| `CurrencyRef` | nvarchar(255) |  |  |  |
| `CurrencyRef_Name` | nvarchar(2000) |  |  |  |
| `ExchangeRate` | decimal |  |  |  |
| `GlobalTaxCalculation` | nvarchar(2000) |  |  |  |

## BillPayments

Payments made to vendors, one row per payment (check, cash or card).

Grain: one row per vendor payment

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `Id` | nvarchar(255) | PK | Unique vendor payment ID. | 104 (1), 117 (1), 118 (1), 22 (1), 37 (1), 50 (1), 77 (1), 79 (1), 82 (1), 91 (1) |
| `SyncToken` | nvarchar(2000) |  |  |  |
| `MetaData_CreateTime` | smalldatetime |  |  |  |
| `MetaData_LastUpdatedTime` | smalldatetime |  |  |  |
| `DocNumber` | nvarchar(21) |  |  |  |
| `TxnDate` | datetime |  | Date the payment was made. | 2021-04-09T00:00:00 (2), 2021-04-10T00:00:00 (2), 2021-04-11T00:00:00 (2), 2021-01-07T00:00:00 (1), 2021-03-21T00:00:00 (1), 2021-04-04T00:00:00 (1), 2021-04-08T00:00:00 (1) |
| `PrivateNote` | nvarchar(4000) |  |  |  |
| `DepartmentRef` | nvarchar(255) |  |  |  |
| `DepartmentRef_Name` | nvarchar(100) |  |  |  |
| `LineAggregate` | text(2147483647) |  |  |  |
| `VendorRef` | nvarchar(255) | FK to Vendors |  |  |
| `VendorRef_Name` | nvarchar(100) |  | Vendor paid. | Cal Telephone (2), Books by Bessie (1), Brosnahan Insurance Agency (1), Hall Properties (1), Hicks Hardware (1), Norton Lumber and Building Materials (1), PG&E (1), Robertson & Associates (1), Tim Philip Masonry (1) |
| `APAccountRef` | nvarchar(255) |  |  |  |
| `APAccountRef_Name` | nvarchar(100) |  |  |  |
| `PayType` | nvarchar(100) |  | How the payment was made, such as Check or CreditCard. | Check (7), CreditCard (3) |
| `CheckPayment_BankAccountRef` | nvarchar(255) | FK to Accounts |  |  |
| `CheckPayment_BankAccountRef_Name` | nvarchar(100) |  |  |  |
| `CheckPayment_PrintStatus` | nvarchar(2000) |  |  |  |
| `CreditCardPayment_CCAccountRef` | nvarchar(255) | FK to Accounts |  |  |
| `CreditCardPayment_CCAccountRef_Name` | nvarchar(100) |  |  |  |
| `TotalAmt` | decimal |  | Amount paid. | 56.5 (1), 74.36 (1), 75.0 (1), 103.55 (1), 114.09 (1), 250.0 (1), 300.0 (1), 666.0 (1), 900.0 (1), 2000.0 (1) |
| `ProcessBillPayment` | bit |  |  |  |
| `CurrencyRef` | nvarchar(255) |  |  |  |
| `CurrencyRef_Name` | nvarchar(2000) |  |  |  |
| `ExchangeRate` | decimal |  |  |  |

## Bills

Bills received from vendors, one row per bill. Lines are in BillLineItems.

Grain: one row per bill

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `Id` | nvarchar(255) | PK | Unique bill ID. |  |
| `SyncToken` | nvarchar(2000) |  |  |  |
| `MetaData_CreateTime` | smalldatetime |  |  |  |
| `MetaData_LastUpdatedTime` | smalldatetime |  |  |  |
| `DocNumber` | nvarchar(21) |  | Vendor's bill or reference number. |  |
| `TxnDate` | datetime |  | Bill date. | 2021-03-26T00:00:00 (3), 2021-04-04T00:00:00 (2), 2021-04-11T00:00:00 (2), 2020-12-08T00:00:00 (1), 2021-02-24T00:00:00 (1), 2021-02-25T00:00:00 (1), 2021-03-05T00:00:00 (1), 2021-03-27T00:00:00 (1), 2021-03-28T00:00:00 (1), 2021-04-08T00:00:00 (1), 2021-04-09T00:00:00 (1) |
| `PrivateNote` | nvarchar(2000) |  |  |  |
| `LinkedTxnAggregate` | text(2147483647) |  |  |  |
| `LineAggregate` | text(2147483647) |  |  |  |
| `VendorRef` | nvarchar(255) | FK to Vendors |  |  |
| `VendorRef_Name` | nvarchar(100) |  | Vendor who sent the bill. | Brosnahan Insurance Agency (2), Cal Telephone (2), Norton Lumber and Building Materials (2), PG&E (2), Robertson & Associates (2), Books by Bessie (1), Diego's Road Warrior Bodyshop (1), Hall Properties (1), Hicks Hardware (1), Tim Philip Masonry (1) |
| `APAccountRef` | nvarchar(255) | FK to Accounts |  |  |
| `APAccountRef_Name` | nvarchar(100) |  | Accounts Payable account that carries the bill. |  |
| `TotalAmt` | decimal |  | Bill total including tax. |  |
| `SalesTermRef` | nvarchar(255) | FK to Terms |  |  |
| `SalesTermRef_Name` | nvarchar(31) |  | Payment terms, such as Net 30. |  |
| `DueDate` | datetime |  | Date the bill is due. | 2021-04-25T00:00:00 (3), 2021-03-27T00:00:00 (2), 2021-04-11T00:00:00 (2), 2021-01-07T00:00:00 (1), 2021-02-24T00:00:00 (1), 2021-03-05T00:00:00 (1), 2021-04-04T00:00:00 (1), 2021-04-12T00:00:00 (1), 2021-04-14T00:00:00 (1), 2021-05-08T00:00:00 (1), 2021-05-09T00:00:00 (1) |
| `Balance` | decimal |  | Amount still owed on the bill. Zero means paid. | 0.0 (10), 86.44 (1), 205.0 (1), 241.23 (1), 315.0 (1), 755.0 (1) |
| `DepartmentRef` | nvarchar(255) |  |  |  |
| `DepartmentRef_Name` | nvarchar(100) |  |  |  |
| `TxnTaxDetail_TxnTaxCodeRef` | nvarchar(255) |  |  |  |
| `TxnTaxDetail_TotalTax` | nvarchar(2000) |  |  |  |
| `TxnTaxDetail_TaxLineAggregate` | text(2147483647) |  |  |  |
| `CurrencyRef` | nvarchar(255) |  |  |  |
| `CurrencyRef_Name` | nvarchar(2000) |  |  |  |
| `ExchangeRate` | decimal |  |  |  |
| `GlobalTaxCalculation` | nvarchar(2000) |  |  |  |

## Class

Classes, used to tag transactions by business unit, location or program.

Grain: one row per class

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `Id` | nvarchar(255) |  |  |  |
| `SyncToken` | nvarchar(2000) |  |  |  |
| `MetaData_CreateTime` | smalldatetime |  |  |  |
| `MetaData_LastUpdatedTime` | smalldatetime |  |  |  |
| `Name` | nvarchar(100) |  | Class name. |  |
| `SubClass` | bit |  |  |  |
| `ParentRef` | nvarchar(255) |  |  |  |
| `ParentRef_Name` | nvarchar(100) |  |  |  |
| `Active` | bit |  | True while the class can be used. |  |
| `FullyQualifiedName` | nvarchar(500) |  | Full name including the parent class. |  |

## Customers

Customers and customer jobs. Jobs are sub-customers, marked IsProject.

Grain: one row per customer or job

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `Id` | nvarchar(255) | PK | Unique customer ID inside QuickBooks Online. |  |
| `SyncToken` | nvarchar(2000) |  |  |  |
| `MetaData_CreateTime` | smalldatetime |  |  |  |
| `MetaData_LastUpdatedTime` | smalldatetime |  |  |  |
| `Title` | nvarchar(15) |  |  |  |
| `GivenName` | nvarchar(100) |  |  |  |
| `MiddleName` | nvarchar(100) |  |  |  |
| `FamilyName` | nvarchar(100) |  |  |  |
| `Suffix` | nvarchar(16) |  |  |  |
| `FullyQualifiedName` | nvarchar(2000) |  |  |  |
| `CompanyName` | nvarchar(50) |  | Company name, when the customer is a business. |  |
| `DisplayName` | nvarchar(100) |  | Name shown in lists and on documents. |  |
| `PrintOnCheckName` | nvarchar(110) |  |  |  |
| `Active` | bit |  | True while the customer is active. Inactive customers are hidden in pick lists. |  |
| `IsProject` | bit |  | True when the record is a job under a parent customer. |  |
| `PrimaryPhone_FreeFormNumber` | nvarchar(30) |  |  |  |
| `AlternatePhone_FreeFormNumber` | nvarchar(30) |  |  |  |
| `Mobile_FreeFormNumber` | nvarchar(30) |  |  |  |
| `Fax_FreeFormNumber` | nvarchar(30) |  |  |  |
| `PrimaryEmailAddr_Address` | nvarchar(100) |  | Main email address. |  |
| `WebAddr_URI` | nvarchar(1000) |  |  |  |
| `DefaultTaxCodeRef` | nvarchar(255) |  |  |  |
| `Taxable` | bit |  | True when sales to this customer are taxable by default. | False (27), True (2) |
| `CustomerTypeRef` | nvarchar(255) |  |  |  |
| `BillAddr_Id` | nvarchar(2000) |  |  |  |
| `BillAddr_Line1` | nvarchar(500) |  |  |  |
| `BillAddr_Line2` | nvarchar(500) |  |  |  |
| `BillAddr_Line3` | nvarchar(500) |  |  |  |
| `BillAddr_Line4` | nvarchar(500) |  |  |  |
| `BillAddr_Line5` | nvarchar(500) |  |  |  |
| `BillAddr_City` | nvarchar(255) |  | Billing city. | Middlefield (8), Menlo Park (5), Bayshore (4), Half Moon Bay (3), Palo Alto (2), Tucson (2), (blank) (1), Maplewood (1), Monlo Park (1), San Jose (1), South Orange (1) |
| `BillAddr_Country` | nvarchar(255) |  |  |  |
| `BillAddr_CountrySubDivisionCode` | nvarchar(255) |  | Billing state or province code. | CA (24), AZ (2), NJ (2), (blank) (1) |
| `BillAddr_PostalCode` | nvarchar(31) |  |  |  |
| `BillAddr_Lat` | nvarchar(2000) |  |  |  |
| `BillAddr_Long` | nvarchar(2000) |  |  |  |
| `ShipAddr_Id` | nvarchar(2000) |  |  |  |
| `ShipAddr_Line1` | nvarchar(500) |  |  |  |
| `ShipAddr_Line2` | nvarchar(500) |  |  |  |
| `ShipAddr_Line3` | nvarchar(500) |  |  |  |
| `ShipAddr_Line4` | nvarchar(500) |  |  |  |
| `ShipAddr_Line5` | nvarchar(500) |  |  |  |
| `ShipAddr_City` | nvarchar(255) |  |  |  |
| `ShipAddr_Country` | nvarchar(255) |  |  |  |
| `ShipAddr_CountrySubDivisionCode` | nvarchar(255) |  |  |  |
| `ShipAddr_PostalCode` | nvarchar(31) |  |  |  |
| `ShipAddr_Note` | nvarchar(2000) |  |  |  |
| `ShipAddr_Lat` | nvarchar(2000) |  |  |  |
| `ShipAddr_Long` | nvarchar(2000) |  |  |  |
| `Notes` | nvarchar(2000) |  |  |  |
| `Job` | bit |  |  |  |
| `BillWithParent` | bit |  |  |  |
| `ParentRef` | nvarchar(255) |  |  |  |
| `ParentRef_Name` | nvarchar(100) |  | Parent customer, for jobs. |  |
| `Level` | int |  |  |  |
| `SalesTermRef` | nvarchar(255) |  |  |  |
| `SalesTermRef_Name` | nvarchar(31) |  | Default payment terms for this customer. |  |
| `PaymentMethodRef` | nvarchar(255) |  |  |  |
| `PaymentMethodRef_Name` | nvarchar(31) |  |  |  |
| `Balance` | decimal |  | Open amount the customer owes. Positive means receivable. |  |
| `OpenBalanceDate` | datetime |  |  |  |
| `BalanceWithJobs` | decimal |  |  |  |
| `PreferredDeliveryMethod` | nvarchar(2000) |  |  |  |
| `ResaleNum` | nvarchar(16) |  |  |  |
| `CurrencyRef` | nvarchar(255) |  |  |  |
| `CurrencyRef_Name` | nvarchar(2000) |  |  |  |

## Departments

Departments or locations used to report transactions by unit.

Grain: one row per department

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `Id` | nvarchar(255) |  |  |  |
| `SyncToken` | nvarchar(2000) |  |  |  |
| `MetaData_CreateTime` | smalldatetime |  |  |  |
| `MetaData_LastUpdatedTime` | smalldatetime |  |  |  |
| `Name` | nvarchar(100) |  | Department name. |  |
| `SubDepartment` | bit |  |  |  |
| `ParentRef` | nvarchar(255) |  |  |  |
| `ParentRef_Name` | nvarchar(100) |  |  |  |
| `FullyQualifiedName` | nvarchar(2000) |  | Full name including the parent. |  |
| `Active` | bit |  | True while the department can be used. |  |

## Deposits

Bank deposits, one row per deposit slip.

Grain: one row per deposit

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `Id` | nvarchar(255) | PK | Unique deposit ID. | 102 (1), 121 (1), 4 (1), 5 (1), 62 (1) |
| `SyncToken` | nvarchar(2000) |  |  |  |
| `MetaData_CreateTime` | smalldatetime |  |  |  |
| `MetaData_LastUpdatedTime` | smalldatetime |  |  |  |
| `TxnDate` | date |  | Deposit date. | 2020-11-26 (1), 2021-04-07 (1), 2021-04-09 (1), 2021-04-10 (1), 2021-04-11 (1) |
| `PrivateNote` | nvarchar(4000) |  |  |  |
| `LineAggregate` | text(2147483647) |  |  |  |
| `TxnTaxDetail_TxnTaxCodeRef` | nvarchar(255) |  |  |  |
| `TxnTaxDetail_TotalTax` | nvarchar(2000) |  |  |  |
| `TxnTaxDetail_TaxLineAggregate` | text(2147483647) |  |  |  |
| `DepositToAccountRef` | nvarchar(255) | FK to Accounts |  |  |
| `DepositToAccountRef_Name` | nvarchar(100) |  | Bank account the deposit went into. | Checking (4), Savings (1) |
| `ClassRef` | nvarchar(255) |  |  |  |
| `ClassRef_Name` | nvarchar(255) |  |  |  |
| `TotalAmt` | decimal |  | Deposit total. | 218.75 (1), 408.0 (1), 600.0 (1), 868.15 (1), 5000.0 (1) |
| `CashBack_AccountRef` | nvarchar(255) | FK to Accounts |  |  |
| `CashBack_AccountRef_Name` | nvarchar(100) |  |  |  |
| `CashBack_Amount` | decimal |  |  |  |
| `CashBack_Memo` | nvarchar(2000) |  |  |  |
| `DepartmentRef` | nvarchar(255) |  |  |  |
| `DepartmentRef_Name` | nvarchar(100) |  |  |  |
| `ApplyTaxAfterDiscount` | bit |  |  |  |
| `ExchangeRate` | decimal |  |  |  |
| `CurrencyRef` | nvarchar(255) |  |  |  |
| `CurrencyRef_Name` | nvarchar(2000) |  |  |  |
| `GlobalTaxCalculation` | nvarchar(2000) |  |  |  |

## Employees

Employees on payroll and time tracking.

Grain: one row per employee

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `Id` | nvarchar(255) | PK | Unique employee ID. | 54 (1), 55 (1) |
| `SyncToken` | nvarchar(2000) |  |  |  |
| `MetaData_CreateTime` | smalldatetime |  |  |  |
| `MetaData_LastUpdatedTime` | smalldatetime |  |  |  |
| `Organization` | bit |  |  |  |
| `Title` | nvarchar(15) |  |  |  |
| `EmployeeNumber` | nvarchar(100) |  |  |  |
| `GivenName` | nvarchar(25) |  |  |  |
| `MiddleName` | nvarchar(25) |  |  |  |
| `FamilyName` | nvarchar(25) |  |  |  |
| `Suffix` | nvarchar(10) |  |  |  |
| `DisplayName` | nvarchar(100) |  | Employee name. | Emily Platt (1), John Johnson (1) |
| `PrintOnCheckName` | nvarchar(110) |  |  |  |
| `Active` | bit |  | True while the employee is active. |  |
| `Mobile_FreeFormNumber` | nvarchar(2000) |  |  |  |
| `PrimaryEmailAddr_Address` | nvarchar(100) |  |  |  |
| `PrimaryAddr_Id` | nvarchar(2000) |  |  |  |
| `PrimaryAddr_Line1` | nvarchar(500) |  |  |  |
| `PrimaryAddr_Line2` | nvarchar(500) |  |  |  |
| `PrimaryAddr_Line3` | nvarchar(500) |  |  |  |
| `PrimaryAddr_Line4` | nvarchar(500) |  |  |  |
| `PrimaryAddr_Line5` | nvarchar(500) |  |  |  |
| `PrimaryAddr_City` | nvarchar(255) |  |  |  |
| `PrimaryAddr_Country` | nvarchar(255) |  |  |  |
| `PrimaryAddr_CountrySubDivisionCode` | nvarchar(255) |  |  |  |
| `PrimaryAddr_PostalCode` | nvarchar(31) |  |  |  |
| `PrimaryAddr_Note` | nvarchar(2000) |  |  |  |
| `PrimaryAddr_Lat` | nvarchar(2000) |  |  |  |
| `PrimaryAddr_Long` | nvarchar(2000) |  |  |  |
| `SSN` | nvarchar(100) |  |  |  |
| `BillableTime` | bit |  |  |  |
| `BillRate` | float |  | Hourly bill rate for billable time. |  |
| `HiredDate` | datetime |  | Start date. | (blank) (1), 2021-03-11T00:00:00 (1) |
| `ReleasedDate` | datetime |  | End date, if the employee has left. |  |
| `BirthDate` | datetime |  |  |  |
| `Gender` | nvarchar(20) |  |  |  |

## InvoiceLineItems

One row per invoice line: the item, quantity, rate and amount. Join to Invoices on InvoiceId.

Grain: one row per invoice line

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `LineId` | nvarchar(255) | PK | Line number within the invoice. | 1 (31), 2 (30), 3 (17), 4 (10), 5 (3), 6 (2) |
| `InvoiceId` | nvarchar(255) | PK FK to Invoices | Invoice this line belongs to (Invoices.Id). |  |
| `SyncToken` | nvarchar(2000) |  |  |  |
| `MetaData_CreateTime` | smalldatetime |  |  |  |
| `MetaData_LastUpdatedTime` | smalldatetime |  |  |  |
| `CustomFieldAggregate` | text(2147483647) |  |  |  |
| `DocNumber` | nvarchar(21) |  |  |  |
| `TxnDate` | datetime |  | Date of the parent invoice. |  |
| `PrivateNote` | nvarchar(4000) |  |  |  |
| `TxnStatus` | nvarchar(2000) |  |  |  |
| `LinkedTxnAggregate` | text(2147483647) |  |  |  |
| `Line_Id` | nvarchar(2000) |  |  |  |
| `Line_LineNum` | nvarchar(2000) |  |  |  |
| `Line_Description` | nvarchar(4000) |  | Description printed on the line. |  |
| `Line_Amount` | decimal |  | Amount of this line before tax. |  |
| `Line_DetailType` | nvarchar(2000) |  | Kind of line, such as SalesItemLineDetail (a product or service) or DiscountLineDetail. | SalesItemLineDetail (60), SubTotalLineDetail (31), DiscountLineDetail (2) |
| `Line_SalesItemLineDetail_ItemRef` | nvarchar(255) | FK to Items |  |  |
| `Line_SalesItemLineDetail_ItemRef_Name` | nvarchar(100) |  | Product or service sold on the line (Items). |  |
| `Line_SalesItemLineDetail_ClassRef` | nvarchar(255) |  |  |  |
| `Line_SalesItemLineDetail_ClassRef_Name` | nvarchar(255) |  |  |  |
| `Line_SalesItemLineDetail_UnitPrice` | decimal |  | Price per unit on the line. |  |
| `Line_SalesItemLineDetail_Qty` | float |  | Quantity sold on the line. |  |
| `Line_SalesItemLineDetail_TaxCodeRef` | nvarchar(255) |  |  |  |
| `Line_SalesItemLineDetail_DiscountAmt` | decimal |  |  |  |
| `Line_SalesItemLineDetail_ServiceDate` | datetime |  |  |  |
| `Line_GroupLineDetail_GroupItemRef` | nvarchar(255) |  |  |  |
| `Line_GroupLineDetail_GroupItemRef_Name` | nvarchar(255) |  |  |  |
| `Line_GroupLineDetail_Quantity` | int |  |  |  |
| `Line_DiscountLineDetail_ClassRef` | nvarchar(255) |  |  |  |
| `Line_DiscountLineDetail_ClassRef_Name` | nvarchar(255) |  |  |  |
| `Line_DiscountLineDetail_TaxCodeRef` | nvarchar(255) |  |  |  |
| `Line_DiscountLineDetail_TaxCodeRef_Name` | nvarchar(255) |  |  |  |
| `Line_DiscountLineDetail_PercentBased` | bit |  |  |  |
| `Line_DiscountLineDetail_DiscountAccountRef` | nvarchar(255) | FK to Accounts |  |  |
| `Line_DiscountLineDetail_DiscountAccountRef_Name` | nvarchar(500) |  |  |  |
| `Line_DiscountLineDetail_DiscountPercent` | decimal |  |  |  |
| `TxnTaxDetail_TxnTaxCodeRef` | nvarchar(255) |  |  |  |
| `TxnTaxDetail_TotalTax` | nvarchar(2000) |  |  |  |
| `TxnTaxDetail_TaxLineAggregate` | text(2147483647) |  |  |  |
| `CustomerRef` | nvarchar(255) | FK to Customers |  |  |
| `CustomerRef_Name` | nvarchar(100) |  |  |  |
| `CustomerMemo` | nvarchar(2000) |  |  |  |
| `BillAddr_Id` | nvarchar(2000) |  |  |  |
| `BillAddr_Line1` | nvarchar(500) |  |  |  |
| `BillAddr_Line2` | nvarchar(500) |  |  |  |
| `BillAddr_Line3` | nvarchar(500) |  |  |  |
| `BillAddr_Line4` | nvarchar(500) |  |  |  |
| `BillAddr_Line5` | nvarchar(500) |  |  |  |
| `BillAddr_City` | nvarchar(255) |  |  |  |
| `BillAddr_Country` | nvarchar(255) |  |  |  |
| `BillAddr_CountrySubDivisionCode` | nvarchar(255) |  |  |  |
| `BillAddr_PostalCode` | nvarchar(31) |  |  |  |
| `BillAddr_Note` | nvarchar(2000) |  |  |  |
| `BillAddr_Lat` | nvarchar(2000) |  |  |  |
| `BillAddr_Long` | nvarchar(2000) |  |  |  |
| `ShipAddr_Id` | nvarchar(2000) |  |  |  |
| `ShipAddr_Line1` | nvarchar(500) |  |  |  |
| `ShipAddr_Line2` | nvarchar(500) |  |  |  |
| `ShipAddr_Line3` | nvarchar(500) |  |  |  |
| `ShipAddr_Line4` | nvarchar(500) |  |  |  |
| `ShipAddr_Line5` | nvarchar(500) |  |  |  |
| `ShipAddr_City` | nvarchar(255) |  |  |  |
| `ShipAddr_Country` | nvarchar(255) |  |  |  |
| `ShipAddr_CountrySubDivisionCode` | nvarchar(255) |  |  |  |
| `ShipAddr_PostalCode` | nvarchar(31) |  |  |  |
| `ShipAddr_Note` | nvarchar(2000) |  |  |  |
| `ShipAddr_Lat` | nvarchar(2000) |  |  |  |
| `ShipAddr_Long` | nvarchar(2000) |  |  |  |
| `ClassRef` | nvarchar(255) |  |  |  |
| `ClassRef_Name` | nvarchar(255) |  |  |  |
| `SalesTermRef` | nvarchar(255) | FK to Terms |  |  |
| `SalesTermRef_Name` | nvarchar(31) |  |  |  |
| `DueDate` | datetime |  |  |  |
| `ShipMethodRef` | nvarchar(255) |  |  |  |
| `ShipMethodRef_Name` | nvarchar(2000) |  |  |  |
| `ShipDate` | datetime |  |  |  |
| `TrackingNum` | nvarchar(2000) |  |  |  |
| `TotalAmt` | decimal |  |  |  |
| `PrintStatus` | nvarchar(50) |  |  |  |
| `EmailStatus` | nvarchar(50) |  |  |  |
| `BillEmail_Address` | nvarchar(100) |  |  |  |
| `Balance` | decimal |  |  |  |
| `DepartmentRef` | nvarchar(255) |  |  |  |
| `DepartmentRef_Name` | nvarchar(100) |  |  |  |
| `AllowOnlineCreditCardPayment` | bit |  |  |  |
| `ApplyTaxAfterDiscount` | bit |  |  |  |
| `CurrencyRef` | nvarchar(2000) |  |  |  |
| `CurrencyRef_Name` | nvarchar(2000) |  |  |  |
| `ExchangeRate` | decimal |  |  |  |
| `GlobalTaxCalculation` | nvarchar(2000) |  |  |  |
| `HomeTotalAmt` | decimal |  |  |  |
| `Crew #` | nvarchar(2000) |  |  |  |

## Invoices

Sales invoices billed to customers, one row per invoice. Totals and balances are here; the lines are in InvoiceLineItems.

Grain: one row per invoice

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `Id` | nvarchar(255) | PK | Unique invoice ID inside QuickBooks Online. Other tables point here through InvoiceId. |  |
| `SyncToken` | nvarchar(2000) |  |  |  |
| `MetaData_CreateTime` | smalldatetime |  |  |  |
| `MetaData_LastUpdatedTime` | smalldatetime |  |  |  |
| `CustomFieldAggregate` | text(2147483647) |  |  |  |
| `DocNumber` | nvarchar(21) |  | Invoice number printed on the document. |  |
| `TxnDate` | datetime |  | Date the invoice was issued (transaction date). |  |
| `PrivateNote` | nvarchar(4000) |  | Internal memo that the customer does not see. | (blank) (30), Front yard, hedges, and sidewalks (1) |
| `TxnStatus` | nvarchar(2000) |  | QuickBooks transaction status field. It is empty in this copy, so ignore it. |  |
| `LinkedTxnAggregate` | text(2147483647) |  |  |  |
| `LineAggregate` | text(2147483647) |  |  |  |
| `TxnTaxDetail_TxnTaxCodeRef` | nvarchar(255) |  |  |  |
| `TxnTaxDetail_TotalTax` | nvarchar(2000) |  |  |  |
| `TxnTaxDetail_TaxLineAggregate` | text(2147483647) |  |  |  |
| `CustomerRef` | nvarchar(255) | FK to Customers | ID of the customer billed (Customers.Id). |  |
| `CustomerRef_Name` | nvarchar(100) |  | Customer name at the time of billing. |  |
| `CustomerMemo` | nvarchar(2000) |  | Memo printed on the invoice for the customer. |  |
| `BillAddr_Id` | nvarchar(2000) |  |  |  |
| `BillAddr_Line1` | nvarchar(500) |  |  |  |
| `BillAddr_Line2` | nvarchar(500) |  |  |  |
| `BillAddr_Line3` | nvarchar(500) |  |  |  |
| `BillAddr_Line4` | nvarchar(500) |  |  |  |
| `BillAddr_Line5` | nvarchar(500) |  |  |  |
| `BillAddr_City` | nvarchar(255) |  |  |  |
| `BillAddr_Country` | nvarchar(255) |  |  |  |
| `BillAddr_CountrySubDivisionCode` | nvarchar(255) |  |  |  |
| `BillAddr_PostalCode` | nvarchar(31) |  |  |  |
| `BillAddr_Note` | nvarchar(2000) |  |  |  |
| `BillAddr_Lat` | nvarchar(2000) |  |  |  |
| `BillAddr_Long` | nvarchar(2000) |  |  |  |
| `ShipAddr_Id` | nvarchar(2000) |  |  |  |
| `ShipAddr_Line1` | nvarchar(500) |  |  |  |
| `ShipAddr_Line2` | nvarchar(500) |  |  |  |
| `ShipAddr_Line3` | nvarchar(500) |  |  |  |
| `ShipAddr_Line4` | nvarchar(500) |  |  |  |
| `ShipAddr_Line5` | nvarchar(500) |  |  |  |
| `ShipAddr_City` | nvarchar(255) |  |  |  |
| `ShipAddr_Country` | nvarchar(255) |  |  |  |
| `ShipAddr_CountrySubDivisionCode` | nvarchar(255) |  |  |  |
| `ShipAddr_PostalCode` | nvarchar(31) |  |  |  |
| `ShipAddr_Note` | nvarchar(2000) |  |  |  |
| `ShipAddr_Lat` | nvarchar(2000) |  |  |  |
| `ShipAddr_Long` | nvarchar(2000) |  |  |  |
| `ClassRef` | nvarchar(255) |  |  |  |
| `ClassRef_Name` | nvarchar(255) |  |  |  |
| `SalesTermRef` | nvarchar(255) | FK to Terms |  |  |
| `SalesTermRef_Name` | nvarchar(31) |  | Payment terms on the invoice, such as Net 30. |  |
| `DueDate` | datetime |  | Date payment is due, from the customer terms. |  |
| `ShipMethodRef` | nvarchar(255) |  |  |  |
| `ShipMethodRef_Name` | nvarchar(2000) |  |  |  |
| `ShipDate` | datetime |  |  |  |
| `TrackingNum` | nvarchar(2000) |  |  |  |
| `TotalAmt` | decimal |  | Invoice total, including tax, in the invoice currency. |  |
| `PrintStatus` | nvarchar(50) |  | Whether the invoice has been printed or sent. | NeedToPrint (23), NotSet (8) |
| `EmailStatus` | nvarchar(50) |  | Whether the invoice was emailed to the customer, and the result. | NotSet (27), NeedToSend (4) |
| `BillEmail_Address` | nvarchar(100) |  |  |  |
| `BillEmailCc_Address` | nvarchar(100) |  |  |  |
| `BillEmailBcc_Address` | nvarchar(100) |  |  |  |
| `Balance` | decimal |  | Amount of the invoice still unpaid. Zero means paid in full. |  |
| `PaymentRefNum` | nvarchar(100) |  |  |  |
| `PaymentMethodRef` | nvarchar(255) |  |  |  |
| `PaymentMethodRef_Name` | nvarchar(31) |  |  |  |
| `DepositToAccountRef` | nvarchar(255) |  |  |  |
| `DepositToAccountRef_Name` | nvarchar(100) |  |  |  |
| `DeliveryInfo_DeliveryType` | nvarchar(100) |  |  |  |
| `DeliveryInfo_DeliveryTime` | smalldatetime |  |  |  |
| `Deposit` | decimal |  |  |  |
| `DepartmentRef` | nvarchar(255) |  |  |  |
| `DepartmentRef_Name` | nvarchar(100) |  |  |  |
| `AllowOnlineCreditCardPayment` | bit |  |  |  |
| `ApplyTaxAfterDiscount` | bit |  |  |  |
| `CurrencyRef` | nvarchar(255) |  |  |  |
| `CurrencyRef_Name` | nvarchar(2000) |  |  |  |
| `ExchangeRate` | decimal |  |  |  |
| `GlobalTaxCalculation` | nvarchar(2000) |  |  |  |
| `HomeTotalAmt` | decimal |  |  |  |
| `Crew #` | nvarchar(2000) |  |  |  |

## Items

Products and services you sell or buy. Inventory items track quantity on hand.

Grain: one row per item

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `Id` | nvarchar(255) | PK | Unique item ID. |  |
| `SyncToken` | nvarchar(2000) |  |  |  |
| `MetaData_CreateTime` | smalldatetime |  |  |  |
| `MetaData_LastUpdatedTime` | smalldatetime |  |  |  |
| `Name` | nvarchar(100) |  | Item name. |  |
| `FullyQualifiedName` | nvarchar(2000) |  |  |  |
| `Description` | nvarchar(2000) |  |  |  |
| `Active` | bit |  | True while the item can be used. |  |
| `ParentRef` | nvarchar(255) |  |  |  |
| `ParentRef_Name` | nvarchar(100) |  |  |  |
| `PrefVendorRef` | nvarchar(255) |  |  |  |
| `PrefVendorRef_Name` | nvarchar(100) |  |  |  |
| `SubItem` | bit |  |  |  |
| `UnitPrice` | decimal |  | Default sales price. | 0.0 (9), 35.0 (2), 2.0 (1), 4.0 (1), 10.0 (1), 15.0 (1), 50.0 (1), 75.0 (1), 275.0 (1) |
| `Type` | nvarchar(50) |  | Kind of item: Inventory (tracks stock), Service, or NonInventory. | Service (14), Inventory (4) |
| `Taxable` | bit |  |  |  |
| `AssetAccountRef` | nvarchar(255) | FK to Accounts |  |  |
| `AssetAccountRef_Name` | nvarchar(255) |  |  |  |
| `IncomeAccountRef` | nvarchar(255) | FK to Accounts |  |  |
| `IncomeAccountRef_Name` | nvarchar(100) |  | Income account the sale posts to. | Sales of Product Income (4), Fountains and Garden Lighting (3), Landscaping Services (2), Plants and Soil (2), Services (2), Design income (1), Installation (1), Maintenance and Repair (1), Other Income (1), Pest Control Services (1) |
| `PurchaseDesc` | nvarchar(2000) |  |  |  |
| `PurchaseCost` | decimal |  | Default cost from the vendor. | 0.0 (13), 0.75 (1), 2.5 (1), 6.5 (1), 10.0 (1), 125.0 (1) |
| `ExpenseAccountRef` | nvarchar(255) | FK to Accounts |  |  |
| `ExpenseAccountRef_Name` | nvarchar(100) |  | Expense account the purchase posts to. | (blank) (13), Cost of Goods Sold (4), Plants and Soil (1) |
| `QtyOnHand` | float |  | Quantity in stock (inventory items). | (blank) (14), 25.0 (2), 2.0 (1), 31.0 (1) |
| `ReorderPoint` | float |  |  |  |
| `InvStartDate` | datetime |  |  |  |
| `SalesTaxCodeRef` | nvarchar(255) |  |  |  |
| `PurchaseTaxCodeRef` | nvarchar(255) |  |  |  |
| `TrackQtyOnHand` | bit |  |  |  |
| `Level` | int |  |  |  |
| `Sku` | nvarchar(100) |  |  |  |
| `SalesTaxIncluded` | bit |  |  |  |
| `PurchaseTaxIncluded` | bit |  |  |  |
| `ItemGroupDetail_ItemGroupLineAggregate` | text(2147483647) |  |  |  |
| `ClassRef` | nvarchar(255) |  |  |  |
| `ClassRef_Name` | nvarchar(255) |  |  |  |

## JournalEntries

Manual journal entries, one row per entry. The debit and credit lines are in JournalEntryLineItems.

Grain: one row per journal entry

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `Id` | nvarchar(255) | PK | Unique journal entry ID. | 6 (1), 7 (1), 8 (1) |
| `SyncToken` | nvarchar(2000) |  |  |  |
| `MetaData_CreateTime` | smalldatetime |  |  |  |
| `MetaData_LastUpdatedTime` | smalldatetime |  |  |  |
| `DocNumber` | nvarchar(21) |  | Journal number. |  |
| `TxnDate` | datetime |  | Posting date of the entry. | 2021-04-08T00:00:00 (2), 2021-03-26T00:00:00 (1) |
| `PrivateNote` | nvarchar(4000) |  |  |  |
| `Adjustment` | bit |  | True for adjusting entries made at period end. |  |
| `LineAggregate` | text(2147483647) |  |  |  |
| `TxnTaxDetail_TxnTaxCodeRef` | nvarchar(255) |  |  |  |
| `TxnTaxDetail_TotalTax` | nvarchar(2000) |  |  |  |
| `TxnTaxDetail_TaxLineAggregate` | text(2147483647) |  |  |  |
| `TotalAmt` | decimal |  | Total debits (equal to total credits). |  |
| `CurrencyRef` | nvarchar(255) |  |  |  |
| `CurrencyRef_Name` | nvarchar(2000) |  |  |  |
| `ExchangeRate` | decimal |  |  |  |

## JournalEntryLineItems

The debit and credit lines of each journal entry. Each entry balances to zero.

Grain: one row per journal line

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `LineId` | nvarchar(255) | PK | Line number within the entry. | 0 (3), 1 (3) |
| `JournalEntryId` | nvarchar(255) | PK FK to JournalEntries | Parent journal entry (JournalEntries.Id). | 6 (2), 7 (2), 8 (2) |
| `SyncToken` | nvarchar(2000) |  |  |  |
| `MetaData_CreateTime` | smalldatetime |  |  |  |
| `MetaData_LastUpdatedTime` | smalldatetime |  |  |  |
| `DocNumber` | nvarchar(21) |  |  |  |
| `TxnDate` | datetime |  |  |  |
| `PrivateNote` | nvarchar(4000) |  |  |  |
| `Adjustment` | bit |  |  |  |
| `Line_Id` | nvarchar(2000) |  |  |  |
| `Line_Description` | nvarchar(4000) |  | Line memo. |  |
| `Line_Amount` | decimal |  | Amount of the line (always positive; the posting type gives the direction). | 4000.0 (2), 13495.0 (2), 25000.0 (2) |
| `Line_DetailType` | nvarchar(2000) |  |  |  |
| `Line_JournalEntryLineDetail_PostingType` | nvarchar(2000) |  | Debit or Credit. | Credit (3), Debit (3) |
| `Line_JournalEntryLineDetail_Entity_Type` | nvarchar(2000) |  |  |  |
| `Line_JournalEntryLineDetail_Entity_EntityRef` | nvarchar(255) |  |  |  |
| `Line_JournalEntryLineDetail_Entity_EntityRef_Name` | nvarchar(2000) |  | Customer, vendor or employee linked to the line, if any. |  |
| `Line_JournalEntryLineDetail_AccountRef` | nvarchar(255) | FK to Accounts |  |  |
| `Line_JournalEntryLineDetail_AccountRef_Name` | nvarchar(100) |  | Account posted to. | Opening Balance Equity (3), Loan Payable (1), Notes Payable (1), Truck:Original Cost (1) |
| `Line_JournalEntryLineDetail_ClassRef` | nvarchar(255) |  |  |  |
| `Line_JournalEntryLineDetail_ClassRef_Name` | nvarchar(255) |  | Class tag on the line. |  |
| `Line_JournalEntryLineDetail_DepartmentRef` | nvarchar(255) |  |  |  |
| `Line_JournalEntryLineDetail_DepartmentRef_Name` | nvarchar(100) |  |  |  |
| `Line_JournalEntryLineDetail_TaxCodeRef` | nvarchar(255) |  |  |  |
| `Line_JournalEntryLineDetail_TaxApplicableOn` | nvarchar(2000) |  |  |  |
| `Line_JournalEntryLineDetail_TaxAmount` | decimal |  |  |  |
| `Line_JournalEntryLineDetail_BillableStatus` | nvarchar(2000) |  |  |  |
| `TxnTaxDetail_TxnTaxCodeRef` | nvarchar(255) |  |  |  |
| `TxnTaxDetail_TotalTax` | nvarchar(2000) |  |  |  |
| `TxnTaxDetail_TaxLineAggregate` | text(2147483647) |  |  |  |
| `TotalAmt` | decimal |  |  |  |
| `CurrencyRef` | nvarchar(2000) |  |  |  |
| `CurrencyRef_Name` | nvarchar(2000) |  |  |  |
| `ExchangeRate` | decimal |  |  |  |

## PaymentLineItems

Applies each payment to the invoices it pays off. Join to Payments on PaymentId.

Grain: one row per payment application

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `LineId` | nvarchar(255) | PK |  |  |
| `PaymentId` | nvarchar(255) | PK FK to Payments | Payment that was applied (Payments.Id). |  |
| `SyncToken` | nvarchar(2000) |  |  |  |
| `MetaData_CreateTime` | smalldatetime |  |  |  |
| `MetaData_LastUpdatedTime` | smalldatetime |  |  |  |
| `TxnDate` | datetime |  |  |  |
| `PrivateNote` | nvarchar(4000) |  |  |  |
| `TxnStatus` | nvarchar(50) |  |  |  |
| `Line_LinkedTxn_TxnId` | nvarchar(255) |  | ID of the invoice (or other transaction) being paid. |  |
| `Line_Amount` | decimal |  | Amount of the payment applied to this transaction. |  |
| `Line_LinkedTxn_TxnType` | nvarchar(2000) |  | Type of the transaction paid, such as Invoice. | Invoice (16), CreditMemo (1) |
| `Line_LinkedTxn_TxnLineId` | nvarchar(255) |  |  |  |
| `TxnTaxDetail_TxnTaxCodeRef` | nvarchar(255) |  |  |  |
| `TxnTaxDetail_TotalTax` | nvarchar(2000) |  |  |  |
| `TxnTaxDetail_TaxLineAggregate` | text(2147483647) |  |  |  |
| `CustomerRef` | nvarchar(255) | FK to Customers |  |  |
| `CustomerRef_Name` | nvarchar(100) |  |  |  |
| `RemitToRef` | nvarchar(2000) |  |  |  |
| `RemitToRef_Name` | nvarchar(2000) |  |  |  |
| `ARAccountRef` | nvarchar(255) |  |  |  |
| `ARAccountRef_Name` | nvarchar(100) |  |  |  |
| `DepositToAccountRef` | nvarchar(255) | FK to Accounts |  |  |
| `DepositToAccountRef_Name` | nvarchar(100) |  |  |  |
| `PaymentMethodRef` | nvarchar(255) | FK to PaymentMethods |  |  |
| `PaymentMethodRef_Name` | nvarchar(31) |  |  |  |
| `PaymentRefNum` | nvarchar(21) |  |  |  |
| `CreditCardPayment_CreditChargeInfo` | nvarchar(2000) |  |  |  |
| `CreditCardPayment_Number` | nvarchar(2000) |  |  |  |
| `CreditCardPayment_Type` | nvarchar(2000) |  |  |  |
| `CreditCardPayment_NameOnAcct` | nvarchar(2000) |  |  |  |
| `CreditCardPayment_CcExpiryMonth` | int |  |  |  |
| `CreditCardPayment_CcExpiryYear` | int |  |  |  |
| `CreditCardPayment_BillAddrStreet` | nvarchar(2000) |  |  |  |
| `CreditCardPayment_PostalCode` | nvarchar(2000) |  |  |  |
| `CreditCardPayment_CommercialCardCode` | nvarchar(2000) |  |  |  |
| `CreditCardPayment_CCTxnMode` | nvarchar(2000) |  |  |  |
| `CreditCardPayment_CCTxnType` | nvarchar(2000) |  |  |  |
| `CreditCardPayment_PrevCCTransId` | nvarchar(2000) |  |  |  |
| `TotalAmt` | decimal |  |  |  |
| `UnappliedAmt` | decimal |  |  |  |
| `ProcessPayment` | bit |  |  |  |
| `CurrencyRef` | nvarchar(255) |  |  |  |
| `CurrencyRef_Name` | nvarchar(2000) |  |  |  |
| `ExchangeRate` | decimal |  |  |  |

## PaymentMethods

The payment methods a customer can use, such as cash, check or card.

Grain: one row per payment method

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `Id` | nvarchar(255) | PK |  |  |
| `SyncToken` | nvarchar(2000) |  |  |  |
| `MetaData_CreateTime` | smalldatetime |  |  |  |
| `MetaData_LastUpdatedTime` | smalldatetime |  |  |  |
| `Name` | nvarchar(31) |  | Method name shown on payments. | American Express (1), Cash (1), Check (1), Diners Club (1), Discover (1), MasterCard (1), Visa (1) |
| `Active` | bit |  | True while the method can be chosen. |  |
| `Type` | nvarchar(2000) |  | Category: CASH, CHECK, CREDIT_CARD or NON_CREDIT_CARD. | CREDIT_CARD (5), NON_CREDIT_CARD (2) |

## Payments

Customer payments received, one row per payment. Lines that apply the money to invoices are in PaymentLineItems.

Grain: one row per customer payment

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `Id` | nvarchar(255) | PK | Unique payment ID. |  |
| `SyncToken` | nvarchar(2000) |  |  |  |
| `MetaData_CreateTime` | smalldatetime |  |  |  |
| `MetaData_LastUpdatedTime` | smalldatetime |  |  |  |
| `TxnDate` | datetime |  | Date the payment was received. | 2021-04-09T00:00:00 (4), 2021-01-15T00:00:00 (2), 2021-04-10T00:00:00 (2), 2021-04-11T00:00:00 (2), 2021-03-15T00:00:00 (1), 2021-03-17T00:00:00 (1), 2021-03-19T00:00:00 (1), 2021-03-25T00:00:00 (1), 2021-04-04T00:00:00 (1), 2021-04-07T00:00:00 (1) |
| `PrivateNote` | nvarchar(2000) |  |  |  |
| `TxnStatus` | nvarchar(50) |  |  |  |
| `LineAggregate` | text(2147483647) |  |  |  |
| `TxnTaxDetail_TxnTaxCodeRef` | nvarchar(255) |  |  |  |
| `TxnTaxDetail_TotalTax` | nvarchar(2000) |  |  |  |
| `TxnTaxDetail_TaxLineAggregate` | text(2147483647) |  |  |  |
| `CustomerRef` | nvarchar(255) | FK to Customers |  |  |
| `CustomerRef_Name` | nvarchar(100) |  | Customer who paid. | Amy's Bird Sanctuary (4), 0969 Ocean View Road (2), 55 Twin Lane (2), Cool Cars (2), Travis Waldron (2), Bill's Windsurf Shop (1), Dukes Basketball Camp (1), John Melton (1), Sushi by Katsuyuki (1) |
| `RemitToRef` | nvarchar(2000) |  |  |  |
| `RemitToRef_Name` | nvarchar(2000) |  |  |  |
| `ARAccountRef` | nvarchar(255) |  |  |  |
| `ARAccountRef_Name` | nvarchar(100) |  |  |  |
| `DepositToAccountRef` | nvarchar(255) | FK to Accounts |  |  |
| `DepositToAccountRef_Name` | nvarchar(100) |  | Bank or undeposited-funds account that received the money. |  |
| `PaymentMethodRef` | nvarchar(255) | FK to PaymentMethods |  |  |
| `PaymentMethodRef_Name` | nvarchar(31) |  | How the customer paid, such as Check, Cash or Credit Card. |  |
| `PaymentRefNum` | nvarchar(21) |  | Check number or payment reference. | (blank) (10), 1053 (1), 1886 (1), 2064 (1), 5421 (1), 5664 (1), 6552 (1) |
| `CreditCardPayment_CreditChargeInfo` | nvarchar(2000) |  |  |  |
| `CreditCardPayment_Number` | nvarchar(2000) |  |  |  |
| `CreditCardPayment_Type` | nvarchar(2000) |  |  |  |
| `CreditCardPayment_NameOnAcct` | nvarchar(2000) |  |  |  |
| `CreditCardPayment_CcExpiryMonth` | int |  |  |  |
| `CreditCardPayment_CcExpiryYear` | int |  |  |  |
| `CreditCardPayment_BillAddrStreet` | nvarchar(2000) |  |  |  |
| `CreditCardPayment_PostalCode` | nvarchar(2000) |  |  |  |
| `CreditCardPayment_CommercialCardCode` | nvarchar(2000) |  |  |  |
| `CreditCardPayment_CCTxnMode` | nvarchar(2000) |  |  |  |
| `CreditCardPayment_CCTxnType` | nvarchar(2000) |  |  |  |
| `CreditCardPayment_PrevCCTransId` | nvarchar(2000) |  |  |  |
| `TotalAmt` | decimal |  | Amount received. |  |
| `UnappliedAmt` | decimal |  | Part of the payment not yet applied to an invoice. Zero means fully applied. |  |
| `ProcessPayment` | bit |  |  |  |
| `CurrencyRef` | nvarchar(255) |  |  |  |
| `CurrencyRef_Name` | nvarchar(2000) |  |  |  |
| `ExchangeRate` | decimal |  |  |  |

## Terms

Payment terms, such as Net 30 or 2/10 Net 30.

Grain: one row per terms record

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `Id` | nvarchar(255) | PK |  |  |
| `SyncToken` | nvarchar(2000) |  |  |  |
| `MetaData_CreateTime` | smalldatetime |  |  |  |
| `MetaData_LastUpdatedTime` | smalldatetime |  |  |  |
| `Name` | nvarchar(31) |  | Terms name. | Due on receipt (1), Net 10 (1), Net 15 (1), Net 30 (1), Net 60 (1) |
| `Active` | bit |  |  |  |
| `Type` | nvarchar(20) |  | STANDARD (due a set number of days after the date) or DATE_DRIVEN (due on a fixed day). Only STANDARD appears in this copy. |  |
| `DiscountPercent` | float |  | Early-payment discount percent. |  |
| `DueDays` | int |  | Days after the transaction date that payment is due. | 0 (1), 10 (1), 15 (1), 30 (1), 60 (1) |
| `DiscountDays` | int |  | Days within which the discount applies. | 0 (4), (blank) (1) |
| `DayOfMonthDue` | int |  |  |  |
| `DueNextMonthDays` | int |  |  |  |
| `DiscountDayOfMonth` | int |  |  |  |

## TimeActivities

Time entries against customers or jobs, for billable time.

Grain: one row per time entry

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `Id` | nvarchar(255) | PK |  |  |
| `SyncToken` | nvarchar(2000) |  |  |  |
| `MetaData_CreateTime` | smalldatetime |  |  |  |
| `MetaData_LastUpdatedTime` | smalldatetime |  |  |  |
| `TxnDate` | datetime |  | Date the work was done. | 2021-04-09T00:00:00 (3), 2021-04-04T00:00:00 (1), 2021-04-08T00:00:00 (1) |
| `NameOf` | nvarchar(20) |  | Whether the time is for a Vendor or an Employee. |  |
| `EmployeeRef` | nvarchar(255) | FK to Employees |  |  |
| `EmployeeRef_Name` | nvarchar(255) |  | Employee who did the work. | Emily Platt (3), John Johnson (2) |
| `VendorRef` | nvarchar(255) |  |  |  |
| `VendorRef_Name` | nvarchar(100) |  |  |  |
| `CustomerRef` | nvarchar(255) | FK to Customers |  |  |
| `CustomerRef_Name` | nvarchar(100) |  | Customer or job the time is for. | Amy's Bird Sanctuary (2), Rondonuwu Fruit and Vegi (2), Mark Cho (1) |
| `DepartmentRef` | nvarchar(255) |  |  |  |
| `DepartmentRef_Name` | nvarchar(100) |  |  |  |
| `ItemRef` | nvarchar(255) | FK to Items |  |  |
| `ItemRef_Name` | nvarchar(100) |  |  |  |
| `ClassRef` | nvarchar(255) |  |  |  |
| `ClassRef_Name` | nvarchar(255) |  |  |  |
| `PayrollItemRef` | nvarchar(255) |  |  |  |
| `BillableStatus` | nvarchar(50) |  | Whether the time can be billed: Billable, NotBillable or HasBeenBilled. | HasBeenBilled (2), NotBillable (2), Billable (1) |
| `Taxable` | bit |  |  |  |
| `HourlyRate` | decimal |  | Rate for the time. | 0.0 (2), 15.0 (2), 75.0 (1) |
| `Hours` | int |  | Whole hours logged. | 4 (2), 2 (1), 3 (1), 5 (1) |
| `Minutes` | int |  | Minutes logged, added to Hours. | 0 (4), 45 (1) |
| `StartTime` | smalldatetime |  |  |  |
| `EndTime` | smalldatetime |  |  |  |
| `Description` | nvarchar(4000) |  |  |  |

## Vendors

Vendors (suppliers and payees), with the open balance owed to each one.

Grain: one row per vendor

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `Id` | nvarchar(255) | PK | Unique vendor ID. |  |
| `SyncToken` | nvarchar(2000) |  |  |  |
| `MetaData_CreateTime` | smalldatetime |  |  |  |
| `MetaData_LastUpdatedTime` | smalldatetime |  |  |  |
| `Title` | nvarchar(15) |  |  |  |
| `GivenName` | nvarchar(25) |  |  |  |
| `MiddleName` | nvarchar(25) |  |  |  |
| `FamilyName` | nvarchar(25) |  |  |  |
| `Suffix` | nvarchar(10) |  |  |  |
| `DisplayName` | nvarchar(100) |  | Vendor name shown in lists. |  |
| `CompanyName` | nvarchar(50) |  |  |  |
| `PrintOnCheckName` | nvarchar(100) |  |  |  |
| `Active` | bit |  | True while the vendor is active. |  |
| `PrimaryPhone_FreeFormNumber` | nvarchar(30) |  |  |  |
| `AlternatePhone_FreeFormNumber` | nvarchar(30) |  |  |  |
| `Mobile_FreeFormNumber` | nvarchar(30) |  |  |  |
| `Fax_FreeFormNumber` | nvarchar(30) |  |  |  |
| `PrimaryEmailAddr_Address` | nvarchar(100) |  |  |  |
| `WebAddr_URI` | nvarchar(1000) |  |  |  |
| `BillAddr_Id` | nvarchar(2000) |  |  |  |
| `BillAddr_Line1` | nvarchar(500) |  |  |  |
| `BillAddr_Line2` | nvarchar(500) |  |  |  |
| `BillAddr_Line3` | nvarchar(500) |  |  |  |
| `BillAddr_Line4` | nvarchar(500) |  |  |  |
| `BillAddr_Line5` | nvarchar(500) |  |  |  |
| `BillAddr_City` | nvarchar(255) |  |  |  |
| `BillAddr_Country` | nvarchar(255) |  |  |  |
| `BillAddr_CountrySubDivisionCode` | nvarchar(255) |  |  |  |
| `BillAddr_PostalCode` | nvarchar(31) |  |  |  |
| `BillAddr_Lat` | nvarchar(2000) |  |  |  |
| `BillAddr_Long` | nvarchar(2000) |  |  |  |
| `OtherContactInfo_Type` | nvarchar(2000) |  |  |  |
| `OtherContactInfo_Telephone_FreeFormNumber` | nvarchar(30) |  |  |  |
| `TaxIdentifier` | nvarchar(20) |  |  |  |
| `TermRef` | nvarchar(255) | FK to Terms |  |  |
| `TermRef_Name` | nvarchar(31) |  | Default payment terms for the vendor. |  |
| `Balance` | nvarchar(2000) |  | Open amount owed to the vendor. | 0 (21), 205.00 (1), 241.23 (1), 315.00 (1), 755.00 (1), 86.44 (1) |
| `AcctNum` | nvarchar(100) |  |  |  |
| `Vendor1099` | bit |  | True when the vendor is tracked for Form 1099 reporting. | False (25), True (1) |
| `CurrencyRef` | nvarchar(255) |  |  |  |
| `CurrencyRef_Name` | nvarchar(2000) |  |  |  |

## Undocumented tables

`AttachableRefs`, `Attachables`, `BillLinkedTransactions`, `BillPaymentLineItems`, `CompanyCurrency`, `CreditMemoLineItems`, `CreditMemos`, `DepositLineItems`, `EstimateLineItems`, `EstimateLinkedTransactions`, `Estimates`, `InvoiceLinkedTransactions`, `JournalCode`, `PurchaseLineItems`, `PurchaseOrderLineItems`, `PurchaseOrderLinkedTransactions`, `PurchaseOrders`, `Purchases`, `RefundReceiptLineItems`, `RefundReceipts`, `SalesReceiptLineItems`, `SalesReceipts`, `sysdiagrams`, `TaxAgency`, `Transfers`, `VendorCreditLineItems`, `VendorCredits`
