# QuickBooks Desktop style data dictionary

Sample database `qbd` 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).
12 of 84 tables are described. Undocumented tables are listed at the end.

## Accounts

The chart of accounts.

Grain: one row per general ledger account

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `ID` | nvarchar(255) | PK | Unique account ID. |  |
| `Name` | nvarchar(31) |  | Account name. |  |
| `FullName` | nvarchar(1000) |  | Full name including the parent account. |  |
| `Type` | nvarchar(100) |  | Account type, such as Bank, AccountsReceivable, AccountsPayable, Income, Expense or Equity. |  |
| `SpecialType` | nvarchar(100) |  |  |  |
| `Number` | nvarchar(2000) |  | Account number. |  |
| `Balance` | float |  | Current balance of the account. |  |
| `AccountBalance` | float |  |  |  |
| `BankAccount` | nvarchar(2000) |  |  |  |
| `Description` | nvarchar(200) |  |  |  |
| `IsActive` | bit |  | True while the account is active. | True (114), False (2) |
| `ParentName` | nvarchar(255) |  | Parent account, for sub-accounts. |  |
| `ParentId` | nvarchar(255) |  |  |  |
| `Sublevel` | int |  | How deep the account sits in the parent-child tree (0 for top level). | 0 (63), 1 (53) |
| `CurrencyName` | nvarchar(64) |  |  |  |
| `CurrencyId` | nvarchar(255) |  |  |  |
| `CashFlowClassification` | nvarchar(2000) |  |  |  |
| `TaxLineName` | nvarchar(256) |  |  |  |
| `TaxLineId` | nvarchar(255) |  |  |  |
| `TimeModified` | smalldatetime |  |  |  |
| `TimeCreated` | smalldatetime |  |  |  |

## Bills

Vendor bills (payables), one row per bill.

Grain: one row per bill

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `ID` | nvarchar(255) | PK | Unique bill ID. |  |
| `VendorName` | nvarchar(255) |  | Vendor who sent the bill. |  |
| `VendorId` | nvarchar(255) | FK to Vendors |  |  |
| `ReferenceNumber` | nvarchar(21) |  | Vendor's reference or invoice number. |  |
| `Date` | datetime |  | Bill date. |  |
| `Amount` | decimal |  | Bill total. |  |
| `TxnNumber` | int |  |  |  |
| `DueDate` | datetime |  | Date payment is due. |  |
| `Terms` | nvarchar(100) |  | Payment terms. | Net 30 (91), Net 15 (60),  (18), 1% 10 Net 30 (12), 2% 10 Net 30 (5), Net 60 (5), Due on receipt (3) |
| `TermsId` | nvarchar(255) |  |  |  |
| `AccountsPayable` | nvarchar(255) |  | Accounts Payable account the bill posts to. |  |
| `AccountsPayableId` | nvarchar(255) |  |  |  |
| `Memo` | text(2147483647) |  |  |  |
| `IsPaid` | bit |  | True when the bill is paid in full. | True (166), False (28) |
| `ExchangeRate` | float |  |  |  |
| `IsTaxIncluded` | bit |  |  |  |
| `ItemCount` | int |  |  |  |
| `ItemAggregate` | text(2147483647) |  |  |  |
| `ExpenseItemCount` | int |  |  |  |
| `ExpenseItemAggregate` | text(2147483647) |  |  |  |
| `TransactionCount` | int |  |  |  |
| `TransactionAggregate` | text(2147483647) |  |  |  |
| `CurrencyName` | nvarchar(64) |  |  |  |
| `CurrencyId` | nvarchar(255) |  |  |  |
| `CustomFields` | nvarchar(2000) |  |  |  |
| `TimeModified` | smalldatetime |  |  |  |
| `TimeCreated` | smalldatetime |  |  |  |
| `OpenAmount` | decimal |  | Amount still owed on the bill. |  |

## Checks

Checks written, one row per check.

Grain: one row per check

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `ID` | nvarchar(255) | PK | Unique check ID. |  |
| `ReferenceNumber` | nvarchar(21) |  | Check number. |  |
| `TxnNumber` | int |  |  |  |
| `Account` | nvarchar(255) |  | Bank account the check is drawn on. |  |
| `AccountId` | nvarchar(255) | FK to Accounts |  |  |
| `Payee` | nvarchar(255) |  | Vendor or person paid. |  |
| `PayeeId` | nvarchar(255) |  |  |  |
| `Date` | datetime |  | Check date. |  |
| `Amount` | decimal |  | Check amount. |  |
| `Memo` | text(2147483647) |  |  |  |
| `Address` | nvarchar(2000) |  |  |  |
| `Line1` | nvarchar(500) |  |  |  |
| `Line2` | nvarchar(500) |  |  |  |
| `Line3` | nvarchar(500) |  |  |  |
| `Line4` | nvarchar(500) |  |  |  |
| `Line5` | nvarchar(41) |  |  |  |
| `City` | nvarchar(255) |  |  |  |
| `State` | nvarchar(255) |  |  |  |
| `PostalCode` | nvarchar(30) |  |  |  |
| `Country` | nvarchar(255) |  |  |  |
| `Note` | nvarchar(41) |  |  |  |
| `ItemCount` | int |  |  |  |
| `ItemAggregate` | text(2147483647) |  |  |  |
| `ExpenseItemCount` | int |  |  |  |
| `ExpenseItemAggregate` | text(2147483647) |  |  |  |
| `IsToBePrinted` | bit |  |  |  |
| `IsTaxIncluded` | bit |  |  |  |
| `CurrencyName` | nvarchar(64) |  |  |  |
| `CurrencyId` | nvarchar(255) |  |  |  |
| `ExchangeRate` | float |  |  |  |
| `CustomFields` | nvarchar(2000) |  |  |  |
| `TimeModified` | smalldatetime |  |  |  |
| `TimeCreated` | smalldatetime |  |  |  |

## Customers

