InvoiceLineItems

Manages individual line items within QuickBooks Invoices, supporting creation, updates, deletion, and queries for billing details.

Table Specific Information

Invoices may be inserted, queried, or updated via the Invoices or InvoiceLineItems tables. Invoices may be deleted by using the Invoices 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 Invoices are Id, Date, TimeModified, ReferenceNumber, CustomerName, CustomerId, IsPaid, Account, and AccountId. 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 InvoiceLineItems WHERE TimeModified > '1/1/2011' AND TimeModified < '2/1/2011' AND ReferenceNumber LIKE '%12345%'

Insert

To add an Invoice, specify a Customer and at least one Line Item. All Line Item columns can be used for inserting multiple Line Items for a new Invoice transaction. For example, the following will insert a new Invoice with two Line Items:

INSERT INTO InvoiceLineItems#TEMP (CustomerName, ItemName, ItemQuantity) VALUES ('Abercrombie, Kristy', 'Repairs', 1)
INSERT INTO InvoiceLineItems#TEMP (CustomerName, ItemName, ItemQuantity) VALUES ('Abercrombie, Kristy', 'Removal', 2)
INSERT INTO InvoiceLineItems (CustomerName, ItemName, ItemQuantity) SELECT CustomerName, ItemName, ItemQuantity FROM InvoiceLineItems#TEMP

Columns


