Exclusive: all our customers get a free six-month subscription to EDNA, a next-gen data and AI learning platform

Explore the Xero API data model

See how the various Xero endpoints relate to each other, using our handy Entity Relationship Diagram

Try our interactive ERD Or, browse below

Pulling data from the Xero API is only the first part of your reporting. You also need to understand the relationships between different endpoints - something that APIs typically fail to convey.

At SyncHub, we not only sync your Xero API endpoints down into a reportable format, we also describe their relationships (e.g. in terms of foreign keys) so that you can quickly get a grasp of your data and how it fits together. This page is intended as technical documentation of these relationships.

Endpoints

Account

Documentation: https://developer.xero.com/documentation/api/accounts

Column Type Relates to Notes
RemoteID string - -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
Name string - -
Description string - -
AccountID guid - -
UpdatedDateUTC datetime - -
Status string - -
Code string - -
Type string - -
TaxType string - -
Class string - -
EnablePaymentsToAccount boolean - -
ShowInExpenseClaims boolean - -
CurrencyCode string - -
ReportingCode string - -
ReportingCodeName string - -

BankTransaction

Documentation: https://developer.xero.com/documentation/api/banktransactions

Column Type Relates to Notes
RemoteID string - -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
TimeZone string - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
BankTransactionID guid - -
UpdatedDateUTC datetime - -
Status string - -
BankAccountID guid Account → AccountID -
Type string - -
Reference string - -
IsReconciled boolean - -
CurrencyRate decimal - -
HasAttachments boolean - -
ContactID guid Contact → ContactID -
DateString string - -
Date datetime - -
LineAmountTypes string - -
SubTotals decimal - -
TotalTax decimal - -
Total decimal - -
CurrencyCode string - -

BankTransactionLineItemTrackingCategory

Documentation: https://developer.xero.com/documentation/api/accounting/banktransactions

Column Type Relates to Notes
RemoteID string - -
BankTransactionLineItemRemoteID string BankTransactionLineItem → RemoteID -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
Name string - -
BankTransactionLineItemID guid BankTransactionLineItem → LineItemID -
TrackingCategoryID guid TrackingCategory → TrackingCategoryID -
Option string - -

BankTransfer

Documentation: https://developer.xero.com/documentation/api/bank-transfers

Column Type Relates to Notes
RemoteID string - -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
TimeZone string - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
BankTransferID guid - -
CreatedDateUTC datetime - -
DateString string - -
Date datetime - -
FromBankAccountID guid Account → AccountID -
ToBankAccountID guid Account → AccountID -
Amount decimal - -
FromBankTransactionID guid BankTransaction → BankTransactionID -
ToBankTransactionID guid BankTransaction → BankTransactionID -
CurrencyRate decimal - -
HasAttachments boolean - -

BatchPayment

Documentation: https://developer.xero.com/documentation/api/accounting/batchpayments/#overview

Column Type Relates to Notes
RemoteID string - -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
BatchPaymentID guid - -
UpdatedDateUTC datetime - -
AccountID guid Account → AccountID -
DateString datetime - -
Date datetime - -
Reference string - -
Type string - -
Status string - -
TotalAmount decimal - -
IsReconciled boolean - -

Budget

Documentation: https://developer.xero.com/documentation/api/budgets

Column Type Relates to Notes
RemoteID string - -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
Description string - -
BudgetID guid - -
UpdatedDateUTC datetime - -
Type string - -

Contact

Documentation: https://developer.xero.com/documentation/api/contacts

Column Type Relates to Notes
RemoteID string - -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
Name string - -
ContactID guid - -
UpdatedDateUTC datetime - -
ContactStatus string - -
Email string - -
FullName string - -
AvatarURL string - -
SalesPaymentTermsDay int - -
SalesPaymentTermsType string - -
BillsPaymentTermsDay int - -
BillsPaymentTermsType string - -
BankAccountDetails string - -
EmailAddress string - -
AccountNumber string - -
SkypeUserName string - -
TaxNumber string - -
AccountsReceivableTaxType string - -
AccountsPayableTaxType string - -
IsSupplier boolean - -
IsCustomer boolean - -
DefaultCurrency string - -
FirstName string - -
LastName string - -

ContactGroup

Documentation: https://developer.xero.com/documentation/api/accounting/contactgroups

Column Type Relates to Notes
RemoteID string - -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
Name string - -
ContactGroupID guid - -
Status string - -

CreditNote

Documentation: https://developer.xero.com/documentation/api/credit-notes

