Skip to Content

Sage Business Cloud Accounting Connection Details

Introduction

Connector Version

This documentation is based on version 23.0.8843 of the connector.

Get Started

Sage Business Cloud Accounting Version Support

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

Establish a Connection

Connect to Sage Business Cloud Accounting

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

Create a Custom OAuth Application

Create a Custom OAuth Application

embeds OAuth Application Credentials with branding that can be used when connecting to Sage Business Cloud Accounting via a desktop application or a headless machine. If you want to use the embedded OAuth application from either of those two auth flows, all you need to do to connect is to:

  • set AuthScheme to OAuth,
  • get and set the OAuthAccessToken, and
  • set the necessary configuration parameters.

(For information on getting and setting the OAuthAccessToken and other configuration parameters, see "Connecting to Sage Business Cloud Accounting".)

However, you must create a custom OAuth application to connect to Sage Business Cloud Accounting via the Web. And since custom OAuth applications seamlessly support all three commonly-used auth flows, you might want to create custom OAuth applications (use your own OAuth Application Credentials) for those auth flows anyway.

Custom OAuth applications are useful if you want to:

  • control branding of the authentication dialog;
  • control the redirect URI that the application redirects the user to after the user authenticates; or
  • customize the permissions that you are requesting from the user.
Procedure

The following steps create a custom OAuth application, registers it in the Sage Business Cloud Accounting Developer account,

and obtains the OAuth client credentials:

  1. Log into the Sage Business Cloud Accounting Developer Account.
  2. Create a new application.
    • If you are developing a desktop application, set the CallbackUrl to http://localhost:3333, or some other similar http URL.
    • If you are developing a web service, set the CallbackUrl to the redirect uri you would like your web service to receive.

When your custom application has been created, the Developer portal displays the OAuthClientId (Client Id) and OAuthClientSecret (Client Secret). Record these for future use.

Important Notes

Configuration Files and Their Paths

  • All references to adding configuration files and their paths refer to files and locations on the Jitterbit 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.

Query Processing

The connector offloads as much of the SELECT statement processing as possible to Sage Business Cloud Accounting and then processes the rest of the query in memory (client-side).

See Query Processing for more information.

User Defined Views

The Sage Business Cloud Accounting connector 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.

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

Define Views Using DDL Statements

The connector is also capable of creating and altering the schema via DDL Statements such as CREATE LOCAL VIEW, ALTER LOCAL VIEW, and DROP LOCAL VIEW.

Create a View

To create a new view using DDL statements, provide the view name and query as follows:

CREATE LOCAL VIEW [MyViewName] AS SELECT * FROM Customers LIMIT 20;

If no JSON file exists, the above code creates one. The view is then created in the JSON configuration file and is now discoverable. The JSON file location is specified by the UserDefinedViews connection property.

Alter a View

To alter an existing view, provide the name of an existing view alongside the new query you would like to use instead:

ALTER LOCAL VIEW [MyViewName] AS SELECT * FROM Customers WHERE TimeModified > '3/1/2020';

The view is then updated in the JSON configuration file.

Drop a View

To drop an existing view, provide the name of an existing schema alongside the new query you would like to use instead.

DROP LOCAL VIEW [MyViewName]

This removes the view from the JSON configuration file. It can no longer be queried.

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.

Work 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

Customize 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 tables. Tables 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

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.

Tables

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

Sage Business Cloud Accounting Connector 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 with a JSON aggregate value for SalesInvoiceLineItemAggregate.

INSERT INTO SalesInvoices (ContactId, Date, SalesInvoiceLineItemAggregate) VALUES ('0603375a7e29443b8d248239b007e72c', '2022-11-28', '{"description":"In Rainbows LP","ledger_account_id":"35efa3bc698311eda8c40ef4cf562701","unit_price":35.0,"quantity":2.0,"tax_rate_id":"24d66d90e1d64620a5d8b13cfca85b81"}')

