This page provides a guide to Establishing a Connection to Sage Business Cloud Accounting in CData Cloud, as well as information on the available resources, and a reference to the available connection properties.

Connecting to Sage Business Cloud Accounting

Establishing a Connection shows how to authenticate to Sage Business Cloud Accounting and configure any necessary connection properties to create a database in CData Cloud

Accessing Data from CData Cloud Services

Accessing data from Sage Business Cloud Accounting through the available standard services and CData Cloud administration is documented in further details in the CData Cloud Documentation.

Connect to Sage Business Cloud Accounting by selecting the corresponding icon in the Database tab. Required properties are listed under Settings. The Advanced tab lists connection properties that are not typically required.

Authenticating to Sage Business Cloud Accounting

Sage Business Cloud Accounting provides embedded OAuth credentials that simplify connection from a Desktop application . To connect from a Web application, you must create a custom OAuth application, as described in Creating a Custom OAuth Application.

To connect via OAuth from all authentication flows, you must set AuthScheme to OAuth.

The following subsections describe how to authenticate to Sage Business Cloud Accounting from the available oauth flows. For information about how to create a custom OAuth application, and why you might want to create one even for auth flows that already have embedded OAuth credentials, see Creating a Custom OAuth Application.

For a complete list of connection string properties available in Sage Business Cloud Accounting, see Connection.

When the access token expires, the Cloud refreshes it automatically.

Creating a Custom OAuth Application

CData embeds OAuth Application Credentials with CData 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.

Customizing the SSL Configuration

By default, the Cloud 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.

Connecting Through a Firewall or Proxy

HTTP Proxies

To authenticate to an HTTP proxy, set the following:

• ProxyServer: the hostname or IP address of the proxy server that you want to route HTTP traffic through.
• ProxyPort: the TCP port that the proxy server is running on.
• ProxyAuthScheme: the authentication method the Cloud uses when authenticating to the proxy server.
• ProxyUser: the username of a user account registered with the proxy server.
• ProxyPassword: the password associated with the ProxyUser.

Other Proxies

Set the following properties:

• To use a proxy-based firewall, set FirewallType, FirewallServer, and FirewallPort.
• To tunnel the connection, set FirewallType to TUNNEL.
• To authenticate, specify FirewallUser and FirewallPassword.
• To authenticate to a SOCKS proxy, additionally set FirewallType to SOCKS5.

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

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.

The Cloud 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.

CData Cloud - Sage Business Cloud Accounting Tables

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

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

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

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

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

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

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

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

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

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

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

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

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

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 'Kreditorennummer' and 'Debitorennummer'
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 'Steuernummer' there (not the 'USt-ID').
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.

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

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

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 - 'invoice', 'cash'.
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 - 'invoice', 'cash'.
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

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

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 'Kreditorennummer' and 'Debitorennummer'
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 'Steuernummer' there (not the 'USt-ID').
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

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

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

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

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

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

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

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

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

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

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

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