Column Type Relates to Notes
RemoteID string - -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
CreditNoteID guid - -
UpdatedDateUTC datetime - -
Status string - -
Date datetime - -
LineAmountTypes string - -
Type string - -
SubTotal decimal - -
TotalTax decimal - -
Total decimal - -
CurrencyCode string - -
CreditNoteNumber string - -
Reference string - -
CurrencyRate decimal - -
RemainingCredit decimal - -
SentToContact boolean - -
HasAttachments boolean - -
FullyPaidOnDate datetime -
  • 2 Jun 2020 NB UTC format according to url
ContactRemoteID guid Contact → ContactID -
LineItemsIncludeTax boolean - -

CreditNoteItem

Column Type Relates to Notes
RemoteID string - -
CreditNoteRemoteID string CreditNote → RemoteID -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
Description string - -
CreditNoteID guid CreditNote → CreditNoteID -
LineItemNumber int - -
ItemCode string - -
UnitAmount decimal - -
TaxType string - -
TaxAmount decimal - -
LineAmount decimal - -
AccountCode string - -
Quantity decimal - -
Tax decimal - -
UnitPriceExTax decimal - -
TotalExTax decimal - -
TotalIncTax decimal - -
TaxRatePercentage decimal - -
ProductRemoteID string - -
TaxRateRemoteID string - -
LineItemsIncludeTax boolean - -

CreditNoteItemTrackingCategory

Documentation: https://developer.xero.com/documentation/api/invoices#TrackingCategory

Column Type Relates to Notes
RemoteID string - -
CreditNoteItemRemoteID string CreditNoteItem → RemoteID -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
Name string - -
TrackingCategoryID guid TrackingCategory → TrackingCategoryID -
Option string - -

Employee

Documentation: https://developer.xero.com/documentation/api/employees

Column Type Relates to Notes
RemoteID string - -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
EmployeeID guid - -
UpdatedDateUTC datetime - -
Status string - -
FirstName string - -
LastName string - -

ContactHistory

Documentation: https://developer.xero.com/documentation/api/history-and-notes

Column Type Relates to Notes
RemoteID string - -
ContactRemoteID string Contact → RemoteID -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
ContactID guid Contact → ContactID -
DateUTC datetime - -
User string - -
Changes string - -
Details string - -

BankTransactionHistory

Documentation: https://developer.xero.com/documentation/api/history-and-notes

Column Type Relates to Notes
RemoteID string - -
BankTransactionRemoteID string BankTransaction → RemoteID -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
BankTransactionID guid BankTransaction → BankTransactionID -
DateUTC datetime - -
User string - -
Changes string - -
Details string - -

BankTransferHistory

Documentation: https://developer.xero.com/documentation/api/history-and-notes

Column Type Relates to Notes
RemoteID string - -
BankTransferRemoteID string BankTransfer → RemoteID -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
BankTransferID guid BankTransfer → BankTransferID -
DateUTC datetime - -
User string - -
Changes string - -
Details string - -

CreditNoteHistory

Documentation: https://developer.xero.com/documentation/api/history-and-notes

Column Type Relates to Notes
RemoteID string - -
CreditNoteRemoteID string CreditNote → RemoteID -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
CreditNoteID guid CreditNote → CreditNoteID -
DateUTC datetime - -
User string - -
Changes string - -
Details string - -

ManualJournalHistory

Documentation: https://developer.xero.com/documentation/api/history-and-notes

Column Type Relates to Notes
RemoteID string - -
ManualJournalRemoteID string ManualJournal → RemoteID -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
ManualJournalID guid ManualJournal → ManualJournalID -
DateUTC datetime - -
User string - -
Changes string - -
Details string - -

InvoiceHistory

Documentation: https://developer.xero.com/documentation/api/history-and-notes

Column Type Relates to Notes
RemoteID string - -
InvoiceRemoteID string Invoice → RemoteID -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
InvoiceID guid Invoice → InvoiceID -
DateUTC datetime - -
User string - -
Changes string - -
Details string - -

ItemHistory

Documentation: https://developer.xero.com/documentation/api/history-and-notes

Column Type Relates to Notes
RemoteID string - -
ItemRemoteID string Item → RemoteID -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
ItemID guid Item → ItemID -
DateUTC datetime - -
User string - -
Changes string - -
Details string - -

PaymentHistory

Documentation: https://developer.xero.com/documentation/api/history-and-notes

Column Type Relates to Notes
RemoteID string - -
PaymentRemoteID string Payment → RemoteID -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
PaymentID guid Payment → PaymentID -
DateUTC datetime - -
User string - -
Changes string - -
Details string - -

PurchaseOrderHistory

Documentation: https://developer.xero.com/documentation/api/history-and-notes

