Sage Business Cloud Accounting Connection

Sage Business Cloud Accounting Connection

This documentation is based on version 21.0.8257 of the connector.

Getting Started

Sage Business Cloud Accounting Version Support

The connector leverages the Sage Accounting API to enable bidirectional access to Sage Business Cloud Accounting.

Establishing a Connection

Connecting to Sage Business Cloud Accounting

Authenticating to Sage Business Cloud Accounting

Sage Business Cloud Accounting uses the OAuth standard to authenticate users.

You can connect after setting required connection properties for your user credentials and obtaining required access tokens using a tool such as Postman.

See Using OAuth Authentication for an authentication guide and details of the OAuth authentication flow.

Using OAuth Authentication

OAuth requires the use of access tokens obtained with Postman or a similar tool. 3-legged OAuth authentication is supported as described on Connections under three-legged OAuth authentication.

Important Notes

Stored Procedures

  • The stored procedures features described in this documentation are not currently supported.
  • Because stored procedures are not currently supported, any feature dependent on stored procedures is also currently not supported.

Configuration Files and Their Paths

  • All references to adding configuration files and their paths refer to files and locations on the Jitterbit Harmony Agent where the connector is installed. These paths are to be adjusted as appropriate depending on the agent and the operating system. If multiple agents are used in an agent group, identical files will be required on each agent.

Advanced Features

This section details a selection of advanced features of the Sage Business Cloud Accounting connector.

User Defined Views

The connector allows you to define virtual tables, called user defined views, whose contents are decided by a pre-configured query. These views are useful when you cannot directly control queries being issued to the drivers. See User Defined Views for an overview of creating and configuring custom views.

SSL Configuration

Use SSL Configuration to adjust how connector handles TLS/SSL certificate negotiations. You can choose from various certificate formats; see the SSLServerCert property under "Connection String Options" for more information.

Proxy

To configure the connector using Private Agent proxy settings, select the Use Proxy Settings checkbox on the connection configuration screen.

User Defined Views

The Jitterbit Connector for Sage Business Cloud Accounting allows you to define a virtual table whose contents are decided by a pre-configured query. These are called User Defined Views, which are useful in situations where you cannot directly control the query being issued to the driver, e.g. when using the driver from Jitterbit. The User Defined Views can be used to define predicates that are always applied. If you specify additional predicates in the query to the view, they are combined with the query already defined as part of the view.

There are two ways to create user defined views:
  • Create a JSON-formatted configuration file defining the views you want.
  • DDL statements.

Defining Views Using a Configuration File

User Defined Views are defined in a JSON-formatted configuration file called UserDefinedViews.json. The connector automatically detects the views specified in this file.

You can also have multiple view definitions and control them using the UserDefinedViews connection property. When you use this property, only the specified views are seen by the connector.

This User Defined View configuration file is formatted as follows:
  • Each root element defines the name of a view.
  • Each root element contains a child element, called query, which contains the custom SQL query for the view.

For example:

{
	"MyView": {
		"query": "SELECT * FROM SampleTable_1 WHERE MyColumn = 'value'"
	},
	"MyView2": {
		"query": "SELECT * FROM MyTable WHERE Id IN (1,2,3)"
	}
}
Use the UserDefinedViews connection property to specify the location of your JSON configuration file. For example:
"UserDefinedViews", "C:\Users\yourusername\Desktop\tmp\UserDefinedViews.json"

Schema for User Defined Views

User Defined Views are exposed in the UserViews schema by default. This is done to avoid the view's name clashing with an actual entity in the data model. You can change the name of the schema used for UserViews by setting the UserViewsSchemaName property.

Working with User Defined Views

For example, a SQL statement with a User Defined View called UserViews.RCustomers only lists customers in Raleigh:
SELECT * FROM Customers WHERE City = 'Raleigh';
An example of a query to the driver:
SELECT * FROM UserViews.RCustomers WHERE Status = 'Active';
Resulting in the effective query to the source:
SELECT * FROM Customers WHERE City = 'Raleigh' AND Status = 'Active';
That is a very simple example of a query to a User Defined View that is effectively a combination of the view query and the view definition. It is possible to compose these queries in much more complex patterns. All SQL operations are allowed in both queries and are combined when appropriate.

SSL Configuration

Customizing the SSL Configuration

By default, the connector attempts to negotiate SSL/TLS by checking the server's certificate against the system's trusted certificate store.

To specify another certificate, see the SSLServerCert property for the available formats to do so.

Data Model

This section shows the available API objects and provides more information on executing SQL to Sage Business Cloud Accounting APIs.

Schemas for most database objects are defined in simple, text-based configuration files.

Key Features

  • The connector models Sage Business Cloud Accounting entities like BankAccounts, Contacts, and Journals as relational tables and views, allowing you to write SQL to select, insert, update, and delete Sage Business Cloud Accounting data.
  • Stored procedures* allow you to execute operations to Sage Business Cloud Accounting, including retrieving the access token and keeping it refreshed in OAuth 2.0.
  • Live connectivity to these objects means any changes to your Sage Business Cloud Accounting account are immediately reflected when using the connector.

Tables

Views describes the available views. Views are statically defined to model BankAccounts, Contacts, and Journals, and more.

Views

Views describes the available views. Views are statically defined to model TaxOffices, Currencies, SalesInvoiceLineItems, and more.

Stored Procedures

NOTE: Stored procedures are not currently supported. See the above note for details.

Stored Procedures are function-like interfaces to Sage Business Cloud Accounting. Stored procedures* allow you to execute operations to Sage Business Cloud Accounting, including downloading documents and moving envelopes.

The connector offloads as much of the SELECT statement processing as possible to the Sage Business Cloud Accounting APIs and then processes the rest of the query in memory. See SupportEnhancedSQL for more information on how the connector circumvents API limitations with in-memory client-side processing.

Tables

The connector models the data in Sage Business Cloud Accounting into a list of tables that can be queried using standard SQL statements.

Generally, querying Sage Business Cloud Accounting tables is the same as querying a table in a relational database. Sometimes there are special cases, for example, including a certain column in the WHERE clause might be required to get data for certain columns in the table. This is typically needed for situations where a separate request must be made for each row to get certain columns. These types of situations are clearly documented at the top of the table page linked below.

Jitterbit Connector for Sage Business Cloud Accounting Tables

Name Description
Addresses Query Addresses in Sage Business Cloud Accounting.
Attachments Query Attachments in Sage Business Cloud Accounting.
BankAccounts Query BankAccounts in Sage Business Cloud Accounting.
BankDeposits Query BankDeposits in Sage Business Cloud Accounting.
BankOpeningBalances Query BankOpeningBalances in Sage Business Cloud Accounting.
BankReconciliations Query BankReconciliations in Sage Business Cloud Accounting.
BankTransfers Query BankTransfers in Sage Business Cloud Accounting.
BusinessExchangeRates Query BusinessExchangeRates in Sage Business Cloud Accounting.
ContactAllocations Query ContactAllocations in Sage Business Cloud Accounting.
ContactOpeningBalances Query ContactOpeningBalances in Sage Business Cloud Accounting.
ContactPayments Query ContactPayments in Sage Business Cloud Accounting.
ContactPeople Query ContactPeople in Sage Business Cloud Accounting.
Contacts Query Contacts in Sage Business Cloud Accounting.
DatevSettings Query DatevSettings in Sage Business Cloud Accounting.
EmailSettings Query EmailSettings in Sage Business Cloud Accounting.
FinancialSettings Query FinancialSettings in Sage Business Cloud Accounting.
HostedArtefactPaymentSettings Query HostedArtefactPaymentSettings in Sage Business Cloud Accounting.
InvoiceSettings Query InvoiceSettings in Sage Business Cloud Accounting.
JournalCodes Query JournalCodes in Sage Business Cloud Accounting.
Journals Query Journals in Sage Business Cloud Accounting.
LedgerAccountOpeningBalances Query LedgerAccountOpeningBalances in Sage Business Cloud Accounting.
LedgerAccounts Query LedgerAccounts in Sage Business Cloud Accounting.
MigrationTaxReturns Query MigrationTaxReturns in Sage Business Cloud Accounting.
OpeningBalanceJournals Query OpeningBalanceJournals in Sage Business Cloud Accounting.
OtherPayments Query OtherPayments in Sage Business Cloud Accounting.
Products Query Products in Sage Business Cloud Accounting.
ProductSalesPriceTypes Query ProductSalesPriceTypes in Sage Business Cloud Accounting.
PurchaseCreditNotes Query PurchaseCreditNotes in Sage Business Cloud Accounting.
PurchaseInvoices Query PurchaseInvoices in Sage Business Cloud Accounting.
PurchaseQuickEntries Query PurchaseQuickEntries in Sage Business Cloud Accounting.
SalesCreditNotes Query SalesCreditNotes in Sage Business Cloud Accounting.
SalesEstimates Query SalesEstimates in Sage Business Cloud Accounting.
SalesInvoices Query SalesInvoices in Sage Business Cloud Accounting.
SalesQuickEntries Query SalesQuickEntries in Sage Business Cloud Accounting.
SalesQuotes Query SalesQuotes in Sage Business Cloud Accounting.
Services Query Services in Sage Business Cloud Accounting.
StockItems Query StockItems in Sage Business Cloud Accounting.
StockMovements Query StockMovements in Sage Business Cloud Accounting.
TaxProfiles Query TaxProfiles in Sage Business Cloud Accounting.
TaxRates Query TaxRates in Sage Business Cloud Accounting.

Addresses

Query Addresses in Sage Business Cloud Accounting.

Table Specific Information

Query the available Addresses

Select

The driver uses the Sage Accounting API to process search criteria that refer to BankAccountId,ContactId,UpdatedOrCreatedSince,DeletedSince columns. The driver processes other filters client-side within the driver.

  • BankAccountId supports the '=' operator.
  • ContactId supports the '=' operator.
  • UpdatedOrCreatedSince supports the '=' operator.
  • DeletedSince supports the '=' operator.

For example, the following queries are processed server side:

SELECT * FROM Addresses WHERE BankAccountId='80b34e32b85a46c39a71c3627d577fa2'
SELECT * FROM Addresses WHERE ContactId='abc'
SELECT * FROM Addresses WHERE UpdatedOrCreatedSince='2018-12-15'
SELECT * FROM Addresses WHERE DeletedSince='2019-10-02T11:46:53Z'

Insert

  • Create a simple address.

    INSERT INTO  [Addresses] (AddressTypeId, name, IsMainAddress, ContactId) VALUES ('DELIVERY', 'AddressName', 'false', '456')

Update

Updates are performed based on Id.

UPDATE Addresses SET AddressTypeId='DELIVERY' WHERE Id='c04b66f130d14478b7978452aeedb8ad'

Delete

You must specify the ID of the Addresses to delete it.

DELETE FROM Addresses WHERE ID ='c04b66f130d14478b7978452aeedb8ad' 

Columns

Name Type ReadOnly References Description
Id [KEY] String False

The unique identifier for the item

CreatedAt Datetime True

The datetime when the item was created

AddressTypeId String False

The unique identifier for the item

The allowed values are DELIVERY, ACCOUNTS, SALES, PURCHASING.

Region String False

The address state/province/region

PostalCode String False

The address postal code/zipcode

DeletedAt Datetime True

The datetime when the item was deleted

BankAccountId String False

BankAccounts.Id

The unique identifier for the item

IsMainAddress Bool False

Indicates whether this is a main address

CountryId String False

Countries.Id

The unique identifier for the item

DisplayedAs String False

The name of the resource

City String False

The address town/city

ContactId String False

Contacts.Id

The unique identifier for the item

LegacyId Int False

The legacy ID for the item

CountryGroupId String False

CountryGroups.Id

The unique identifier for the item

AddressLine2 String False

The second line of the address

AddressLine1 String False

The first line of the address

Name String False

The name of the address

UpdatedAt Datetime True

The datetime when the item was last updated

DeletedSince Datetime True

Attachments

Query Attachments in Sage Business Cloud Accounting.

Table Specific Information

Query the available Attachments

Select

The driver uses the Sage Accounting API to process search criteria that refer to AttachmentContextTypeId,AttachmentContextId,LegacyAttachmentContextType,LegacyAttachmentContextId,UpdatedOrCreatedSince,DeletedSince columns. The driver processes other filters client-side within the driver.

  • AttachmentContextTypeId supports the '=' operator.
  • AttachmentContextId supports the '=' operator.
  • LegacyAttachmentContextType supports the '=' operator.
  • LegacyAttachmentContextId supports the '=' operator.
  • UpdatedOrCreatedSince supports the '=' operator.
  • DeletedSince supports the '=' operator.

For example, the following queries are processed server side:

SELECT * FROM Attachments WHERE AttachmentContextTypeId='PURCHASE_CREDIT_NOTE'
SELECT * FROM Attachments WHERE AttachmentContextId='abc'
SELECT * FROM Attachments WHERE LegacyAttachmentContextType='abc'
SELECT * FROM Attachments WHERE LegacyAttachmentContextId='abc'
SELECT * FROM Attachments WHERE UpdatedOrCreatedSince='2018-12-15'
SELECT * FROM Attachments WHERE DeletedSince='2018-12-15'

Update

Updates are performed based on Id.

UPDATE Attachments SET FileExtension='abc' WHERE Id='123'

Delete

You must specify the ID of the Attachments to delete it.

DELETE FROM Attachments WHERE ID ='123' 

Columns

Name Type ReadOnly References Description
Id [KEY] String False

The unique identifier for the item

CreatedAt Datetime True

The datetime when the attachment was created

DisplayedAs String False

The name of the resource

FileExtension String False

The file extension of the attachment

TransactionId String False

Transactions.Id

The unique identifier for the item

File String False

The base64 encoded representation of the file

AttachmentContextTypeId String False

The unique identifier for the item

The allowed values are BANK_TRANSFER, CONTACT, JOURNAL, OTHER_PAYMENT, PURCHASE_CORRECTIVE_INVOICE, PURCHASE_CREDIT_NOTE, PURCHASE_INVOICE, SALES_CORRECTIVE_INVOICE, SALES_CREDIT_NOTE, SALES_ESTIMATE, SALES_INVOICE, SALES_QUOTE, BUSINESS.

_filePath String False

The file path of the attachment

FileSize Int False

The file size of the attachment in Bytes

DeletedAt Datetime True

The datetime when the item was deleted

FileName String False

The file name of the attachment

UpdatedAt Datetime True

The datetime when the attachment was last updated

MimeType String False

The mime type of the attachment

Description String False

The description of the attachment

AttachmentContextDisplayedAs String False

The name of the resource

AttachmentContextLegacyId Int False

The legacy ID for the item

AttachmentContextId String False

The unique identifier for the item

LegacyAttachmentContextType String True
LegacyAttachmentContextId String False
DeletedSince Datetime True

BankAccounts

Query BankAccounts in Sage Business Cloud Accounting.

Table Specific Information

Query the available BankAccounts

Select

The driver uses the Sage Accounting API to process search criteria that refer to UpdatedOrCreatedSince,DeletedSince,ExcludeStripe columns. The driver processes other filters client-side within the driver.

  • UpdatedOrCreatedSince supports the '=' operator.
  • DeletedSince supports the '=' operator.
  • ExcludeStripe supports the '=' operator.

For example, the following queries are processed server side:

SELECT * FROM BankAccounts WHERE UpdatedOrCreatedSince='2019-10-02T11:46:53Z'
SELECT * FROM BankAccounts WHERE DeletedSince='2019-10-02T11:46:53Z'
SELECT * FROM BankAccounts WHERE ExcludeStripe='false'

Insert

  • Create a simple bank account.

    INSERT INTO  [Addresses] (BankAccountTypeId, Balance, AccountName) VALUES ('2', '0.0', 'test')

Update

Updates are performed based on Id.

UPDATE BankAccounts SET BankAccountTypeId='SAVINGS' WHERE Id='80b34e32b85a46c39a71c3627d577fa2'

Delete

You must specify the ID of the BankAccounts to delete it.

DELETE FROM BankAccounts WHERE ID ='80b34e32b85a46c39a71c3627d577fa2' 

Columns

Name Type ReadOnly References Description
Id [KEY] String False

The unique identifier for the item

BankAccountTypeId String False

The unique identifier for the item

The allowed values are CHECKING, SAVINGS, CREDIT_CARD, CASH_IN_HAND, LOAN, OTHER.

Balance String False

The bank account balance

CreatedAt Datetime True

The datetime when the item was created

SortCode String False

The sort code

Iban String False

The iban

Bic String False

The bic

AccountName String False

The account name

AccountNumber String False

The account number

MainContactId String False

The fax number of the contact

DefaultPaymentMethodId String False

PaymentMethods.Id

The unique identifier for the item

Deletable Bool False

Indicates whether or not the bank account can be deleted

LedgerAccountId String False

LedgerAccounts.Id

The unique identifier for the item

MainAddressId String False

Addresses.Id

The unique identifier for the item

DeletedAt Datetime True

The datetime when the item was deleted

Editable Bool False

Indicates whether or not the bank account can be edited

DisplayedAs String False

The name of the resource

JournalCodeId String False

JournalCodes.Id

The unique identifier for the item

NominalCode Int False

The nominal code of the bank account

LegacyId Int False

The legacy ID for the item

UpdatedAt Datetime True

The datetime when the item was last updated

DeletedSince Datetime True
ExcludeStripe Boolean True

BankDeposits

Query BankDeposits in Sage Business Cloud Accounting.

Table Specific Information

Query the available BankDeposits

Select

The driver uses the Sage Accounting API to process search criteria that refer to UpdatedOrCreatedSince,DeletedSince,FromDate,ToDate columns. The driver processes other filters client-side within the driver.

  • UpdatedOrCreatedSince supports the '=' operator.
  • DeletedSince supports the '=' operator.
  • FromDate supports the '=' operator.
  • ToDate supports the '=' operator.

For example, the following queries are processed server side:

SELECT * FROM BankDeposits WHERE UpdatedOrCreatedSince='2018-12-15'
SELECT * FROM BankDeposits WHERE DeletedSince='2018-12-15'
SELECT * FROM BankDeposits WHERE FromDate='2018-12-15'
SELECT * FROM BankDeposits WHERE ToDate='2018-12-15'

Delete

You must specify the ID of the BankDeposits to delete it.

DELETE FROM BankDeposits WHERE ID ='123' 

Columns

Name Type ReadOnly References Description
Id [KEY] String False

The unique identifier for the item

CreatedAt Datetime True

The datetime when the item was created

DisplayedAs String False

The name of the resource

TransactionTypeId String False

TransactionTypes.id

The unique identifier for the item

ChequeAmount String False

Cheque total in the deposit

TransactionId String False

Transactions.id

The unique identifier for the item

Date Datetime True

User generated date of transaction, not necessarily when it was created

Reference String False

Reference

UpdatedAt Datetime True

The datetime when the item was last updated

LegacyId Int False

The legacy ID for the item

DeletedAt Datetime True

The datetime when the item was deleted

FromBankAccountId String False

BankAccounts.Id

The unique identifier for the item

ToBankAccountId String False

BankAccounts.Id

The unique identifier for the item

TotalAmount String False

Total of cash and cheques in the deposit

CashAmount String False

Cash total in the deposit

DeletedSince Datetime True
FromDate Datetime True
ToDate Datetime True

BankOpeningBalances

Query BankOpeningBalances in Sage Business Cloud Accounting.

Table Specific Information

Query the available BankOpeningBalances

Select

The driver uses the Sage Accounting API to process search criteria that refer to BankAccountId,UpdatedOrCreatedSince columns. The driver processes other filters client-side within the driver.

  • BankAccountId supports the '=' operator.
  • UpdatedOrCreatedSince supports the '=' operator.

For example, the following queries are processed server side:

SELECT * FROM BankOpeningBalances WHERE BankAccountId='abc'
SELECT * FROM BankOpeningBalances WHERE UpdatedOrCreatedSince='2018-12-15'

Insert

INSERT INTO BankOpeningBalances (TransactionId, BankAccountId) VALUES ('2', '123')

Update

Updates are performed based on Id.

UPDATE BankOpeningBalances SET Date='2018-12-15' WHERE Id='123'

Delete

You must specify the ID of the BankOpeningBalances to delete it.

DELETE FROM BankOpeningBalances WHERE ID ='123' 

Columns

Name Type ReadOnly References Description
Id [KEY] String False

The unique identifier for the item

CreatedAt Datetime True

The datetime when the item was created

DisplayedAs String False

The name of the resource

TransactionId String False

Transactions.Id

The unique identifier for the item

Date Datetime True

The date of the opening balance

Credit String False

The credit amount of the opening balance

BankAccountId String False

BankAccounts.Id

The unique identifier for the item

UpdatedAt Datetime True

The datetime when the item was last updated

LegacyId Int False

The legacy ID for the item

Debit String False

The debit amount of the opening balance

BankReconciliations

Query BankReconciliations in Sage Business Cloud Accounting.

Table Specific Information

Query the available BankReconciliations

Select

The driver uses the Sage Accounting API to process search criteria that refer to BankAccountId,UpdatedOrCreatedSince columns. The driver processes other filters client-side within the driver.

  • BankAccountId supports the '=' operator.
  • UpdatedOrCreatedSince supports the '=' operator.

For example, the following queries are processed server side:

SELECT * FROM BankReconciliations WHERE BankAccountId='abc'
SELECT * FROM BankReconciliations WHERE UpdatedOrCreatedSince='2018-12-15'

Update

Updates are performed based on Id.

UPDATE BankReconciliations SET BalanceDifference='abc' WHERE Id='123'

Columns

Name Type ReadOnly References Description
Id [KEY] String False

The unique identifier for the item

CreatedAt Datetime True

The datetime when the item was created

DisplayedAs String False

The name of the resource

BalanceDifference String False

The difference between the statement end balance and the reconciled balance

TotalPaid String False

The total amount paid

Reference String False

A reference for the bank reconciliation

TotalReceived String False

The total amount received

StatusDisplayedAs String False

The bank reconciliation status name

StatusId String False

The bank reconciliation status id

BankAccountId String False

BankAccounts.Id

The unique identifier for the item

StatementDate String False

The date of the bank reconciliation

UpdatedAt Datetime True

The datetime when the item was last updated

LegacyId Int False

The legacy ID for the item

StatementEndBalance String False

The statement end balance for the reconciliation

StartingBalance String False

The starting balance of the bank reconciliation

ClosingBalance String False

The closing balance of the bank reconciliation

ReconciledBalance String False

The reconciled balance of the bank reconciliation

BankTransfers

Query BankTransfers in Sage Business Cloud Accounting.

Table Specific Information

Query the available BankTransfers

Select

The driver uses the Sage Accounting API to process search criteria that refer to UpdatedOrCreatedSince,DeletedSince,FromDate,ToDate columns. The driver processes other filters client-side within the driver.

  • UpdatedOrCreatedSince supports the '=' operator.
  • DeletedSince supports the '=' operator.
  • FromDate supports the '=' operator.
  • ToDate supports the '=' operator.

For example, the following queries are processed server side:

SELECT * FROM BankTransfers WHERE UpdatedOrCreatedSince='2018-12-15'
SELECT * FROM BankTransfers WHERE DeletedSince='2018-12-15'
SELECT * FROM BankTransfers WHERE FromDate='2018-12-15'
SELECT * FROM BankTransfers WHERE ToDate='2018-12-15'

Insert

INSERT INTO BankTransfers (Amount, FromBankAccountId, ToBankAccountId) VALUES ('2', '123', '456')

Update

Updates are performed based on Id.

UPDATE BankTransfers SET Date='abc' WHERE Id='123'

Delete

You must specify the ID of the BankTransfers to delete it.

DELETE FROM BankTransfers WHERE ID ='123' 

Columns

Name Type ReadOnly References Description
Id [KEY] String False

The unique identifier for the item

CreatedAt Datetime True

The datetime when the item was created

DisplayedAs String False

The name of the resource

TransactionId String False

Transactions.Id

The unique identifier for the item

Date String False

The date of the bank transfer

Reference String False

The reference for the bank transfer

Amount String False

The amount of the bank transfer

UpdatedAt Datetime True

The datetime when the item was last updated

LegacyId Int False

The legacy ID for the item

DeletedAt Datetime True

The datetime when the item was deleted

FromBankAccountId String False

BankAccounts.Id

The unique identifier for the item

ToBankAccountId String False

BankAccounts.Id

The unique identifier for the item

Description String False

The description for the bank transfer

DeletedSince Datetime True
FromDate Datetime True
ToDate Datetime True

BusinessExchangeRates

Query BusinessExchangeRates in Sage Business Cloud Accounting.

Table Specific Information

Query the available BusinessExchangeRates

Select

The driver uses the Sage Accounting API to process search criteria that refer to CurrencyId column. The driver processes other filters client-side within the driver.

  • CurrencyId supports the '=' operator.

For example, the following queries are processed server side:

SELECT * FROM BusinessExchangeRates WHERE CurrencyId='USD'

Update

Updates are performed based on Id.

UPDATE BusinessExchangeRates SET Rate=1.5 WHERE CurrencyId='USD'

Delete

You must specify the ID of the BusinessExchangeRates to delete it.

DELETE FROM BusinessExchangeRates WHERE CurrencyId ='USD' 

Columns

Name Type ReadOnly References Description
CurrencyId [KEY] String False

Currencies.Id

The unique identifier for the item

UpdatedAt Datetime True

The datetime when the item was last updated

Rate String False

The exchange rate

DisplayedAs String False

Display text for the item

CreatedAt Datetime True

The datetime when the item was created

BaseCurrencyId String False

Currencies.Id

The unique identifier for the item

InverseRate String False

The inverse exchange rate

ContactAllocations

Query ContactAllocations in Sage Business Cloud Accounting.

Table Specific Information

Query the available ContactAllocations

Select

The driver uses the Sage Accounting API to process search criteria that refer to TransactionTypeId,ContactId,UpdatedOrCreatedSince,DeletedSince columns. The driver processes other filters client-side within the driver.

  • TransactionTypeId supports the '=' operator.
  • ContactId supports the '=' operator.
  • UpdatedOrCreatedSince supports the '=' operator.
  • DeletedSince supports the '=' operator.

For example, the following queries are processed server side:

SELECT * FROM ContactAllocations WHERE TransactionTypeId='abc'
SELECT * FROM ContactAllocations WHERE ContactId='abc'
SELECT * FROM ContactAllocations WHERE UpdatedOrCreatedSince='2018-12-15'
SELECT * FROM ContactAllocations WHERE DeletedSince='2018-12-15'

Update

Updates are performed based on Id.

UPDATE ContactAllocations SET TransactionTypeId='abc' WHERE Id='123'

Delete

You must specify the ID of the ContactAllocations to delete it.

DELETE FROM ContactAllocations WHERE ID ='123' 

Columns

Name Type ReadOnly References Description
Id [KEY] String False

The unique identifier for the item

CreatedAt Datetime True

The datetime when the item was created

DisplayedAs String False

The name of the resource

TransactionTypeId String False

TransactionTypes.Id

The unique identifier for the item

TransactionId String False

Transactions.Id

The unique identifier for the item

Date Datetime True

The date the allocation was made

AllocatedArtefactsAggregate String False

The payment lines of the payment

ContactId String False

Contacts.Id

The unique identifier for the item

UpdatedAt Datetime True

The datetime when the item was last updated

LegacyId Int False

The legacy ID for the item

DeletedAt Datetime True

The datetime when the item was deleted

LinksAggregate String False

Links for the resource

DeletedSince Datetime True

ContactOpeningBalances

Query ContactOpeningBalances in Sage Business Cloud Accounting.

Table Specific Information

Query the available ContactOpeningBalances

Select

The driver uses the Sage Accounting API to process search criteria that refer to ContactId,ContactTypeId,UpdatedOrCreatedSince,DeletedSince columns. The driver processes other filters client-side within the driver.

  • ContactId supports the '=' operator.
  • ContactTypeId supports the '=' operator.
  • UpdatedOrCreatedSince supports the '=' operator.
  • DeletedSince supports the '=' operator.

For example, the following queries are processed server side:

SELECT * FROM ContactOpeningBalances WHERE ContactId='abc'
SELECT * FROM ContactOpeningBalances WHERE ContactTypeId='CUSTOMER'
SELECT * FROM ContactOpeningBalances WHERE UpdatedOrCreatedSince='2018-12-15'
SELECT * FROM ContactOpeningBalances WHERE DeletedSince='2018-12-15'

Update

Updates are performed based on Id.

UPDATE ContactOpeningBalances SET TaxRateId='abc' WHERE Id='123'

Delete

You must specify the ID of the ContactOpeningBalances to delete it.

DELETE FROM ContactOpeningBalances WHERE ID ='123' 

Columns

Name Type ReadOnly References Description
Id [KEY] String False

The unique identifier for the item

TaxRateId String False

TaxRates.Id

The unique identifier for the item

