The tables below lists the fields present in the Query builder when you select the “Fields:” link on the Query Builder screen or “- Columns” on the Excel ribbon bar:
In general columns are named by entity. Specifically:
| Account_* | Accounts |
| Class_* | Classes |
| Customer_* | Customers |
| Employee_* | Employees |
| Job_* | Customer Jobs (children of Customers) |
| SalesRep_* | Sales Reps |
| Vendor_* | Vendors |
Many of the “_*” values are common to all entities in which they appear. Specifically:
| _ID | Current database key (integer). Note that this can change when the database is reloaded. |
| _SelectID | Permanent database key (integer), always assigned to this entity in the current company file. Will not change when the database is reloaded. This is used for selecting entities in filters and can be used for combining reports. |
| _Name | Entity name. For hierarchical entities, this is the child name. |
| _Full_Name | Entity full name, as parent:child. |
| _Number | A number that represents this entity, when assigned. Used for Account, Customer, Vendor, etc. |
| _Description | Description |
| _Sub_Level | Level in hierarchy |
| _Special_Type | A sub-type value |
| _Type | Entity type, varies with entities. (i.e. item_type is different from account_type). |
| _First_Name | Self-explanatory |
| _Last_Name | Self-explanatory |
| _Phone | Self-explanatory |
| _Email | Self-explanatory |
| _Fax | Self-explanatory |
Also, field names ending “_Addr”, “_City”, “_Phone”, “_State”, “_Zip”, “_First_Name”, “_Last_Name”, etc. are self-explanatory and are not further described here..
Numeric value columns are used by transactions:
| Credit | Always the credit amount |
| Debit | Always the debit amount |
| Net | A “Net” value than can be summed in tables. Positive for income and asset accounts, negative for expense and liability accounts. On the GL, this is Credit-Debit for P&L accounts and Debit-Credit for Balance Sheet accounts. |
| Amount | A “Net Positive” amount suitable for use as an account balance. Credit-Debit for income and liability accounts, Debit-Credit for expense and asset accounts. |
| Balance | Same as “Amount”. |
| Income | Income amount for sales-related reports (basically Credit) |
| Expense | Expense amount for purchase-related reports (basically Debit). |
| Cogs | Cost of sales amount |
| Unit_Price | Unit price for items |
| Gross_Profit | Income – Cogs on sales reports where applicable |
| SalesTax | Sales tax where applicable |
| Count | Usually the number of transactions (not items) |
The most important transaction and transaction-date columns are:
| Trans_Date | The transaction date |
| Cal_Year | The calendar year of this date |
| Fiscal_Year | The fiscal year of this date |
| Cal/Fiscal_Mon | The calendar or fiscal month of this date |
| Cal/Fiscal_Yr_Mon | Combined year and month as “yyyy-mm” |
| Cal_Week | Week of calendar year, as “yyyy-Wnn” |
| Trans_Number | QuickBooks transaction number |
| Trans_Sequence | Sequence number for line items |
| Trans_Type | Transaction type description |
| Trans_Table | Database table in which transaction appears (e.g. “Bills”) |
| Trans_ID | Current database key for Trans_Table |
| REF# | Reference number from transaction |
The complete column list in alphabetic order starts on the next page. Columns already described above will have the description “*”.
A note on reporting of SalesReps
A sales rep can be identified on many transactions in QuickBooks Desktop, but not all transactions. If a sales rep is not identified in a transaction but a customer/job is identified, the sales rep assigned to the job will be used for reporting. One example of where this is used is for payroll expenses assigned to a job.
If you use customers and jobs, only the sales-rep assigned to the job will be used.
You can use the SalesRep_IsDefault field to detect whether the transaction (‘0’ value) or job field (‘1’ value) is used and to filter by it using the custom filter feature.
| Column Name | Description |
| Account_Category | General account category. One of: Assets, Expense, Income, Liabilities and Equities |
| Account_Description | Account number + name |
| Account_ID | * |
| Account_Level1 through _Level5 | These fields provide the complete account hierarchy, Level1 is the lowest level and is the same as “Account_Description”. Level2 is the next level up and is the same as “Account_Parent”. |
| Account_Name | * |
| Account_Number | * |
| Account_Order | Order of account on P&L or B.S. Used to insure proper order for Income/Expense & Asset/Liability. |
| Account_SelectID | * |
| Account_Sign | A +1/-1 indicator of the account type used for posting calculations: -1 for income and liabilities/equity, +1 for expense and assets. |
| Account_Type | * |
| Addr1 | * |
| Addr2 | * |
| Addr3 | * |
| Age | Age (days) for aging reports |
| Age_By30 | Age group as 30,60,90, etc. |
| Age_Group | Age group with max=120 |
| Age_Range | Age group name for reports |
| Alt_Contact | Contact name |
| Alt_Phone | Contact phone |
| Amount | * |
| ARAmount | AR Amount for some reports |
| Assembly_ID | Item assembly database key |
| Assembly_Name | Item assembly name |
| Assembly_Type | Item assembly type |
| Asset_Account | Asset account for item |
| Average_Cost | Average cost for item |
| Balance | * |
| Balance_Remaining | For transactions that maintain a remaining balance or for which FinJinni can calculate a remaining balance, this is the value. |
| Bill_Addr1 | * |
| Bill_Addr2 | * |
| Bill_Addr3 | * |
| Bill_City | * |
| Bill_country | * |
| Bill_State | * |
| Bill_Zip | * |
| Cal_Qtr | * |
| Cal_Week | * |
| Cal_Year | * |
| Cal_Yr_Mon | * |
| City | Address field |
| Class_Code | “U” for unassigned class, else “N” |
| Class_Full_Name | * |
| Class_ID | * |
| Class_Name | * |
| Class_SelectID | * |
| Cogs | * |
| Cogs_Account | Cogs account for items |
| Company_ID | Company Identifier (aka “nickname” or “extract name”) |
| Company_Name | Company Name |
| Contact | Contact name |
| Count | * |
| Country | Contact address |
| Created_Date | Date entity or transaction was created |
| Credit | * |
| Credit_Limit | Credit Limit |
| Customer_Full_Name | * |
| Customer_Balance | Current balance maintained by QuickBooks |
| Customer_Company_Name | Company name |
| Customer_Contact | Contact name |
| Customer_Created_Date | Date customer entry was created |
| Customer_ID | * |
| Customer_Name | * |
| Customer_Number | * |
| Customer_SalesRep_Name | Sales-rep assigned to this customer |
| Customer_SalesRep_Initial | * |
| Customer_SalesRep_SelectID | Unique identifier used to select sales-rep |
| Customer_SelectID | * |
| Customer_Total_Balance | Total balance maintained by QuickBooks |
| Customer_Type | * |
| Debit | * |
| DebitOrCredit | In sales & purchases reports, 1 for debit, -1 for credit |
| Department | Employee dept. |
| Description | Transaction description (usually line-item field) |
| Due_Date | Date payment is due |
| * | |
| Employee_Addr1 | * |
| Employee_Addr2 | * |
| Employee_Addr3 | * |
| Employee_City | * |
| Employee_Country | * |
| Employee_First_Name | * |
| Employee_ID | * |
| Employee_Last_Name | * |
| Employee_Middle_Name | * |
| Employee_Name | * |
| Employee_Number | * |
| Employee_SelectID | * |
| Employee_State | * |
| Employee_Type | * |
| Employee_Zip | * |
| Entity_Number | * |
| Entity_Type | * |
| Expected_Date | Date delivery is expected |
| Expense | * |
| Fax | * |
| First_Name | * |
| Fiscal_Month | * |
| Fiscal_Qtr | * |
| Fiscal_Year | * |
| Fiscal_Yr_Mon | * |
| Gross_Profit | * |
| InCashBasis | Whether GL transaction appears in cash reports: “0” never, “1” both accrual and cash, “2” cash only, “3” cash adjustment. |
| Income | * |
| Income_Account | Account for item |
| Invoice_Number | Invoice # on AR reports |
| IsActive | Whether entity is active |
| IsClosed | Whether a sales order is closed |
| IsCogs | Whether a GL transaction line is Cogs |
| IsCredit | Whether a transaction is a credit transaction |
| IsInvoiced | Whether a sales order has been invoiced |
| IsReceived | Whether a PO was received |
| IsRIExpense | Whether this is a reimbursed expense transaction item |
| IsRollOver | Whether this is a roll-over between years (see notes) |
| IsSale | Whether this is a sales transaction (included in sales reports) |
| IsSalesItem | Whether this is a purchased item for resale |
| IsTotal | Whether this transaction line is the total |
| Item_Account | * |
| Item_Assembly_Name | Assembly name to which item belongs |
| Item_Assembly_Type | Assembly type |
| Item_Average_Cost | Average cost reported by QuickBooks |
| Item_Description | * |
| Item_Full_Name | * |
| Item_ID | * |
| Item_InSales | Whether this is an item sold |
| Item_IsActive | * |
| Item_IsTaxable | Whether the item is taxable |
| Item_Name | * |
| Item_Preferred_Vendor_Name | Preferred vendor |
| Item_Preferred_Vendor_Number | Preferred vendor |
| Item_Preferred_Vendor_SelectID | Preferred vendor unique id for selection |
| Item_Purchase_Cost | Purchase cost |
| Item_Purchase_Description | * |
| Item_Quantity_On_Hand | Current quantity reported by QuickBooks |
| Item_Quantity_On_Order | Current quantity reported by QuickBooks |
| Item_Quantity_On_SalesOrder | Current quantity reported by QuickBooks |
| Item_Sales_Description | * |
| Item_Sales_Price | Sales price |
| Item_SelectID | * |
| Item_Special_Type | * |
| Item_Type | * |
| Item_Sub_Level | * |
| Item_Asset_Account_* | Fields for an item that has an asset account defined |
| Item_Cogs_Account_* | Fields for an item that has a cogs account defined |
| Item_Expense_Account_* | Fields for an item that has an expense account defined |
| Item_Income_Account_* | Fields for an item that has an income account defined |
| Job | Job name on reports |
| Job_Full_Name | * |
| Job_ID | * |
| Job_Name | * |
| Job_Number | * |
| Job_SelectID | * |
| Job_Status | Job status |
| Job_Type | * |
| Last_Name | * |
| Link_Trans_QBID | When one transaction is linked to another, such as a payment applied to a transaction, this will be populated with the Trans_QBID field of the transaction to which the payment applies. Note: At present, this field is only set for Bill payments and Received payments. |
| Link_Trans_Type | When one transaction is linked to another, this is the type of the transaction being linked to, e.g. “Invoice” or “Bill”. |
| Location_Full_Name | For QuickBooks Online, this is the department/location full name (parent:child format). |
| Location_Name | For QuickBooks Online, this is the department/location name (parent or child name only). |
| Location_Parent_Name | For QuickBooks Online, this is the department/location parent name when the location is a child element. |
| Location_SelectID | Unique ID used for selection |
| Memo | Transaction memo field |
| Net | * |
| Paid_Through_Date | Payroll date |
| Payment_Method | The payment method name for a transaction |
| Payroll_Item | Payroll item |
| Phone | * |
| PO_Number | PO Number |
| Posting_Sign | * |
| Preferred_Vendor | Preferred Vendor for item |
| Purchase_Cost | Purchase cost for item |
| Quantity | * |
| Quantity_On_Hand | Qty for item |
| Quantity_On_Order | Qty for item |
| Quantity_On_SalesOrder | Qty for item |
| Received_Quantity | Qty for item |
| REF# | * |
| Sales_Description | Description for item |
| Sales_Price | Price for item |
| SalesRep_IsDefault | “0” if sales rep is assigned on transaction, “1” if this is the default rep. for a customer |
| SalesRep_ID | * |
| SalesRep_Initial | Initials |
| SalesRep_Name | * |
| SalesRep_SelectID | * |
| SalesRep_Type | “Employee” or “Vendor” |
| SalesTax | * |
| SaleType | “Sale”, “NoCharge”, “Purchase”, or “Credit” |
| Ship_Addr1 | * |
| Ship_Addr2 | * |
| Ship_Addr3 | * |
| Ship_City | * |
| Ship_Country | * |
| Ship_Date | * |
| Ship_State | * |
| Ship_Zip | * |
| Special_Type | Item type |
| State | * |
| Sub_Level | Entity level in hierarchy |
| Total | * |
| Total_Balance | * |
| Total_Due | * |
| Trans_Date | * |
| Trans_ID | * |
| Trans_Message | Customer or Vendor message for a transaction |
| Trans_Number | * |
| Trans_QBID | This is the QuickBooks unique identifier for a transaction. |
| Trans_Sequence | * |
| Trans_Table | * |
| Trans_SubType | A sub-type to identify each transaction posting on the GL. For example, “Total” would be the transaction total, while “Item” would identify a line item, “Expense” would identify an expense item, and so on. |
| Trans_Type | The transaction-type, e.g. “Bill”, “Check”, “Invoice”. |
| Unbilled_Quantity | From PO |
| Unit_Price | * |
| Vendor_Account | * |
| Vendor_Balance | Current balance maintained by QuickBooks |
| Vendor_Created_Date | Date customer entry was created |
| Vendor_Full_Name | * |
| Vendor_ID | * |
| Vendor_Name | * |
| Vendor_Number | * |
| Vendor_SelectID | * |
| Vendor_Type | * |
| Zip | * |