Column Type Relates to Notes
RemoteID string - -
PurchaseOrderRemoteID string PurchaseOrder → RemoteID -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
PurchaseOrderID guid PurchaseOrder → PurchaseOrderID -
DateUTC datetime - -
User string - -
Changes string - -
Details string - -

RepeatingInvoiceHistory

Documentation: https://developer.xero.com/documentation/api/history-and-notes

Column Type Relates to Notes
RemoteID string - -
RepeatingInvoiceRemoteID string RepeatingInvoice → RemoteID -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
RepeatingInvoiceID guid RepeatingInvoice → RepeatingInvoiceID -
DateUTC datetime - -
User string - -
Changes string - -
Details string - -

QuoteHistory

Documentation: https://developer.xero.com/documentation/api/history-and-notes

Column Type Relates to Notes
RemoteID string - -
QuoteRemoteID string Quote → RemoteID -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
QuoteID guid Quote → QuoteID -
DateUTC datetime - -
User string - -
Changes string - -
Details string - -

OverpaymentHistory

Documentation: https://developer.xero.com/documentation/api/history-and-notes

Column Type Relates to Notes
RemoteID string - -
OverpaymentRemoteID string Overpayment → RemoteID -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
OverpaymentID guid Overpayment → OverpaymentID -
DateUTC datetime - -
User string - -
Changes string - -
Details string - -

PrepaymentHistory

Documentation: https://developer.xero.com/documentation/api/history-and-notes

Column Type Relates to Notes
RemoteID string - -
PrepaymentRemoteID string Prepayment → RemoteID -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
PrepaymentID guid Prepayment → PrepaymentID -
DateUTC datetime - -
User string - -
Changes string - -
Details string - -

BatchPaymentHistory

Documentation: https://developer.xero.com/documentation/api/history-and-notes

Column Type Relates to Notes
RemoteID string - -
BatchPaymentRemoteID string BatchPayment → RemoteID -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
BatchPaymentID guid BatchPayment → BatchPaymentID -
DateUTC datetime - -
User string - -
Changes string - -
Details string - -

Invoice

Documentation: https://developer.xero.com/documentation/api/invoices

Column Type Relates to Notes
RemoteID string - -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
TimeZone string - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
InvoiceID guid - -
Status string - -
Date datetime -
  • 10 Aug 2020 Invoice date is NULL (for example) if the invoice has been deleted
DueDate datetime - -
LineAmountTypes string - -
Type string - -
RepeatingInvoiceID guid RepeatingInvoice → RepeatingInvoiceID
  • 29 Jul 2021 Interestingly, this can also be found in the /Invoices endpoint, as well as the /RepeatingInvoices endpoint
TotalDiscount decimal - -
UpdatedDateUTC datetime - -
SubTotal decimal - -
TotalTax decimal - -
Total decimal - -
CurrencyCode string - -
CurrencyRate decimal - -
InvoiceNumber string - -
Reference string - -
Url string - -
SentToContact boolean - -
AmountDue decimal - -
ExpectedPaymentDate datetime -
  • 6 May 2021 Only shown on sales invoices (Accounts Receivable) when this has been set
PlannedPaymentDate datetime -
  • 6 May 2021 Only shown on bills (Accounts Payable) when this has been set
HasAttachments boolean - -
FullyPaidOnDate datetime - -
BrandingThemeID guid - -
ContactID guid Contact → ContactID -
ContactRemoteID string - -
WhenPaid datetime - -
IsAccountsReceivable boolean - -
IsAccountsPayable boolean - -
LineItemsIncludeTax boolean - -
TotalExTax decimal - -
Tax decimal - -
TotalIncTax decimal - -
AmountPaid decimal - -
AmountCredited decimal - -
CustomerRemoteID string - -
WhenDue datetime - -
TaxRatePercentage decimal - -
TaxRateRemoteID string - -
IsSentToClient boolean - -

InvoiceItem

Documentation: https://developer.xero.com/documentation/api/invoices#LineItems

Column Type Relates to Notes
RemoteID string - -
InvoiceRemoteID string Invoice → RemoteID -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
Description string - -
LineItemID guid - -
InvoiceID guid Invoice → InvoiceID -
DiscountRate decimal - -
DiscountPercentage decimal - -
DiscountAmount decimal - -
ItemCode string - -
UnitAmount decimal - -
TaxType string - -
TaxAmount decimal - -
LineAmount decimal - -
AccountCode string - -
Quantity decimal - -
Tax decimal - -
UnitPriceExTax decimal - -
TotalExTax decimal - -
TotalIncTax decimal - -
TaxRatePercentage decimal - -
ProductRemoteID string - -
TaxRateRemoteID string - -
LineItemsIncludeTax boolean - -

InvoiceItemTrackingCategory

