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 Online: invoices and their line items, bills, payments, deposits, journal entries and items.
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 qbo, in Excel or Power BI (Import mode) or
DBeaver. In queries use the schema-qualified name shown for each table (for example qbo.Accounts).
Download the Excel connection (.odc)
How to use it: download the file and open it. Excel opens it as a connection to qbo
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. 20 of 47 tables are described so far. Read it as Markdown.
Click a table in the diagram above to jump to it.
The chart of accounts. Use Classification and AccountType to group accounts for financial statements.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
Id |
nvarchar(255) | PK | Unique account ID. | |
SyncToken |
nvarchar(2000) | Not described yet | ||
MetaData_CreateTime |
smalldatetime | Not described yet | ||
MetaData_LastUpdatedTime |
smalldatetime | Not described yet | ||
Name |
nvarchar(100) | Account name. | ||
SubAccount |
bit | Not described yet | ||
ParentRef |
nvarchar(255) | Not described yet | ||
ParentRef_Name |
nvarchar(100) | Parent account, for sub-accounts. | ||
FullyQualifiedName |
nvarchar(2000) | Full name including parent accounts, such as Checking:Operating. | ||
Description |
nvarchar(100) | Not described yet | ||
Active |
bit | True while the account can be used in new transactions. | True 89 False 1 | |
Classification |
nvarchar(25) | Accounting class: Asset, Liability, Equity, Revenue or Expense. | Expense 49 Revenue 22 Asset 10 Liability 7 Equity 2 | |
AccountType |
nvarchar(100) | Broad account type, such as Bank, Accounts Receivable, Accounts Payable, Income, Expense or Equity. | ||
AccountSubType |
nvarchar(100) | Finer type under AccountType, such as Checking or Services. | ||
AcctNum |
nvarchar(2000) | Account number from the chart of accounts. | ||
CurrentBalance |
decimal | Current balance of the account. | ||
CurrentBalanceWithSubAccounts |
decimal | Not described yet | ||
CurrencyRef |
nvarchar(255) | Not described yet | ||
CurrencyRef_Name |
nvarchar(2000) | Not described yet | ||
TaxCodeRef |
nvarchar(255) | Not described yet |
One row per bill line: the expense account or item, class and amount. Join to Bills on BillId.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
LineId |
nvarchar(255) | PK | Line number within the bill. | 1 15 2 1 |
BillId |
nvarchar(255) | PK FK to Bills | Bill this line belongs to (Bills.Id). | |
SyncToken |
nvarchar(2000) | Not described yet | ||
MetaData_CreateTime |
smalldatetime | Not described yet | ||
MetaData_LastUpdatedTime |
smalldatetime | Not described yet | ||
DocNumber |
nvarchar(21) | Not described yet | ||
TxnDate |
datetime | Not described yet | ||
PrivateNote |
nvarchar(4000) | Not described yet | ||
LinkedTxnAggregate |
text(2147483647) | Not described yet | ||
Line_Id |
nvarchar(2000) | Not described yet | ||
Line_Amount |
decimal | Amount of this line. | ||
Line_Description |
nvarchar(2000) | Description of the line. | (blank) 10 Building Lease 1 Fountain Pump 1 Lumber 1 Monthly Phone Bill 1 Repairs on the truck 1 Rock Fountain 1 | |
Line_DetailType |
nvarchar(2000) | Kind of line, such as AccountBasedExpenseLineDetail or ItemBasedExpenseLineDetail. | AccountBasedExpenseLineDetail 14 ItemBasedExpenseLineDetail 2 | |
Line_AccountBasedExpenseLineDetail_ClassRef |
nvarchar(255) | Not described yet | ||
Line_AccountBasedExpenseLineDetail_ClassRef_Name |
nvarchar(255) | Not described yet | ||
Line_AccountBasedExpenseLineDetail_CustomerRef |
nvarchar(255) | FK to Customers | Not described yet | |
Line_AccountBasedExpenseLineDetail_CustomerRef_Name |
nvarchar(100) | Customer or job the expense is billable to. | (blank) 15 Travis Waldron 1 | |
Line_AccountBasedExpenseLineDetail_AccountRef |
nvarchar(255) | FK to Accounts | Not described yet | |
Line_AccountBasedExpenseLineDetail_AccountRef_Name |
nvarchar(100) | Expense account charged on the line. | Miscellaneous 3 (blank) 2 Legal & Professional Fees:Accounting 2 Utilities:Gas and Electric 2 Utilities:Telephone 2 Insurance 1 Job Expenses:Job Materials:Decks and Patios 1 Legal & Professional Fees:Lawyer 1 Maintenance and Repair:Equipment Repairs 1 Rent or Lease 1 | |
Line_AccountBasedExpenseLineDetail_TaxCodeRef |
nvarchar(255) | Not described yet | ||
Line_AccountBasedExpenseLineDetail_TaxAmount |
decimal | Not described yet | ||
Line_AccountBasedExpenseLineDetail_TaxInclusiveAmt |
decimal | Not described yet | ||
Line_AccountBasedExpenseLineDetail_BillableStatus |
nvarchar(2000) | Whether the expense can be passed on to the customer, such as Billable, NotBillable or HasBeenBilled. | NotBillable 13 (blank) 2 HasBeenBilled 1 | |
Line_AccountBasedExpenseLineDetail_MarkupInfo_Value |
decimal | Not described yet | ||
Line_AccountBasedExpenseLineDetail_MarkupInfo_Percent |
float | Not described yet | ||
Line_AccountBasedExpenseLineDetail_MarkupInfo_PriceLevelRef |
nvarchar(255) | Not described yet | ||
Line_ItemBasedExpenseLineDetail_ItemRef |
nvarchar(255) | FK to Items | Not described yet | |
Line_ItemBasedExpenseLineDetail_ItemRef_Name |
nvarchar(100) | Not described yet | ||
Line_ItemBasedExpenseLineDetail_ClassRef |
nvarchar(255) | Not described yet | ||
Line_ItemBasedExpenseLineDetail_ClassRef_Name |
nvarchar(255) | Not described yet | ||
Line_ItemBasedExpenseLineDetail_UnitPrice |
decimal | Not described yet | ||
Line_ItemBasedExpenseLineDetail_Qty |
float | Not described yet | ||
Line_ItemBasedExpenseLineDetail_CustomerRef |
nvarchar(255) | Not described yet | ||
Line_ItemBasedExpenseLineDetail_CustomerRef_Name |
nvarchar(100) | Not described yet | ||
Line_ItemBasedExpenseLineDetail_TaxCodeRef |
nvarchar(255) | Not described yet | ||
Line_ItemBasedExpenseLineDetail_BillableStatus |
nvarchar(2000) | Not described yet | ||
Line_ItemBasedExpenseLineDetail_MarkupInfo_Value |
float | Not described yet | ||
Line_ItemBasedExpenseLineDetail_MarkupInfo_Percent |
float | Not described yet | ||
Line_ItemBasedExpenseLineDetail_MarkupInfo_PriceLevelRef |
nvarchar(255) | Not described yet | ||
Line_ItemBasedExpenseLineDetail_MarkupInfo_PriceLevelRef_Name |
nvarchar(2000) | Not described yet | ||
VendorRef |
nvarchar(255) | FK to Vendors | Not described yet | |
VendorRef_Name |
nvarchar(100) | Not described yet | ||
APAccountRef |
nvarchar(255) | FK to Accounts | Not described yet | |
APAccountRef_Name |
nvarchar(100) | Not described yet | ||
TotalAmt |
decimal | Not described yet | ||
SalesTermRef |
nvarchar(255) | FK to Terms | Not described yet | |
SalesTermRef_Name |
nvarchar(31) | Not described yet | ||
DueDate |
datetime | Not described yet | ||
Balance |
decimal | Not described yet | ||
DepartmentRef |
nvarchar(255) | Not described yet | ||
DepartmentRef_Name |
nvarchar(100) | Not described yet | ||
TxnTaxDetail_TxnTaxCodeRef |
nvarchar(255) | Not described yet | ||
TxnTaxDetail_TotalTax |
nvarchar(2000) | Not described yet | ||
TxnTaxDetail_TaxLineAggregate |
text(2147483647) | Not described yet | ||
CurrencyRef |
nvarchar(255) | Not described yet | ||
CurrencyRef_Name |
nvarchar(2000) | Not described yet | ||
ExchangeRate |
decimal | Not described yet | ||
GlobalTaxCalculation |
nvarchar(2000) | Not described yet |
Payments made to vendors, one row per payment (check, cash or card).
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
Id |
nvarchar(255) | PK | Unique vendor payment ID. | 104 1 117 1 118 1 22 1 37 1 50 1 77 1 79 1 82 1 91 1 |
SyncToken |
nvarchar(2000) | Not described yet | ||
MetaData_CreateTime |
smalldatetime | Not described yet | ||
MetaData_LastUpdatedTime |
smalldatetime | Not described yet | ||
DocNumber |
nvarchar(21) | Not described yet | ||
TxnDate |
datetime | Date the payment was made. | 2021-04-09T00:00:00 2 2021-04-10T00:00:00 2 2021-04-11T00:00:00 2 2021-01-07T00:00:00 1 2021-03-21T00:00:00 1 2021-04-04T00:00:00 1 2021-04-08T00:00:00 1 | |
PrivateNote |
nvarchar(4000) | Not described yet | ||
DepartmentRef |
nvarchar(255) | Not described yet | ||
DepartmentRef_Name |
nvarchar(100) | Not described yet | ||
LineAggregate |
text(2147483647) | Not described yet | ||
VendorRef |
nvarchar(255) | FK to Vendors | Not described yet | |
VendorRef_Name |
nvarchar(100) | Vendor paid. | Cal Telephone 2 Books by Bessie 1 Brosnahan Insurance Agency 1 Hall Properties 1 Hicks Hardware 1 Norton Lumber and Building Materials 1 PG&E 1 Robertson & Associates 1 Tim Philip Masonry 1 | |
APAccountRef |
nvarchar(255) | Not described yet | ||
APAccountRef_Name |
nvarchar(100) | Not described yet | ||
PayType |
nvarchar(100) | How the payment was made, such as Check or CreditCard. | Check 7 CreditCard 3 | |
CheckPayment_BankAccountRef |
nvarchar(255) | FK to Accounts | Not described yet | |
CheckPayment_BankAccountRef_Name |
nvarchar(100) | Not described yet | ||
CheckPayment_PrintStatus |
nvarchar(2000) | Not described yet | ||
CreditCardPayment_CCAccountRef |
nvarchar(255) | FK to Accounts | Not described yet | |
CreditCardPayment_CCAccountRef_Name |
nvarchar(100) | Not described yet | ||
TotalAmt |
decimal | Amount paid. | 56.5 1 74.36 1 75.0 1 103.55 1 114.09 1 250.0 1 300.0 1 666.0 1 900.0 1 2000.0 1 | |
ProcessBillPayment |
bit | Not described yet | ||
CurrencyRef |
nvarchar(255) | Not described yet | ||
CurrencyRef_Name |
nvarchar(2000) | Not described yet | ||
ExchangeRate |
decimal | Not described yet |
Bills received from vendors, one row per bill. Lines are in BillLineItems.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
Id |
nvarchar(255) | PK | Unique bill ID. | |
SyncToken |
nvarchar(2000) | Not described yet | ||
MetaData_CreateTime |
smalldatetime | Not described yet | ||
MetaData_LastUpdatedTime |
smalldatetime | Not described yet | ||
DocNumber |
nvarchar(21) | Vendor's bill or reference number. | ||
TxnDate |
datetime | Bill date. | 2021-03-26T00:00:00 3 2021-04-04T00:00:00 2 2021-04-11T00:00:00 2 2020-12-08T00:00:00 1 2021-02-24T00:00:00 1 2021-02-25T00:00:00 1 2021-03-05T00:00:00 1 2021-03-27T00:00:00 1 2021-03-28T00:00:00 1 2021-04-08T00:00:00 1 2021-04-09T00:00:00 1 | |
PrivateNote |
nvarchar(2000) | Not described yet | ||
LinkedTxnAggregate |
text(2147483647) | Not described yet | ||
LineAggregate |
text(2147483647) | Not described yet | ||
VendorRef |
nvarchar(255) | FK to Vendors | Not described yet | |
VendorRef_Name |
nvarchar(100) | Vendor who sent the bill. | Brosnahan Insurance Agency 2 Cal Telephone 2 Norton Lumber and Building Materials 2 PG&E 2 Robertson & Associates 2 Books by Bessie 1 Diego's Road Warrior Bodyshop 1 Hall Properties 1 Hicks Hardware 1 Tim Philip Masonry 1 | |
APAccountRef |
nvarchar(255) | FK to Accounts | Not described yet | |
APAccountRef_Name |
nvarchar(100) | Accounts Payable account that carries the bill. | ||
TotalAmt |
decimal | Bill total including tax. | ||
SalesTermRef |
nvarchar(255) | FK to Terms | Not described yet | |
SalesTermRef_Name |
nvarchar(31) | Payment terms, such as Net 30. | ||
DueDate |
datetime | Date the bill is due. | 2021-04-25T00:00:00 3 2021-03-27T00:00:00 2 2021-04-11T00:00:00 2 2021-01-07T00:00:00 1 2021-02-24T00:00:00 1 2021-03-05T00:00:00 1 2021-04-04T00:00:00 1 2021-04-12T00:00:00 1 2021-04-14T00:00:00 1 2021-05-08T00:00:00 1 2021-05-09T00:00:00 1 | |
Balance |
decimal | Amount still owed on the bill. Zero means paid. | 0.0 10 86.44 1 205.0 1 241.23 1 315.0 1 755.0 1 | |
DepartmentRef |
nvarchar(255) | Not described yet | ||
DepartmentRef_Name |
nvarchar(100) | Not described yet | ||
TxnTaxDetail_TxnTaxCodeRef |
nvarchar(255) | Not described yet | ||
TxnTaxDetail_TotalTax |
nvarchar(2000) | Not described yet | ||
TxnTaxDetail_TaxLineAggregate |
text(2147483647) | Not described yet | ||
CurrencyRef |
nvarchar(255) | Not described yet | ||
CurrencyRef_Name |
nvarchar(2000) | Not described yet | ||
ExchangeRate |
decimal | Not described yet | ||
GlobalTaxCalculation |
nvarchar(2000) | Not described yet |
Classes, used to tag transactions by business unit, location or program.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
Id |
nvarchar(255) | Not described yet | ||
SyncToken |
nvarchar(2000) | Not described yet | ||
MetaData_CreateTime |
smalldatetime | Not described yet | ||
MetaData_LastUpdatedTime |
smalldatetime | Not described yet | ||
Name |
nvarchar(100) | Class name. | ||
SubClass |
bit | Not described yet | ||
ParentRef |
nvarchar(255) | Not described yet | ||
ParentRef_Name |
nvarchar(100) | Not described yet | ||
Active |
bit | True while the class can be used. | ||
FullyQualifiedName |
nvarchar(500) | Full name including the parent class. |
Customers and customer jobs. Jobs are sub-customers, marked IsProject.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
Id |
nvarchar(255) | PK | Unique customer ID inside QuickBooks Online. | |
SyncToken |
nvarchar(2000) | Not described yet | ||
MetaData_CreateTime |
smalldatetime | Not described yet | ||
MetaData_LastUpdatedTime |
smalldatetime | Not described yet | ||
Title |
nvarchar(15) | Not described yet | ||
GivenName |
nvarchar(100) | Not described yet | ||
MiddleName |
nvarchar(100) | Not described yet | ||
FamilyName |
nvarchar(100) | Not described yet | ||
Suffix |
nvarchar(16) | Not described yet | ||
FullyQualifiedName |
nvarchar(2000) | Not described yet | ||
CompanyName |
nvarchar(50) | Company name, when the customer is a business. | ||
DisplayName |
nvarchar(100) | Name shown in lists and on documents. | ||
PrintOnCheckName |
nvarchar(110) | Not described yet | ||
Active |
bit | True while the customer is active. Inactive customers are hidden in pick lists. | ||
IsProject |
bit | True when the record is a job under a parent customer. | ||
PrimaryPhone_FreeFormNumber |
nvarchar(30) | Not described yet | ||
AlternatePhone_FreeFormNumber |
nvarchar(30) | Not described yet | ||
Mobile_FreeFormNumber |
nvarchar(30) | Not described yet | ||
Fax_FreeFormNumber |
nvarchar(30) | Not described yet | ||
PrimaryEmailAddr_Address |
nvarchar(100) | Main email address. | ||
WebAddr_URI |
nvarchar(1000) | Not described yet | ||
DefaultTaxCodeRef |
nvarchar(255) | Not described yet | ||
Taxable |
bit | True when sales to this customer are taxable by default. | False 27 True 2 | |
CustomerTypeRef |
nvarchar(255) | Not described yet | ||
BillAddr_Id |
nvarchar(2000) | Not described yet | ||
BillAddr_Line1 |
nvarchar(500) | Not described yet | ||
BillAddr_Line2 |
nvarchar(500) | Not described yet | ||
BillAddr_Line3 |
nvarchar(500) | Not described yet | ||
BillAddr_Line4 |
nvarchar(500) | Not described yet | ||
BillAddr_Line5 |
nvarchar(500) | Not described yet | ||
BillAddr_City |
nvarchar(255) | Billing city. | Middlefield 8 Menlo Park 5 Bayshore 4 Half Moon Bay 3 Palo Alto 2 Tucson 2 (blank) 1 Maplewood 1 Monlo Park 1 San Jose 1 South Orange 1 | |
BillAddr_Country |
nvarchar(255) | Not described yet | ||
BillAddr_CountrySubDivisionCode |
nvarchar(255) | Billing state or province code. | CA 24 AZ 2 NJ 2 (blank) 1 | |
BillAddr_PostalCode |
nvarchar(31) | Not described yet | ||
BillAddr_Lat |
nvarchar(2000) | Not described yet | ||
BillAddr_Long |
nvarchar(2000) | Not described yet | ||
ShipAddr_Id |
nvarchar(2000) | Not described yet | ||
ShipAddr_Line1 |
nvarchar(500) | Not described yet | ||
ShipAddr_Line2 |
nvarchar(500) | Not described yet | ||
ShipAddr_Line3 |
nvarchar(500) | Not described yet | ||
ShipAddr_Line4 |
nvarchar(500) | Not described yet | ||
ShipAddr_Line5 |
nvarchar(500) | Not described yet | ||
ShipAddr_City |
nvarchar(255) | Not described yet | ||
ShipAddr_Country |
nvarchar(255) | Not described yet | ||
ShipAddr_CountrySubDivisionCode |
nvarchar(255) | Not described yet | ||
ShipAddr_PostalCode |
nvarchar(31) | Not described yet | ||
ShipAddr_Note |
nvarchar(2000) | Not described yet | ||
ShipAddr_Lat |
nvarchar(2000) | Not described yet | ||
ShipAddr_Long |
nvarchar(2000) | Not described yet | ||
Notes |
nvarchar(2000) | Not described yet | ||
Job |
bit | Not described yet | ||
BillWithParent |
bit | Not described yet | ||
ParentRef |
nvarchar(255) | Not described yet | ||
ParentRef_Name |
nvarchar(100) | Parent customer, for jobs. | ||
Level |
int | Not described yet | ||
SalesTermRef |
nvarchar(255) | Not described yet | ||
SalesTermRef_Name |
nvarchar(31) | Default payment terms for this customer. | ||
PaymentMethodRef |
nvarchar(255) | Not described yet | ||
PaymentMethodRef_Name |
nvarchar(31) | Not described yet | ||
Balance |
decimal | Open amount the customer owes. Positive means receivable. | ||
OpenBalanceDate |
datetime | Not described yet | ||
BalanceWithJobs |
decimal | Not described yet | ||
PreferredDeliveryMethod |
nvarchar(2000) | Not described yet | ||
ResaleNum |
nvarchar(16) | Not described yet | ||
CurrencyRef |
nvarchar(255) | Not described yet | ||
CurrencyRef_Name |
nvarchar(2000) | Not described yet |
Departments or locations used to report transactions by unit.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
Id |
nvarchar(255) | Not described yet | ||
SyncToken |
nvarchar(2000) | Not described yet | ||
MetaData_CreateTime |
smalldatetime | Not described yet | ||
MetaData_LastUpdatedTime |
smalldatetime | Not described yet | ||
Name |
nvarchar(100) | Department name. | ||
SubDepartment |
bit | Not described yet | ||
ParentRef |
nvarchar(255) | Not described yet | ||
ParentRef_Name |
nvarchar(100) | Not described yet | ||
FullyQualifiedName |
nvarchar(2000) | Full name including the parent. | ||
Active |
bit | True while the department can be used. |
Bank deposits, one row per deposit slip.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
Id |
nvarchar(255) | PK | Unique deposit ID. | 102 1 121 1 4 1 5 1 62 1 |
SyncToken |
nvarchar(2000) | Not described yet | ||
MetaData_CreateTime |
smalldatetime | Not described yet | ||
MetaData_LastUpdatedTime |
smalldatetime | Not described yet | ||
TxnDate |
date | Deposit date. | 2020-11-26 1 2021-04-07 1 2021-04-09 1 2021-04-10 1 2021-04-11 1 | |
PrivateNote |
nvarchar(4000) | Not described yet | ||
LineAggregate |
text(2147483647) | Not described yet | ||
TxnTaxDetail_TxnTaxCodeRef |
nvarchar(255) | Not described yet | ||
TxnTaxDetail_TotalTax |
nvarchar(2000) | Not described yet | ||
TxnTaxDetail_TaxLineAggregate |
text(2147483647) | Not described yet | ||
DepositToAccountRef |
nvarchar(255) | FK to Accounts | Not described yet | |
DepositToAccountRef_Name |
nvarchar(100) | Bank account the deposit went into. | Checking 4 Savings 1 | |
ClassRef |
nvarchar(255) | Not described yet | ||
ClassRef_Name |
nvarchar(255) | Not described yet | ||
TotalAmt |
decimal | Deposit total. | 218.75 1 408.0 1 600.0 1 868.15 1 5000.0 1 | |
CashBack_AccountRef |
nvarchar(255) | FK to Accounts | Not described yet | |
CashBack_AccountRef_Name |
nvarchar(100) | Not described yet | ||
CashBack_Amount |
decimal | Not described yet | ||
CashBack_Memo |
nvarchar(2000) | Not described yet | ||
DepartmentRef |
nvarchar(255) | Not described yet | ||
DepartmentRef_Name |
nvarchar(100) | Not described yet | ||
ApplyTaxAfterDiscount |
bit | Not described yet | ||
ExchangeRate |
decimal | Not described yet | ||
CurrencyRef |
nvarchar(255) | Not described yet | ||
CurrencyRef_Name |
nvarchar(2000) | Not described yet | ||
GlobalTaxCalculation |
nvarchar(2000) | Not described yet |
Employees on payroll and time tracking.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
Id |
nvarchar(255) | PK | Unique employee ID. | 54 1 55 1 |
SyncToken |
nvarchar(2000) | Not described yet | ||
MetaData_CreateTime |
smalldatetime | Not described yet | ||
MetaData_LastUpdatedTime |
smalldatetime | Not described yet | ||
Organization |
bit | Not described yet | ||
Title |
nvarchar(15) | Not described yet | ||
EmployeeNumber |
nvarchar(100) | Not described yet | ||
GivenName |
nvarchar(25) | Not described yet | ||
MiddleName |
nvarchar(25) | Not described yet | ||
FamilyName |
nvarchar(25) | Not described yet | ||
Suffix |
nvarchar(10) | Not described yet | ||
DisplayName |
nvarchar(100) | Employee name. | Emily Platt 1 John Johnson 1 | |
PrintOnCheckName |
nvarchar(110) | Not described yet | ||
Active |
bit | True while the employee is active. | ||
Mobile_FreeFormNumber |
nvarchar(2000) | Not described yet | ||
PrimaryEmailAddr_Address |
nvarchar(100) | Not described yet | ||
PrimaryAddr_Id |
nvarchar(2000) | Not described yet | ||
PrimaryAddr_Line1 |
nvarchar(500) | Not described yet | ||
PrimaryAddr_Line2 |
nvarchar(500) | Not described yet | ||
PrimaryAddr_Line3 |
nvarchar(500) | Not described yet | ||
PrimaryAddr_Line4 |
nvarchar(500) | Not described yet | ||
PrimaryAddr_Line5 |
nvarchar(500) | Not described yet | ||
PrimaryAddr_City |
nvarchar(255) | Not described yet | ||
PrimaryAddr_Country |
nvarchar(255) | Not described yet | ||
PrimaryAddr_CountrySubDivisionCode |
nvarchar(255) | Not described yet | ||
PrimaryAddr_PostalCode |
nvarchar(31) | Not described yet | ||
PrimaryAddr_Note |
nvarchar(2000) | Not described yet | ||
PrimaryAddr_Lat |
nvarchar(2000) | Not described yet | ||
PrimaryAddr_Long |
nvarchar(2000) | Not described yet | ||
SSN |
nvarchar(100) | Not described yet | ||
BillableTime |
bit | Not described yet | ||
BillRate |
float | Hourly bill rate for billable time. | ||
HiredDate |
datetime | Start date. | (blank) 1 2021-03-11T00:00:00 1 | |
ReleasedDate |
datetime | End date, if the employee has left. | ||
BirthDate |
datetime | Not described yet | ||
Gender |
nvarchar(20) | Not described yet |
One row per invoice line: the item, quantity, rate and amount. Join to Invoices on InvoiceId.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
LineId |
nvarchar(255) | PK | Line number within the invoice. | 1 31 2 30 3 17 4 10 5 3 6 2 |
InvoiceId |
nvarchar(255) | PK FK to Invoices | Invoice this line belongs to (Invoices.Id). | |
SyncToken |
nvarchar(2000) | Not described yet | ||
MetaData_CreateTime |
smalldatetime | Not described yet | ||
MetaData_LastUpdatedTime |
smalldatetime | Not described yet | ||
CustomFieldAggregate |
text(2147483647) | Not described yet | ||
DocNumber |
nvarchar(21) | Not described yet | ||
TxnDate |
datetime | Date of the parent invoice. | ||
PrivateNote |
nvarchar(4000) | Not described yet | ||
TxnStatus |
nvarchar(2000) | Not described yet | ||
LinkedTxnAggregate |
text(2147483647) | Not described yet | ||
Line_Id |
nvarchar(2000) | Not described yet | ||
Line_LineNum |
nvarchar(2000) | Not described yet | ||
Line_Description |
nvarchar(4000) | Description printed on the line. | ||
Line_Amount |
decimal | Amount of this line before tax. | ||
Line_DetailType |
nvarchar(2000) | Kind of line, such as SalesItemLineDetail (a product or service) or DiscountLineDetail. | SalesItemLineDetail 60 SubTotalLineDetail 31 DiscountLineDetail 2 | |
Line_SalesItemLineDetail_ItemRef |
nvarchar(255) | FK to Items | Not described yet | |
Line_SalesItemLineDetail_ItemRef_Name |
nvarchar(100) | Product or service sold on the line (Items). | ||
Line_SalesItemLineDetail_ClassRef |
nvarchar(255) | Not described yet | ||
Line_SalesItemLineDetail_ClassRef_Name |
nvarchar(255) | Not described yet | ||
Line_SalesItemLineDetail_UnitPrice |
decimal | Price per unit on the line. | ||
Line_SalesItemLineDetail_Qty |
float | Quantity sold on the line. | ||
Line_SalesItemLineDetail_TaxCodeRef |
nvarchar(255) | Not described yet | ||
Line_SalesItemLineDetail_DiscountAmt |
decimal | Not described yet | ||
Line_SalesItemLineDetail_ServiceDate |
datetime | Not described yet | ||
Line_GroupLineDetail_GroupItemRef |
nvarchar(255) | Not described yet | ||
Line_GroupLineDetail_GroupItemRef_Name |
nvarchar(255) | Not described yet | ||
Line_GroupLineDetail_Quantity |
int | Not described yet | ||
Line_DiscountLineDetail_ClassRef |
nvarchar(255) | Not described yet | ||
Line_DiscountLineDetail_ClassRef_Name |
nvarchar(255) | Not described yet | ||
Line_DiscountLineDetail_TaxCodeRef |
nvarchar(255) | Not described yet | ||
Line_DiscountLineDetail_TaxCodeRef_Name |
nvarchar(255) | Not described yet | ||
Line_DiscountLineDetail_PercentBased |
bit | Not described yet | ||
Line_DiscountLineDetail_DiscountAccountRef |
nvarchar(255) | FK to Accounts | Not described yet | |
Line_DiscountLineDetail_DiscountAccountRef_Name |
nvarchar(500) | Not described yet | ||
Line_DiscountLineDetail_DiscountPercent |
decimal | Not described yet | ||
TxnTaxDetail_TxnTaxCodeRef |
nvarchar(255) | Not described yet | ||
TxnTaxDetail_TotalTax |
nvarchar(2000) | Not described yet | ||
TxnTaxDetail_TaxLineAggregate |
text(2147483647) | Not described yet | ||
CustomerRef |
nvarchar(255) | FK to Customers | Not described yet | |
CustomerRef_Name |
nvarchar(100) | Not described yet | ||
CustomerMemo |
nvarchar(2000) | Not described yet | ||
BillAddr_Id |
nvarchar(2000) | Not described yet | ||
BillAddr_Line1 |
nvarchar(500) | Not described yet | ||
BillAddr_Line2 |
nvarchar(500) | Not described yet | ||
BillAddr_Line3 |
nvarchar(500) | Not described yet | ||
BillAddr_Line4 |
nvarchar(500) | Not described yet | ||
BillAddr_Line5 |
nvarchar(500) | Not described yet | ||
BillAddr_City |
nvarchar(255) | Not described yet | ||
BillAddr_Country |
nvarchar(255) | Not described yet | ||
BillAddr_CountrySubDivisionCode |
nvarchar(255) | Not described yet | ||
BillAddr_PostalCode |
nvarchar(31) | Not described yet | ||
BillAddr_Note |
nvarchar(2000) | Not described yet | ||
BillAddr_Lat |
nvarchar(2000) | Not described yet | ||
BillAddr_Long |
nvarchar(2000) | Not described yet | ||
ShipAddr_Id |
nvarchar(2000) | Not described yet | ||
ShipAddr_Line1 |
nvarchar(500) | Not described yet | ||
ShipAddr_Line2 |
nvarchar(500) | Not described yet | ||
ShipAddr_Line3 |
nvarchar(500) | Not described yet | ||
ShipAddr_Line4 |
nvarchar(500) | Not described yet | ||
ShipAddr_Line5 |
nvarchar(500) | Not described yet | ||
ShipAddr_City |
nvarchar(255) | Not described yet | ||
ShipAddr_Country |
nvarchar(255) | Not described yet | ||
ShipAddr_CountrySubDivisionCode |
nvarchar(255) | Not described yet | ||
ShipAddr_PostalCode |
nvarchar(31) | Not described yet | ||
ShipAddr_Note |
nvarchar(2000) | Not described yet | ||
ShipAddr_Lat |
nvarchar(2000) | Not described yet | ||
ShipAddr_Long |
nvarchar(2000) | Not described yet | ||
ClassRef |
nvarchar(255) | Not described yet | ||
ClassRef_Name |
nvarchar(255) | Not described yet | ||
SalesTermRef |
nvarchar(255) | FK to Terms | Not described yet | |
SalesTermRef_Name |
nvarchar(31) | Not described yet | ||
DueDate |
datetime | Not described yet | ||
ShipMethodRef |
nvarchar(255) | Not described yet | ||
ShipMethodRef_Name |
nvarchar(2000) | Not described yet | ||
ShipDate |
datetime | Not described yet | ||
TrackingNum |
nvarchar(2000) | Not described yet | ||
TotalAmt |
decimal | Not described yet | ||
PrintStatus |
nvarchar(50) | Not described yet | ||
EmailStatus |
nvarchar(50) | Not described yet | ||
BillEmail_Address |
nvarchar(100) | Not described yet | ||
Balance |
decimal | Not described yet | ||
DepartmentRef |
nvarchar(255) | Not described yet | ||
DepartmentRef_Name |
nvarchar(100) | Not described yet | ||
AllowOnlineCreditCardPayment |
bit | Not described yet | ||
ApplyTaxAfterDiscount |
bit | Not described yet | ||
CurrencyRef |
nvarchar(2000) | Not described yet | ||
CurrencyRef_Name |
nvarchar(2000) | Not described yet | ||
ExchangeRate |
decimal | Not described yet | ||
GlobalTaxCalculation |
nvarchar(2000) | Not described yet | ||
HomeTotalAmt |
decimal | Not described yet | ||
Crew # |
nvarchar(2000) | Not described yet |
Sales invoices billed to customers, one row per invoice. Totals and balances are here; the lines are in InvoiceLineItems.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
Id |
nvarchar(255) | PK | Unique invoice ID inside QuickBooks Online. Other tables point here through InvoiceId. | |
SyncToken |
nvarchar(2000) | Not described yet | ||
MetaData_CreateTime |
smalldatetime | Not described yet | ||
MetaData_LastUpdatedTime |
smalldatetime | Not described yet | ||
CustomFieldAggregate |
text(2147483647) | Not described yet | ||
DocNumber |
nvarchar(21) | Invoice number printed on the document. | ||
TxnDate |
datetime | Date the invoice was issued (transaction date). | ||
PrivateNote |
nvarchar(4000) | Internal memo that the customer does not see. | (blank) 30 Front yard, hedges, and sidewalks 1 | |
TxnStatus |
nvarchar(2000) | QuickBooks transaction status field. It is empty in this copy, so ignore it. | ||
LinkedTxnAggregate |
text(2147483647) | Not described yet | ||
LineAggregate |
text(2147483647) | Not described yet | ||
TxnTaxDetail_TxnTaxCodeRef |
nvarchar(255) | Not described yet | ||
TxnTaxDetail_TotalTax |
nvarchar(2000) | Not described yet | ||
TxnTaxDetail_TaxLineAggregate |
text(2147483647) | Not described yet | ||
CustomerRef |
nvarchar(255) | FK to Customers | ID of the customer billed (Customers.Id). | |
CustomerRef_Name |
nvarchar(100) | Customer name at the time of billing. | ||
CustomerMemo |
nvarchar(2000) | Memo printed on the invoice for the customer. | ||
BillAddr_Id |
nvarchar(2000) | Not described yet | ||
BillAddr_Line1 |
nvarchar(500) | Not described yet | ||
BillAddr_Line2 |
nvarchar(500) | Not described yet | ||
BillAddr_Line3 |
nvarchar(500) | Not described yet | ||
BillAddr_Line4 |
nvarchar(500) | Not described yet | ||
BillAddr_Line5 |
nvarchar(500) | Not described yet | ||
BillAddr_City |
nvarchar(255) | Not described yet | ||
BillAddr_Country |
nvarchar(255) | Not described yet | ||
BillAddr_CountrySubDivisionCode |
nvarchar(255) | Not described yet | ||
BillAddr_PostalCode |
nvarchar(31) | Not described yet | ||
BillAddr_Note |
nvarchar(2000) | Not described yet | ||
BillAddr_Lat |
nvarchar(2000) | Not described yet | ||
BillAddr_Long |
nvarchar(2000) | Not described yet | ||
ShipAddr_Id |
nvarchar(2000) | Not described yet | ||
ShipAddr_Line1 |
nvarchar(500) | Not described yet | ||
ShipAddr_Line2 |
nvarchar(500) | Not described yet | ||
ShipAddr_Line3 |
nvarchar(500) | Not described yet | ||
ShipAddr_Line4 |
nvarchar(500) | Not described yet | ||
ShipAddr_Line5 |
nvarchar(500) | Not described yet | ||
ShipAddr_City |
nvarchar(255) | Not described yet | ||
ShipAddr_Country |
nvarchar(255) | Not described yet | ||
ShipAddr_CountrySubDivisionCode |
nvarchar(255) | Not described yet | ||
ShipAddr_PostalCode |
nvarchar(31) | Not described yet | ||
ShipAddr_Note |
nvarchar(2000) | Not described yet | ||
ShipAddr_Lat |
nvarchar(2000) | Not described yet | ||
ShipAddr_Long |
nvarchar(2000) | Not described yet | ||
ClassRef |
nvarchar(255) | Not described yet | ||
ClassRef_Name |
nvarchar(255) | Not described yet | ||
SalesTermRef |
nvarchar(255) | FK to Terms | Not described yet | |
SalesTermRef_Name |
nvarchar(31) | Payment terms on the invoice, such as Net 30. | ||
DueDate |
datetime | Date payment is due, from the customer terms. | ||
ShipMethodRef |
nvarchar(255) | Not described yet | ||
ShipMethodRef_Name |
nvarchar(2000) | Not described yet | ||
ShipDate |
datetime | Not described yet | ||
TrackingNum |
nvarchar(2000) | Not described yet | ||
TotalAmt |
decimal | Invoice total, including tax, in the invoice currency. | ||
PrintStatus |
nvarchar(50) | Whether the invoice has been printed or sent. | NeedToPrint 23 NotSet 8 | |
EmailStatus |
nvarchar(50) | Whether the invoice was emailed to the customer, and the result. | NotSet 27 NeedToSend 4 | |
BillEmail_Address |
nvarchar(100) | Not described yet | ||
BillEmailCc_Address |
nvarchar(100) | Not described yet | ||
BillEmailBcc_Address |
nvarchar(100) | Not described yet | ||
Balance |
decimal | Amount of the invoice still unpaid. Zero means paid in full. | ||
PaymentRefNum |
nvarchar(100) | Not described yet | ||
PaymentMethodRef |
nvarchar(255) | Not described yet | ||
PaymentMethodRef_Name |
nvarchar(31) | Not described yet | ||
DepositToAccountRef |
nvarchar(255) | Not described yet | ||
DepositToAccountRef_Name |
nvarchar(100) | Not described yet | ||
DeliveryInfo_DeliveryType |
nvarchar(100) | Not described yet | ||
DeliveryInfo_DeliveryTime |
smalldatetime | Not described yet | ||
Deposit |
decimal | Not described yet | ||
DepartmentRef |
nvarchar(255) | Not described yet | ||
DepartmentRef_Name |
nvarchar(100) | Not described yet | ||
AllowOnlineCreditCardPayment |
bit | Not described yet | ||
ApplyTaxAfterDiscount |
bit | Not described yet | ||
CurrencyRef |
nvarchar(255) | Not described yet | ||
CurrencyRef_Name |
nvarchar(2000) | Not described yet | ||
ExchangeRate |
decimal | Not described yet | ||
GlobalTaxCalculation |
nvarchar(2000) | Not described yet | ||
HomeTotalAmt |
decimal | Not described yet | ||
Crew # |
nvarchar(2000) | Not described yet |
Products and services you sell or buy. Inventory items track quantity on hand.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
Id |
nvarchar(255) | PK | Unique item ID. | |
SyncToken |
nvarchar(2000) | Not described yet | ||
MetaData_CreateTime |
smalldatetime | Not described yet | ||
MetaData_LastUpdatedTime |
smalldatetime | Not described yet | ||
Name |
nvarchar(100) | Item name. | ||
FullyQualifiedName |
nvarchar(2000) | Not described yet | ||
Description |
nvarchar(2000) | Not described yet | ||
Active |
bit | True while the item can be used. | ||
ParentRef |
nvarchar(255) | Not described yet | ||
ParentRef_Name |
nvarchar(100) | Not described yet | ||
PrefVendorRef |
nvarchar(255) | Not described yet | ||
PrefVendorRef_Name |
nvarchar(100) | Not described yet | ||
SubItem |
bit | Not described yet | ||
UnitPrice |
decimal | Default sales price. | 0.0 9 35.0 2 2.0 1 4.0 1 10.0 1 15.0 1 50.0 1 75.0 1 275.0 1 | |
Type |
nvarchar(50) | Kind of item: Inventory (tracks stock), Service, or NonInventory. | Service 14 Inventory 4 | |
Taxable |
bit | Not described yet | ||
AssetAccountRef |
nvarchar(255) | FK to Accounts | Not described yet | |
AssetAccountRef_Name |
nvarchar(255) | Not described yet | ||
IncomeAccountRef |
nvarchar(255) | FK to Accounts | Not described yet | |
IncomeAccountRef_Name |
nvarchar(100) | Income account the sale posts to. | Sales of Product Income 4 Fountains and Garden Lighting 3 Landscaping Services 2 Plants and Soil 2 Services 2 Design income 1 Installation 1 Maintenance and Repair 1 Other Income 1 Pest Control Services 1 | |
PurchaseDesc |
nvarchar(2000) | Not described yet | ||
PurchaseCost |
decimal | Default cost from the vendor. | 0.0 13 0.75 1 2.5 1 6.5 1 10.0 1 125.0 1 | |
ExpenseAccountRef |
nvarchar(255) | FK to Accounts | Not described yet | |
ExpenseAccountRef_Name |
nvarchar(100) | Expense account the purchase posts to. | (blank) 13 Cost of Goods Sold 4 Plants and Soil 1 | |
QtyOnHand |
float | Quantity in stock (inventory items). | (blank) 14 25.0 2 2.0 1 31.0 1 | |
ReorderPoint |
float | Not described yet | ||
InvStartDate |
datetime | Not described yet | ||
SalesTaxCodeRef |
nvarchar(255) | Not described yet | ||
PurchaseTaxCodeRef |
nvarchar(255) | Not described yet | ||
TrackQtyOnHand |
bit | Not described yet | ||
Level |
int | Not described yet | ||
Sku |
nvarchar(100) | Not described yet | ||
SalesTaxIncluded |
bit | Not described yet | ||
PurchaseTaxIncluded |
bit | Not described yet | ||
ItemGroupDetail_ItemGroupLineAggregate |
text(2147483647) | Not described yet | ||
ClassRef |
nvarchar(255) | Not described yet | ||
ClassRef_Name |
nvarchar(255) | Not described yet |
Manual journal entries, one row per entry. The debit and credit lines are in JournalEntryLineItems.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
Id |
nvarchar(255) | PK | Unique journal entry ID. | 6 1 7 1 8 1 |
SyncToken |
nvarchar(2000) | Not described yet | ||
MetaData_CreateTime |
smalldatetime | Not described yet | ||
MetaData_LastUpdatedTime |
smalldatetime | Not described yet | ||
DocNumber |
nvarchar(21) | Journal number. | ||
TxnDate |
datetime | Posting date of the entry. | 2021-04-08T00:00:00 2 2021-03-26T00:00:00 1 | |
PrivateNote |
nvarchar(4000) | Not described yet | ||
Adjustment |
bit | True for adjusting entries made at period end. | ||
LineAggregate |
text(2147483647) | Not described yet | ||
TxnTaxDetail_TxnTaxCodeRef |
nvarchar(255) | Not described yet | ||
TxnTaxDetail_TotalTax |
nvarchar(2000) | Not described yet | ||
TxnTaxDetail_TaxLineAggregate |
text(2147483647) | Not described yet | ||
TotalAmt |
decimal | Total debits (equal to total credits). | ||
CurrencyRef |
nvarchar(255) | Not described yet | ||
CurrencyRef_Name |
nvarchar(2000) | Not described yet | ||
ExchangeRate |
decimal | Not described yet |
The debit and credit lines of each journal entry. Each entry balances to zero.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
LineId |
nvarchar(255) | PK | Line number within the entry. | 0 3 1 3 |
JournalEntryId |
nvarchar(255) | PK FK to JournalEntries | Parent journal entry (JournalEntries.Id). | 6 2 7 2 8 2 |
SyncToken |
nvarchar(2000) | Not described yet | ||
MetaData_CreateTime |
smalldatetime | Not described yet | ||
MetaData_LastUpdatedTime |
smalldatetime | Not described yet | ||
DocNumber |
nvarchar(21) | Not described yet | ||
TxnDate |
datetime | Not described yet | ||
PrivateNote |
nvarchar(4000) | Not described yet | ||
Adjustment |
bit | Not described yet | ||
Line_Id |
nvarchar(2000) | Not described yet | ||
Line_Description |
nvarchar(4000) | Line memo. | ||
Line_Amount |
decimal | Amount of the line (always positive; the posting type gives the direction). | 4000.0 2 13495.0 2 25000.0 2 | |
Line_DetailType |
nvarchar(2000) | Not described yet | ||
Line_JournalEntryLineDetail_PostingType |
nvarchar(2000) | Debit or Credit. | Credit 3 Debit 3 | |
Line_JournalEntryLineDetail_Entity_Type |
nvarchar(2000) | Not described yet | ||
Line_JournalEntryLineDetail_Entity_EntityRef |
nvarchar(255) | Not described yet | ||
Line_JournalEntryLineDetail_Entity_EntityRef_Name |
nvarchar(2000) | Customer, vendor or employee linked to the line, if any. | ||
Line_JournalEntryLineDetail_AccountRef |
nvarchar(255) | FK to Accounts | Not described yet | |
Line_JournalEntryLineDetail_AccountRef_Name |
nvarchar(100) | Account posted to. | Opening Balance Equity 3 Loan Payable 1 Notes Payable 1 Truck:Original Cost 1 | |
Line_JournalEntryLineDetail_ClassRef |
nvarchar(255) | Not described yet | ||
Line_JournalEntryLineDetail_ClassRef_Name |
nvarchar(255) | Class tag on the line. | ||
Line_JournalEntryLineDetail_DepartmentRef |
nvarchar(255) | Not described yet | ||
Line_JournalEntryLineDetail_DepartmentRef_Name |
nvarchar(100) | Not described yet | ||
Line_JournalEntryLineDetail_TaxCodeRef |
nvarchar(255) | Not described yet | ||
Line_JournalEntryLineDetail_TaxApplicableOn |
nvarchar(2000) | Not described yet | ||
Line_JournalEntryLineDetail_TaxAmount |
decimal | Not described yet | ||
Line_JournalEntryLineDetail_BillableStatus |
nvarchar(2000) | Not described yet | ||
TxnTaxDetail_TxnTaxCodeRef |
nvarchar(255) | Not described yet | ||
TxnTaxDetail_TotalTax |
nvarchar(2000) | Not described yet | ||
TxnTaxDetail_TaxLineAggregate |
text(2147483647) | Not described yet | ||
TotalAmt |
decimal | Not described yet | ||
CurrencyRef |
nvarchar(2000) | Not described yet | ||
CurrencyRef_Name |
nvarchar(2000) | Not described yet | ||
ExchangeRate |
decimal | Not described yet |
Applies each payment to the invoices it pays off. Join to Payments on PaymentId.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
LineId |
nvarchar(255) | PK | Not described yet | |
PaymentId |
nvarchar(255) | PK FK to Payments | Payment that was applied (Payments.Id). | |
SyncToken |
nvarchar(2000) | Not described yet | ||
MetaData_CreateTime |
smalldatetime | Not described yet | ||
MetaData_LastUpdatedTime |
smalldatetime | Not described yet | ||
TxnDate |
datetime | Not described yet | ||
PrivateNote |
nvarchar(4000) | Not described yet | ||
TxnStatus |
nvarchar(50) | Not described yet | ||
Line_LinkedTxn_TxnId |
nvarchar(255) | ID of the invoice (or other transaction) being paid. | ||
Line_Amount |
decimal | Amount of the payment applied to this transaction. | ||
Line_LinkedTxn_TxnType |
nvarchar(2000) | Type of the transaction paid, such as Invoice. | Invoice 16 CreditMemo 1 | |
Line_LinkedTxn_TxnLineId |
nvarchar(255) | Not described yet | ||
TxnTaxDetail_TxnTaxCodeRef |
nvarchar(255) | Not described yet | ||
TxnTaxDetail_TotalTax |
nvarchar(2000) | Not described yet | ||
TxnTaxDetail_TaxLineAggregate |
text(2147483647) | Not described yet | ||
CustomerRef |
nvarchar(255) | FK to Customers | Not described yet | |
CustomerRef_Name |
nvarchar(100) | Not described yet | ||
RemitToRef |
nvarchar(2000) | Not described yet | ||
RemitToRef_Name |
nvarchar(2000) | Not described yet | ||
ARAccountRef |
nvarchar(255) | Not described yet | ||
ARAccountRef_Name |
nvarchar(100) | Not described yet | ||
DepositToAccountRef |
nvarchar(255) | FK to Accounts | Not described yet | |
DepositToAccountRef_Name |
nvarchar(100) | Not described yet | ||
PaymentMethodRef |
nvarchar(255) | FK to PaymentMethods | Not described yet | |
PaymentMethodRef_Name |
nvarchar(31) | Not described yet | ||
PaymentRefNum |
nvarchar(21) | Not described yet | ||
CreditCardPayment_CreditChargeInfo |
nvarchar(2000) | Not described yet | ||
CreditCardPayment_Number |
nvarchar(2000) | Not described yet | ||
CreditCardPayment_Type |
nvarchar(2000) | Not described yet | ||
CreditCardPayment_NameOnAcct |
nvarchar(2000) | Not described yet | ||
CreditCardPayment_CcExpiryMonth |
int | Not described yet | ||
CreditCardPayment_CcExpiryYear |
int | Not described yet | ||
CreditCardPayment_BillAddrStreet |
nvarchar(2000) | Not described yet | ||
CreditCardPayment_PostalCode |
nvarchar(2000) | Not described yet | ||
CreditCardPayment_CommercialCardCode |
nvarchar(2000) | Not described yet | ||
CreditCardPayment_CCTxnMode |
nvarchar(2000) | Not described yet | ||
CreditCardPayment_CCTxnType |
nvarchar(2000) | Not described yet | ||
CreditCardPayment_PrevCCTransId |
nvarchar(2000) | Not described yet | ||
TotalAmt |
decimal | Not described yet | ||
UnappliedAmt |
decimal | Not described yet | ||
ProcessPayment |
bit | Not described yet | ||
CurrencyRef |
nvarchar(255) | Not described yet | ||
CurrencyRef_Name |
nvarchar(2000) | Not described yet | ||
ExchangeRate |
decimal | Not described yet |
The payment methods a customer can use, such as cash, check or card.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
Id |
nvarchar(255) | PK | Not described yet | |
SyncToken |
nvarchar(2000) | Not described yet | ||
MetaData_CreateTime |
smalldatetime | Not described yet | ||
MetaData_LastUpdatedTime |
smalldatetime | Not described yet | ||
Name |
nvarchar(31) | Method name shown on payments. | American Express 1 Cash 1 Check 1 Diners Club 1 Discover 1 MasterCard 1 Visa 1 | |
Active |
bit | True while the method can be chosen. | ||
Type |
nvarchar(2000) | Category: CASH, CHECK, CREDIT_CARD or NON_CREDIT_CARD. | CREDIT_CARD 5 NON_CREDIT_CARD 2 |
Customer payments received, one row per payment. Lines that apply the money to invoices are in PaymentLineItems.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
Id |
nvarchar(255) | PK | Unique payment ID. | |
SyncToken |
nvarchar(2000) | Not described yet | ||
MetaData_CreateTime |
smalldatetime | Not described yet | ||
MetaData_LastUpdatedTime |
smalldatetime | Not described yet | ||
TxnDate |
datetime | Date the payment was received. | 2021-04-09T00:00:00 4 2021-01-15T00:00:00 2 2021-04-10T00:00:00 2 2021-04-11T00:00:00 2 2021-03-15T00:00:00 1 2021-03-17T00:00:00 1 2021-03-19T00:00:00 1 2021-03-25T00:00:00 1 2021-04-04T00:00:00 1 2021-04-07T00:00:00 1 | |
PrivateNote |
nvarchar(2000) | Not described yet | ||
TxnStatus |
nvarchar(50) | Not described yet | ||
LineAggregate |
text(2147483647) | Not described yet | ||
TxnTaxDetail_TxnTaxCodeRef |
nvarchar(255) | Not described yet | ||
TxnTaxDetail_TotalTax |
nvarchar(2000) | Not described yet | ||
TxnTaxDetail_TaxLineAggregate |
text(2147483647) | Not described yet | ||
CustomerRef |
nvarchar(255) | FK to Customers | Not described yet | |
CustomerRef_Name |
nvarchar(100) | Customer who paid. | Amy's Bird Sanctuary 4 0969 Ocean View Road 2 55 Twin Lane 2 Cool Cars 2 Travis Waldron 2 Bill's Windsurf Shop 1 Dukes Basketball Camp 1 John Melton 1 Sushi by Katsuyuki 1 | |
RemitToRef |
nvarchar(2000) | Not described yet | ||
RemitToRef_Name |
nvarchar(2000) | Not described yet | ||
ARAccountRef |
nvarchar(255) | Not described yet | ||
ARAccountRef_Name |
nvarchar(100) | Not described yet | ||
DepositToAccountRef |
nvarchar(255) | FK to Accounts | Not described yet | |
DepositToAccountRef_Name |
nvarchar(100) | Bank or undeposited-funds account that received the money. | ||
PaymentMethodRef |
nvarchar(255) | FK to PaymentMethods | Not described yet | |
PaymentMethodRef_Name |
nvarchar(31) | How the customer paid, such as Check, Cash or Credit Card. | ||
PaymentRefNum |
nvarchar(21) | Check number or payment reference. | (blank) 10 1053 1 1886 1 2064 1 5421 1 5664 1 6552 1 | |
CreditCardPayment_CreditChargeInfo |
nvarchar(2000) | Not described yet | ||
CreditCardPayment_Number |
nvarchar(2000) | Not described yet | ||
CreditCardPayment_Type |
nvarchar(2000) | Not described yet | ||
CreditCardPayment_NameOnAcct |
nvarchar(2000) | Not described yet | ||
CreditCardPayment_CcExpiryMonth |
int | Not described yet | ||
CreditCardPayment_CcExpiryYear |
int | Not described yet | ||
CreditCardPayment_BillAddrStreet |
nvarchar(2000) | Not described yet | ||
CreditCardPayment_PostalCode |
nvarchar(2000) | Not described yet | ||
CreditCardPayment_CommercialCardCode |
nvarchar(2000) | Not described yet | ||
CreditCardPayment_CCTxnMode |
nvarchar(2000) | Not described yet | ||
CreditCardPayment_CCTxnType |
nvarchar(2000) | Not described yet | ||
CreditCardPayment_PrevCCTransId |
nvarchar(2000) | Not described yet | ||
TotalAmt |
decimal | Amount received. | ||
UnappliedAmt |
decimal | Part of the payment not yet applied to an invoice. Zero means fully applied. | ||
ProcessPayment |
bit | Not described yet | ||
CurrencyRef |
nvarchar(255) | Not described yet | ||
CurrencyRef_Name |
nvarchar(2000) | Not described yet | ||
ExchangeRate |
decimal | Not described yet |
Payment terms, such as Net 30 or 2/10 Net 30.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
Id |
nvarchar(255) | PK | Not described yet | |
SyncToken |
nvarchar(2000) | Not described yet | ||
MetaData_CreateTime |
smalldatetime | Not described yet | ||
MetaData_LastUpdatedTime |
smalldatetime | Not described yet | ||
Name |
nvarchar(31) | Terms name. | Due on receipt 1 Net 10 1 Net 15 1 Net 30 1 Net 60 1 | |
Active |
bit | Not described yet | ||
Type |
nvarchar(20) | STANDARD (due a set number of days after the date) or DATE_DRIVEN (due on a fixed day). Only STANDARD appears in this copy. | ||
DiscountPercent |
float | Early-payment discount percent. | ||
DueDays |
int | Days after the transaction date that payment is due. | 0 1 10 1 15 1 30 1 60 1 | |
DiscountDays |
int | Days within which the discount applies. | 0 4 (blank) 1 | |
DayOfMonthDue |
int | Not described yet | ||
DueNextMonthDays |
int | Not described yet | ||
DiscountDayOfMonth |
int | Not described yet |
Time entries against customers or jobs, for billable time.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
Id |
nvarchar(255) | PK | Not described yet | |
SyncToken |
nvarchar(2000) | Not described yet | ||
MetaData_CreateTime |
smalldatetime | Not described yet | ||
MetaData_LastUpdatedTime |
smalldatetime | Not described yet | ||
TxnDate |
datetime | Date the work was done. | 2021-04-09T00:00:00 3 2021-04-04T00:00:00 1 2021-04-08T00:00:00 1 | |
NameOf |
nvarchar(20) | Whether the time is for a Vendor or an Employee. | ||
EmployeeRef |
nvarchar(255) | FK to Employees | Not described yet | |
EmployeeRef_Name |
nvarchar(255) | Employee who did the work. | Emily Platt 3 John Johnson 2 | |
VendorRef |
nvarchar(255) | Not described yet | ||
VendorRef_Name |
nvarchar(100) | Not described yet | ||
CustomerRef |
nvarchar(255) | FK to Customers | Not described yet | |
CustomerRef_Name |
nvarchar(100) | Customer or job the time is for. | Amy's Bird Sanctuary 2 Rondonuwu Fruit and Vegi 2 Mark Cho 1 | |
DepartmentRef |
nvarchar(255) | Not described yet | ||
DepartmentRef_Name |
nvarchar(100) | Not described yet | ||
ItemRef |
nvarchar(255) | FK to Items | Not described yet | |
ItemRef_Name |
nvarchar(100) | Not described yet | ||
ClassRef |
nvarchar(255) | Not described yet | ||
ClassRef_Name |
nvarchar(255) | Not described yet | ||
PayrollItemRef |
nvarchar(255) | Not described yet | ||
BillableStatus |
nvarchar(50) | Whether the time can be billed: Billable, NotBillable or HasBeenBilled. | HasBeenBilled 2 NotBillable 2 Billable 1 | |
Taxable |
bit | Not described yet | ||
HourlyRate |
decimal | Rate for the time. | 0.0 2 15.0 2 75.0 1 | |
Hours |
int | Whole hours logged. | 4 2 2 1 3 1 5 1 | |
Minutes |
int | Minutes logged, added to Hours. | 0 4 45 1 | |
StartTime |
smalldatetime | Not described yet | ||
EndTime |
smalldatetime | Not described yet | ||
Description |
nvarchar(4000) | Not described yet |
Vendors (suppliers and payees), with the open balance owed to each one.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
Id |
nvarchar(255) | PK | Unique vendor ID. | |
SyncToken |
nvarchar(2000) | Not described yet | ||
MetaData_CreateTime |
smalldatetime | Not described yet | ||
MetaData_LastUpdatedTime |
smalldatetime | Not described yet | ||
Title |
nvarchar(15) | Not described yet | ||
GivenName |
nvarchar(25) | Not described yet | ||
MiddleName |
nvarchar(25) | Not described yet | ||
FamilyName |
nvarchar(25) | Not described yet | ||
Suffix |
nvarchar(10) | Not described yet | ||
DisplayName |
nvarchar(100) | Vendor name shown in lists. | ||
CompanyName |
nvarchar(50) | Not described yet | ||
PrintOnCheckName |
nvarchar(100) | Not described yet | ||
Active |
bit | True while the vendor is active. | ||
PrimaryPhone_FreeFormNumber |
nvarchar(30) | Not described yet | ||
AlternatePhone_FreeFormNumber |
nvarchar(30) | Not described yet | ||
Mobile_FreeFormNumber |
nvarchar(30) | Not described yet | ||
Fax_FreeFormNumber |
nvarchar(30) | Not described yet | ||
PrimaryEmailAddr_Address |
nvarchar(100) | Not described yet | ||
WebAddr_URI |
nvarchar(1000) | Not described yet | ||
BillAddr_Id |
nvarchar(2000) | Not described yet | ||
BillAddr_Line1 |
nvarchar(500) | Not described yet | ||
BillAddr_Line2 |
nvarchar(500) | Not described yet | ||
BillAddr_Line3 |
nvarchar(500) | Not described yet | ||
BillAddr_Line4 |
nvarchar(500) | Not described yet | ||
BillAddr_Line5 |
nvarchar(500) | Not described yet | ||
BillAddr_City |
nvarchar(255) | Not described yet | ||
BillAddr_Country |
nvarchar(255) | Not described yet | ||
BillAddr_CountrySubDivisionCode |
nvarchar(255) | Not described yet | ||
BillAddr_PostalCode |
nvarchar(31) | Not described yet | ||
BillAddr_Lat |
nvarchar(2000) | Not described yet | ||
BillAddr_Long |
nvarchar(2000) | Not described yet | ||
OtherContactInfo_Type |
nvarchar(2000) | Not described yet | ||
OtherContactInfo_Telephone_FreeFormNumber |
nvarchar(30) | Not described yet | ||
TaxIdentifier |
nvarchar(20) | Not described yet | ||
TermRef |
nvarchar(255) | FK to Terms | Not described yet | |
TermRef_Name |
nvarchar(31) | Default payment terms for the vendor. | ||
Balance |
nvarchar(2000) | Open amount owed to the vendor. | 0 21 205.00 1 241.23 1 315.00 1 755.00 1 86.44 1 | |
AcctNum |
nvarchar(100) | Not described yet | ||
Vendor1099 |
bit | True when the vendor is tracked for Form 1099 reporting. | False 25 True 1 | |
CurrencyRef |
nvarchar(255) | Not described yet | ||
CurrencyRef_Name |
nvarchar(2000) | Not described yet |