Downloads
Small-business accounting

QuickBooks Desktop style

Accounting data shaped like QuickBooks Desktop: customers, vendors, invoices, bills, payments, accounts and journal entries.

qbdSQL Server 84 tables
InvoicesCustomersBillsAccountsChecks

Good for

AR and AP agingP&L and balance sheetAccounting reconciliations

Questions to try

  • Which customers have open invoices?
  • What do we owe each vendor?
  • How much did we spend by account?

Write each as a query in the SQL editor, or ask an AI assistant to walk you through it.

Connect to 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.

Tables (84)

Click a table to preview it in the Playground.

Data dictionary

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.

Accounts one row per general ledger account 21 columns

The chart of accounts.

ColumnTypeKeyMeaningValues 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
Bills one row per bill 28 columns

Vendor bills (payables), one row per bill.

ColumnTypeKeyMeaningValues 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 one row per check 33 columns

Checks written, one row per check.

ColumnTypeKeyMeaningValues 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 one row per customer or job 88 columns

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

ColumnTypeKeyMeaningValues 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
InvoiceLineItems one row per invoice line 104 columns

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

ColumnTypeKeyMeaningValues 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
Invoices one row per invoice 78 columns

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

ColumnTypeKeyMeaningValues 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 one row per item 68 columns

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

ColumnTypeKeyMeaningValues 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
JournalEntries one row per journal entry 24 columns

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

ColumnTypeKeyMeaningValues 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
JournalEntryLines one row per journal line 27 columns

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

ColumnTypeKeyMeaningValues 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
ReceivePayments one row per payment 21 columns

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

ColumnTypeKeyMeaningValues 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
SalesReceipts one row per sales receipt 66 columns

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

ColumnTypeKeyMeaningValues 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 one row per vendor 61 columns

Vendors (suppliers and payees).

ColumnTypeKeyMeaningValues 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
72 more tables (no notes yet)

← All databases