Customers and jobs. Jobs are sub-customers with a ParentName.

Grain: one row per customer or job

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `ID` | nvarchar(255) | PK | Unique customer ID. |  |
| `Name` | nvarchar(41) |  | Short name of the customer or job. |  |
| `FullName` | nvarchar(159) |  | Full name including the parent customer, for example Smith:Kitchen. |  |
| `Salutation` | nvarchar(15) |  |  |  |
| `FirstName` | nvarchar(25) |  |  |  |
| `MiddleInitial` | nvarchar(5) |  |  |  |
| `LastName` | nvarchar(25) |  |  |  |
| `AccountNumber` | nvarchar(99) |  |  |  |
| `Company` | nvarchar(41) |  | Company name, when the customer is a business. |  |
| `Balance` | float |  | Open amount the customer owes (receivable). |  |
| `CustomerBalance` | float |  |  |  |
| `Contact` | nvarchar(41) |  |  |  |
| `Type` | nvarchar(255) |  | Customer type, such as Retail or Wholesale. | Residential (112), Commercial (33),  (1) |
| `TypeId` | nvarchar(255) |  |  |  |
| `Phone` | nvarchar(21) |  |  |  |
| `Fax` | nvarchar(21) |  |  |  |
| `AlternateContact` | nvarchar(41) |  |  |  |
| `AlternatePhone` | nvarchar(21) |  |  |  |
| `Email` | nvarchar(1023) |  |  |  |
| `Cc` | nvarchar(1023) |  |  |  |
| `AdditionalContactInfo` | nvarchar(2000) |  |  |  |
| `ContactsAggregate` | text(2147483647) |  |  |  |
| `ClassName` | nvarchar(2000) |  |  |  |
| `ClassId` | nvarchar(2000) |  |  |  |
| `Notes` | text(2147483647) |  |  |  |
| `NotesAggregate` | text(2147483647) |  |  |  |
| `ParentName` | nvarchar(255) |  | Parent customer, for jobs. |  |
| `ParentId` | nvarchar(255) |  |  |  |
| `Sublevel` | int |  |  |  |
| `JobStatus` | nvarchar(10) |  | Status of a job: Pending, Awarded, InProgress, Closed or Pending. Blank for customers that are not jobs. | None (101), Closed (30), InProgress (12), Pending (2), Awarded (1) |
| `JobStartDate` | datetime |  |  |  |
| `JobProjectedEndDate` | datetime |  |  |  |
| `JobEndDate` | datetime |  |  |  |
| `JobDescription` | nvarchar(99) |  |  |  |
| `JobType` | nvarchar(255) |  |  |  |
| `JobTypeId` | nvarchar(255) |  |  |  |
| `CreditCardAddress` | nvarchar(41) |  |  |  |
| `CreditCardExpMonth` | int |  |  |  |
| `CreditCardExpYear` | int |  |  |  |
| `CreditCardNameOnCard` | nvarchar(41) |  |  |  |
| `CreditCardNumber` | nvarchar(25) |  |  |  |
| `CreditCardPostalCode` | nvarchar(41) |  |  |  |
| `CreditLimit` | float |  | Maximum credit allowed for the customer. |  |
| `BillingAddress` | nvarchar(2000) |  |  |  |
| `BillingLine1` | nvarchar(41) |  |  |  |
| `BillingLine2` | nvarchar(41) |  |  |  |
| `BillingLine3` | nvarchar(41) |  |  |  |
| `BillingLine4` | nvarchar(41) |  |  |  |
| `BillingLine5` | nvarchar(41) |  |  |  |
| `BillingCity` | nvarchar(31) |  |  |  |
| `BillingState` | nvarchar(21) |  |  |  |
| `BillingPostalCode` | nvarchar(13) |  |  |  |
| `BillingCountry` | nvarchar(31) |  |  |  |
| `BillingNote` | nvarchar(41) |  |  |  |
| `ShippingAddress` | nvarchar(2000) |  |  |  |
| `ShippingLine1` | nvarchar(41) |  |  |  |
| `ShippingLine2` | nvarchar(41) |  |  |  |
| `ShippingLine3` | nvarchar(41) |  |  |  |
| `ShippingLine4` | nvarchar(41) |  |  |  |
| `ShippingLine5` | nvarchar(41) |  |  |  |
| `ShippingCity` | nvarchar(31) |  |  |  |
| `ShippingState` | nvarchar(21) |  |  |  |
| `ShippingPostalCode` | nvarchar(13) |  |  |  |
| `ShippingCountry` | nvarchar(31) |  |  |  |
| `ShippingNote` | nvarchar(41) |  |  |  |
| `ShippingAggregate` | text(2147483647) |  |  |  |
| `ResaleNumber` | nvarchar(16) |  |  |  |
| `SalesRep` | nvarchar(5) |  | Sales representative assigned to the customer. |  |
| `SalesRepId` | nvarchar(255) |  |  |  |
| `Terms` | nvarchar(100) |  |  |  |
| `TermsId` | nvarchar(255) |  | Default payment terms (Terms ID). | 10000-933272658 (62), 20000-933272658 (43),  (24), 30000-933272658 (8), 60000-933272660 (8), 50000-933272659 (1) |
| `CurrencyName` | nvarchar(64) |  |  |  |
| `CurrencyId` | nvarchar(255) |  |  |  |
| `TaxCode` | nvarchar(3) |  |  |  |
| `TaxCodeId` | nvarchar(255) |  |  |  |
| `TaxItem` | nvarchar(100) |  |  |  |
| `TaxItemId` | nvarchar(255) |  |  |  |
| `SalesTaxCountry` | nvarchar(100) |  |  |  |
| `PriceLevel` | nvarchar(100) |  | Price level applied to the customer. | (140), Pretell Real Estate Rates (3), Residential (2), Kitchen Upgrade Package (1) |
| `PriceLevelId` | nvarchar(255) |  |  |  |
| `PreferredDeliveryMethod` | nvarchar(20) |  |  |  |
| `PreferredPaymentMethodName` | nvarchar(100) |  |  |  |
| `PreferredPaymentMethodId` | nvarchar(255) |  |  |  |
| `IsActive` | bit |  | True while the customer is active. |  |
| `CustomFields` | nvarchar(2000) |  |  |  |
| `EditSequence` | nvarchar(16) |  |  |  |
| `TimeModified` | smalldatetime |  |  |  |
| `TimeCreated` | smalldatetime |  |  |  |