Documentation: https://developer.xero.com/documentation/api/invoices#TrackingCategory

Column Type Relates to Notes
RemoteID string - -
InvoiceItemRemoteID string InvoiceItem → RemoteID -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
Name string - -
LineItemID guid InvoiceItem → LineItemID -
TrackingCategoryID guid TrackingCategory → TrackingCategoryID -
Option string - -

Item

Documentation: https://developer.xero.com/documentation/api/items

Column Type Relates to Notes
RemoteID string - -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
Name string - -
Description string - -
ItemID guid - -
UpdatedDateUTC datetime - -
SalePrice decimal - -
SalesAccountCode string - -
SalesTaxType string - -
PurchasePrice decimal - -
PurchaseAccountCode string - -
PurchaseAccountCOGSCode string - -
PurchaseTaxType string - -
Price decimal - -
QuantityOnHand decimal - -
TotalCostPool decimal - -
IsTrackedAsInventory boolean - -
PurchaseDescription string - -
InventoryAssetAccountCode string - -
IsSold boolean - -
IsPurchased boolean - -
Code string - -

Journal

Documentation: https://developer.xero.com/documentation/api/journals

Column Type Relates to Notes
RemoteID string - -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
JournalID guid - -
CreatedDateUTC datetime - -
JournalDate datetime - -
JournalNumber int - -
Reference string - -
SourceID guid - -
SourceType string - -

JournalItem

Documentation: https://developer.xero.com/documentation/api/journals#JournalLines

Column Type Relates to Notes
RemoteID string - -
JournalRemoteID string Journal → RemoteID -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
Description string - -
JournalLineID guid - -
JournalID guid Journal → JournalID -
AccountID guid Account → AccountID -
AccountCode string - -
AccountType string - -
AccountName string - -
NetAmount decimal - -
GrossAmount decimal - -
TaxAmount decimal - -
TaxType string - -
TaxName string - -

ExpenseClaim

Documentation: https://developer.xero.com/documentation/api/expense-claims

Column Type Relates to Notes
RemoteID string - -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
LinkedTransactionID guid - -
UpdatedDateUTC datetime - -
ExpenseClaimID guid - -
SourceTransactionID guid - -
SourceLineItemID guid - -
ContactID guid Contact → ContactID -
TargetTransactionID guid -
  • 2 Jun 2020 Presumably only assigned once the transaction is invoiced
TargetLineItemID guid -
  • 2 Jun 2020 Presumably only assigned once the transaction is invoiced
Status string - -
Type string - -
SourceTransactionTypeCode string - -

ManualJournal

Documentation: https://developer.xero.com/documentation/api/manual-journals

Column Type Relates to Notes
RemoteID string - -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
ManualJournalID guid - -
UpdatedDateUTC datetime - -
Status string - -
Date datetime - -
LineAmountTypes string - -
Narration string - -
ShowOnCashBasisReports boolean - -
HasAttachements boolean - -

Organisation

Documentation: https://developer.xero.com/documentation/api/organisation

Column Type Relates to Notes
RemoteID string - -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
Name string - -
OrganisationID guid - -
CreatedDateUTC datetime - -
OrganisationStatus string - -
Timezone string - -
IsDemoCompany boolean - -
ShortCode string - -
CountryCode string - -
LegalName string - -
PaysTax boolean - -
Version string - -
OrganisationType string - -
BaseCurrency string - -
TaxNumber string - -
FinancialYearEndDay int - -
FinancialYearEndMonth int - -
SalesTaxBasis string - -
SalesTaxPeriod string - -
DefaultSalesTax string - -
DefaultPurchasesTax string - -
OrganisationEntityType string - -
Edition string - -
Class string - -
LineOfBusiness string - -

Overpayment

Documentation: https://developer.xero.com/documentation/api/overpayments

Column Type Relates to Notes
RemoteID string - -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
TimeZone string - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
OverpaymentID guid - -
UpdatedDateUTC datetime - -
Status string - -
Type string - -
RemainingCredit decimal - -
HasAttachments boolean - -
ContactID guid Contact → ContactID -
DateString string - -
Date datetime - -
LineAmountTypes string - -
SubTotal decimal - -
TotalTax decimal - -
Total decimal - -
CurrencyCode string - -
CurrencyRate decimal - -

OverpaymentItem

Documentation: https://developer.xero.com/documentation/api/overpayments#LineItems

