Excel Add-In for Xero

Build 22.0.8479

Receipts

Query, insert and update receipts for a Xero organisation.

Table Specific Information

The Receipts table allows you to SELECT, INSERT, UPDATE, and DELETE receipt line items for a Xero organization. The Id column is generated by the 本製品; it combines the index of the line item with the unique, Xero-generated ReceiptId.

Note that this table only includes draft receipts - to retrieve receipts that have already been claimed, you will need to read from the ExpenseClaimReceipts view.

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 receipt record, the following fields are required in addition to at least one line item: Date, Contact_Name, and User_UserId. To add a new line item, the following fields are required: LineItem_Description, LineItem_UnitAmount, and LineItem_AccountCode.

In addition to inserting a single row, line item tables offer two additional ways to insert into a table.

  1. You can insert a new object with multiple line items using XML aggregates. The elements supported here are the same as the LineItem columns without the "LineItem_" prefix, except when assigning tracking categories. Please refer to the Xero documentation of the Receipts API for more details.
    INSERT INTO Receipts (Date, Contact_Name, User_UserId, LineItemAggregate) VALUES (
      '4/19/2006 9:16:48 AM',
      'John Doe',
      'c81045b2-5740-4aea-bf8a-3956941af387',
      '<LineItem><Description>Coffee with client</Description><UnitAmount>13.00</UnitAmount><AccountCode>200</AccountCode></LineItem> <LineItem><Description>Bagel</Description><UnitAmount>2.00</UnitAmount><AccountCode>200</AccountCode></LineItem>'
    )
  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 Receipts (ReceiptId, LineItem_Description, LineItem_UnitAmount) VALUES ('XXXXX-XXXXX-XXXXX-XXXXX', 'Coffee with client', 13.80)

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.

Delete

The Xero API has limited support for deleting any object. In order to delete objects, you must update their Status to an appropriate value.

Applicable values for this field may be state-dependent. For example, invoices can only have their status set to DELETED when the invoice status is currently DRAFT or SUBMITTED.

Columns

Name Type ReadOnly Description
Id [KEY] String True

The line item index combined with the unique, Xero-generated identifier for the receipt.

ReceiptId String False

The unique, Xero-generated identifier for the script.

Date Date False

The date of the receipt.

Contact_ContactId String False

The Id of the contact. A contact must be specified when creating a receipt.

Contact_Name String False

The name of the contact.

LineItem_Description String False

The description for the item.

LineItem_UnitAmount Decimal False

The unit amount of the item.

LineItem_AccountCode String False

The code for the associated account.

LineItem_Quantity Double False

The quantity of the item.

LineItem_TaxType String False

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

LineItem_LineAmount Decimal False

The total of the unit amount multiplied by the quantity.

LineItem_TrackingCategory1_CategoryId String False

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

LineItem_TrackingCategory1_CategoryName String False

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

LineItem_TrackingCategory1_OptionId String False

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

LineItem_TrackingCategory1_OptionName String False

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

LineItem_TrackingCategory1_Option_Stat String False

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

LineItem_TrackingCategory2_CategoryId String False

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

LineItem_TrackingCategory2_CategoryName String False

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

LineItem_TrackingCategory2_OptionId String False

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

LineItem_TrackingCategory2_OptionName String False

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

LineItem_TrackingCategory2_Option_Stat String False

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

LineItem_DiscountRate Double False

The percentage discount being applied to the line item.

LineItemAggregate String False

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

User_UserId String False

The user in the organisation that the expense claim receipt is for.

Reference String False

Additional reference number.

LineAmountTypes String False

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

SubTotal Decimal False

The total, excluding taxes, of the receipt.

TotalTax Decimal False

The total tax on the receipt.

Total Decimal False

The total of the receipt -- the sum of SubTotal plus TotalTax.

Status String True

The current status of the receipt. The valid status types are DRAFT, SUBMITTED, AUTHORISED, and DECLINED.

ReceiptNumber String True

The Xero-generated number of the receipt in sequence in the current claim for the given user.

UpdatedDateUTC Datetime True

The date when the receipt was last updated.

HasAttachments Boolean True

This field indicates whether the receipt has attachments.

URL String True

A link to a source document.

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