## InvoiceLineItems

Invoice lines, one row per item line on an invoice.

Grain: one row per invoice line

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `ID` | nvarchar(255) | PK | Unique row ID for the line. |  |
| `InvoiceId` | nvarchar(255) | FK to Invoices | Invoice this line belongs to (Invoices.ID). |  |
| `ReferenceNumber` | nvarchar(21) |  |  |  |
| `TxnNumber` | int |  |  |  |
| `CustomerName` | nvarchar(255) |  |  |  |
| `CustomerId` | nvarchar(255) | FK to Customers |  |  |
| `Account` | nvarchar(255) |  |  |  |
| `AccountId` | nvarchar(255) | FK to Accounts |  |  |
| `Date` | datetime |  |  |  |
| `ShipMethod` | nvarchar(255) |  |  |  |
| `ShipMethodId` | nvarchar(255) |  |  |  |
| `ShipDate` | datetime |  |  |  |
| `Memo` | text(2147483647) |  |  |  |
| `Class` | nvarchar(255) |  |  |  |
| `ClassId` | nvarchar(255) | FK to Class |  |  |
| `Amount` | decimal |  |  |  |
| `Message` | nvarchar(101) |  |  |  |
| `MessageId` | nvarchar(255) |  |  |  |
| `SalesRep` | nvarchar(5) |  |  |  |
| `SalesRepId` | nvarchar(255) |  |  |  |
| `FOB` | nvarchar(13) |  |  |  |
| `BillingAddress` | nvarchar(2000) |  |  |  |
| `BillingLine1` | nvarchar(500) |  |  |  |
| `BillingLine2` | nvarchar(500) |  |  |  |
| `BillingLine3` | nvarchar(500) |  |  |  |
| `BillingLine4` | nvarchar(500) |  |  |  |
| `BillingLine5` | nvarchar(41) |  |  |  |
| `BillingCity` | nvarchar(255) |  |  |  |
| `BillingState` | nvarchar(255) |  |  |  |
| `BillingPostalCode` | nvarchar(30) |  |  |  |
| `BillingCountry` | nvarchar(255) |  |  |  |
| `BillingNote` | nvarchar(41) |  |  |  |
| `ShippingAddress` | nvarchar(2000) |  |  |  |
| `ShippingLine1` | nvarchar(500) |  |  |  |
| `ShippingLine2` | nvarchar(500) |  |  |  |
| `ShippingLine3` | nvarchar(500) |  |  |  |
| `ShippingLine4` | nvarchar(500) |  |  |  |
| `ShippingLine5` | nvarchar(41) |  |  |  |
| `ShippingCity` | nvarchar(255) |  |  |  |
| `ShippingState` | nvarchar(255) |  |  |  |
| `ShippingPostalCode` | nvarchar(30) |  |  |  |
| `ShippingCountry` | nvarchar(255) |  |  |  |
| `ShippingNote` | nvarchar(41) |  |  |  |
| `Subtotal` | decimal |  |  |  |
| `Tax` | float |  |  |  |
| `TaxItem` | nvarchar(100) |  |  |  |
| `TaxItemId` | nvarchar(255) |  |  |  |
| `TaxPercent` | float |  |  |  |
| `PONumber` | nvarchar(25) |  |  |  |
| `DueDate` | datetime |  |  |  |
| `Terms` | nvarchar(100) |  |  |  |
| `TermsId` | nvarchar(255) |  |  |  |
| `CustomFields` | nvarchar(2000) |  |  |  |
| `ItemLineId` | nvarchar(2000) |  |  |  |
| `ItemLineNumber` | nvarchar(255) |  |  |  |
| `ItemName` | nvarchar(255) |  | Item sold on the line (Items.Name). |  |
| `ItemId` | nvarchar(255) |  |  |  |
| `ItemGroup` | nvarchar(100) |  |  |  |
| `ItemGroupId` | nvarchar(255) |  |  |  |
| `ItemDescription` | text(2147483647) |  | Description printed on the line. |  |
| `ItemUnitOfMeasure` | nvarchar(31) |  |  |  |
| `ItemQuantity` | float |  | Quantity sold. |  |
| `ItemRate` | float |  | Price per unit. |  |
| `ItemRatePercent` | float |  |  |  |
| `ItemTaxCode` | nvarchar(3) |  | Tax code for the line, such as Tax or Non. | Non (527), Tax (290),  (146) |
| `ItemTaxCodeId` | nvarchar(255) |  |  |  |
| `ItemAmount` | decimal |  | Line amount before tax. |  |
| `ItemClass` | nvarchar(255) |  |  |  |
| `ItemClassId` | nvarchar(255) |  |  |  |
| `ItemServiceDate` | datetime |  | Date the service was performed. |  |
| `ItemInventorySiteId` | nvarchar(31) |  |  |  |
| `ItemInventorySiteName` | nvarchar(255) |  |  |  |
| `ItemInventorySiteLocationId` | nvarchar(31) |  |  |  |
| `ItemInventorySiteLocationName` | nvarchar(255) |  |  |  |
| `ItemSerialNumber` | text(2147483647) |  |  |  |
| `ItemLotNumber` | nvarchar(40) |  |  |  |
| `ItemOther1` | nvarchar(29) |  |  |  |
| `ItemOther2` | nvarchar(29) |  |  |  |
| `ItemCustomFields` | nvarchar(2000) |  |  |  |
| `ItemUOMSetFullName` | nvarchar(255) |  |  |  |
| `ItemUOMSetListID` | nvarchar(255) |  |  |  |
| `ItemIsGetPrintItemsInGroup` | nvarchar(1000) |  |  |  |
| `AppliedAmount` | decimal |  |  |  |
| `Balance` | decimal |  |  |  |
| `CustomerTaxCode` | nvarchar(3) |  |  |  |
| `CustomerTaxCodeId` | nvarchar(255) |  |  |  |
| `IsToBePrinted` | bit |  |  |  |
| `IsToBeEmailed` | bit |  |  |  |
| `IsPaid` | bit |  |  |  |
| `IsTaxIncluded` | bit |  |  |  |
| `IsPending` | bit |  |  |  |
| `IsFinanceCharge` | nvarchar(16) |  |  |  |
| `Template` | nvarchar(100) |  |  |  |
| `TemplateId` | nvarchar(255) |  |  |  |
| `SuggestedDiscountAmount` | decimal |  |  |  |
| `SuggestedDiscountDate` | datetime |  |  |  |
| `ExchangeRate` | float |  |  |  |
| `BalanceInHomeCurrency` | float |  |  |  |
| `CurrencyName` | nvarchar(64) |  |  |  |
| `CurrencyId` | nvarchar(255) |  |  |  |
| `Other` | nvarchar(29) |  |  |  |
| `EditSequence` | nvarchar(16) |  |  |  |
| `TimeModified` | smalldatetime |  |  |  |
| `TimeCreated` | smalldatetime |  |  |  |