Column Type Relates to Notes
RemoteID string - -
OverpaymentRemoteID string Overpayment → RemoteID -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
Description string - -
OverpaymentID guid Overpayment → OverpaymentID -
LineNumber int - -
ItemCode string - -
UnitAmount decimal - -
TaxType string - -
TaxAmount decimal - -
LineAmount decimal - -
AccountCode string - -
Quantity decimal - -
Tax decimal - -
UnitPriceExTax decimal - -
TotalExTax decimal - -
TotalIncTax decimal - -
TaxRatePercentage decimal - -
ProductRemoteID string - -
TaxRateRemoteID string - -
LineItemsIncludeTax boolean - -

Payment

Documentation: https://developer.xero.com/documentation/api/payments

Column Type Relates to Notes
RemoteID string - -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
TimeZone string - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
PaymentID guid - -
UpdatedDateUTC datetime - -
Status string - -
Number int - -
Tip decimal - -
InvoiceRemoteID guid Invoice → InvoiceID -
CreditNoteRemoteID guid CreditNote → CreditNoteID -
AccountRemoteID guid Account → AccountID -
Amount decimal - -
CurrencyRate decimal - -
IsReconciled boolean - -
Date datetime - -
Reference string - -
PaymentType string - -
BatchPaymentID guid -
  • 2 Jun 2020 Foreign Key - References `BatchPayment` (not implemented). Only returned when getting individual payments.

Prepayment

Documentation: https://developer.xero.com/documentation/api/accounting/prepayments

Column Type Relates to Notes
RemoteID string - -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
PrepaymentID guid - -
UpdatedDateUTC datetime - -
Status string - -
Type string - -
Reference string - -
RemainingCredit decimal - -
HasAttachments boolean - -
ContactID guid Contact → ContactID -
DateString string - -
Date datetime - -
LineAmountTypes string - -
SubTotal decimal - -
TotalTax decimal - -
Total decimal - -
CurrencyCode string - -

PrepaymentItem

Documentation: https://developer.xero.com/documentation/api/prepayments#LineItems

Column Type Relates to Notes
RemoteID string - -
PrepaymentRemoteID string Prepayment → RemoteID -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
Description string - -
PrepaymentID guid Prepayment → PrepaymentID -
LineItemID guid - -
ItemCode string - -
UnitAmount decimal - -
TaxType string - -
TaxAmount decimal - -
LineAmount decimal - -
AccountCode string - -
Quantity decimal - -
Tax decimal - -
UnitPriceExTax decimal - -
TotalExTax decimal - -
TotalIncTax decimal - -
TaxRatePercentage decimal - -
ProductRemoteID string - -
TaxRateRemoteID string - -
LineItemsIncludeTax boolean - -

PrepaymentItemTrackingCategory

Documentation: https://developer.xero.com/documentation/api/accounting/prepayments#TrackingCategory

Column Type Relates to Notes
RemoteID string - -
PrepaymentItemRemoteID string PrepaymentItem → RemoteID -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
TrackingCategoryID guid TrackingCategory → TrackingCategoryID -
Option string - -

PurchaseOrder

Documentation: https://developer.xero.com/documentation/api/purchase-orders

Column Type Relates to Notes
RemoteID string - -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
PurchaseOrderID guid - -
UpdatedDateUTC datetime - -
Status string - -
PurchaseOrderNumber string - -
DateString string - -
Date datetime - -
DeliveryDateString string - -
DeliveryDate datetime - -
DeliveryAddress string - -
AttentionTo string - -
Telephone string - -
DeliveryInstructions string - -
IsDiscounted boolean - -
Reference string - -
Type string - -
CurrencyCode string - -
CurrencyRate decimal - -
ContactID guid Contact → ContactID -
BrandingThemeID guid - -
LineAmountTypes string - -
SubTotal decimal - -
TotalTax decimal - -
Total decimal - -
HasErrors boolean - -
SentToContact boolean - -

PurchaseOrderItemTrackingCategory

Documentation: https://developer.xero.com/documentation/api/purchaseorders#TrackingCategory

Column Type Relates to Notes
RemoteID string - -
PurchaseOrderLineItemRemoteID string PurchaseOrderLineItem → RemoteID -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
Name string - -
PurchaseOrderLineItemID guid PurchaseOrderLineItem → LineItemID -
TrackingCategoryID guid TrackingCategory → TrackingCategoryID -
Option string - -

Quote

Documentation: https://developer.xero.com/documentation/api/quotes

Column Type Relates to Notes
RemoteID string - -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
QuoteID guid - -
UpdatedDateUTC datetime - -
Status string - -
QuoteNumber string - -
Reference string - -
Terms string - -
ContactRemoteID guid Contact → ContactID -
Date datetime - -
DateString string - -
ExpiryDate datetime - -
ExpiryDateString string - -
CurrencyRate decimal - -
CurrencyCode string - -
SubTotal decimal - -
TotalTax decimal - -
Total decimal - -
Title string - -
Summary string - -
BrandingThemeID guid - -
LineAmountTypes string - -