CreatedAt Datetime True

The datetime when the item was created

TransactionTypeId String False

TransactionTypes.Id

The unique identifier for the item

NetAmount String False

The net amount of the opening balance

CurrencyId String False

Currencies.Id

The unique identifier for the item

Reference String False

The reference for the opening balance

TotalAmount String False

The total amount of the opening balance

TaxBreakdownAggregate String False

The tax breakdown for the opening balance

DeletedAt Datetime True

The datetime when the item was deleted

ContactOpeningBalanceTypeId String False

ContactOpeningBalances.Id

The unique identifier for the item

DisplayedAs String False

The name of the resource

TaxAmount String False

The tax amount of the opening balance

TransactionId String False

Transactions.Id

The unique identifier for the item

ContactId String False

Contacts.Id

The unique identifier for the item

Details String False

A description of the opening balance

LegacyId Int False

The legacy ID for the item

ContactTypeId String False

The allowed values are CUSTOMER, VENDOR.

Date String False

The date of the opening balance

UpdatedAt Datetime True

The datetime when the item was last updated

ExchangeRate String False

The exchange rate for the opening balance

DeletedSince Datetime True

ContactPayments

Query ContactPayments in Sage Business Cloud Accounting.

Table Specific Information

Query the available ContactPayments

Select

The driver uses the Sage Accounting API to process search criteria that refer to TransactionTypeId,BankAccountId,ContactId,UpdatedOrCreatedSince,DeletedSince,FromDate,ToDate columns. The driver processes other filters client-side within the driver.

  • TransactionTypeId supports the '=' operator.
  • BankAccountId supports the '=' operator.
  • ContactId supports the '=' operator.
  • UpdatedOrCreatedSince supports the '=' operator.
  • DeletedSince supports the '=' operator.
  • FromDate supports the '=' operator.
  • ToDate supports the '=' operator.

For example, the following queries are processed server side:

SELECT * FROM ContactPayments WHERE TransactionTypeId='abc'
SELECT * FROM ContactPayments WHERE BankAccountId='abc'
SELECT * FROM ContactPayments WHERE ContactId='abc'
SELECT * FROM ContactPayments WHERE UpdatedOrCreatedSince='2018-12-15'
SELECT * FROM ContactPayments WHERE DeletedSince='2018-12-15'
SELECT * FROM ContactPayments WHERE FromDate='2018-12-15'
SELECT * FROM ContactPayments WHERE ToDate='2018-12-15'

Update

Updates are performed based on Id.

UPDATE ContactPayments SET TaxRateId='abc' WHERE Id='123'

Delete

You must specify the ID of the ContactPayments to delete it.

DELETE FROM ContactPayments WHERE ID ='123' 

Columns

Name Type ReadOnly References Description
Id [KEY] String False

The unique identifier for the item

TaxRateId String False

TaxRates.Id

The unique identifier for the item

BaseCurrencyCurrencyCharge String False

The currency conversion charges in base currency

CreatedAt Datetime True

The datetime when the item was created

LinksAggregate String False

Links for the resource

AllocatedArtefactsAggregate String False

The allocated artefacts

TransactionTypeId String False

TransactionTypes.Id

The unique identifier for the item

NetAmount String False

The net amount of the payment

BaseCurrencyTaxAmount String False

The tax amount of the payment in base currency

CurrencyId String False

Currencies.Id

The unique identifier for the item

PaymentMethodId String False

PaymentMethods.Id

The unique identifier for the item

BaseCurrencyTotalAmount String False

The total amount of the payment in base currency

Reference String False

A reference for the payment

TotalAmount String False

The total amount of the payment

DeletedAt Datetime True

The datetime when the item was deleted

BankAccountId String False

BankAccounts.Id

The unique identifier for the item

BaseCurrencyNetAmount String False

The net amount of the payment in base currency

PaymentOnAccountContactName String False

The name of the contact when the payment on account was created

PaymentOnAccountCreatedAt Datetime True

The datetime when the item was created

PaymentOnAccountBaseCurrencyOutstandingAmount String False

The outstanding amount of the payment on account in base currency

PaymentOnAccountNetAmount String False

The net amount of the payment on account

PaymentOnAccountBaseCurrencyTaxAmount String False

The tax amount of the payment on account in base currency

PaymentOnAccountCurrencyId String False

Currencies.Id

The unique identifier for the item

PaymentOnAccountBaseCurrencyTotalAmount String False

The total amount of the payment on account in base currency

PaymentOnAccountReference String False

The reference for the payment on account

PaymentOnAccountTotalAmount String False

The total amount of the payment on account

PaymentOnAccountId String False

The unique identifier for the item

PaymentOnAccountBaseCurrencyNetAmount String False

The net amount of the payment on account in base currency

PaymentOnAccountStatusDisplayedAs String False

The name of the resource

PaymentOnAccountStatusLegacyId Int False

The legacy ID for the item

PaymentOnAccountStatusId String False

The unique identifier for the item

PaymentOnAccountContactReference String False

The reference of the contact when the payment on account was created

PaymentOnAccountDisplayedAs String False

The name of the resource

PaymentOnAccountTaxAmount String False

The tax amount of the payment on account

PaymentOnAccountContactDisplayedAs String False

The name of the resource

PaymentOnAccountContactLegacyId Int False

The legacy ID for the item

PaymentOnAccountContactId String False

The unique identifier for the item

PaymentOnAccountOutstandingAmount String False

The outstanding amount of the payment on account

PaymentOnAccountLegacyId Int False

The legacy ID for the item

PaymentOnAccountDate String False

The date of the payment on account

PaymentOnAccountExchangeRate String False

The exchange rate for the payment on account

PaymentOnAccountUpdatedAt Datetime True

The datetime when the item was last updated

Editable Bool False

Indicates whether payment can be edited

DisplayedAs String False

The name of the resource

TaxAmount String False

The tax amount of the payment

TransactionId String False

Transactions.Id

The unique identifier for the item

ContactId String False

Contacts.Id

The unique identifier for the item

LegacyId Int False

The legacy ID for the item

Date String False

The date the payment was made

UpdatedAt Datetime True

The datetime when the item was last updated

ExchangeRate String False

The exchange rate of the payment

DeletedSince Datetime True
FromDate Datetime True
ToDate Datetime True

ContactPeople

Query ContactPeople in Sage Business Cloud Accounting.

Table Specific Information

Query the available ContactPeople

Select

The driver uses the Sage Accounting API to process search criteria that refer to AddressId,UpdatedOrCreatedSince,DeletedSince,ContactId columns. The driver processes other filters client-side within the driver.

  • AddressId supports the '=' operator.
  • UpdatedOrCreatedSince supports the '=' operator.
  • DeletedSince supports the '=' operator.
  • ContactId supports the '=' operator.

For example, the following queries are processed server side:

SELECT * FROM ContactPeople WHERE AddressId='c04b66f130d14478b7978452aeedb8ad'
SELECT * FROM ContactPeople WHERE UpdatedOrCreatedSince='2019-10-02T11:46:53Z'
SELECT * FROM ContactPeople WHERE DeletedSince='2019-10-02T11:46:53Z'
SELECT * FROM ContactPeople WHERE ContactId='c04b66f130d16538b7978452aeedb8ad'

Delete

You must specify the ID of the ContactPeople to delete it.

DELETE FROM ContactPeople WHERE ID ='69d00f6931614e3fbb7ccad85fb84af0' 

Columns

Name Type ReadOnly References Description
Id [KEY] String False

The unique identifier for the item

CreatedAt Datetime True

The datetime when the item was created

DisplayedAs String False

The name of the resource

Mobile String False

The mobile number of the contact person

Name String False

The name of the contact person

Fax String False

The fax number of the contact person

Telephone String False

The telephone number of the contact person

ContactPersonTypeAggregate String False

The contact person types for the contact person

Email String False

The email address of the contact person

UpdatedAt Datetime True

The datetime when the item was last updated

DeletedAt Datetime True

The datetime when the item was deleted

LegacyId Int False

The legacy ID for the item

IsMainContact Bool False

Indicates whether this is the main contact person

AddressId String False

Addresses.Id

The unique identifier for the item

JobTitle String False

The job title of the contact person

IsPreferredContact Bool False

Indicates that this contact person is a preferred contact

DeletedSince Datetime True
ContactId String False

Contacts

Query Contacts in Sage Business Cloud Accounting.

Table Specific Information

Query the available Contacts

Select

The driver uses the Sage Accounting API to process search criteria that refer to Email,ShowBalance,UpdatedOrCreatedSince,DeletedSince,ContactTypeId,ExcludeSystem,ShowBalance columns. The driver processes other filters client-side within the driver.

  • Email supports the '=' operator.
  • ShowBalance supports the '=' operator.
  • UpdatedOrCreatedSince supports the '=' operator.
  • DeletedSince supports the '=' operator.
  • ContactTypeId supports the '=' operator.
  • ExcludeSystem supports the '=' operator.
  • ShowBalance supports the '=' operator.

For example, the following queries are processed server side:

SELECT * FROM Contacts WHERE Email='abc'
SELECT * FROM Contacts WHERE ShowBalance='abc'
SELECT * FROM Contacts WHERE UpdatedOrCreatedSince='2018-12-15'
SELECT * FROM Contacts WHERE DeletedSince='2018-12-15'
SELECT * FROM Contacts WHERE ContactTypeId='abc'
SELECT * FROM Contacts WHERE ExcludeSystem='abc'
SELECT * FROM Contacts WHERE ShowBalance='abc'

Insert

INSERT INTO Contacts (CreditDays, TaxNumber, AccountNumber) VALUES ('2', '123', '456')

Update

Updates are performed based on Id.

UPDATE Contacts SET CreditDays=123 WHERE Id='123'

Delete

You must specify the ID of the Contacts to delete it.

DELETE FROM Contacts WHERE ID ='123' 

Columns

Name Type ReadOnly References Description
Id [KEY] String False

The unique identifier for the item

DisplayedAs String False

The name of the resource

CreditDays Int False

Custom credit days for the contact. If returned as null in a GET response, you may want to GET /invoice_settings and use 'vendor_credit_days' as default/fallback according to your use case.

Reference String False

The reference for the contact

TaxNumber String False

The tax number for the contact

MainAddressId String False

Addresses.Id

The unique identifier for the item

GdprObfuscated Bool False

General Data Protection Regulation (GDPR) came into effect on 25th May 2018. It introduces new rules for how business owners manage their contacts' personal data. When this field returns 'true', means that the contact has been requested to be obfuscated and you can not create any artifact (sales invoices, purchase invoices, ...) but you can still check previously created artifacts.

DefaultSalesTaxRateId String False

TaxRates.Id

The unique identifier for the item

AuxReference String False

Auxiliary reference. Used for German

ProductSalesPriceTypeId String False

ProductSalesPriceTypes.Id

The unique identifier for the item

SortCode String False

The sort code

Iban String False

The iban

Bic String False

The bic

AccountName String False

The account name

AccountNumber String False

The account number

Name String False

The name of the contact

Deletable Bool False

Indicates whether the ledger entry has been deleted or not

AuxiliaryAccount String False

Auxiliary account - used when auxiliary accounting is enabled in business settings

DefaultPurchaseLedgerAccountId String False

LedgerAccounts.Id

The unique identifier for the item

DeliveryAddressId String False

Addresses.Id

The unique identifier for the item

Balance String False

The contact balance

Locale String False

The locale for the contact

UpdatedAt Datetime True

The datetime when the item was last updated

RegisteredNumber String False

The registered number of the contact's business. Only used for German businesses and represents the

Email String False

The email address for the given contact

LegacyId Int False

The legacy ID for the item

ContactTypesAggregate String False

The contact types for the contact

TaxTreatmentRestOfWorldTax Bool False

Indicates a contact where rest of world tax rules should be applied

TaxTreatmentEuTaxRegistered Bool False

Indicates a contact where EC tax rules should be applied

TaxTreatmentEuNotTaxRegistered Bool False

Indicates an EU contact without a tax number - home tax rules should be applied

TaxTreatmentHomeTax Bool False

Indicates a contact where home country tax rules should be applied

CreditLimit String False

Custom credit limit amount for the contact

CreatedAt Datetime True

The datetime when the item was created

LinksAggregate String False

Links for the resource

TaxCalculation String False

The tax calculation method - used for French VAT & Recargo

MainContactPersonId String False

The unique identifier for the item

ContactPersonTypeAggregate String False

The contact person types for the contact person

SourceGuid String False

Used when importing contacts from external sources

CurrencyId String False

Currencies.Id

The unique identifier for the item

CreditTermsAndConditions String False

Custom terms and conditions for the contact (Customers only)

Notes String False

The notes for the contact

DeletedAt Datetime True

The datetime when the item was deleted

System Bool False

Identifies a contact as being a system contact used for processing specific transaction types and reserved specifically for those transaction types such as tax return payments/refunds.

DefaultSalesLedgerAccountId String False

LedgerAccounts.Id

The unique identifier for the item

ShowBalance String False
DeletedSince Datetime True
ContactTypeId String True
ExcludeSystem String True
NIBased Bool True

Determines if a business is located in Northern Ireland

TaxTreatmentHomeTax Bool True

Explains which tax treatment should be applied for a contact.

TaxTreatmentEuTaxRegistered Bool True

Explains which tax treatment should be applied for a contact.

TaxTreatmentEuNotTaxRegistered Bool True

Explains which tax treatment should be applied for a contact.

TaxTreatmentRestOfWorldTax Bool True

Explains which tax treatment should be applied for a contact.

TaxTreatmentIsImporter Bool True

Explains which tax treatment should be applied for a contact.

CISRegistered Bool True

Boolean flag set to true if the contact is CIS registered.

DatevSettings

Query DatevSettings in Sage Business Cloud Accounting.

Table Specific Information

Query the available DatevSettings

Select

The driver processes all filters client-side within the driver.

Columns

Name Type ReadOnly References Description
ClientNumber Int False

The users registration number

NextCustomerNumber Int False

The next unique customer number

TaxConsultantNumber Int False

Registration number of the Accountant

NextSupplierNumber Int False

The next unique supplier number

EmailSettings

Query EmailSettings in Sage Business Cloud Accounting.

Table Specific Information

Query the available EmailSettings

Select

The driver processes all filters client-side within the driver.

Columns

Name Type ReadOnly References Description
SendBcc Bool False

Indicates whether the user should always be sent a copy when sending a document via email

DefaultMessagesAggregate String False

The default email messages for the businesses per message type and locale.

PdfAttached Bool False

Indicates whether PDFs are always attached as part of sending emails for a business

FinancialSettings

Query FinancialSettings in Sage Business Cloud Accounting.

Table Specific Information

Query the available FinancialSettings

Select

The driver processes all filters client-side within the driver.

Columns

Name Type ReadOnly References Description
TaxReturnFrequencyId String False

TaxReturnFrequencies.Id

The unique identifier for the item

FlatRateTaxPercentage String False

The tax percentage that applies to flat rate tax schemes.

SalesTaxCalculation String False

The method of collection for tax on sales. Allowed values -

UseLiveExchangeRates Bool False

Indicates whether to use live or business defined exchange rates

YearEndLockdownDate String False

The year end lockdown date of the business

PurchaseTaxCalculation String False

The method of collection for tax on purchases. Allowed values -

BaseCurrencyId String False

Currencies.Id

The unique identifier for the item

AccountingType String False

Indicates the accounting type of a business, it can be accrual or cash based

MtdAuthenticatedDate Datetime True

Indicates when a UK business enabled UK Making Tax Digital for VAT, nil if not enabled or non-uk

YearEndDate Datetime True

The financial year end date of the business

TaxNumber String False

The tax number

MultiCurrencyEnabled Bool False

Indicates whether multi-currency is enabled for the business

MtdActivationStatus String False

Indicates the UK Making Tax Digital for VAT activation status

AccountsStartDate String False

The accounts start date of the business

GeneralTaxNumber String False

The number for various tax report submissions

DefaultIrpfRate String False

The default IRPF rate

MtdConnected Bool False

Indicates whether UK Making Tax Digital for VAT is currently connected

PostponedAccounting Bool False

If you import goods into the EU from the rest of the world, you can use postponed accounting to deal with import VAT.

TaxOfficeId String False

TaxOffices.Id

The unique identifier for the item

TaxSchemeId String False

TaxSchemes.Id

The unique identifier for the item

HostedArtefactPaymentSettings

Query HostedArtefactPaymentSettings in Sage Business Cloud Accounting.

Table Specific Information

Query the available HostedArtefactPaymentSettings

Select

The driver uses the Sage Accounting API to process search criteria that refer to ObjectGuid column. The driver processes other filters client-side within the driver.

  • ObjectGuid supports the '=' operator.

For example, the following queries are processed server side:

SELECT * FROM HostedArtefactPaymentSettings WHERE ObjectGuid='abc'

Delete

You must specify the ID of the HostedArtefactPaymentSettings to delete it.

DELETE FROM HostedArtefactPaymentSettings WHERE ID ='123' 

Columns

Name Type ReadOnly References Description
Id [KEY] String False

The unique identifier for the item

ObjectGuid String False

The sageone_guid of the object you are disabling card payments for.

DisplayedAs String False

The name of the resource

DisablePayment Bool False

The flag to disable online payments.

LegacyId Int False

The legacy ID for the item

InvoiceSettings

Query InvoiceSettings in Sage Business Cloud Accounting.

Table Specific Information

Query the available InvoiceSettings

Select

The driver processes all filters client-side within the driver.

Columns

Name Type ReadOnly References Description
LineItemUnitPrice String False

The user defined unit price column title

LineItemDescription String False

The user defined description column title

LineItemQuantity String False

The user defined quantity column title

LineItemDiscount String False

The user defined discount column title

DeliveryNoteShowSignature Bool False

Indicates whether to include the signature lines on delivery notes

PaymentBankAccountId String False

BankAccounts.Id

The unique identifier for the item

PrintStatementsDaysOverdue Bool False

Indicates wether days of overdue are printed in the contact's statement

PrintStatementsTableOfBalances Bool False

Indicates wether table of balances is printed in the contact's statement

PrintContactDefaultDeliveryAddress String False

Indicates which contact address is used for the delivery address of the invoice. Valid options are: INVOICE_ADDRESS, CONTACT_DELIVERY_ADDRESS, NONE.

PrintContactTelephone Bool False

Indicates wether telephone is printed on the invoice

PrintContactWebsite Bool False

Indicates wether website is printed on the invoice

PrintContactMobile Bool False

Indicates wether mobile is printed on the invoice

PrintContactBusinessName Bool False

Indicates wether business name is printed on the invoice

PrintContactDueDate Bool False

Indicates wether due date is printed on the invoice

PrintContactEmailAddress Bool False

Indicates wether email address is printed on the invoice

InsurerDisplayedAs String False

The name of the resource

InsurerCreditDays Int False

Custom credit days for the contact. If returned as null in a GET response, you may want to GET /invoice_settings and use 'vendor_credit_days' as default/fallback according to your use case.

InsurerReference String False

The reference for the contact

InsurerTaxNumber String False

The tax number for the contact

InsurerMainAddressId String False

Addresses.Id

The unique identifier for the item

InsurerGdprObfuscated Bool False

General Data Protection Regulation (GDPR) came into effect on 25th May 2018. It introduces new rules for how business owners manage their contacts' personal data. When this field returns 'true', means that the contact has been requested to be obfuscated and you can not create any artifact (sales invoices, purchase invoices, ...) but you can still check previously created artifacts.

InsurerDefaultSalesTaxRateId String False

TaxRates.Id

The unique identifier for the item

InsurerAuxReference String False

Auxiliary reference. Used for German

InsurerProductSalesPriceTypeId String False

ProductSalesPriceTypes.Id

The unique identifier for the item

InsurerBankAccountDetailsSortCode String False

The sort code

InsurerBankAccountDetailsIban String False

The iban

InsurerBankAccountDetailsBic String False

The bic

InsurerBankAccountDetailsAccountName String False

The account name

InsurerBankAccountDetailsAccountNumber String False

The account number

InsurerName String False

The name of the contact

InsurerDeletable Bool False

Indicates whether the ledger entry has been deleted or not

InsurerAuxiliaryAccount String False

Auxiliary account - used when auxiliary accounting is enabled in business settings

InsurerDefaultPurchaseLedgerAccountId String False

LedgerAccounts.Id

The unique identifier for the item

InsurerDeliveryAddressId String False

Addresses.Id

The unique identifier for the item

InsurerBalance String False

The contact balance

InsurerLocale String False

The locale for the contact

InsurerUpdatedAt Datetime True

The datetime when the item was last updated

InsurerRegisteredNumber String False

The registered number of the contact's business. Only used for German businesses and represents the

InsurerEmail String False

The email address for the given contact

InsurerLegacyId Int False

The legacy ID for the item

ContactTypesAggregate String False

The contact types for the contact

InsurerTaxTreatmentRestOfWorldTax Bool False

Indicates a contact where rest of world tax rules should be applied

InsurerTaxTreatmentEuTaxRegistered Bool False

Indicates a contact where EC tax rules should be applied

InsurerTaxTreatmentEuNotTaxRegistered Bool False

Indicates an EU contact without a tax number - home tax rules should be applied

InsurerTaxTreatmentHomeTax Bool False

Indicates a contact where home country tax rules should be applied

InsurerCreditLimit String False

Custom credit limit amount for the contact

InsurerCreatedAt Datetime True

The datetime when the item was created

InsurerId String False

The unique identifier for the item

InsurerTaxCalculation String False

The tax calculation method - used for French VAT & Recargo

InsurerMainContactPersonId String False

The unique identifier for the item

InsurerSourceGuid String False

Used when importing contacts from external sources

InsurerCurrencyDisplayedAs String False

The name of the resource

InsurerCurrencyLegacyId Int False

The legacy ID for the item

InsurerCurrencyId String False

The unique identifier for the item

InsurerCreditTermsAndConditions String False

Custom terms and conditions for the contact (Customers only)

InsurerNotes String False

The notes for the contact

InsurerDeletedAt Datetime True

The datetime when the item was deleted

InsurerSystem Bool False

Identifies a contact as being a system contact used for processing specific transaction types and reserved specifically for those transaction types such as tax return payments/refunds.

InsurerDefaultSalesLedgerAccountId String False

LedgerAccounts.Id

The unique identifier for the item

VendorCreditDays Int False

The default delay within which the business has to pay any vendor's invoice

InsuranceText String False

The insurance mention to be displayed on invoices (France only)

SeparateInvoiceCreditNoteNumbering Bool False

Indicates whether to use separate or combined number sequences for invoices and credit notes

EstimateNumberPrefix String False

The prefix to use for sales estimates

QuoteTermsAndConditions String False

The default terms and conditions to include on quotes

InsuranceArea String False

The insurance area to be displayed on invoices (France only)

QuickEntryPrefix String False

The prefix to use for quick entries

CustomerCreditDays Int False

The default delay within which the business.

DocumentHeadingsSalesEstimate String False

The customisable sales estimate document header

DocumentHeadingsSalesInvoice String False

The customisable sales invoice document header

DocumentHeadingsSalesCorrectiveInvoice String False

The customisable sales corrective invoice document header

DocumentHeadingsProForma String False

The customisable pro forma document header

DocumentHeadingsRemittanceAdvice String False

The customisable remittance advice document header

DocumentHeadingsSalesCreditNote String False

The customisable sales credit note document header

DocumentHeadingsStatement String False

The customisable statement document header

DocumentHeadingsDeliveryNote String False

The customisable delivery note document header

DocumentHeadingsSalesQuote String False

The customisable sales quote document header

SalesCreditNoteNumberPrefix String False

The prefix to use for sales credit notes

QuoteDefaultDaysToExpiry Int False

The default number of days before quotes expire

FooterDetailsColumn3 String False

The pdf footer details for column 3

FooterDetailsColumn2 String False

The pdf footer details for column 2

FooterDetailsColumn1 String False

The pdf footer details for column 1

QuoteNumberPrefix String False

The prefix to use for sales quotes

NextCreditNoteNumber Int False

The next credit note number

PromptPaymentPercentage String False

The percentage applied to late payment of invoices (France only)

ShowAutoEntrepreneur Bool False

Indicates whether to include auto entrepreneur details on invoices (France only)

NextSalesCorrectiveInvoiceNumber Int False

The next sales corrective invoice number

InvoiceTermsAndConditions String False

The default terms and conditions to include on invoices

DeliveryNoteTermsAndConditions String False

The default terms and conditions to include on delivery notes

InsuranceType String False

The insurance type to be displayed on invoices (France only)

NextInvoiceNumber Int False

The next invoice number

SalesInvoiceNumberPrefix String False

The prefix to use for sales invoices

ShowInsurance Bool False

Indicates whether to include insurance details on invoices (France only)

DefaultNoteOnInvoice String False

The default notes to include on invoices

SalesCorrectiveInvoiceNumberPrefix String False

The sales corrective invoice number prefix (Spain only)

DefaultNoteOnCreditNote String False

The default notes to include on credit notes

EstimateDefaultDaysToExpiry Int False

The default number of days before estimates expire

NextQuoteNumber Int False

The next quote number

EstimateTermsAndConditions String False

The default terms and conditions to include on estimates

LatePaymentPercentage String False

The percentage charge applied to late payment of invoices (France only)

DeliveryNoteShowNotes Bool False

Indicates whether to include the document notes on delivery notes

DeliveryNoteShowPicked Bool False

Indicates whether to include the picked column on delivery notes

DeliveryNoteShowContactDetails Bool False

Indicates whether to include contact details on delivery notes

JournalCodes

Query JournalCodes in Sage Business Cloud Accounting.

Table Specific Information

Query the available JournalCodes

Select

The driver uses the Sage Accounting API to process search criteria that refer to UpdatedOrCreatedSince column. The driver processes other filters client-side within the driver.

  • UpdatedOrCreatedSince supports the '=' operator.

For example, the following queries are processed server side:

SELECT * FROM JournalCodes WHERE UpdatedOrCreatedSince='2018-12-15'

Update

Updates are performed based on Id.

UPDATE JournalCodes SET Name='abc' WHERE Id='123'

Delete

You must specify the ID of the JournalCodes to delete it.

DELETE FROM JournalCodes WHERE ID ='123' 

Columns

Name Type ReadOnly References Description
Id [KEY] String False

The unique identifier for the item

CreatedAt Datetime True

The datetime when the item was created

DisplayedAs String False

The name of the resource

Name String False

The name of the journal code

Code String False

The code of the journal code

Reserved Bool False

Indicates whether the journal code is reserved

UpdatedAt Datetime True

The datetime when the item was last updated

LegacyId Int False

The legacy ID for the item

ControlName String False

The control name of the journal code

Journals

Query Journals in Sage Business Cloud Accounting.

Table Specific Information

Query the available Journals

Select

The driver uses the Sage Accounting API to process search criteria that refer to UpdatedOrCreatedSince,DeletedSince columns. The driver processes other filters client-side within the driver.

  • UpdatedOrCreatedSince supports the '=' operator.
  • DeletedSince supports the '=' operator.

For example, the following queries are processed server side:

SELECT * FROM Journals WHERE UpdatedOrCreatedSince='2018-12-15'
SELECT * FROM Journals WHERE DeletedSince='2018-12-15'