## Invoices

Sales invoices, one row per invoice header. Line detail is in InvoiceLineItems.

Grain: one row per invoice

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `ID` | nvarchar(255) | PK | Unique invoice ID (QuickBooks Desktop list ID). InvoiceLineItems.InvoiceId points here. |  |
| `ReferenceNumber` | nvarchar(21) |  | Invoice number shown to the customer. |  |
| `TxnNumber` | int |  |  |  |
| `CustomerName` | nvarchar(255) |  | Customer billed. |  |
| `CustomerId` | nvarchar(255) | FK to Customers | ID of the customer (Customers.ID). |  |
| `Account` | nvarchar(255) |  | Accounts Receivable account the invoice posts to. |  |
| `AccountId` | nvarchar(255) | FK to Accounts |  |  |
| `Date` | datetime |  | Invoice date. |  |
| `ShipMethod` | nvarchar(255) |  |  |  |
| `ShipMethodId` | nvarchar(255) |  |  |  |
| `ShipDate` | datetime |  |  |  |
| `Memo` | text(2147483647) |  |  |  |
| `Class` | nvarchar(255) |  |  |  |
| `ClassId` | nvarchar(255) | FK to Class |  |  |
| `Amount` | decimal |  | Invoice total. |  |
| `Message` | nvarchar(101) |  |  |  |
| `MessageId` | nvarchar(255) |  |  |  |
| `SalesRep` | nvarchar(5) |  |  |  |
| `SalesRepId` | nvarchar(255) |  |  |  |
| `FOB` | nvarchar(13) |  |  |  |
| `BillingAddress` | nvarchar(2000) |  |  |  |
| `BillingLine1` | nvarchar(500) |  |  |  |
| `BillingLine2` | nvarchar(500) |  |  |  |
| `BillingLine3` | nvarchar(500) |  |  |  |
| `BillingLine4` | nvarchar(500) |  |  |  |
| `BillingLine5` | nvarchar(41) |  |  |  |
| `BillingCity` | nvarchar(255) |  |  |  |
| `BillingState` | nvarchar(255) |  |  |  |
| `BillingPostalCode` | nvarchar(30) |  |  |  |
| `BillingCountry` | nvarchar(255) |  |  |  |
| `BillingNote` | nvarchar(41) |  |  |  |
| `ShippingAddress` | nvarchar(2000) |  |  |  |
| `ShippingLine1` | nvarchar(500) |  |  |  |
| `ShippingLine2` | nvarchar(500) |  |  |  |
| `ShippingLine3` | nvarchar(500) |  |  |  |
| `ShippingLine4` | nvarchar(500) |  |  |  |
| `ShippingLine5` | nvarchar(41) |  |  |  |
| `ShippingCity` | nvarchar(255) |  |  |  |
| `ShippingState` | nvarchar(255) |  |  |  |
| `ShippingPostalCode` | nvarchar(30) |  |  |  |
| `ShippingCountry` | nvarchar(255) |  |  |  |
| `ShippingNote` | nvarchar(41) |  |  |  |
| `Subtotal` | decimal |  |  |  |
| `Tax` | float |  |  |  |
| `TaxItem` | nvarchar(100) |  |  |  |
| `TaxItemId` | nvarchar(255) |  |  |  |
| `TaxPercent` | float |  |  |  |
| `POnumber` | nvarchar(25) |  |  |  |
| `DueDate` | datetime |  | Date payment is due. |  |
| `Terms` | nvarchar(100) |  | Payment terms, such as Net 30. | Net 30 (49),  (25), Net 15 (23), Due on receipt (9), Net 60 (5), 1% 10 Net 30 (2) |
| `TermsId` | nvarchar(255) |  |  |  |
| `ItemCount` | int |  |  |  |
| `ItemAggregate` | text(2147483647) |  |  |  |
| `TransactionCount` | int |  |  |  |
| `TransactionAggregate` | text(2147483647) |  |  |  |
| `AppliedAmount` | decimal |  | Payments and credits applied to the invoice so far. |  |
| `Balance` | decimal |  | Amount still open on the invoice. Zero means paid. |  |
| `CustomerTaxCode` | nvarchar(3) |  |  |  |
| `CustomerTaxCodeId` | nvarchar(255) |  |  |  |
| `IsToBePrinted` | bit |  | True if the invoice still needs to be printed. |  |
| `IsToBeEmailed` | bit |  | True if the invoice still needs to be emailed. | False (107), True (6) |
| `IsPaid` | bit |  | True when the invoice is paid in full. | True (92), False (21) |
| `IsTaxIncluded` | bit |  |  |  |
| `IsPending` | bit |  | True for a pending invoice that is not yet final. |  |
| `IsFinanceCharge` | nvarchar(16) |  |  |  |
| `Template` | nvarchar(100) |  |  |  |
| `TemplateId` | nvarchar(255) |  |  |  |
| `SuggestedDiscountAmount` | decimal |  |  |  |
| `SuggestedDiscountDate` | datetime |  |  |  |
| `ExchangeRate` | float |  |  |  |
| `BalanceInHomeCurrency` | float |  |  |  |
| `CurrencyName` | nvarchar(64) |  |  |  |
| `CurrencyId` | nvarchar(255) |  |  |  |
| `Other` | nvarchar(29) |  |  |  |
| `CustomFields` | nvarchar(2000) |  |  |  |
| `EditSequence` | nvarchar(16) |  |  |  |
| `TimeModified` | smalldatetime |  |  |  |
| `TimeCreated` | smalldatetime |  |  |  |