Create a simple sales invoice by using temp tables.

INSERT INTO SalesInvoiceLineItemAggregate#TEMP (Description, LedgerAccountId, UnitPrice, Quantity, TaxRateId) VALUES ('In Rainbows LP', '35efa3bc698311eda8c40ef4cf562701', '35.0', '2.0', '24d66d90e1d64620a5d8b13cfca85b81')
INSERT INTO SalesInvoiceLineItemAggregate#TEMP (Description, LedgerAccountId, UnitPrice, Quantity, TaxRateId) VALUES ('Kid A LP', '35efa3bc698311eda8c40ef4cf562701', '45.0', '4.0', '24d66d90e1d64620a5d8b13cfca85b81')
INSERT INTO SalesInvoices (ContactId, Date, SalesInvoiceLineItemAggregate) VALUES ('0603375a7e29443b8d248239b007e72c', '2022-11-28', 'SalesInvoiceLineItemAggregate#TEMP')
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 similar to tables in the way that data is represented; however, views are read-only.

Queries can be executed against a view as if it were a normal table.

Sage Business Cloud Accounting Connector 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 Double The net amount for the invoice line
SalesInvoicesCurrencyTaxAmount Double The tax amount for the invoice line in base currency
SalesInvoicesCurrencyTotalAmount Double The total amount for the invoice line in base currency
DiscountPercentage String The discount percentage for the invoice line
Quantity Double The quantity for the invoice line
TotalAmount Double The total amount for the invoice line
SalesInvoicesCurrencyDiscountAmount Double 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 Double The unit price for the invoice line
SalesInvoicesCurrencyUnitPrice Double The unit price for the invoice line in base currency
DiscountAmount String The discount amount for the invoice line
SalesInvoicesCurrencyNetAmount Double 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 Double The tax amount for the invoice line. This attribute is required in v3.1, unless the tax rate is of a 'zero', 'exempt' or 'no_tax' type. Then the tax_amount is infered as 0.0.
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

Stored procedures are function-like interfaces that extend the functionality of the connector beyond simple SELECT/INSERT/UPDATE/DELETE operations with Sage Business Cloud Accounting.

Stored procedures accept a list of parameters, perform their intended function, and then return any relevant response data from Sage Business Cloud Accounting, along with an indication of whether the procedure succeeded or failed.

Sage Business Cloud Accounting Connector 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.
IsReadOnly Boolean Whether the column is read-only.
IsKey Boolean Indicates whether a field returned from sys_tablecolumns is the primary key of the table.

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_primarykeys

Describes the primary keys.

The following query retrieves the primary keys from all tables and views:

SELECT * FROM sys_primarykeys
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.
KeySeq String The sequence number of the primary key.
KeyName String The name of the primary 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.

Discovering the Data Source's SELECT Capabilities

Below is an example data set of SQL capabilities. Some aspects of SELECT functionality are returned in a comma-separated list if supported; otherwise, the column contains NO.