Name	Type	ReadOnly	References	ColumnSize	Description
ID [KEY]	String	True		255	The unique identifier combining the InvoiceId and ItemLineId, used to reference specific line items in an invoice.
InvoiceId	String	False	Invoices.ID		The unique identifier for the invoice to which this line item belongs.
ReferenceNumber	String	False		21	The transaction reference number for identifying this invoice.
TxnNumber	Integer	True			The unique transaction number assigned by QuickBooks, separate from the InvoiceId.
CustomerName	String	False		1000	The name of the customer associated with this invoice. Either CustomerName or CustomerId must be specified when inserting.
CustomerId	String	False	Customers.ID	255	The Quickbooks generated identifier for the customer associated with this invoice. Can be used instead of CustomerName during inserts.
Account	String	False		1000	The name of the accounts-receivable account where funds from this invoice will be deposited.
AccountId	String	False	Accounts.ID	255	The unique identifier for the accounts-receivable account where funds from this invoice will be deposited.
Date	Date	False			The transaction date of this invoice. If specified in a query, it overrides StartDate and EndDate pseudo-columns.
ShipMethod	String	False		1000	The shipping method associated with the invoice, such as FedEx or UPS.
ShipMethodId	String	False	ShippingMethods.ID	255	The unique identifier for the shipping method associated with the invoice.
ShipDate	Date	False			The shipping date related to this invoice.
Memo	String	False		5000	A memo or note providing additional details about this invoice.
Class	String	False		1000	A reference to the class of the transaction for categorization purposes.
ClassId	String	False	Class.ID	255	The unique identifier for the class associated with this transaction.
Amount	Decimal	True			The total monetary value of this invoice.
Message	String	False		101	A custom message for the customer or vendor, to be displayed on the invoice.
MessageId	String	False	CustomerMessages.ID	255	The unique identifier for the message displayed on the invoice.
SalesRep	String	False		5	A reference to the sales representative (identified by initials or name) responsible for this transaction.
SalesRepId	String	False	SalesReps.ID	255	The unique identifier for the sales representative responsible for this transaction.
FOB	String	False		13	Indicates the location from which the goods are shipped (Freight On Board).
BillingAddress	String	True			The complete billing address associated with the invoice.
BillingLine1	String	False		500	The first line of the billing address, typically containing the street address.
BillingLine2	String	False		500	The second line of the billing address, often used for suite or apartment numbers.
BillingLine3	String	False		500	The third line of the billing address, for additional details if necessary.
BillingLine4	String	False		500	The fourth line of the billing address, if required for additional address information.
BillingLine5	String	False		41	The fifth line of the billing address, if applicable.
BillingCity	String	False		255	The city name for the billing address.
BillingState	String	False		255	The state or province name for the billing address.
BillingPostalCode	String	False		30	The postal code or ZIP code for the billing address.
BillingCountry	String	False		255	The country name for the billing address.
BillingNote	String	False		41	A note associated with the billing address.
ShippingAddress	String	True			The complete shipping address associated with the invoice.
ShippingLine1	String	False		500	The first line of the shipping address, typically containing the street address.
ShippingLine2	String	False		500	The second line of the shipping address, often used for suite or apartment numbers.
ShippingLine3	String	False		500	The third line of the shipping address, for additional details if necessary.
ShippingLine4	String	False		500	The fourth line of the shipping address, if required for additional address information.
ShippingLine5	String	False		41	The fifth line of the shipping address, if applicable.
ShippingCity	String	False		255	The city name for the shipping address.
ShippingState	String	False		255	The state or province name for the shipping address.
ShippingPostalCode	String	False		30	The postal code or ZIP code for the shipping address.
ShippingCountry	String	False		255	The country name for the shipping address.
ShippingNote	String	False		41	A note associated with the shipping address.
Subtotal	Decimal	True			The total of the line items before taxes or additional charges are applied.
Tax	Double	True			The total sales tax amount applied to this transaction.
TaxItem	String	False		100	A sales tax item representing a specific tax rate and agency collecting the tax.
TaxItemId	String	False	SalesTaxItems.ID	255	The unique identifier for the sales tax item associated with this invoice.
TaxPercent	Double	True			The percentage of sales tax applied to the invoice.
PONumber	String	False		25	The purchase order number associated with this invoice.
DueDate	Date	False			The date by which payment for this invoice is due.
Terms	String	False		100	The payment terms agreed upon for this invoice.
TermsId	String	False		255	The unique identifier for the payment terms applied to this invoice.
CustomFields	String	False			Custom fields containing additional data returned from QuickBooks, formatted as XML.
ItemLineId	String	True			The unique identifier for the specific line item within the invoice.
ItemLineNumber	String	True		255	The line number of this item in the invoice.
ItemName	String	False		1000	The name of the item included in this invoice.
ItemId	String	False	Items.ID	255	The unique identifier for the item included in this invoice.
ItemGroup	String	False		100	The name of the item group this line item belongs to.
ItemGroupId	String	False	Items.ID	255	The unique identifier for the item group this line item belongs to.
ItemDescription	String	False		5000	A detailed description of the item included in this line.
ItemUnitOfMeasure	String	False		31	The unit of measure for this item, such as pounds, pieces, or liters.
ItemQuantity	Double	False			The quantity of the item included in this invoice line.
ItemRate	Double	False			The unit rate charged for this item.
ItemRatePercent	Double	False			The percentage rate charged for this item.
ItemTaxCode	String	False		3	The tax code applied to this item, specifying whether it is taxable or not.
ItemTaxCodeId	String	False	SalesTaxCodes.ID	255	The unique identifier for the tax code applied to this item.
ItemAmount	Decimal	False			The total cost for this item in the invoice line.
ItemClass	String	False		1000	The class name assigned to this item for categorization.
ItemClassId	String	False	Class.ID	255	The unique identifier for the class assigned to this item.
ItemServiceDate	Date	False			The date the service associated with this item was performed.
ItemInventorySiteId	String	False	InventorySites.ID	31	The unique identifier for the inventory site storing this item.
ItemInventorySiteName	String	False		255	The name of the inventory site storing this item.
ItemInventorySiteLocationId	String	False		31	The unique identifier for the location within the inventory site.
ItemInventorySiteLocationName	String	False		255	The name of the location within the inventory site storing this item.
ItemSerialNumber	String	False		5000	The serial number of this item, if applicable.
ItemLotNumber	String	False		40	The lot number associated with this item, if applicable.
ItemExpirationDateForSerialLotNumber	String	True		1099	The expiration date for the serial lot number of this item.
ItemOther1	String	False		29	An additional field for custom data related to this item.
ItemOther2	String	False		29	Another field for custom data related to this item.
ItemCustomFields	String	False			Additional custom fields specific to this line item.
ItemUOMSetFullName	String	False		1000	The full name of the unit of measure set applied to this item.
ItemUOMSetListID	String	False	UnitOfMeasure.ID	1000	The unique identifier for the unit of measure set applied to this item.
ItemIsGetPrintItemsInGroup	String	False		1000	Indicates if individual items in a group appear on printed forms.
AppliedAmount	Decimal	True			The total amount of applied credits and payments for this invoice.
Balance	Decimal	False			The remaining unpaid amount for this invoice.
CustomerTaxCode	String	False		3	The tax code specifically associated with this customer.
CustomerTaxCodeId	String	False	SalesTaxCodes.ID	255	The unique identifier for the customer's tax code.
IsToBePrinted	Boolean	False			Indicates if this invoice is marked for printing.
IsToBeEmailed	Boolean	False			Indicates if this invoice is marked for emailing.
IsPaid	Boolean	True			Indicates if this invoice has been fully paid.
IsTaxIncluded	Boolean	False			Specifies whether the tax is included in the transaction amount.
IsPending	Boolean	False			Indicates if the invoice is pending or completed.
IsFinanceCharge	String	False		16	Indicates if this invoice includes a finance charge. 使用できる値は次のとおりです。NotSet, IsFinanceCharge, NotFinanceCharge デフォルト値はNotSetです。
Template	String	False		100	The name of the template applied to this invoice for formatting.
TemplateId	String	False	Templates.ID	255	The unique identifier for the template applied to this invoice.
SuggestedDiscountAmount	Decimal	False			The discount amount suggested for this invoice.
SuggestedDiscountDate	Date	False			The suggested date by which a discount applies to this invoice.
ExchangeRate	Double	False			The currency exchange rate applied to this invoice.
BalanceInHomeCurrency	Double	False			The remaining balance converted to the home currency.
CurrencyName	String	False		64	The name of the currency used for this invoice.
CurrencyId	String	False	Currency.ID	1000	The unique identifier for the currency used for this invoice.
Other	String	False		29	Additional data associated with this invoice.
EditSequence	String	True		16	A version control string that tracks changes to this invoice.
TimeModified	Datetime	True			The timestamp indicating when the invoice was last modified.
TimeCreated	Datetime	True			The timestamp indicating when the invoice was created.

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.