## Items

Items you sell or buy (inventory, service, non-inventory and others).

Grain: one row per item

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `ID` | nvarchar(255) | PK | Unique item ID. |  |
| `Name` | nvarchar(31) |  | Item name. |  |
| `FullName` | nvarchar(159) |  |  |  |
| `Type` | nvarchar(100) |  | Item type, such as Service, Inventory, NonInventory, OtherCharge or Discount. | Service (21), FixedAsset (17), NonInventory (14), Inventory (11), OtherCharge (9), Group (7), SalesTax (4), Payment (2), SubTotal (2), Discount (1), InventoryAssembly (1), SalesTaxGroup (1) |
| `Account` | nvarchar(255) |  | Account the item is linked to (for a sales item, its income account). |  |
| `AccountId` | nvarchar(255) |  |  |  |
| `COGSAccount` | nvarchar(255) |  |  |  |
| `COGSAccountId` | nvarchar(255) |  |  |  |
| `AssetAccount` | nvarchar(255) |  |  |  |
| `AssetAccountId` | nvarchar(255) |  |  |  |
| `Class` | nvarchar(255) |  |  |  |
| `ClassId` | nvarchar(255) |  |  |  |
| `DateSold` | smalldatetime |  |  |  |
| `PurchaseDate` | datetime |  |  |  |
| `ItemCount` | int |  |  |  |
| `ParentName` | nvarchar(255) |  |  |  |
| `ParentId` | nvarchar(255) |  |  |  |
| `Description` | text(2147483647) |  |  |  |
| `Price` | float |  | Default sales price. | 0.0 (42), (blank) (36), 35.0 (3), 0.365 (1), 30.0 (1), 38.0 (1), 55.0 (1), 72.0 (1), 120.0 (1), 200.0 (1), 1799.0 (1), 1899.98 (1) |
| `PricePercent` | float |  |  |  |
| `AverageCost` | float |  |  |  |
| `IsActive` | bit |  | True while the item is active. | True (89), False (1) |
| `PurchaseCost` | float |  | Default purchase cost. |  |
| `PurchaseDescription` | text(2147483647) |  |  |  |
| `ExpenseAccount` | nvarchar(255) |  |  |  |
| `ExpenseAccountId` | nvarchar(255) |  |  |  |
| `PreferredVendor` | nvarchar(255) |  |  |  |
| `PreferredVendorId` | nvarchar(255) |  |  |  |
| `QuantityOnHand` | float |  | Quantity in stock. | (blank) (78), 0.0 (2), 1.0 (1), 6.0 (1), 16.0 (1), 20.0 (1), 21.0 (1), 47.0 (1), 122.0 (1), 124.0 (1), 246.0 (1), 423.0 (1) |
| `QuantityOnOrder` | float |  |  |  |
| `QuantityOnSalesOrder` | float |  |  |  |
| `InventoryDate` | datetime |  |  |  |
| `ReorderPoint` | float |  |  |  |
| `Barcode` | nvarchar(50) |  |  |  |
| `TaxCode` | nvarchar(3) |  |  |  |
| `TaxCodeId` | nvarchar(255) |  |  |  |
| `IsTaxIncluded` | bit |  |  |  |
| `PurchaseTaxCode` | nvarchar(3) |  |  |  |
| `PurchaseTaxCodeId` | nvarchar(255) |  |  |  |
| `PartNumber` | nvarchar(31) |  |  |  |
| `PaymentMethodName` | nvarchar(100) |  |  |  |
| `PaymentMethodId` | nvarchar(255) |  |  |  |
| `TaxRate` | float |  |  |  |
| `TaxVendorName` | nvarchar(255) |  |  |  |
| `TaxVendorId` | nvarchar(255) |  |  |  |
| `SpecialItemType` | nvarchar(50) |  |  |  |
| `VendorOrPayeeName` | nvarchar(50) |  |  |  |
| `IsPrintItemsInGroup` | bit |  |  |  |
| `SalesExpense` | nvarchar(2000) |  |  |  |
| `AssetAcquiredAs` | nvarchar(3) |  |  |  |
| `AssetDescription` | text(2147483647) |  |  |  |
| `AssetLocation` | nvarchar(50) |  |  |  |
| `AssetPONumber` | nvarchar(30) |  |  |  |
| `AssetSerialNumber` | text(2147483647) |  |  |  |
| `AssetWarrantyExpires` | datetime |  |  |  |
| `AssetNotes` | text(2147483647) |  |  |  |
| `AssetNumber` | nvarchar(10) |  |  |  |
| `AssetCostBasis` | decimal |  |  |  |
| `AssetDepreciation` | float |  |  |  |
| `AssetBookValue` | float |  |  |  |
| `UnitOfMeasure` | nvarchar(31) |  |  |  |
| `UnitOfMeasureId` | nvarchar(255) |  |  |  |
| `Sublevel` | int |  |  |  |
| `LineAggregate` | text(2147483647) |  |  |  |
| `CustomFields` | nvarchar(2000) |  |  |  |
| `EditSequence` | nvarchar(16) |  |  |  |
| `TimeModified` | smalldatetime |  |  |  |
| `TimeCreated` | smalldatetime |  |  |  |

