2024 is the year of the connector - vote here

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, read the docs 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 - -
Code string - -
Status string - -
Type string - -
TaxType string - -
Class string - -
EnablePaymentsToAccount boolean - -
ShowInExpenseClaims boolean - -
CurrencyCode string - -
ReportingCode string - -
ReportingCodeName string - -
UpdatedDateUTC datetime - -

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 - -
BankAccountID guid Account → AccountID -
Type string - -
Reference string - -
IsReconciled boolean - -
CurrencyRate decimal - -
HasAttachments boolean - -
ContactID guid Contact → ContactID -
DateString string - -
Date datetime - -
Status string - -
LineAmountTypes string - -
SubTotals decimal - -
TotalTax decimal - -
Total decimal - -
UpdatedDateUTC datetime - -
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 - -
TrackingCategoryID guid TrackingCategory → TrackingCategoryID -
BankTransactionLineItemID guid BankTransactionLineItem → LineItemID -
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 - -
Reference string - -
UpdatedDateUTC datetime - -
AccountID guid Account → AccountID -
DateString datetime - -
Date datetime - -
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 - -
Type string - -
UpdatedDateUTC datetime - -

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 - -
SalesPaymentTermsDay int - -
SalesPaymentTermsType string - -
BillsPaymentTermsDay int - -
BillsPaymentTermsType string - -
ContactID guid - -
ContactStatus string - -
BankAccountDetails string - -
EmailAddress string - -
AccountNumber string - -
SkypeUserName string - -
TaxNumber string - -
AccountsReceivableTaxType string - -
AccountsPayableTaxType string - -
UpdatedDateUTC datetime - -
IsSupplier boolean - -
IsCustomer boolean - -
DefaultCurrency string - -
FirstName string - -
LastName string - -
Email string - -
FullName string - -
AvatarURL 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 - -
Status string - -
Date datetime - -
LineAmountTypes string - -
Type string - -
UpdatedDateUTC datetime - -
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 - -
Status string - -
FirstName string - -
LastName string - -
UpdatedDateUTC datetime - -

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 -
Changes string - -
DateUTC datetime - -
User 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 -
Changes string - -
DateUTC datetime - -
User 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 -
Changes string - -
DateUTC datetime - -
User 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 -
Changes string - -
DateUTC datetime - -
User 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 -
Changes string - -
DateUTC datetime - -
User 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 -
Changes string - -
DateUTC datetime - -
User 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 -
Changes string - -
DateUTC datetime - -
User 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 -
Changes string - -
DateUTC datetime - -
User 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 -
Changes string - -
DateUTC datetime - -
User 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 -
Changes string - -
DateUTC datetime - -
User 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 -
Changes string - -
DateUTC datetime - -
User 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 -
Changes string - -
DateUTC datetime - -
User 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 -
Changes string - -
DateUTC datetime - -
User 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 -
Changes string - -
DateUTC datetime - -
User 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 - -
TrackingCategoryID guid TrackingCategory → TrackingCategoryID -
LineItemID guid InvoiceItem → LineItemID -
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 - -
QuantityOnHand decimal - -
TotalCostPool decimal - -
IsTrackedAsInventory boolean - -
PurchaseDescription string - -
InventoryAssetAccountCode string - -
IsSold boolean - -
IsPurchased boolean - -
UpdatedDateUTC datetime - -
Code string - -
SalePrice decimal - -
SalesAccountCode string - -
SalesTaxType string - -
PurchasePrice decimal - -
PurchaseAccountCode string - -
PurchaseAccountCOGSCode string - -
PurchaseTaxType string - -
Price decimal - -

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 - -
JournalDate datetime - -
JournalNumber int - -
CreatedDateUTC datetime - -
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 - -
JournalID guid Journal → JournalID -
JournalLineID guid - -
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 - -
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 - -
UpdatedDateUTC datetime - -
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 - -
Date datetime - -
Status string - -
LineAmountTypes string - -
UpdatedDateUTC datetime - -
ManualJournalID guid - -
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 -
  • 28 May 2020 NB. the OrganisationStatus field is about API connectivity, so we can't use that for the purposes of our IsDeleted flag
