Power BI Connector for QuickBooks

Build 23.0.8839

JournalEntries

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.

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 JournalEntries are Id, Date, TimeCreated, 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 JournalEntries WHERE TimeModified > '1/1/2011' AND TimeModified < '2/1/2011' AND ReferenceNumber LIKE '%12345%'

Insert

To add a JournalEntry, specify at least one Credit and one Debit Line. The LineAggregate column may be used to specify an XML aggregate of JournalEntry Line data. The columns that may be used in these aggregates are defined in the JournalEntryLines table and it starts with Line. For example, the following will insert a new JournalEntry with two Credit Lines and one Debit Line:

INSERT INTO JournalEntries 
         (ReferenceNumber, LineAggregate) 
VALUES ('12345', 
'<JournalEntryLines>
<Row><LineType>Credit</LineType><LineAccount>Retained Earnings</LineAccount><LineAmount>100</LineAmount></Row>
<Row><LineType>Credit</LineType><LineAccount>Note Payable - Bank of Anycity</LineAccount><LineAmount>20</LineAmount></Row>
<Row><LineType>Debit</LineType><LineAccount>Checking</LineAccount><LineAmount>120</LineAmount></Row>
</JournalEntryLines>')

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

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 for the journal entry.

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.

FirstCreditAccount String False

The first credit account associated with the JournalEntry.

FirstCreditAmount Decimal False

The first credit amount associated with the JournalEntry.

FirstCreditMemo String False

The first credit memo associated with the JournalEntry.

FirstCreditEntityName String False

The first credit entity name associated with the JournalEntry.

FirstCreditEntityId String False

The first credit entity id associated with the JournalEntry.

FirstDebitAccount String False

The first debit account associated with the JournalEntry.

FirstDebitAmount Decimal False

The first debit amount associated with the JournalEntry.

FirstDebitMemo String False

The first debit memo associated with the JournalEntry.

FirstDebitEntityName String False

The first debit entity name associated with the JournalEntry.

FirstDebitEntityId String False

The first debit entity id associated with the JournalEntry.

LineAggregate String False 5000

An aggregate of the credit lines and debit ines data which can be used for adding a journal entry and its line item data.

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.

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
Line* String

All line-item-specific columns may be used in insertions.

LineEntityName String

LineEntityName may be used when filtering the data.

LineEntityId String

LineEntityId may be used when filtering the data.

LineAccount String

LineAccount may be used when filtering the data.

LineAccountId String

LineAccountId may be used when filtering the data.

Copyright (c) 2024 CData Software, Inc. - All rights reserved.
Build 23.0.8839