## JournalEntries

Journal entry headers. Debit and credit lines are in JournalEntryLines.

Grain: one row per journal entry

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `ID` | nvarchar(255) | PK | Unique journal entry ID. |  |
| `ReferenceNumber` | nvarchar(21) |  | Journal number. |  |
| `TxnNumber` | int |  |  |  |
| `Date` | datetime |  | Entry date. |  |
| `CreditLineCount` | int |  | Number of credit lines. | 1 (61), 3 (1), 4 (1), 7 (1) |
| `DebitLineCount` | int |  | Number of debit lines. | 1 (38), 3 (23), 2 (3) |
| `FirstCreditAccount` | nvarchar(255) |  |  |  |
| `FirstCreditAmount` | decimal |  |  |  |
| `FirstCreditMemo` | nvarchar(2000) |  |  |  |
| `FirstCreditEntityName` | nvarchar(2000) |  |  |  |
| `FirstCreditEntityId` | nvarchar(2000) |  |  |  |
| `FirstDebitAccount` | nvarchar(255) |  |  |  |
| `FirstDebitAmount` | decimal |  |  |  |
| `FirstDebitMemo` | nvarchar(2000) |  |  |  |
| `FirstDebitEntityName` | nvarchar(2000) |  |  |  |
| `FirstDebitEntityId` | nvarchar(2000) |  |  |  |
| `LineAggregate` | text(2147483647) |  |  |  |
| `CurrencyName` | nvarchar(64) |  |  |  |
| `CurrencyId` | nvarchar(255) |  |  |  |
| `ExchangeRate` | float |  |  |  |
| `IsHomeCurrencyAdjustment` | bit |  |  |  |
| `EditSequence` | nvarchar(16) |  |  |  |
| `TimeModified` | smalldatetime |  |  |  |
| `TimeCreated` | smalldatetime |  |  |  |

## JournalEntryLines

Debit and credit lines of each journal entry. Each entry balances.

Grain: one row per journal line

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `ID` | nvarchar(255) | PK |  |  |
| `JournalEntryID` | nvarchar(255) | FK to JournalEntries | Parent journal entry (JournalEntries.ID). |  |
| `ReferenceNumber` | nvarchar(21) |  |  |  |
| `TxnNumber` | int |  |  |  |
| `Date` | datetime |  |  |  |
| `CreditLineCount` | int |  |  |  |
| `DebitLineCount` | int |  |  |  |
| `LineId` | nvarchar(255) |  |  |  |
| `LineType` | nvarchar(100) |  | Whether the line is a Debit or a Credit. | Debit (113), Credit (75) |
| `LineAccount` | nvarchar(255) |  | Account the line posts to. |  |
| `LineAccountId` | nvarchar(255) | FK to Accounts |  |  |
| `LineAmount` | decimal |  | Amount of the line. |  |
| `LineEntityName` | nvarchar(1000) |  | Customer, vendor or employee linked to the line. | Overhead (178),  (6), Mason, Elizabeth (1), Miller, Dan (1), Sergeant Insurance (1), State Board of Equalization (1) |
| `LineEntityId` | nvarchar(255) |  |  |  |
| `LineMemo` | text(2147483647) |  | Memo on the line. |  |
| `LineClass` | nvarchar(255) |  |  |  |
| `LineClassId` | nvarchar(255) |  |  |  |
| `LineStatus` | nvarchar(13) |  |  |  |
| `LineTaxItem` | nvarchar(100) |  |  |  |
| `LineTaxItemId` | nvarchar(255) |  |  |  |
| `CurrencyName` | nvarchar(64) |  |  |  |
| `CurrencyId` | nvarchar(255) |  |  |  |
| `ExchangeRate` | float |  |  |  |
| `IsHomeCurrencyAdjustment` | bit |  |  |  |
| `EditSequence` | nvarchar(16) |  |  |  |
| `TimeModified` | smalldatetime |  |  |  |
| `TimeCreated` | smalldatetime |  |  |  |

## ReceivePayments

Customer payments received, one row per payment. Applied amounts are in ReceivePaymentsAppliedTo.

Grain: one row per payment

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `ID` | nvarchar(255) | PK | Unique payment ID. |  |
| `ReferenceNumber` | nvarchar(21) |  |  |  |
| `TxnNumber` | int |  |  |  |
| `Date` | datetime |  | Payment date. |  |
| `UnusedPayment` | decimal |  | Part of the payment not applied to an invoice. |  |
| `Amount` | decimal |  | Amount received. |  |
| `AccountsReceivableName` | nvarchar(255) |  |  |  |
| `AccountsReceivableId` | nvarchar(255) |  |  |  |
| `CustomerName` | nvarchar(255) |  | Customer who paid. |  |
| `CustomerId` | nvarchar(255) | FK to Customers |  |  |
| `DepositToAccountName` | nvarchar(255) |  | Account the money was deposited into. | Undeposited Funds (95), Checking (11) |
| `DepositToAccountId` | nvarchar(255) | FK to Accounts |  |  |
| `PaymentMethodName` | nvarchar(100) |  | How the customer paid, such as Check or Cash. | Check (93), Cash (5),  (3), Master Card (3), American Express (1), VISA (1) |
| `PaymentMethodId` | nvarchar(255) |  |  |  |
| `Memo` | text(2147483647) |  |  |  |
| `ExchangeRate` | float |  |  |  |
| `TotalAmountInHomeCurrency` | decimal |  |  |  |
| `AppliedToAggregate` | text(2147483647) |  |  |  |
| `CustomFields` | nvarchar(2000) |  |  |  |
| `TimeModified` | smalldatetime |  |  |  |
| `TimeCreated` | smalldatetime |  |  |  |