WhenCreated datetime - -
WhenModified datetime - -
Name string - -
OrganisationID guid - -
OrganisationStatus string - -
CreatedDateUTC datetime - -
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 - -
Type string - -
RemainingCredit decimal - -
HasAttachments boolean - -
ContactID guid Contact → ContactID -
DateString string - -
Date datetime - -
Status string - -
LineAmountTypes string - -
SubTotal decimal - -
TotalTax decimal - -
Total decimal - -
UpdatedDateUTC datetime - -
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 - -
InvoiceRemoteID guid Invoice → InvoiceID -
CreditNoteRemoteID guid CreditNote → CreditNoteID -
AccountRemoteID guid Account → AccountID -
UpdatedDateUTC datetime - -
Amount decimal - -
CurrencyRate decimal - -
IsReconciled boolean - -
Date datetime - -
Reference string - -
Status string - -
PaymentType string - -
BatchPaymentID guid -
  • 2 Jun 2020 Foreign Key - References `BatchPayment` (not implemented). Only returned when getting individual payments.
Number int - -
Tip decimal - -

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 - -
Type string - -
Reference string - -
RemainingCredit decimal - -
HasAttachments boolean - -
ContactID guid Contact → ContactID -
DateString string - -
Date datetime - -
Status string - -
LineAmountTypes string - -
SubTotal decimal - -
TotalTax decimal - -
Total decimal - -
UpdatedDateUTC datetime - -
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 - -
LineItemID guid - -
PrepaymentID guid Prepayment → PrepaymentID -
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 - -

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 - -
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 - -
Status string - -
LineAmountTypes string - -
SubTotal decimal - -
TotalTax decimal - -
Total decimal - -
UpdatedDateUTC datetime - -
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 - -
TrackingCategoryID guid TrackingCategory → TrackingCategoryID -
PurchaseOrderLineItemID guid PurchaseOrderLineItem → LineItemID -
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 - -
QuoteNumber string - -
Reference string - -
Terms string - -
ContactRemoteID guid Contact → ContactID -
Date datetime - -
DateString string - -
ExpiryDate datetime - -
ExpiryDateString string - -
Status string - -
CurrencyRate decimal - -
CurrencyCode string - -
SubTotal decimal - -
TotalTax decimal - -
Total decimal - -
Title string - -
Summary string - -
BrandingThemeID guid - -
UpdatedDateUTC datetime - -
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 - -
TrackingCategoryID guid TrackingCategory → TrackingCategoryID -
LineItemID guid QuoteItem → LineItemID -
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 - -
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 - -
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 - -

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 - -
TrackingCategoryID guid - -
RepeatingLineItemID guid InvoiceItem → LineItemID -
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 - -
CanApplyToAssets boolean - -
CanApplyToEquity boolean - -
CanApplyToExpenses boolean - -
CanApplyToLiabilities boolean - -
CanApplyToRevenue boolean - -
DisplayTaxRate decimal - -
EffectiveRate decimal - -
Status string - -

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 - -
EmailAddress string - -
UpdatedDateUTC datetime - -
IsSubscriber boolean - -
OrganisationRole string - -
Email string - -
FirstName string - -
LastName string - -
AvatarURL string - -
FullName 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 - -

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 - -

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 - -
BudgetID guid Budget → BudgetID -
TrackingCategoryID guid TrackingCategory → TrackingCategoryID -
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 - -
TrackingCategoryID guid TrackingCategory → TrackingCategoryID -
TrackingOptionID guid TrackingCategoryOption → TrackingOptionID -
JournalItemID guid JournalItem → JournalLineID -

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 - -
TaxType string - -
TaxAmount decimal - -
LineAmount decimal - -
AccountCode string - -
AccountID guid Account → AccountID -
IsBlank boolean - -

OverPaymentAllocation

Column Type Relates to Notes
RemoteID string - -
OverpaymentRemoteID string Overpayment → RemoteID -
WhenUpsertedIntoDataStore datetime - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
SortOrder int - -
OverpaymentID guid Overpayment → OverpaymentID -
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 - -
LineItemID guid - -
PurchaseOrderID guid PurchaseOrder → PurchaseOrderID -
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 -
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 - -
Period string - -
Amount decimal - -
Notes string - -
AccountID guid Account → AccountID -
BudgetID guid Budget → BudgetID -

ManualJournalItemTrackingCategory

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

Need more?

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

Connect your own Xero data Interactive ERD Querying Xero using regular SQL