QuoteItem

Documentation: https://developer.xero.com/documentation/api/quotes#LineItems

Column Type Relates to Notes
RemoteID string - -
QuoteRemoteID string Quote → RemoteID -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
Description string - -
LineItemID guid - -
QuoteID guid Quote → QuoteID -
ItemCode string - -
UnitAmount decimal - -
TaxType string - -
TaxAmount decimal - -
LineAmount decimal - -
AccountCode string - -
Quantity decimal - -
Tax decimal - -
UnitPriceExTax decimal - -
TotalExTax decimal - -
TotalIncTax decimal - -
TaxRatePercentage decimal - -
ProductRemoteID string - -
TaxRateRemoteID string - -
LineItemsIncludeTax boolean - -

QuoteItemTrackingCategory

Documentation: https://developer.xero.com/documentation/api/quotes#TrackingCategory

Column Type Relates to Notes
RemoteID string - -
QuoteItemRemoteID string QuoteItem → RemoteID -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
Name string - -
LineItemID guid QuoteItem → LineItemID -
TrackingCategoryID guid TrackingCategory → TrackingCategoryID -
Option string - -

RepeatingInvoice

Documentation: https://developer.xero.com/documentation/api/repeating-invoices

Column Type Relates to Notes
RemoteID string - -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
RepeatingInvoiceID guid - -
Status string - -
TotalDiscount decimal - -
InvoiceNumber string - -
AmountDue decimal - -
AmountPaid decimal - -
AmountCredited decimal - -
IsAccountsReceivable boolean - -
IsAccountsPayable boolean - -
LineItemsIncludeTax boolean - -
TotalExTax decimal - -
Tax decimal - -
TotalIncTax decimal - -
CustomerRemoteID string - -
WhenDue datetime - -
WhenPaid datetime - -
TaxRatePercentage decimal - -
TaxRateRemoteID string - -
IsSentToClient boolean - -
LineAmountTypes string - -
Type string - -
SubTotal decimal - -
TotalTax decimal - -
Total decimal - -
CurrencyCode string - -
Reference string - -
HasAttachments boolean - -
ContactID guid Contact → ContactID -
SchedulePeriod int - -
ScheduleUnit string - -
ScheduleDueDateType string - -
ScheduleStartDate datetime - -
ScheduleEndDate datetime - -
ScheduleNextDate datetime - -

RepeatingInvoiceItem

Documentation: https://developer.xero.com/documentation/api/repeating-invoices#LineItemsElements

Column Type Relates to Notes
RemoteID string - -
RepeatingInvoiceRemoteID string RepeatingInvoice → RemoteID -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
Description string - -
LineItemID guid - -
DiscountRate decimal - -
RepeatingInvoiceID guid RepeatingInvoice → RepeatingInvoiceID -
DiscountPercentage decimal - -
DiscountAmount decimal - -
ItemCode string - -
UnitAmount decimal - -
TaxType string - -
TaxAmount decimal - -
LineAmount decimal - -
AccountCode string - -
Quantity decimal - -
Tax decimal - -
UnitPriceExTax decimal - -
TotalExTax decimal - -
TotalIncTax decimal - -
TaxRatePercentage decimal - -
ProductRemoteID string - -
TaxRateRemoteID string - -
LineItemsIncludeTax boolean - -

RepeatingInvoiceItemTrackingCategory

Documentation: https://developer.xero.com/documentation/api/invoices#TrackingCategory

Column Type Relates to Notes
RemoteID string - -
RepeatingInvoiceItemRemoteID string RepeatingInvoiceItem → RemoteID -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
Name string - -
RepeatingLineItemID guid RepeatingInvoiceItem → LineItemID -
TrackingCategoryID guid - -
Option string - -

TaxRate

Documentation: https://developer.xero.com/documentation/api/tax-rates

Column Type Relates to Notes
RemoteID string - -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
Name string - -
TaxType string - -
Status string - -
CanApplyToAssets boolean - -
CanApplyToEquity boolean - -
CanApplyToExpenses boolean - -
CanApplyToLiabilities boolean - -
CanApplyToRevenue boolean - -
DisplayTaxRate decimal - -
EffectiveRate decimal - -

TrackingCategory

Documentation: https://developer.xero.com/documentation/api/tracking-categories

Column Type Relates to Notes
RemoteID string - -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
Name string - -
TrackingCategoryID guid - -
Status string - -

User

Documentation: https://developer.xero.com/documentation/api/users