Name Description Possible Values
AGGREGATE_FUNCTIONS Supported aggregation functions. AVG, COUNT, MAX, MIN, SUM, DISTINCT
COUNT Whether COUNT function is supported. YES, NO
IDENTIFIER_QUOTE_OPEN_CHAR The opening character used to escape an identifier. [
IDENTIFIER_QUOTE_CLOSE_CHAR The closing character used to escape an identifier. ]
SUPPORTED_OPERATORS A list of supported SQL operators. =, >, <, >=, <=, <>, !=, LIKE, NOT LIKE, IN, NOT IN, IS NULL, IS NOT NULL, AND, OR
GROUP_BY Whether GROUP BY is supported, and, if so, the degree of support. NO, NO_RELATION, EQUALS_SELECT, SQL_GB_COLLATE
STRING_FUNCTIONS Supported 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_FUNCTIONS Supported 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_FUNCTIONS Supported 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_TABLES Indicates tables skipped during replication.
REPLICATION_TIMECHECK_COLUMNS A 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_PATTERN String value indicating what string is valid for an identifier.
SUPPORT_TRANSACTION Indicates if the provider supports transactions such as commit and rollback. YES, NO
DIALECT Indicates the SQL dialect to use.
KEY_PROPERTIES Indicates the properties which identify the uniform database.
SUPPORTS_MULTIPLE_SCHEMAS Indicates if multiple schemas may exist for the provider. YES, NO
SUPPORTS_MULTIPLE_CATALOGS Indicates if multiple catalogs may exist for the provider. YES, NO
DATASYNCVERSION The Data Sync version needed to access this driver. Standard, Starter, Professional, Enterprise
DATASYNCCATEGORY The Data Sync category of this driver. Source, Destination, Cloud Destination
SUPPORTSENHANCEDSQL Whether enhanced SQL functionality beyond what is offered by the API is supported. TRUE, FALSE
SUPPORTS_BATCH_OPERATIONS Whether batch operations are supported. YES, NO
SQL_CAP All 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_OPTIONS A string value specifies the preferred cacheOptions.
ENABLE_EF_ADVANCED_QUERY Indicates if the driver directly supports advanced queries coming from Entity Framework. If not, queries will be handled client side. YES, NO
PSEUDO_COLUMNS A string array indicating the available pseudo columns.
MERGE_ALWAYS If the value is true, The Merge Mode is forcibly executed in Data Sync. TRUE, FALSE
REPLICATION_MIN_DATE_QUERY A select query to return the replicate start datetime.
REPLICATION_MIN_FUNCTION Allows a provider to specify the formula name to use for executing a server side min.
REPLICATION_START_DATE Allows a provider to specify a replicate startdate.
REPLICATION_MAX_DATE_QUERY A select query to return the replicate end datetime.
REPLICATION_MAX_FUNCTION Allows a provider to specify the formula name to use for executing a server side max.
IGNORE_INTERVALS_ON_INITIAL_REPLICATE A list of tables which will skip dividing the replicate into chunks on the initial replicate.
CHECKCACHE_USE_PARENTID Indicates whether the CheckCache statement should be done against the parent key column. TRUE, FALSE
CREATE_SCHEMA_PROCEDURES Indicates 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

Property Description
InitiateOAuth Set this property to initiate the process to obtain or refresh the OAuth access token when you connect.
OAuthClientId The client ID assigned when you register your application with an OAuth authorization server.
OAuthClientSecret The client secret assigned when you register your application with an OAuth authorization server.
OAuthAccessToken The access token for connecting using OAuth.
OAuthSettingsLocation The location of the settings file where OAuth values are saved when InitiateOAuth is set to GETANDREFRESH or REFRESH . Alternatively, you can hold this location in memory by specifying a value starting with 'memory://'.
CallbackURL The OAuth callback URL to return to when authenticating. This value must match the callback URL you specify in your app settings.
OAuthVerifier The verifier code returned from the OAuth authorization URL.
OAuthRefreshToken The OAuth refresh token for the corresponding OAuth access token.
OAuthExpiresIn The lifetime in seconds of the OAuth AccessToken.
OAuthTokenTimestamp The Unix epoch timestamp in milliseconds when the current Access Token was created.

SSL

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

Schema

Property Description
Location A path to the directory that contains the schema files defining tables, views, and stored procedures.
BrowsableSchemas This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA, SchemaB, SchemaC.
Tables This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA, TableB, TableC.
Views Restricts the views reported to a subset of the available tables. For example, Views=ViewA, ViewB, ViewC.

Miscellaneous

Property Description
BusinessId The ID of your Bussiness.
MaxRows Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.
Other These hidden properties are used only in specific use cases.
Pagesize The maximum number of results to return per page from Sage Business Cloud Accounting.
PseudoColumns This property indicates whether or not to include pseudo columns as columns to the table.
Timeout The value in seconds until the timeout error is thrown, canceling the operation.
UserDefinedViews A filepath pointing to the JSON configuration file containing your custom views.

