Start here
Lessons and reference
Look things up
Start here
SQL
Power Query
Data modeling
Power BI and DAX
Finance playbooks
Run queries
Build and check
Accounting data shaped like QuickBooks Desktop: customers, vendors, invoices, bills, payments, accounts and journal entries.
Write each as a query in the SQL editor, or ask an AI assistant to walk you through it.
Server learnsql.cpetoday.com,1433, database qbd, in Excel or Power BI (Import mode) or
DBeaver. In queries use the schema-qualified name shown for each table (for example qbd.Accounts).
Download the Excel connection (.odc)
How to use it: download the file and open it. Excel opens it as a connection to qbd
and asks for the SQL Server username and password from your CPE Today course.
The file holds no password. The first table is selected by default, and you can pick others from the Data window.
Setup details for other tools are on Connect.
Click a table to preview it in the Playground.
What each column means, its type and keys, and for codes and statuses the values seen in this copy. 12 of 84 tables are described so far. Read it as Markdown.
Click a table in the diagram above to jump to it.
The chart of accounts.
| 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) | Not described yet | ||
Number |
nvarchar(2000) | Account number. | ||
Balance |
float | Current balance of the account. | ||
AccountBalance |
float | Not described yet | ||
BankAccount |
nvarchar(2000) | Not described yet | ||
Description |
nvarchar(200) | Not described yet | ||
IsActive |
bit | True while the account is active. | True 114 False 2 | |
ParentName |
nvarchar(255) | Parent account, for sub-accounts. | ||
ParentId |
nvarchar(255) | Not described yet | ||
Sublevel |
int | How deep the account sits in the parent-child tree (0 for top level). | 0 63 1 53 | |
CurrencyName |
nvarchar(64) | Not described yet | ||
CurrencyId |
nvarchar(255) | Not described yet | ||
CashFlowClassification |
nvarchar(2000) | Not described yet | ||
TaxLineName |
nvarchar(256) | Not described yet | ||
TaxLineId |
nvarchar(255) | Not described yet | ||
TimeModified |
smalldatetime | Not described yet | ||
TimeCreated |
smalldatetime | Not described yet |
Vendor bills (payables), 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 | Not described yet | |
ReferenceNumber |
nvarchar(21) | Vendor's reference or invoice number. | ||
Date |
datetime | Bill date. | ||
Amount |
decimal | Bill total. | ||
TxnNumber |
int | Not described yet | ||
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) | Not described yet | ||
AccountsPayable |
nvarchar(255) | Accounts Payable account the bill posts to. | ||
AccountsPayableId |
nvarchar(255) | Not described yet | ||
Memo |
text(2147483647) | Not described yet | ||
IsPaid |
bit | True when the bill is paid in full. | True 166 False 28 | |
ExchangeRate |
float | Not described yet | ||
IsTaxIncluded |
bit | Not described yet | ||
ItemCount |
int | Not described yet | ||
ItemAggregate |
text(2147483647) | Not described yet | ||
ExpenseItemCount |
int | Not described yet | ||
ExpenseItemAggregate |
text(2147483647) | Not described yet | ||
TransactionCount |
int | Not described yet | ||
TransactionAggregate |
text(2147483647) | Not described yet | ||
CurrencyName |
nvarchar(64) | Not described yet | ||
CurrencyId |
nvarchar(255) | Not described yet | ||
CustomFields |
nvarchar(2000) | Not described yet | ||
TimeModified |
smalldatetime | Not described yet | ||
TimeCreated |
smalldatetime | Not described yet | ||
OpenAmount |
decimal | Amount still owed on the bill. |
Checks written, one row per check.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
ID |
nvarchar(255) | PK | Unique check ID. | |
ReferenceNumber |
nvarchar(21) | Check number. | ||
TxnNumber |
int | Not described yet | ||
Account |
nvarchar(255) | Bank account the check is drawn on. | ||
AccountId |
nvarchar(255) | FK to Accounts | Not described yet | |
Payee |
nvarchar(255) | Vendor or person paid. | ||
PayeeId |
nvarchar(255) | Not described yet | ||
Date |
datetime | Check date. | ||
Amount |
decimal | Check amount. | ||
Memo |
text(2147483647) | Not described yet | ||
Address |
nvarchar(2000) | Not described yet | ||
Line1 |
nvarchar(500) | Not described yet | ||
Line2 |
nvarchar(500) | Not described yet | ||
Line3 |
nvarchar(500) | Not described yet | ||
Line4 |
nvarchar(500) | Not described yet | ||
Line5 |
nvarchar(41) | Not described yet | ||
City |
nvarchar(255) | Not described yet | ||
State |
nvarchar(255) | Not described yet | ||
PostalCode |
nvarchar(30) | Not described yet | ||
Country |
nvarchar(255) | Not described yet | ||
Note |
nvarchar(41) | Not described yet | ||
ItemCount |
int | Not described yet | ||
ItemAggregate |
text(2147483647) | Not described yet | ||
ExpenseItemCount |
int | Not described yet | ||
ExpenseItemAggregate |
text(2147483647) | Not described yet | ||
IsToBePrinted |
bit | Not described yet | ||
IsTaxIncluded |
bit | Not described yet | ||
CurrencyName |
nvarchar(64) | Not described yet | ||
CurrencyId |
nvarchar(255) | Not described yet | ||
ExchangeRate |
float | Not described yet | ||
CustomFields |
nvarchar(2000) | Not described yet | ||
TimeModified |
smalldatetime | Not described yet | ||
TimeCreated |
smalldatetime | Not described yet |
Customers and jobs. Jobs are sub-customers with a ParentName.
| 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) | Not described yet | ||
FirstName |
nvarchar(25) | Not described yet | ||
MiddleInitial |
nvarchar(5) | Not described yet | ||
LastName |
nvarchar(25) | Not described yet | ||
AccountNumber |
nvarchar(99) | Not described yet | ||
Company |
nvarchar(41) | Company name, when the customer is a business. | ||
Balance |
float | Open amount the customer owes (receivable). | ||
CustomerBalance |
float | Not described yet | ||
Contact |
nvarchar(41) | Not described yet | ||
Type |
nvarchar(255) | Customer type, such as Retail or Wholesale. | Residential 112 Commercial 33 1 | |
TypeId |
nvarchar(255) | Not described yet | ||
Phone |
nvarchar(21) | Not described yet | ||
Fax |
nvarchar(21) | Not described yet | ||
AlternateContact |
nvarchar(41) | Not described yet | ||
AlternatePhone |
nvarchar(21) | Not described yet | ||
Email |
nvarchar(1023) | Not described yet | ||
Cc |
nvarchar(1023) | Not described yet | ||
AdditionalContactInfo |
nvarchar(2000) | Not described yet | ||
ContactsAggregate |
text(2147483647) | Not described yet | ||
ClassName |
nvarchar(2000) | Not described yet | ||
ClassId |
nvarchar(2000) | Not described yet | ||
Notes |
text(2147483647) | Not described yet | ||
NotesAggregate |
text(2147483647) | Not described yet | ||
ParentName |
nvarchar(255) | Parent customer, for jobs. | ||
ParentId |
nvarchar(255) | Not described yet | ||
Sublevel |
int | Not described yet | ||
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 | Not described yet | ||
JobProjectedEndDate |
datetime | Not described yet | ||
JobEndDate |
datetime | Not described yet | ||
JobDescription |
nvarchar(99) | Not described yet | ||
JobType |
nvarchar(255) | Not described yet | ||
JobTypeId |
nvarchar(255) | Not described yet | ||
CreditCardAddress |
nvarchar(41) | Not described yet | ||
CreditCardExpMonth |
int | Not described yet | ||
CreditCardExpYear |
int | Not described yet | ||
CreditCardNameOnCard |
nvarchar(41) | Not described yet | ||
CreditCardNumber |
nvarchar(25) | Not described yet | ||
CreditCardPostalCode |
nvarchar(41) | Not described yet | ||
CreditLimit |
float | Maximum credit allowed for the customer. | ||
BillingAddress |
nvarchar(2000) | Not described yet | ||
BillingLine1 |
nvarchar(41) | Not described yet | ||
BillingLine2 |
nvarchar(41) | Not described yet | ||
BillingLine3 |
nvarchar(41) | Not described yet | ||
BillingLine4 |
nvarchar(41) | Not described yet | ||
BillingLine5 |
nvarchar(41) | Not described yet | ||
BillingCity |
nvarchar(31) | Not described yet | ||
BillingState |
nvarchar(21) | Not described yet | ||
BillingPostalCode |
nvarchar(13) | Not described yet | ||
BillingCountry |
nvarchar(31) | Not described yet | ||
BillingNote |
nvarchar(41) | Not described yet | ||
ShippingAddress |
nvarchar(2000) | Not described yet | ||
ShippingLine1 |
nvarchar(41) | Not described yet | ||
ShippingLine2 |
nvarchar(41) | Not described yet | ||
ShippingLine3 |
nvarchar(41) | Not described yet | ||
ShippingLine4 |
nvarchar(41) | Not described yet | ||
ShippingLine5 |
nvarchar(41) | Not described yet | ||
ShippingCity |
nvarchar(31) | Not described yet | ||
ShippingState |
nvarchar(21) | Not described yet | ||
ShippingPostalCode |
nvarchar(13) | Not described yet | ||
ShippingCountry |
nvarchar(31) | Not described yet | ||
ShippingNote |
nvarchar(41) | Not described yet | ||
ShippingAggregate |
text(2147483647) | Not described yet | ||
ResaleNumber |
nvarchar(16) | Not described yet | ||
SalesRep |
nvarchar(5) | Sales representative assigned to the customer. | ||
SalesRepId |
nvarchar(255) | Not described yet | ||
Terms |
nvarchar(100) | Not described yet | ||
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) | Not described yet | ||
CurrencyId |
nvarchar(255) | Not described yet | ||
TaxCode |
nvarchar(3) | Not described yet | ||
TaxCodeId |
nvarchar(255) | Not described yet | ||
TaxItem |
nvarchar(100) | Not described yet | ||
TaxItemId |
nvarchar(255) | Not described yet | ||
SalesTaxCountry |
nvarchar(100) | Not described yet | ||
PriceLevel |
nvarchar(100) | Price level applied to the customer. | 140 Pretell Real Estate Rates 3 Residential 2 Kitchen Upgrade Package 1 | |
PriceLevelId |
nvarchar(255) | Not described yet | ||
PreferredDeliveryMethod |
nvarchar(20) | Not described yet | ||
PreferredPaymentMethodName |
nvarchar(100) | Not described yet | ||
PreferredPaymentMethodId |
nvarchar(255) | Not described yet | ||
IsActive |
bit | True while the customer is active. | ||
CustomFields |
nvarchar(2000) | Not described yet | ||
EditSequence |
nvarchar(16) | Not described yet | ||
TimeModified |
smalldatetime | Not described yet | ||
TimeCreated |
smalldatetime | Not described yet |
Invoice lines, one row per item line on an invoice.
| 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) | Not described yet | ||
TxnNumber |
int | Not described yet | ||
CustomerName |
nvarchar(255) | Not described yet | ||
CustomerId |
nvarchar(255) | FK to Customers | Not described yet | |
Account |
nvarchar(255) | Not described yet | ||
AccountId |
nvarchar(255) | FK to Accounts | Not described yet | |
Date |
datetime | Not described yet | ||
ShipMethod |
nvarchar(255) | Not described yet | ||
ShipMethodId |
nvarchar(255) | Not described yet | ||
ShipDate |
datetime | Not described yet | ||
Memo |
text(2147483647) | Not described yet | ||
Class |
nvarchar(255) | Not described yet | ||
ClassId |
nvarchar(255) | FK to Class | Not described yet | |
Amount |
decimal | Not described yet | ||
Message |
nvarchar(101) | Not described yet | ||
MessageId |
nvarchar(255) | Not described yet | ||
SalesRep |
nvarchar(5) | Not described yet | ||
SalesRepId |
nvarchar(255) | Not described yet | ||
FOB |
nvarchar(13) | Not described yet | ||
BillingAddress |
nvarchar(2000) | Not described yet | ||
BillingLine1 |
nvarchar(500) | Not described yet | ||
BillingLine2 |
nvarchar(500) | Not described yet | ||
BillingLine3 |
nvarchar(500) | Not described yet | ||
BillingLine4 |
nvarchar(500) | Not described yet | ||
BillingLine5 |
nvarchar(41) | Not described yet | ||
BillingCity |
nvarchar(255) | Not described yet | ||
BillingState |
nvarchar(255) | Not described yet | ||
BillingPostalCode |
nvarchar(30) | Not described yet | ||
BillingCountry |
nvarchar(255) | Not described yet | ||
BillingNote |
nvarchar(41) | Not described yet | ||
ShippingAddress |
nvarchar(2000) | Not described yet | ||
ShippingLine1 |
nvarchar(500) | Not described yet | ||
ShippingLine2 |
nvarchar(500) | Not described yet | ||
ShippingLine3 |
nvarchar(500) | Not described yet | ||
ShippingLine4 |
nvarchar(500) | Not described yet | ||
ShippingLine5 |
nvarchar(41) | Not described yet | ||
ShippingCity |
nvarchar(255) | Not described yet | ||
ShippingState |
nvarchar(255) | Not described yet | ||
ShippingPostalCode |
nvarchar(30) | Not described yet | ||
ShippingCountry |
nvarchar(255) | Not described yet | ||
ShippingNote |
nvarchar(41) | Not described yet | ||
Subtotal |
decimal | Not described yet | ||
Tax |
float | Not described yet | ||
TaxItem |
nvarchar(100) | Not described yet | ||
TaxItemId |
nvarchar(255) | Not described yet | ||
TaxPercent |
float | Not described yet | ||
PONumber |
nvarchar(25) | Not described yet | ||
DueDate |
datetime | Not described yet | ||
Terms |
nvarchar(100) | Not described yet | ||
TermsId |
nvarchar(255) | Not described yet | ||
CustomFields |
nvarchar(2000) | Not described yet | ||
ItemLineId |
nvarchar(2000) | Not described yet | ||
ItemLineNumber |
nvarchar(255) | Not described yet | ||
ItemName |
nvarchar(255) | Item sold on the line (Items.Name). | ||
ItemId |
nvarchar(255) | Not described yet | ||
ItemGroup |
nvarchar(100) | Not described yet | ||
ItemGroupId |
nvarchar(255) | Not described yet | ||
ItemDescription |
text(2147483647) | Description printed on the line. | ||
ItemUnitOfMeasure |
nvarchar(31) | Not described yet | ||
ItemQuantity |
float | Quantity sold. | ||
ItemRate |
float | Price per unit. | ||
ItemRatePercent |
float | Not described yet | ||
ItemTaxCode |
nvarchar(3) | Tax code for the line, such as Tax or Non. | Non 527 Tax 290 146 | |
ItemTaxCodeId |
nvarchar(255) | Not described yet | ||
ItemAmount |
decimal | Line amount before tax. | ||
ItemClass |
nvarchar(255) | Not described yet | ||
ItemClassId |
nvarchar(255) | Not described yet | ||
ItemServiceDate |
datetime | Date the service was performed. | ||
ItemInventorySiteId |
nvarchar(31) | Not described yet | ||
ItemInventorySiteName |
nvarchar(255) | Not described yet | ||
ItemInventorySiteLocationId |
nvarchar(31) | Not described yet | ||
ItemInventorySiteLocationName |
nvarchar(255) | Not described yet | ||
ItemSerialNumber |
text(2147483647) | Not described yet | ||
ItemLotNumber |
nvarchar(40) | Not described yet | ||
ItemOther1 |
nvarchar(29) | Not described yet | ||
ItemOther2 |
nvarchar(29) | Not described yet | ||
ItemCustomFields |
nvarchar(2000) | Not described yet | ||
ItemUOMSetFullName |
nvarchar(255) | Not described yet | ||
ItemUOMSetListID |
nvarchar(255) | Not described yet | ||
ItemIsGetPrintItemsInGroup |
nvarchar(1000) | Not described yet | ||
AppliedAmount |
decimal | Not described yet | ||
Balance |
decimal | Not described yet | ||
CustomerTaxCode |
nvarchar(3) | Not described yet | ||
CustomerTaxCodeId |
nvarchar(255) | Not described yet | ||
IsToBePrinted |
bit | Not described yet | ||
IsToBeEmailed |
bit | Not described yet | ||
IsPaid |
bit | Not described yet | ||
IsTaxIncluded |
bit | Not described yet | ||
IsPending |
bit | Not described yet | ||
IsFinanceCharge |
nvarchar(16) | Not described yet | ||
Template |
nvarchar(100) | Not described yet | ||
TemplateId |
nvarchar(255) | Not described yet | ||
SuggestedDiscountAmount |
decimal | Not described yet | ||
SuggestedDiscountDate |
datetime | Not described yet | ||
ExchangeRate |
float | Not described yet | ||
BalanceInHomeCurrency |
float | Not described yet | ||
CurrencyName |
nvarchar(64) | Not described yet | ||
CurrencyId |
nvarchar(255) | Not described yet | ||
Other |
nvarchar(29) | Not described yet | ||
EditSequence |
nvarchar(16) | Not described yet | ||
TimeModified |
smalldatetime | Not described yet | ||
TimeCreated |
smalldatetime | Not described yet |
Sales invoices, one row per invoice header. Line detail is in InvoiceLineItems.
| 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 | Not described yet | ||
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 | Not described yet | |
Date |
datetime | Invoice date. | ||
ShipMethod |
nvarchar(255) | Not described yet | ||
ShipMethodId |
nvarchar(255) | Not described yet | ||
ShipDate |
datetime | Not described yet | ||
Memo |
text(2147483647) | Not described yet | ||
Class |
nvarchar(255) | Not described yet | ||
ClassId |
nvarchar(255) | FK to Class | Not described yet | |
Amount |
decimal | Invoice total. | ||
Message |
nvarchar(101) | Not described yet | ||
MessageId |
nvarchar(255) | Not described yet | ||
SalesRep |
nvarchar(5) | Not described yet | ||
SalesRepId |
nvarchar(255) | Not described yet | ||
FOB |
nvarchar(13) | Not described yet | ||
BillingAddress |
nvarchar(2000) | Not described yet | ||
BillingLine1 |
nvarchar(500) | Not described yet | ||
BillingLine2 |
nvarchar(500) | Not described yet | ||
BillingLine3 |
nvarchar(500) | Not described yet | ||
BillingLine4 |
nvarchar(500) | Not described yet | ||
BillingLine5 |
nvarchar(41) | Not described yet | ||
BillingCity |
nvarchar(255) | Not described yet | ||
BillingState |
nvarchar(255) | Not described yet | ||
BillingPostalCode |
nvarchar(30) | Not described yet | ||
BillingCountry |
nvarchar(255) | Not described yet | ||
BillingNote |
nvarchar(41) | Not described yet | ||
ShippingAddress |
nvarchar(2000) | Not described yet | ||
ShippingLine1 |
nvarchar(500) | Not described yet | ||
ShippingLine2 |
nvarchar(500) | Not described yet | ||
ShippingLine3 |
nvarchar(500) | Not described yet | ||
ShippingLine4 |
nvarchar(500) | Not described yet | ||
ShippingLine5 |
nvarchar(41) | Not described yet | ||
ShippingCity |
nvarchar(255) | Not described yet | ||
ShippingState |
nvarchar(255) | Not described yet | ||
ShippingPostalCode |
nvarchar(30) | Not described yet | ||
ShippingCountry |
nvarchar(255) | Not described yet | ||
ShippingNote |
nvarchar(41) | Not described yet | ||
Subtotal |
decimal | Not described yet | ||
Tax |
float | Not described yet | ||
TaxItem |
nvarchar(100) | Not described yet | ||
TaxItemId |
nvarchar(255) | Not described yet | ||
TaxPercent |
float | Not described yet | ||
POnumber |
nvarchar(25) | Not described yet | ||
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) | Not described yet | ||
ItemCount |
int | Not described yet | ||
ItemAggregate |
text(2147483647) | Not described yet | ||
TransactionCount |
int | Not described yet | ||
TransactionAggregate |
text(2147483647) | Not described yet | ||
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) | Not described yet | ||
CustomerTaxCodeId |
nvarchar(255) | Not described yet | ||
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 | Not described yet | ||
IsPending |
bit | True for a pending invoice that is not yet final. | ||
IsFinanceCharge |
nvarchar(16) | Not described yet | ||
Template |
nvarchar(100) | Not described yet | ||
TemplateId |
nvarchar(255) | Not described yet | ||
SuggestedDiscountAmount |
decimal | Not described yet | ||
SuggestedDiscountDate |
datetime | Not described yet | ||
ExchangeRate |
float | Not described yet | ||
BalanceInHomeCurrency |
float | Not described yet | ||
CurrencyName |
nvarchar(64) | Not described yet | ||
CurrencyId |
nvarchar(255) | Not described yet | ||
Other |
nvarchar(29) | Not described yet | ||
CustomFields |
nvarchar(2000) | Not described yet | ||
EditSequence |
nvarchar(16) | Not described yet | ||
TimeModified |
smalldatetime | Not described yet | ||
TimeCreated |
smalldatetime | Not described yet |
Items you sell or buy (inventory, service, non-inventory and others).
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
ID |
nvarchar(255) | PK | Unique item ID. | |
Name |
nvarchar(31) | Item name. | ||
FullName |
nvarchar(159) | Not described yet | ||
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) | Not described yet | ||
COGSAccount |
nvarchar(255) | Not described yet | ||
COGSAccountId |
nvarchar(255) | Not described yet | ||
AssetAccount |
nvarchar(255) | Not described yet | ||
AssetAccountId |
nvarchar(255) | Not described yet | ||
Class |
nvarchar(255) | Not described yet | ||
ClassId |
nvarchar(255) | Not described yet | ||
DateSold |
smalldatetime | Not described yet | ||
PurchaseDate |
datetime | Not described yet | ||
ItemCount |
int | Not described yet | ||
ParentName |
nvarchar(255) | Not described yet | ||
ParentId |
nvarchar(255) | Not described yet | ||
Description |
text(2147483647) | Not described yet | ||
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 | Not described yet | ||
AverageCost |
float | Not described yet | ||
IsActive |
bit | True while the item is active. | True 89 False 1 | |
PurchaseCost |
float | Default purchase cost. | ||
PurchaseDescription |
text(2147483647) | Not described yet | ||
ExpenseAccount |
nvarchar(255) | Not described yet | ||
ExpenseAccountId |
nvarchar(255) | Not described yet | ||
PreferredVendor |
nvarchar(255) | Not described yet | ||
PreferredVendorId |
nvarchar(255) | Not described yet | ||
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 | Not described yet | ||
QuantityOnSalesOrder |
float | Not described yet | ||
InventoryDate |
datetime | Not described yet | ||
ReorderPoint |
float | Not described yet | ||
Barcode |
nvarchar(50) | Not described yet | ||
TaxCode |
nvarchar(3) | Not described yet | ||
TaxCodeId |
nvarchar(255) | Not described yet | ||
IsTaxIncluded |
bit | Not described yet | ||
PurchaseTaxCode |
nvarchar(3) | Not described yet | ||
PurchaseTaxCodeId |
nvarchar(255) | Not described yet | ||
PartNumber |
nvarchar(31) | Not described yet | ||
PaymentMethodName |
nvarchar(100) | Not described yet | ||
PaymentMethodId |
nvarchar(255) | Not described yet | ||
TaxRate |
float | Not described yet | ||
TaxVendorName |
nvarchar(255) | Not described yet | ||
TaxVendorId |
nvarchar(255) | Not described yet | ||
SpecialItemType |
nvarchar(50) | Not described yet | ||
VendorOrPayeeName |
nvarchar(50) | Not described yet | ||
IsPrintItemsInGroup |
bit | Not described yet | ||
SalesExpense |
nvarchar(2000) | Not described yet | ||
AssetAcquiredAs |
nvarchar(3) | Not described yet | ||
AssetDescription |
text(2147483647) | Not described yet | ||
AssetLocation |
nvarchar(50) | Not described yet | ||
AssetPONumber |
nvarchar(30) | Not described yet | ||
AssetSerialNumber |
text(2147483647) | Not described yet | ||
AssetWarrantyExpires |
datetime | Not described yet | ||
AssetNotes |
text(2147483647) | Not described yet | ||
AssetNumber |
nvarchar(10) | Not described yet | ||
AssetCostBasis |
decimal | Not described yet | ||
AssetDepreciation |
float | Not described yet | ||
AssetBookValue |
float | Not described yet | ||
UnitOfMeasure |
nvarchar(31) | Not described yet | ||
UnitOfMeasureId |
nvarchar(255) | Not described yet | ||
Sublevel |
int | Not described yet | ||
LineAggregate |
text(2147483647) | Not described yet | ||
CustomFields |
nvarchar(2000) | Not described yet | ||
EditSequence |
nvarchar(16) | Not described yet | ||
TimeModified |
smalldatetime | Not described yet | ||
TimeCreated |
smalldatetime | Not described yet |
Journal entry headers. Debit and credit lines are in JournalEntryLines.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
ID |
nvarchar(255) | PK | Unique journal entry ID. | |
ReferenceNumber |
nvarchar(21) | Journal number. | ||
TxnNumber |
int | Not described yet | ||
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) | Not described yet | ||
FirstCreditAmount |
decimal | Not described yet | ||
FirstCreditMemo |
nvarchar(2000) | Not described yet | ||
FirstCreditEntityName |
nvarchar(2000) | Not described yet | ||
FirstCreditEntityId |
nvarchar(2000) | Not described yet | ||
FirstDebitAccount |
nvarchar(255) | Not described yet | ||
FirstDebitAmount |
decimal | Not described yet | ||
FirstDebitMemo |
nvarchar(2000) | Not described yet | ||
FirstDebitEntityName |
nvarchar(2000) | Not described yet | ||
FirstDebitEntityId |
nvarchar(2000) | Not described yet | ||
LineAggregate |
text(2147483647) | Not described yet | ||
CurrencyName |
nvarchar(64) | Not described yet | ||
CurrencyId |
nvarchar(255) | Not described yet | ||
ExchangeRate |
float | Not described yet | ||
IsHomeCurrencyAdjustment |
bit | Not described yet | ||
EditSequence |
nvarchar(16) | Not described yet | ||
TimeModified |
smalldatetime | Not described yet | ||
TimeCreated |
smalldatetime | Not described yet |
Debit and credit lines of each journal entry. Each entry balances.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
ID |
nvarchar(255) | PK | Not described yet | |
JournalEntryID |
nvarchar(255) | FK to JournalEntries | Parent journal entry (JournalEntries.ID). | |
ReferenceNumber |
nvarchar(21) | Not described yet | ||
TxnNumber |
int | Not described yet | ||
Date |
datetime | Not described yet | ||
CreditLineCount |
int | Not described yet | ||
DebitLineCount |
int | Not described yet | ||
LineId |
nvarchar(255) | Not described yet | ||
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 | Not described yet | |
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) | Not described yet | ||
LineMemo |
text(2147483647) | Memo on the line. | ||
LineClass |
nvarchar(255) | Not described yet | ||
LineClassId |
nvarchar(255) | Not described yet | ||
LineStatus |
nvarchar(13) | Not described yet | ||
LineTaxItem |
nvarchar(100) | Not described yet | ||
LineTaxItemId |
nvarchar(255) | Not described yet | ||
CurrencyName |
nvarchar(64) | Not described yet | ||
CurrencyId |
nvarchar(255) | Not described yet | ||
ExchangeRate |
float | Not described yet | ||
IsHomeCurrencyAdjustment |
bit | Not described yet | ||
EditSequence |
nvarchar(16) | Not described yet | ||
TimeModified |
smalldatetime | Not described yet | ||
TimeCreated |
smalldatetime | Not described yet |
Customer payments received, one row per payment. Applied amounts are in ReceivePaymentsAppliedTo.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
ID |
nvarchar(255) | PK | Unique payment ID. | |
ReferenceNumber |
nvarchar(21) | Not described yet | ||
TxnNumber |
int | Not described yet | ||
Date |
datetime | Payment date. | ||
UnusedPayment |
decimal | Part of the payment not applied to an invoice. | ||
Amount |
decimal | Amount received. | ||
AccountsReceivableName |
nvarchar(255) | Not described yet | ||
AccountsReceivableId |
nvarchar(255) | Not described yet | ||
CustomerName |
nvarchar(255) | Customer who paid. | ||
CustomerId |
nvarchar(255) | FK to Customers | Not described yet | |
DepositToAccountName |
nvarchar(255) | Account the money was deposited into. | Undeposited Funds 95 Checking 11 | |
DepositToAccountId |
nvarchar(255) | FK to Accounts | Not described yet | |
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) | Not described yet | ||
Memo |
text(2147483647) | Not described yet | ||
ExchangeRate |
float | Not described yet | ||
TotalAmountInHomeCurrency |
decimal | Not described yet | ||
AppliedToAggregate |
text(2147483647) | Not described yet | ||
CustomFields |
nvarchar(2000) | Not described yet | ||
TimeModified |
smalldatetime | Not described yet | ||
TimeCreated |
smalldatetime | Not described yet |
Cash sales, paid at the time of sale. One row per 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) | Not described yet | ||
TxnNumber |
int | Not described yet | ||
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 | Not described yet | |
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) | Not described yet | ||
ShipMethodId |
nvarchar(255) | Not described yet | ||
ShipDate |
datetime | Not described yet | ||
Memo |
text(2147483647) | Not described yet | ||
Class |
nvarchar(255) | Not described yet | ||
ClassId |
nvarchar(255) | FK to Class | Not described yet | |
DueDate |
datetime | Not described yet | ||
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) | Not described yet | ||
MessageId |
nvarchar(255) | Not described yet | ||
SalesRep |
nvarchar(5) | Not described yet | ||
SalesRepId |
nvarchar(255) | Not described yet | ||
Template |
nvarchar(100) | Not described yet | ||
TemplateId |
nvarchar(255) | Not described yet | ||
ExchangeRate |
float | Not described yet | ||
FOB |
nvarchar(13) | Not described yet | ||
BillingAddress |
nvarchar(2000) | Not described yet | ||
BillingLine1 |
nvarchar(500) | Not described yet | ||
BillingLine2 |
nvarchar(500) | Not described yet | ||
BillingLine3 |
nvarchar(500) | Not described yet | ||
BillingLine4 |
nvarchar(500) | Not described yet | ||
BillingLine5 |
nvarchar(41) | Not described yet | ||
BillingCity |
nvarchar(255) | Not described yet | ||
BillingState |
nvarchar(255) | Not described yet | ||
BillingPostalCode |
nvarchar(30) | Not described yet | ||
BillingCountry |
nvarchar(255) | Not described yet | ||
BillingNote |
nvarchar(41) | Not described yet | ||
ShippingAddress |
nvarchar(2000) | Not described yet | ||
ShippingLine1 |
nvarchar(500) | Not described yet | ||
ShippingLine2 |
nvarchar(500) | Not described yet | ||
ShippingLine3 |
nvarchar(500) | Not described yet | ||
ShippingLine4 |
nvarchar(500) | Not described yet | ||
ShippingLine5 |
nvarchar(41) | Not described yet | ||
ShippingCity |
nvarchar(255) | Not described yet | ||
ShippingState |
nvarchar(255) | Not described yet | ||
ShippingPostalCode |
nvarchar(30) | Not described yet | ||
ShippingCountry |
nvarchar(255) | Not described yet | ||
ShippingNote |
nvarchar(41) | Not described yet | ||
Subtotal |
decimal | Not described yet | ||
Tax |
float | Not described yet | ||
TaxItem |
nvarchar(100) | Not described yet | ||
TaxItemId |
nvarchar(255) | FK to Items | Not described yet | |
TaxPercent |
float | Not described yet | ||
IsPending |
bit | Not described yet | ||
IsToBePrinted |
bit | Not described yet | ||
IsTaxIncluded |
bit | Not described yet | ||
IsToBeEmailed |
bit | Not described yet | ||
ItemCount |
int | Not described yet | ||
ItemAggregate |
text(2147483647) | Not described yet | ||
CheckNumber |
nvarchar(25) | Not described yet | ||
PaymentMethod |
nvarchar(100) | How it was paid. | 4 Check 4 | |
PaymentMethodId |
nvarchar(255) | Not described yet | ||
DepositAccount |
nvarchar(255) | Account the money went into. | ||
DepositAccountId |
nvarchar(255) | FK to Accounts | Not described yet | |
CustomerTaxCode |
nvarchar(3) | Not described yet | ||
CustomerTaxCodeId |
nvarchar(255) | Not described yet | ||
CustomFields |
nvarchar(2000) | Not described yet | ||
EditSequence |
nvarchar(16) | Not described yet | ||
TimeModified |
smalldatetime | Not described yet | ||
TimeCreated |
smalldatetime | Not described yet |
Vendors (suppliers and payees).
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
ID |
nvarchar(255) | PK | Unique vendor ID. | |
Name |
nvarchar(100) | Vendor name. | ||
Salutation |
nvarchar(15) | Not described yet | ||
FirstName |
nvarchar(25) | Not described yet | ||
MiddleInitial |
nvarchar(25) | Not described yet | ||
LastName |
nvarchar(25) | Not described yet | ||
Company |
nvarchar(50) | Not described yet | ||
Contact |
nvarchar(1023) | Not described yet | ||
AccountNumber |
nvarchar(99) | Not described yet | ||
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) | Not described yet | ||
CreditLimit |
float | Not described yet | ||
TaxIdentity |
nvarchar(20) | Not described yet | ||
SalesTaxCountry |
nvarchar(100) | Not described yet | ||
AlternateContact |
nvarchar(41) | Not described yet | ||
Phone |
nvarchar(21) | Not described yet | ||
Fax |
nvarchar(21) | Not described yet | ||
AlternatePhone |
nvarchar(21) | Not described yet | ||
Email |
nvarchar(1023) | Not described yet | ||
Cc |
nvarchar(1023) | Not described yet | ||
Notes |
text(2147483647) | Not described yet | ||
Address |
nvarchar(2000) | Not described yet | ||
Line1 |
nvarchar(500) | Not described yet | ||
Line2 |
nvarchar(500) | Not described yet | ||
Line3 |
nvarchar(500) | Not described yet | ||
Line4 |
nvarchar(500) | Not described yet | ||
Line5 |
nvarchar(41) | Not described yet | ||
City |
nvarchar(255) | Not described yet | ||
State |
nvarchar(255) | Not described yet | ||
PostalCode |
nvarchar(30) | Not described yet | ||
Country |
nvarchar(255) | Not described yet | ||
Note |
nvarchar(41) | Not described yet | ||
ShippingAddress |
nvarchar(2000) | Not described yet | ||
ShippingLine1 |
nvarchar(500) | Not described yet | ||
ShippingLine2 |
nvarchar(500) | Not described yet | ||
ShippingLine3 |
nvarchar(500) | Not described yet | ||
ShippingLine4 |
nvarchar(500) | Not described yet | ||
ShippingLine5 |
nvarchar(41) | Not described yet | ||
ShippingCity |
nvarchar(255) | Not described yet | ||
ShippingState |
nvarchar(255) | Not described yet | ||
ShippingPostalCode |
nvarchar(30) | Not described yet | ||
ShippingCountry |
nvarchar(255) | Not described yet | ||
ShippingNote |
nvarchar(41) | Not described yet | ||
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) | Not described yet | ||
EligibleFor1099 |
bit | True if the vendor is tracked for Form 1099. | False 43 True 11 | |
NameOnCheck |
nvarchar(110) | Not described yet | ||
CurrencyName |
nvarchar(64) | Not described yet | ||
CurrencyId |
nvarchar(255) | Not described yet | ||
IsActive |
bit | True while the vendor is active. | ||
CustomFields |
nvarchar(2000) | Not described yet | ||
EditSequence |
nvarchar(16) | Not described yet | ||
TimeModified |
smalldatetime | Not described yet | ||
TimeCreated |
smalldatetime | Not described yet | ||
PrefillAccountId1 |
nvarchar(2000) | Not described yet | ||
PrefillAccountName1 |
nvarchar(2000) | Not described yet | ||
PrefillAccountId2 |
nvarchar(2000) | Not described yet | ||
PrefillAccountName2 |
nvarchar(2000) | Not described yet | ||
PrefillAccountId3 |
nvarchar(2000) | Not described yet | ||
PrefillAccountName3 |
nvarchar(2000) | Not described yet |