Column Type Relates to Notes
RemoteID string - -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
Name string - -
UserID guid - -
UpdatedDateUTC datetime - -
Email string - -
FirstName string - -
LastName string - -
AvatarURL string - -
FullName string - -
EmailAddress string - -
IsSubscriber boolean - -
OrganisationRole string - -

BalanceSheetReport

Documentation: https://developer.xero.com/documentation/api/accounting/reports#balance-sheet

Column Type Relates to Notes
RemoteID string - -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
AsOfDate datetime - -
ReportType string - -
Title string - -
SubTitle string - -
SubSubTitle string - -
UpdatedDateUTC datetime - -

ProfitAndLossReportByFiscalYear

Documentation: https://developer.xero.com/documentation/api/accounting/reports/#profit-and-loss

Column Type Relates to Notes
RemoteID string - -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
From datetime - -
Until datetime - -
ReportType string - -
Title string - -
SubTitle string - -
SubSubTitle string - -
UpdatedDateUTC datetime - -

ProfitAndLossReportByFiscalYearAndTrackingCategory

Documentation: https://developer.xero.com/documentation/api/accounting/reports/#profit-and-loss

Column Type Relates to Notes
RemoteID string - -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
From datetime - -
Until datetime - -
TrackingCategoryID guid - -
ReportType string - -
Title string - -
SubTitle string - -
SubSubTitle string - -
UpdatedDateUTC datetime - -

ProfitAndLossReportByMonth

Documentation: https://developer.xero.com/documentation/api/accounting/reports/#profit-and-loss

Column Type Relates to Notes
RemoteID string - -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
From datetime - -
Until datetime - -
ReportType string - -
Title string - -
SubTitle string - -
SubSubTitle string - -
UpdatedDateUTC datetime - -

ProfitAndLossReportByMonthAndTrackingCategory

Documentation: https://developer.xero.com/documentation/api/accounting/reports/#profit-and-loss

Column Type Relates to Notes
RemoteID string - -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
From datetime - -
Until datetime - -
TrackingCategoryID guid - -
ReportType string - -
Title string - -
SubTitle string - -
SubSubTitle string - -
UpdatedDateUTC datetime - -

TrialBalanceReport

Documentation: https://developer.xero.com/documentation/api/accounting/reports/#trial-balance

Column Type Relates to Notes
RemoteID string - -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
AsOfDate datetime - -
ReportType string - -
Title string - -
SubTitle string - -
SubSubTitle string - -
UpdatedDateUTC datetime - -

BankTransactionLineItem

Column Type Relates to Notes
RemoteID string - -
BankTransactionRemoteID string BankTransaction → RemoteID -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
Description string - -
LineItemID guid - -
BankTransactionID guid BankTransaction → BankTransactionID -
UnitAmount decimal - -
TaxType string - -
TaxAmount decimal - -
LineAmount decimal - -
AccountCode string - -
Quantity decimal - -
DiscountEnteredAsPercent boolean - -

BatchPaymentPayment

Column Type Relates to Notes
RemoteID string - -
BatchPaymentRemoteID string BatchPayment → RemoteID -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
BatchPaymentID guid BatchPayment → BatchPaymentID -
PaymentID guid Payment → PaymentID -

BudgetTrackingCategory

Column Type Relates to Notes
RemoteID string - -
BudgetRemoteID string Budget → RemoteID -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
TrackingCategoryID guid TrackingCategory → TrackingCategoryID -
BudgetID guid Budget → BudgetID -
Option string -
  • 20 Apr 2021 The API docs suggest a TrackingOptionID is returned, however we only get the Option description. Use this against our TrackingCategoryOption.Name field to join your data.

ContactContactGroup

Column Type Relates to Notes
RemoteID string - -
ContactRemoteID string Contact → RemoteID -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
ContactGroupID guid ContactGroup → ContactGroupID -
ContactID guid Contact → ContactID -

ContactAddress

Column Type Relates to Notes
RemoteID string - -
ContactRemoteID string Contact → RemoteID -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
ContactID guid Contact → ContactID -
AddressType string - -
City string - -
Region string - -
PostalCode string - -
Country string - -
AddressLine1 string - -
AddressLine2 string - -
AddressLine3 string - -
AddressLine4 string - -
AttentionTo string - -

ContactPhone

Column Type Relates to Notes
RemoteID string - -
ContactRemoteID string Contact → RemoteID -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
ContactID guid Contact → ContactID -
PhoneType string -
  • 3 Jun 2021 May be one of DEFAULT, DDI, MOBILE, FAX
PhoneNumber string - -
PhoneAreaCode string - -
PhoneCountryCode string - -

CreditNoteAllocation