Insert

  • Insert a journal with aggregate value.
    INSERT INTO  [Journals] ( JournalLineAggregate, date, reference) VALUES ('{"ledger_account_id": "1fcc2765741311ea8d3c022ace1a91e2","debit": 12,"credit": 0,"details": "details","tax_reconciled": true,"cleared": true,"bank_reconciled": true},{"ledger_account_id": "1fcc2765741311ea8d3c022ace1a91e2","debit": 0,"credit": 12,"details": "details","tax_reconciled": true,"cleared": true,"bank_reconciled": true}','2019-07-12','Journal Reference')
  • Insert a journal using temp tables.
    INSERT INTO  [JournalLineItemsAggregate#Temp] (credit, debit, details, ledgerAccountId) VALUES ('10', '0', 'details1', '6035f3d39d7511e9a2b70e070fead9d0')
    INSERT INTO  [JournalLineItemsAggregate#Temp] (credit, debit, details, ledgerAccountId) VALUES ('0', '10', 'details2', '6035f3d39d7511e9a2b70e070fead9d0')
    INSERT INTO  [Journals] (JournalLineAggregate, date, reference) VALUES ('JournalLineItemsAggregate#Temp','2019-07-12','Reference2')

Delete

You must specify the ID of the Journals to delete it.

DELETE FROM Journals WHERE ID ='123' 

Columns

Name Type ReadOnly References Description
Id [KEY] String False

The unique identifier for the item

CreatedAt Datetime True

The datetime when the item was created

DisplayedAs String False

The name of the resource

JournalLineAggregate String False

The journal lines

TransactionTypeId String False

TransactionTypes.Id

The unique identifier for the item

TransactionId String False

Transactions.Id

The unique identifier for the item

Date Datetime True

The date of the journal

Reference String False

A reference for the journal

Migrated Bool False

Indicates if the journal was migrated from another system.

UpdatedAt Datetime True

The datetime when the item was last updated

LegacyId Int False

The legacy ID for the item

DeletedAt Datetime True

The datetime when the item was deleted

Total String False

The total for the journal

Description String False

A description of the journal

JournalCodeId String False

JournalCodes.Id

The unique identifier for the item

DeletedSince Datetime True

LedgerAccountOpeningBalances

Query LedgerAccountOpeningBalances in Sage Business Cloud Accounting.

Table Specific Information

Query the available LedgerAccountOpeningBalances

Select

The driver uses the Sage Accounting API to process search criteria that refer to UpdatedOrCreatedSince column. The driver processes other filters client-side within the driver.

  • UpdatedOrCreatedSince supports the '=' operator.

For example, the following queries are processed server side:

SELECT * FROM LedgerAccountOpeningBalances WHERE UpdatedOrCreatedSince='2018-12-15'

Update

Updates are performed based on Id.

UPDATE LedgerAccountOpeningBalances SET Details='abc' WHERE Id='123'

Delete

You must specify the ID of the LedgerAccountOpeningBalances to delete it.

DELETE FROM LedgerAccountOpeningBalances WHERE ID ='123' 

Columns

Name Type ReadOnly References Description
Id [KEY] String False

The unique identifier for the item

CreatedAt Datetime True

The datetime when the item was created

Details String False

A description of the opening balance

DisplayedAs String False

The name of the resource

Credit String False

The credit amount of the opening balance

LedgerAccountId String False

LedgerAccounts.Id

The unique identifier for the item

UpdatedAt Datetime True

The datetime when the item was last updated

LegacyId Int False

The legacy ID for the item

Debit String False

The debit amount of the opening balance

LedgerAccounts

Query LedgerAccounts in Sage Business Cloud Accounting.

Table Specific Information

Query the available LedgerAccounts

Select

The driver uses the Sage Accounting API to process search criteria that refer to LedgerAccountClassificationId,LedgerAccountTypeId,UpdatedOrCreatedSince,VisibleIn,NotVisibleIn,ShowIncludedInChart,ShowControlAccounts,ShowBalanceDetails,ExcludeDeletedEntries,FromDate,ToDate,Search columns. The driver processes other filters client-side within the driver.

  • LedgerAccountClassificationId supports the '=' operator.
  • LedgerAccountTypeId supports the '=' operator.
  • UpdatedOrCreatedSince supports the '=' operator.
  • VisibleIn supports the '=' operator.
  • NotVisibleIn supports the '=' operator.
  • ShowIncludedInChart supports the '=' operator.
  • ShowControlAccounts supports the '=' operator.
  • ShowBalanceDetails supports the '=' operator.
  • ExcludeDeletedEntries supports the '=' operator.
  • FromDate supports the '=' operator.
  • ToDate supports the '=' operator.
  • Search supports the '=' operator.

For example, the following queries are processed server side:

SELECT * FROM LedgerAccounts WHERE LedgerAccountClassificationId='abc'
SELECT * FROM LedgerAccounts WHERE LedgerAccountTypeId='SALES'
SELECT * FROM LedgerAccounts WHERE UpdatedOrCreatedSince='2019-10-02T11:46:53Z'
SELECT * FROM LedgerAccounts WHERE VisibleIn='Expenses'
SELECT * FROM LedgerAccounts WHERE NotVisibleIn='Journals'
SELECT * FROM LedgerAccounts WHERE ShowIncludedInChart='true'
SELECT * FROM LedgerAccounts WHERE ShowControlAccounts='false'
SELECT * FROM LedgerAccounts WHERE ShowBalanceDetails='true'
SELECT * FROM LedgerAccounts WHERE ExcludeDeletedEntries='true'
SELECT * FROM LedgerAccounts WHERE FromDate='2018-12-15'
SELECT * FROM LedgerAccounts WHERE ToDate='2018-12-15'
SELECT * FROM LedgerAccounts WHERE Search='abc'

Update

Updates are performed based on Id.

UPDATE LedgerAccounts SET TaxRateId='abc' WHERE Id='123'

Columns

Name Type ReadOnly References Description
Id [KEY] String False

The unique identifier for the item

TaxRateId String False

TaxRates.Id

The unique identifier for the item

CreatedAt Datetime True

The datetime when the item was created

VisibleInJournals Bool False

Indicates whether the ledger account is displayed in this area of the application

LedgerAccountClassificationId String False

LedgerAccountClassifications.Id

The unique identifier for the item

LedgerAccountGroupDisplayedAs String False

The name of the resource

LedgerAccountGroupLegacyId Int False

The legacy ID for the item

LedgerAccountGroupId String False

The unique identifier for the item

VisibleInOtherPayments Bool False

Indicates whether the ledger account is displayed in this area of the application

BalanceDetailsToDate String False

The to date filter

BalanceDetailsCreditOrDebit String False

Is the balance a debit or credit

BalanceDetailsCredits String False

The credit balance

BalanceDetailsBalance String False

The account balance

BalanceDetailsFromDate String False

The from date filter

BalanceDetailsDebits String False

The debit balance

VisibleScopes String False

The visible scopes.

VisibleInSales Bool False

Indicates whether the ledger account is displayed in this area of the application

IncludedInChart Bool False

Indicates whether the ledger account is included in the chart of accounts

VisibleInOtherReceipts Bool False

Indicates whether the ledger account is displayed in this area of the application

DisplayFormatted String False

The display name formatted based on coa_list_order in settings

VisibleInReporting Bool False

Indicates whether the ledger account is displayed in this area of the application

DisplayedAs String False

The name of the resource

ControlName String False

The control name for the ledger account

VisibleInBanking Bool False

Indicates whether the ledger account is displayed in this area of the application

LedgerAccountTypeId String False

The unique identifier for the item

The allowed values are SALES, OTHER_INCOME, DIRECT_EXPENSES, OVERHEADS, DEPRECIATION, CURRENT_ASSETS, FIXED_ASSETS, FUTURE_ASSETS, BANK, CURRENT_LIABILITY, FUTURE_LIABILITY, v, LINE_OF_CREDIT.

NominalCode Int False

The nominal code of the ledger account

LegacyId Int False

The legacy ID for the item

FixedTaxRate Bool False

Indicates whether the default tax rate is fixed or may be changed

IsControlAccount Bool False

Indicates whether the ledger account is a control account

Name String False

The name for the ledger account. Changes to this field do not propagate to other resources, namely not to the name of the associated bank_account (unlike the behaviour of the UI).

UpdatedAt Datetime True

The datetime when the item was last updated

DisplayName String False

The display name for the ledger account

VisibleInExpenses Bool False

Indicates whether the ledger account is displayed in this area of the application

VisibleIn String False
NotVisibleIn String False
ShowIncludedInChart Bool False
ShowControlAccounts Bool False
ShowBalanceDetails Bool False
ExcludeDeletedEntries Bool False
FromDate Datetime True
ToDate Datetime True
Search String True

MigrationTaxReturns

Query MigrationTaxReturns in Sage Business Cloud Accounting.

Table Specific Information

Query the available MigrationTaxReturns

Select

The driver processes all filters client-side within the driver.

Columns

Name Type ReadOnly References Description
Id [KEY] String False

The unique identifier for the item

CreatedAt Datetime True

The datetime when the item was created

IeBoxT4 String False

The value of box T4

IeBoxT3 String False

The value of box T3

IeBoxT2 String False

The value of box T2

IeBoxT1 String False

The value of box T1

IeBoxES2 String False

The value of box ES2

IeBoxES1 String False

The value of box ES1

IeBoxE2 String False

The value of box E2

IeBoxE1 String False

The value of box E1

DisplayedAs String False

The name of the resource

FromDate Datetime True

The start date of the tax return

LegacyId Int False

The legacy ID for the item

TaxReturnFrequencyId String False

TaxReturnFrequencies.Id

The unique identifier for the item

UpdatedAt Datetime True

The datetime when the item was last updated

GbBox9 String False

The value of box 9

GbBox8 String False

The value of box 8

GbBox7 String False

The value of box 7

GbBox6 String False

The value of box 6

GbBox5 String False

The value of box 5

GbBox4 String False

The value of box 4

GbBox3 String False

The value of box 3

GbBox2 String False

The value of box 2

GbBox1 String False

The value of box 1

TotalAmount String False

The total of the tax return

ToDate Datetime True

The end date of the tax return

OpeningBalanceJournals

Query OpeningBalanceJournals in Sage Business Cloud Accounting.

Table Specific Information

Query the available OpeningBalanceJournals

Select

The driver processes all filters client-side within the driver.

Insert

  • Insert a journal with aggregate value.
    INSERT INTO  [OpeningBalanceJournals] ( BaseJournalLineAggregate, date, reference) VALUES ('{"ledger_account_id": "1fcc2765741311ea8d3c022ace1a91e2","debit": 12,"credit": 0,"details": "details","tax_reconciled": true,"cleared": true,"bank_reconciled": true},{"ledger_account_id": "1fcc2765741311ea8d3c022ace1a91e2","debit": 0,"credit": 12,"details": "details","tax_reconciled": true,"cleared": true,"bank_reconciled": true}','2019-07-12','Journal Reference')
  • Insert a journal using temp tables.
    INSERT INTO  [JournalLineItemsAggregate#Temp] (credit, debit, details, ledgerAccountId) VALUES ('10', '0', 'details1', '6035f3d39d7511e9a2b70e070fead9d0')
    INSERT INTO  [JournalLineItemsAggregate#Temp] (credit, debit, details, ledgerAccountId) VALUES ('0', '10', 'details2', '6035f3d39d7511e9a2b70e070fead9d0')
    INSERT INTO  [OpeningBalanceJournals] (BaseJournalLineAggregate, date, reference) VALUES ('JournalLineItemsAggregate#Temp','2019-07-12','Reference2')

Delete

You must specify the ID of the OpeningBalanceJournals to delete it.

DELETE FROM OpeningBalanceJournals WHERE ID ='123' 

Columns

Name Type ReadOnly References Description
Id [KEY] String False

The unique identifier for the item

CreatedAt Datetime True

The datetime when the item was created

DisplayedAs String False

The name of the resource

BaseJournalLineAggregate String False

The journal lines

TransactionTypeId String False

TransactionTypes.Id

The unique identifier for the item

TransactionId String False

Transactions.Id

The unique identifier for the item

Date Datetime True

The date of the opening balance journal

Reference String False

A reference for the opening balance journal

UpdatedAt Datetime True

The datetime when the item was last updated

LegacyId Int False

The legacy ID for the item

DeletedAt Datetime True

The datetime when the item was deleted

OtherPayments

Query OtherPayments in Sage Business Cloud Accounting.

Table Specific Information

Query the available OtherPayments

Select

The driver uses the Sage Accounting API to process search criteria that refer to TransactionTypeId,BankAccountId,ContactId,DeletedSince,FromDate,HasAttachments,ToDate,UpdatedOrCreatedSince columns. The driver processes other filters client-side within the driver.

  • TransactionTypeId supports the '=' operator.
  • BankAccountId supports the '=' operator.
  • ContactId supports the '=' operator.
  • DeletedSince supports the '=' operator.
  • FromDate supports the '=' operator.
  • HasAttachments supports the '=' operator.
  • ToDate supports the '=' operator.
  • UpdatedOrCreatedSince supports the '=' operator.

For example, the following queries are processed server side:

SELECT * FROM OtherPayments WHERE TransactionTypeId='abc'
SELECT * FROM OtherPayments WHERE BankAccountId='abc'
SELECT * FROM OtherPayments WHERE ContactId='abc'
SELECT * FROM OtherPayments WHERE DeletedSince='2018-12-15'
SELECT * FROM OtherPayments WHERE FromDate='2018-12-15'
SELECT * FROM OtherPayments WHERE HasAttachments='abc'
SELECT * FROM OtherPayments WHERE ToDate='2018-12-15'
SELECT * FROM OtherPayments WHERE UpdatedOrCreatedSince='2018-12-15'

Update

Updates are performed based on Id.

UPDATE OtherPayments SET TransactionTypeId='abc' WHERE Id='123'

Delete

You must specify the ID of the OtherPayments to delete it.

DELETE FROM OtherPayments WHERE ID ='123' 

Columns

Name Type ReadOnly References Description
Id [KEY] String False

The unique identifier for the item

CreatedAt Datetime True

The datetime when the item was created

TransactionTypeDisplayedAs String False

The name of the resource

TransactionTypeLegacyId Int False

The legacy ID for the item

TransactionTypeId String False

The unique identifier for the item

NetAmount String False

The net amount of the payment

PaymentMethodId String False

PaymentMethods.Id

The unique identifier for the item

Deletable Bool False

Indicates whether or not the payment can be deleted

Reference String False

A reference of the payment

TotalAmount String False

The total amount of the payment

TaxAddressRegionDisplayedAs String False

The name of the resource

TaxAddressRegionLegacyId Int False

The legacy ID for the item

TaxAddressRegionId String False

The unique identifier for the item

WithholdingTaxRate String False

IRPF withheld tax rate

DeletedAt Datetime True

The datetime when the item was deleted

BankAccountId String False

The unique identifier for the item

Editable Bool False

Indicates whether or not the payment can be edited

DisplayedAs String False

The name of the resource

TaxAmount String False

The tax amount of the payment

ContactId String False

Contacts.Id

The unique identifier for the item

TransactionId String False

Transactions.Id

The unique identifier for the item

LegacyId Int False

The legacy ID for the item

Date Datetime True

The date of the payment

WithholdingTaxAmount String False

IRPF withheld tax amount

OtherPaymentLineItemAggregate String False

The payment lines of the payment

UpdatedAt Datetime True

The datetime when the item was last updated

DeletedSince Datetime True
FromDate Datetime True
HasAttachments String False
ToDate Datetime True

Products

Query Products in Sage Business Cloud Accounting.

Table Specific Information

Query the available Products

Select

The driver uses the Sage Accounting API to process search criteria that refer to Active,Search,UpdatedOrCreatedSince,DeletedSince columns. The driver processes other filters client-side within the driver.

  • Active supports the '=' operator.
  • Search supports the '=' operator.
  • UpdatedOrCreatedSince supports the '=' operator.
  • DeletedSince supports the '=' operator.

For example, the following queries are processed server side:

SELECT * FROM Products WHERE Active=false
SELECT * FROM Products WHERE Search='abc'
SELECT * FROM Products WHERE UpdatedOrCreatedSince='2018-12-15'
SELECT * FROM Products WHERE DeletedSince='2018-12-15'

Insert

Create a simple product.

INSERT INTO  [Products] (SalesLedgerAccountId, CostPrice, ItemCode) VALUES ('1', '1', '1')

Update

Updates are performed based on Id.

UPDATE Products SET SalesLedgerAccountId='abc' WHERE Id='123'

Delete

You must specify the ID of the Products to delete it.

DELETE FROM Products WHERE ID ='123' 

Columns

Name Type ReadOnly References Description
Id [KEY] String False

The unique identifier for the item

SalesLedgerAccountId String False

LedgerAccounts.Id

The unique identifier for the item

CreatedAt Datetime True

The datetime when the item was created

SalesTaxRateId String False

TaxRates.Id

The unique identifier for the item

Active Bool False

Indicates whether the product is active

ItemCode String False

The item code for the product

PurchaseLedgerAccountId String False

LedgerAccounts.Id

The unique identifier for the item

SalesPricesAggregate String False

The sales prices for the product

DeletedAt Datetime True

The datetime when the item was deleted

PurchaseTaxRateId String False

TaxRates.Id

The unique identifier for the item

Notes String False

The notes for the product

CostPrice String False

The cost price of the product

UsualSupplierId String False

Contacts.Id

The unique identifier for the item

DisplayedAs String False

The name of the resource

Description String False

The product description

PurchaseDescription String False

The product purchase description

LegacyId Int False

The legacy ID for the item

SourceGuid String False

Used when importing products from external sources

UpdatedAt Datetime True

The datetime when the item was last updated

Search String False
DeletedSince Datetime True

ProductSalesPriceTypes

Query ProductSalesPriceTypes in Sage Business Cloud Accounting.

Table Specific Information

Query the available ProductSalesPriceTypes

Select

The driver uses the Sage Accounting API to process search criteria that refer to Active column. The driver processes other filters client-side within the driver.

  • Active supports the '=' operator.

For example, the following queries are processed server side:

SELECT * FROM ProductSalesPriceTypes WHERE Active=false

Update

Updates are performed based on Id.

UPDATE ProductSalesPriceTypes SET Name='Selling Price' WHERE Id='c9ec71a1fe5c4916a72a9efc22324663'

Delete

You must specify the ID of the ProductSalesPriceTypes to delete it.

DELETE FROM ProductSalesPriceTypes WHERE ID ='c9ec71a1fe5c4916a72a9efc22324663' 

Columns

Name Type ReadOnly References Description
Id [KEY] String False

The unique identifier for the item

UpdatedAt Datetime True

The datetime when the item was last updated

Name String False

The name of the product sales price type

DisplayedAs String False

The name of the resource

CreatedAt Datetime True

The datetime when the item was created

Active Bool False

Indicates whether the price type is displayed in the application

LegacyId Int False

The legacy ID for the item

PurchaseCreditNotes

Query PurchaseCreditNotes in Sage Business Cloud Accounting.

Table Specific Information

Query the available PurchaseCreditNotes

Select

The driver uses the Sage Accounting API to process search criteria that refer to ContactId,StatusId,ShowPaymentsAllocations,Search,FromDate,ToDate,UpdatedOrCreatedSince,DeletedSince,HasAttachments,ShowPaymentsAllocations columns. The driver processes other filters client-side within the driver.

  • ContactId supports the '=' operator.
  • StatusId supports the '=' operator.
  • ShowPaymentsAllocations supports the '=' operator.
  • Search supports the '=' operator.
  • FromDate supports the '=' operator.
  • ToDate supports the '=' operator.
  • UpdatedOrCreatedSince supports the '=' operator.
  • DeletedSince supports the '=' operator.
  • HasAttachments supports the '=' operator.
  • ShowPaymentsAllocations supports the '=' operator.

For example, the following queries are processed server side:

SELECT * FROM PurchaseCreditNotes WHERE ContactId='abc'
SELECT * FROM PurchaseCreditNotes WHERE StatusId='abc'
SELECT * FROM PurchaseCreditNotes WHERE ShowPaymentsAllocations='abc'
SELECT * FROM PurchaseCreditNotes WHERE Search='abc'
SELECT * FROM PurchaseCreditNotes WHERE FromDate='2018-12-15'
SELECT * FROM PurchaseCreditNotes WHERE ToDate='2018-12-15'
SELECT * FROM PurchaseCreditNotes WHERE UpdatedOrCreatedSince='2018-12-15'
SELECT * FROM PurchaseCreditNotes WHERE DeletedSince='2018-12-15'
SELECT * FROM PurchaseCreditNotes WHERE HasAttachments='abc'
SELECT * FROM PurchaseCreditNotes WHERE ShowPaymentsAllocations='abc'

Insert

Create a simple purchase credit note.

INSERT INTO  [PurchaseCreditNotes] (ContactName, TotalQuantity, DisplayedAs) VALUES ('test', '1', 'test')

Update

Updates are performed based on Id.

UPDATE PurchaseCreditNotes SET Reference='abc' WHERE Id='123'

Delete

You must specify the ID of the PurchaseCreditNotes to delete it.

DELETE FROM PurchaseCreditNotes WHERE ID ='123' 

Columns

Name Type ReadOnly References Description
Id [KEY] String False

The unique identifier for the item

DisplayedAs String False

The name of the resource

Reference String False

The reference for the credit note

PurchaseCreditNoteLineItemAggregate String False

The credit note lines of the credit note

BaseCurrencyTotalAmount String False

The total amount of the credit note in base currency

BaseCurrencyNetAmount String False

The net amount of the credit note in base currency

ContactName String False

The name of the contact when the credit note was created

Migrated Bool False

Indicates if the purchase credit note was migrated from another system.

TaxAmount String False

The tax amount of the credit note

LastPaid String False

The date of the last payment

PaymentsAllocationsTotalDiscount String False

The total discount of all payments and allocations

TotalAmount String False

The total amount of the credit note

ExchangeRate String False

The exchange rate for the credit note

OutstandingAmount String False

The outstanding amount of the credit note

ContactId String False

Contacts.Id

The unique identifier for the item

NetAmount String False

The net amount of the credit note

ContactReference String False

The reference of the contact when the credit note was created

TaxAddressRegionDisplayedAs String False

The name of the resource

TaxAddressRegionLegacyId Int False

The legacy ID for the item

TaxAddressRegionId String False

The unique identifier for the item

TaxCalculationMethod String False

The tax calculation method, if applicable, for this purchase credit note, returns invoice or cash.

PaymentsAllocationsAggregate String False

The associated payments and allocations

UpdatedAt Datetime True

The datetime when the item was last updated

LegacyId Int False

The legacy ID for the item

TaxAnalysisAggregate String False

The credit note tax analysis

InverseExchangeRate String False

The inverse exchange rate for the credit note

TotalQuantity String False

The total quantity of the credit note

TransactionId String False

Transactions.Id

The unique identifier for the item

StatusDisplayedAs String False

The name of the resource

StatusLegacyId Int False

The legacy ID for the item

StatusId String False

The unique identifier for the item

DetailedTaxAnalysisBaseCurrencyTotalGoodsAmount String False

The total base currency amount relating to goods for all tax rates

DetailedTaxAnalysisBaseCurrencyTotalTax String False

The base currency total tax amount for all tax rates

DetailedTaxAnalysisTotalRetailerTax String False

The total retailer tax amount for all tax rates

DetailedTaxAnalysisBaseCurrencyTotal String False

The total base currency amount for all tax rates

DetailedTaxAnalysisTotalGoodsAmount String False

The total amount relating to goods for all tax rates

DetailedTaxAnalysisTotalNet String False

The total net amount for all tax rates

DetailedTaxAnalysisBaseCurrencyTotalServicesAmount String False

The total base currency amount relating to services for all tax rates

DetailedTaxAnalysisBaseCurrencyTotalNet String False

The base currency total net amount for all tax rates

DetailedTaxAnalysisTotalServicesAmount String False

The total amount relating to services for all tax rates

DetailedTaxAnalysisTotal String False

The total amount for all tax rates

DetailedTaxAnalysisTotalTax String False

The total tax amount for all tax rates

TransactionTypeId String False

Transactions.Id

The unique identifier for the item

CreatedAt Datetime True

The datetime when the item was created

PaymentsAllocationsTotalAmount String False

The total amount of all payments and allocations

VoidReason String False

The reason the credit note was voided

TaxReconciled Bool False

Indicates if the purchase credit note is tax reconciled or not.

Editable Bool False

Indicates whether artefact can be edited

VendorReference String False

The vendor reference for the credit note

CurrencyId String False

Currencies.Id

The unique identifier for the item

Notes String False

credit note notes

TotalPaid String False

The total paid amount of the credit note including any payments, allocations and discounts

DeletedAt Datetime True

The datetime when the item was deleted

BaseCurrencyTaxAmount String False

The tax amount of the credit note in base currency

Date Datetime True

The date of the credit note

BaseCurrencyOutstandingAmount String False

The outstanding amount of the credit note in base currency

ShowPaymentsAllocations String False
Search String False
FromDate Datetime True
ToDate Datetime True
DeletedSince Datetime True
HasAttachments String False
ShowPaymentsAllocations String True

PurchaseInvoices

Query PurchaseInvoices in Sage Business Cloud Accounting.

Table Specific Information

Query the available PurchaseInvoices

Select

The driver uses the Sage Accounting API to process search criteria that refer to ContactId,StatusId,ShowPaymentsAllocations,ShowCorrections,ShowPaymentsAllocations,Search,FromDate,ToDate,UpdatedOrCreatedSince,DeletedSince,HasAttachments columns. The driver processes other filters client-side within the driver.

  • ContactId supports the '=' operator.
  • StatusId supports the '=' operator.
  • ShowPaymentsAllocations supports the '=' operator.
  • ShowCorrections supports the '=' operator.
  • ShowPaymentsAllocations supports the '=' operator.
  • Search supports the '=' operator.
  • FromDate supports the '=' operator.
  • ToDate supports the '=' operator.
  • UpdatedOrCreatedSince supports the '=' operator.
  • DeletedSince supports the '=' operator.
  • HasAttachments supports the '=' operator.

For example, the following queries are processed server side:

SELECT * FROM PurchaseInvoices WHERE ContactId='abc'
SELECT * FROM PurchaseInvoices WHERE StatusId='abc'
SELECT * FROM PurchaseInvoices WHERE ShowPaymentsAllocations='abc'
SELECT * FROM PurchaseInvoices WHERE ShowCorrections='abc'
SELECT * FROM PurchaseInvoices WHERE ShowPaymentsAllocations='abc'
SELECT * FROM PurchaseInvoices WHERE Search='abc'
SELECT * FROM PurchaseInvoices WHERE FromDate='2018-12-15'
SELECT * FROM PurchaseInvoices WHERE ToDate='2018-12-15'
SELECT * FROM PurchaseInvoices WHERE UpdatedOrCreatedSince='2018-12-15'
SELECT * FROM PurchaseInvoices WHERE DeletedSince='2018-12-15'
SELECT * FROM PurchaseInvoices WHERE HasAttachments='abc'

Insert

Create a simple purchase invoice.

INSERT INTO  [PurchaseInvoices] (ContactName, TotalQuantity, DisplayedAs) VALUES ('test', '1', 'test')

Update

Updates are performed based on Id.

UPDATE PurchaseInvoices SET Reference='abc' WHERE Id='123'

Delete

You must specify the ID of the PurchaseInvoices to delete it.

DELETE FROM PurchaseInvoices WHERE ID ='123' 

Columns

Name Type ReadOnly References Description
Id [KEY] String False

The unique identifier for the item

DisplayedAs String False

The name of the resource

Reference String False

The reference for the invoice

BaseCurrencyTotalAmount String False

The total amount of the invoice in base currency

BaseCurrencyNetAmount String False

The net amount of the invoice in base currency

ContactName String False

The name of the contact when the invoice was created

Migrated Bool False

Indicates if the purchase invoice was migrated from another system.

DueDate Datetime True

The due date of the invoice

TaxAmount String False

The tax amount of the invoice

LastPaid String False

The date of the last payment

PaymentsAllocationsTotalDiscount String False

The total discount of all payments and allocations

WithholdingTaxRate String False

IRPF withheld Tax Rate (Spain only)

TotalAmount String False

The total amount of the invoice

ExchangeRate String False

The exchange rate for the invoice

OutstandingAmount String False

The outstanding amount of the invoice

ContactId String False

Contacts.Id

The unique identifier for the item

NetAmount String False

The net amount of the invoice

ContactReference String False

The reference of the contact when the invoice was created

TaxAddressRegionDisplayedAs String False

The name of the resource

TaxAddressRegionLegacyId Int False

The legacy ID for the item

TaxAddressRegionId String False

The unique identifier for the item

TaxCalculationMethod String False

The tax calculation method, if applicable, for this purchase invoice, returns invoice or cash.

PaymentsAllocationsAggregate String False

The associated payments and allocations

BaseCurrencyWithholdingTaxAmount String False

IRPF withheld Tax Amount (Spain only) in the base currency

WithholdingTaxAmount String False

IRPF withheld Tax Amount (Spain only)

UpdatedAt Datetime True

The datetime when the item was last updated

LegacyId Int False

The legacy ID for the item

TaxAnalysisAggregate String False

The invoice tax analysis (Optional for Spain, restricted for all other regions)

PurchaseCorrectiveInvoiceAggregate String False

The corrective entries associated with the invoice

InverseExchangeRate String False

The inverse exchange rate for the invoice

TotalQuantity String False

The total quantity of the invoice

TransactionId String False

Transactions.Id

The unique identifier for the item

StatusDisplayedAs String False

The name of the resource

StatusLegacyId Int False

The legacy ID for the item

StatusId String False

The unique identifier for the item

DetailedTaxAnalysisBaseCurrencyTotalGoodsAmount String False

The total base currency amount relating to goods for all tax rates

DetailedTaxAnalysisBaseCurrencyTotalTax String False

The base currency total tax amount for all tax rates

DetailedTaxAnalysisTotalRetailerTax String False

The total retailer tax amount for all tax rates

DetailedTaxAnalysisBaseCurrencyTotal String False

The total base currency amount for all tax rates

DetailedTaxAnalysisTotalGoodsAmount String False

The total amount relating to goods for all tax rates

DetailedTaxAnalysisTotalNet String False

The total net amount for all tax rates

DetailedTaxAnalysisBaseCurrencyTotalServicesAmount String False

The total base currency amount relating to services for all tax rates

DetailedTaxAnalysisBaseCurrencyTotalNet String False

The base currency total net amount for all tax rates

DetailedTaxAnalysisTotalServicesAmount String False

The total amount relating to services for all tax rates

DetailedTaxAnalysisTotal String False

The total amount for all tax rates

DetailedTaxAnalysisTotalTax String False

The total tax amount for all tax rates

TransactionTypeId String False

TransactionTypes.Id

The unique identifier for the item

CreatedAt Datetime True

The datetime when the item was created

PaymentsAllocationsTotalAmount String False

The total amount of all payments and allocations

VoidReason String False

The reason the invoice was voided

TaxReconciled Bool False

Indicates if the purchase invoice is tax reconciled or not.

Editable Bool False

Indicates whether artefact can be edited

VendorReference String False

The vendor reference for the invoice

CurrencyId String False

Currencies.Id

The unique identifier for the item

PurchaseInvoiceLineItemAggregate String False

The invoice lines of the invoice

Notes String False

Invoice notes

TotalPaid String False

The total paid amount of the invoice including any payments, allocations and discounts

DeletedAt Datetime True

The datetime when the item was deleted

BaseCurrencyTaxAmount String False

The tax amount of the invoice in base currency

Date String False

The date of the invoice

BaseCurrencyOutstandingAmount String False

The outstanding amount of the invoice in base currency

ShowPaymentsAllocations String False
ShowCorrections String False
ShowPaymentsAllocations String False
Search String False
FromDate Datetime True
ToDate Datetime True
DeletedSince Datetime True
HasAttachments String True
VatExemptConsignment Bool True

indicate when consignment values are under £135 and you have agreed with your supplier that you will account for VAT on your VAT return using Reverse Charge.

IsCIS Bool True

Determines if this is a CIS transaction.

CISApplicableAmount String True
BaseCurrencyCISApplicableAmount String True
TotalAfterCISDeduction String True
BaseCurrencyTotalAfterCISDeduction String True
HasCISLabour Bool True
HasCISMaterials Bool True

PurchaseQuickEntries

Query PurchaseQuickEntries in Sage Business Cloud Accounting.

Table Specific Information

Query the available PurchaseQuickEntries

Select

The driver uses the Sage Accounting API to process search criteria that refer to ContactId,StatusId,ShowPaymentsAllocations,Search,FromDate,ToDate,UpdatedOrCreatedSince,DeletedSince,ShowPaymentsAllocations,HasAttachments columns. The driver processes other filters client-side within the driver.

  • ContactId supports the '=' operator.
  • StatusId supports the '=' operator.
  • ShowPaymentsAllocations supports the '=' operator.
  • Search supports the '=' operator.
  • FromDate supports the '=' operator.
  • ToDate supports the '=' operator.
  • UpdatedOrCreatedSince supports the '=' operator.
  • DeletedSince supports the '=' operator.
  • ShowPaymentsAllocations supports the '=' operator.
  • HasAttachments supports the '=' operator.

For example, the following queries are processed server side:

SELECT * FROM PurchaseQuickEntries WHERE ContactId='abc'
SELECT * FROM PurchaseQuickEntries WHERE StatusId='abc'
SELECT * FROM PurchaseQuickEntries WHERE ShowPaymentsAllocations='abc'
SELECT * FROM PurchaseQuickEntries WHERE Search='abc'
SELECT * FROM PurchaseQuickEntries WHERE FromDate='abc'
SELECT * FROM PurchaseQuickEntries WHERE ToDate='abc'
SELECT * FROM PurchaseQuickEntries WHERE UpdatedOrCreatedSince='2018-12-15'
SELECT * FROM PurchaseQuickEntries WHERE DeletedSince='2018-12-15'
SELECT * FROM PurchaseQuickEntries WHERE ShowPaymentsAllocations='abc'
SELECT * FROM PurchaseQuickEntries WHERE HasAttachments='abc'

Insert

Create a simple purchase quick entry.

INSERT INTO  [PurchaseQuickEntries] (ContactName, TotalAmount, DisplayedAs) VALUES ('test', '1', 'test')

Update

Updates are performed based on Id.

UPDATE PurchaseQuickEntries SET Reference='abc' WHERE Id='123'

Delete

You must specify the ID of the PurchaseQuickEntries to delete it.

DELETE FROM PurchaseQuickEntries WHERE ID ='123' 

Columns

Name Type ReadOnly References Description
Id [KEY] String False

The unique identifier for the item

DisplayedAs String False

The name of the resource

Reference String False

The reference for the quick entry

BaseCurrencyTotalAmount String False

The total amount of the quick entry in base currency

TradeOfAsset Bool False

Whether the quick entry is marked as trade of asset.

CaseCurrencyTaxBreakdownAggregate String False

The tax breakdown for the quick entry in base currency

BaseCurrencyNetAmount String False

The net amount of the quick entry in base currency

ContactName String False

The name of the contact when the quick entry was created

Migrated Bool False

Indicates if the quick entry was migrated from another system.

TaxAmount String False

The tax amount of the quick entry

TotalAmount String False

The total amount of the quick entry

ExchangeRate String False

The exchange rate for the quick entry

OutstandingAmount String False

The outstanding amount of the quick entry

ContactDisplayedAs String False

The name of the resource

ContactLegacyId Int False

The legacy ID for the item

ContactId String False

The unique identifier for the item

NetAmount String False

The net amount of the quick entry

ContactReference String False

The reference of the contact when the quick entry was created

TaxAddressRegionDisplayedAs String False

The name of the resource

TaxAddressRegionLegacyId Int False

The legacy ID for the item

TaxAddressRegionId String False

The unique identifier for the item

PaymentsAllocationsAggregate String False

The associated payments and allocations

UpdatedAt Datetime True

The datetime when the item was last updated

LegacyId Int False

The legacy ID for the item

LedgerAccountId String False

LedgerAccounts.Id

The unique identifier for the item

InverseExchangeRate String False

The inverse exchange rate for the quick entry

TransactionId String False

Transactions.Id

The unique identifier for the item

StatusDisplayedAs String False

The name of the resource

StatusLegacyId Int False

The legacy ID for the item

StatusId String False

The unique identifier for the item

TaxBreakdownAggregate String False

The tax breakdown for the quick entry

CreatedAt Datetime True

The datetime when the item was created

PstAmount String False

The pst or qst tax amount for the purchase quick entry

Details String False

A description of the quick entry

GstAmount String False

The gst or hst tax amount for the purchase quick entry

CurrencyId String False

Currencies.Id

The unique identifier for the item

DeletedAt Datetime True

The datetime when the item was deleted

TaxRateId String False

TaxRates.Id

The unique identifier for the item

Date String False

The date of the quick entry

BaseCurrencyTaxAmount String False

The tax amount of the quick entry in base currency

QuickEntryTypeDisplayedAs String False

The name of the resource

QuickEntryTypeLegacyId Int False

The legacy ID for the item

QuickEntryTypeId String False

The unique identifier for the item

BaseCurrencyOutstandingAmount String False

The outstanding amount of the quick entry in base currency

ShowPaymentsAllocations String False
Search String False
FromDate String True
ToDate String True
DeletedSince Datetime True
ShowPaymentsAllocations String True
HasAttachments String True

SalesCreditNotes

Query SalesCreditNotes in Sage Business Cloud Accounting.

Table Specific Information

Query the available SalesCreditNotes

Select

The driver uses the Sage Accounting API to process search criteria that refer to ContactId,StatusId,ShowPaymentsAllocations,MarkAsSent,ShowPaymentsAllocations,Search,FromDate,ToDate,UpdatedOrCreatedSince,DeletedSince,HasAttachments columns. The driver processes other filters client-side within the driver.

  • ContactId supports the '=' operator.
  • StatusId supports the '=' operator.
  • ShowPaymentsAllocations supports the '=' operator.
  • MarkAsSent supports the '=' operator.
  • ShowPaymentsAllocations supports the '=' operator.
  • Search supports the '=' operator.
  • FromDate supports the '=' operator.
  • ToDate supports the '=' operator.
  • UpdatedOrCreatedSince supports the '=' operator.
  • DeletedSince supports the '=' operator.
  • HasAttachments supports the '=' operator.

For example, the following queries are processed server side:

SELECT * FROM SalesCreditNotes WHERE ContactId='abc'
SELECT * FROM SalesCreditNotes WHERE StatusId='abc'
SELECT * FROM SalesCreditNotes WHERE ShowPaymentsAllocations='abc'
SELECT * FROM SalesCreditNotes WHERE MarkAsSent='abc'
SELECT * FROM SalesCreditNotes WHERE ShowPaymentsAllocations='abc'
SELECT * FROM SalesCreditNotes WHERE Search='abc'
SELECT * FROM SalesCreditNotes WHERE FromDate='2018-12-15'
SELECT * FROM SalesCreditNotes WHERE ToDate='2018-12-15'
SELECT * FROM SalesCreditNotes WHERE UpdatedOrCreatedSince='2018-12-15'
SELECT * FROM SalesCreditNotes WHERE DeletedSince='2018-12-15'
SELECT * FROM SalesCreditNotes WHERE HasAttachments='abc'

Insert

Create a simple sales credit note.

INSERT INTO  [SalesCreditNotes] (ContactName, TotalQuantity, DisplayedAs) VALUES ('test', '1', 'test')

Update

Updates are performed based on Id.

UPDATE SalesCreditNotes SET Reference='abc' WHERE Id='123'

Delete

You must specify the ID of the SalesCreditNotes to delete it.

DELETE FROM SalesCreditNotes WHERE ID ='123' 

Columns

Name Type ReadOnly References Description
Id [KEY] String False

The unique identifier for the item

DisplayedAs String False

The name of the resource

Reference String False

The reference for the credit note

SalesCreditNoteLineItemAggregate String False

The credit note lines of the credit note

BaseCurrencyTotalAmount String False

The total amount of the credit note in base currency

MainAddressId String False

Addresses.Id

The unique identifier for the item

BaseCurrencyNetAmount String False

The net amount of the credit note in base currency

BaseCurrencyShippingTaxBreakdownAggregate String False

The shipping tax breakdown for the credit note in base currency

ContactName String False

The name of the contact when the credit note was created

SentByEmail Bool False

Indicates whether the credit note has been emailed

ShippingTaxBreakdownAggregate String False

The shipping tax breakdown for the credit note

Migrated Bool False

Indicates if the sales credit note was migrated from another system.

TotalDiscountAmount String False

The discount amount on the credit note

TaxAmount String False

The tax amount of the credit note

LastPaid String False

The date of the last payment

CreditNoteNumber String False

The generated credit note number

PaymentsAllocationsTotalDiscount String False

The total discount of all payments and allocations

TotalAmount String False

The total amount of the credit note

ExchangeRate String False

The exchange rate for the credit note

OutstandingAmount String False

The outstanding amount of the credit note

ContactId String False

Contacts.Id

The unique identifier for the item

NetAmount String False

The net amount of the credit note

ContactReference String False

The reference of the contact when the credit note was created

TaxAddressRegionDisplayedAs String False

The name of the resource

TaxAddressRegionLegacyId Int False

The legacy ID for the item

TaxAddressRegionId String False

The unique identifier for the item

TaxCalculationMethod String False

The tax calculation method, if applicable, for this sales credit note, returns invoice or cash.

TermsAndConditions String False

Credit note terms and conditions

PaymentsAllocationsAggregate String False

The associated payments and allocations

ShippingTaxRateId String False

TaxRates.Id

The unique identifier for the item

Sent Bool False

Indicates whether the credit note has been sent

DeliveryAddressId String False

Addresses.Id

The unique identifier for the item

UpdatedAt Datetime True

The datetime when the item was last updated

LegacyId Int False

The legacy ID for the item

TaxAnalysisAggregate String False

The credit note tax analysis

BaseCurrencyShippingTotalAmount String False

The total shipping amount in base currency

InverseExchangeRate String False

The inverse exchange rate for the credit note

TransactionId String False

Transactions.Id

The unique identifier for the item

TotalQuantity String False

The total quantity of the credit note

StatusDisplayedAs String False

The name of the resource

StatusLegacyId Int False

The legacy ID for the item

StatusId String False

The unique identifier for the item

DetailedTaxAnalysisBaseCurrencyTotalGoodsAmount String False

The total base currency amount relating to goods for all tax rates

DetailedTaxAnalysisBaseCurrencyTotalTax String False

The base currency total tax amount for all tax rates

DetailedTaxAnalysisTotalRetailerTax String False

The total retailer tax amount for all tax rates

DetailedTaxAnalysisBaseCurrencyTotal String False

The total base currency amount for all tax rates

DetailedTaxAnalysisTotalGoodsAmount String False

The total amount relating to goods for all tax rates

DetailedTaxAnalysisTotalNet String False

The total net amount for all tax rates

DetailedTaxAnalysisBaseCurrencyTotalServicesAmount String False

The total base currency amount relating to services for all tax rates

DetailedTaxAnalysisBaseCurrencyTotalNet String False

The base currency total net amount for all tax rates

DetailedTaxAnalysisTotalServicesAmount String False

The total amount relating to services for all tax rates

DetailedTaxAnalysisTotal String False

The total amount for all tax rates

DetailedTaxAnalysisTotalTax String False

The total tax amount for all tax rates

TransactionTypeId String False

TransactionTypes.Id

The unique identifier for the item

BaseCurrencyShippingTaxAmount String False

The tax shipping amount in base currency

ShippingTaxAmount String False

The tax shipping amount. NOTE: This is not required for POST/PUT requests as the shipping tax is calculated based on the shipping_net_amount and the shipping_tax_rate.

CreatedAt Datetime True

The datetime when the item was created

PaymentsAllocationsTotalAmount String False

The total amount of all payments and allocations

CreditNoteNumberPrefix String False

The credit note number prefix

VoidReason String False

The reason the credit note was voided

TaxReconciled Bool False

Indicates if the sales credit note is tax reconciled or not.

Editable Bool False

Indicates whether artefact can be edited

ShippingTotalAmount String False

The total shipping amount

BaseCurrencyShippingNetAmount String False

The net shipping amount in base currency

ShippingNetAmount String False

The net shipping amount

CurrencyDisplayedAs String False

The name of the resource

CurrencyLegacyId Int False

The legacy ID for the item

CurrencyId String False

The unique identifier for the item

Notes String False

credit note notes

MainAddressFreeForm String False

The free-form main address of the credit note

TotalPaid String False

The total paid amount of the credit note including any payments, allocations and discounts

DeletedAt Datetime True

The datetime when the item was deleted

Date String False

The date of the credit note

BaseCurrencyTaxAmount String False

The tax amount of the credit note in base currency

BaseCurrencyOutstandingAmount String False

The outstanding amount of the credit note in base currency

BaseCurrencyTotalDiscountAmount String False

The discount amount on the credit note in base currency

ShowPaymentsAllocations String False
MarkAsSent String False
ShowPaymentsAllocations String False
Search String False
FromDate Datetime True
ToDate Datetime True
DeletedSince Datetime True
HasAttachments String True

SalesEstimates

Query SalesEstimates in Sage Business Cloud Accounting.

Table Specific Information

Query the available SalesEstimates

Select

The driver uses the Sage Accounting API to process search criteria that refer to ContactId,StatusId,Search,FromDate,ToDate,UpdatedOrCreatedSince,HasAttachments columns. The driver processes other filters client-side within the driver.

  • ContactId supports the '=' operator.
  • StatusId supports the '=' operator.
  • Search supports the '=' operator.
  • FromDate supports the '=' operator.
  • ToDate supports the '=' operator.
  • UpdatedOrCreatedSince supports the '=' operator.
  • HasAttachments supports the '=' operator.

For example, the following queries are processed server side:

SELECT * FROM SalesEstimates WHERE ContactId='abc'
SELECT * FROM SalesEstimates WHERE StatusId='abc'
SELECT * FROM SalesEstimates WHERE Search='abc'
SELECT * FROM SalesEstimates WHERE FromDate='2018-12-15'
SELECT * FROM SalesEstimates WHERE ToDate='2018-12-15'
SELECT * FROM SalesEstimates WHERE UpdatedOrCreatedSince='2018-12-15'
SELECT * FROM SalesEstimates WHERE HasAttachments='abc'

Insert

Create a simple sales estimate.

INSERT INTO  [SalesEstimates] (ContactName, MainAddressId, Editable) VALUES ('test', '1', 'true')

Update

Updates are performed based on Id.

UPDATE SalesEstimates SET Reference='abc' WHERE Id='123'

Delete

You must specify the ID of the SalesEstimates to delete it.

DELETE FROM SalesEstimates WHERE ID ='123' 

Columns

Name Type ReadOnly References Description
Id [KEY] String False

The unique identifier for the item

DisplayedAs String False

The name of the resource

Reference String False

The reference for the estimate

BaseCurrencyTotalAmount String False

The total amount of the estimate in base currency

MainAddressId String False

Addresses.Id

The unique identifier for the item

BaseCurrencyNetAmount String False

The net amount of the estimate in base currency

BaseCurrencyShippingTaxBreakdownAggregate String False

The shipping tax breakdown for the estimate in base currency

ProfitAnalysisTotalProfit String False

The profit amount

ProfitAnalysisTotalDescription String False

The description

ProfitAnalysisTotalTotalSale String False

The total sale

ProfitAnalysisTotalTotalCost String False

The total cost

ProfitAnalysisTotalProfitPercentage String False

The profit percentage

LineBreakdownAggregate String False

The breakdown of profit per line

ContactName String False

The name of the contact when the estimate was created

SentByEmail Bool False

Indicates whether the estimate has been emailed

ShippingTaxBreakdownAggregate String False

The shipping tax breakdown for the estimate

TotalDiscountAmount String False

The discount amount on the estimate

TaxAmount String False

The tax amount of the estimate

PaymentsAllocationsTotalDiscount String False

The total discount of all payments and allocations

WithholdingTaxRate String False

IRPF withheld Tax Rate (Spain only)

TotalAmount String False

The total amount of the estimate

ExchangeRate String False

The exchange rate for the estimate

EstimateNumberPrefix String False

The estimate number prefix

ContactId String False

Contacts.Id

The unique identifier for the item

TaxAddressRegionDisplayedAs String False

The name of the resource

TaxAddressRegionLegacyId Int False

The legacy ID for the item

TaxAddressRegionId String False

The unique identifier for the item

TaxCalculationMethod String False

The tax calculation method, if applicable, for this sales invoice, returns invoice, cash or retailer.

TermsAndConditions String False

Estimate terms and conditions

ShippingTaxRateDisplayedAs String False

The name of the resource

ShippingTaxRateLegacyId Int False

The legacy ID for the item

ShippingTaxRateId String False

The unique identifier for the item

Sent Bool False

Indicates whether the estimate has been sent

DeliveryAddressFreeForm String False

The free-form delivery address of the estimate

BaseCurrencyWithholdingTaxAmount String False

IRPF withheld Tax Amount (Spain only) in the base currency

DeliveryAddressId String False

Addresses.Id

The unique identifier for the item

WithholdingTaxAmount String False

IRPF withheld Tax Amount (Spain only)

UpdatedAt Datetime True

The datetime when the item was last updated

LegacyId Int False

The legacy ID for the item

TaxAnalysisAggregate String False

The estimate tax analysis

BaseCurrencyShippingTotalAmount String False

The total shipping amount in base currency

InverseExchangeRate String False

The inverse exchange rate for the estimate

TotalQuantity String False

The total quantity of the estimate

StatusDisplayedAs String False

The quote status name

StatusId String False

The quote status id

SalesQuoteLineItemAggregate String False

The estimate lines of the estimate

DetailedTaxAnalysisBaseCurrencyTotalGoodsAmount String False

The total base currency amount relating to goods for all tax rates

DetailedTaxAnalysisBaseCurrencyTotalTax String False

The base currency total tax amount for all tax rates

DetailedTaxAnalysisTotalRetailerTax String False

The total retailer tax amount for all tax rates

DetailedTaxAnalysisBaseCurrencyTotal String False

The total base currency amount for all tax rates

DetailedTaxAnalysisTotalGoodsAmount String False

The total amount relating to goods for all tax rates

DetailedTaxAnalysisTotalNet String False

The total net amount for all tax rates

DetailedTaxAnalysisBaseCurrencyTotalServicesAmount String False

The total base currency amount relating to services for all tax rates

DetailedTaxAnalysisBaseCurrencyTotalNet String False

The base currency total net amount for all tax rates

DetailedTaxAnalysisTotalServicesAmount String False

The total amount relating to services for all tax rates

DetailedTaxAnalysisTotal String False

The total amount for all tax rates

DetailedTaxAnalysisTotalTax String False

The total tax amount for all tax rates

ExpiryDate String False

The expiry date of the estimate

BaseCurrencyShippingTaxAmount String False

The tax shipping amount in base currency

ShippingTaxAmount String False

The tax shipping amount. NOTE: This is not required for POST/PUT requests as the shipping tax is calculated based on the shipping_net_amount and the shipping_tax_rate.

CreatedAt Datetime True

The datetime when the item was created

PaymentsAllocationsTotalAmount String False

The total amount of all payments and allocations

TaxReconciled Bool False

Indicates if the quote/estimate is tax reconciled or not.

Editable Bool False

Indicates whether artefact can be edited

ShippingTotalAmount String False

The total shipping amount

InvoiceDisplayedAs String False

The name of the resource

InvoiceLegacyId Int False

The legacy ID for the item

InvoiceId String False

The unique identifier for the item

BaseCurrencyShippingNetAmount String False

The net shipping amount in base currency

ShippingNetAmount String False

The net shipping amount

CurrencyDisplayedAs String False

The name of the resource

CurrencyLegacyId Int False

The legacy ID for the item

CurrencyId String False

The unique identifier for the item

EstimateNumber String False

The generated estimate number

Notes String False

Estimate notes

MainAddressFreeForm String False

The free-form main address of the estimate

TotalPaid String False

The total paid amount of the estimate including any payments, allocations and discounts

Date Datetime True

The date of the estimate

BaseCurrencyTaxAmount String False

The tax amount of the estimate in base currency

BaseCurrencyTotalDiscountAmount String False

The discount amount on the estimate in base currency

Search String False
FromDate Datetime True
ToDate Datetime True
HasAttachments String True

SalesInvoices

Query SalesInvoices in Sage Business Cloud Accounting.

Table Specific Information

Query the available SalesInvoices

Select

The driver uses the Sage Accounting API to process search criteria that refer to ContactId,StatusId,ShowPaymentsAllocations,ShowCorrections,MarkAsSent,ShowPaymentsAllocations,Search,FromDate,ToDate,UpdatedOrCreatedSince,DeletedSince,HasAttachments columns. The driver processes other filters client-side within the driver.

  • ContactId supports the '=' operator.
  • StatusId supports the '=' operator.
  • ShowPaymentsAllocations supports the '=' operator.
  • ShowCorrections supports the '=' operator.
  • MarkAsSent supports the '=' operator.
  • ShowPaymentsAllocations supports the '=' operator.
  • Search supports the '=' operator.
  • FromDate supports the '=' operator.
  • ToDate supports the '=' operator.
  • UpdatedOrCreatedSince supports the '=' operator.
  • DeletedSince supports the '=' operator.
  • HasAttachments supports the '=' operator.

For example, the following queries are processed server side:

SELECT * FROM SalesInvoices WHERE ContactId='abc'
SELECT * FROM SalesInvoices WHERE StatusId='abc'
SELECT * FROM SalesInvoices WHERE ShowPaymentsAllocations='abc'
SELECT * FROM SalesInvoices WHERE ShowCorrections='abc'
SELECT * FROM SalesInvoices WHERE MarkAsSent='abc'
SELECT * FROM SalesInvoices WHERE ShowPaymentsAllocations='abc'
SELECT * FROM SalesInvoices WHERE Search='abc'
SELECT * FROM SalesInvoices WHERE FromDate='2018-12-15'
SELECT * FROM SalesInvoices WHERE ToDate='2018-12-15'
SELECT * FROM SalesInvoices WHERE UpdatedOrCreatedSince='2018-12-15'
SELECT * FROM SalesInvoices WHERE DeletedSince='2018-12-15'
SELECT * FROM SalesInvoices WHERE HasAttachments='abc'

Insert

Create a simple sales invoice.

INSERT INTO  [SalesInvoices] (ContactName, TotalPaid, TransactionTypeId, TotalQuantity) VALUES ('test', '1', '2', '3')

Update

Updates are performed based on Id.

UPDATE SalesInvoices SET Reference='abc' WHERE Id='123'

Delete

You must specify the ID of the SalesInvoices to delete it.

DELETE FROM SalesInvoices WHERE ID ='123' 

Columns

Name Type ReadOnly References Description
Id [KEY] String False

The unique identifier for the item

DisplayedAs String False

The name of the resource

Reference String False

The reference for the invoice

BaseCurrencyTotalAmount String False

The total amount of the invoice in base currency

MainAddressId String False

Addresses.Id

The unique identifier for the item

BaseCurrencyNetAmount String False

The net amount of the invoice in base currency

BaseCurrencyShippingTaxBreakdownAggregate String False

The shipping tax breakdown for the invoice in base currency

ContactName String False

The name of the contact when the invoice was created

SentByEmail Bool False

Indicates whether the invoice has been emailed

ShippingTaxBreakdownAggregate String False

The shipping tax breakdown for the invoice

Migrated Bool False

Indicates if the sales invoice was migrated from another system.

DueDate Datetime True

The due date of the invoice

TotalDiscountAmount String False

The discount amount on the invoice

TaxAmount String False

The tax amount of the invoice

LastPaid String False

The date of the last payment

PaymentsAllocationsTotalDiscount String False

The total discount of all payments and allocations

WithholdingTaxRate String False

IRPF withheld Tax Rate (Spain only)

TotalAmount String False

The total amount of the invoice

ExchangeRate String False

The exchange rate for the invoice

InvoiceNumberPrefix String False

The invoice number prefix

OutstandingAmount String False

The outstanding amount of the invoice

ContactId String False

Contacts.Id

The unique identifier for the item

TaxAddressRegionDisplayedAs String False

The name of the resource

TaxAddressRegionLegacyId Int False

The legacy ID for the item

TaxAddressRegionId String False

The unique identifier for the item

DeliveryPerformanceDate String False

Delivery/Performance Date (Germany only)

TaxCalculationMethod String False

The tax calculation method, if applicable, for this sales invoice, returns invoice, cash or retailer.

TermsAndConditions String False

Invoice terms and conditions

PaymentsAllocationsAggregate String False

The associated payments and allocations

ShippingTaxRateId String False

TaxRates.Id

The unique identifier for the item

Sent Bool False

Indicates whether the invoice has been sent

DeliveryAddressFreeForm String False

The free-form delivery address of the invoice

BaseCurrencyWithholdingTaxAmount String False

IRPF withheld Tax Amount (Spain only) in the base currency

DeliveryAddressId String False

Addresses.Id

The unique identifier for the item

WithholdingTaxAmount String False

IRPF withheld Tax Amount (Spain only)

UpdatedAt Datetime True

The datetime when the item was last updated

LegacyId Int False

The legacy ID for the item

TaxAnalysisAggregate String False

The invoice tax analysis

SalesCorrectiveInvoiceAggregate String False

The corrective entries associated with the invoice

InverseExchangeRate String False

The inverse exchange rate for the invoice

BaseCurrencyShippingTotalAmount String False

The total shipping amount in base currency

TransactionId String False

Transactions.Id

The unique identifier for the item

TotalQuantity String False

The total quantity of the invoice

StatusDisplayedAs String False

The name of the resource

StatusLegacyId Int False

The legacy ID for the item

StatusId String False

The unique identifier for the item

DetailedTaxAnalysisBaseCurrencyTotalGoodsAmount String False

The total base currency amount relating to goods for all tax rates

DetailedTaxAnalysisBaseCurrencyTotalTax String False

The base currency total tax amount for all tax rates

DetailedTaxAnalysisTotalRetailerTax String False

The total retailer tax amount for all tax rates

DetailedTaxAnalysisBaseCurrencyTotal String False

The total base currency amount for all tax rates

DetailedTaxAnalysisTotalGoodsAmount String False

The total amount relating to goods for all tax rates

DetailedTaxAnalysisTotalNet String False

The total net amount for all tax rates

DetailedTaxAnalysisBaseCurrencyTotalServicesAmount String False

The total base currency amount relating to services for all tax rates

DetailedTaxAnalysisBaseCurrencyTotalNet String False

The base currency total net amount for all tax rates

DetailedTaxAnalysisTotalServicesAmount String False

The total amount relating to services for all tax rates

DetailedTaxAnalysisTotal String False

The total amount for all tax rates

DetailedTaxAnalysisTotalTax String False

The total tax amount for all tax rates

TransactionTypeId String False

TransactionTypes.Id

The unique identifier for the item

BaseCurrencyShippingTaxAmount String False

The tax shipping amount in base currency

ShippingTaxAmount String False

The tax shipping amount. NOTE: This is not required for POST/PUT requests as the shipping tax is calculated based on the shipping_net_amount and the shipping_tax_rate.

CreatedAt Datetime True

The datetime when the item was created

PaymentsAllocationsTotalAmount String False

The total amount of all payments and allocations

VoidReason String False

The reason the invoice was voided

TaxReconciled Bool False

Indicates if the sales invoice is tax reconciled or not.

Editable Bool False

Indicates whether artefact can be edited

ShippingTotalAmount String False

The total shipping amount

BaseCurrencyShippingNetAmount String False

The net shipping amount in base currency

ShippingNetAmount String False

The net shipping amount

CurrencyId String False

Currencies.Id

The unique identifier for the item

SalesInvoiceLineItemAggregate String False

The invoice lines of the invoice

InvoiceNumber String False

The generated invoice number

Notes String False

Invoice notes

MainAddressFreeForm String False

The free-form main address of the invoice

TotalPaid String False

The total paid amount of the invoice including any payments, allocations and discounts

DeletedAt Datetime True

The datetime when the item was deleted

Date String False

The date of the invoice

BaseCurrencyTaxAmount String False

The tax amount of the invoice in base currency

BaseCurrencyOutstandingAmount String False

The outstanding amount of the invoice in base currency

BaseCurrencyTotalDiscountAmount String False

The discount amount on the invoice in base currency

LinksAggregate String False

Links for the resource

OriginalQuoteEstimateDisplayedAs String False

The name of the resource

OriginalQuoteEstimateLegacyId Int False

The legacy ID for the item

OriginalQuoteEstimateId String False

The unique identifier for the item

ShowPaymentsAllocations String False
ShowCorrections String False
MarkAsSent String False
ShowPaymentsAllocations String False
Search String False
FromDate Datetime True
ToDate Datetime True
DeletedSince Datetime True
HasAttachments String True
IsCIS Bool True

Determines if this is a CIS transaction.

CISApplicableAmount String True
BaseCurrencyCISApplicableAmount String True
TotalAfterCISDeduction String True
BaseCurrencyTotalAfterCISDeduction String True
HasCISLabour Bool True
HasCISMaterials Bool True

SalesQuickEntries

Query SalesQuickEntries in Sage Business Cloud Accounting.

Table Specific Information

Query the available SalesQuickEntries

Select

The driver uses the Sage Accounting API to process search criteria that refer to ContactId,StatusId,ShowPaymentsAllocations,Search,FromDate,ToDate,UpdatedOrCreatedSince,DeletedSince,ShowPaymentsAllocations,HasAttachments columns. The driver processes other filters client-side within the driver.

  • ContactId supports the '=' operator.
  • StatusId supports the '=' operator.
  • ShowPaymentsAllocations supports the '=' operator.
  • Search supports the '=' operator.
  • FromDate supports the '=' operator.
  • ToDate supports the '=' operator.
  • UpdatedOrCreatedSince supports the '=' operator.
  • DeletedSince supports the '=' operator.
  • ShowPaymentsAllocations supports the '=' operator.
  • HasAttachments supports the '=' operator.

For example, the following queries are processed server side:

SELECT * FROM SalesQuickEntries WHERE ContactId='abc'
SELECT * FROM SalesQuickEntries WHERE StatusId='abc'
SELECT * FROM SalesQuickEntries WHERE ShowPaymentsAllocations='abc'
SELECT * FROM SalesQuickEntries WHERE Search='abc'
SELECT * FROM SalesQuickEntries WHERE FromDate='2018-12-15'
SELECT * FROM SalesQuickEntries WHERE ToDate='2018-12-15'
SELECT * FROM SalesQuickEntries WHERE UpdatedOrCreatedSince='2018-12-15'
SELECT * FROM SalesQuickEntries WHERE DeletedSince='2018-12-15'
SELECT * FROM SalesQuickEntries WHERE ShowPaymentsAllocations='abc'
SELECT * FROM SalesQuickEntries WHERE HasAttachments='abc'

Update

Updates are performed based on Id.

UPDATE SalesQuickEntries SET Reference='abc' WHERE Id='123'

Delete

You must specify the ID of the SalesQuickEntries to delete it.

DELETE FROM SalesQuickEntries WHERE ID ='123' 

Columns

Name Type ReadOnly References Description
Id [KEY] String False

The unique identifier for the item

DisplayedAs String False

The name of the resource

Reference String False

The reference for the quick entry

BaseCurrencyTotalAmount String False

The total amount of the quick entry in base currency

TradeOfAsset Bool False

Whether the quick entry is marked as trade of asset.

BaseCurrencyTaxBreakdownAggregate String False

The tax breakdown for the quick entry in base currency

BaseCurrencyNetAmount String False

The net amount of the quick entry in base currency

ContactName String False

The name of the contact when the quick entry was created

Migrated Bool False

Indicates if the quick entry was migrated from another system.

TaxAmount String False

The tax amount of the quick entry

TotalAmount String False

The total amount of the quick entry

ExchangeRate String False

The exchange rate for the quick entry

OutstandingAmount String False

The outstanding amount of the quick entry

ContactId String False

Contacts.Id

The unique identifier for the item

NetAmount String False

The net amount of the quick entry

ContactReference String False

The reference of the contact when the quick entry was created

TaxAddressRegionDisplayedAs String False

The name of the resource

TaxAddressRegionLegacyId Int False

The legacy ID for the item

TaxAddressRegionId String False

The unique identifier for the item

PaymentsAllocationsAggregate String False

The associated payments and allocations

UpdatedAt Datetime True

The datetime when the item was last updated

LegacyId Int False

The legacy ID for the item

LedgerAccountId String False

LedgerAccounts.Id

The unique identifier for the item

InverseExchangeRate String False

The inverse exchange rate for the quick entry

TransactionId String False

Transactions.Id

The unique identifier for the item

StatusDisplayedAs String False

The name of the resource

StatusLegacyId Int False

The legacy ID for the item

StatusId String False

The unique identifier for the item

TaxBreakdownAggregate String False

The tax breakdown for the quick entry

TransactionTypeId String False

TransactionTypes.Id

The unique identifier for the item

CreatedAt Datetime True

The datetime when the item was created

Details String False

A description of the quick entry

CurrencyId String False

Currencies.Id

The unique identifier for the item

DeletedAt Datetime True

The datetime when the item was deleted

TaxRateId String False

TaxRates.Id

The unique identifier for the item

Date String False

The date of the quick entry

BaseCurrencyTaxAmount String False

The tax amount of the quick entry in base currency

QuickEntryTypeDisplayedAs String False

The name of the resource

QuickEntryTypeLegacyId Int False

The legacy ID for the item

QuickEntryTypeId String False

The unique identifier for the item

BaseCurrencyOutstandingAmount String False

The outstanding amount of the quick entry in base currency

ShowPaymentsAllocations String False
Search String False
FromDate Datetime True
ToDate Datetime True
DeletedSince Datetime False
ShowPaymentsAllocations String True
HasAttachments String True

SalesQuotes

Query SalesQuotes in Sage Business Cloud Accounting.

Table Specific Information

Query the available SalesQuotes

Select

The driver uses the Sage Accounting API to process search criteria that refer to ContactId,StatusId,FromDate,ToDate,UpdatedOrCreatedSince,HasAttachments columns. The driver processes other filters client-side within the driver.

  • ContactId supports the '=' operator.
  • StatusId supports the '=' operator.
  • FromDate supports the '=' operator.
  • ToDate supports the '=' operator.
  • UpdatedOrCreatedSince supports the '=' operator.
  • HasAttachments supports the '=' operator.

For example, the following queries are processed server side:

SELECT * FROM SalesQuotes WHERE ContactId='abc'
SELECT * FROM SalesQuotes WHERE StatusId='abc'
SELECT * FROM SalesQuotes WHERE FromDate='2018-12-15'
SELECT * FROM SalesQuotes WHERE ToDate='2018-12-15'
SELECT * FROM SalesQuotes WHERE UpdatedOrCreatedSince='2018-12-15'
SELECT * FROM SalesQuotes WHERE HasAttachments='abc'

Update

Updates are performed based on Id.

UPDATE SalesQuotes SET Reference='abc' WHERE Id='123'

Delete

You must specify the ID of the SalesQuotes to delete it.

DELETE FROM SalesQuotes WHERE ID ='123' 

Columns

Name Type ReadOnly References Description
Id [KEY] String False

The unique identifier for the item

DisplayedAs String False

The name of the resource

Reference String False

The reference for the quote

BaseCurrencyTotalAmount String False

The total amount of the quote in base currency

MainAddressId String False

Addresses.Id

The unique identifier for the item

BaseCurrencyNetAmount String False

The net amount of the quote in base currency

BaseCurrencyShippingTaxBreakdownAggregate String False

The shipping tax breakdown for the quote in base currency

ProfitAnalysisTotalProfit String False

The profit amount

ProfitAnalysisTotalDescription String False

The description

ProfitAnalysisTotalTotalSale String False

The total sale

ProfitAnalysisTotalTotalCost String False

The total cost

ProfitAnalysisTotalProfitPercentage String False

The profit percentage

LineBreakdownAggregate String False

The breakdown of profit per line

ContactName String False

The name of the contact when the quote was created

SentByEmail Bool False

Indicates whether the quote has been emailed

ShippingTaxBreakdownAggregate String False

The shipping tax breakdown for the quote

TotalDiscountAmount String False

The discount amount on the quote

TaxAmount String False

The tax amount of the quote

PaymentsAllocationsTotalDiscount String False

The total discount of all payments and allocations

WithholdingTaxRate String False

IRPF withheld Tax Rate (Spain only)

TotalAmount String False

The total amount of the quote

ExchangeRate String False

The exchange rate for the quote

ContactId String False

Contacts.Id

The unique identifier for the item

NetAmount String False

The net amount of the quote

ContactReference String False

The reference of the contact when the quote was created

TaxCalculationMethod String False

The tax calculation method, if applicable, for this sales invoice, returns invoice, cash or retailer.

TermsAndConditions String False

Quote terms and conditions

ShippingTaxRateId String False

TaxRates.Id

The unique identifier for the item

Sent Bool False

Indicates whether the quote has been sent

DeliveryAddressFreeForm String False

The free-form delivery address of the quote

BaseCurrencyWithholdingTaxAmount String False

IRPF withheld Tax Amount (Spain only) in the base currency

DeliveryAddressId String False

Addresses.Id

The unique identifier for the item

WithholdingTaxAmount String False

IRPF withheld Tax Amount (Spain only)

UpdatedAt Datetime True

The datetime when the item was last updated

LegacyId Int False

The legacy ID for the item

TaxAnalysisAggregate String False

The quote tax analysis

QuoteNumberPrefix String False

The quote number prefix

BaseCurrencyShippingTotalAmount String False

The total shipping amount in base currency

QuoteNumber String False

The generated quote number

InverseExchangeRate String False

The inverse exchange rate for the quote

TotalQuantity String False

The total quantity of the quote

StatusDisplayedAs String False

The quote status name

StatusId String False

The quote status id

DetailedTaxAnalysisBaseCurrencyTotalGoodsAmount String False

The total base currency amount relating to goods for all tax rates

DetailedTaxAnalysisBaseCurrencyTotalTax String False

The base currency total tax amount for all tax rates

DetailedTaxAnalysisTotalRetailerTax String False

The total retailer tax amount for all tax rates

DetailedTaxAnalysisBaseCurrencyTotal String False

The total base currency amount for all tax rates

DetailedTaxAnalysisTotalGoodsAmount String False

The total amount relating to goods for all tax rates

DetailedTaxAnalysisTotalNet String False

The total net amount for all tax rates

DetailedTaxAnalysisBaseCurrencyTotalServicesAmount String False

The total base currency amount relating to services for all tax rates

DetailedTaxAnalysisBaseCurrencyTotalNet String False

The base currency total net amount for all tax rates

DetailedTaxAnalysisTotalServicesAmount String False

The total amount relating to services for all tax rates

DetailedTaxAnalysisTotal String False

The total amount for all tax rates

DetailedTaxAnalysisTotalTax String False

The total tax amount for all tax rates

ExpiryDate String False

The expiry date of the quote

BaseCurrencyShippingTaxAmount String False

The tax shipping amount in base currency

ShippingTaxAmount String False

The tax shipping amount. NOTE: This is not required for POST/PUT requests as the shipping tax is calculated based on the shipping_net_amount and the shipping_tax_rate.

CreatedAt Datetime True

The datetime when the item was created

PaymentsAllocationsTotalAmount String False

The total amount of all payments and allocations

TaxReconciled Bool False

Indicates if the quote/estimate is tax reconciled or not.

Editable Bool False

Indicates whether artefact can be edited

ShippingTotalAmount String False

The total shipping amount

InvoiceDisplayedAs String False

The name of the resource

InvoiceLegacyId Int False

The legacy ID for the item

InvoiceId String False

The unique identifier for the item

BaseCurrencyShippingNetAmount String False

The net shipping amount in base currency

ShippingNetAmount String False

The net shipping amount

CurrencyId String False

Currencies.Id

The unique identifier for the item

Notes String False

Quote notes

MainAddressFreeForm String False

The free-form main address of the quote

TotalPaid String False

The total paid amount of the quote including any payments, allocations and discounts

Date Datetime False

The date of the quote

BaseCurrencyTaxAmount String False

The tax amount of the quote in base currency

SalesQuoteLineItemAggregate String False

The quote lines of the quote

BaseCurrencyTotalDiscountAmount String False

The discount amount on the quote in base currency

FromDate Datetime False
ToDate Datetime False
HasAttachments String False

Services

Query Services in Sage Business Cloud Accounting.

Table Specific Information

Query the available Services

Select

The driver uses the Sage Accounting API to process search criteria that refer to Active,Search,UpdatedOrCreatedSince,DeletedSince columns. The driver processes other filters client-side within the driver.

  • Active supports the '=' operator.
  • Search supports the '=' operator.
  • UpdatedOrCreatedSince supports the '=' operator.
  • DeletedSince supports the '=' operator.

For example, the following queries are processed server side:

SELECT * FROM Services WHERE Active=false
SELECT * FROM Services WHERE Search='abc'
SELECT * FROM Services WHERE UpdatedOrCreatedSince='2018-12-15'
SELECT * FROM Services WHERE DeletedSince='2018-12-15'

Insert

INSERT into StockItems (SalesLedgerAccountId, SalesTaxRateId, Active) VALUES ('1', '2', 'true')

Update

Updates are performed based on Id.

UPDATE Services SET SalesLedgerAccountId='abc' WHERE Id='123'

Delete

You must specify the ID of the Services to delete it.

DELETE FROM Services WHERE ID ='123' 

Columns

Name Type ReadOnly References Description
Id [KEY] String False

The unique identifier for the item

SalesLedgerAccountId String False

LedgerAccounts.Id

The unique identifier for the item

CreatedAt Datetime True

The datetime when the item was created

SalesTaxRateDisplayedAs String False

The name of the resource

SalesTaxRateLegacyId Int False

The legacy ID for the item

SalesTaxRateId String False

The unique identifier for the item

Active Bool False

Indicates whether the service is active

ItemCode String False

The item code for the service

PurchaseLedgerAccountId String False

LedgerAccounts.Id

The unique identifier for the item

DeletedAt Datetime True

The datetime when the item was deleted

PurchaseTaxRateId String False

TaxRates.Id

The unique identifier for the item

Notes String False

The notes for the service

UsualSupplierId String False

Contacts.Id

The unique identifier for the item

SalesRatesAggregate String False

The sales rates for the service

DisplayedAs String False

The name of the resource

Description String False

The service description

PurchaseDescription String False

The service purchase description

LegacyId Int False

The legacy ID for the item

SourceGuid String False

Used when importing services from external sources

UpdatedAt Datetime True

The datetime when the item was last updated

Search String True
DeletedSince Datetime True

StockItems

Query StockItems in Sage Business Cloud Accounting.

Table Specific Information

Query the available StockItems

Select

The driver uses the Sage Accounting API to process search criteria that refer to Active,Search,UpdatedOrCreatedSince,DeletedSince,OutOfStock,BelowReorderLevel columns. The driver processes other filters client-side within the driver.

  • Active supports the '=' operator.
  • Search supports the '=' operator.
  • UpdatedOrCreatedSince supports the '=' operator.
  • DeletedSince supports the '=' operator.
  • OutOfStock supports the '=' operator.
  • BelowReorderLevel supports the '=' operator.

For example, the following queries are processed server side:

SELECT * FROM StockItems WHERE Active=false
SELECT * FROM StockItems WHERE Search='abc'
SELECT * FROM StockItems WHERE UpdatedOrCreatedSince='2018-12-15'
SELECT * FROM StockItems WHERE DeletedSince='2018-12-15'
SELECT * FROM StockItems WHERE OutOfStock='abc'
SELECT * FROM StockItems WHERE BelowReorderLevel='abc'

Insert

INSERT into StockItems (SalesLedgerAccountId, SalesTaxRateId) VALUES ('1', '2')

Update

Updates are performed based on Id.

UPDATE StockItems SET Location='abc' WHERE Id='123'

Delete

You must specify the ID of the StockItems to delete it.

DELETE FROM StockItems WHERE ID ='123' 

Columns

Name Type ReadOnly References Description
Id [KEY] String False

The unique identifier for the item

Location String False

The location for the stock item

SalesLedgerAccountId String False

LedgerAccounts.Id

The unique identifier for the item

CreatedAt Datetime True

The datetime when the item was created

ReorderQuantity String False

The reorder quantity for the stock item

SalesTaxRateId String False

TaxRates.Id

The unique identifier for the item

AverageCostPrice String False

The average price across all purchases of this stock item

LastCostPrice String False

The most recent 'purchase invoice' or 'adjustment in' price

Active Bool False

Indicates whether the stock item is active

ItemCode String False

The item code for the stock item

PurchaseLedgerAccountId String False

LedgerAccounts.Id

The unique identifier for the item

WeightConverted String False

The weight of stock item converted to the lowest unit of measurement

SalesPricesAggregate String False

The sales prices for the stock item

DeletedAt Datetime True

The datetime when the item was deleted

SupplierPartNumber String False

The supplier part number for stock item

PurchaseTaxRateId String False

TaxRates.Id

The unique identifier for the item

Notes String False

The notes for the stock item

Weight String False

The weight of stock item

AverageCostPriceStockValue String False

The value of the current stock in terms of the average cost price

CostPrice String False

The cost price of the stock item

UsualSupplierId String False

Contacts.Id

The unique identifier for the item

MeasurementUnit String False

The unit of measure of weight for stock item

DisplayedAs String False

The name of the resource

Description String False

The stock item description

PurchaseDescription String False

The stock item purchase description

ReorderLevel String False

The reorder level for the stock item

LegacyId Int False

The legacy ID for the item

SourceGuid String False

Used when importing stock items from external sources

Barcode String False

The barcode for the stock item

LastCostPriceStockValue String False

The value of the current stock in terms of the last cost price

CostPriceLastUpdated String False

The date on which the last cost price was last updated

QuantityInStock String False

The current quantity of the stock item held by the business

UpdatedAt Datetime True

The datetime when the item was last updated

Search String False
DeletedSince Datetime True
OutOfStock String True
BelowReorderLevel String True

StockMovements

Query StockMovements in Sage Business Cloud Accounting.

Table Specific Information

Query the available StockMovements

Select

The driver uses the Sage Accounting API to process search criteria that refer to StockItemId,Search,FromDate,ToDate columns. The driver processes other filters client-side within the driver.

  • StockItemId supports the '=' operator.
  • Search supports the '=' operator.
  • FromDate supports the '=' operator.
  • ToDate supports the '=' operator.

For example, the following queries are processed server side:

SELECT * FROM StockMovements WHERE StockItemId='abc'
SELECT * FROM StockMovements WHERE Search='abc'
SELECT * FROM StockMovements WHERE FromDate='2018-12-15'
SELECT * FROM StockMovements WHERE ToDate='2018-12-15'

Insert

INSERT into StockMovements (Quantity, CostPrice) VALUES ('7', '12')

Update

Updates are performed based on Id.

UPDATE StockMovements SET Details='abc' WHERE Id='123'

Delete

You must specify the ID of the StockMovements to delete it.

DELETE FROM StockMovements WHERE ID ='123' 

Columns

Name Type ReadOnly References Description
Id [KEY] String False

The unique identifier for the item

CreatedAt Datetime True

The datetime when the item was created

Details String False

The transaction details of the stock movement

DisplayedAs String False

Display text for the stock movement

Date Datetime False

The date the stock movement occurred

MovementNumber String False

The movement number of the stock movement

Reference String False

The reference of the stock movement

Quantity String False

The quantity of the goods adjusted

UpdatedAt Datetime True

The datetime when the item was last updated

LegacyId Int False

The legacy ID for the item

CostPrice String False

The cost per unit of stock purchased

StockItemId String False

StockItems.Id

The unique identifier for the item

Search String False
FromDate Datetime True
ToDate Datetime True

TaxProfiles

Query TaxProfiles in Sage Business Cloud Accounting.

Table Specific Information

Query the available TaxProfiles

Select

The driver uses the Sage Accounting API to process search criteria that refer to UpdatedOrCreatedSince column. The driver processes other filters client-side within the driver.

  • UpdatedOrCreatedSince supports the '=' operator.

For example, the following queries are processed server side:

SELECT * FROM TaxProfiles WHERE UpdatedOrCreatedSince='2018-12-15'

Columns

Name Type ReadOnly References Description
Id [KEY] String False

The unique identifier for the item

CreatedAt Datetime True

The datetime when the item was created

DisplayedAs String False

The name of the resource

CollectTax Bool False

Indicates whether tax is collected for this tax type

LegacyId Int False

The legacy ID for the item

TaxReturnFrequencyId String False

TaxReturnFrequencies.Id

The unique identifier for the item

UpdatedAt Datetime True

The datetime when the item was last updated

TaxNumberSuffix String False

The tax number suffix

TaxNumber String False

The tax number

AddressRegionId String False

AddressRegions.Id

The unique identifier for the item

TaxRates

Query TaxRates in Sage Business Cloud Accounting.

Table Specific Information

Query the available TaxRates

Select

The driver uses the Sage Accounting API to process search criteria that refer to Date,UpdatedOrCreatedSince,AddressRegionId,Date,IncludeHistoricalData columns. The driver processes other filters client-side within the driver.

  • Date supports the '=' operator.
  • UpdatedOrCreatedSince supports the '=' operator.
  • AddressRegionId supports the '=' operator.
  • Date supports the '=' operator.
  • IncludeHistoricalData supports the '=' operator.

For example, the following queries are processed server side:

SELECT * FROM TaxRates WHERE Date='2018-12-15'
SELECT * FROM TaxRates WHERE UpdatedOrCreatedSince='2019-10-02T11:46:54'
SELECT * FROM TaxRates WHERE AddressRegionId='CA-AB'
SELECT * FROM TaxRates WHERE IncludeHistoricalData=false

Insert

INSERT into TaxRates (Name, Editable, IsVisible) VALUES ('Test', 'true', 'true')

Update

Updates are performed based on Id.

UPDATE TaxRates SET Name='test' WHERE Id='1a756edcf15743ba8cc53804df6049da'

Delete

You must specify the ID of the TaxRates to delete it.

DELETE FROM TaxRates WHERE ID ='1a756edcf15743ba8cc53804df6049da' 

Columns

Name Type ReadOnly References Description
Id [KEY] String False

The unique identifier for the item

CreatedAt Datetime True

The datetime when the item was created

IsCombinedRate Bool False

Indicates whether the tax rate is made up of component tax rates

DisplayedAs String False

The name of the resource

Name String False

The name of the tax rate

TaxRatePercentageAggregate String False

The tax rate percentage and date ranges they apply to

Editable Bool False

Indicates whether a tax rate can be edited

Retailer Bool False

Indicates if tax rate is a retailer rate or not

ComponentTaxRatesAggregate String False

The component tax rates which make up a combined rate

Deletable Bool False

Indicates whether a tax rate can be deleted

UpdatedAt Datetime True

The datetime when the item was last updated

Agency String False

The agency name (US Only)

LegacyId Int False

The legacy ID for the item

IsVisible Bool False

Indicates whether the tax rate is displayed in the application

Percentage String False

The current tax rate percentage

Date Datetime True
AddressRegionId String True
IncludeHistoricalData Bool True

Views

Views are composed of columns and pseudo columns. Views are similar to tables in the way that data is represented; however, views do not support updates. Entities that are represented as views are typically read-only entities. Often, a stored procedure* is available to update the data if such functionality is applicable to the data source.

Queries can be executed against a view as if it were a normal table, and the data that comes back is similar in that regard. To find out more about tables and stored procedures, please navigate to their corresponding entries in this help document.

Jitterbit Connector for Sage Business Cloud Accounting Views

Name Description
AddressRegions Query AddressRegions in Sage Business Cloud Accounting.
ArtefactStatuses Query ArtefactStatuses in Sage Business Cloud Accounting.
BaseJournalLine Query BaseJournalLine in Sage Business Cloud Accounting.
BusinessActivityTypes Query BusinessActivityTypes in Sage Business Cloud Accounting.
Businesses Query Businesses in Sage Business Cloud Accounting.
BusinessSettings Query BusinessSettings in Sage Business Cloud Accounting.
CoaAccounts Query CoaAccounts in Sage Business Cloud Accounting.
CoaTemplates Query CoaTemplates in Sage Business Cloud Accounting.
CorrectiveReasonCodes Query CorrectiveReasonCodes in Sage Business Cloud Accounting.
Countries Query Countries in Sage Business Cloud Accounting.
CountryGroups Query CountryGroups in Sage Business Cloud Accounting.
CountryOfRegistrations Query CountryOfRegistrations in Sage Business Cloud Accounting.
Currencies Query Currencies in Sage Business Cloud Accounting.
EuSalesDescriptions Query EuSalesDescriptions in Sage Business Cloud Accounting.
ExchangeRates Query ExchangeRates in Sage Business Cloud Accounting.
JournalLine Query JournalLine in Sage Business Cloud Accounting.
LedgerAccountClassifications Query LedgerAccountClassifications in Sage Business Cloud Accounting.
LedgerEntries Query LedgerEntries in Sage Business Cloud Accounting.
LiveExchangeRates Query LiveExchangeRates in Sage Business Cloud Accounting.
OtherPaymentLineItem Query OtherPaymentLineItem in Sage Business Cloud Accounting.
PaymentMethods Query PaymentMethods in Sage Business Cloud Accounting.
PurchaseCorrectiveInvoice Query PurchaseCorrectiveInvoice in Sage Business Cloud Accounting.
PurchaseCreditNoteLineItem Query PurchaseCreditNoteLineItem in Sage Business Cloud Accounting.
PurchaseInvoiceLineItem Query PurchaseInvoiceLineItem in Sage Business Cloud Accounting.
SalesCorrectiveInvoice Query SalesCorrectiveInvoice in Sage Business Cloud Accounting.
SalesCreditNoteLineItem Query SalesCreditNoteLineItem in Sage Business Cloud Accounting.
SalesQuoteLineItem Query SalesEstimatesQuoteLineItem in Sage Business Cloud Accounting.
SalesInvoiceLineItem Query SalesInvoiceLineItem in Sage Business Cloud Accounting.
SalesQuoteLineItem Query SalesQuoteLineItem in Sage Business Cloud Accounting.
TaxOffices Query TaxOffices in Sage Business Cloud Accounting.
TaxReturnFrequencies Query TaxReturnFrequencies in Sage Business Cloud Accounting.
TaxSchemes Query TaxSchemes in Sage Business Cloud Accounting.
Transactions Query Transactions in Sage Business Cloud Accounting.
TransactionTypes Query TransactionTypes in Sage Business Cloud Accounting.
UnallocatedArtefacts Query UnallocatedArtefacts in Sage Business Cloud Accounting.

AddressRegions

Query AddressRegions in Sage Business Cloud Accounting.

Table Specific Information

Query the available AddressRegions

Select

The driver uses the Sage Accounting API to process search criteria that refer to CountryId column. The driver processes other filters client-side within the driver.

  • CountryId supports the '=' operator.

For example, the following queries are processed server side:

SELECT * FROM AddressRegions WHERE CountryId='AF'

Columns

Name Type References Description
Id [KEY] String The unique identifier for the item
Name String The name of the address region
Code String The code of the address region
DisplayedAs String Display text for the item
LegacyId Int The legacy ID for the item
CountryId String

ArtefactStatuses

Query ArtefactStatuses in Sage Business Cloud Accounting.

Table Specific Information

Query the available ArtefactStatuses

Select

The driver processes all filters client-side within the driver.

Columns

Name Type References Description
Id [KEY] String The unique identifier for the item
DisplayedAs String The name of the resource
LegacyId Int The legacy ID for the item

BaseJournalLine

Query BaseJournalLine in Sage Business Cloud Accounting.

Table Specific Information

Query the available BaseJournalLine

Select

The driver processes all filters client-side within the driver.

Columns

Name Type References Description
Id [KEY] String The unique identifier for the item
Details String A description of the journal line
LedgerAccountId String

LedgerAccounts.Id

The unique identifier for the item
Debit String The debit amount of the journal line
Credit String The credit amount of the journal line
OpeningBalanceJournalsId String Parent ID

BusinessActivityTypes

Query BusinessActivityTypes in Sage Business Cloud Accounting.

Table Specific Information

Query the available BusinessActivityTypes

Select

The driver processes all filters client-side within the driver.

Columns

Name Type References Description
Id [KEY] String The unique identifier for the item
DisplayedAs String The name of the resource
LegacyId Int The legacy ID for the item

Businesses

Query Businesses in Sage Business Cloud Accounting.

Columns

Name Type References Description
Id [KEY] String The unique identifier for the item
DisplayedAs String The name of the resource

BusinessSettings

Query BusinessSettings in Sage Business Cloud Accounting.

Table Specific Information

Query the available BusinessSettings

Select

The driver processes all filters client-side within the driver.

Columns

Name Type References Description
Siret String SIRET Number (France only)
ShareCapital String Share Capital (France only)
AuxiliaryAccountsVisible Bool Auxiliary Accounts Visible (France & Spain only)
PurchaseLedgerAccountId String

LedgerAccounts.Id

The unique identifier for the item
SalesLedgerAccountId String

LedgerAccounts.Id

The unique identifier for the item
BankInterestChargesPaidLedgerAccountId String

LedgerAccounts.Id

The unique identifier for the item
StockPurchaseLedgerAccountId String

LedgerAccounts.Id

The unique identifier for the item
CarriageLedgerAccountId String

LedgerAccounts.Id

The unique identifier for the item
ProductSalesLedgerAccountId String The unique identifier for the item
PurchaseDiscountLedgerAccountId String The unique identifier for the item
ExchangeRateLossesLedgerAccountId String The unique identifier for the item
OtherPaymentLedgerAccountId String The unique identifier for the item
BankChargesLedgerAccountId String The unique identifier for the item
ProductPurchaseLedgerAccountId String The unique identifier for the item
PurchaseLedgerAccountId String The unique identifier for the item
CustomerReceiptDiscountLedgerAccountId String The unique identifier for the item
SalesDiscountLedgerAccountId String The unique identifier for the item
ExchangeRateGainsLedgerAccountId String The unique identifier for the item
VendorPaymentDiscountLedgerAccountId String The unique identifier for the item
OtherReceiptLedgerAccountId String The unique identifier for the item
BankInterestReceivedLedgerAccountId String The unique identifier for the item
ServiceSalesLedgerAccountId String The unique identifier for the item
ManagementCentreMember Bool Member of Approved Management Centres (France only)
CountryOfRegistrationId String

Countries.Id

The unique identifier for the item
RcsNumber String RCS Number (France only)
BusinessActivityTypeId String

BusinessActivityTypes.Id

The unique identifier for the item
NIBased Bool Determines if a business is located in Northern Ireland

CoaAccounts

Query CoaAccounts in Sage Business Cloud Accounting.

Table Specific Information

Query the available CoaAccounts

Select

The driver uses the Sage Accounting API to process search criteria that refer to CoaTemplateId column. The driver processes other filters client-side within the driver.

  • CoaTemplateId supports the '=' operator.

For example, the following queries are processed server side:

SELECT * FROM CoaAccounts WHERE CoaTemplateId='SMALL_BUSINESS_GB'

Columns

Name Type References Description
Id [KEY] String The unique identifier for the item
CreatedAt Datetime The datetime when the item was created
DisplayedAs String The name of the resource
FixedTaxRate Bool Indicates whether the default tax rate is fixed or may be changed
Name String The name for the COA account
LedgerAccountTypeId String The unique identifier for the item

The allowed values are SALES, OTHER_INCOME, DIRECT_EXPENSES, OVERHEADS, DEPRECIATION, CURRENT_ASSETS, FIXED_ASSETS, FUTURE_ASSETS, BANK, CURRENT_LIABILITY, FUTURE_LIABILITY, v, LINE_OF_CREDIT.

LedgerAccountGroupDisplayedAs String The name of the resource
LedgerAccountGroupLegacyId Int The legacy ID for the item
LedgerAccountGroupId String The unique identifier for the item
NominalCode Int The nominal code of the COA account
UpdatedAt Datetime The datetime when the item was last updated
LegacyId Int The legacy ID for the item
ControlName String The system control name for the COA account
LedgerAccountClassificationId String

LedgerAccountClassifications.Id

The unique identifier for the item
TaxRateId String

TaxRates.Id

The unique identifier for the item
CoaTemplateId String

CoaTemplates

Query CoaTemplates in Sage Business Cloud Accounting.

Table Specific Information

Query the available CoaTemplates

Select

The driver uses the Sage Accounting API to process search criteria that refer to CountryId column. The driver processes other filters client-side within the driver.

  • CountryId supports the '=' operator.

For example, the following queries are processed server side:

SELECT * FROM CoaTemplates WHERE CountryId='AF'

Columns

Name Type References Description
Id [KEY] String The unique identifier for the item
UpdatedAt Datetime The datetime when the item was last updated
Name String The name for the COA template
DisplayedAs String The name of the resource
CreatedAt Datetime The datetime when the item was created
CountryId String

Countries.Id

The unique identifier for the item
LegacyId Int The legacy ID for the item

CorrectiveReasonCodes

Query CorrectiveReasonCodes in Sage Business Cloud Accounting.

Table Specific Information

Query the available CorrectiveReasonCodes

Select

The driver processes all filters client-side within the driver.

Columns

Name Type References Description
Id [KEY] String The unique identifier for the item
DisplayedAs String The name of the resource
LegacyId Int The legacy ID for the item

Countries

Query Countries in Sage Business Cloud Accounting.

Table Specific Information

Query the available Countries

Select

The driver processes all filters client-side within the driver.

Columns

Name Type References Description
Id [KEY] String The unique identifier for the item
DisplayedAs String The name of the resource
LegacyId Int The legacy ID for the item

CountryGroups

Query CountryGroups in Sage Business Cloud Accounting.

Table Specific Information

Query the available CountryGroups

Select

The driver processes all filters client-side within the driver.

Columns

Name Type References Description
Id [KEY] String The unique identifier for the item
DisplayedAs String The name of the resource
LegacyId Int The legacy ID for the item

CountryOfRegistrations

Query CountryOfRegistrations in Sage Business Cloud Accounting.

Table Specific Information

Query the available CountryOfRegistrations

Select

The driver processes all filters client-side within the driver.

Columns

Name Type References Description
Id [KEY] String The unique identifier for the item
DisplayedAs String The name of the resource
LegacyId Int The legacy ID for the item

Currencies

Query Currencies in Sage Business Cloud Accounting.

Table Specific Information

Query the available Currencies

Select

The driver processes all filters client-side within the driver.

Columns

Name Type References Description
Id [KEY] String The unique identifier for the item
DisplayedAs String The name of the resource
LegacyId Int The legacy ID for the item

EuSalesDescriptions

Query EuSalesDescriptions in Sage Business Cloud Accounting.

Table Specific Information

Query the available EuSalesDescriptions

Select

The driver processes all filters client-side within the driver.

Columns

Name Type References Description
Id [KEY] String The unique identifier for the item
DisplayedAs String The name of the resource
LegacyId Int The legacy ID for the item

ExchangeRates

Query ExchangeRates in Sage Business Cloud Accounting.

Table Specific Information

Query the available ExchangeRates

Select

The driver processes all filters client-side within the driver.

Columns

Name Type References Description
UpdatedAt Datetime The datetime when the item was last updated
CurrencyId String

Currencies.Id

The unique identifier for the item
Rate String The exchange rate
DisplayedAs String Display text for the item
CreatedAt Datetime The datetime when the item was created
BaseCurrencyId String

Currencies.Id

The unique identifier for the item
InverseRate String The inverse exchange rate

JournalLine

Query JournalLine in Sage Business Cloud Accounting.

Table Specific Information

Query the available JournalLine

Select

The driver uses the Sage Accounting API to process search criteria that refer to UpdatedOrCreatedSince,DeletedSince columns. The driver processes other filters client-side within the driver.

  • UpdatedOrCreatedSince supports the '=' operator.
  • DeletedSince supports the '=' operator.

For example, the following queries are processed server side:

SELECT * FROM JournalLine WHERE UpdatedOrCreatedSince='2018-12-15'
SELECT * FROM JournalLine WHERE DeletedSince='2018-12-15'

Columns

Name Type References Description
Id [KEY] String The unique identifier for the item
Details String A description of the journal line
Cleared Bool Indicates if the journal line is cleared or not.
Credit String The credit amount of the journal line
BankReconciled Bool Indicates if the journal line is bank reconciled or not.
LedgerAccountId String

LedgerAccounts.Id

The unique identifier for the item
IncludeOnTaxReturn Bool Indicates whether the journal line should affect the tax return
TaxReconciled Bool Indicates if the journal line is tax reconciled or not.
Debit String The debit amount of the journal line
JournalsId [KEY] String Parent ID
DeletedSince Datetime

LedgerAccountClassifications

Query LedgerAccountClassifications in Sage Business Cloud Accounting.

Table Specific Information

Query the available LedgerAccountClassifications

Select

The driver uses the Sage Accounting API to process search criteria that refer to LedgerAccountTypeId column. The driver processes other filters client-side within the driver.

  • LedgerAccountTypeId supports the '=' operator.

For example, the following queries are processed server side:

SELECT * FROM LedgerAccountClassifications WHERE LedgerAccountTypeId='abc'

Columns

Name Type References Description
Id [KEY] String The unique identifier for the item
DisplayedAs String The name of the resource
LegacyId Int The legacy ID for the item
LedgerAccountTypeId String

LedgerEntries

Query LedgerEntries in Sage Business Cloud Accounting.

Table Specific Information

Query the available LedgerEntries

Select

The driver uses the Sage Accounting API to process search criteria that refer to TransactionId,LedgerAccountId,JournalCodeId,FromDate,ToDate,TransactionTypeId,UpdatedOrCreatedSince columns. The driver processes other filters client-side within the driver.

  • TransactionId supports the '=' operator.
  • LedgerAccountId supports the '=' operator.
  • JournalCodeId supports the '=' operator.
  • FromDate supports the '=' operator.
  • ToDate supports the '=' operator.
  • TransactionTypeId supports the '=' operator.
  • UpdatedOrCreatedSince supports the '=' operator.

For example, the following queries are processed server side:

SELECT * FROM LedgerEntries WHERE TransactionId='abc'
SELECT * FROM LedgerEntries WHERE LedgerAccountId='abc'
SELECT * FROM LedgerEntries WHERE JournalCodeId='abc'
SELECT * FROM LedgerEntries WHERE FromDate='2018-12-15'
SELECT * FROM LedgerEntries WHERE ToDate='2018-12-15'
SELECT * FROM LedgerEntries WHERE TransactionTypeId='abc'
SELECT * FROM LedgerEntries WHERE UpdatedOrCreatedSince='2018-12-15'

Columns

Name Type References Description
Id [KEY] String The unique identifier for the item
CreatedAt Datetime The datetime when the item was created
DisplayedAs String The name of the resource
Deleted Bool Indicates whether the ledger entry has been deleted or not
TransactionId String

Transactions.Id

The unique identifier for the item
Date Datetime The date of the ledger entry
Credit String The credit amount of the ledger entry
LedgerAccountId String

LedgerAccounts.Id

The unique identifier for the item
ContactId String

Contacts.Id

The unique identifier for the item
LinksAggregate String Links for the resource
UpdatedAt Datetime The datetime when the item was last updated
LegacyId Int The legacy ID for the item
Debit String The debit amount of the ledger entry
TaxRateId String

TaxRates.Id

The unique identifier for the item
ComponentTaxRatesAggregate String The component tax rates which make up a combined rate
Description String The ledger entry description
JournalCodeId String

JournalCodes.Id

The unique identifier for the item
FromDate Datetime
ToDate Datetime
TransactionTypeId String

LiveExchangeRates

Query LiveExchangeRates in Sage Business Cloud Accounting.

Table Specific Information

Query the available LiveExchangeRates

Select

The driver processes all filters client-side within the driver.

Columns

Name Type References Description
CurrencyId String

Currencies.Id

The unique identifier for the item
UpdatedAt Datetime
Rate String The exchange rate
DisplayedAs String Display text for the item
BaseCurrencyId String

Currencies.Id

The unique identifier for the item
InverseRate String The inverse exchange rate
RetrievedAt String

OtherPaymentLineItem

Query OtherPaymentLineItem in Sage Business Cloud Accounting.

Table Specific Information

Query the available OtherPaymentLineItem

Select

The driver uses the Sage Accounting API to process search criteria that refer to BankAccountId,ContactId,DeletedSince,FromDate,HasAttachments,ToDate,TransactionTypeId,UpdatedOrCreatedSince columns. The driver processes other filters client-side within the driver.

  • BankAccountId supports the '=' operator.
  • ContactId supports the '=' operator.
  • DeletedSince supports the '=' operator.
  • FromDate supports the '=' operator.
  • HasAttachments supports the '=' operator.
  • ToDate supports the '=' operator.
  • TransactionTypeId supports the '=' operator.
  • UpdatedOrCreatedSince supports the '=' operator.

For example, the following queries are processed server side:

SELECT * FROM OtherPaymentLineItem WHERE BankAccountId='abc'
SELECT * FROM OtherPaymentLineItem WHERE ContactId='abc'
SELECT * FROM OtherPaymentLineItem WHERE DeletedSince='2018-12-15'
SELECT * FROM OtherPaymentLineItem WHERE FromDate='2018-12-15'
SELECT * FROM OtherPaymentLineItem WHERE HasAttachments='abc'
SELECT * FROM OtherPaymentLineItem WHERE ToDate='abc'
SELECT * FROM OtherPaymentLineItem WHERE TransactionTypeId='abc'
SELECT * FROM OtherPaymentLineItem WHERE UpdatedOrCreatedSince='2018-12-15'

Columns

Name Type References Description
Id [KEY] String The unique identifier for the item
Details String The details of the payment line
DisplayedAs String The name of the resource
GstAmount String The gst or hst tax amount for the other payment
TradeOfAsset Bool Whether the line item is marked as trade of asset.
LedgerAccountId String

LedgerAccounts.Id

The unique identifier for the item
LegacyId Int The legacy ID for the item
TaxBreakdownAggregate String The tax breakdown for the payment line
IsPurchaseForResale Bool Identifies whether the line item is for resale. (Ireland only)
TaxRateId String

TaxRates.Id

The unique identifier for the item
PstAmount String The pst or qst tax amount for the other payment
TotalAmount String The total amount of the payment line
TaxAmount String The tax amount of the payment line
NetAmount String The net amount of the payment line
BaseId String Parent ID
BankAccountId String
ContactId String
DeletedSince Datetime
FromDate Datetime
HasAttachments String
ToDate String
TransactionTypeId String

PaymentMethods

Query PaymentMethods in Sage Business Cloud Accounting.

Table Specific Information

Query the available PaymentMethods

Select

The driver processes all filters client-side within the driver.

Columns

Name Type References Description
Id [KEY] String The unique identifier for the item
DisplayedAs String The name of the resource
LegacyId Int The legacy ID for the item

PurchaseCorrectiveInvoice

Query PurchaseCorrectiveInvoice in Sage Business Cloud Accounting.

Table Specific Information

Query the available PurchaseCorrectiveInvoice

Select

The driver uses the Sage Accounting API to process search criteria that refer to ShowPaymentsAllocations,ShowCorrections,Search,ContactId,StatusId,FromDate,ToDate,UpdatedOrCreatedSince,DeletedSince,HasAttachments columns. The driver processes other filters client-side within the driver.

  • ShowPaymentsAllocations supports the '=' operator.
  • ShowCorrections supports the '=' operator.
  • Search supports the '=' operator.
  • ContactId supports the '=' operator.
  • StatusId supports the '=' operator.
  • FromDate supports the '=' operator.
  • ToDate supports the '=' operator.
  • UpdatedOrCreatedSince supports the '=' operator.
  • DeletedSince supports the '=' operator.
  • HasAttachments supports the '=' operator.

For example, the following queries are processed server side:

SELECT * FROM PurchaseCorrectiveInvoice WHERE ShowPaymentsAllocations='abc'
SELECT * FROM PurchaseCorrectiveInvoice WHERE ShowCorrections='abc'
SELECT * FROM PurchaseCorrectiveInvoice WHERE Search='abc'
SELECT * FROM PurchaseCorrectiveInvoice WHERE ContactId='abc'
SELECT * FROM PurchaseCorrectiveInvoice WHERE StatusId='abc'
SELECT * FROM PurchaseCorrectiveInvoice WHERE FromDate='2018-12-15'
SELECT * FROM PurchaseCorrectiveInvoice WHERE ToDate='2018-12-15'
SELECT * FROM PurchaseCorrectiveInvoice WHERE UpdatedOrCreatedSince='2018-12-15'
SELECT * FROM PurchaseCorrectiveInvoice WHERE DeletedSince='2018-12-15'
SELECT * FROM PurchaseCorrectiveInvoice WHERE HasAttachments='abc'

Columns

Name Type References Description
Id [KEY] String The unique identifier for the item
BaseId String Parent ID
DisplayedAs String The name of the resource
Reference String The reference for the invoice
BaseCurrencyTotalAmount String The total amount of the invoice in base currency
BaseCurrencyNetAmount String The net amount of the invoice in base currency
ContactName String The name of the contact when the invoice was created
DueDate String The due date of the invoice
OriginalInvoiceDisplayedAs String The name of the resource
OriginalInvoiceLegacyId Int The legacy ID for the item
OriginalInvoiceId String The unique identifier for the item
TaxAmount String The tax amount of the invoice
LastPaid String The date of the last payment
PaymentsAllocationsTotalDiscount String The total discount of all payments and allocations
OriginalInvoiceDate String The Invoice date relating to the original invoice (Spain only)
WithholdingTaxRate String IRPF withheld Tax Rate (Spain only)
TotalAmount String The total amount of the invoice
ExchangeRate String The exchange rate for the invoice
OutstandingAmount String The outstanding amount of the invoice
ContactDisplayedAs String The name of the resource
ContactId String

Contacts.Id

The unique identifier for the item
NetAmount String The net amount of the invoice
ContactReference String The reference of the contact when the invoice was created
PaymentsAllocationsAggregate String The associated payments and allocations
BaseCurrencyWithholdingTaxAmount String IRPF withheld Tax Amount (Spain only) in the base currency
WithholdingTaxAmount String IRPF withheld Tax Amount (Spain only)
UpdatedAt Datetime The datetime when the item was last updated
LegacyId Int The legacy ID for the item
TaxanalysisAggregate String The invoice tax analysis (Optional for Spain, restricted for all other regions)
OriginalInvoiceNumber String The number relating to the original invoice (Spain only)
InverseExchangeRate String The inverse exchange rate for the credit note
TotalQuantity String The total quantity of the invoice
TransactionId String

Transactions.Id

The unique identifier for the item
StatusDisplayedAs String The name of the resource
StatusLegacyId Int The legacy ID for the item
StatusId String The unique identifier for the item
BaseCurrencyTotalGoodsAmount String The total base currency amount relating to goods for all tax rates
BaseCurrencyTotalTax String The base currency total tax amount for all tax rates
TotalRetailerTax String The total retailer tax amount for all tax rates
BaseCurrencyTotal String The total base currency amount for all tax rates
TotalGoodsAmount String The total amount relating to goods for all tax rates
TaxRatesBreakdownServicesAmount String The total amount for the tax rate relating to services
TaxRatesBreakdownBaseCurrencyNetAmount String The base currency net amount for the tax rate
TaxRatesBreakdownName String The tax rate name
TaxRatesBreakdownBaseCurrencyServicesAmount String The total base currency amount for the tax rate relating to services
TaxRatesBreakdownBaseCurrencyTaxAmount String The base currency tax amount for the tax rate
TaxRatesBreakdownBaseCurrencyTotalAmount String The base currency total amount for the tax rate
TaxRatesBreakdownGoodsAmount String The total amount for the tax rate relating to goods
TaxRatesBreakdownRetailTaxAmount String The retailer tax amount for the tax rate
TaxRatesBreakdownBaseCurrencyGoodsAmount String The total base currency amount for the tax rate relating to goods
TaxRatesBreakdownTaxRateCreatedAt Datetime The datetime when the item was created
TaxRatesBreakdownTaxRateIsCombinedRate Bool Indicates whether the tax rate is made up of component tax rates
TaxRatesBreakdownTaxRateDisplayedAs String The name of the resource
TaxRatesBreakdownTaxRateName String The name of the tax rate
TaxRatePercentageAggregate String The tax rate percentage and date ranges they apply to
TransactionTypeId String

TaxRates.Id

The unique identifier for the item
CreatedAt Datetime The datetime when the item was created
PaymentsAllocationsTotalAmount String The total amount of all payments and allocations
VoidReason String The reason the invoice was voided
TaxReconciled Bool Indicates if the artefact is tax reconciled or not.
Editable Bool Indicates whether artefact can be edited
VendorReference String The vendor reference for the invoice
CurrencyId String

Currencies.Id

The unique identifier for the item
PurchaseInvoiceLineItemAggregate String The invoice lines of the invoice
Notes String Invoice notes
TotalPaid String The total paid amount of the invoice including any payments, allocations and discounts
DeletedAt Datetime The datetime when the item was deleted
BaseCurrencyTaxAmount String The tax amount of the invoice in base currency
Date String The date of the invoice
BaseCurrencyOutstandingAmount String The outstanding amount of the invoice in base currency
ShowPaymentsAllocations String
ShowCorrections String
Search String
ContactId String
StatusId String
FromDate Datetime
ToDate Datetime
DeletedSince Datetime
HasAttachments String

PurchaseCreditNoteLineItem

Query PurchaseCreditNoteLineItem in Sage Business Cloud Accounting.

Table Specific Information

Query the available PurchaseCreditNoteLineItem

Select

The driver uses the Sage Accounting API to process search criteria that refer to ShowPaymentsAllocations,Search,ContactId,StatusId,FromDate,ToDate,UpdatedOrCreatedSince,DeletedSince,HasAttachments,ShowPaymentsAllocations columns. The driver processes other filters client-side within the driver.

  • ShowPaymentsAllocations supports the '=' operator.
  • Search supports the '=' operator.
  • ContactId supports the '=' operator.
  • StatusId supports the '=' operator.
  • FromDate supports the '=' operator.
  • ToDate supports the '=' operator.
  • UpdatedOrCreatedSince supports the '=' operator.
  • DeletedSince supports the '=' operator.
  • HasAttachments supports the '=' operator.
  • ShowPaymentsAllocations supports the '=' operator.

For example, the following queries are processed server side:

SELECT * FROM PurchaseCreditNoteLineItem WHERE ShowPaymentsAllocations='abc'
SELECT * FROM PurchaseCreditNoteLineItem WHERE Search='abc'
SELECT * FROM PurchaseCreditNoteLineItem WHERE ContactId='abc'
SELECT * FROM PurchaseCreditNoteLineItem WHERE StatusId='abc'
SELECT * FROM PurchaseCreditNoteLineItem WHERE FromDate='2018-12-15'
SELECT * FROM PurchaseCreditNoteLineItem WHERE ToDate='2018-12-15'
SELECT * FROM PurchaseCreditNoteLineItem WHERE UpdatedOrCreatedSince='2018-12-15'
SELECT * FROM PurchaseCreditNoteLineItem WHERE DeletedSince='2018-12-15'
SELECT * FROM PurchaseCreditNoteLineItem WHERE HasAttachments='abc'
SELECT * FROM PurchaseCreditNoteLineItem WHERE ShowPaymentsAllocations='abc'

Columns

Name Type References Description
Id [KEY] String The unique identifier for the item
PurchaseCreditNotesId String Parent ID
TaxRateId String

TaxRates.Id

The unique identifier for the item
ProductId String

Products.Id

The unique identifier for the item
EuGoodsServicesTypeId String The unique identifier for the item

The allowed values are GOODS, SERVICES.

NetAmount String The net amount for the credit note line
BaseCurrencyTaxAmount String The tax amount for the credit note line in base currency
BaseCurrencyTotalAmount String The total amount for the credit note line in base currency
Quantity String The quantity for the credit note line
TotalAmount String The total amount for the credit note line
LedgerAccountId String

LedgerAccounts.Id

The unique identifier for the item
IsPurchaseForResale Bool Identifies whether the line item is for resale. (Ireland Only)
BaseCurrencyTaxBreakdownAggregate String The tax breakdown for the credit note line in base currency
TaxBreakdownAggregate String The tax breakdown for the credit note line
UnitPrice String The unit price for the credit note line
BaseCurrencyUnitPrice String The unit price for the credit note line in base currency
GstAmount String The gst or hst tax amount for the credit note line
BaseCurrencyNetAmount String The net amount for the credit note line in base currency
DisplayedAs String The name of the resource
Description String The description for the credit note line
TaxAmount String The tax amount for the credit note line
PstAmount String The pst or qst tax amount for the credit note line
LegacyId Int The legacy ID for the item
TradeOfAsset Bool Whether the line item is marked as trade of asset.
UnitPriceIncludesTax Bool Defines whether the unit price includes tax
ServiceId String

Services.Id

The unique identifier for the item
ShowPaymentsAllocations String
Search String
ContactId String
StatusId String
FromDate Datetime
ToDate Datetime
DeletedSince Datetime
HasAttachments String
ShowPaymentsAllocations String

PurchaseInvoiceLineItem

Query PurchaseInvoiceLineItem in Sage Business Cloud Accounting.

Table Specific Information

Query the available PurchaseInvoiceLineItem

Select

The driver uses the Sage Accounting API to process search criteria that refer to ShowPaymentsAllocations,HasAttachments,FromDate,ContactId,StatusId,ShowCorrections,DeletedSince,Search,ToDate columns. The driver processes other filters client-side within the driver.

  • ShowPaymentsAllocations supports the '=' operator.
  • HasAttachments supports the '=' operator.
  • FromDate supports the '=' operator.
  • ContactId supports the '=' operator.
  • StatusId supports the '=' operator.
  • ShowCorrections supports the '=' operator.
  • DeletedSince supports the '=' operator.
  • Search supports the '=' operator.
  • ToDate supports the '=' operator.

For example, the following queries are processed server side:

SELECT * FROM PurchaseInvoiceLineItem WHERE ShowPaymentsAllocations='abc'
SELECT * FROM PurchaseInvoiceLineItem WHERE HasAttachments='abc'
SELECT * FROM PurchaseInvoiceLineItem WHERE FromDate='2018-12-15'
SELECT * FROM PurchaseInvoiceLineItem WHERE ContactId='abc'
SELECT * FROM PurchaseInvoiceLineItem WHERE StatusId='abc'
SELECT * FROM PurchaseInvoiceLineItem WHERE ShowCorrections='abc'
SELECT * FROM PurchaseInvoiceLineItem WHERE DeletedSince='2018-12-15'
SELECT * FROM PurchaseInvoiceLineItem WHERE Search='abc'
SELECT * FROM PurchaseInvoiceLineItem WHERE ToDate='2018-12-15'

Columns

Name Type References Description
Id [KEY] String The unique identifier for the item
PurchaseInvoicesId String Parent ID
TaxRateDisplayedAs String The name of the resource
TaxRateLegacyId Int The legacy ID for the item
TaxRateId String The unique identifier for the item
ProductId String

Products.Id

The unique identifier for the item
EuGoodsServicesTypeId String The unique identifier for the item

The allowed values are GOODS, SERVICES.

NetAmount String The net amount for the invoice line
PurchaseInvoicesCurrencyTaxAmount String The tax amount for the invoice line in PurchaseInvoices currency
PurchaseInvoicesCurrencyTotalAmount String The total amount for the invoice line in PurchaseInvoices currency
Quantity String The quantity for the invoice line
TotalAmount String The total amount for the invoice line
LedgerAccountId String

LedgerAccounts.Id

The unique identifier for the item
IsPurchaseForResale Bool Identifies whether the line item is for resale. (Ireland Only)
PurchaseInvoicesCurrencyTaxBreakdownAggregate String The tax breakdown for the invoice line in PurchaseInvoices currency
TaxBreakdownAggregate String The tax breakdown for the invoice line
UnitPrice String The unit price for the invoice line
PurchaseInvoicesCurrencyUnitPrice String The unit price for the invoice line in PurchaseInvoices currency
GstAmount String The gst or hst tax amount for the invoice line
PurchaseInvoicesCurrencyNetAmount String The net amount for the invoice line in PurchaseInvoices currency
DisplayedAs String The name of the resource
Description String The description for the invoice line
TaxAmount String The tax amount for the invoice line
PstAmount String The pst or qst tax amount for the invoice line
LegacyId Int The legacy ID for the item
TradeOfAsset Bool Whether the line item is marked as trade of asset.
UnitPriceIncludesTax Bool Defines whether the unit price includes tax
ServiceId String

Services.Id

The unique identifier for the item
ShowPaymentsAllocations String
HasAttachments String
FromDate Datetime
ContactId String
StatusId String
ShowCorrections String
DeletedSince Datetime
Search String
ToDate Datetime

SalesCorrectiveInvoice

Query SalesCorrectiveInvoice in Sage Business Cloud Accounting.

Table Specific Information

Query the available SalesCorrectiveInvoice

Select

The driver uses the Sage Accounting API to process search criteria that refer to ShowPaymentsAllocations,ShowCorrections,MarkAsSent,Search,ContactId,StatusId,FromDate,ToDate,UpdatedOrCreatedSince,DeletedSince,HasAttachments columns. The driver processes other filters client-side within the driver.

  • ShowPaymentsAllocations supports the '=' operator.
  • ShowCorrections supports the '=' operator.
  • MarkAsSent supports the '=' operator.
  • Search supports the '=' operator.
  • ContactId supports the '=' operator.
  • StatusId supports the '=' operator.
  • FromDate supports the '=' operator.
  • ToDate supports the '=' operator.
  • UpdatedOrCreatedSince supports the '=' operator.
  • DeletedSince supports the '=' operator.
  • HasAttachments supports the '=' operator.

For example, the following queries are processed server side:

SELECT * FROM SalesCorrectiveInvoice WHERE ShowPaymentsAllocations='abc'
SELECT * FROM SalesCorrectiveInvoice WHERE ShowCorrections='abc'
SELECT * FROM SalesCorrectiveInvoice WHERE MarkAsSent='abc'
SELECT * FROM SalesCorrectiveInvoice WHERE Search='abc'
SELECT * FROM SalesCorrectiveInvoice WHERE ContactId='abc'
SELECT * FROM SalesCorrectiveInvoice WHERE StatusId='abc'
SELECT * FROM SalesCorrectiveInvoice WHERE FromDate='2018-12-15'
SELECT * FROM SalesCorrectiveInvoice WHERE ToDate='2018-12-15'
SELECT * FROM SalesCorrectiveInvoice WHERE UpdatedOrCreatedSince='2018-12-15'
SELECT * FROM SalesCorrectiveInvoice WHERE DeletedSince='2018-12-15'
SELECT * FROM SalesCorrectiveInvoice WHERE HasAttachments='abc'

Columns

Name Type References Description
Id [KEY] String The unique identifier for the item
SalesArtefactAddressId String Parent ID
DisplayedAs String The name of the resource
Reference String The reference for the invoice
BaseCurrencyTotalAmount String The total amount of the invoice in base currency
MainAddressId String

Addresses.Id

The unique identifier for the item
BaseCurrencyNetAmount String The net amount of the invoice in base currency
BaseCurrencyShippingTaxBreakdownAggregate String The shipping tax breakdown for the invoice in base currency
ContactName String The name of the contact when the invoice was created
SentByEmail Bool Indicates whether the invoice has been emailed
ShippingTaxBreakdownAggregate String The shipping tax breakdown for the invoice
DueDate Datetime The due date of the invoice
TotalDiscountAmount String The discount amount on the invoice
LinksAggregate String Links for the resource
OriginalInvoiceDisplayedAs String The name of the resource
OriginalInvoiceLegacyId Int The legacy ID for the item
OriginalInvoiceId String The unique identifier for the item
TaxAmount String The tax amount of the invoice
LastPaid String The date of the last payment
PaymentsAllocationsTotalDiscount String The total discount of all payments and allocations
OriginalInvoiceDate String The Invoice date relating to the original invoice
WithholdingTaxRate String IRPF Witheld Tax Rate (Spain only)
TotalAmount String The total amount of the invoice
ExchangeRate String The exchange rate for the invoice
InvoiceNumberPrefix String The invoice number prefix
OutstandingAmount String The outstanding amount of the invoice
ContactId String

Contacts.Id

The unique identifier for the item
NetAmount String The net amount of the invoice
ContactReference String The reference of the contact when the invoice was created
TaxCalculationMethod String The tax calculation method, if applicable, for this sales invoice, returns invoice, cash or retailer.
TermsAndConditions String Invoice terms and conditions
PaymentsAllocationsAggregate String The associated payments and allocations
ShippingTaxRateId String

TaxRates.Id

The unique identifier for the item
Sent Bool Indicates whether the invoice has been sent
DeliveryAddressFreeForm String The free-form delivery address of the invoice
BaseCurrencyWithholdingTaxAmount String IRPF Witheld Tax Amount (Spain only) in the base currency
DeliveryAddressId String

Addresses.Id

The unique identifier for the item
WithholdingTaxAmount String IRPF Witheld Tax Amount (Spain only)
UpdatedAt Datetime The datetime when the item was last updated
LegacyId Int The legacy ID for the item
TaxAnalysisAggregate String The invoice tax analysis
OriginalInvoiceNumber String The number relating to the original invoice
InverseExchangeRate String The inverse exchange rate for the invoice
BaseCurrencyShippingTotalAmount String The total shipping amount in base currency
TransactionId String

Transactions.Id

The unique identifier for the item
TotalQuantity String The total quantity of the invoice
StatusDisplayedAs String The name of the resource
StatusLegacyId Int The legacy ID for the item
StatusId String The unique identifier for the item
DetailedTaxAnalysisBaseCurrencyTotalGoodsAmount String The total base currency amount relating to goods for all tax rates
DetailedTaxAnalysisBaseCurrencyTotalTax String The base currency total tax amount for all tax rates
DetailedTaxAnalysisTotalRetailerTax String The total retailer tax amount for all tax rates
DetailedTaxAnalysisBaseCurrencyTotal String The total base currency amount for all tax rates
DetailedTaxAnalysisTotalGoodsAmount String The total amount relating to goods for all tax rates
DetailedTaxAnalysisTotalNet String The total net amount for all tax rates
DetailedTaxAnalysisBaseCurrencyTotalServicesAmount String The total base currency amount relating to services for all tax rates
DetailedTaxAnalysisBaseCurrencyTotalNet String The base currency total net amount for all tax rates
DetailedTaxAnalysisTotalServicesAmount String The total amount relating to services for all tax rates
DetailedTaxAnalysisTotal String The total amount for all tax rates
DetailedTaxAnalysisTotalTax String The total tax amount for all tax rates
TransactionTypeId String

TransactionTypes.Id

The unique identifier for the item
BaseCurrencyShippingTaxAmount String The tax shipping amount in base currency
ShippingTaxAmount String The tax shipping amount. NOTE: This is not required for POST/PUT requests as the shipping tax is calculated based on the shipping_net_amount and the shipping_tax_rate.
CreatedAt Datetime The datetime when the item was created
PaymentsAllocationsTotalAmount String The total amount of all payments and allocations
VoidReason String The reason the invoice was voided
TaxReconciled Bool Indicates if the artefact is tax reconciled or not.
Editable Bool Indicates whether artefact can be edited
ShippingTotalAmount String The total shipping amount
BaseCurrencyShippingNetAmount String The net shipping amount in base currency
ShippingNetAmount String The net shipping amount
CurrencyId String

Currencies.Id

The unique identifier for the item
Details String The corrective invoice details
SalesInvoiceLineItemAggregate String The invoice lines of the invoice
CorrectiveReasonCodeId String

CorrectiveReasonCodes.Id

The unique identifier for the item
InvoiceNumber String The generated invoice number
Notes String Invoice notes
MainAddressFreeForm String The free-form main address of the invoice
TotalPaid String The total paid amount of the invoice including any payments, allocations and discounts
DeletedAt Datetime The datetime when the item was deleted
Date String The date of the invoice
BaseCurrencyTaxAmount String The tax amount of the invoice in base currency
BaseCurrencyOutstandingAmount String The outstanding amount of the invoice in base currency
BaseCurrencyTotalDiscountAmount String The discount amount on the invoice in base currency
ShowPaymentsAllocations String
ShowCorrections String
MarkAsSent String
Search String
ContactId String
StatusId String
FromDate Datetime
ToDate Datetime
DeletedSince Datetime
HasAttachments String

SalesCreditNoteLineItem

Query SalesCreditNoteLineItem in Sage Business Cloud Accounting.

Table Specific Information

Query the available SalesCreditNoteLineItem

Select

The driver uses the Sage Accounting API to process search criteria that refer to ShowPaymentsAllocations,MarkAsSent,ShowPaymentsAllocations,Search,ContactId,StatusId,FromDate,ToDate,UpdatedOrCreatedSince,DeletedSince,HasAttachments columns. The driver processes other filters client-side within the driver.

  • ShowPaymentsAllocations supports the '=' operator.
  • MarkAsSent supports the '=' operator.
  • ShowPaymentsAllocations supports the '=' operator.
  • Search supports the '=' operator.
  • ContactId supports the '=' operator.
  • StatusId supports the '=' operator.
  • FromDate supports the '=' operator.
  • ToDate supports the '=' operator.
  • UpdatedOrCreatedSince supports the '=' operator.
  • DeletedSince supports the '=' operator.
  • HasAttachments supports the '=' operator.

For example, the following queries are processed server side:

SELECT * FROM SalesCreditNoteLineItem WHERE ShowPaymentsAllocations='abc'
SELECT * FROM SalesCreditNoteLineItem WHERE MarkAsSent='abc'
SELECT * FROM SalesCreditNoteLineItem WHERE ShowPaymentsAllocations='abc'
SELECT * FROM SalesCreditNoteLineItem WHERE Search='abc'
SELECT * FROM SalesCreditNoteLineItem WHERE ContactId='abc'
SELECT * FROM SalesCreditNoteLineItem WHERE StatusId='abc'
SELECT * FROM SalesCreditNoteLineItem WHERE FromDate='2018-12-15'
SELECT * FROM SalesCreditNoteLineItem WHERE ToDate='2018-12-15'
SELECT * FROM SalesCreditNoteLineItem WHERE UpdatedOrCreatedSince='2018-12-15'
SELECT * FROM SalesCreditNoteLineItem WHERE DeletedSince='2018-12-15'
SELECT * FROM SalesCreditNoteLineItem WHERE HasAttachments='abc'

Columns

Name Type References Description
Id [KEY] String The unique identifier for the item
SalesCreditNotesId String Parent ID
TaxRateDisplayedAs String The name of the resource
TaxRateLegacyId Int The legacy ID for the item
TaxRateId String The unique identifier for the item
ProductId String

Products.Id

The unique identifier for the item
EuGoodsServicesTypeId String The unique identifier for the item

The allowed values are GOODS, SERVICES.

NetAmount String The net amount for the invoice line
BaseCurrencyTaxAmount String The tax amount for the invoice line in base currency
BaseCurrencyTotalAmount String The total amount for the invoice line in base currency
DiscountPercentage String The discount percentage for the invoice line
Quantity String The quantity for the invoice line
TotalAmount String The total amount for the invoice line
BaseCurrencyDiscountAmount String The discount amount for the invoice line in base currency
LedgerAccountId String

LedgerAccounts.Id

The unique identifier for the item
BaseCurrencyTaxBreakdownAggregate String The tax breakdown for the invoice line in base currency
TaxbreakdownAggregate String The tax breakdown for the invoice line
UnitPrice String The unit price for the invoice line
BaseCurrencyUnitPrice String The unit price for the invoice line in base currency
DiscountAmount String The discount amount for the invoice line
BaseCurrencyNetAmount String The net amount for the invoice line in base currency
DisplayedAs String The name of the resource
Description String The description for the invoice line
TaxAmount String The tax amount for the invoice line
LegacyId Int The legacy ID for the item
TradeOfAsset Bool Whether the line item is marked as trade of asset.
EuSalesDescriptionId String

EuSalesDescriptions.Id

The unique identifier for the item
UnitPriceIncludesTax Bool Defines whether the unit price includes tax
ServiceId String

Services.Id

The unique identifier for the item
ShowPaymentsAllocations String
MarkAsSent String
ShowPaymentsAllocations String
Search String
ContactId String
StatusId String
FromDate Datetime
ToDate Datetime
DeletedSince Datetime
HasAttachments String

SalesQuoteLineItem

Query SalesEstimatesQuoteLineItem in Sage Business Cloud Accounting.

Table Specific Information

Query the available SalesQuoteLineItem

Select

The driver uses the Sage Accounting API to process search criteria that refer to ContactId,Search,StatusId,FromDate,ToDate,UpdatedOrCreatedSince,HasAttachments columns. The driver processes other filters client-side within the driver.

  • ContactId supports the '=' operator.
  • Search supports the '=' operator.
  • StatusId supports the '=' operator.
  • FromDate supports the '=' operator.
  • ToDate supports the '=' operator.
  • UpdatedOrCreatedSince supports the '=' operator.
  • HasAttachments supports the '=' operator.

For example, the following queries are processed server side:

SELECT * FROM SalesQuoteLineItem WHERE ContactId='abc'
SELECT * FROM SalesQuoteLineItem WHERE Search='abc'
SELECT * FROM SalesQuoteLineItem WHERE StatusId='abc'
SELECT * FROM SalesQuoteLineItem WHERE FromDate='2018-12-15'
SELECT * FROM SalesQuoteLineItem WHERE ToDate='2018-12-15'
SELECT * FROM SalesQuoteLineItem WHERE UpdatedOrCreatedSince='2018-12-15'
SELECT * FROM SalesQuoteLineItem WHERE HasAttachments='abc'

Columns

Name Type References Description
Id [KEY] String The unique identifier for the item
QuoteStatusId String Parent ID
TaxRateId String

TaxRates.Id

The unique identifier for the item
ProductId String

Products.Id

The unique identifier for the item
EuGoodsServicesTypeId String The unique identifier for the item

The allowed values are GOODS, SERVICES.

NetAmount String The net amount for the quote line
BaseCurrencyTaxAmount String The tax amount for the quote line in base currency
BaseCurrencyTotalAmount String The total amount for the quote line in base currency
DiscountPercentage String The discount percentage for the quote line
Quantity String The quantity for the quote line
TotalAmount String The total amount for the quote line
BaseCurrencyDiscountAmount String The discount amount for the quote line in base currency
LedgerAccountId String

LedgerAccounts.Id

The unique identifier for the item
BaseCurrencyTaxBreakdownAggregate String The tax breakdown for the quote line in base currency
TaxBreakdownAggregate String The tax breakdown for the quote line
UnitPrice String The unit price for the quote line
BaseCurrencyUnitPrice String The unit price for the quote line in base currency
DiscountAmount String The discount amount for the quote line
BaseCurrencyNetAmount String The net amount for the quote line in base currency
DisplayedAs String The name of the resource
Description String The description for the quote line
TaxAmount String The tax amount for the quote line
LegacyId Int The legacy ID for the item
TradeOfAsset Bool Whether the line item is marked as trade of asset.
EuSalesDescriptionId String

EuSalesDescriptions.Id

The unique identifier for the item
UnitPriceIncludesTax Bool Defines whether the unit price includes tax
ServiceId String

Services.Id

The unique identifier for the item
ContactId String
Search String
StatusId String
FromDate Datetime
ToDate Datetime
HasAttachments String

Query SalesQuoteLineItem in Sage Business Cloud Accounting.

Table Specific Information

Query the available SalesQuoteLineItem

Select

The driver uses the Sage Accounting API to process search criteria that refer to ContactId,Search,StatusId,FromDate,ToDate,UpdatedOrCreatedSince,HasAttachments columns. The driver processes other filters client-side within the driver.

  • ContactId supports the '=' operator.
  • Search supports the '=' operator.
  • StatusId supports the '=' operator.
  • FromDate supports the '=' operator.
  • ToDate supports the '=' operator.
  • UpdatedOrCreatedSince supports the '=' operator.
  • HasAttachments supports the '=' operator.

For example, the following queries are processed server side:

SELECT * FROM SalesQuoteLineItem WHERE ContactId='abc'
SELECT * FROM SalesQuoteLineItem WHERE Search='abc'
SELECT * FROM SalesQuoteLineItem WHERE StatusId='abc'
SELECT * FROM SalesQuoteLineItem WHERE FromDate='2018-12-15'
SELECT * FROM SalesQuoteLineItem WHERE ToDate='2018-12-15'
SELECT * FROM SalesQuoteLineItem WHERE UpdatedOrCreatedSince='2018-12-15'
SELECT * FROM SalesQuoteLineItem WHERE HasAttachments='abc'

Columns

Name Type References Description
Id [KEY] String The unique identifier for the item
QuoteStatusId String Parent ID
TaxRateId String

TaxRates.Id

The unique identifier for the item
ProductId String

Products.Id

The unique identifier for the item
EuGoodsServicesTypeId String The unique identifier for the item

The allowed values are GOODS, SERVICES.

NetAmount String The net amount for the quote line
BaseCurrencyTaxAmount String The tax amount for the quote line in base currency
BaseCurrencyTotalAmount String The total amount for the quote line in base currency
DiscountPercentage String The discount percentage for the quote line
Quantity String The quantity for the quote line
TotalAmount String The total amount for the quote line
BaseCurrencyDiscountAmount String The discount amount for the quote line in base currency
LedgerAccountId String

LedgerAccounts.Id

The unique identifier for the item
BaseCurrencyTaxBreakdownAggregate String The tax breakdown for the quote line in base currency
TaxBreakdownAggregate String The tax breakdown for the quote line
UnitPrice String The unit price for the quote line
BaseCurrencyUnitPrice String The unit price for the quote line in base currency
DiscountAmount String The discount amount for the quote line
BaseCurrencyNetAmount String The net amount for the quote line in base currency
DisplayedAs String The name of the resource
Description String The description for the quote line
TaxAmount String The tax amount for the quote line
LegacyId Int The legacy ID for the item
TradeOfAsset Bool Whether the line item is marked as trade of asset.
EuSalesDescriptionId String

EuSalesDescriptions.Id

The unique identifier for the item
UnitPriceIncludesTax Bool Defines whether the unit price includes tax
ServiceId String

Services.Id

The unique identifier for the item
ContactId String

Contacts.Id

Search String
StatusId String
FromDate Datetime
ToDate Datetime
HasAttachments String

SalesInvoiceLineItem

Query SalesInvoiceLineItem in Sage Business Cloud Accounting.

Table Specific Information

Query the available SalesInvoiceLineItem

Select

The driver uses the Sage Accounting API to process search criteria that refer to ContactId,StatusId,FromDate,ToDate,UpdatedOrCreatedSince,DeletedSince,HasAttachments,ShowPaymentsAllocations,Search columns. The driver processes other filters client-side within the driver.

  • ContactId supports the '=' operator.
  • StatusId supports the '=' operator.
  • FromDate supports the '=' operator.
  • ToDate supports the '=' operator.
  • UpdatedOrCreatedSince supports the '=' operator.
  • DeletedSince supports the '=' operator.
  • HasAttachments supports the '=' operator.
  • ShowPaymentsAllocations supports the '=' operator.
  • Search supports the '=' operator.

For example, the following queries are processed server side:

SELECT * FROM SalesInvoiceLineItem WHERE ContactId='abc'
SELECT * FROM SalesInvoiceLineItem WHERE StatusId='abc'
SELECT * FROM SalesInvoiceLineItem WHERE FromDate='2018-12-15'
SELECT * FROM SalesInvoiceLineItem WHERE ToDate='2018-12-15'
SELECT * FROM SalesInvoiceLineItem WHERE UpdatedOrCreatedSince='2018-12-15'
SELECT * FROM SalesInvoiceLineItem WHERE DeletedSince='2018-12-15'
SELECT * FROM SalesInvoiceLineItem WHERE HasAttachments='abc'
SELECT * FROM SalesInvoiceLineItem WHERE ShowPaymentsAllocations='abc'
SELECT * FROM SalesInvoiceLineItem WHERE Search='abc'

Columns

Name Type References Description
Id [KEY] String The unique identifier for the item
SalesInvoicesId String Parent ID
TaxRateId String

TaxRates.Id

The unique identifier for the item
ProductId String

Products.Id

The unique identifier for the item
EuGoodsServicesTypeId String The unique identifier for the item

The allowed values are GOODS, SERVICES.

NetAmount String The net amount for the invoice line
SalesInvoicesCurrencyTaxAmount String The tax amount for the invoice line in base currency
SalesInvoicesCurrencyTotalAmount String The total amount for the invoice line in base currency
DiscountPercentage String The discount percentage for the invoice line
Quantity String The quantity for the invoice line
TotalAmount String The total amount for the invoice line
SalesInvoicesCurrencyDiscountAmount String The discount amount for the invoice line in base currency
LedgerAccountId String

LedgerAccounts.Id

The unique identifier for the item
BaseCurrencyTaxBreakdownAggregate String The tax breakdown for the invoice line in base currency
TaxBreakdownAggregate String The tax breakdown for the invoice line
UnitPrice String The unit price for the invoice line
SalesInvoicesCurrencyUnitPrice String The unit price for the invoice line in base currency
DiscountAmount String The discount amount for the invoice line
SalesInvoicesCurrencyNetAmount String The net amount for the invoice line in base currency
DisplayedAs String The name of the resource
Description String The description for the invoice line
TaxAmount String The tax amount for the invoice line
LegacyId Int The legacy ID for the item
TradeOfAsset Bool Whether the line item is marked as trade of asset.
EuSalesDescriptionDisplayedAs String The name of the resource
EuSalesDescriptionLegacyId Int The legacy ID for the item
EuSalesDescriptionId String The unique identifier for the item
UnitPriceIncludesTax Bool Defines whether the unit price includes tax
ServiceId String

Services.Id

The unique identifier for the item
ContactId String

Contacts.Id

StatusId String
FromDate Datetime
ToDate Datetime
DeletedSince Datetime
HasAttachments String
ShowPaymentsAllocations String
Search String

TaxOffices

Query TaxOffices in Sage Business Cloud Accounting.

Table Specific Information

Query the available TaxOffices

Select

The driver processes all filters client-side within the driver.

Columns

Name Type References Description
Id [KEY] String The unique identifier for the item
OfficeNumber String The tax office number
Name String The tax office name
DisplayedAs String The name of the resource
LegacyId Int The legacy ID for the item

TaxReturnFrequencies

Query TaxReturnFrequencies in Sage Business Cloud Accounting.

Table Specific Information

Query the available TaxReturnFrequencies

Select

The driver uses the Sage Accounting API to process search criteria that refer to TaxTypeId column. The driver processes other filters client-side within the driver.

  • TaxTypeId supports the '=' operator.

For example, the following queries are processed server side:

SELECT * FROM TaxReturnFrequencies WHERE TaxTypeId='abc'

Columns

Name Type References Description
Id [KEY] String The unique identifier for the item
DisplayedAs String The name of the resource
LegacyId Int The legacy ID for the item
TaxTypeId String

TaxSchemes

Query TaxSchemes in Sage Business Cloud Accounting.

Table Specific Information

Query the available TaxSchemes

Select

The driver processes all filters client-side within the driver.

Columns

Name Type References Description
Id [KEY] String The unique identifier for the item
DisplayedAs String The name of the resource
LegacyId Int The legacy ID for the item

Transactions

Query Transactions in Sage Business Cloud Accounting.

Table Specific Information

Query the available Transactions

Select

The driver uses the Sage Accounting API to process search criteria that refer to TransactionTypeId,UpdatedOrCreatedSince,FromDate,ToDate,UpdatedFromDate,UpdatedToDate,HasAttachments columns. The driver processes other filters client-side within the driver.

  • TransactionTypeId supports the '=' operator.
  • UpdatedOrCreatedSince supports the '=' operator.
  • FromDate supports the '=' operator.
  • ToDate supports the '=' operator.
  • UpdatedFromDate supports the '=' operator.
  • UpdatedToDate supports the '=' operator.
  • HasAttachments supports the '=' operator.

For example, the following queries are processed server side:

SELECT * FROM Transactions WHERE TransactionTypeId='abc'
SELECT * FROM Transactions WHERE UpdatedOrCreatedSince='2018-12-15'
SELECT * FROM Transactions WHERE FromDate='2018-12-15'
SELECT * FROM Transactions WHERE ToDate='2018-12-15'
SELECT * FROM Transactions WHERE UpdatedFromDate='2018-12-15'
SELECT * FROM Transactions WHERE UpdatedToDate='2018-12-15'
SELECT * FROM Transactions WHERE HasAttachments='abc'

Columns

Name Type References Description
Id [KEY] String The unique identifier for the item
CreatedAt Datetime The datetime when the item was created
DisplayedAs String The name of the resource
NumberOfAttachments String The number of attachments related to the transaction
TransactionTypeId String

TransactionTypes.Id

The unique identifier for the item
Deleted Bool Indicates whether the transaction has been deleted
Date Datetime The date of the transaction
Reference String The transaction reference
ContactId String

Contacts.Id

The unique identifier for the item
UpdatedAt Datetime The datetime when the item was last updated
LegacyId Int The legacy ID for the item
Total String The transaction total
LinksAggregate String Links for the resource
OriginDisplayedAs String The name of the resource
OriginLegacyId Int The legacy ID for the item
OriginId String The unique identifier for the item
AuditTrailId String The original entity that generated the transaction
FromDate Datetime
ToDate Datetime
UpdatedFromDate Datetime
UpdatedToDate Datetime
HasAttachments String

TransactionTypes

Query TransactionTypes in Sage Business Cloud Accounting.

Table Specific Information

Query the available TransactionTypes

Select

The driver uses the Sage Accounting API to process search criteria that refer to ValidForBusiness column. The driver processes other filters client-side within the driver.

  • ValidForBusiness supports the '=' operator.

For example, the following queries are processed server side:

SELECT * FROM TransactionTypes WHERE ValidForBusiness=true'

Columns

Name Type References Description
Id [KEY] String The unique identifier for the item
DisplayedAs String The name of the resource
LegacyId Int The legacy ID for the item
ValidForBusiness Bool

UnallocatedArtefacts

Query UnallocatedArtefacts in Sage Business Cloud Accounting.

Table Specific Information

Query the available UnallocatedArtefacts

Select

The driver uses the Sage Accounting API to process search criteria that refer to ContactId column. The driver processes other filters client-side within the driver.

  • ContactId supports the '=' operator.

For example, the following queries are processed server side:

SELECT * FROM UnallocatedArtefacts WHERE ContactId='abc'

Columns

Name Type References Description
Id [KEY] String The unique identifier for the item
LinksAggregate String Links for the resource
DisplayedAs String The name of the resource
LegacyId Int The legacy ID for the item
ContactId String

Stored Procedures

NOTE: Stored procedures are not currently supported. See the above note for details.

Stored procedures* are available to complement the data available from the Data Model. It may be necessary to update data available from a view using a stored procedure* because the data does not provide for direct, table-like, two-way updates. In these situations, the retrieval of the data is done using the appropriate view or table, while the update is done by calling a stored procedure. Stored procedures* take a list of parameters and return back a dataset that contains the collection of tuples that constitute the response.

Jitterbit Connector for Sage Business Cloud Accounting Stored Procedures

Name Description
GetOAuthAccessToken Gets an authentication token from sagebca.
GetOAuthAuthorizationURL Gets the authorization URL that must be opened separately by the user to grant access to your application. Only needed when developing Web apps. You will request the OAuthAccessToken from this URL.
RefreshOAuthAccessToken Refreshes the OAuth access token used for authentication with sagebca.

GetOAuthAccessToken

Gets an authentication token from sagebca.

Input

Name Type Required Description
AuthMode String False The type of authentication mode to use. Select App for getting authentication tokens via a desktop app. Select Web for getting authentication tokens via a Web app.

The allowed values are APP, WEB.

The default value is WEB.

Scope String False The scope or permissions you are requesting.
CallbackUrl String False The URL the user will be redirected to after authorizing your application. This value must match the Redirect URL you have specified in the Sage app settings. Only needed when the Authmode parameter is Web.
State String False An arbitrary string of your choosing that is returned to your app; a successful roundtrip of this string helps ensure that your app initiated the request.
Verifier String False The verifier returned from SageBusinessCloudAccounting after the user has authorized your app to have access to their data. This value will be returned as a parameter to the callback URL.

Result Set Columns

Name Type Description
OAuthAccessToken String The access token used for communication with sagebca.
OAuthRefreshToken String The OAuth refresh token. This is the same as the access token in the case of sagebca.
ExpiresIn String The remaining lifetime on the access token. A -1 denotes that it will not expire.

GetOAuthAuthorizationURL

Gets the authorization URL that must be opened separately by the user to grant access to your application. Only needed when developing Web apps. You will request the OAuthAccessToken from this URL.

Input

Name Type Required Description
CallbackUrl String False The URL that Sage Business Cloud Accounting will return to after the user has authorized your app.
Scope String False The scope or permissions you are requesting.

The allowed values are readonly, full_access.

State String False The same value for state that you sent when you requested the authorization code.

Result Set Columns

Name Type Description
URL String The authorization URL, entered into a Web browser to obtain the verifier token and authorize your app.

RefreshOAuthAccessToken

Refreshes the OAuth access token used for authentication with sagebca.

Input

Name Type Required Description
OAuthRefreshToken String True The refresh token returned with the previous access token.

Result Set Columns

Name Type Description
OAuthAccessToken String The authentication token returned from sagebca. This can be used in subsequent calls to other operations for this particular service.
OAuthRefreshToken String This is the same as the access token.
ExpiresIn String The remaining lifetime on the access token.

System Tables

You can query the system tables described in this section to access schema information, information on data source functionality, and batch operation statistics.

Schema Tables

The following tables return database metadata for Sage Business Cloud Accounting:

Data Source Tables

The following tables return information about how to connect to and query the data source:

  • sys_connection_props: Returns information on the available connection properties.
  • sys_sqlinfo: Describes the SELECT queries that the connector can offload to the data source.

Query Information Tables

The following table returns query statistics for data modification queries:

  • sys_identity: Returns information about batch operations or single updates.

sys_catalogs

Lists the available databases.

The following query retrieves all databases determined by the connection string:

SELECT * FROM sys_catalogs

Columns

Name Type Description
CatalogName String The database name.

sys_schemas

Lists the available schemas.

The following query retrieves all available schemas:

SELECT * FROM sys_schemas

Columns

Name Type Description
CatalogName String The database name.
SchemaName String The schema name.

sys_tables

Lists the available tables.

The following query retrieves the available tables and views:

SELECT * FROM sys_tables

Columns

Name Type Description
CatalogName String The database containing the table or view.
SchemaName String The schema containing the table or view.
TableName String The name of the table or view.
TableType String The table type (table or view).
Description String A description of the table or view.
IsUpdateable Boolean Whether the table can be updated.

sys_tablecolumns

Describes the columns of the available tables and views.

The following query returns the columns and data types for the SampleTable_1 table:

SELECT ColumnName, DataTypeName FROM sys_tablecolumns WHERE TableName='SampleTable_1' 

Columns

Name Type Description
CatalogName String The name of the database containing the table or view.
SchemaName String The schema containing the table or view.
TableName String The name of the table or view containing the column.
ColumnName String The column name.
DataTypeName String The data type name.
DataType Int32 An integer indicating the data type. This value is determined at run time based on the environment.
Length Int32 The storage size of the column.
DisplaySize Int32 The designated column's normal maximum width in characters.
NumericPrecision Int32 The maximum number of digits in numeric data. The column length in characters for character and date-time data.
NumericScale Int32 The column scale or number of digits to the right of the decimal point.
IsNullable Boolean Whether the column can contain null.
Description String A brief description of the column.
Ordinal Int32 The sequence number of the column.
IsAutoIncrement String Whether the column value is assigned in fixed increments.
IsGeneratedColumn String Whether the column is generated.
IsHidden Boolean Whether the column is hidden.
IsArray Boolean Whether the column is an array.

sys_procedures

Lists the available stored procedures.

The following query retrieves the available stored procedures:

SELECT * FROM sys_procedures

Columns

Name Type Description
CatalogName String The database containing the stored procedure.
SchemaName String The schema containing the stored procedure.
ProcedureName String The name of the stored procedure.
Description String A description of the stored procedure.
ProcedureType String The type of the procedure, such as PROCEDURE or FUNCTION.

sys_procedureparameters

Describes stored procedure* parameters.

The following query returns information about all of the input parameters for the SampleProcedure stored procedure:

SELECT * FROM sys_procedureparameters WHERE ProcedureName='SampleProcedure' AND Direction=1 OR Direction=2

Columns

Name Type Description
CatalogName String The name of the database containing the stored procedure.
SchemaName String The name of the schema containing the stored procedure.
ProcedureName String The name of the stored procedure* containing the parameter.
ColumnName String The name of the stored procedure* parameter.
Direction Int32 An integer corresponding to the type of the parameter: input (1), input/output (2), or output(4). input/output type parameters can be both input and output parameters.
DataTypeName String The name of the data type.
DataType Int32 An integer indicating the data type. This value is determined at run time based on the environment.
Length Int32 The number of characters allowed for character data. The number of digits allowed for numeric data.
NumericPrecision Int32 The maximum precision for numeric data. The column length in characters for character and date-time data.
NumericScale Int32 The number of digits to the right of the decimal point in numeric data.
IsNullable Boolean Whether the parameter can contain null.
IsRequired Boolean Whether the parameter is required for execution of the procedure.
IsArray Boolean Whether the parameter is an array.
Description String The description of the parameter.
Ordinal Int32 The index of the parameter.

sys_keycolumns

Describes the primary and foreign keys. The following query retrieves the primary key for the SampleTable_1 table:

SELECT * FROM sys_keycolumns WHERE IsKey='True' AND TableName='SampleTable_1' 

Columns

Name Type Description
CatalogName String The name of the database containing the key.
SchemaName String The name of the schema containing the key.
TableName String The name of the table containing the key.
ColumnName String The name of the key column.
IsKey Boolean Whether the column is a primary key in the table referenced in the TableName field.
IsForeignKey Boolean Whether the column is a foreign key referenced in the TableName field.
PrimaryKeyName String The name of the primary key.
ForeignKeyName String The name of the foreign key.
ReferencedCatalogName String The database containing the primary key.
ReferencedSchemaName String The schema containing the primary key.
ReferencedTableName String The table containing the primary key.
ReferencedColumnName String The column name of the primary key.

sys_foreignkeys

Describes the foreign keys. The following query retrieves all foreign keys which refer to other tables:

SELECT * FROM sys_foreignkeys WHERE ForeignKeyType = 'FOREIGNKEY_TYPE_IMPORT'

Columns

Name Type Description
CatalogName String The name of the database containing the key.
SchemaName String The name of the schema containing the key.
TableName String The name of the table containing the key.
ColumnName String The name of the key column.
PrimaryKeyName String The name of the primary key.
ForeignKeyName String The name of the foreign key.
ReferencedCatalogName String The database containing the primary key.
ReferencedSchemaName String The schema containing the primary key.
ReferencedTableName String The table containing the primary key.
ReferencedColumnName String The column name of the primary key.
ForeignKeyType String Designates whether the foreign key is an import (points to other tables) or export (referenced from other tables) key.

sys_indexes

Describes the available indexes. By filtering on indexes, you can write more selective queries with faster query response times.

The following query retrieves all indexes that are not primary keys:

SELECT * FROM sys_indexes WHERE IsPrimary='false'

Columns

Name Type Description
CatalogName String The name of the database containing the index.
SchemaName String The name of the schema containing the index.
TableName String The name of the table containing the index.
IndexName String The index name.
ColumnName String The name of the column associated with the index.
IsUnique Boolean True if the index is unique. False otherwise.
IsPrimary Boolean True if the index is a primary key. False otherwise.
Type Int16 An integer value corresponding to the index type: statistic (0), clustered (1), hashed (2), or other (3).
SortOrder String The sort order: A for ascending or D for descending.
OrdinalPosition Int16 The sequence number of the column in the index.

sys_connection_props

Returns information on the available connection properties and those set in the connection string.

When querying this table, the config connection string should be used:

jdbc:cdata:sagebcaccounting:config:

This connection string enables you to query this table without a valid connection.

The following query retrieves all connection properties that have been set in the connection string or set through a default value:

SELECT * FROM sys_connection_props WHERE Value <> ''

Columns

Name Type Description
Name String The name of the connection property.
ShortDescription String A brief description.
Type String The data type of the connection property.
Default String The default value if one is not explicitly set.
Values String A comma-separated list of possible values. A validation error is thrown if another value is specified.
Value String The value you set or a preconfigured default.
Required Boolean Whether the property is required to connect.
Category String The category of the connection property.
IsSessionProperty String Whether the property is a session property, used to save information about the current connection.
Sensitivity String The sensitivity level of the property. This informs whether the property is obfuscated in logging and authentication forms.
PropertyName String A camel-cased truncated form of the connection property name.
Ordinal Int32 The index of the parameter.
CatOrdinal Int32 The index of the parameter category.
Hierarchy String Shows dependent properties associated that need to be set alongside this one.
Visible Boolean Informs whether the property is visible in the connection UI.
ETC String Various miscellaneous information about the property.

sys_sqlinfo

Describes the SELECT query processing that the connector can offload to the data source.

Collaborative Query Processing

When working with data sources that do not support SQL-92, you can query the sys_sqlinfo view to determine the query capabilities of the underlying APIs, expressed in SQL syntax. The connector offloads as much of the SELECT statement processing as possible to the server and then processes the rest of the query in memory.

Discovering the Data Source's SELECT Capabilities

Below is an example data set of SQL capabilities. The following result set indicates the SELECT functionality that the connector can offload to the data source or process client side. Your data source may support additional SQL syntax. Some aspects of SELECT functionality are returned in a comma-separated list if supported; otherwise, the column contains NO.

NameDescriptionPossible Values
AGGREGATE_FUNCTIONSSupported aggregation functions.AVG, COUNT, MAX, MIN, SUM, DISTINCT
COUNTWhether COUNT function is supported.YES, NO
IDENTIFIER_QUOTE_OPEN_CHARThe opening character used to escape an identifier.[
IDENTIFIER_QUOTE_CLOSE_CHARThe closing character used to escape an identifier.]
SUPPORTED_OPERATORSA list of supported SQL operators.=, >, <, >=, <=, <>, !=, LIKE, NOT LIKE, IN, NOT IN, IS NULL, IS NOT NULL, AND, OR
GROUP_BYWhether GROUP BY is supported, and, if so, the degree of support.NO, NO_RELATION, EQUALS_SELECT, SQL_GB_COLLATE
STRING_FUNCTIONSSupported string functions.LENGTH, CHAR, LOCATE, REPLACE, SUBSTRING, RTRIM, LTRIM, RIGHT, LEFT, UCASE, SPACE, SOUNDEX, LCASE, CONCAT, ASCII, REPEAT, OCTET, BIT, POSITION, INSERT, TRIM, UPPER, REGEXP, LOWER, DIFFERENCE, CHARACTER, SUBSTR, STR, REVERSE, PLAN, UUIDTOSTR, TRANSLATE, TRAILING, TO, STUFF, STRTOUUID, STRING, SPLIT, SORTKEY, SIMILAR, REPLICATE, PATINDEX, LPAD, LEN, LEADING, KEY, INSTR, INSERTSTR, HTML, GRAPHICAL, CONVERT, COLLATION, CHARINDEX, BYTE
NUMERIC_FUNCTIONSSupported numeric functions.ABS, ACOS, ASIN, ATAN, ATAN2, CEILING, COS, COT, EXP, FLOOR, LOG, MOD, SIGN, SIN, SQRT, TAN, PI, RAND, DEGREES, LOG10, POWER, RADIANS, ROUND, TRUNCATE
TIMEDATE_FUNCTIONSSupported date/time functions.NOW, CURDATE, DAYOFMONTH, DAYOFWEEK, DAYOFYEAR, MONTH, QUARTER, WEEK, YEAR, CURTIME, HOUR, MINUTE, SECOND, TIMESTAMPADD, TIMESTAMPDIFF, DAYNAME, MONTHNAME, CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, EXTRACT
REPLICATION_SKIP_TABLESIndicates tables skipped during replication.
REPLICATION_TIMECHECK_COLUMNSA string array containing a list of columns which will be used to check for (in the given order) to use as a modified column during replication.
IDENTIFIER_PATTERNString value indicating what string is valid for an identifier.
SUPPORT_TRANSACTIONIndicates if the provider supports transactions such as commit and rollback.YES, NO
DIALECTIndicates the SQL dialect to use.
KEY_PROPERTIESIndicates the properties which identify the uniform database.
SUPPORTS_MULTIPLE_SCHEMASIndicates if multiple schemas may exist for the provider.YES, NO
SUPPORTS_MULTIPLE_CATALOGSIndicates if multiple catalogs may exist for the provider.YES, NO
DATASYNCVERSIONThe Data Sync version needed to access this driver.Standard, Starter, Professional, Enterprise
DATASYNCCATEGORYThe Data Sync category of this driver.Source, Destination, Cloud Destination
SUPPORTSENHANCEDSQLWhether enhanced SQL functionality beyond what is offered by the API is supported.TRUE, FALSE
SUPPORTS_BATCH_OPERATIONSWhether batch operations are supported.YES, NO
SQL_CAPAll supported SQL capabilities for this driver.SELECT, INSERT, DELETE, UPDATE, TRANSACTIONS, ORDERBY, OAUTH, ASSIGNEDID, LIMIT, LIKE, BULKINSERT, COUNT, BULKDELETE, BULKUPDATE, GROUPBY, HAVING, AGGS, OFFSET, REPLICATE, COUNTDISTINCT, JOINS, DROP, CREATE, DISTINCT, INNERJOINS, SUBQUERIES, ALTER, MULTIPLESCHEMAS, GROUPBYNORELATION, OUTERJOINS, UNIONALL, UNION, UPSERT, GETDELETED, CROSSJOINS, GROUPBYCOLLATE, MULTIPLECATS, FULLOUTERJOIN, MERGE, JSONEXTRACT, BULKUPSERT, SUM, SUBQUERIESFULL, MIN, MAX, JOINSFULL, XMLEXTRACT, AVG, MULTISTATEMENTS, FOREIGNKEYS, CASE, LEFTJOINS, COMMAJOINS, WITH, LITERALS, RENAME, NESTEDTABLES, EXECUTE, BATCH, BASIC, INDEX
PREFERRED_CACHE_OPTIONSA string value specifies the preferred cacheOptions.
ENABLE_EF_ADVANCED_QUERYIndicates if the driver directly supports advanced queries coming from Entity Framework. If not, queries will be handled client side.YES, NO
PSEUDO_COLUMNSA string array indicating the available pseudo columns.
MERGE_ALWAYSIf the value is true, The Merge Mode is forcibly executed in Data Sync.TRUE, FALSE
REPLICATION_MIN_DATE_QUERYA select query to return the replicate start datetime.
REPLICATION_MIN_FUNCTIONAllows a provider to specify the formula name to use for executing a server side min.
REPLICATION_START_DATEAllows a provider to specify a replicate startdate.
REPLICATION_MAX_DATE_QUERYA select query to return the replicate end datetime.
REPLICATION_MAX_FUNCTIONAllows a provider to specify the formula name to use for executing a server side max.
IGNORE_INTERVALS_ON_INITIAL_REPLICATEA list of tables which will skip dividing the replicate into chunks on the initial replicate.
CHECKCACHE_USE_PARENTIDIndicates whether the CheckCache statement should be done against the parent key column.TRUE, FALSE
CREATE_SCHEMA_PROCEDURESIndicates stored procedures* that can be used for generating schema files.

The following query retrieves the operators that can be used in the WHERE clause:
SELECT * FROM sys_sqlinfo WHERE Name='SUPPORTED_OPERATORS'
Note that individual tables may have different limitations or requirements on the WHERE clause; refer to the Data Model section for more information.

Columns

Name Type Description
NAME String A component of SQL syntax, or a capability that can be processed on the server.
VALUE String Detail on the supported SQL or SQL syntax.

sys_identity

Returns information about attempted modifications.

The following query retrieves the Ids of the modified rows in a batch operation:

SELECT * FROM sys_identity

Columns

Name Type Description
Id String The database-generated ID returned from a data modification operation.
Batch String An identifier for the batch. 1 for a single operation.
Operation String The result of the operation in the batch: INSERTED, UPDATED, or DELETED.
Message String SUCCESS or an error message if the update in the batch failed.

Advanced Configurations Properties

The advanced configurations properties are the various options that can be used to establish a connection. This section provides a complete list of the options you can configure. Click the links for further details.

OAuth


PropertyDescription
InitiateOAuthSet this property to initiate the process to obtain or refresh the OAuth access token when you connect.
OAuthClientIdThe client ID assigned when you register your application with an OAuth authorization server.
OAuthClientSecretThe client secret assigned when you register your application with an OAuth authorization server.
OAuthAccessTokenThe access token for connecting using OAuth.
CallbackURLThe OAuth callback URL to return to when authenticating. This value must match the callback URL you specify in your app settings.
OAuthVerifierThe verifier code returned from the OAuth authorization URL.
OAuthRefreshTokenThe OAuth refresh token for the corresponding OAuth access token.
OAuthExpiresInThe lifetime in seconds of the OAuth AccessToken.
OAuthTokenTimestampThe Unix epoch timestamp in milliseconds when the current Access Token was created.

SSL


PropertyDescription
SSLServerCertThe certificate to be accepted from the server when connecting using TLS/SSL.

Schema


PropertyDescription
LocationA path to the directory that contains the schema files defining tables, views, and stored procedures.
BrowsableSchemasThis property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA, SchemaB, SchemaC.
TablesThis property restricts the tables reported to a subset of the available tables. For example, Tables=TableA, TableB, TableC.
ViewsRestricts the views reported to a subset of the available tables. For example, Views=ViewA, ViewB, ViewC.

Miscellaneous


PropertyDescription
BusinessIdThe ID of your Bussiness.
MaxRowsLimits the number of rows returned rows when no aggregation or group by is used in the query. This helps avoid performance issues at design time.
OtherThese hidden properties are used only in specific use cases.
PagesizeThe maximum number of results to return per page from Sage Business Cloud Accounting.
PseudoColumnsThis property indicates whether or not to include pseudo columns as columns to the table.
TimeoutThe value in seconds until the timeout error is thrown, canceling the operation.

OAuth

This section provides a complete list of OAuth properties you can configure.


PropertyDescription
InitiateOAuthSet this property to initiate the process to obtain or refresh the OAuth access token when you connect.
OAuthClientIdThe client ID assigned when you register your application with an OAuth authorization server.
OAuthClientSecretThe client secret assigned when you register your application with an OAuth authorization server.
OAuthAccessTokenThe access token for connecting using OAuth.
CallbackURLThe OAuth callback URL to return to when authenticating. This value must match the callback URL you specify in your app settings.
OAuthVerifierThe verifier code returned from the OAuth authorization URL.
OAuthRefreshTokenThe OAuth refresh token for the corresponding OAuth access token.
OAuthExpiresInThe lifetime in seconds of the OAuth AccessToken.
OAuthTokenTimestampThe Unix epoch timestamp in milliseconds when the current Access Token was created.

InitiateOAuth

Set this property to initiate the process to obtain or refresh the OAuth access token when you connect.

Possible Values

OFF, GETANDREFRESH, REFRESH

Data Type

string

Default Value

"OFF"

Remarks

The following options are available:

  1. OFF: Indicates that the OAuth flow will be handled entirely by the user. An OAuthAccessToken will be required to authenticate.
  2. GETANDREFRESH: Indicates that the entire OAuth Flow will be handled by the connector. If no token currently exists, it will be obtained by prompting the user via the browser. If a token exists, it will be refreshed when applicable.
  3. REFRESH: Indicates that the connector will only handle refreshing the OAuthAccessToken. The user will never be prompted by the connector to authenticate via the browser. The user must handle obtaining the OAuthAccessToken and OAuthRefreshToken initially.

OAuthClientId

The client ID assigned when you register your application with an OAuth authorization server.

Data Type

string

Default Value

""

Remarks

As part of registering an OAuth application, you will receive the OAuthClientId value, sometimes also called a consumer key, and a client secret, the OAuthClientSecret.

OAuthClientSecret

The client secret assigned when you register your application with an OAuth authorization server.

Data Type

string

Default Value

""

Remarks

As part of registering an OAuth application, you will receive the OAuthClientId, also called a consumer key. You will also receive a client secret, also called a consumer secret. Set the client secret in the OAuthClientSecret property.

OAuthAccessToken

The access token for connecting using OAuth.

Data Type

string

Default Value

""

Remarks

The OAuthAccessToken property is used to connect using OAuth. The OAuthAccessToken is retrieved from the OAuth server as part of the authentication process. It has a server-dependent timeout and can be reused between requests.

The access token is used in place of your user name and password. The access token protects your credentials by keeping them on the server.

CallbackURL

The OAuth callback URL to return to when authenticating. This value must match the callback URL you specify in your app settings.

Data Type

string

Default Value

"http://localhost:33333"

Remarks

During the authentication process, the OAuth authorization server redirects the user to this URL. This value must match the callback URL you specify in your app settings.

OAuthVerifier

The verifier code returned from the OAuth authorization URL.

Data Type

string

Default Value

""

Remarks

The verifier code returned from the OAuth authorization URL. This can be used on systems where a browser cannot be launched such as headless systems.

Authentication on Headless Machines

See to obtain the OAuthVerifier value.

Set OAuthSettingsLocation along with OAuthVerifier. When you connect, the connector exchanges the OAuthVerifier for the OAuth authentication tokens and saves them, encrypted, to the specified file. Set InitiateOAuth to GETANDREFRESH automate the exchange.

Once the OAuth settings file has been generated, you can remove OAuthVerifier from the connection properties and connect with OAuthSettingsLocation set.

To automatically refresh the OAuth token values, set OAuthSettingsLocation and additionally set InitiateOAuth to REFRESH.

OAuthRefreshToken

The OAuth refresh token for the corresponding OAuth access token.

Data Type

string

Default Value

""

Remarks

The OAuthRefreshToken property is used to refresh the OAuthAccessToken when using OAuth authentication.

OAuthExpiresIn

The lifetime in seconds of the OAuth AccessToken.

Data Type

string

Default Value

""

Remarks

Pair with OAuthTokenTimestamp to determine when the AccessToken will expire.

OAuthTokenTimestamp

The Unix epoch timestamp in milliseconds when the current Access Token was created.

Data Type

string

Default Value

""

Remarks

Pair with OAuthExpiresIn to determine when the AccessToken will expire.

SSL

This section provides a complete list of SSL properties you can configure.


PropertyDescription
SSLServerCertThe certificate to be accepted from the server when connecting using TLS/SSL.

SSLServerCert

The certificate to be accepted from the server when connecting using TLS/SSL.

Data Type

string

Default Value

""

Remarks

If using a TLS/SSL connection, this property can be used to specify the TLS/SSL certificate to be accepted from the server. Any other certificate that is not trusted by the machine is rejected.

This property can take the following forms:

Description Example
A full PEM Certificate (example shortened for brevity) -----BEGIN CERTIFICATE----- MIIChTCCAe4CAQAwDQYJKoZIhv......Qw== -----END CERTIFICATE-----
A path to a local file containing the certificate C:\cert.cer
The public key (example shortened for brevity) -----BEGIN RSA PUBLIC KEY----- MIGfMA0GCSq......AQAB

-----END RSA PUBLIC KEY-----
The MD5 Thumbprint (hex values can also be either space or colon separated) ecadbdda5a1529c58a1e9e09828d70e4
The SHA1 Thumbprint (hex values can also be either space or colon separated) 34a929226ae0819f2ec14b4a3d904f801cbb150d

If not specified, any certificate trusted by the machine is accepted.

Certificates are validated as trusted by the machine based on the System's trust store. The trust store used is the 'javax.net.ssl.trustStore' value specified for the system. If no value is specified for this property, Java's default trust store is used (for example, JAVA_HOME\lib\security\cacerts).

Use '*' to signify to accept all certificates. Note that this is not recommended due to security concerns.

Schema

This section provides a complete list of schema properties you can configure.


PropertyDescription
LocationA path to the directory that contains the schema files defining tables, views, and stored procedures.
BrowsableSchemasThis property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA, SchemaB, SchemaC.
TablesThis property restricts the tables reported to a subset of the available tables. For example, Tables=TableA, TableB, TableC.
ViewsRestricts the views reported to a subset of the available tables. For example, Views=ViewA, ViewB, ViewC.

Location

A path to the directory that contains the schema files defining tables, views, and stored procedures.

Data Type

string

Default Value

"%APPDATA%\\SageBCAccounting Data Provider\Schema"

Remarks

The path to a directory which contains the schema files for the connector (.rsd files for tables and views, .rsb files for stored procedures). The folder location can be a relative path from the location of the executable. The Location property is only needed if you want to customize definitions (for example, change a column name, ignore a column, and so on) or extend the data model with new tables, views, or stored procedures.

If left unspecified, the default location is "%APPDATA%\\SageBCAccounting Data Provider\Schema" with %APPDATA% being set to the user's configuration directory:

Platform %APPDATA%
Windows The value of the APPDATA environment variable
Mac ~/Library/Application Support
Linux ~/.config

BrowsableSchemas

This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC.

Data Type

string

Default Value

""

Remarks

Listing the schemas from databases can be expensive. Providing a list of schemas in the connection string improves the performance.

Tables

This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA,TableB,TableC.

Data Type

string

Default Value

""

Remarks

Listing the tables from some databases can be expensive. Providing a list of tables in the connection string improves the performance of the connector.

This property can also be used as an alternative to automatically listing views if you already know which ones you want to work with and there would otherwise be too many to work with.

Specify the tables you want in a comma-separated list. Each table should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Tables=TableA,[TableB/WithSlash],WithCatalog.WithSchema.`TableC With Space`.

Note that when connecting to a data source with multiple schemas or catalogs, you will need to provide the fully qualified name of the table in this property, as in the last example here, to avoid ambiguity between tables that exist in multiple catalogs or schemas.

Views

Restricts the views reported to a subset of the available tables. For example, Views=ViewA,ViewB,ViewC.

Data Type

string

Default Value

""

Remarks

Listing the views from some databases can be expensive. Providing a list of views in the connection string improves the performance of the connector.

This property can also be used as an alternative to automatically listing views if you already know which ones you want to work with and there would otherwise be too many to work with.

Specify the views you want in a comma-separated list. Each view should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Views=ViewA,[ViewB/WithSlash],WithCatalog.WithSchema.`ViewC With Space`.

Note that when connecting to a data source with multiple schemas or catalogs, you will need to provide the fully qualified name of the table in this property, as in the last example here, to avoid ambiguity between tables that exist in multiple catalogs or schemas.

Miscellaneous

This section provides a complete list of miscellaneous properties you can configure.


PropertyDescription
BusinessIdThe ID of your Bussiness.
MaxRowsLimits the number of rows returned rows when no aggregation or group by is used in the query. This helps avoid performance issues at design time.
OtherThese hidden properties are used only in specific use cases.
PagesizeThe maximum number of results to return per page from Sage Business Cloud Accounting.
PseudoColumnsThis property indicates whether or not to include pseudo columns as columns to the table.
TimeoutThe value in seconds until the timeout error is thrown, canceling the operation.

BusinessId

The ID of your Bussiness.

Data Type

string

Default Value

""

Remarks

The ID of your Bussiness. If you have multiple companies connected to your Sage BC Accounting Account, by default we use the first one.

MaxRows

Limits the number of rows returned rows when no aggregation or group by is used in the query. This helps avoid performance issues at design time.

Data Type

int

Default Value

-1

Remarks

Limits the number of rows returned rows when no aggregation or group by is used in the query. This helps avoid performance issues at design time.

Other

These hidden properties are used only in specific use cases.

Data Type

string

Default Value

""

Remarks

The properties listed below are available for specific use cases. Normal driver use cases and functionality should not require these properties.

Specify multiple properties in a semicolon-separated list.

Integration and Formatting

DefaultColumnSizeSets the default length of string fields when the data source does not provide column length in the metadata. The default value is 2000.
ConvertDateTimeToGMTDetermines whether to convert date-time values to GMT, instead of the local time of the machine.
RecordToFile=filenameRecords the underlying socket data transfer to the specified file.

Pagesize

The maximum number of results to return per page from Sage Business Cloud Accounting.

Data Type

int

Default Value

200

Remarks

The Pagesize property affects the maximum number of results to return per page from Sage Business Cloud Accounting. Setting a higher value may result in better performance at the cost of additional memory allocated per page consumed.

PseudoColumns

This property indicates whether or not to include pseudo columns as columns to the table.

Data Type

string

Default Value

""

Remarks

This setting is particularly helpful in Entity Framework, which does not allow you to set a value for a pseudo column unless it is a table column. The value of this connection setting is of the format "Table1=Column1, Table1=Column2, Table2=Column3". You can use the "*" character to include all tables and all columns; for example, "*=*".

Timeout

The value in seconds until the timeout error is thrown, canceling the operation.

Data Type

int

Default Value

60

Remarks

If Timeout = 0, operations do not time out. The operations run until they complete successfully or until they encounter an error condition.

If Timeout expires and the operation is not yet complete, the connector throws an exception.

Copyright © 2022-2023 Jitterbit, Inc. All rights reserved.