Sage Business Cloud Accounting Connection Details

Introduction

Connector Version

This documentation is based on version 25.0.9298 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

OAuth application credentials are embedded 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 supports the use of user defined views, virtual tables whose contents are decided by a pre-configured user defined query. These views are useful when you cannot directly control queries being issued to the drivers. For an overview of creating and configuring custom views, see User Defined Views.

SSL Configuration

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

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

For further information, see Query Processing.

Log

For an overview of configuration settings that can be used to refine logging, see Logging. Only two connection properties are required for basic logging, but there are numerous features that support more refined logging, which enables you to use the LogModules connection property to specify subsets of information to be logged.

User Defined Views

The Sage Business Cloud Accounting connector supports the use of user defined views: user-defined virtual tables whose contents are decided by a preconfigured query. User defined views are useful in situations where you cannot directly control the query being issued to the driver; for example, when using the driver from Jitterbit.

Use a user defined view 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 so that each root element defines the name of a view, and includes 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

In order to avoid a view's name clashing with an actual entity in the data model, user defined views are exposed in the UserViews schema by default. To change the name of the schema used for UserViews, reset 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 TLS with the server. The server certificate is validated against the default system trusted certificate store. You can override how the certificate gets validated using the SSLServerCert connection property.

To specify another certificate, see the SSLServerCert connection property.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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