Bills
Create, Query, Update, and Delete QuickBooks Bills. You can add Bills via the BillLineItems or BillExpenseItems tables.
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
QuickBooks allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns can only be used with the equals or '=' comparison. The available columns for Bills are Id, ReferenceNumber, VendorName, VendorId, AccountsPayable, AccountsPayableId and IsPaid. In addition, the pseudo-columns PaidStatus, StartModifiedDate, EndModifiedDate, StartTxnDate, and EndTxnDate may also be specified.
Insert
Note: There are two ways to insert a Bill. See BillLineItems or BillExpenseItems for another example of how to insert a Bill.
In order to add a Bill, a Vendor, Date, and at least one Expense or Line Item must be specified. The ItemAggregate and ExpenseAggregate columns may be used to specify an XML aggregate of Line or Expense item data. The columns that may be used in these aggregates are defined in the BillLineItems and BillExpenseItems tables as # columns. For example, the following will insert a new Bill with two Line Items:
INSERT INTO Bills (VendorName, Date, ItemAggregate)
VALUES ('Cal Telephone', '1/1/2011',
'<BillLineItems>
<Row><ItemName>Repairs</ItemName><ItemQuantity>1</ItemQuantity></Row>
<Row><ItemName>Removal</ItemName><ItemQuantity>2</ItemQuantity></Row>
</BillLineItems>')
QuickBooks Online
The QuickBooks Online API does not support updates. In addition, Bills may only have Expense Items in Online Edition.
Columns
| Name | Type | Description | ReadOnly |
| ID [KEY] | String |
The unique identifier for the bill. | True |
| VendorName | String |
Vendor for this transaction. Either VenderName or VendorId must have a value when inserting. | False |
| VendorId | String |
Vendor id for this transaction. Either VenderName or VendorId must have a value when inserting. | False |
| ReferenceNumber | String |
Reference number for the transaction. This may be set to refnumber*, *refnumber, and *refnumber* in the WHERE clause of a SELECT statement to search by StartsWith, EndsWith, and Contains. Refnum1:refnum2, refnum1:, and :refnum1 may also be used to denote a range. | False |
| Date | Date |
Date of the transaction. If it is set in the WHERE clause of a SELECT query, the pseudo-columns StartDate and EndDate are overwritten with the value. | False |
| Amount | Double |
Amount of the transaction. This is calculated by QuickBooks based on the Line Items or Expense Line Items. | True |
| TxnNumber | String |
The transaction number. An identifying number for the transaction, but different from the QuickBooks generated ID. | True |
| DueDate | Date |
Date when payment is due. | False |
| Terms | String |
Reference to terms of payment. | False |
| TermsId | String |
Reference id for the terms of payment. | False |
| AccountsPayable | String |
Reference to the Accounts Payable account. | False |
| AccountsPayableId | String |
Reference id for the Accounts Payable account. | False |
| Memo | String |
Memo for the transaction. | False |
| IsPaid | Boolean |
Indicates whether this bill has been paid. | True |
| ExchangeRate | Double |
Exchange Rate is the market price for which this currency can be exchanged for the currency used by the QuickBooks company file as the 'home' currency. | False |
| ItemCount | Integer |
The count of line items. | True |
| ItemAggregate | String |
An aggregate of the Line Item data which can be used for adding a Bill and its Line Item data. | False |
| ExpenseItemCount | Integer |
The count of expense line items. | True |
| ExpenseItemAggregate | String |
An aggregate of the Expense Item data which can be used for adding a Bill and its Expense Item data. | False |
| TransactionCount | Integer |
The count of related transactions to the bill. | True |
| TransactionAggregate | String |
An aggregate of the linked transaction data. | True |
| CustomFields | String |
Custom fields returned from QuickBooks formatted into XML. | False |
| TimeModified | Datetime |
When the Bill was last modified. | True |
| TimeCreated | Datetime |
When the Bill was created. | True |
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.
| Name | Type | Description | |
| Item* | String |
All LineItem specific columns may be used in insertions. | |
| Expense* | String |
All ExpenseItem specific columns may be used in insertions. | |
| StartTxnDate | String |
Earliest transaction date to search for. | |
| EndTxnDate | String |
Latest transaction date to search for. | |
| StartModifiedDate | String |
Earliest modified date to search for. | |
| EndModifiedDate | String |
Latest modified date to search for. | |
| IncludeLinkedTxns | String |
Whether or not to include linked transactions in the response for a SELECT. The allowed values are True, False. | |
| IncludeLineItems | String |
Whether or not to include line items in the response for a SELECT. The allowed values are True, False. | |
| PaidStatus | String |
The paid status of the bill. The allowed values are ALL, PAID, UNPAID, NA. The default value is ALL. | |
| LinkToTxn | String |
A transaction to link the bill to. This transaction must be a Purchase Order. You will get a runtime error if the transaction specified is already closed or fully received. This is only available on Insert and requires a minimum QBXML Version 4.0. |