Invoices

Handles QuickBooks Invoices, supporting creation, updates, deletion, and queries for billing and receivables tracking.

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 Invoices 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. The ItemAggregate columns may be used to specify an XML aggregate of Line Item data. The columns that may be used in these aggregates are defined in the InvoiceLineItems tables and it starts with Item. For example, the following will insert a new Invoice with two Line Items:

INSERT INTO Invoices (CustomerName, ItemAggregate) 
VALUES ('Abercrombie, Kristy', '<InvoiceLineItems>
<Row><ItemName>Repairs</ItemName><ItemQuantity>1</ItemQuantity></Row>
<Row><ItemName>Removal</ItemName><ItemQuantity>2</ItemQuantity></Row>
</InvoiceLineItems>')

To insert subitems, set the ItemName field to the FullName of the item; for example, '<Row><ItemName>Subs:Carpet</ItemName><ItemQuantity>0</ItemQuantity></Row>'

Columns


Name	Type	ReadOnly	References	ColumnSize	Description
ID [KEY]	String	True		255	A unique identifier for the invoice, used to reference the invoice within QuickBooks.
ReferenceNumber	String	False		21	A transaction reference number assigned to the invoice for tracking and identification.
TxnNumber	Integer	True			A unique number assigned to the transaction by QuickBooks, distinct from the invoice ID.
CustomerName	String	False		1000	The name of the customer associated with the invoice. Either CustomerName or CustomerId must be provided when inserting a new invoice.
CustomerId	String	False	Customers.ID	255	The Quickbooks generated unique identifier for the customer associated with the invoice. Can be used as an alternative to CustomerName during insertion.
Account	String	False		1000	The name of the accounts-receivable account where funds from this invoice will be recorded.
AccountId	String	False	Accounts.ID	255	The unique identifier of the accounts-receivable account where funds from this invoice will be recorded.
Date	Date	False			The date of the invoice transaction. If specified in a query, it overrides the StartDate and EndDate pseudo-columns.
ShipMethod	String	False		1000	The shipping method used for the items in the invoice, such as UPS or FedEx.
ShipMethodId	String	False	ShippingMethods.ID	255	The unique identifier for the shipping method associated with the invoice.
ShipDate	Date	False			The date when the shipment for the invoice is scheduled or completed.
Memo	String	False		5000	A memo providing additional details or notes related to the invoice transaction.
Class	String	False		1000	A reference to the class associated with the invoice, useful for categorization and reporting.
ClassId	String	False	Class.ID	255	The unique identifier for the class associated with the invoice.
Amount	Decimal	True			The total monetary value of the invoice, including all items, taxes, and fees.
Message	String	False		101	A message displayed to the customer or vendor on the invoice.
MessageId	String	False	CustomerMessages.ID	255	The unique identifier for the message displayed to the customer or vendor on the invoice.
SalesRep	String	False		5	A reference to the sales representative responsible for the transaction, identified by initials.
SalesRepId	String	False	SalesReps.ID	255	The unique identifier for the sales representative responsible for the transaction.
FOB	String	False		13	Freight on board: Specifies the location from which the shipment originates or becomes the buyer's responsibility.
BillingAddress	String	True			The full billing address for the customer, as returned by QuickBooks.
BillingLine1	String	False		500	The first line of the billing address.
BillingLine2	String	False		500	The second line of the billing address.
BillingLine3	String	False		500	The third line of the billing address.
BillingLine4	String	False		500	The fourth line of the billing address.
BillingLine5	String	False		41	The fifth line of the billing address.
BillingCity	String	False		255	The city associated with the billing address.
BillingState	String	False		255	The state or province associated with the billing address.
BillingPostalCode	String	False		30	The postal code associated with the billing address.
BillingCountry	String	False		255	The country associated with the billing address.
BillingNote	String	False		41	Additional notes or comments related to the billing address.
ShippingAddress	String	True			The full shipping address for the customer, as returned by QuickBooks.
ShippingLine1	String	False		500	The first line of the shipping address.
ShippingLine2	String	False		500	The second line of the shipping address.
ShippingLine3	String	False		500	The third line of the shipping address.
ShippingLine4	String	False		500	The fourth line of the shipping address.
ShippingLine5	String	False		41	The fifth line of the shipping address.
ShippingCity	String	False		255	The city associated with the shipping address.
ShippingState	String	False		255	The state or province associated with the shipping address.
ShippingPostalCode	String	False		30	The postal code associated with the shipping address.
ShippingCountry	String	False		255	The country associated with the shipping address.
ShippingNote	String	False		41	Additional notes or comments related to the shipping address.
Subtotal	Decimal	True			The gross subtotal for the invoice before applying taxes or payments.
Tax	Double	True			The total sales tax applied to the invoice.
TaxItem	String	False		100	The specific sales tax item applied to the invoice, collected at a specified rate and paid to a single agency.
TaxItemId	String	False	SalesTaxItems.ID	255	The unique identifier for the sales tax item applied to the invoice.
TaxPercent	Double	True			The percentage rate applied as sales tax for the invoice.
POnumber	String	False		25	The purchase order number associated with the invoice.
DueDate	Date	False			The date when payment for the invoice is due.
Terms	String	False		100	The payment terms agreed upon for the invoice, such as Net 30.
TermsId	String	False		255	The unique identifier for the payment terms associated with the invoice.
ItemCount	Integer	True			The total number of items included in the invoice.
ItemAggregate	String	False		5000	A structured summary of all line items included in the invoice.
TransactionCount	Integer	True			The number of transactions linked to the invoice.
TransactionAggregate	String	True		5000	A structured summary of all linked transactions associated with the invoice.
AppliedAmount	Decimal	True			The total amount of credits or payments applied to the invoice.
Balance	Decimal	False			The remaining unpaid balance for the invoice.
CustomerTaxCode	String	False		3	The tax code associated with the customer receiving the invoice.
CustomerTaxCodeId	String	False	SalesTaxCodes.ID	255	The unique identifier for the tax code associated with the customer.
IsToBePrinted	Boolean	False			Indicates whether the invoice is marked for printing.
IsToBeEmailed	Boolean	False			Indicates whether the invoice is marked for emailing.
IsPaid	Boolean	True			Indicates whether the invoice has been paid in full.
IsTaxIncluded	Boolean	False			Indicates whether taxes are included in the line item amounts on the invoice.
IsPending	Boolean	False			Indicates whether the transaction is pending completion or finalization.
IsFinanceCharge	String	False		16	Indicates whether the invoice includes a finance charge. 使用できる値は次のとおりです。NotSet, IsFinanceCharge, NotFinanceCharge デフォルト値はNotSetです。
Template	String	False		100	The template used for printing or displaying the invoice.
TemplateId	String	False	Templates.ID	255	The unique identifier for the template associated with the invoice.
SuggestedDiscountAmount	Decimal	False			A suggested discount amount for early payment of the invoice.
SuggestedDiscountDate	Date	False			A suggested discount deadline for early payment of the invoice.
ExchangeRate	Double	False			The currency exchange rate applied to the invoice.
BalanceInHomeCurrency	Double	False			The remaining balance for the invoice, converted to the company's home currency.
CurrencyName	String	False		64	The name of the currency used for the invoice.
CurrencyId	String	False	Currency.ID	1000	The unique identifier for the currency used for the invoice.
Other	String	False		29	Additional data or information related to the invoice.
CustomFields	String	False			Custom-defined fields for the invoice, returned in XML format.
EditSequence	String	True		16	A unique identifier used for versioning the current copy of the invoice.
TimeModified	Datetime	True			The date and time when the invoice was last modified.
TimeCreated	Datetime	True			The date and time when the invoice was originally 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.