OAuth

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

Property Description
InitiateOAuth Set this property to initiate the process to obtain or refresh the OAuth access token when you connect.
OAuthClientId The client ID assigned when you register your application with an OAuth authorization server.
OAuthClientSecret The client secret assigned when you register your application with an OAuth authorization server.
OAuthAccessToken The access token for connecting using OAuth.
OAuthSettingsLocation The location of the settings file where OAuth values are saved when InitiateOAuth is set to GETANDREFRESH or REFRESH . Alternatively, you can hold this location in memory by specifying a value starting with 'memory://'.
CallbackURL The OAuth callback URL to return to when authenticating. This value must match the callback URL you specify in your app settings.
OAuthVerifier The verifier code returned from the OAuth authorization URL.
OAuthRefreshToken The OAuth refresh token for the corresponding OAuth access token.
OAuthExpiresIn The lifetime in seconds of the OAuth AccessToken.
OAuthTokenTimestamp The 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.

OAuthSettingsLocation

The location of the settings file where OAuth values are saved when InitiateOAuth is set to GETANDREFRESH or REFRESH . Alternatively, you can hold this location in memory by specifying a value starting with 'memory://'.

Data Type

string

Default Value

%APPDATA%\SageBCAccounting Data Provider\OAuthSettings.txt

Remarks

When InitiateOAuth is set to GETANDREFRESH or REFRESH, the connector saves OAuth values to avoid requiring the user to manually enter OAuth connection properties and to allow the credentials to be shared across connections or processes.

Instead of specifying a file path, you can use memory storage. Memory locations are specified by using a value starting with 'memory://' followed by a unique identifier for that set of credentials (for example, memory://user1). The identifier can be anything you choose but should be unique to the user. Unlike file-based storage, where credentials persist across connections, memory storage loads the credentials into static memory, and the credentials are shared between connections using the same identifier for the life of the process. To persist credentials outside the current process, you must manually store the credentials prior to closing the connection. This enables you to set them in the connection when the process is started again. You can retrieve OAuth property values with a query to the sys_connection_props system table. If there are multiple connections using the same credentials, the properties are read from the previously closed connection.

The default location is "%APPDATA%\SageBCAccounting Data Provider\OAuthSettings.txt" with %APPDATA% set to the user's configuration directory. The default values are

  • Windows: "register://%DSN"
  • Unix: "%AppData%..."
  • Mac: "%AppData%..."

where DSN is the name of the current DSN used in the open connection.

The following table lists the value of %APPDATA% by OS:

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

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 location. Set InitiateOAuth to GETANDREFRESH to 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.

Property Description
SSLServerCert The 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.

Property Description
Location A path to the directory that contains the schema files defining tables, views, and stored procedures.
BrowsableSchemas This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA, SchemaB, SchemaC.
Tables This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA, TableB, TableC.
Views Restricts 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.

Property Description
BusinessId The ID of your Bussiness.
MaxRows Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.
Other These hidden properties are used only in specific use cases.
Pagesize The maximum number of results to return per page from Sage Business Cloud Accounting.
PseudoColumns This property indicates whether or not to include pseudo columns as columns to the table.
Timeout The value in seconds until the timeout error is thrown, canceling the operation.
UserDefinedViews A filepath pointing to the JSON configuration file containing your custom views.

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 when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.

Data Type

int

Default Value

-1

Remarks

Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.

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
Property Description
DefaultColumnSize Sets the default length of string fields when the data source does not provide column length in the metadata. The default value is 2000.
ConvertDateTimeToGMT Determines whether to convert date-time values to GMT, instead of the local time of the machine.
RecordToFile=filename Records 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.

UserDefinedViews

A filepath pointing to the JSON configuration file containing your custom views.

Data Type

string

Default Value

""

Remarks

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

Note that the specified path is not embedded in quotation marks.