## SalesReceipts

Cash sales, paid at the time of sale. One row per receipt.

Grain: one row per sales receipt

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `ID` | nvarchar(255) | PK | Unique sales receipt ID. | 36F4-1071511667 (1), 3D7D-1071533088 (1), 3D82-1071533509 (1), 4D7-933290769 (1), 58F-933355431 (1), 5AA-933356715 (1), 7CE-933364306 (1), CE8-933782165 (1) |
| `ReferenceNumber` | nvarchar(21) |  |  |  |
| `TxnNumber` | int |  |  |  |
| `CustomerName` | nvarchar(255) |  | Customer. | Abercrombie, Kristy:Family Room (2), Ecker Designs:Office Repairs (2), Freeman, Kirby:Repairs (1), Keenan, Bridget:Sun Room (1), Natiello, Ernesto:Kitchen (1), Reyes Properties - C:Repairs (1) |
| `CustomerId` | nvarchar(255) | FK to Customers |  |  |
| `Date` | datetime |  | Sale date. | 2023-05-21T00:00:00 (1), 2023-09-11T00:00:00 (1), 2023-10-15T00:00:00 (1), 2023-10-25T00:00:00 (1), 2023-10-27T00:00:00 (1), 2023-10-30T00:00:00 (1), 2023-11-29T00:00:00 (1), 2023-12-10T00:00:00 (1) |
| `ShipMethod` | nvarchar(255) |  |  |  |
| `ShipMethodId` | nvarchar(255) |  |  |  |
| `ShipDate` | datetime |  |  |  |
| `Memo` | text(2147483647) |  |  |  |
| `Class` | nvarchar(255) |  |  |  |
| `ClassId` | nvarchar(255) | FK to Class |  |  |
| `DueDate` | datetime |  |  |  |
| `TotalAmount` | decimal |  | Total of the receipt. | 102.65 (1), 437.5 (1), 717.5 (1), 743.75 (1), 770.0 (1), 840.0 (1), 1067.88 (1), 3150.0 (1) |
| `Message` | nvarchar(101) |  |  |  |
| `MessageId` | nvarchar(255) |  |  |  |
| `SalesRep` | nvarchar(5) |  |  |  |
| `SalesRepId` | nvarchar(255) |  |  |  |
| `Template` | nvarchar(100) |  |  |  |
| `TemplateId` | nvarchar(255) |  |  |  |
| `ExchangeRate` | float |  |  |  |
| `FOB` | nvarchar(13) |  |  |  |
| `BillingAddress` | nvarchar(2000) |  |  |  |
| `BillingLine1` | nvarchar(500) |  |  |  |
| `BillingLine2` | nvarchar(500) |  |  |  |
| `BillingLine3` | nvarchar(500) |  |  |  |
| `BillingLine4` | nvarchar(500) |  |  |  |
| `BillingLine5` | nvarchar(41) |  |  |  |
| `BillingCity` | nvarchar(255) |  |  |  |
| `BillingState` | nvarchar(255) |  |  |  |
| `BillingPostalCode` | nvarchar(30) |  |  |  |
| `BillingCountry` | nvarchar(255) |  |  |  |
| `BillingNote` | nvarchar(41) |  |  |  |
| `ShippingAddress` | nvarchar(2000) |  |  |  |
| `ShippingLine1` | nvarchar(500) |  |  |  |
| `ShippingLine2` | nvarchar(500) |  |  |  |
| `ShippingLine3` | nvarchar(500) |  |  |  |
| `ShippingLine4` | nvarchar(500) |  |  |  |
| `ShippingLine5` | nvarchar(41) |  |  |  |
| `ShippingCity` | nvarchar(255) |  |  |  |
| `ShippingState` | nvarchar(255) |  |  |  |
| `ShippingPostalCode` | nvarchar(30) |  |  |  |
| `ShippingCountry` | nvarchar(255) |  |  |  |
| `ShippingNote` | nvarchar(41) |  |  |  |
| `Subtotal` | decimal |  |  |  |
| `Tax` | float |  |  |  |
| `TaxItem` | nvarchar(100) |  |  |  |
| `TaxItemId` | nvarchar(255) | FK to Items |  |  |
| `TaxPercent` | float |  |  |  |
| `IsPending` | bit |  |  |  |
| `IsToBePrinted` | bit |  |  |  |
| `IsTaxIncluded` | bit |  |  |  |
| `IsToBeEmailed` | bit |  |  |  |
| `ItemCount` | int |  |  |  |
| `ItemAggregate` | text(2147483647) |  |  |  |
| `CheckNumber` | nvarchar(25) |  |  |  |
| `PaymentMethod` | nvarchar(100) |  | How it was paid. | (4), Check (4) |
| `PaymentMethodId` | nvarchar(255) |  |  |  |
| `DepositAccount` | nvarchar(255) |  | Account the money went into. |  |
| `DepositAccountId` | nvarchar(255) | FK to Accounts |  |  |
| `CustomerTaxCode` | nvarchar(3) |  |  |  |
| `CustomerTaxCodeId` | nvarchar(255) |  |  |  |
| `CustomFields` | nvarchar(2000) |  |  |  |
| `EditSequence` | nvarchar(16) |  |  |  |
| `TimeModified` | smalldatetime |  |  |  |
| `TimeCreated` | smalldatetime |  |  |  |

## Vendors

Vendors (suppliers and payees).