Column Type Relates to Notes
RemoteID string - -
CreditNoteRemoteID string CreditNote → RemoteID -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
CreditNoteID guid CreditNote → CreditNoteID -
AllocationNumber int -
  • 21 Mar 2023 Confirmed in testing that more than one allocation can be made to the same invoice, as such this manual iterator has been implemented due to a lack of unique identifiers
Amount decimal - -
Date datetime - -
InvoiceID guid Invoice → InvoiceID -

JournalItemTrackingCategory

Column Type Relates to Notes
RemoteID string - -
JournalItemRemoteID string JournalItem → RemoteID -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
JournalItemID guid JournalItem → JournalLineID -
TrackingCategoryID guid TrackingCategory → TrackingCategoryID -
TrackingOptionID guid TrackingCategoryOption → TrackingOptionID -

ManualJournalLineItem

Column Type Relates to Notes
RemoteID string - -
ManualJournalRemoteID string ManualJournal → RemoteID -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
Description string - -
ManualJournalID guid ManualJournal → ManualJournalID -
Position int - -
AccountID guid Account → AccountID -
TaxType string - -
TaxAmount decimal - -
LineAmount decimal - -
AccountCode string - -
IsBlank boolean - -

OverPaymentAllocation

Column Type Relates to Notes
RemoteID string - -
OverpaymentRemoteID string Overpayment → RemoteID -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
OverpaymentID guid Overpayment → OverpaymentID -
SortOrder int - -
Amount decimal - -
Date datetime - -
AllocationInvoiceID guid Invoice → InvoiceID -

PurchaseOrderLineItem

Column Type Relates to Notes
RemoteID string - -
PurchaseOrderRemoteID string PurchaseOrder → RemoteID -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
Description string - -
PurchaseOrderID guid PurchaseOrder → PurchaseOrderID -
LineItemID guid - -
UnitAmount decimal - -
TaxType string - -
TaxAmount decimal - -
LineAmount decimal - -
Quantity decimal - -

TrackingCategoryOption

Documentation: https://developer.xero.com/documentation/api/tracking-categories#Options

Column Type Relates to Notes
RemoteID string - -
TrackingCategoryRemoteID string TrackingCategory → RemoteID -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
Name string - -
TrackingOptionID guid - -
TrackingCategoryID guid TrackingCategory → TrackingCategoryID -
Status string - -
HasValidationErrors boolean - -
IsArchived boolean - -
IsActive boolean - -

ReportRowCell

Column Type Relates to Notes
RemoteID string - -
ReportRowRemoteID string ReportRow → RemoteID -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
Value string - -
AccountID guid Account → AccountID -
CellNumber int - -
ValueAsDecimal decimal - -
ValueAsDate datetime - -
ValueAsBoolean boolean - -

ReportRow

Column Type Relates to Notes
RemoteID string - -
BalanceSheetReportRemoteID string BalanceSheetReport → RemoteID -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
ReportRemoteID string - -
ProfitAndLossReportByFiscalYearRemoteID string ProfitAndLossReportByFiscalYear → RemoteID -
ProfitAndLossReportByMonthAndTrackingCategoryRemoteID string ProfitAndLossReportByMonthAndTrackingCategory → RemoteID -
ProfitAndLossReportByFiscalYearAndTrackingCategoryRemoteID string ProfitAndLossReportByFiscalYearAndTrackingCategory → RemoteID -
ProfitAndLossReportByMonthRemoteID string ProfitAndLossReportByMonth → RemoteID -
TrialBalanceReportRemoteID string TrialBalanceReport → RemoteID -
ReportRowRemoteID string ReportRow → RemoteID -
ParentReportRowRemoteID string ReportRow → RemoteID
  • 6 Jun 2023 Where the row falls within a section, we reference the section ID here
RowNumber int - -
RowType string - -
Title string -
  • 6 Jun 2023 The row title is only populated for 'Section' row types

BudgetAccountBalance

Column Type Relates to Notes
RemoteID string - -
BudgetRemoteID string Budget → RemoteID -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
AccountID guid Account → AccountID -
Period string - -
BudgetID guid Budget → BudgetID -
Amount decimal - -
Notes string - -

ManualJournalItemTrackingCategory

Column Type Relates to Notes
RemoteID string - -
ManualJournalLineItemRemoteID string ManualJournalLineItem → RemoteID -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
ManualJournalItemRemoteID string ManualJournalLineItem → RemoteID -
TrackingCategoryID guid TrackingCategory → TrackingCategoryID -
TrackingOptionID guid TrackingCategoryOption → TrackingOptionID -

Need more?

SyncHub lives and breathes APIs and data. Keep exploring using the links below.

Connect your own Xero data Querying Xero using regular SQL