BillExpenseItems

Manages QuickBooks Bill Expense Line Items, supporting creation, updates, deletions, and queries for detailed expense tracking.

Table Specific Information

Bills may be inserted, queried, or updated via the Bills, BillExpenseItems, or BillLineItems tables. Bills may be deleted by using the Bills table.

This table has a Custom Fields column. See the Custom Fields page for more information.

Select

By default, SupportEnhancedSQL is set to true, and the following will be honored if present. Other filters will be executed client side. If SupportEnhancedSQL is set to false, only the following filters will be honored.

QuickBooks allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns can typically be used with only the equals or = comparison. The available columns for Bills are Id, Date, ReferenceNumber, VendorName, VendorId, AccountsPayable, AccountsPayableId, IsPaid, and TimeModified. TimeModified and Date may be used with the >, >=, <, <=, or = conditions and may be used twice to specify a range. ReferenceNumber may be used with the = or LIKE conditions to establish a starts-with, ends-with, or contains syntax. For example:

SELECT * FROM Bills WHERE TimeModified > '1/1/2011' AND TimeModified < '2/1/2011' AND ReferenceNumber LIKE '%12345%'

Insert

You can also use Bills and BillExpenseItems to insert a Bill.

To add a Bill, specify a Vendor, Date, and at least one Expense or Line Item. All Expense Line Item columns can be used for inserting multiple expense Line Items for a new Bill transaction. For example, the following will insert a new Bill with two Expense Line Items:

INSERT INTO BillExpenseItems#TEMP (VendorName, Date, ExpenseAccount, ExpenseAmount) VALUES ('Cal Telephone', '1/1/2011', 'Utilities:Telephone', 52.25)
INSERT INTO BillExpenseItems#TEMP (VendorName, Date, ExpenseAccount, ExpenseAmount) VALUES ('Cal Telephone', '1/1/2011', 'Professional Fees:Accounting', 235.87)
INSERT INTO BillExpenseItems (VendorName, Date, ExpenseAccount, ExpenseAmount) SELECT VendorName, Date, ExpenseAccount, ExpenseAmount FROM BillExpenseItems#TEMP

Columns


Name	Type	ReadOnly	References	ColumnSize	Description
ID [KEY]	String	True		255	A unique identifier for the expense line item in the format BillId\|ExpenseLineId, combining the bill and expense line information.
BillId	String	False	Bills.ID	255	The unique identifier for the bill associated with this transaction.
VendorName	String	False		1000	The name of the vendor for this transaction. Either VendorName or VendorId must be provided when inserting.
VendorId	String	False	Vendors.ID	255	The unique identifier for the vendor associated with this transaction. Either VendorName or VendorId must be provided when inserting.
ReferenceNumber	String	False		21	The reference number for the transaction, used for tracking and identification.
Date	Date	False			The date of the transaction. If set in the WHERE clause of a SELECT query, it overrides the pseudo columns StartDate and EndDate.
TxnNumber	Integer	True			A manually assigned transaction number that identifies this transaction separately from the Quickbooks generated ID.
DueDate	Date	False			The date when payment for this bill is due.
Terms	String	False		100	The payment terms associated with this bill, such as Net30 or COD.
TermsId	String	False		255	The unique identifier for the payment terms associated with this bill.
AccountsPayable	String	False		1000	The name of the accounts payable account associated with this bill.
AccountsPayableId	String	False	Accounts.ID	255	The unique identifier for the accounts payable account associated with this bill.
Amount	Decimal	True			The total amount of the transaction, calculated by QuickBooks based on the line items or expense line items.
Memo	String	False		5000	A memo providing additional details about the transaction.
IsPaid	Boolean	True			Indicates whether this bill has been paid. Returns true if paid, otherwise false.
IsTaxIncluded	Boolean	False			Specifies whether tax is included in the transaction amount. This field is available only in international editions of QuickBooks.
ExpenseLineId	String	True		255	The unique identifier for the specific expense line item.
ExpenseLineNumber	String	True		255	The line number associated with this expense within the transaction.
ExpenseAccount	String	False		1000	The name of the account associated with this expense line. Either ExpenseAccount or ExpenseAccountId must be provided when inserting.
ExpenseAccountId	String	False	Accounts.ID	255	The unique identifier for the account associated with this expense line. Either ExpenseAccount or ExpenseAccountId must be provided when inserting.
ExpenseAmount	Decimal	False			The total monetary amount for this specific expense line.
ExpenseBillableStatus	String	False		13	The billing status of the expense line, such as Billable or Non-Billable. 使用できる値は次のとおりです。Empty, Billable, NotBillable, HasBeenBilled
ExpenseCustomer	String	False		1000	The name of the customer associated with this expense line.
ExpenseCustomerId	String	False	Customers.ID	255	The unique identifier for the customer associated with this expense line.
ExpenseClass	String	False		1000	The class name associated with this expense, used for categorization.
ExpenseClassId	String	False	Class.ID	255	The unique identifier for the class associated with this expense.
ExpenseMemo	String	False		5000	A memo providing details about this specific expense line.
ExpenseTaxCode	String	False		3	The tax code associated with this expense line, indicating whether it is taxable or non-taxable.
ExpenseTaxCodeId	String	False	SalesTaxCodes.ID	255	The unique identifier for the tax code associated with this expense line.
ExpenseCustomFields	String	True			Custom fields defined for this specific expense line item.
ExchangeRate	Double	False			The exchange rate applied to convert this transaction's currency to the home currency of the QuickBooks company file.
TimeModified	Datetime	True			The timestamp of the last modification made to the bill.
TimeCreated	Datetime	True			The timestamp of when the bill was created.
OpenAmount	Decimal	True			The current outstanding balance for the vendor associated with this bill.

Pseudo-Columns

Pseudo Column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.