Power BI Connector for Xero

Build 22.0.8479

ManualJournals

Query, insert and update manual journals for a Xero organisation.

Table Specific Information

The ManualJournals table allows you to SELECT, INSERT, and UPDATE manual journals journal lines for a Xero organization. The Id column is generated by the 本製品; it combines the index of the line item with the unique, Xero-generated ManualJournalId.

SELECT

The Xero API allows extensive filtering and ordering. You can define WHERE and ORDER BY clauses in a query to filter and order the results using any non-line-item column.

Note: The Xero API does not support filtering on line items or journal lines.

INSERT

Inserting a single row is straightforward, even with line item tables. Simply set the required fields and a new table object will be created as a single entry. To insert a new manual journal record, the Narration field is required in addition to at least two line items. A journal record must contain at least one credit and one debit, so that the record as a whole is balanced.

  1. You can insert a new object with multiple line items using an XML aggregate. The elements supported here are the same as the JournalLine columns without the "JournalLine_" prefix, except when assigning tracking categories. Please refer to the Xero documentation of the ManualJournals API for more details.
    INSERT INTO ManualJournals (Narration, JournalLineAggregate) VALUES (
      'Accrued expenses',
      '<JournalLine><LineAmount>55.00</LineAmount><AccountCode>200</AccountCode></JournalLine> <JournalLine><LineAmount>-55.00</LineAmount><AccountCode>201</AccountCode></JournalLine>'
    )
  2. You can also insert a new line item on an existing object by specifying the Xero-generated value of the existing record. For example, to add a line item to an existing invoice:
    INSERT INTO ManualJournals (ManualJournalId, JournalLine_LineAmount, JournalLine_AccountCode) VALUES ('XXXXX-XXXXX-XXXXX-XXXXX', 55.00, '200')

Note: Inserting into existing records will count as two operations against the Xero API. One is required to retrieve the existing record, and another to update the record with new data.

Update

You can update any field that is not read-only.

Note: Update operations will count as two operations against the Xero API. One is required to retrieve the existing record, and another to update the record with new data.

Columns

Name Type ReadOnly Description
Id [KEY] String True

The journal line index combined with the Id of the manual journal.

ManualJournalId String False

The Id of the manual journal.

Narration String False

A description of the journal being posted.

JournalLine_LineAmount Decimal False

The total for the line item. This value is positive for debits and negative for credits.

JournalLine_AccountCode String False

The account code that identifies the account.

JournalLine_Description String False

The description for the manual journal.

JournalLine_TaxType String False

This field can be used to override the default tax code for the selected account.

JournalLine_TrackingCategory1_CategoryId String False

The Id of a tracking category. The API allows two optional tracking categories to be defined.

JournalLine_TrackingCategory1_CategoryName String False

The name of a tracking category. The API allows two optional tracking categories to be defined.

JournalLine_TrackingCategory1_OptionId String False

The Id of a tracking category. The API allows two optional tracking categories to be defined.

JournalLine_TrackingCategory1_OptionName String False

The option of a tracking category. The API allows two optional tracking categories to be defined.

JournalLine_TrackingCategory1_Option_Stat String False

The status of a tracking category. The API allows two optional tracking categories to be defined.

JournalLine_TrackingCategory2_CategoryId String False

The Id of a tracking category. The API allows two optional tracking categories to be defined.

JournalLine_TrackingCategory2_CategoryName String False

The name of a tracking category. The API allows two optional tracking categories to be defined.

JournalLine_TrackingCategory2_OptionId String False

The Id of a tracking category. The API allows two optional tracking categories to be defined.

JournalLine_TrackingCategory2_OptionName String False

The option of a tracking category. The API allows two optional tracking categories to be defined.

JournalLine_TrackingCategory2_Option_Stat String False

The status of a tracking category. The API allows two optional tracking categories to be defined.

JournalLineAggregate String False

Used to define JournalLine rows using XML values. Should be provided on INSERT only.

Date Date False

The date the journal was posted.

Status String False

The status code for the manual journal. The valid values are VALID, POSTED, DELETED, and VOIDED.

URL String False

A link to a source document.

ShowOnCashBasisReports Boolean False

This field indicates whether this journal should be included on cash-basis reports. The default value is true.

LineAmountTypes String False

The line amount type, which specifies whether line amounts include tax (default). When this field is not specified, line amount totals do not include tax. The valid values are Exclusive, Inclusive, and NoTax.

UpdatedDateUTC Datetime True

The date when the manual journal was last updated.

HasAttachments Boolean True

This field indicates whether the invoice has an attachment.

TenantId String False

The ID of the tenant to query instead of the connection tenant

Copyright (c) 2023 CData Software, Inc. - All rights reserved.
Build 22.0.8479