JournalEntryLines
Create, update, delete, and query QuickBooks Journal Entries. Note that while Journal Entry Lines can be created with a new Journal Entry, they cannot be added or removed from an existing Journal Entry.
Table Specific Information
JournalEntries are unique in that the Credit Line Items and Debit Line Items must add up to the same total in one transaction. It is not possible to change a Journal Line Item one at a time and thus end up with an unbalanced transaction. Note that while Journal Entry Lines can be created with a new Journal Entry, they cannot be added or removed from an existing Journal Entry.
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 only be used with the equals or = comparison. The available columns for JournalEntries are Id, Date, TimeModified, ReferenceNumber, LineEntityName, LineEntityId, LineAccount, and LineAccountId. 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 JournalEntryLines WHERE TimeModified > '1/1/2011' AND TimeModified < '2/1/2011' AND ReferenceNumber LIKE '%12345%'
Insert
To add a JournalEntry, at least one Credit and one Debit Line must be added. Both types of lines are denoted by the Line columns. Debit Lines have a LineType of Debit while Credit Lines have a LineType of Credit. For example, to insert a JournalEntry:
INSERT INTO JournalEntryLines#TEMP (ReferenceNumber, LineType, LineAccount, LineAmount) VALUES ('12345', 'Credit', 'Retained Earnings', '100')
INSERT INTO JournalEntryLines#TEMP (ReferenceNumber, LineType, LineAccount, LineAmount) VALUES ('12345', 'Credit', 'Note Payable - Bank of Anycity', '20')
INSERT INTO JournalEntryLines#TEMP (ReferenceNumber, LineType, LineAccount, LineAmount) VALUES ('12345', 'Debit', 'Checking', '120')
INSERT INTO JournalEntryLines (ReferenceNumber, LineType, LineAccount, LineAmount) SELECT ReferenceNumber, LineType, LineAccount, LineAmount FROM JournalEntryLines#TEMP
To delete a JournalEntry, simply perform a DELETE statement and set the Id equal to the JournalEntryId you wish to delete. For example:
DELETE FROM JournalEntries WHERE Id = '16336-1450191232'
Columns
| Name | Type | ReadOnly | References | ColumnSize | Description |
| ID [KEY] | String | True | 255 |
The unique identifier in the format JournalEntryId|ItemLineId. | |
| JournalEntryID | String | False | 255 |
The journal entry Id. | |
| ReferenceNumber | String | False | 21 |
The transaction reference number. | |
| TxnNumber | Integer | True |
The transaction number. An identifying number for the transaction, different from the QuickBooks-generated Id. | ||
| Date | Date | False |
The transaction date. If it is set in the WHERE clause of a SELECT query, the pseudo columns StartDate and EndDate are overwritten with the value. | ||
| CreditLineCount | Integer | False |
Number of credit lines. | ||
| DebitLineCount | Integer | False |
Number of debit lines. | ||
| LineId | String | True | 255 |
The line item identifier. | |
| LineType | String | False | 100 |
Type of line: credit or debit. | |
| LineAccount | String | False | 1000 |
Account name of a credit or debit line. | |
| LineAccountId | String | False |
Accounts.ID | 255 |
Account Id of a credit or debit line. |
| LineAmount | Decimal | False |
Amount of a credit or debit line. | ||
| LineEntityName | String | False | 1000 |
Entity name of a credit or debit line. | |
| LineEntityId | String | False | 255 |
Entity Id of a credit or debit line. | |
| LineMemo | String | False | 5000 |
Memo for a credit or debit line. | |
| LineClass | String | False | 1000 |
Class name of a credit or debit line. | |
| LineClassId | String | False |
Class.ID | 255 |
Class Id of a credit or debit line. |
| LineStatus | String | False | 13 |
Billing status of a credit or debit line. The allowed values are EMPTY, BILLABLE, NOTBILLABLE, HASBEENBILLED. | |
| LineTaxItem | String | False | 100 |
The sales-tax item used to calculate a single sales tax that is collected at a specified rate and paid to a single agency. Available in only CA, UK, and AU versions. | |
| LineTaxItemId | String | False | 255 |
Id of the sales-tax item used to calculate a single sales tax that is collected at a specified rate and paid to a single agency. Only available in CA, UK, and AU versions. | |
| CurrencyName | String | False | 64 |
The name of the currency for this customer. A minimum QBXML version of 8.0 is required for this feature. | |
| CurrencyId | String | False |
Currency.ID | 255 |
The unique Id of the currency for this customer. A minimum QBXML version of 8.0 is required for this feature. |
| ExchangeRate | Double | False |
The market price for which this currency can be exchanged for the currency used by the QuickBooks company file as the home currency. | ||
| IsHomeCurrencyAdjustment | Boolean | False |
Indicates whether the transaction is using a home currency or foreign currency. Setting this field will override the Currency and ExchangeRate fields to use the home currency. | ||
| EditSequence | String | False | 16 |
An identifier used for versioning for this copy of the object. | |
| TimeModified | Datetime | False |
When the journal entry was last modified. | ||
| TimeCreated | Datetime | False |
When the journal entry was created. |