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 Harmony Agent where the connector is installed. These paths are to be adjusted as appropriate depending on the agent and the operating system. If multiple agents are used in an agent group, identical files will be required on each agent.

Advanced Features¶

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

User Defined Views

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

SSL Configuration

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

Proxy

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

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¶

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¶