Grain: one row per vendor

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `ID` | nvarchar(255) | PK | Unique vendor ID. |  |
| `Name` | nvarchar(100) |  | Vendor name. |  |
| `Salutation` | nvarchar(15) |  |  |  |
| `FirstName` | nvarchar(25) |  |  |  |
| `MiddleInitial` | nvarchar(25) |  |  |  |
| `LastName` | nvarchar(25) |  |  |  |
| `Company` | nvarchar(50) |  |  |  |
| `Contact` | nvarchar(1023) |  |  |  |
| `AccountNumber` | nvarchar(99) |  |  |  |
| `Type` | nvarchar(1000) |  | Vendor type. | (23), Subcontractors (10), Materials (7), Equipment (4), Permits (3), Utility (3), Consultant (2), Supplies (1), Tax agency (1) |
| `TypeId` | nvarchar(255) |  |  |  |
| `CreditLimit` | float |  |  |  |
| `TaxIdentity` | nvarchar(20) |  |  |  |
| `SalesTaxCountry` | nvarchar(100) |  |  |  |
| `AlternateContact` | nvarchar(41) |  |  |  |
| `Phone` | nvarchar(21) |  |  |  |
| `Fax` | nvarchar(21) |  |  |  |
| `AlternatePhone` | nvarchar(21) |  |  |  |
| `Email` | nvarchar(1023) |  |  |  |
| `Cc` | nvarchar(1023) |  |  |  |
| `Notes` | text(2147483647) |  |  |  |
| `Address` | nvarchar(2000) |  |  |  |
| `Line1` | nvarchar(500) |  |  |  |
| `Line2` | nvarchar(500) |  |  |  |
| `Line3` | nvarchar(500) |  |  |  |
| `Line4` | nvarchar(500) |  |  |  |
| `Line5` | nvarchar(41) |  |  |  |
| `City` | nvarchar(255) |  |  |  |
| `State` | nvarchar(255) |  |  |  |
| `PostalCode` | nvarchar(30) |  |  |  |
| `Country` | nvarchar(255) |  |  |  |
| `Note` | nvarchar(41) |  |  |  |
| `ShippingAddress` | nvarchar(2000) |  |  |  |
| `ShippingLine1` | nvarchar(500) |  |  |  |
| `ShippingLine2` | nvarchar(500) |  |  |  |
| `ShippingLine3` | nvarchar(500) |  |  |  |
| `ShippingLine4` | nvarchar(500) |  |  |  |
| `ShippingLine5` | nvarchar(41) |  |  |  |
| `ShippingCity` | nvarchar(255) |  |  |  |
| `ShippingState` | nvarchar(255) |  |  |  |
| `ShippingPostalCode` | nvarchar(30) |  |  |  |
| `ShippingCountry` | nvarchar(255) |  |  |  |
| `ShippingNote` | nvarchar(41) |  |  |  |
| `Balance` | float |  | Open amount owed to the vendor. |  |
| `Terms` | nvarchar(100) |  | Default payment terms. | Net 30 (20),  (18), Net 15 (10), 2% 10 Net 30 (2), Due on receipt (2), Net 60 (2) |
| `TermsId` | nvarchar(255) |  |  |  |
| `EligibleFor1099` | bit |  | True if the vendor is tracked for Form 1099. | False (43), True (11) |
| `NameOnCheck` | nvarchar(110) |  |  |  |
| `CurrencyName` | nvarchar(64) |  |  |  |
| `CurrencyId` | nvarchar(255) |  |  |  |
| `IsActive` | bit |  | True while the vendor is active. |  |
| `CustomFields` | nvarchar(2000) |  |  |  |
| `EditSequence` | nvarchar(16) |  |  |  |
| `TimeModified` | smalldatetime |  |  |  |
| `TimeCreated` | smalldatetime |  |  |  |
| `PrefillAccountId1` | nvarchar(2000) |  |  |  |
| `PrefillAccountName1` | nvarchar(2000) |  |  |  |
| `PrefillAccountId2` | nvarchar(2000) |  |  |  |
| `PrefillAccountName2` | nvarchar(2000) |  |  |  |
| `PrefillAccountId3` | nvarchar(2000) |  |  |  |
| `PrefillAccountName3` | nvarchar(2000) |  |  |  |

## Undocumented tables

`BillExpenseItems`, `BillLineItems`, `BillPaymentChecks`, `BillPaymentChecksAppliedTo`, `BillPaymentCreditCards`, `BillPaymentCreditCardsAppliedTo`, `BuildAssemblies`, `BuildAssemblyLineItems`, `CheckExpenseItems`, `CheckLineItems`, `Class`, `CreditCardChargeExpenseItems`, `CreditCardChargeLineItems`, `CreditCardCharges`, `CreditCardCreditExpenseItems`, `CreditCardCreditLineItems`, `CreditCardCredits`, `CreditCardRefunds`, `CreditMemoLineItems`, `CreditMemos`, `Currency`, `CustomerContacts`, `CustomerMessages`, `CustomerNotes`, `CustomerShippingAddresses`, `CustomerTypes`, `DateDrivenTerms`, `DepositLineItems`, `Deposits`, `EmployeeEarnings`, `Employees`, `EstimateLineItems`, `Estimates`, `InventoryAdjustmentLineItems`, `InventoryAdjustments`, `InventorySites`, `ItemLineItems`, `ItemReceiptExpenseItems`, `ItemReceiptLineItems`, `ItemReceipts`, `JobTypes`, `OtherNames`, `PaymentMethods`, `PayrollNonWageItems`, `PayrollWageItems`, `PriceLevelPerItem`, `PriceLevels`, `PurchaseOrderLineItems`, `PurchaseOrders`, `ReceivePaymentsAppliedTo`, `SalesOrderLineItems`, `SalesOrders`, `SalesReceiptLineItems`, `SalesReps`, `SalesTaxCodes`, `SalesTaxItems`, `ShippingMethods`, `StandardTerms`, `StatementCharges`, `TimeTracking`, `ToDo`, `TransferInventory`, `TransferInventoryLineItems`, `Transfers`, `UnitOfMeasure`, `UnitOfMeasureDefaultUnits`, `UnitOfMeasureRelatedUnits`, `VehicleMileage`, `VendorCreditExpenseItems`, `VendorCreditLineItems`, `VendorCredits`, `VendorTypes`
