BillLineItems

Handles creation, updates, deletion, and querying of individual line items associated with QuickBooks Bills.

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.

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 Line Item columns can be used for inserting multiple Line Items for a new Bill transaction. For example, the following will insert a new Bill with two Line Items:

INSERT INTO BillLineItems#TEMP (VendorName, Date, ItemName, ItemQuantity) VALUES ('Cal Telephone', '1/1/2011', 'Repairs', 1)
INSERT INTO BillLineItems#TEMP (VendorName, Date, ItemName, ItemQuantity) VALUES ('Cal Telephone', '1/1/2011', 'Removal', 2)
INSERT INTO BillLineItems (VendorName, Date, ItemName, ItemQuantity) SELECT VendorName, Date, ItemName, ItemQuantity FROM BillLineItems#TEMP

Columns


Name	Type	ReadOnly	References	ColumnSize	Description
ID [KEY]	String	True		255	A unique identifier for the line item in the format BillId\|ItemLineId, combining the bill and line item 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.
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.
ItemLineId	String	True		255	The unique identifier for the specific line item.
ItemLineNumber	String	True		255	The line number of this item within the transaction.
ItemName	String	False			The name of the item listed in this transaction.
ItemId	String	False	Items.ID	255	The unique identifier for the item listed in this transaction.
ItemGroup	String	False		100	The name of the item group this line item belongs to, if applicable.
ItemGroupId	String	False	Items.ID	255	The unique identifier for the item group this line item belongs to, if applicable.
ItemDescription	String	False		5000	A detailed description of the item listed in this transaction.
ItemQuantity	Double	False			The quantity of the item or item group specified in this line.
ItemUnitOfMeasure	String	False		31	The unit of measure for the item as defined in the company's settings. Requires QBXML version 7.0 or above.
ItemCost	Double	False			The cost per unit of the item listed in this transaction.
ItemAmount	Decimal	False			The total amount for this item line, calculated as quantity multiplied by cost.
ItemBillableStatus	String	False		13	The billing status of the item, such as Billable or Non-Billable. The allowed values are EMPTY, BILLABLE, NOTBILLABLE, HASBEENBILLED.
ItemCustomer	String	False		1000	The name of the customer associated with this item line.
ItemCustomerId	String	False	Customers.ID	255	The unique identifier for the customer associated with this item line.
ItemInventorySiteId	String	False	InventorySites.ID	31	The unique identifier for the inventory site where this item is stored. Requires QBXML version 10.0 and the Advanced Inventory add-on.
ItemInventorySiteName	String	False		255	The name of the inventory site where this item is stored. Requires QBXML version 10.0 and the Advanced Inventory add-on.
ItemInventorySiteLocationId	String	False		31	The unique identifier for the location within the inventory site for this item. Requires QBXML version 10.0 and the Advanced Inventory add-on.
ItemInventorySiteLocationName	String	False		255	The name of the location within the inventory site for this item. Requires QBXML version 10.0 and the Advanced Inventory add-on.
ItemSerialNumber	String	False		5000	The serial number of the item, if applicable. Requires QBXML version 11.0 and the Advanced Inventory add-on.
ItemLotNumber	String	False		40	The lot number of the item, if applicable. Requires QBXML version 11.0 and the Advanced Inventory add-on.
ItemClass	String	False		1000	The name of the class associated with this item, used for categorization.
ItemClassId	String	False	Class.ID	255	The unique identifier for the class associated with this item.
ItemTaxCode	String	False		3	The tax code indicating whether the item is taxable or non-taxable.
ItemTaxCodeId	String	False	SalesTaxCodes.ID	255	The unique identifier for the tax code associated with this item.
ItemCustomFields	String	False			Custom fields defined for this specific line item.
ItemExpirationDateForSerialLotNumber	String	True		1099	The expiration date for the serial or lot number of this item. Requires QBXML version 16.0 or above.
CurrencyName	String	False		64	The name of the currency used for this bill.
CurrencyId	String	False	Currency.ID	1000	The unique identifier for the currency used for this bill.
ExchangeRate	Decimal	False			The exchange rate used to convert the currency of this bill to the company's home currency.
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.