Establishing a Connection

Create a connection to Sage Business Cloud Accounting by navigating to the Connections page in the Sync App application and selecting the corresponding icon in the Add Connections panel. If the Sage Business Cloud Accounting icon is not available, click the Add More icon to download and install the Sage Business Cloud Accounting connector from the CData site.

Required properties are listed under the Settings tab. The Advanced tab lists connection properties that are not typically required.

Authenticating to Sage Business Cloud Accounting

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

OAuth

See Using OAuth Authentication for an authentication guide.

Advanced Features

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

User Defined Views

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

SSL Configuration

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

Firewall and Proxy

Configure the Sync App for compliance with Firewall and Proxy, including Windows proxies and HTTP proxies. You can also set up tunnel connections.

Query Processing

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

See Query Processing for more information.

Logging

See Logging for an overview of configuration settings that can be used to refine CData logging. For basic logging, you only need to set two connection properties, but there are numerous features that support more refined logging, where you can select subsets of information to be logged using the LogModules connection property.

SSL Configuration

Customizing the SSL Configuration

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

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

Firewall and Proxy

Connecting Through a Firewall or Proxy

HTTP Proxies

To connect through the Windows system proxy, you do not need to set any additional connection properties. To connect to other proxies, set ProxyAutoDetect to false.

In addition, to authenticate to an HTTP proxy, set ProxyAuthScheme, ProxyUser, and ProxyPassword, in addition to ProxyServer and ProxyPort.

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.

Data Model

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

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

Key Features

The Sync App 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 Sync App.

Tables

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

Views

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

Stored Procedures

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 Sync App offloads as much of the SELECT statement processing as possible to the Sage Business Cloud Accounting APIs and then processes the rest of the query in memory. See SupportEnhancedSQL for more information on how the Sync App circumvents API limitations with in-memory client-side processing.

Tables

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

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

Sage Business Cloud Accounting Connector for CData Sync Tables


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

Addresses

Query Addresses in Sage Business Cloud Accounting.

Table Specific Information

Query the available Addresses

Select

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

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

For example, the following queries are processed server side:

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

Insert

Create a simple address.

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

Update

Updates are performed based on Id.

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

Delete

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

DELETE FROM Addresses WHERE id ='c04b66f130d14478b7978452aeedb8ad'

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	String	False		The unique identifier for the item
CreatedAt	Datetime	True		The datetime when the item was created
AddressTypeId	String	False		The unique identifier for the item The allowed values are DELIVERY, ACCOUNTS, SALES, PURCHASING.
Region	String	False		The address state/province/region
PostalCode	String	False		The address postal code/zipcode
DeletedAt	Datetime	True		The datetime when the item was deleted
BankAccountId	String	False	BankAccounts.Id	The unique identifier for the item
IsMainAddress	Bool	False		Indicates whether this is a main address
CountryId	String	False	Countries.Id	The unique identifier for the item
DisplayedAs	String	False		The name of the resource
City	String	False		The address town/city
ContactId	String	False	Contacts.Id	The unique identifier for the item
LegacyId	Int	False		The legacy ID for the item
CountryGroupId	String	False	CountryGroups.Id	The unique identifier for the item
AddressLine2	String	False		The second line of the address
AddressLine1	String	False		The first line of the address
Name	String	False		The name of the address
UpdatedAt	Datetime	True		The datetime when the item was last updated
DeletedSince	Datetime	True

Attachments

Query Attachments in Sage Business Cloud Accounting.

Table Specific Information

Query the available Attachments

Select

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

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

For example, the following queries are processed server side:

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

Update

Updates are performed based on Id.

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

Delete

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

DELETE FROM Attachments WHERE id ='123'

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	String	False		The unique identifier for the item
CreatedAt	Datetime	True		The datetime when the attachment was created
DisplayedAs	String	False		The name of the resource
FileExtension	String	False		The file extension of the attachment
TransactionId	String	False	Transactions.Id	The unique identifier for the item
File	String	False		The base64 encoded representation of the file
AttachmentContextTypeId	String	False		The unique identifier for the item The allowed values are BANK_TRANSFER, CONTACT, JOURNAL, OTHER_PAYMENT, PURCHASE_CORRECTIVE_INVOICE, PURCHASE_CREDIT_NOTE, PURCHASE_INVOICE, SALES_CORRECTIVE_INVOICE, SALES_CREDIT_NOTE, SALES_ESTIMATE, SALES_INVOICE, SALES_QUOTE, BUSINESS.
_filePath	String	False		The file path of the attachment
FileSize	Int	False		The file size of the attachment in Bytes
DeletedAt	Datetime	True		The datetime when the item was deleted
FileName	String	False		The file name of the attachment
UpdatedAt	Datetime	True		The datetime when the attachment was last updated
MimeType	String	False		The mime type of the attachment
Description	String	False		The description of the attachment
AttachmentContextDisplayedAs	String	False		The name of the resource
AttachmentContextLegacyId	Int	False		The legacy ID for the item
AttachmentContextId	String	False		The unique identifier for the item
LegacyAttachmentContextType	String	True
LegacyAttachmentContextId	String	False
DeletedSince	Datetime	True

BankAccounts

Query BankAccounts in Sage Business Cloud Accounting.

Table Specific Information

Query the available BankAccounts

Select

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

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

For example, the following queries are processed server side:

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

Insert

Create a simple bank account.

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

Update

Updates are performed based on Id.

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

Delete

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

DELETE FROM BankAccounts WHERE id ='80b34e32b85a46c39a71c3627d577fa2'

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	String	False		The unique identifier for the item
BankAccountTypeId	String	False		The unique identifier for the item The allowed values are CHECKING, SAVINGS, CREDIT_CARD, CASH_IN_HAND, LOAN, OTHER.
Balance	String	False		The bank account balance
CreatedAt	Datetime	True		The datetime when the item was created
SortCode	String	False		The sort code
Iban	String	False		The iban
Bic	String	False		The bic
AccountName	String	False		The account name
AccountNumber	String	False		The account number
MainContactId	String	False		The fax number of the contact
DefaultPaymentMethodId	String	False	PaymentMethods.Id	The unique identifier for the item
Deletable	Bool	False		Indicates whether or not the bank account can be deleted
LedgerAccountId	String	False	LedgerAccounts.Id	The unique identifier for the item
MainAddressId	String	False	Addresses.Id	The unique identifier for the item
DeletedAt	Datetime	True		The datetime when the item was deleted
Editable	Bool	False		Indicates whether or not the bank account can be edited
DisplayedAs	String	False		The name of the resource
JournalCodeId	String	False	JournalCodes.Id	The unique identifier for the item
NominalCode	Int	False		The nominal code of the bank account
LegacyId	Int	False		The legacy ID for the item
UpdatedAt	Datetime	True		The datetime when the item was last updated
DeletedSince	Datetime	True
ExcludeStripe	Boolean	True

BankDeposits

Query BankDeposits in Sage Business Cloud Accounting.

Table Specific Information

Query the available BankDeposits

Select

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

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

For example, the following queries are processed server side:

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

Delete

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

DELETE FROM BankDeposits WHERE id ='123'

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	String	False		The unique identifier for the item
CreatedAt	Datetime	True		The datetime when the item was created
DisplayedAs	String	False		The name of the resource
TransactionTypeId	String	False	TransactionTypes.id	The unique identifier for the item
ChequeAmount	String	False		Cheque total in the deposit
TransactionId	String	False	Transactions.id	The unique identifier for the item
Date	Datetime	True		User generated date of transaction, not necessarily when it was created
Reference	String	False		Reference
UpdatedAt	Datetime	True		The datetime when the item was last updated
LegacyId	Int	False		The legacy ID for the item
DeletedAt	Datetime	True		The datetime when the item was deleted
FromBankAccountId	String	False	BankAccounts.Id	The unique identifier for the item
ToBankAccountId	String	False	BankAccounts.Id	The unique identifier for the item
TotalAmount	String	False		Total of cash and cheques in the deposit
CashAmount	String	False		Cash total in the deposit
DeletedSince	Datetime	True
FromDate	Datetime	True
ToDate	Datetime	True

BankOpeningBalances

Query BankOpeningBalances in Sage Business Cloud Accounting.

Table Specific Information

Query the available BankOpeningBalances

Select

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

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

For example, the following queries are processed server side:

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

Insert

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

Update

Updates are performed based on Id.

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

Delete

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

DELETE FROM BankOpeningBalances WHERE id ='123'

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	String	False		The unique identifier for the item
CreatedAt	Datetime	True		The datetime when the item was created
DisplayedAs	String	False		The name of the resource
TransactionId	String	False	Transactions.Id	The unique identifier for the item
Date	Datetime	True		The date of the opening balance
Credit	String	False		The credit amount of the opening balance
BankAccountId	String	False	BankAccounts.Id	The unique identifier for the item
UpdatedAt	Datetime	True		The datetime when the item was last updated
LegacyId	Int	False		The legacy ID for the item
Debit	String	False		The debit amount of the opening balance

BankReconciliations

Query BankReconciliations in Sage Business Cloud Accounting.

Table Specific Information

Query the available BankReconciliations

Select

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

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

For example, the following queries are processed server side:

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

Update

Updates are performed based on Id.

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

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	String	False		The unique identifier for the item
CreatedAt	Datetime	True		The datetime when the item was created
DisplayedAs	String	False		The name of the resource
BalanceDifference	String	False		The difference between the statement end balance and the reconciled balance
TotalPaid	String	False		The total amount paid
Reference	String	False		A reference for the bank reconciliation
TotalReceived	String	False		The total amount received
StatusDisplayedAs	String	False		The bank reconciliation status name
StatusId	String	False		The bank reconciliation status id
BankAccountId	String	False	BankAccounts.Id	The unique identifier for the item
StatementDate	String	False		The date of the bank reconciliation
UpdatedAt	Datetime	True		The datetime when the item was last updated
LegacyId	Int	False		The legacy ID for the item
StatementEndBalance	String	False		The statement end balance for the reconciliation
StartingBalance	String	False		The starting balance of the bank reconciliation
ClosingBalance	String	False		The closing balance of the bank reconciliation
ReconciledBalance	String	False		The reconciled balance of the bank reconciliation

BankTransfers

Query BankTransfers in Sage Business Cloud Accounting.

Table Specific Information

Query the available BankTransfers

Select

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

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

For example, the following queries are processed server side:

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

Insert

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

Update

Updates are performed based on Id.

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

Delete

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

DELETE FROM BankTransfers WHERE id ='123'

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	String	False		The unique identifier for the item
CreatedAt	Datetime	True		The datetime when the item was created
DisplayedAs	String	False		The name of the resource
TransactionId	String	False	Transactions.Id	The unique identifier for the item
Date	String	False		The date of the bank transfer
Reference	String	False		The reference for the bank transfer
Amount	String	False		The amount of the bank transfer
UpdatedAt	Datetime	True		The datetime when the item was last updated
LegacyId	Int	False		The legacy ID for the item
DeletedAt	Datetime	True		The datetime when the item was deleted
FromBankAccountId	String	False	BankAccounts.Id	The unique identifier for the item
ToBankAccountId	String	False	BankAccounts.Id	The unique identifier for the item
Description	String	False		The description for the bank transfer
DeletedSince	Datetime	True
FromDate	Datetime	True
ToDate	Datetime	True

BusinessExchangeRates

Query BusinessExchangeRates in Sage Business Cloud Accounting.

Table Specific Information

Query the available BusinessExchangeRates

Select

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

CurrencyId supports the '=' operator.

For example, the following queries are processed server side:

SELECT * FROM BusinessExchangeRates WHERE CurrencyId='USD'

Update

Updates are performed based on Id.

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

Delete

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

DELETE FROM BusinessExchangeRates WHERE CurrencyId ='USD'

Columns


Name	Type	ReadOnly	References	Description
CurrencyId [KEY]	String	False	Currencies.Id	The unique identifier for the item
UpdatedAt	Datetime	True		The datetime when the item was last updated
Rate	String	False		The exchange rate
DisplayedAs	String	False		Display text for the item
CreatedAt	Datetime	True		The datetime when the item was created
BaseCurrencyId	String	False	Currencies.Id	The unique identifier for the item
InverseRate	String	False		The inverse exchange rate

ContactAllocations

Query ContactAllocations in Sage Business Cloud Accounting.

Table Specific Information

Query the available ContactAllocations

Select

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

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

For example, the following queries are processed server side:

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

Update

Updates are performed based on Id.

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

Delete

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

DELETE FROM ContactAllocations WHERE id ='123'

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	String	False		The unique identifier for the item
CreatedAt	Datetime	True		The datetime when the item was created
DisplayedAs	String	False		The name of the resource
TransactionTypeId	String	False	TransactionTypes.Id	The unique identifier for the item
TransactionId	String	False	Transactions.Id	The unique identifier for the item
Date	Datetime	True		The date the allocation was made
AllocatedArtefactsAggregate	String	False		The payment lines of the payment
ContactId	String	False	Contacts.Id	The unique identifier for the item
UpdatedAt	Datetime	True		The datetime when the item was last updated
LegacyId	Int	False		The legacy ID for the item
DeletedAt	Datetime	True		The datetime when the item was deleted
LinksAggregate	String	False		Links for the resource
DeletedSince	Datetime	True

ContactOpeningBalances

Query ContactOpeningBalances in Sage Business Cloud Accounting.

Table Specific Information

Query the available ContactOpeningBalances

Select

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

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

For example, the following queries are processed server side:

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

Update

Updates are performed based on Id.

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

Delete

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

DELETE FROM ContactOpeningBalances WHERE id ='123'

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	String	False		The unique identifier for the item
TaxRateId	String	False	TaxRates.Id	The unique identifier for the item
CreatedAt	Datetime	True		The datetime when the item was created
TransactionTypeId	String	False	TransactionTypes.Id	The unique identifier for the item
NetAmount	String	False		The net amount of the opening balance
CurrencyId	String	False	Currencies.Id	The unique identifier for the item
Reference	String	False		The reference for the opening balance
TotalAmount	String	False		The total amount of the opening balance
TaxBreakdownAggregate	String	False		The tax breakdown for the opening balance
DeletedAt	Datetime	True		The datetime when the item was deleted
ContactOpeningBalanceTypeId	String	False	ContactOpeningBalances.Id	The unique identifier for the item
DisplayedAs	String	False		The name of the resource
TaxAmount	String	False		The tax amount of the opening balance
TransactionId	String	False	Transactions.Id	The unique identifier for the item
ContactId	String	False	Contacts.Id	The unique identifier for the item
Details	String	False		A description of the opening balance
LegacyId	Int	False		The legacy ID for the item
ContactTypeId	String	False		The allowed values are CUSTOMER, VENDOR.
Date	String	False		The date of the opening balance
UpdatedAt	Datetime	True		The datetime when the item was last updated
ExchangeRate	String	False		The exchange rate for the opening balance
DeletedSince	Datetime	True

ContactPayments

Query ContactPayments in Sage Business Cloud Accounting.

Table Specific Information

Query the available ContactPayments

Select

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

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

For example, the following queries are processed server side:

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

Update

Updates are performed based on Id.

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

Delete

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

DELETE FROM ContactPayments WHERE id ='123'

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	String	False		The unique identifier for the item
TaxRateId	String	False	TaxRates.Id	The unique identifier for the item
BaseCurrencyCurrencyCharge	String	False		The currency conversion charges in base currency
CreatedAt	Datetime	True		The datetime when the item was created
LinksAggregate	String	False		Links for the resource
AllocatedArtefactsAggregate	String	False		The allocated artefacts
TransactionTypeId	String	False	TransactionTypes.Id	The unique identifier for the item
NetAmount	String	False		The net amount of the payment
BaseCurrencyTaxAmount	String	False		The tax amount of the payment in base currency
CurrencyId	String	False	Currencies.Id	The unique identifier for the item
PaymentMethodId	String	False	PaymentMethods.Id	The unique identifier for the item
BaseCurrencyTotalAmount	String	False		The total amount of the payment in base currency
Reference	String	False		A reference for the payment
TotalAmount	String	False		The total amount of the payment
DeletedAt	Datetime	True		The datetime when the item was deleted
BankAccountId	String	False	BankAccounts.Id	The unique identifier for the item
BaseCurrencyNetAmount	String	False		The net amount of the payment in base currency
PaymentOnAccountContactName	String	False		The name of the contact when the payment on account was created
PaymentOnAccountCreatedAt	Datetime	True		The datetime when the item was created
PaymentOnAccountBaseCurrencyOutstandingAmount	String	False		The outstanding amount of the payment on account in base currency
PaymentOnAccountNetAmount	String	False		The net amount of the payment on account
PaymentOnAccountBaseCurrencyTaxAmount	String	False		The tax amount of the payment on account in base currency
PaymentOnAccountCurrencyId	String	False	Currencies.Id	The unique identifier for the item
PaymentOnAccountBaseCurrencyTotalAmount	String	False		The total amount of the payment on account in base currency
PaymentOnAccountReference	String	False		The reference for the payment on account
PaymentOnAccountTotalAmount	String	False		The total amount of the payment on account
PaymentOnAccountId	String	False		The unique identifier for the item
PaymentOnAccountBaseCurrencyNetAmount	String	False		The net amount of the payment on account in base currency
PaymentOnAccountStatusDisplayedAs	String	False		The name of the resource
PaymentOnAccountStatusLegacyId	Int	False		The legacy ID for the item
PaymentOnAccountStatusId	String	False		The unique identifier for the item
PaymentOnAccountContactReference	String	False		The reference of the contact when the payment on account was created
PaymentOnAccountDisplayedAs	String	False		The name of the resource
PaymentOnAccountTaxAmount	String	False		The tax amount of the payment on account
PaymentOnAccountContactDisplayedAs	String	False		The name of the resource
PaymentOnAccountContactLegacyId	Int	False		The legacy ID for the item
PaymentOnAccountContactId	String	False		The unique identifier for the item
PaymentOnAccountOutstandingAmount	String	False		The outstanding amount of the payment on account
PaymentOnAccountLegacyId	Int	False		The legacy ID for the item
PaymentOnAccountDate	String	False		The date of the payment on account
PaymentOnAccountExchangeRate	String	False		The exchange rate for the payment on account
PaymentOnAccountUpdatedAt	Datetime	True		The datetime when the item was last updated
Editable	Bool	False		Indicates whether payment can be edited
DisplayedAs	String	False		The name of the resource
TaxAmount	String	False		The tax amount of the payment
TransactionId	String	False	Transactions.Id	The unique identifier for the item
ContactId	String	False	Contacts.Id	The unique identifier for the item
LegacyId	Int	False		The legacy ID for the item
Date	String	False		The date the payment was made
UpdatedAt	Datetime	True		The datetime when the item was last updated
ExchangeRate	String	False		The exchange rate of the payment
DeletedSince	Datetime	True
FromDate	Datetime	True
ToDate	Datetime	True

ContactPeople

Query ContactPeople in Sage Business Cloud Accounting.

Table Specific Information

Query the available ContactPeople

Select

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

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

For example, the following queries are processed server side:

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

Delete

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

DELETE FROM ContactPeople WHERE id ='69d00f6931614e3fbb7ccad85fb84af0'

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	String	False		The unique identifier for the item
CreatedAt	Datetime	True		The datetime when the item was created
DisplayedAs	String	False		The name of the resource
Mobile	String	False		The mobile number of the contact person
Name	String	False		The name of the contact person
Fax	String	False		The fax number of the contact person
Telephone	String	False		The telephone number of the contact person
ContactPersonTypeAggregate	String	False		The contact person types for the contact person
Email	String	False		The email address of the contact person
UpdatedAt	Datetime	True		The datetime when the item was last updated
DeletedAt	Datetime	True		The datetime when the item was deleted
LegacyId	Int	False		The legacy ID for the item
IsMainContact	Bool	False		Indicates whether this is the main contact person
AddressId	String	False	Addresses.Id	The unique identifier for the item
JobTitle	String	False		The job title of the contact person
IsPreferredContact	Bool	False		Indicates that this contact person is a preferred contact
DeletedSince	Datetime	True
ContactId	String	False

Contacts

Query Contacts in Sage Business Cloud Accounting.

Table Specific Information

Query the available Contacts

Select

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

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

For example, the following queries are processed server side:

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

Insert

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

Update

Updates are performed based on Id.

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

Delete

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

DELETE FROM Contacts WHERE id ='123'

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	String	False		The unique identifier for the item
DisplayedAs	String	False		The name of the resource
CreditDays	Int	False		Custom credit days for the contact. If returned as null in a GET response, you may want to GET /invoice_settings and use 'vendor_credit_days' as default/fallback according to your use case.
Reference	String	False		The reference for the contact
TaxNumber	String	False		The tax number for the contact
MainAddressId	String	False	Addresses.Id	The unique identifier for the item
GdprObfuscated	Bool	False		General Data Protection Regulation (GDPR) came into effect on 25th May 2018. It introduces new rules for how business owners manage their contacts' personal data. When this field returns 'true', means that the contact has been requested to be obfuscated and you can not create any artifact (sales invoices, purchase invoices, ...) but you can still check previously created artifacts.
DefaultSalesTaxRateId	String	False	TaxRates.Id	The unique identifier for the item
AuxReference	String	False		Auxiliary reference. Used for German
ProductSalesPriceTypeId	String	False	ProductSalesPriceTypes.Id	The unique identifier for the item
SortCode	String	False		The sort code
Iban	String	False		The iban
Bic	String	False		The bic
AccountName	String	False		The account name
AccountNumber	String	False		The account number
Name	String	False		The name of the contact
Deletable	Bool	False		Indicates whether the ledger entry has been deleted or not
AuxiliaryAccount	String	False		Auxiliary account - used when auxiliary accounting is enabled in business settings
DefaultPurchaseLedgerAccountId	String	False	LedgerAccounts.Id	The unique identifier for the item
DeliveryAddressId	String	False	Addresses.Id	The unique identifier for the item
Balance	String	False		The contact balance
Locale	String	False		The locale for the contact
UpdatedAt	Datetime	True		The datetime when the item was last updated
RegisteredNumber	String	False		The registered number of the contact's business. Only used for German businesses and represents the
Email	String	False		The email address for the given contact
LegacyId	Int	False		The legacy ID for the item
ContactTypesAggregate	String	False		The contact types for the contact
TaxTreatmentRestOfWorldTax	Bool	False		Indicates a contact where rest of world tax rules should be applied
TaxTreatmentEuTaxRegistered	Bool	False		Indicates a contact where EC tax rules should be applied
TaxTreatmentEuNotTaxRegistered	Bool	False		Indicates an EU contact without a tax number - home tax rules should be applied
TaxTreatmentHomeTax	Bool	False		Indicates a contact where home country tax rules should be applied
CreditLimit	String	False		Custom credit limit amount for the contact
CreatedAt	Datetime	True		The datetime when the item was created
LinksAggregate	String	False		Links for the resource
TaxCalculation	String	False		The tax calculation method - used for French VAT & Recargo
MainContactPersonId	String	False		The unique identifier for the item
ContactPersonTypeAggregate	String	False		The contact person types for the contact person
SourceGuid	String	False		Used when importing contacts from external sources
CurrencyId	String	False	Currencies.Id	The unique identifier for the item
CreditTermsAndConditions	String	False		Custom terms and conditions for the contact (Customers only)
Notes	String	False		The notes for the contact
DeletedAt	Datetime	True		The datetime when the item was deleted
System	Bool	False		Identifies a contact as being a system contact used for processing specific transaction types and reserved specifically for those transaction types such as tax return payments/refunds.
DefaultSalesLedgerAccountId	String	False	LedgerAccounts.Id	The unique identifier for the item
ShowBalance	String	False
DeletedSince	Datetime	True
ContactTypeId	String	True
ExcludeSystem	String	True
NIBased	Bool	True		Determines if a business is located in Northern Ireland
TaxTreatmentHomeTax	Bool	True		Explains which tax treatment should be applied for a contact.
TaxTreatmentEuTaxRegistered	Bool	True		Explains which tax treatment should be applied for a contact.
TaxTreatmentEuNotTaxRegistered	Bool	True		Explains which tax treatment should be applied for a contact.
TaxTreatmentRestOfWorldTax	Bool	True		Explains which tax treatment should be applied for a contact.
TaxTreatmentIsImporter	Bool	True		Explains which tax treatment should be applied for a contact.
CISRegistered	Bool	True		Boolean flag set to true if the contact is CIS registered.

ObjectGuid supports the '=' operator.

For example, the following queries are processed server side:

SELECT * FROM HostedArtefactPaymentSettings WHERE ObjectGuid='abc'

Delete

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

DELETE FROM HostedArtefactPaymentSettings WHERE id ='123'

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	String	False		The unique identifier for the item
ObjectGuid	String	False		The sageone_guid of the object you are disabling card payments for.
DisplayedAs	String	False		The name of the resource
DisablePayment	Bool	False		The flag to disable online payments.
LegacyId	Int	False		The legacy ID for the item

InvoiceSettings

Query InvoiceSettings in Sage Business Cloud Accounting.

Table Specific Information

Query the available InvoiceSettings

Select

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

Columns


Name	Type	ReadOnly	References	Description
LineItemUnitPrice	String	False		The user defined unit price column title
LineItemDescription	String	False		The user defined description column title
LineItemQuantity	String	False		The user defined quantity column title
LineItemDiscount	String	False		The user defined discount column title
DeliveryNoteShowSignature	Bool	False		Indicates whether to include the signature lines on delivery notes
PaymentBankAccountId	String	False	BankAccounts.Id	The unique identifier for the item
PrintStatementsDaysOverdue	Bool	False		Indicates wether days of overdue are printed in the contact's statement
PrintStatementsTableOfBalances	Bool	False		Indicates wether table of balances is printed in the contact's statement
PrintContactDefaultDeliveryAddress	String	False		Indicates which contact address is used for the delivery address of the invoice. Valid options are: INVOICE_ADDRESS, CONTACT_DELIVERY_ADDRESS, NONE.
PrintContactTelephone	Bool	False		Indicates wether telephone is printed on the invoice
PrintContactWebsite	Bool	False		Indicates wether website is printed on the invoice
PrintContactMobile	Bool	False		Indicates wether mobile is printed on the invoice
PrintContactBusinessName	Bool	False		Indicates wether business name is printed on the invoice
PrintContactDueDate	Bool	False		Indicates wether due date is printed on the invoice
PrintContactEmailAddress	Bool	False		Indicates wether email address is printed on the invoice
InsurerDisplayedAs	String	False		The name of the resource
InsurerCreditDays	Int	False		Custom credit days for the contact. If returned as null in a GET response, you may want to GET /invoice_settings and use 'vendor_credit_days' as default/fallback according to your use case.
InsurerReference	String	False		The reference for the contact
InsurerTaxNumber	String	False		The tax number for the contact
InsurerMainAddressId	String	False	Addresses.Id	The unique identifier for the item
InsurerGdprObfuscated	Bool	False		General Data Protection Regulation (GDPR) came into effect on 25th May 2018. It introduces new rules for how business owners manage their contacts' personal data. When this field returns 'true', means that the contact has been requested to be obfuscated and you can not create any artifact (sales invoices, purchase invoices, ...) but you can still check previously created artifacts.
InsurerDefaultSalesTaxRateId	String	False	TaxRates.Id	The unique identifier for the item
InsurerAuxReference	String	False		Auxiliary reference. Used for German
InsurerProductSalesPriceTypeId	String	False	ProductSalesPriceTypes.Id	The unique identifier for the item
InsurerBankAccountDetailsSortCode	String	False		The sort code
InsurerBankAccountDetailsIban	String	False		The iban
InsurerBankAccountDetailsBic	String	False		The bic
InsurerBankAccountDetailsAccountName	String	False		The account name
InsurerBankAccountDetailsAccountNumber	String	False		The account number
InsurerName	String	False		The name of the contact
InsurerDeletable	Bool	False		Indicates whether the ledger entry has been deleted or not
InsurerAuxiliaryAccount	String	False		Auxiliary account - used when auxiliary accounting is enabled in business settings
InsurerDefaultPurchaseLedgerAccountId	String	False	LedgerAccounts.Id	The unique identifier for the item
InsurerDeliveryAddressId	String	False	Addresses.Id	The unique identifier for the item
InsurerBalance	String	False		The contact balance
InsurerLocale	String	False		The locale for the contact
InsurerUpdatedAt	Datetime	True		The datetime when the item was last updated
InsurerRegisteredNumber	String	False		The registered number of the contact's business. Only used for German businesses and represents the
InsurerEmail	String	False		The email address for the given contact
InsurerLegacyId	Int	False		The legacy ID for the item
ContactTypesAggregate	String	False		The contact types for the contact
InsurerTaxTreatmentRestOfWorldTax	Bool	False		Indicates a contact where rest of world tax rules should be applied
InsurerTaxTreatmentEuTaxRegistered	Bool	False		Indicates a contact where EC tax rules should be applied
InsurerTaxTreatmentEuNotTaxRegistered	Bool	False		Indicates an EU contact without a tax number - home tax rules should be applied
InsurerTaxTreatmentHomeTax	Bool	False		Indicates a contact where home country tax rules should be applied
InsurerCreditLimit	String	False		Custom credit limit amount for the contact
InsurerCreatedAt	Datetime	True		The datetime when the item was created
InsurerId	String	False		The unique identifier for the item
InsurerTaxCalculation	String	False		The tax calculation method - used for French VAT & Recargo
InsurerMainContactPersonId	String	False		The unique identifier for the item
InsurerSourceGuid	String	False		Used when importing contacts from external sources
InsurerCurrencyDisplayedAs	String	False		The name of the resource
InsurerCurrencyLegacyId	Int	False		The legacy ID for the item
InsurerCurrencyId	String	False		The unique identifier for the item
InsurerCreditTermsAndConditions	String	False		Custom terms and conditions for the contact (Customers only)
InsurerNotes	String	False		The notes for the contact
InsurerDeletedAt	Datetime	True		The datetime when the item was deleted
InsurerSystem	Bool	False		Identifies a contact as being a system contact used for processing specific transaction types and reserved specifically for those transaction types such as tax return payments/refunds.
InsurerDefaultSalesLedgerAccountId	String	False	LedgerAccounts.Id	The unique identifier for the item
VendorCreditDays	Int	False		The default delay within which the business has to pay any vendor's invoice
InsuranceText	String	False		The insurance mention to be displayed on invoices (France only)
SeparateInvoiceCreditNoteNumbering	Bool	False		Indicates whether to use separate or combined number sequences for invoices and credit notes
EstimateNumberPrefix	String	False		The prefix to use for sales estimates
QuoteTermsAndConditions	String	False		The default terms and conditions to include on quotes
InsuranceArea	String	False		The insurance area to be displayed on invoices (France only)
QuickEntryPrefix	String	False		The prefix to use for quick entries
CustomerCreditDays	Int	False		The default delay within which the business.
DocumentHeadingsSalesEstimate	String	False		The customisable sales estimate document header
DocumentHeadingsSalesInvoice	String	False		The customisable sales invoice document header
DocumentHeadingsSalesCorrectiveInvoice	String	False		The customisable sales corrective invoice document header
DocumentHeadingsProForma	String	False		The customisable pro forma document header
DocumentHeadingsRemittanceAdvice	String	False		The customisable remittance advice document header
DocumentHeadingsSalesCreditNote	String	False		The customisable sales credit note document header
DocumentHeadingsStatement	String	False		The customisable statement document header
DocumentHeadingsDeliveryNote	String	False		The customisable delivery note document header
DocumentHeadingsSalesQuote	String	False		The customisable sales quote document header
SalesCreditNoteNumberPrefix	String	False		The prefix to use for sales credit notes
QuoteDefaultDaysToExpiry	Int	False		The default number of days before quotes expire
FooterDetailsColumn3	String	False		The pdf footer details for column 3
FooterDetailsColumn2	String	False		The pdf footer details for column 2
FooterDetailsColumn1	String	False		The pdf footer details for column 1
QuoteNumberPrefix	String	False		The prefix to use for sales quotes
NextCreditNoteNumber	Int	False		The next credit note number
PromptPaymentPercentage	String	False		The percentage applied to late payment of invoices (France only)
ShowAutoEntrepreneur	Bool	False		Indicates whether to include auto entrepreneur details on invoices (France only)
NextSalesCorrectiveInvoiceNumber	Int	False		The next sales corrective invoice number
InvoiceTermsAndConditions	String	False		The default terms and conditions to include on invoices
DeliveryNoteTermsAndConditions	String	False		The default terms and conditions to include on delivery notes
InsuranceType	String	False		The insurance type to be displayed on invoices (France only)
NextInvoiceNumber	Int	False		The next invoice number
SalesInvoiceNumberPrefix	String	False		The prefix to use for sales invoices
ShowInsurance	Bool	False		Indicates whether to include insurance details on invoices (France only)
DefaultNoteOnInvoice	String	False		The default notes to include on invoices
SalesCorrectiveInvoiceNumberPrefix	String	False		The sales corrective invoice number prefix (Spain only)
DefaultNoteOnCreditNote	String	False		The default notes to include on credit notes
EstimateDefaultDaysToExpiry	Int	False		The default number of days before estimates expire
NextQuoteNumber	Int	False		The next quote number
EstimateTermsAndConditions	String	False		The default terms and conditions to include on estimates
LatePaymentPercentage	String	False		The percentage charge applied to late payment of invoices (France only)
DeliveryNoteShowNotes	Bool	False		Indicates whether to include the document notes on delivery notes
DeliveryNoteShowPicked	Bool	False		Indicates whether to include the picked column on delivery notes
DeliveryNoteShowContactDetails	Bool	False		Indicates whether to include contact details on delivery notes

JournalCodes

Query JournalCodes in Sage Business Cloud Accounting.

Table Specific Information

Query the available JournalCodes

Select

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

UpdatedOrCreatedSince supports the '=' operator.

For example, the following queries are processed server side:

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

Update

Updates are performed based on Id.

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

Delete

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

DELETE FROM JournalCodes WHERE id ='123'

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	String	False		The unique identifier for the item
CreatedAt	Datetime	True		The datetime when the item was created
DisplayedAs	String	False		The name of the resource
Name	String	False		The name of the journal code
Code	String	False		The code of the journal code
Reserved	Bool	False		Indicates whether the journal code is reserved
UpdatedAt	Datetime	True		The datetime when the item was last updated
LegacyId	Int	False		The legacy ID for the item
ControlName	String	False		The control name of the journal code

Journals

Query Journals in Sage Business Cloud Accounting.

Table Specific Information

Query the available Journals

Select

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

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

For example, the following queries are processed server side:

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

Insert

Insert a journal with aggregate value.

INSERT INTO  [Journals] ( JournalLineAggregate, date, reference) VALUES ('{"ledger_account_id": "1fcc2765741311ea8d3c022ace1a91e2","debit": 12,"credit": 0,"details": "details","tax_reconciled": true,"cleared": true,"bank_reconciled": true},{"ledger_account_id": "1fcc2765741311ea8d3c022ace1a91e2","debit": 0,"credit": 12,"details": "details","tax_reconciled": true,"cleared": true,"bank_reconciled": true}','2019-07-12','Journal Reference')

Insert a journal using temp tables.

INSERT INTO  [JournalLineItemsAggregate#Temp] (credit, debit, details, ledgerAccountId) VALUES ('10', '0', 'details1', '6035f3d39d7511e9a2b70e070fead9d0')
INSERT INTO  [JournalLineItemsAggregate#Temp] (credit, debit, details, ledgerAccountId) VALUES ('0', '10', 'details2', '6035f3d39d7511e9a2b70e070fead9d0')
INSERT INTO  [Journals] (JournalLineAggregate, date, reference) VALUES ('JournalLineItemsAggregate#Temp','2019-07-12','Reference2')

Delete

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

DELETE FROM Journals WHERE id ='123'

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	String	False		The unique identifier for the item
CreatedAt	Datetime	True		The datetime when the item was created
DisplayedAs	String	False		The name of the resource
JournalLineAggregate	String	False		The journal lines
TransactionTypeId	String	False	TransactionTypes.Id	The unique identifier for the item
TransactionId	String	False	Transactions.Id	The unique identifier for the item
Date	Datetime	True		The date of the journal
Reference	String	False		A reference for the journal
Migrated	Bool	False		Indicates if the journal was migrated from another system.
UpdatedAt	Datetime	True		The datetime when the item was last updated
LegacyId	Int	False		The legacy ID for the item
DeletedAt	Datetime	True		The datetime when the item was deleted
Total	String	False		The total for the journal
Description	String	False		A description of the journal
JournalCodeId	String	False	JournalCodes.Id	The unique identifier for the item
DeletedSince	Datetime	True

LedgerAccountOpeningBalances

Query LedgerAccountOpeningBalances in Sage Business Cloud Accounting.

Table Specific Information

Query the available LedgerAccountOpeningBalances

Select

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

UpdatedOrCreatedSince supports the '=' operator.

For example, the following queries are processed server side:

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

Update

Updates are performed based on Id.

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

Delete

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

DELETE FROM LedgerAccountOpeningBalances WHERE id ='123'

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	String	False		The unique identifier for the item
CreatedAt	Datetime	True		The datetime when the item was created
Details	String	False		A description of the opening balance
DisplayedAs	String	False		The name of the resource
Credit	String	False		The credit amount of the opening balance
LedgerAccountId	String	False	LedgerAccounts.Id	The unique identifier for the item
UpdatedAt	Datetime	True		The datetime when the item was last updated
LegacyId	Int	False		The legacy ID for the item
Debit	String	False		The debit amount of the opening balance

LedgerAccounts

Query LedgerAccounts in Sage Business Cloud Accounting.

Table Specific Information

Query the available LedgerAccounts

Select

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

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

For example, the following queries are processed server side:

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

Update

Updates are performed based on Id.

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

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	String	False		The unique identifier for the item
TaxRateId	String	False	TaxRates.Id	The unique identifier for the item
CreatedAt	Datetime	True		The datetime when the item was created
VisibleInJournals	Bool	False		Indicates whether the ledger account is displayed in this area of the application
LedgerAccountClassificationId	String	False	LedgerAccountClassifications.Id	The unique identifier for the item
LedgerAccountGroupDisplayedAs	String	False		The name of the resource
LedgerAccountGroupLegacyId	Int	False		The legacy ID for the item
LedgerAccountGroupId	String	False		The unique identifier for the item
VisibleInOtherPayments	Bool	False		Indicates whether the ledger account is displayed in this area of the application
BalanceDetailsToDate	String	False		The to date filter
BalanceDetailsCreditOrDebit	String	False		Is the balance a debit or credit
BalanceDetailsCredits	String	False		The credit balance
BalanceDetailsBalance	String	False		The account balance
BalanceDetailsFromDate	String	False		The from date filter
BalanceDetailsDebits	String	False		The debit balance
VisibleScopes	String	False		The visible scopes.
VisibleInSales	Bool	False		Indicates whether the ledger account is displayed in this area of the application
IncludedInChart	Bool	False		Indicates whether the ledger account is included in the chart of accounts
VisibleInOtherReceipts	Bool	False		Indicates whether the ledger account is displayed in this area of the application
DisplayFormatted	String	False		The display name formatted based on coa_list_order in settings
VisibleInReporting	Bool	False		Indicates whether the ledger account is displayed in this area of the application
DisplayedAs	String	False		The name of the resource
ControlName	String	False		The control name for the ledger account
VisibleInBanking	Bool	False		Indicates whether the ledger account is displayed in this area of the application
LedgerAccountTypeId	String	False		The unique identifier for the item The allowed values are SALES, OTHER_INCOME, DIRECT_EXPENSES, OVERHEADS, DEPRECIATION, CURRENT_ASSETS, FIXED_ASSETS, FUTURE_ASSETS, BANK, CURRENT_LIABILITY, FUTURE_LIABILITY, v, LINE_OF_CREDIT.
NominalCode	Int	False		The nominal code of the ledger account
LegacyId	Int	False		The legacy ID for the item
FixedTaxRate	Bool	False		Indicates whether the default tax rate is fixed or may be changed
IsControlAccount	Bool	False		Indicates whether the ledger account is a control account
Name	String	False		The name for the ledger account. Changes to this field do not propagate to other resources, namely not to the name of the associated bank_account (unlike the behaviour of the UI).
UpdatedAt	Datetime	True		The datetime when the item was last updated
DisplayName	String	False		The display name for the ledger account
VisibleInExpenses	Bool	False		Indicates whether the ledger account is displayed in this area of the application
VisibleIn	String	False
NotVisibleIn	String	False
ShowIncludedInChart	Bool	False
ShowControlAccounts	Bool	False
ShowBalanceDetails	Bool	False
ExcludeDeletedEntries	Bool	False
FromDate	Datetime	True
ToDate	Datetime	True
Search	String	True

MigrationTaxReturns

Query MigrationTaxReturns in Sage Business Cloud Accounting.

Table Specific Information

Query the available MigrationTaxReturns

Select

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

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	String	False		The unique identifier for the item
CreatedAt	Datetime	True		The datetime when the item was created
IeBoxT4	String	False		The value of box T4
IeBoxT3	String	False		The value of box T3
IeBoxT2	String	False		The value of box T2
IeBoxT1	String	False		The value of box T1
IeBoxES2	String	False		The value of box ES2
IeBoxES1	String	False		The value of box ES1
IeBoxE2	String	False		The value of box E2
IeBoxE1	String	False		The value of box E1
DisplayedAs	String	False		The name of the resource
FromDate	Datetime	True		The start date of the tax return
LegacyId	Int	False		The legacy ID for the item
TaxReturnFrequencyId	String	False	TaxReturnFrequencies.Id	The unique identifier for the item
UpdatedAt	Datetime	True		The datetime when the item was last updated
GbBox9	String	False		The value of box 9
GbBox8	String	False		The value of box 8
GbBox7	String	False		The value of box 7
GbBox6	String	False		The value of box 6
GbBox5	String	False		The value of box 5
GbBox4	String	False		The value of box 4
GbBox3	String	False		The value of box 3
GbBox2	String	False		The value of box 2
GbBox1	String	False		The value of box 1
TotalAmount	String	False		The total of the tax return
ToDate	Datetime	True		The end date of the tax return

OpeningBalanceJournals

Query OpeningBalanceJournals in Sage Business Cloud Accounting.

Table Specific Information

Query the available OpeningBalanceJournals

Select

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

Insert

Insert a journal with aggregate value.

INSERT INTO  [OpeningBalanceJournals] ( BaseJournalLineAggregate, date, reference) VALUES ('{"ledger_account_id": "1fcc2765741311ea8d3c022ace1a91e2","debit": 12,"credit": 0,"details": "details","tax_reconciled": true,"cleared": true,"bank_reconciled": true},{"ledger_account_id": "1fcc2765741311ea8d3c022ace1a91e2","debit": 0,"credit": 12,"details": "details","tax_reconciled": true,"cleared": true,"bank_reconciled": true}','2019-07-12','Journal Reference')

Insert a journal using temp tables.

INSERT INTO  [JournalLineItemsAggregate#Temp] (credit, debit, details, ledgerAccountId) VALUES ('10', '0', 'details1', '6035f3d39d7511e9a2b70e070fead9d0')
INSERT INTO  [JournalLineItemsAggregate#Temp] (credit, debit, details, ledgerAccountId) VALUES ('0', '10', 'details2', '6035f3d39d7511e9a2b70e070fead9d0')
INSERT INTO  [OpeningBalanceJournals] (BaseJournalLineAggregate, date, reference) VALUES ('JournalLineItemsAggregate#Temp','2019-07-12','Reference2')

Delete

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

DELETE FROM OpeningBalanceJournals WHERE id ='123'

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	String	False		The unique identifier for the item
CreatedAt	Datetime	True		The datetime when the item was created
DisplayedAs	String	False		The name of the resource
BaseJournalLineAggregate	String	False		The journal lines
TransactionTypeId	String	False	TransactionTypes.Id	The unique identifier for the item
TransactionId	String	False	Transactions.Id	The unique identifier for the item
Date	Datetime	True		The date of the opening balance journal
Reference	String	False		A reference for the opening balance journal
UpdatedAt	Datetime	True		The datetime when the item was last updated
LegacyId	Int	False		The legacy ID for the item
DeletedAt	Datetime	True		The datetime when the item was deleted

OtherPayments

Query OtherPayments in Sage Business Cloud Accounting.

Table Specific Information

Query the available OtherPayments

Select

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

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

For example, the following queries are processed server side:

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

Update

Updates are performed based on Id.

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

Delete

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

DELETE FROM OtherPayments WHERE id ='123'

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	String	False		The unique identifier for the item
CreatedAt	Datetime	True		The datetime when the item was created
TransactionTypeDisplayedAs	String	False		The name of the resource
TransactionTypeLegacyId	Int	False		The legacy ID for the item
TransactionTypeId	String	False		The unique identifier for the item
NetAmount	String	False		The net amount of the payment
PaymentMethodId	String	False	PaymentMethods.Id	The unique identifier for the item
Deletable	Bool	False		Indicates whether or not the payment can be deleted
Reference	String	False		A reference of the payment
TotalAmount	String	False		The total amount of the payment
TaxAddressRegionDisplayedAs	String	False		The name of the resource
TaxAddressRegionLegacyId	Int	False		The legacy ID for the item
TaxAddressRegionId	String	False		The unique identifier for the item
WithholdingTaxRate	String	False		IRPF withheld tax rate
DeletedAt	Datetime	True		The datetime when the item was deleted
BankAccountId	String	False		The unique identifier for the item
Editable	Bool	False		Indicates whether or not the payment can be edited
DisplayedAs	String	False		The name of the resource
TaxAmount	String	False		The tax amount of the payment
ContactId	String	False	Contacts.Id	The unique identifier for the item
TransactionId	String	False	Transactions.Id	The unique identifier for the item
LegacyId	Int	False		The legacy ID for the item
Date	Datetime	True		The date of the payment
WithholdingTaxAmount	String	False		IRPF withheld tax amount
OtherPaymentLineItemAggregate	String	False		The payment lines of the payment
UpdatedAt	Datetime	True		The datetime when the item was last updated
DeletedSince	Datetime	True
FromDate	Datetime	True
HasAttachments	String	False
ToDate	Datetime	True

Products

Query Products in Sage Business Cloud Accounting.

Table Specific Information

Query the available Products

Select

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

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

For example, the following queries are processed server side:

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

Insert

Create a simple product.

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

Update

Updates are performed based on Id.

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

Delete

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

DELETE FROM Products WHERE id ='123'

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	String	False		The unique identifier for the item
SalesLedgerAccountId	String	False	LedgerAccounts.Id	The unique identifier for the item
CreatedAt	Datetime	True		The datetime when the item was created
SalesTaxRateId	String	False	TaxRates.Id	The unique identifier for the item
Active	Bool	False		Indicates whether the product is active
ItemCode	String	False		The item code for the product
PurchaseLedgerAccountId	String	False	LedgerAccounts.Id	The unique identifier for the item
SalesPricesAggregate	String	False		The sales prices for the product
DeletedAt	Datetime	True		The datetime when the item was deleted
PurchaseTaxRateId	String	False	TaxRates.Id	The unique identifier for the item
Notes	String	False		The notes for the product
CostPrice	String	False		The cost price of the product
UsualSupplierId	String	False	Contacts.Id	The unique identifier for the item
DisplayedAs	String	False		The name of the resource
Description	String	False		The product description
PurchaseDescription	String	False		The product purchase description
LegacyId	Int	False		The legacy ID for the item
SourceGuid	String	False		Used when importing products from external sources
UpdatedAt	Datetime	True		The datetime when the item was last updated
Search	String	False
DeletedSince	Datetime	True

ProductSalesPriceTypes

Query ProductSalesPriceTypes in Sage Business Cloud Accounting.

Table Specific Information

Query the available ProductSalesPriceTypes

Select

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

Active supports the '=' operator.

For example, the following queries are processed server side:

SELECT * FROM ProductSalesPriceTypes WHERE Active=false

Update

Updates are performed based on Id.

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

Delete

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

DELETE FROM ProductSalesPriceTypes WHERE id ='c9ec71a1fe5c4916a72a9efc22324663'

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	String	False		The unique identifier for the item
UpdatedAt	Datetime	True		The datetime when the item was last updated
Name	String	False		The name of the product sales price type
DisplayedAs	String	False		The name of the resource
CreatedAt	Datetime	True		The datetime when the item was created
Active	Bool	False		Indicates whether the price type is displayed in the application
LegacyId	Int	False		The legacy ID for the item

PurchaseCreditNotes

Query PurchaseCreditNotes in Sage Business Cloud Accounting.

Table Specific Information

Query the available PurchaseCreditNotes

Select

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

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

For example, the following queries are processed server side:

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

Insert

Create a simple purchase credit note.

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

Update

Updates are performed based on Id.

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

Delete

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

DELETE FROM PurchaseCreditNotes WHERE id ='123'

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	String	False		The unique identifier for the item
DisplayedAs	String	False		The name of the resource
Reference	String	False		The reference for the credit note
PurchaseCreditNoteLineItemAggregate	String	False		The credit note lines of the credit note
BaseCurrencyTotalAmount	String	False		The total amount of the credit note in base currency
BaseCurrencyNetAmount	String	False		The net amount of the credit note in base currency
ContactName	String	False		The name of the contact when the credit note was created
Migrated	Bool	False		Indicates if the purchase credit note was migrated from another system.
TaxAmount	String	False		The tax amount of the credit note
LastPaid	String	False		The date of the last payment
PaymentsAllocationsTotalDiscount	String	False		The total discount of all payments and allocations
TotalAmount	String	False		The total amount of the credit note
ExchangeRate	String	False		The exchange rate for the credit note
OutstandingAmount	String	False		The outstanding amount of the credit note
ContactId	String	False	Contacts.Id	The unique identifier for the item
NetAmount	String	False		The net amount of the credit note
ContactReference	String	False		The reference of the contact when the credit note was created
TaxAddressRegionDisplayedAs	String	False		The name of the resource
TaxAddressRegionLegacyId	Int	False		The legacy ID for the item
TaxAddressRegionId	String	False		The unique identifier for the item
TaxCalculationMethod	String	False		The tax calculation method, if applicable, for this purchase credit note, returns invoice or cash.
PaymentsAllocationsAggregate	String	False		The associated payments and allocations
UpdatedAt	Datetime	True		The datetime when the item was last updated
LegacyId	Int	False		The legacy ID for the item
TaxAnalysisAggregate	String	False		The credit note tax analysis
InverseExchangeRate	String	False		The inverse exchange rate for the credit note
TotalQuantity	String	False		The total quantity of the credit note
TransactionId	String	False	Transactions.Id	The unique identifier for the item
StatusDisplayedAs	String	False		The name of the resource
StatusLegacyId	Int	False		The legacy ID for the item
StatusId	String	False		The unique identifier for the item
DetailedTaxAnalysisBaseCurrencyTotalGoodsAmount	String	False		The total base currency amount relating to goods for all tax rates
DetailedTaxAnalysisBaseCurrencyTotalTax	String	False		The base currency total tax amount for all tax rates
DetailedTaxAnalysisTotalRetailerTax	String	False		The total retailer tax amount for all tax rates
DetailedTaxAnalysisBaseCurrencyTotal	String	False		The total base currency amount for all tax rates
DetailedTaxAnalysisTotalGoodsAmount	String	False		The total amount relating to goods for all tax rates
DetailedTaxAnalysisTotalNet	String	False		The total net amount for all tax rates
DetailedTaxAnalysisBaseCurrencyTotalServicesAmount	String	False		The total base currency amount relating to services for all tax rates
DetailedTaxAnalysisBaseCurrencyTotalNet	String	False		The base currency total net amount for all tax rates
DetailedTaxAnalysisTotalServicesAmount	String	False		The total amount relating to services for all tax rates
DetailedTaxAnalysisTotal	String	False		The total amount for all tax rates
DetailedTaxAnalysisTotalTax	String	False		The total tax amount for all tax rates
TransactionTypeId	String	False	Transactions.Id	The unique identifier for the item
CreatedAt	Datetime	True		The datetime when the item was created
PaymentsAllocationsTotalAmount	String	False		The total amount of all payments and allocations
VoidReason	String	False		The reason the credit note was voided
TaxReconciled	Bool	False		Indicates if the purchase credit note is tax reconciled or not.
Editable	Bool	False		Indicates whether artefact can be edited
VendorReference	String	False		The vendor reference for the credit note
CurrencyId	String	False	Currencies.Id	The unique identifier for the item
Notes	String	False		credit note notes
TotalPaid	String	False		The total paid amount of the credit note including any payments, allocations and discounts
DeletedAt	Datetime	True		The datetime when the item was deleted
BaseCurrencyTaxAmount	String	False		The tax amount of the credit note in base currency
Date	Datetime	True		The date of the credit note
BaseCurrencyOutstandingAmount	String	False		The outstanding amount of the credit note in base currency
ShowPaymentsAllocations	String	False
Search	String	False
FromDate	Datetime	True
ToDate	Datetime	True
DeletedSince	Datetime	True
HasAttachments	String	False
ShowPaymentsAllocations	String	True

PurchaseInvoices

Query PurchaseInvoices in Sage Business Cloud Accounting.

Table Specific Information

Query the available PurchaseInvoices

Select

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

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

For example, the following queries are processed server side:

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

Insert

Create a simple purchase invoice.

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

Update

Updates are performed based on Id.

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

Delete

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

DELETE FROM PurchaseInvoices WHERE id ='123'

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	String	False		The unique identifier for the item
DisplayedAs	String	False		The name of the resource
Reference	String	False		The reference for the invoice
BaseCurrencyTotalAmount	String	False		The total amount of the invoice in base currency
BaseCurrencyNetAmount	String	False		The net amount of the invoice in base currency
ContactName	String	False		The name of the contact when the invoice was created
Migrated	Bool	False		Indicates if the purchase invoice was migrated from another system.
DueDate	Datetime	True		The due date of the invoice
TaxAmount	String	False		The tax amount of the invoice
LastPaid	String	False		The date of the last payment
PaymentsAllocationsTotalDiscount	String	False		The total discount of all payments and allocations
WithholdingTaxRate	String	False		IRPF withheld Tax Rate (Spain only)
TotalAmount	String	False		The total amount of the invoice
ExchangeRate	String	False		The exchange rate for the invoice
OutstandingAmount	String	False		The outstanding amount of the invoice
ContactId	String	False	Contacts.Id	The unique identifier for the item
NetAmount	String	False		The net amount of the invoice
ContactReference	String	False		The reference of the contact when the invoice was created
TaxAddressRegionDisplayedAs	String	False		The name of the resource
TaxAddressRegionLegacyId	Int	False		The legacy ID for the item
TaxAddressRegionId	String	False		The unique identifier for the item
TaxCalculationMethod	String	False		The tax calculation method, if applicable, for this purchase invoice, returns invoice or cash.
PaymentsAllocationsAggregate	String	False		The associated payments and allocations
BaseCurrencyWithholdingTaxAmount	String	False		IRPF withheld Tax Amount (Spain only) in the base currency
WithholdingTaxAmount	String	False		IRPF withheld Tax Amount (Spain only)
UpdatedAt	Datetime	True		The datetime when the item was last updated
LegacyId	Int	False		The legacy ID for the item
TaxAnalysisAggregate	String	False		The invoice tax analysis (Optional for Spain, restricted for all other regions)
PurchaseCorrectiveInvoiceAggregate	String	False		The corrective entries associated with the invoice
InverseExchangeRate	String	False		The inverse exchange rate for the invoice
TotalQuantity	String	False		The total quantity of the invoice
TransactionId	String	False	Transactions.Id	The unique identifier for the item
StatusDisplayedAs	String	False		The name of the resource
StatusLegacyId	Int	False		The legacy ID for the item
StatusId	String	False		The unique identifier for the item
DetailedTaxAnalysisBaseCurrencyTotalGoodsAmount	String	False		The total base currency amount relating to goods for all tax rates
DetailedTaxAnalysisBaseCurrencyTotalTax	String	False		The base currency total tax amount for all tax rates
DetailedTaxAnalysisTotalRetailerTax	String	False		The total retailer tax amount for all tax rates
DetailedTaxAnalysisBaseCurrencyTotal	String	False		The total base currency amount for all tax rates
DetailedTaxAnalysisTotalGoodsAmount	String	False		The total amount relating to goods for all tax rates
DetailedTaxAnalysisTotalNet	String	False		The total net amount for all tax rates
DetailedTaxAnalysisBaseCurrencyTotalServicesAmount	String	False		The total base currency amount relating to services for all tax rates
DetailedTaxAnalysisBaseCurrencyTotalNet	String	False		The base currency total net amount for all tax rates
DetailedTaxAnalysisTotalServicesAmount	String	False		The total amount relating to services for all tax rates
DetailedTaxAnalysisTotal	String	False		The total amount for all tax rates
DetailedTaxAnalysisTotalTax	String	False		The total tax amount for all tax rates
TransactionTypeId	String	False	TransactionTypes.Id	The unique identifier for the item
CreatedAt	Datetime	True		The datetime when the item was created
PaymentsAllocationsTotalAmount	String	False		The total amount of all payments and allocations
VoidReason	String	False		The reason the invoice was voided
TaxReconciled	Bool	False		Indicates if the purchase invoice is tax reconciled or not.
Editable	Bool	False		Indicates whether artefact can be edited
VendorReference	String	False		The vendor reference for the invoice
CurrencyId	String	False	Currencies.Id	The unique identifier for the item
PurchaseInvoiceLineItemAggregate	String	False		The invoice lines of the invoice
Notes	String	False		Invoice notes
TotalPaid	String	False		The total paid amount of the invoice including any payments, allocations and discounts
DeletedAt	Datetime	True		The datetime when the item was deleted
BaseCurrencyTaxAmount	String	False		The tax amount of the invoice in base currency
Date	String	False		The date of the invoice
BaseCurrencyOutstandingAmount	String	False		The outstanding amount of the invoice in base currency
ShowPaymentsAllocations	String	False
ShowCorrections	String	False
ShowPaymentsAllocations	String	False
Search	String	False
FromDate	Datetime	True
ToDate	Datetime	True
DeletedSince	Datetime	True
HasAttachments	String	True
VatExemptConsignment	Bool	True		indicate when consignment values are under £135 and you have agreed with your supplier that you will account for VAT on your VAT return using Reverse Charge.
IsCIS	Bool	True		Determines if this is a CIS transaction.
CISApplicableAmount	String	True
BaseCurrencyCISApplicableAmount	String	True
TotalAfterCISDeduction	String	True
BaseCurrencyTotalAfterCISDeduction	String	True
HasCISLabour	Bool	True
HasCISMaterials	Bool	True

PurchaseQuickEntries

Query PurchaseQuickEntries in Sage Business Cloud Accounting.

Table Specific Information

Query the available PurchaseQuickEntries

Select

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

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

For example, the following queries are processed server side:

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

Insert

Create a simple purchase quick entry.

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

Update

Updates are performed based on Id.

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

Delete

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

DELETE FROM PurchaseQuickEntries WHERE id ='123'

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	String	False		The unique identifier for the item
DisplayedAs	String	False		The name of the resource
Reference	String	False		The reference for the quick entry
BaseCurrencyTotalAmount	String	False		The total amount of the quick entry in base currency
TradeOfAsset	Bool	False		Whether the quick entry is marked as trade of asset.
CaseCurrencyTaxBreakdownAggregate	String	False		The tax breakdown for the quick entry in base currency
BaseCurrencyNetAmount	String	False		The net amount of the quick entry in base currency
ContactName	String	False		The name of the contact when the quick entry was created
Migrated	Bool	False		Indicates if the quick entry was migrated from another system.
TaxAmount	String	False		The tax amount of the quick entry
TotalAmount	String	False		The total amount of the quick entry
ExchangeRate	String	False		The exchange rate for the quick entry
OutstandingAmount	String	False		The outstanding amount of the quick entry
ContactDisplayedAs	String	False		The name of the resource
ContactLegacyId	Int	False		The legacy ID for the item
ContactId	String	False		The unique identifier for the item
NetAmount	String	False		The net amount of the quick entry
ContactReference	String	False		The reference of the contact when the quick entry was created
TaxAddressRegionDisplayedAs	String	False		The name of the resource
TaxAddressRegionLegacyId	Int	False		The legacy ID for the item
TaxAddressRegionId	String	False		The unique identifier for the item
PaymentsAllocationsAggregate	String	False		The associated payments and allocations
UpdatedAt	Datetime	True		The datetime when the item was last updated
LegacyId	Int	False		The legacy ID for the item
LedgerAccountId	String	False	LedgerAccounts.Id	The unique identifier for the item
InverseExchangeRate	String	False		The inverse exchange rate for the quick entry
TransactionId	String	False	Transactions.Id	The unique identifier for the item
StatusDisplayedAs	String	False		The name of the resource
StatusLegacyId	Int	False		The legacy ID for the item
StatusId	String	False		The unique identifier for the item
TaxBreakdownAggregate	String	False		The tax breakdown for the quick entry
CreatedAt	Datetime	True		The datetime when the item was created
PstAmount	String	False		The pst or qst tax amount for the purchase quick entry
Details	String	False		A description of the quick entry
GstAmount	String	False		The gst or hst tax amount for the purchase quick entry
CurrencyId	String	False	Currencies.Id	The unique identifier for the item
DeletedAt	Datetime	True		The datetime when the item was deleted
TaxRateId	String	False	TaxRates.Id	The unique identifier for the item
Date	String	False		The date of the quick entry
BaseCurrencyTaxAmount	String	False		The tax amount of the quick entry in base currency
QuickEntryTypeDisplayedAs	String	False		The name of the resource
QuickEntryTypeLegacyId	Int	False		The legacy ID for the item
QuickEntryTypeId	String	False		The unique identifier for the item
BaseCurrencyOutstandingAmount	String	False		The outstanding amount of the quick entry in base currency
ShowPaymentsAllocations	String	False
Search	String	False
FromDate	String	True
ToDate	String	True
DeletedSince	Datetime	True
ShowPaymentsAllocations	String	True
HasAttachments	String	True

SalesCreditNotes

Query SalesCreditNotes in Sage Business Cloud Accounting.

Table Specific Information

Query the available SalesCreditNotes

Select

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

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

For example, the following queries are processed server side:

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

Insert

Create a simple sales credit note.

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

Update

Updates are performed based on Id.

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

Delete

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

DELETE FROM SalesCreditNotes WHERE id ='123'

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	String	False		The unique identifier for the item
DisplayedAs	String	False		The name of the resource
Reference	String	False		The reference for the credit note
SalesCreditNoteLineItemAggregate	String	False		The credit note lines of the credit note
BaseCurrencyTotalAmount	String	False		The total amount of the credit note in base currency
MainAddressId	String	False	Addresses.Id	The unique identifier for the item
BaseCurrencyNetAmount	String	False		The net amount of the credit note in base currency
BaseCurrencyShippingTaxBreakdownAggregate	String	False		The shipping tax breakdown for the credit note in base currency
ContactName	String	False		The name of the contact when the credit note was created
SentByEmail	Bool	False		Indicates whether the credit note has been emailed
ShippingTaxBreakdownAggregate	String	False		The shipping tax breakdown for the credit note
Migrated	Bool	False		Indicates if the sales credit note was migrated from another system.
TotalDiscountAmount	String	False		The discount amount on the credit note
TaxAmount	String	False		The tax amount of the credit note
LastPaid	String	False		The date of the last payment
CreditNoteNumber	String	False		The generated credit note number
PaymentsAllocationsTotalDiscount	String	False		The total discount of all payments and allocations
TotalAmount	String	False		The total amount of the credit note
ExchangeRate	String	False		The exchange rate for the credit note
OutstandingAmount	String	False		The outstanding amount of the credit note
ContactId	String	False	Contacts.Id	The unique identifier for the item
NetAmount	String	False		The net amount of the credit note
ContactReference	String	False		The reference of the contact when the credit note was created
TaxAddressRegionDisplayedAs	String	False		The name of the resource
TaxAddressRegionLegacyId	Int	False		The legacy ID for the item
TaxAddressRegionId	String	False		The unique identifier for the item
TaxCalculationMethod	String	False		The tax calculation method, if applicable, for this sales credit note, returns invoice or cash.
TermsAndConditions	String	False		Credit note terms and conditions
PaymentsAllocationsAggregate	String	False		The associated payments and allocations
ShippingTaxRateId	String	False	TaxRates.Id	The unique identifier for the item
Sent	Bool	False		Indicates whether the credit note has been sent
DeliveryAddressId	String	False	Addresses.Id	The unique identifier for the item
UpdatedAt	Datetime	True		The datetime when the item was last updated
LegacyId	Int	False		The legacy ID for the item
TaxAnalysisAggregate	String	False		The credit note tax analysis
BaseCurrencyShippingTotalAmount	String	False		The total shipping amount in base currency
InverseExchangeRate	String	False		The inverse exchange rate for the credit note
TransactionId	String	False	Transactions.Id	The unique identifier for the item
TotalQuantity	String	False		The total quantity of the credit note
StatusDisplayedAs	String	False		The name of the resource
StatusLegacyId	Int	False		The legacy ID for the item
StatusId	String	False		The unique identifier for the item
DetailedTaxAnalysisBaseCurrencyTotalGoodsAmount	String	False		The total base currency amount relating to goods for all tax rates
DetailedTaxAnalysisBaseCurrencyTotalTax	String	False		The base currency total tax amount for all tax rates
DetailedTaxAnalysisTotalRetailerTax	String	False		The total retailer tax amount for all tax rates
DetailedTaxAnalysisBaseCurrencyTotal	String	False		The total base currency amount for all tax rates
DetailedTaxAnalysisTotalGoodsAmount	String	False		The total amount relating to goods for all tax rates
DetailedTaxAnalysisTotalNet	String	False		The total net amount for all tax rates
DetailedTaxAnalysisBaseCurrencyTotalServicesAmount	String	False		The total base currency amount relating to services for all tax rates
DetailedTaxAnalysisBaseCurrencyTotalNet	String	False		The base currency total net amount for all tax rates
DetailedTaxAnalysisTotalServicesAmount	String	False		The total amount relating to services for all tax rates
DetailedTaxAnalysisTotal	String	False		The total amount for all tax rates
DetailedTaxAnalysisTotalTax	String	False		The total tax amount for all tax rates
TransactionTypeId	String	False	TransactionTypes.Id	The unique identifier for the item
BaseCurrencyShippingTaxAmount	String	False		The tax shipping amount in base currency
ShippingTaxAmount	String	False		The tax shipping amount. NOTE: This is not required for POST/PUT requests as the shipping tax is calculated based on the shipping_net_amount and the shipping_tax_rate.
CreatedAt	Datetime	True		The datetime when the item was created
PaymentsAllocationsTotalAmount	String	False		The total amount of all payments and allocations
CreditNoteNumberPrefix	String	False		The credit note number prefix
VoidReason	String	False		The reason the credit note was voided
TaxReconciled	Bool	False		Indicates if the sales credit note is tax reconciled or not.
Editable	Bool	False		Indicates whether artefact can be edited
ShippingTotalAmount	String	False		The total shipping amount
BaseCurrencyShippingNetAmount	String	False		The net shipping amount in base currency
ShippingNetAmount	String	False		The net shipping amount
CurrencyDisplayedAs	String	False		The name of the resource
CurrencyLegacyId	Int	False		The legacy ID for the item
CurrencyId	String	False		The unique identifier for the item
Notes	String	False		credit note notes
MainAddressFreeForm	String	False		The free-form main address of the credit note
TotalPaid	String	False		The total paid amount of the credit note including any payments, allocations and discounts
DeletedAt	Datetime	True		The datetime when the item was deleted
Date	String	False		The date of the credit note
BaseCurrencyTaxAmount	String	False		The tax amount of the credit note in base currency
BaseCurrencyOutstandingAmount	String	False		The outstanding amount of the credit note in base currency
BaseCurrencyTotalDiscountAmount	String	False		The discount amount on the credit note in base currency
ShowPaymentsAllocations	String	False
MarkAsSent	String	False
ShowPaymentsAllocations	String	False
Search	String	False
FromDate	Datetime	True
ToDate	Datetime	True
DeletedSince	Datetime	True
HasAttachments	String	True

SalesEstimates

Query SalesEstimates in Sage Business Cloud Accounting.

Table Specific Information

Query the available SalesEstimates

Select

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

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

For example, the following queries are processed server side:

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

Insert

Create a simple sales estimate.

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

Update

Updates are performed based on Id.

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

Delete

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

DELETE FROM SalesEstimates WHERE id ='123'

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	String	False		The unique identifier for the item
DisplayedAs	String	False		The name of the resource
Reference	String	False		The reference for the estimate
BaseCurrencyTotalAmount	String	False		The total amount of the estimate in base currency
MainAddressId	String	False	Addresses.Id	The unique identifier for the item
BaseCurrencyNetAmount	String	False		The net amount of the estimate in base currency
BaseCurrencyShippingTaxBreakdownAggregate	String	False		The shipping tax breakdown for the estimate in base currency
ProfitAnalysisTotalProfit	String	False		The profit amount
ProfitAnalysisTotalDescription	String	False		The description
ProfitAnalysisTotalTotalSale	String	False		The total sale
ProfitAnalysisTotalTotalCost	String	False		The total cost
ProfitAnalysisTotalProfitPercentage	String	False		The profit percentage
LineBreakdownAggregate	String	False		The breakdown of profit per line
ContactName	String	False		The name of the contact when the estimate was created
SentByEmail	Bool	False		Indicates whether the estimate has been emailed
ShippingTaxBreakdownAggregate	String	False		The shipping tax breakdown for the estimate
TotalDiscountAmount	String	False		The discount amount on the estimate
TaxAmount	String	False		The tax amount of the estimate
PaymentsAllocationsTotalDiscount	String	False		The total discount of all payments and allocations
WithholdingTaxRate	String	False		IRPF withheld Tax Rate (Spain only)
TotalAmount	String	False		The total amount of the estimate
ExchangeRate	String	False		The exchange rate for the estimate
EstimateNumberPrefix	String	False		The estimate number prefix
ContactId	String	False	Contacts.Id	The unique identifier for the item
TaxAddressRegionDisplayedAs	String	False		The name of the resource
TaxAddressRegionLegacyId	Int	False		The legacy ID for the item
TaxAddressRegionId	String	False		The unique identifier for the item
TaxCalculationMethod	String	False		The tax calculation method, if applicable, for this sales invoice, returns invoice, cash or retailer.
TermsAndConditions	String	False		Estimate terms and conditions
ShippingTaxRateDisplayedAs	String	False		The name of the resource
ShippingTaxRateLegacyId	Int	False		The legacy ID for the item
ShippingTaxRateId	String	False		The unique identifier for the item
Sent	Bool	False		Indicates whether the estimate has been sent
DeliveryAddressFreeForm	String	False		The free-form delivery address of the estimate
BaseCurrencyWithholdingTaxAmount	String	False		IRPF withheld Tax Amount (Spain only) in the base currency
DeliveryAddressId	String	False	Addresses.Id	The unique identifier for the item
WithholdingTaxAmount	String	False		IRPF withheld Tax Amount (Spain only)
UpdatedAt	Datetime	True		The datetime when the item was last updated
LegacyId	Int	False		The legacy ID for the item
TaxAnalysisAggregate	String	False		The estimate tax analysis
BaseCurrencyShippingTotalAmount	String	False		The total shipping amount in base currency
InverseExchangeRate	String	False		The inverse exchange rate for the estimate
TotalQuantity	String	False		The total quantity of the estimate
StatusDisplayedAs	String	False		The quote status name
StatusId	String	False		The quote status id
SalesQuoteLineItemAggregate	String	False		The estimate lines of the estimate
DetailedTaxAnalysisBaseCurrencyTotalGoodsAmount	String	False		The total base currency amount relating to goods for all tax rates
DetailedTaxAnalysisBaseCurrencyTotalTax	String	False		The base currency total tax amount for all tax rates
DetailedTaxAnalysisTotalRetailerTax	String	False		The total retailer tax amount for all tax rates
DetailedTaxAnalysisBaseCurrencyTotal	String	False		The total base currency amount for all tax rates
DetailedTaxAnalysisTotalGoodsAmount	String	False		The total amount relating to goods for all tax rates
DetailedTaxAnalysisTotalNet	String	False		The total net amount for all tax rates
DetailedTaxAnalysisBaseCurrencyTotalServicesAmount	String	False		The total base currency amount relating to services for all tax rates
DetailedTaxAnalysisBaseCurrencyTotalNet	String	False		The base currency total net amount for all tax rates
DetailedTaxAnalysisTotalServicesAmount	String	False		The total amount relating to services for all tax rates
DetailedTaxAnalysisTotal	String	False		The total amount for all tax rates
DetailedTaxAnalysisTotalTax	String	False		The total tax amount for all tax rates
ExpiryDate	String	False		The expiry date of the estimate
BaseCurrencyShippingTaxAmount	String	False		The tax shipping amount in base currency
ShippingTaxAmount	String	False		The tax shipping amount. NOTE: This is not required for POST/PUT requests as the shipping tax is calculated based on the shipping_net_amount and the shipping_tax_rate.
CreatedAt	Datetime	True		The datetime when the item was created
PaymentsAllocationsTotalAmount	String	False		The total amount of all payments and allocations
TaxReconciled	Bool	False		Indicates if the quote/estimate is tax reconciled or not.
Editable	Bool	False		Indicates whether artefact can be edited
ShippingTotalAmount	String	False		The total shipping amount
InvoiceDisplayedAs	String	False		The name of the resource
InvoiceLegacyId	Int	False		The legacy ID for the item
InvoiceId	String	False		The unique identifier for the item
BaseCurrencyShippingNetAmount	String	False		The net shipping amount in base currency
ShippingNetAmount	String	False		The net shipping amount
CurrencyDisplayedAs	String	False		The name of the resource
CurrencyLegacyId	Int	False		The legacy ID for the item
CurrencyId	String	False		The unique identifier for the item
EstimateNumber	String	False		The generated estimate number
Notes	String	False		Estimate notes
MainAddressFreeForm	String	False		The free-form main address of the estimate
TotalPaid	String	False		The total paid amount of the estimate including any payments, allocations and discounts
Date	Datetime	True		The date of the estimate
BaseCurrencyTaxAmount	String	False		The tax amount of the estimate in base currency
BaseCurrencyTotalDiscountAmount	String	False		The discount amount on the estimate in base currency
Search	String	False
FromDate	Datetime	True
ToDate	Datetime	True
HasAttachments	String	True

SalesInvoices

Query SalesInvoices in Sage Business Cloud Accounting.

Table Specific Information

Query the available SalesInvoices

Select

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

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

For example, the following queries are processed server side:

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

Insert

Create a simple sales invoice with a JSON aggregate value for SalesInvoiceLineItemAggregate.

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

Create a simple sales invoice by using temp tables.

INSERT INTO SalesInvoiceLineItemAggregate#TEMP (Description, LedgerAccountId, UnitPrice, Quantity, TaxRateId) VALUES ('In Rainbows LP', '35efa3bc698311eda8c40ef4cf562701', '35.0', '2.0', '24d66d90e1d64620a5d8b13cfca85b81')
INSERT INTO SalesInvoiceLineItemAggregate#TEMP (Description, LedgerAccountId, UnitPrice, Quantity, TaxRateId) VALUES ('Kid A LP', '35efa3bc698311eda8c40ef4cf562701', '45.0', '4.0', '24d66d90e1d64620a5d8b13cfca85b81')
INSERT INTO SalesInvoices (ContactId, Date, SalesInvoiceLineItemAggregate) VALUES ('0603375a7e29443b8d248239b007e72c','2022-11-28', 'SalesInvoiceLineItemAggregate#TEMP')

Update

Updates are performed based on Id.

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

Delete

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

DELETE FROM SalesInvoices WHERE id ='123'

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	String	False		The unique identifier for the item
DisplayedAs	String	False		The name of the resource
Reference	String	False		The reference for the invoice
BaseCurrencyTotalAmount	String	False		The total amount of the invoice in base currency
MainAddressId	String	False	Addresses.Id	The unique identifier for the item
BaseCurrencyNetAmount	String	False		The net amount of the invoice in base currency
BaseCurrencyShippingTaxBreakdownAggregate	String	False		The shipping tax breakdown for the invoice in base currency
ContactName	String	False		The name of the contact when the invoice was created
SentByEmail	Bool	False		Indicates whether the invoice has been emailed
ShippingTaxBreakdownAggregate	String	False		The shipping tax breakdown for the invoice
Migrated	Bool	False		Indicates if the sales invoice was migrated from another system.
DueDate	Datetime	True		The due date of the invoice
TotalDiscountAmount	String	False		The discount amount on the invoice
TaxAmount	String	False		The tax amount of the invoice
LastPaid	String	False		The date of the last payment
PaymentsAllocationsTotalDiscount	String	False		The total discount of all payments and allocations
WithholdingTaxRate	String	False		IRPF withheld Tax Rate (Spain only)
TotalAmount	String	False		The total amount of the invoice
ExchangeRate	String	False		The exchange rate for the invoice
InvoiceNumberPrefix	String	False		The invoice number prefix
OutstandingAmount	String	False		The outstanding amount of the invoice
ContactId	String	False	Contacts.Id	The unique identifier for the item
TaxAddressRegionDisplayedAs	String	False		The name of the resource
TaxAddressRegionLegacyId	Int	False		The legacy ID for the item
TaxAddressRegionId	String	False		The unique identifier for the item
DeliveryPerformanceDate	String	False		Delivery/Performance Date (Germany only)
TaxCalculationMethod	String	False		The tax calculation method, if applicable, for this sales invoice, returns invoice, cash or retailer.
TermsAndConditions	String	False		Invoice terms and conditions
PaymentsAllocationsAggregate	String	False		The associated payments and allocations
ShippingTaxRateId	String	False	TaxRates.Id	The unique identifier for the item
Sent	Bool	False		Indicates whether the invoice has been sent
DeliveryAddressFreeForm	String	False		The free-form delivery address of the invoice
BaseCurrencyWithholdingTaxAmount	String	False		IRPF withheld Tax Amount (Spain only) in the base currency
DeliveryAddressId	String	False	Addresses.Id	The unique identifier for the item
WithholdingTaxAmount	String	False		IRPF withheld Tax Amount (Spain only)
UpdatedAt	Datetime	True		The datetime when the item was last updated
LegacyId	Int	False		The legacy ID for the item
TaxAnalysisAggregate	String	False		The invoice tax analysis
SalesCorrectiveInvoiceAggregate	String	False		The corrective entries associated with the invoice
InverseExchangeRate	String	False		The inverse exchange rate for the invoice
BaseCurrencyShippingTotalAmount	String	False		The total shipping amount in base currency
TransactionId	String	False	Transactions.Id	The unique identifier for the item
TotalQuantity	String	False		The total quantity of the invoice
StatusDisplayedAs	String	False		The name of the resource
StatusLegacyId	Int	False		The legacy ID for the item
StatusId	String	False		The unique identifier for the item
DetailedTaxAnalysisBaseCurrencyTotalGoodsAmount	String	False		The total base currency amount relating to goods for all tax rates
DetailedTaxAnalysisBaseCurrencyTotalTax	String	False		The base currency total tax amount for all tax rates
DetailedTaxAnalysisTotalRetailerTax	String	False		The total retailer tax amount for all tax rates
DetailedTaxAnalysisBaseCurrencyTotal	String	False		The total base currency amount for all tax rates
DetailedTaxAnalysisTotalGoodsAmount	String	False		The total amount relating to goods for all tax rates
DetailedTaxAnalysisTotalNet	String	False		The total net amount for all tax rates
DetailedTaxAnalysisBaseCurrencyTotalServicesAmount	String	False		The total base currency amount relating to services for all tax rates
DetailedTaxAnalysisBaseCurrencyTotalNet	String	False		The base currency total net amount for all tax rates
DetailedTaxAnalysisTotalServicesAmount	String	False		The total amount relating to services for all tax rates
DetailedTaxAnalysisTotal	String	False		The total amount for all tax rates
DetailedTaxAnalysisTotalTax	String	False		The total tax amount for all tax rates
TransactionTypeId	String	False	TransactionTypes.Id	The unique identifier for the item
BaseCurrencyShippingTaxAmount	String	False		The tax shipping amount in base currency
ShippingTaxAmount	String	False		The tax shipping amount. NOTE: This is not required for POST/PUT requests as the shipping tax is calculated based on the shipping_net_amount and the shipping_tax_rate.
CreatedAt	Datetime	True		The datetime when the item was created
PaymentsAllocationsTotalAmount	String	False		The total amount of all payments and allocations
VoidReason	String	False		The reason the invoice was voided
TaxReconciled	Bool	False		Indicates if the sales invoice is tax reconciled or not.
Editable	Bool	False		Indicates whether artefact can be edited
ShippingTotalAmount	String	False		The total shipping amount
BaseCurrencyShippingNetAmount	String	False		The net shipping amount in base currency
ShippingNetAmount	String	False		The net shipping amount
CurrencyId	String	False	Currencies.Id	The unique identifier for the item
SalesInvoiceLineItemAggregate	String	False		The invoice lines of the invoice
InvoiceNumber	String	False		The generated invoice number
Notes	String	False		Invoice notes
MainAddressFreeForm	String	False		The free-form main address of the invoice
TotalPaid	String	False		The total paid amount of the invoice including any payments, allocations and discounts
DeletedAt	Datetime	True		The datetime when the item was deleted
Date	String	False		The date of the invoice
BaseCurrencyTaxAmount	String	False		The tax amount of the invoice in base currency
BaseCurrencyOutstandingAmount	String	False		The outstanding amount of the invoice in base currency
BaseCurrencyTotalDiscountAmount	String	False		The discount amount on the invoice in base currency
LinksAggregate	String	False		Links for the resource
OriginalQuoteEstimateDisplayedAs	String	False		The name of the resource
OriginalQuoteEstimateLegacyId	Int	False		The legacy ID for the item
OriginalQuoteEstimateId	String	False		The unique identifier for the item
ShowPaymentsAllocations	String	False
ShowCorrections	String	False
MarkAsSent	String	False
ShowPaymentsAllocations	String	False
Search	String	False
FromDate	Datetime	True
ToDate	Datetime	True
DeletedSince	Datetime	True
HasAttachments	String	True
IsCIS	Bool	True		Determines if this is a CIS transaction.
CISApplicableAmount	String	True
BaseCurrencyCISApplicableAmount	String	True
TotalAfterCISDeduction	String	True
BaseCurrencyTotalAfterCISDeduction	String	True
HasCISLabour	Bool	True
HasCISMaterials	Bool	True

SalesQuickEntries

Query SalesQuickEntries in Sage Business Cloud Accounting.

Table Specific Information

Query the available SalesQuickEntries

Select

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

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

For example, the following queries are processed server side:

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

Update

Updates are performed based on Id.

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

Delete

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

DELETE FROM SalesQuickEntries WHERE id ='123'

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	String	False		The unique identifier for the item
DisplayedAs	String	False		The name of the resource
Reference	String	False		The reference for the quick entry
BaseCurrencyTotalAmount	String	False		The total amount of the quick entry in base currency
TradeOfAsset	Bool	False		Whether the quick entry is marked as trade of asset.
BaseCurrencyTaxBreakdownAggregate	String	False		The tax breakdown for the quick entry in base currency
BaseCurrencyNetAmount	String	False		The net amount of the quick entry in base currency
ContactName	String	False		The name of the contact when the quick entry was created
Migrated	Bool	False		Indicates if the quick entry was migrated from another system.
TaxAmount	String	False		The tax amount of the quick entry
TotalAmount	String	False		The total amount of the quick entry
ExchangeRate	String	False		The exchange rate for the quick entry
OutstandingAmount	String	False		The outstanding amount of the quick entry
ContactId	String	False	Contacts.Id	The unique identifier for the item
NetAmount	String	False		The net amount of the quick entry
ContactReference	String	False		The reference of the contact when the quick entry was created
TaxAddressRegionDisplayedAs	String	False		The name of the resource
TaxAddressRegionLegacyId	Int	False		The legacy ID for the item
TaxAddressRegionId	String	False		The unique identifier for the item
PaymentsAllocationsAggregate	String	False		The associated payments and allocations
UpdatedAt	Datetime	True		The datetime when the item was last updated
LegacyId	Int	False		The legacy ID for the item
LedgerAccountId	String	False	LedgerAccounts.Id	The unique identifier for the item
InverseExchangeRate	String	False		The inverse exchange rate for the quick entry
TransactionId	String	False	Transactions.Id	The unique identifier for the item
StatusDisplayedAs	String	False		The name of the resource
StatusLegacyId	Int	False		The legacy ID for the item
StatusId	String	False		The unique identifier for the item
TaxBreakdownAggregate	String	False		The tax breakdown for the quick entry
TransactionTypeId	String	False	TransactionTypes.Id	The unique identifier for the item
CreatedAt	Datetime	True		The datetime when the item was created
Details	String	False		A description of the quick entry
CurrencyId	String	False	Currencies.Id	The unique identifier for the item
DeletedAt	Datetime	True		The datetime when the item was deleted
TaxRateId	String	False	TaxRates.Id	The unique identifier for the item
Date	String	False		The date of the quick entry
BaseCurrencyTaxAmount	String	False		The tax amount of the quick entry in base currency
QuickEntryTypeDisplayedAs	String	False		The name of the resource
QuickEntryTypeLegacyId	Int	False		The legacy ID for the item
QuickEntryTypeId	String	False		The unique identifier for the item
BaseCurrencyOutstandingAmount	String	False		The outstanding amount of the quick entry in base currency
ShowPaymentsAllocations	String	False
Search	String	False
FromDate	Datetime	True
ToDate	Datetime	True
DeletedSince	Datetime	False
ShowPaymentsAllocations	String	True
HasAttachments	String	True

SalesQuotes

Query SalesQuotes in Sage Business Cloud Accounting.

Table Specific Information

Query the available SalesQuotes

Select

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

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

For example, the following queries are processed server side:

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

Update

Updates are performed based on Id.

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

Delete

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

DELETE FROM SalesQuotes WHERE id ='123'

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	String	False		The unique identifier for the item
DisplayedAs	String	False		The name of the resource
Reference	String	False		The reference for the quote
BaseCurrencyTotalAmount	String	False		The total amount of the quote in base currency
MainAddressId	String	False	Addresses.Id	The unique identifier for the item
BaseCurrencyNetAmount	String	False		The net amount of the quote in base currency
BaseCurrencyShippingTaxBreakdownAggregate	String	False		The shipping tax breakdown for the quote in base currency
ProfitAnalysisTotalProfit	String	False		The profit amount
ProfitAnalysisTotalDescription	String	False		The description
ProfitAnalysisTotalTotalSale	String	False		The total sale
ProfitAnalysisTotalTotalCost	String	False		The total cost
ProfitAnalysisTotalProfitPercentage	String	False		The profit percentage
LineBreakdownAggregate	String	False		The breakdown of profit per line
ContactName	String	False		The name of the contact when the quote was created
SentByEmail	Bool	False		Indicates whether the quote has been emailed
ShippingTaxBreakdownAggregate	String	False		The shipping tax breakdown for the quote
TotalDiscountAmount	String	False		The discount amount on the quote
TaxAmount	String	False		The tax amount of the quote
PaymentsAllocationsTotalDiscount	String	False		The total discount of all payments and allocations
WithholdingTaxRate	String	False		IRPF withheld Tax Rate (Spain only)
TotalAmount	String	False		The total amount of the quote
ExchangeRate	String	False		The exchange rate for the quote
ContactId	String	False	Contacts.Id	The unique identifier for the item
NetAmount	String	False		The net amount of the quote
ContactReference	String	False		The reference of the contact when the quote was created
TaxCalculationMethod	String	False		The tax calculation method, if applicable, for this sales invoice, returns invoice, cash or retailer.
TermsAndConditions	String	False		Quote terms and conditions
ShippingTaxRateId	String	False	TaxRates.Id	The unique identifier for the item
Sent	Bool	False		Indicates whether the quote has been sent
DeliveryAddressFreeForm	String	False		The free-form delivery address of the quote
BaseCurrencyWithholdingTaxAmount	String	False		IRPF withheld Tax Amount (Spain only) in the base currency
DeliveryAddressId	String	False	Addresses.Id	The unique identifier for the item
WithholdingTaxAmount	String	False		IRPF withheld Tax Amount (Spain only)
UpdatedAt	Datetime	True		The datetime when the item was last updated
LegacyId	Int	False		The legacy ID for the item
TaxAnalysisAggregate	String	False		The quote tax analysis
QuoteNumberPrefix	String	False		The quote number prefix
BaseCurrencyShippingTotalAmount	String	False		The total shipping amount in base currency
QuoteNumber	String	False		The generated quote number
InverseExchangeRate	String	False		The inverse exchange rate for the quote
TotalQuantity	String	False		The total quantity of the quote
StatusDisplayedAs	String	False		The quote status name
StatusId	String	False		The quote status id
DetailedTaxAnalysisBaseCurrencyTotalGoodsAmount	String	False		The total base currency amount relating to goods for all tax rates
DetailedTaxAnalysisBaseCurrencyTotalTax	String	False		The base currency total tax amount for all tax rates
DetailedTaxAnalysisTotalRetailerTax	String	False		The total retailer tax amount for all tax rates
DetailedTaxAnalysisBaseCurrencyTotal	String	False		The total base currency amount for all tax rates
DetailedTaxAnalysisTotalGoodsAmount	String	False		The total amount relating to goods for all tax rates
DetailedTaxAnalysisTotalNet	String	False		The total net amount for all tax rates
DetailedTaxAnalysisBaseCurrencyTotalServicesAmount	String	False		The total base currency amount relating to services for all tax rates
DetailedTaxAnalysisBaseCurrencyTotalNet	String	False		The base currency total net amount for all tax rates
DetailedTaxAnalysisTotalServicesAmount	String	False		The total amount relating to services for all tax rates
DetailedTaxAnalysisTotal	String	False		The total amount for all tax rates
DetailedTaxAnalysisTotalTax	String	False		The total tax amount for all tax rates
ExpiryDate	String	False		The expiry date of the quote
BaseCurrencyShippingTaxAmount	String	False		The tax shipping amount in base currency
ShippingTaxAmount	String	False		The tax shipping amount. NOTE: This is not required for POST/PUT requests as the shipping tax is calculated based on the shipping_net_amount and the shipping_tax_rate.
CreatedAt	Datetime	True		The datetime when the item was created
PaymentsAllocationsTotalAmount	String	False		The total amount of all payments and allocations
TaxReconciled	Bool	False		Indicates if the quote/estimate is tax reconciled or not.
Editable	Bool	False		Indicates whether artefact can be edited
ShippingTotalAmount	String	False		The total shipping amount
InvoiceDisplayedAs	String	False		The name of the resource
InvoiceLegacyId	Int	False		The legacy ID for the item
InvoiceId	String	False		The unique identifier for the item
BaseCurrencyShippingNetAmount	String	False		The net shipping amount in base currency
ShippingNetAmount	String	False		The net shipping amount
CurrencyId	String	False	Currencies.Id	The unique identifier for the item
Notes	String	False		Quote notes
MainAddressFreeForm	String	False		The free-form main address of the quote
TotalPaid	String	False		The total paid amount of the quote including any payments, allocations and discounts
Date	Datetime	False		The date of the quote
BaseCurrencyTaxAmount	String	False		The tax amount of the quote in base currency
SalesQuoteLineItemAggregate	String	False		The quote lines of the quote
BaseCurrencyTotalDiscountAmount	String	False		The discount amount on the quote in base currency
FromDate	Datetime	False
ToDate	Datetime	False
HasAttachments	String	False

Services

Query Services in Sage Business Cloud Accounting.

Table Specific Information

Query the available Services

Select

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

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

For example, the following queries are processed server side:

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

Insert

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

Update

Updates are performed based on Id.

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

Delete

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

DELETE FROM Services WHERE id ='123'

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	String	False		The unique identifier for the item
SalesLedgerAccountId	String	False	LedgerAccounts.Id	The unique identifier for the item
CreatedAt	Datetime	True		The datetime when the item was created
SalesTaxRateDisplayedAs	String	False		The name of the resource
SalesTaxRateLegacyId	Int	False		The legacy ID for the item
SalesTaxRateId	String	False		The unique identifier for the item
Active	Bool	False		Indicates whether the service is active
ItemCode	String	False		The item code for the service
PurchaseLedgerAccountId	String	False	LedgerAccounts.Id	The unique identifier for the item
DeletedAt	Datetime	True		The datetime when the item was deleted
PurchaseTaxRateId	String	False	TaxRates.Id	The unique identifier for the item
Notes	String	False		The notes for the service
UsualSupplierId	String	False	Contacts.Id	The unique identifier for the item
SalesRatesAggregate	String	False		The sales rates for the service
DisplayedAs	String	False		The name of the resource
Description	String	False		The service description
PurchaseDescription	String	False		The service purchase description
LegacyId	Int	False		The legacy ID for the item
SourceGuid	String	False		Used when importing services from external sources
UpdatedAt	Datetime	True		The datetime when the item was last updated
Search	String	True
DeletedSince	Datetime	True

StockItems

Query StockItems in Sage Business Cloud Accounting.

Table Specific Information

Query the available StockItems

Select

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

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

For example, the following queries are processed server side:

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

Insert

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

Update

Updates are performed based on Id.

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

Delete

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

DELETE FROM StockItems WHERE id ='123'

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	String	False		The unique identifier for the item
Location	String	False		The location for the stock item
SalesLedgerAccountId	String	False	LedgerAccounts.Id	The unique identifier for the item
CreatedAt	Datetime	True		The datetime when the item was created
ReorderQuantity	String	False		The reorder quantity for the stock item
SalesTaxRateId	String	False	TaxRates.Id	The unique identifier for the item
AverageCostPrice	String	False		The average price across all purchases of this stock item
LastCostPrice	String	False		The most recent 'purchase invoice' or 'adjustment in' price
Active	Bool	False		Indicates whether the stock item is active
ItemCode	String	False		The item code for the stock item
PurchaseLedgerAccountId	String	False	LedgerAccounts.Id	The unique identifier for the item
WeightConverted	String	False		The weight of stock item converted to the lowest unit of measurement
SalesPricesAggregate	String	False		The sales prices for the stock item
DeletedAt	Datetime	True		The datetime when the item was deleted
SupplierPartNumber	String	False		The supplier part number for stock item
PurchaseTaxRateId	String	False	TaxRates.Id	The unique identifier for the item
Notes	String	False		The notes for the stock item
Weight	String	False		The weight of stock item
AverageCostPriceStockValue	String	False		The value of the current stock in terms of the average cost price
CostPrice	String	False		The cost price of the stock item
UsualSupplierId	String	False	Contacts.Id	The unique identifier for the item
MeasurementUnit	String	False		The unit of measure of weight for stock item
DisplayedAs	String	False		The name of the resource
Description	String	False		The stock item description
PurchaseDescription	String	False		The stock item purchase description
ReorderLevel	String	False		The reorder level for the stock item
LegacyId	Int	False		The legacy ID for the item
SourceGuid	String	False		Used when importing stock items from external sources
Barcode	String	False		The barcode for the stock item
LastCostPriceStockValue	String	False		The value of the current stock in terms of the last cost price
CostPriceLastUpdated	String	False		The date on which the last cost price was last updated
QuantityInStock	String	False		The current quantity of the stock item held by the business
UpdatedAt	Datetime	True		The datetime when the item was last updated
Search	String	False
DeletedSince	Datetime	True
OutOfStock	String	True
BelowReorderLevel	String	True

StockMovements

Query StockMovements in Sage Business Cloud Accounting.

Table Specific Information

Query the available StockMovements

Select

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

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

For example, the following queries are processed server side:

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

Insert

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

Update

Updates are performed based on Id.

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

Delete

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

DELETE FROM StockMovements WHERE id ='123'

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	String	False		The unique identifier for the item
CreatedAt	Datetime	True		The datetime when the item was created
Details	String	False		The transaction details of the stock movement
DisplayedAs	String	False		Display text for the stock movement
Date	Datetime	False		The date the stock movement occurred
MovementNumber	String	False		The movement number of the stock movement
Reference	String	False		The reference of the stock movement
Quantity	String	False		The quantity of the goods adjusted
UpdatedAt	Datetime	True		The datetime when the item was last updated
LegacyId	Int	False		The legacy ID for the item
CostPrice	String	False		The cost per unit of stock purchased
StockItemId	String	False	StockItems.Id	The unique identifier for the item
Search	String	False
FromDate	Datetime	True
ToDate	Datetime	True

TaxProfiles

Query TaxProfiles in Sage Business Cloud Accounting.

Table Specific Information

Query the available TaxProfiles

Select

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

UpdatedOrCreatedSince supports the '=' operator.

For example, the following queries are processed server side:

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

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	String	False		The unique identifier for the item
CreatedAt	Datetime	True		The datetime when the item was created
DisplayedAs	String	False		The name of the resource
CollectTax	Bool	False		Indicates whether tax is collected for this tax type
LegacyId	Int	False		The legacy ID for the item
TaxReturnFrequencyId	String	False	TaxReturnFrequencies.Id	The unique identifier for the item
UpdatedAt	Datetime	True		The datetime when the item was last updated
TaxNumberSuffix	String	False		The tax number suffix
TaxNumber	String	False		The tax number
AddressRegionId	String	False	AddressRegions.Id	The unique identifier for the item

TaxRates

Query TaxRates in Sage Business Cloud Accounting.

Table Specific Information

Query the available TaxRates

Select

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

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

For example, the following queries are processed server side:

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

Insert

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

Update

Updates are performed based on Id.

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

Delete

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

DELETE FROM TaxRates WHERE id ='1a756edcf15743ba8cc53804df6049da'

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	String	False		The unique identifier for the item
CreatedAt	Datetime	True		The datetime when the item was created
IsCombinedRate	Bool	False		Indicates whether the tax rate is made up of component tax rates
DisplayedAs	String	False		The name of the resource
Name	String	False		The name of the tax rate
TaxRatePercentageAggregate	String	False		The tax rate percentage and date ranges they apply to
Editable	Bool	False		Indicates whether a tax rate can be edited
Retailer	Bool	False		Indicates if tax rate is a retailer rate or not
ComponentTaxRatesAggregate	String	False		The component tax rates which make up a combined rate
Deletable	Bool	False		Indicates whether a tax rate can be deleted
UpdatedAt	Datetime	True		The datetime when the item was last updated
Agency	String	False		The agency name (US Only)
LegacyId	Int	False		The legacy ID for the item
IsVisible	Bool	False		Indicates whether the tax rate is displayed in the application
Percentage	String	False		The current tax rate percentage
Date	Datetime	True
AddressRegionId	String	True
IncludeHistoricalData	Bool	True

Views

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

Queries can be executed against a view as if it were a normal table, and the data that comes back is similar in that regard.

Dynamic views, such as queries exposed as views, and views for looking up specific combinations of project_team work items are supported.

Sage Business Cloud Accounting Connector for CData Sync Views


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

AddressRegions

Query AddressRegions in Sage Business Cloud Accounting.

Table Specific Information

Query the available AddressRegions

Select

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

CountryId supports the '=' operator.

For example, the following queries are processed server side:

SELECT * FROM AddressRegions WHERE CountryId='AF'

Columns


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

ArtefactStatuses

Query ArtefactStatuses in Sage Business Cloud Accounting.

Table Specific Information

Query the available ArtefactStatuses

Select

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

Columns


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

BaseJournalLine

Query BaseJournalLine in Sage Business Cloud Accounting.

Table Specific Information

Query the available BaseJournalLine

Select

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

Columns


Name	Type	References	Description
Id [KEY]	String		The unique identifier for the item
Details	String		A description of the journal line
LedgerAccountId	String	LedgerAccounts.Id	The unique identifier for the item
Debit	String		The debit amount of the journal line
Credit	String		The credit amount of the journal line
OpeningBalanceJournalsId	String		Parent ID

BusinessActivityTypes

Query BusinessActivityTypes in Sage Business Cloud Accounting.

Table Specific Information

Query the available BusinessActivityTypes

Select

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

Columns


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

Businesses

Query Businesses in Sage Business Cloud Accounting.

Columns


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

BusinessSettings

Query BusinessSettings in Sage Business Cloud Accounting.

Table Specific Information

Query the available BusinessSettings

Select

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

Columns


Name	Type	References	Description
Siret	String		SIRET Number (France only)
ShareCapital	String		Share Capital (France only)
AuxiliaryAccountsVisible	Bool		Auxiliary Accounts Visible (France & Spain only)
PurchaseLedgerAccountId	String	LedgerAccounts.Id	The unique identifier for the item
SalesLedgerAccountId	String	LedgerAccounts.Id	The unique identifier for the item
BankInterestChargesPaidLedgerAccountId	String	LedgerAccounts.Id	The unique identifier for the item
StockPurchaseLedgerAccountId	String	LedgerAccounts.Id	The unique identifier for the item
CarriageLedgerAccountId	String	LedgerAccounts.Id	The unique identifier for the item
ProductSalesLedgerAccountId	String		The unique identifier for the item
PurchaseDiscountLedgerAccountId	String		The unique identifier for the item
ExchangeRateLossesLedgerAccountId	String		The unique identifier for the item
OtherPaymentLedgerAccountId	String		The unique identifier for the item
BankChargesLedgerAccountId	String		The unique identifier for the item
ProductPurchaseLedgerAccountId	String		The unique identifier for the item
PurchaseLedgerAccountId	String		The unique identifier for the item
CustomerReceiptDiscountLedgerAccountId	String		The unique identifier for the item
SalesDiscountLedgerAccountId	String		The unique identifier for the item
ExchangeRateGainsLedgerAccountId	String		The unique identifier for the item
VendorPaymentDiscountLedgerAccountId	String		The unique identifier for the item
OtherReceiptLedgerAccountId	String		The unique identifier for the item
BankInterestReceivedLedgerAccountId	String		The unique identifier for the item
ServiceSalesLedgerAccountId	String		The unique identifier for the item
ManagementCentreMember	Bool		Member of Approved Management Centres (France only)
CountryOfRegistrationId	String	Countries.Id	The unique identifier for the item
RcsNumber	String		RCS Number (France only)
BusinessActivityTypeId	String	BusinessActivityTypes.Id	The unique identifier for the item
NIBased	Bool		Determines if a business is located in Northern Ireland

CoaAccounts

Query CoaAccounts in Sage Business Cloud Accounting.

Table Specific Information

Query the available CoaAccounts

Select

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

CoaTemplateId supports the '=' operator.

For example, the following queries are processed server side:

SELECT * FROM CoaAccounts WHERE CoaTemplateId='SMALL_BUSINESS_GB'

Columns


Name	Type	References	Description
Id [KEY]	String		The unique identifier for the item
CreatedAt	Datetime		The datetime when the item was created
DisplayedAs	String		The name of the resource
FixedTaxRate	Bool		Indicates whether the default tax rate is fixed or may be changed
Name	String		The name for the COA account
LedgerAccountTypeId	String		The unique identifier for the item The allowed values are SALES, OTHER_INCOME, DIRECT_EXPENSES, OVERHEADS, DEPRECIATION, CURRENT_ASSETS, FIXED_ASSETS, FUTURE_ASSETS, BANK, CURRENT_LIABILITY, FUTURE_LIABILITY, v, LINE_OF_CREDIT.
LedgerAccountGroupDisplayedAs	String		The name of the resource
LedgerAccountGroupLegacyId	Int		The legacy ID for the item
LedgerAccountGroupId	String		The unique identifier for the item
NominalCode	Int		The nominal code of the COA account
UpdatedAt	Datetime		The datetime when the item was last updated
LegacyId	Int		The legacy ID for the item
ControlName	String		The system control name for the COA account
LedgerAccountClassificationId	String	LedgerAccountClassifications.Id	The unique identifier for the item
TaxRateId	String	TaxRates.Id	The unique identifier for the item
CoaTemplateId	String

CoaTemplates

Query CoaTemplates in Sage Business Cloud Accounting.

Table Specific Information

Query the available CoaTemplates

Select

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

CountryId supports the '=' operator.

For example, the following queries are processed server side:

SELECT * FROM CoaTemplates WHERE CountryId='AF'

Columns


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

CorrectiveReasonCodes

Query CorrectiveReasonCodes in Sage Business Cloud Accounting.

Table Specific Information

Query the available CorrectiveReasonCodes

Select

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

Columns


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

Countries

Query Countries in Sage Business Cloud Accounting.

Table Specific Information

Query the available Countries

Select

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

Columns


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

CountryGroups

Query CountryGroups in Sage Business Cloud Accounting.

Table Specific Information

Query the available CountryGroups

Select

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

Columns


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

CountryOfRegistrations

Query CountryOfRegistrations in Sage Business Cloud Accounting.

Table Specific Information

Query the available CountryOfRegistrations

Select

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

Columns


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

Currencies

Query Currencies in Sage Business Cloud Accounting.

Table Specific Information

Query the available Currencies

Select

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

Columns


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

EuSalesDescriptions

Query EuSalesDescriptions in Sage Business Cloud Accounting.

Table Specific Information

Query the available EuSalesDescriptions

Select

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

Columns


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

ExchangeRates

Query ExchangeRates in Sage Business Cloud Accounting.

Table Specific Information

Query the available ExchangeRates

Select

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

Columns


Name	Type	References	Description
UpdatedAt	Datetime		The datetime when the item was last updated
CurrencyId	String	Currencies.Id	The unique identifier for the item
Rate	String		The exchange rate
DisplayedAs	String		Display text for the item
CreatedAt	Datetime		The datetime when the item was created
BaseCurrencyId	String	Currencies.Id	The unique identifier for the item
InverseRate	String		The inverse exchange rate

JournalLine

Query JournalLine in Sage Business Cloud Accounting.

Table Specific Information

Query the available JournalLine

Select

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

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

For example, the following queries are processed server side:

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

Columns


Name	Type	References	Description
Id [KEY]	String		The unique identifier for the item
Details	String		A description of the journal line
Cleared	Bool		Indicates if the journal line is cleared or not.
Credit	String		The credit amount of the journal line
BankReconciled	Bool		Indicates if the journal line is bank reconciled or not.
LedgerAccountId	String	LedgerAccounts.Id	The unique identifier for the item
IncludeOnTaxReturn	Bool		Indicates whether the journal line should affect the tax return
TaxReconciled	Bool		Indicates if the journal line is tax reconciled or not.
Debit	String		The debit amount of the journal line
JournalsId [KEY]	String		Parent ID
DeletedSince	Datetime

LedgerAccountClassifications

Query LedgerAccountClassifications in Sage Business Cloud Accounting.

Table Specific Information

Query the available LedgerAccountClassifications

Select

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

LedgerAccountTypeId supports the '=' operator.

For example, the following queries are processed server side:

SELECT * FROM LedgerAccountClassifications WHERE LedgerAccountTypeId='abc'

Columns


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

LedgerEntries

Query LedgerEntries in Sage Business Cloud Accounting.

Table Specific Information

Query the available LedgerEntries

Select

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

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

For example, the following queries are processed server side:

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

Columns


Name	Type	References	Description
Id [KEY]	String		The unique identifier for the item
CreatedAt	Datetime		The datetime when the item was created
DisplayedAs	String		The name of the resource
Deleted	Bool		Indicates whether the ledger entry has been deleted or not
TransactionId	String	Transactions.Id	The unique identifier for the item
Date	Datetime		The date of the ledger entry
Credit	String		The credit amount of the ledger entry
LedgerAccountId	String	LedgerAccounts.Id	The unique identifier for the item
ContactId	String	Contacts.Id	The unique identifier for the item
LinksAggregate	String		Links for the resource
UpdatedAt	Datetime		The datetime when the item was last updated
LegacyId	Int		The legacy ID for the item
Debit	String		The debit amount of the ledger entry
TaxRateId	String	TaxRates.Id	The unique identifier for the item
ComponentTaxRatesAggregate	String		The component tax rates which make up a combined rate
Description	String		The ledger entry description
JournalCodeId	String	JournalCodes.Id	The unique identifier for the item
FromDate	Datetime
ToDate	Datetime
TransactionTypeId	String

LiveExchangeRates

Query LiveExchangeRates in Sage Business Cloud Accounting.

Table Specific Information

Query the available LiveExchangeRates

Select

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

Columns


Name	Type	References	Description
CurrencyId	String	Currencies.Id	The unique identifier for the item
UpdatedAt	Datetime
Rate	String		The exchange rate
DisplayedAs	String		Display text for the item
BaseCurrencyId	String	Currencies.Id	The unique identifier for the item
InverseRate	String		The inverse exchange rate
RetrievedAt	String

OtherPaymentLineItem

Query OtherPaymentLineItem in Sage Business Cloud Accounting.

Table Specific Information

Query the available OtherPaymentLineItem

Select

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

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

For example, the following queries are processed server side:

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

Columns


Name	Type	References	Description
Id [KEY]	String		The unique identifier for the item
Details	String		The details of the payment line
DisplayedAs	String		The name of the resource
GstAmount	String		The gst or hst tax amount for the other payment
TradeOfAsset	Bool		Whether the line item is marked as trade of asset.
LedgerAccountId	String	LedgerAccounts.Id	The unique identifier for the item
LegacyId	Int		The legacy ID for the item
TaxBreakdownAggregate	String		The tax breakdown for the payment line
IsPurchaseForResale	Bool		Identifies whether the line item is for resale. (Ireland only)
TaxRateId	String	TaxRates.Id	The unique identifier for the item
PstAmount	String		The pst or qst tax amount for the other payment
TotalAmount	String		The total amount of the payment line
TaxAmount	String		The tax amount of the payment line
NetAmount	String		The net amount of the payment line
BaseId	String		Parent ID
BankAccountId	String
ContactId	String
DeletedSince	Datetime
FromDate	Datetime
HasAttachments	String
ToDate	String
TransactionTypeId	String

PaymentMethods

Query PaymentMethods in Sage Business Cloud Accounting.

Table Specific Information

Query the available PaymentMethods

Select

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

Columns


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

PurchaseCorrectiveInvoice

Query PurchaseCorrectiveInvoice in Sage Business Cloud Accounting.

Table Specific Information

Query the available PurchaseCorrectiveInvoice

Select

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

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

For example, the following queries are processed server side:

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

Columns


Name	Type	References	Description
Id [KEY]	String		The unique identifier for the item
BaseId	String		Parent ID
DisplayedAs	String		The name of the resource
Reference	String		The reference for the invoice
BaseCurrencyTotalAmount	String		The total amount of the invoice in base currency
BaseCurrencyNetAmount	String		The net amount of the invoice in base currency
ContactName	String		The name of the contact when the invoice was created
DueDate	String		The due date of the invoice
OriginalInvoiceDisplayedAs	String		The name of the resource
OriginalInvoiceLegacyId	Int		The legacy ID for the item
OriginalInvoiceId	String		The unique identifier for the item
TaxAmount	String		The tax amount of the invoice
LastPaid	String		The date of the last payment
PaymentsAllocationsTotalDiscount	String		The total discount of all payments and allocations
OriginalInvoiceDate	String		The Invoice date relating to the original invoice (Spain only)
WithholdingTaxRate	String		IRPF withheld Tax Rate (Spain only)
TotalAmount	String		The total amount of the invoice
ExchangeRate	String		The exchange rate for the invoice
OutstandingAmount	String		The outstanding amount of the invoice
ContactDisplayedAs	String		The name of the resource
ContactId	String	Contacts.Id	The unique identifier for the item
NetAmount	String		The net amount of the invoice
ContactReference	String		The reference of the contact when the invoice was created
PaymentsAllocationsAggregate	String		The associated payments and allocations
BaseCurrencyWithholdingTaxAmount	String		IRPF withheld Tax Amount (Spain only) in the base currency
WithholdingTaxAmount	String		IRPF withheld Tax Amount (Spain only)
UpdatedAt	Datetime		The datetime when the item was last updated
LegacyId	Int		The legacy ID for the item
TaxanalysisAggregate	String		The invoice tax analysis (Optional for Spain, restricted for all other regions)
OriginalInvoiceNumber	String		The number relating to the original invoice (Spain only)
InverseExchangeRate	String		The inverse exchange rate for the credit note
TotalQuantity	String		The total quantity of the invoice
TransactionId	String	Transactions.Id	The unique identifier for the item
StatusDisplayedAs	String		The name of the resource
StatusLegacyId	Int		The legacy ID for the item
StatusId	String		The unique identifier for the item
BaseCurrencyTotalGoodsAmount	String		The total base currency amount relating to goods for all tax rates
BaseCurrencyTotalTax	String		The base currency total tax amount for all tax rates
TotalRetailerTax	String		The total retailer tax amount for all tax rates
BaseCurrencyTotal	String		The total base currency amount for all tax rates
TotalGoodsAmount	String		The total amount relating to goods for all tax rates
TaxRatesBreakdownServicesAmount	String		The total amount for the tax rate relating to services
TaxRatesBreakdownBaseCurrencyNetAmount	String		The base currency net amount for the tax rate
TaxRatesBreakdownName	String		The tax rate name
TaxRatesBreakdownBaseCurrencyServicesAmount	String		The total base currency amount for the tax rate relating to services
TaxRatesBreakdownBaseCurrencyTaxAmount	String		The base currency tax amount for the tax rate
TaxRatesBreakdownBaseCurrencyTotalAmount	String		The base currency total amount for the tax rate
TaxRatesBreakdownGoodsAmount	String		The total amount for the tax rate relating to goods
TaxRatesBreakdownRetailTaxAmount	String		The retailer tax amount for the tax rate
TaxRatesBreakdownBaseCurrencyGoodsAmount	String		The total base currency amount for the tax rate relating to goods
TaxRatesBreakdownTaxRateCreatedAt	Datetime		The datetime when the item was created
TaxRatesBreakdownTaxRateIsCombinedRate	Bool		Indicates whether the tax rate is made up of component tax rates
TaxRatesBreakdownTaxRateDisplayedAs	String		The name of the resource
TaxRatesBreakdownTaxRateName	String		The name of the tax rate
TaxRatePercentageAggregate	String		The tax rate percentage and date ranges they apply to
TransactionTypeId	String	TaxRates.Id	The unique identifier for the item
CreatedAt	Datetime		The datetime when the item was created
PaymentsAllocationsTotalAmount	String		The total amount of all payments and allocations
VoidReason	String		The reason the invoice was voided
TaxReconciled	Bool		Indicates if the artefact is tax reconciled or not.
Editable	Bool		Indicates whether artefact can be edited
VendorReference	String		The vendor reference for the invoice
CurrencyId	String	Currencies.Id	The unique identifier for the item
PurchaseInvoiceLineItemAggregate	String		The invoice lines of the invoice
Notes	String		Invoice notes
TotalPaid	String		The total paid amount of the invoice including any payments, allocations and discounts
DeletedAt	Datetime		The datetime when the item was deleted
BaseCurrencyTaxAmount	String		The tax amount of the invoice in base currency
Date	String		The date of the invoice
BaseCurrencyOutstandingAmount	String		The outstanding amount of the invoice in base currency
ShowPaymentsAllocations	String
ShowCorrections	String
Search	String
ContactId	String
StatusId	String
FromDate	Datetime
ToDate	Datetime
DeletedSince	Datetime
HasAttachments	String

PurchaseCreditNoteLineItem

Query PurchaseCreditNoteLineItem in Sage Business Cloud Accounting.

Table Specific Information

Query the available PurchaseCreditNoteLineItem

Select

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

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

For example, the following queries are processed server side:

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

Columns


Name	Type	References	Description
Id [KEY]	String		The unique identifier for the item
PurchaseCreditNotesId	String		Parent ID
TaxRateId	String	TaxRates.Id	The unique identifier for the item
ProductId	String	Products.Id	The unique identifier for the item
EuGoodsServicesTypeId	String		The unique identifier for the item The allowed values are GOODS, SERVICES.
NetAmount	String		The net amount for the credit note line
BaseCurrencyTaxAmount	String		The tax amount for the credit note line in base currency
BaseCurrencyTotalAmount	String		The total amount for the credit note line in base currency
Quantity	String		The quantity for the credit note line
TotalAmount	String		The total amount for the credit note line
LedgerAccountId	String	LedgerAccounts.Id	The unique identifier for the item
IsPurchaseForResale	Bool		Identifies whether the line item is for resale. (Ireland Only)
BaseCurrencyTaxBreakdownAggregate	String		The tax breakdown for the credit note line in base currency
TaxBreakdownAggregate	String		The tax breakdown for the credit note line
UnitPrice	String		The unit price for the credit note line
BaseCurrencyUnitPrice	String		The unit price for the credit note line in base currency
GstAmount	String		The gst or hst tax amount for the credit note line
BaseCurrencyNetAmount	String		The net amount for the credit note line in base currency
DisplayedAs	String		The name of the resource
Description	String		The description for the credit note line
TaxAmount	String		The tax amount for the credit note line
PstAmount	String		The pst or qst tax amount for the credit note line
LegacyId	Int		The legacy ID for the item
TradeOfAsset	Bool		Whether the line item is marked as trade of asset.
UnitPriceIncludesTax	Bool		Defines whether the unit price includes tax
ServiceId	String	Services.Id	The unique identifier for the item
ShowPaymentsAllocations	String
Search	String
ContactId	String
StatusId	String
FromDate	Datetime
ToDate	Datetime
DeletedSince	Datetime
HasAttachments	String
ShowPaymentsAllocations	String

PurchaseInvoiceLineItem

Query PurchaseInvoiceLineItem in Sage Business Cloud Accounting.

Table Specific Information

Query the available PurchaseInvoiceLineItem

Select

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

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

For example, the following queries are processed server side:

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

Columns


Name	Type	References	Description
Id [KEY]	String		The unique identifier for the item
PurchaseInvoicesId	String		Parent ID
TaxRateDisplayedAs	String		The name of the resource
TaxRateLegacyId	Int		The legacy ID for the item
TaxRateId	String		The unique identifier for the item
ProductId	String	Products.Id	The unique identifier for the item
EuGoodsServicesTypeId	String		The unique identifier for the item The allowed values are GOODS, SERVICES.
NetAmount	String		The net amount for the invoice line
PurchaseInvoicesCurrencyTaxAmount	String		The tax amount for the invoice line in PurchaseInvoices currency
PurchaseInvoicesCurrencyTotalAmount	String		The total amount for the invoice line in PurchaseInvoices currency
Quantity	String		The quantity for the invoice line
TotalAmount	String		The total amount for the invoice line
LedgerAccountId	String	LedgerAccounts.Id	The unique identifier for the item
IsPurchaseForResale	Bool		Identifies whether the line item is for resale. (Ireland Only)
PurchaseInvoicesCurrencyTaxBreakdownAggregate	String		The tax breakdown for the invoice line in PurchaseInvoices currency
TaxBreakdownAggregate	String		The tax breakdown for the invoice line
UnitPrice	String		The unit price for the invoice line
PurchaseInvoicesCurrencyUnitPrice	String		The unit price for the invoice line in PurchaseInvoices currency
GstAmount	String		The gst or hst tax amount for the invoice line
PurchaseInvoicesCurrencyNetAmount	String		The net amount for the invoice line in PurchaseInvoices currency
DisplayedAs	String		The name of the resource
Description	String		The description for the invoice line
TaxAmount	String		The tax amount for the invoice line
PstAmount	String		The pst or qst tax amount for the invoice line
LegacyId	Int		The legacy ID for the item
TradeOfAsset	Bool		Whether the line item is marked as trade of asset.
UnitPriceIncludesTax	Bool		Defines whether the unit price includes tax
ServiceId	String	Services.Id	The unique identifier for the item
ShowPaymentsAllocations	String
HasAttachments	String
FromDate	Datetime
ContactId	String
StatusId	String
ShowCorrections	String
DeletedSince	Datetime
Search	String
ToDate	Datetime

SalesCorrectiveInvoice

Query SalesCorrectiveInvoice in Sage Business Cloud Accounting.

Table Specific Information

Query the available SalesCorrectiveInvoice

Select

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

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

For example, the following queries are processed server side:

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

Columns


Name	Type	References	Description
Id [KEY]	String		The unique identifier for the item
SalesArtefactAddressId	String		Parent ID
DisplayedAs	String		The name of the resource
Reference	String		The reference for the invoice
BaseCurrencyTotalAmount	String		The total amount of the invoice in base currency
MainAddressId	String	Addresses.Id	The unique identifier for the item
BaseCurrencyNetAmount	String		The net amount of the invoice in base currency
BaseCurrencyShippingTaxBreakdownAggregate	String		The shipping tax breakdown for the invoice in base currency
ContactName	String		The name of the contact when the invoice was created
SentByEmail	Bool		Indicates whether the invoice has been emailed
ShippingTaxBreakdownAggregate	String		The shipping tax breakdown for the invoice
DueDate	Datetime		The due date of the invoice
TotalDiscountAmount	String		The discount amount on the invoice
LinksAggregate	String		Links for the resource
OriginalInvoiceDisplayedAs	String		The name of the resource
OriginalInvoiceLegacyId	Int		The legacy ID for the item
OriginalInvoiceId	String		The unique identifier for the item
TaxAmount	String		The tax amount of the invoice
LastPaid	String		The date of the last payment
PaymentsAllocationsTotalDiscount	String		The total discount of all payments and allocations
OriginalInvoiceDate	String		The Invoice date relating to the original invoice
WithholdingTaxRate	String		IRPF Witheld Tax Rate (Spain only)
TotalAmount	String		The total amount of the invoice
ExchangeRate	String		The exchange rate for the invoice
InvoiceNumberPrefix	String		The invoice number prefix
OutstandingAmount	String		The outstanding amount of the invoice
ContactId	String	Contacts.Id	The unique identifier for the item
NetAmount	String		The net amount of the invoice
ContactReference	String		The reference of the contact when the invoice was created
TaxCalculationMethod	String		The tax calculation method, if applicable, for this sales invoice, returns invoice, cash or retailer.
TermsAndConditions	String		Invoice terms and conditions
PaymentsAllocationsAggregate	String		The associated payments and allocations
ShippingTaxRateId	String	TaxRates.Id	The unique identifier for the item
Sent	Bool		Indicates whether the invoice has been sent
DeliveryAddressFreeForm	String		The free-form delivery address of the invoice
BaseCurrencyWithholdingTaxAmount	String		IRPF Witheld Tax Amount (Spain only) in the base currency
DeliveryAddressId	String	Addresses.Id	The unique identifier for the item
WithholdingTaxAmount	String		IRPF Witheld Tax Amount (Spain only)
UpdatedAt	Datetime		The datetime when the item was last updated
LegacyId	Int		The legacy ID for the item
TaxAnalysisAggregate	String		The invoice tax analysis
OriginalInvoiceNumber	String		The number relating to the original invoice
InverseExchangeRate	String		The inverse exchange rate for the invoice
BaseCurrencyShippingTotalAmount	String		The total shipping amount in base currency
TransactionId	String	Transactions.Id	The unique identifier for the item
TotalQuantity	String		The total quantity of the invoice
StatusDisplayedAs	String		The name of the resource
StatusLegacyId	Int		The legacy ID for the item
StatusId	String		The unique identifier for the item
DetailedTaxAnalysisBaseCurrencyTotalGoodsAmount	String		The total base currency amount relating to goods for all tax rates
DetailedTaxAnalysisBaseCurrencyTotalTax	String		The base currency total tax amount for all tax rates
DetailedTaxAnalysisTotalRetailerTax	String		The total retailer tax amount for all tax rates
DetailedTaxAnalysisBaseCurrencyTotal	String		The total base currency amount for all tax rates
DetailedTaxAnalysisTotalGoodsAmount	String		The total amount relating to goods for all tax rates
DetailedTaxAnalysisTotalNet	String		The total net amount for all tax rates
DetailedTaxAnalysisBaseCurrencyTotalServicesAmount	String		The total base currency amount relating to services for all tax rates
DetailedTaxAnalysisBaseCurrencyTotalNet	String		The base currency total net amount for all tax rates
DetailedTaxAnalysisTotalServicesAmount	String		The total amount relating to services for all tax rates
DetailedTaxAnalysisTotal	String		The total amount for all tax rates
DetailedTaxAnalysisTotalTax	String		The total tax amount for all tax rates
TransactionTypeId	String	TransactionTypes.Id	The unique identifier for the item
BaseCurrencyShippingTaxAmount	String		The tax shipping amount in base currency
ShippingTaxAmount	String		The tax shipping amount. NOTE: This is not required for POST/PUT requests as the shipping tax is calculated based on the shipping_net_amount and the shipping_tax_rate.
CreatedAt	Datetime		The datetime when the item was created
PaymentsAllocationsTotalAmount	String		The total amount of all payments and allocations
VoidReason	String		The reason the invoice was voided
TaxReconciled	Bool		Indicates if the artefact is tax reconciled or not.
Editable	Bool		Indicates whether artefact can be edited
ShippingTotalAmount	String		The total shipping amount
BaseCurrencyShippingNetAmount	String		The net shipping amount in base currency
ShippingNetAmount	String		The net shipping amount
CurrencyId	String	Currencies.Id	The unique identifier for the item
Details	String		The corrective invoice details
SalesInvoiceLineItemAggregate	String		The invoice lines of the invoice
CorrectiveReasonCodeId	String	CorrectiveReasonCodes.Id	The unique identifier for the item
InvoiceNumber	String		The generated invoice number
Notes	String		Invoice notes
MainAddressFreeForm	String		The free-form main address of the invoice
TotalPaid	String		The total paid amount of the invoice including any payments, allocations and discounts
DeletedAt	Datetime		The datetime when the item was deleted
Date	String		The date of the invoice
BaseCurrencyTaxAmount	String		The tax amount of the invoice in base currency
BaseCurrencyOutstandingAmount	String		The outstanding amount of the invoice in base currency
BaseCurrencyTotalDiscountAmount	String		The discount amount on the invoice in base currency
ShowPaymentsAllocations	String
ShowCorrections	String
MarkAsSent	String
Search	String
ContactId	String
StatusId	String
FromDate	Datetime
ToDate	Datetime
DeletedSince	Datetime
HasAttachments	String

SalesCreditNoteLineItem

Query SalesCreditNoteLineItem in Sage Business Cloud Accounting.

Table Specific Information

Query the available SalesCreditNoteLineItem

Select

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

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

For example, the following queries are processed server side:

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

Columns


Name	Type	References	Description
Id [KEY]	String		The unique identifier for the item
SalesCreditNotesId	String		Parent ID
TaxRateDisplayedAs	String		The name of the resource
TaxRateLegacyId	Int		The legacy ID for the item
TaxRateId	String		The unique identifier for the item
ProductId	String	Products.Id	The unique identifier for the item
EuGoodsServicesTypeId	String		The unique identifier for the item The allowed values are GOODS, SERVICES.
NetAmount	String		The net amount for the invoice line
BaseCurrencyTaxAmount	String		The tax amount for the invoice line in base currency
BaseCurrencyTotalAmount	String		The total amount for the invoice line in base currency
DiscountPercentage	String		The discount percentage for the invoice line
Quantity	String		The quantity for the invoice line
TotalAmount	String		The total amount for the invoice line
BaseCurrencyDiscountAmount	String		The discount amount for the invoice line in base currency
LedgerAccountId	String	LedgerAccounts.Id	The unique identifier for the item
BaseCurrencyTaxBreakdownAggregate	String		The tax breakdown for the invoice line in base currency
TaxbreakdownAggregate	String		The tax breakdown for the invoice line
UnitPrice	String		The unit price for the invoice line
BaseCurrencyUnitPrice	String		The unit price for the invoice line in base currency
DiscountAmount	String		The discount amount for the invoice line
BaseCurrencyNetAmount	String		The net amount for the invoice line in base currency
DisplayedAs	String		The name of the resource
Description	String		The description for the invoice line
TaxAmount	String		The tax amount for the invoice line
LegacyId	Int		The legacy ID for the item
TradeOfAsset	Bool		Whether the line item is marked as trade of asset.
EuSalesDescriptionId	String	EuSalesDescriptions.Id	The unique identifier for the item
UnitPriceIncludesTax	Bool		Defines whether the unit price includes tax
ServiceId	String	Services.Id	The unique identifier for the item
ShowPaymentsAllocations	String
MarkAsSent	String
ShowPaymentsAllocations	String
Search	String
ContactId	String
StatusId	String
FromDate	Datetime
ToDate	Datetime
DeletedSince	Datetime
HasAttachments	String

SalesQuoteLineItem

Query SalesEstimatesQuoteLineItem in Sage Business Cloud Accounting.

Table Specific Information

Query the available SalesQuoteLineItem

Select

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

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

For example, the following queries are processed server side:

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

Columns


Name	Type	References	Description
Id [KEY]	String		The unique identifier for the item
QuoteStatusId	String		Parent ID
TaxRateId	String	TaxRates.Id	The unique identifier for the item
ProductId	String	Products.Id	The unique identifier for the item
EuGoodsServicesTypeId	String		The unique identifier for the item The allowed values are GOODS, SERVICES.
NetAmount	String		The net amount for the quote line
BaseCurrencyTaxAmount	String		The tax amount for the quote line in base currency
BaseCurrencyTotalAmount	String		The total amount for the quote line in base currency
DiscountPercentage	String		The discount percentage for the quote line
Quantity	String		The quantity for the quote line
TotalAmount	String		The total amount for the quote line
BaseCurrencyDiscountAmount	String		The discount amount for the quote line in base currency
LedgerAccountId	String	LedgerAccounts.Id	The unique identifier for the item
BaseCurrencyTaxBreakdownAggregate	String		The tax breakdown for the quote line in base currency
TaxBreakdownAggregate	String		The tax breakdown for the quote line
UnitPrice	String		The unit price for the quote line
BaseCurrencyUnitPrice	String		The unit price for the quote line in base currency
DiscountAmount	String		The discount amount for the quote line
BaseCurrencyNetAmount	String		The net amount for the quote line in base currency
DisplayedAs	String		The name of the resource
Description	String		The description for the quote line
TaxAmount	String		The tax amount for the quote line
LegacyId	Int		The legacy ID for the item
TradeOfAsset	Bool		Whether the line item is marked as trade of asset.
EuSalesDescriptionId	String	EuSalesDescriptions.Id	The unique identifier for the item
UnitPriceIncludesTax	Bool		Defines whether the unit price includes tax
ServiceId	String	Services.Id	The unique identifier for the item
ContactId	String
Search	String
StatusId	String
FromDate	Datetime
ToDate	Datetime
HasAttachments	String

Query SalesQuoteLineItem in Sage Business Cloud Accounting.

Table Specific Information

Query the available SalesQuoteLineItem

Select

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

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

For example, the following queries are processed server side:

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

Columns


Name	Type	References	Description
Id [KEY]	String		The unique identifier for the item
QuoteStatusId	String		Parent ID
TaxRateId	String	TaxRates.Id	The unique identifier for the item
ProductId	String	Products.Id	The unique identifier for the item
EuGoodsServicesTypeId	String		The unique identifier for the item The allowed values are GOODS, SERVICES.
NetAmount	String		The net amount for the quote line
BaseCurrencyTaxAmount	String		The tax amount for the quote line in base currency
BaseCurrencyTotalAmount	String		The total amount for the quote line in base currency
DiscountPercentage	String		The discount percentage for the quote line
Quantity	String		The quantity for the quote line
TotalAmount	String		The total amount for the quote line
BaseCurrencyDiscountAmount	String		The discount amount for the quote line in base currency
LedgerAccountId	String	LedgerAccounts.Id	The unique identifier for the item
BaseCurrencyTaxBreakdownAggregate	String		The tax breakdown for the quote line in base currency
TaxBreakdownAggregate	String		The tax breakdown for the quote line
UnitPrice	String		The unit price for the quote line
BaseCurrencyUnitPrice	String		The unit price for the quote line in base currency
DiscountAmount	String		The discount amount for the quote line
BaseCurrencyNetAmount	String		The net amount for the quote line in base currency
DisplayedAs	String		The name of the resource
Description	String		The description for the quote line
TaxAmount	String		The tax amount for the quote line
LegacyId	Int		The legacy ID for the item
TradeOfAsset	Bool		Whether the line item is marked as trade of asset.
EuSalesDescriptionId	String	EuSalesDescriptions.Id	The unique identifier for the item
UnitPriceIncludesTax	Bool		Defines whether the unit price includes tax
ServiceId	String	Services.Id	The unique identifier for the item
ContactId	String	Contacts.Id
Search	String
StatusId	String
FromDate	Datetime
ToDate	Datetime
HasAttachments	String

SalesInvoiceLineItem

Query SalesInvoiceLineItem in Sage Business Cloud Accounting.

Table Specific Information

Query the available SalesInvoiceLineItem

Select

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

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

For example, the following queries are processed server side:

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

Columns


Name	Type	References	Description
Id [KEY]	String		The unique identifier for the item
SalesInvoicesId	String		Parent ID
TaxRateId	String	TaxRates.Id	The unique identifier for the item
ProductId	String	Products.Id	The unique identifier for the item
EuGoodsServicesTypeId	String		The unique identifier for the item The allowed values are GOODS, SERVICES.
NetAmount	Double		The net amount for the invoice line
SalesInvoicesCurrencyTaxAmount	Double		The tax amount for the invoice line in base currency
SalesInvoicesCurrencyTotalAmount	Double		The total amount for the invoice line in base currency
DiscountPercentage	String		The discount percentage for the invoice line
Quantity	Double		The quantity for the invoice line
TotalAmount	Double		The total amount for the invoice line
SalesInvoicesCurrencyDiscountAmount	Double		The discount amount for the invoice line in base currency
LedgerAccountId	String	LedgerAccounts.Id	The unique identifier for the item
BaseCurrencyTaxBreakdownAggregate	String		The tax breakdown for the invoice line in base currency
TaxBreakdownAggregate	String		The tax breakdown for the invoice line
UnitPrice	Double		The unit price for the invoice line
SalesInvoicesCurrencyUnitPrice	Double		The unit price for the invoice line in base currency
DiscountAmount	String		The discount amount for the invoice line
SalesInvoicesCurrencyNetAmount	Double		The net amount for the invoice line in base currency
DisplayedAs	String		The name of the resource
Description	String		The description for the invoice line
TaxAmount	Double		The tax amount for the invoice line. This attribute is required in v3.1, unless the tax rate is of a 'zero', 'exempt' or 'no_tax' type. Then the tax_amount is infered as 0.0.
LegacyId	Int		The legacy ID for the item
TradeOfAsset	Bool		Whether the line item is marked as trade of asset.
EuSalesDescriptionDisplayedAs	String		The name of the resource
EuSalesDescriptionLegacyId	Int		The legacy ID for the item
EuSalesDescriptionId	String		The unique identifier for the item
UnitPriceIncludesTax	Bool		Defines whether the unit price includes tax
ServiceId	String	Services.Id	The unique identifier for the item
ContactId	String	Contacts.Id
StatusId	String
FromDate	Datetime
ToDate	Datetime
DeletedSince	Datetime
HasAttachments	String
ShowPaymentsAllocations	String
Search	String

SalesQuoteLineItem

Query SalesEstimatesQuoteLineItem in Sage Business Cloud Accounting.

Table Specific Information

Query the available SalesQuoteLineItem

Select

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

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

For example, the following queries are processed server side:

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

Columns


Name	Type	References	Description
Id [KEY]	String		The unique identifier for the item
QuoteStatusId	String		Parent ID
TaxRateId	String	TaxRates.Id	The unique identifier for the item
ProductId	String	Products.Id	The unique identifier for the item
EuGoodsServicesTypeId	String		The unique identifier for the item The allowed values are GOODS, SERVICES.
NetAmount	String		The net amount for the quote line
BaseCurrencyTaxAmount	String		The tax amount for the quote line in base currency
BaseCurrencyTotalAmount	String		The total amount for the quote line in base currency
DiscountPercentage	String		The discount percentage for the quote line
Quantity	String		The quantity for the quote line
TotalAmount	String		The total amount for the quote line
BaseCurrencyDiscountAmount	String		The discount amount for the quote line in base currency
LedgerAccountId	String	LedgerAccounts.Id	The unique identifier for the item
BaseCurrencyTaxBreakdownAggregate	String		The tax breakdown for the quote line in base currency
TaxBreakdownAggregate	String		The tax breakdown for the quote line
UnitPrice	String		The unit price for the quote line
BaseCurrencyUnitPrice	String		The unit price for the quote line in base currency
DiscountAmount	String		The discount amount for the quote line
BaseCurrencyNetAmount	String		The net amount for the quote line in base currency
DisplayedAs	String		The name of the resource
Description	String		The description for the quote line
TaxAmount	String		The tax amount for the quote line
LegacyId	Int		The legacy ID for the item
TradeOfAsset	Bool		Whether the line item is marked as trade of asset.
EuSalesDescriptionId	String	EuSalesDescriptions.Id	The unique identifier for the item
UnitPriceIncludesTax	Bool		Defines whether the unit price includes tax
ServiceId	String	Services.Id	The unique identifier for the item
ContactId	String
Search	String
StatusId	String
FromDate	Datetime
ToDate	Datetime
HasAttachments	String

Query SalesQuoteLineItem in Sage Business Cloud Accounting.

Table Specific Information

Query the available SalesQuoteLineItem

Select

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

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

For example, the following queries are processed server side:

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

Columns


Name	Type	References	Description
Id [KEY]	String		The unique identifier for the item
QuoteStatusId	String		Parent ID
TaxRateId	String	TaxRates.Id	The unique identifier for the item
ProductId	String	Products.Id	The unique identifier for the item
EuGoodsServicesTypeId	String		The unique identifier for the item The allowed values are GOODS, SERVICES.
NetAmount	String		The net amount for the quote line
BaseCurrencyTaxAmount	String		The tax amount for the quote line in base currency
BaseCurrencyTotalAmount	String		The total amount for the quote line in base currency
DiscountPercentage	String		The discount percentage for the quote line
Quantity	String		The quantity for the quote line
TotalAmount	String		The total amount for the quote line
BaseCurrencyDiscountAmount	String		The discount amount for the quote line in base currency
LedgerAccountId	String	LedgerAccounts.Id	The unique identifier for the item
BaseCurrencyTaxBreakdownAggregate	String		The tax breakdown for the quote line in base currency
TaxBreakdownAggregate	String		The tax breakdown for the quote line
UnitPrice	String		The unit price for the quote line
BaseCurrencyUnitPrice	String		The unit price for the quote line in base currency
DiscountAmount	String		The discount amount for the quote line
BaseCurrencyNetAmount	String		The net amount for the quote line in base currency
DisplayedAs	String		The name of the resource
Description	String		The description for the quote line
TaxAmount	String		The tax amount for the quote line
LegacyId	Int		The legacy ID for the item
TradeOfAsset	Bool		Whether the line item is marked as trade of asset.
EuSalesDescriptionId	String	EuSalesDescriptions.Id	The unique identifier for the item
UnitPriceIncludesTax	Bool		Defines whether the unit price includes tax
ServiceId	String	Services.Id	The unique identifier for the item
ContactId	String	Contacts.Id
Search	String
StatusId	String
FromDate	Datetime
ToDate	Datetime
HasAttachments	String

TaxOffices

Query TaxOffices in Sage Business Cloud Accounting.

Table Specific Information

Query the available TaxOffices

Select

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

Columns


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

TaxReturnFrequencies

Query TaxReturnFrequencies in Sage Business Cloud Accounting.

Table Specific Information

Query the available TaxReturnFrequencies

Select

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

TaxTypeId supports the '=' operator.

For example, the following queries are processed server side:

SELECT * FROM TaxReturnFrequencies WHERE TaxTypeId='abc'

Columns


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

TaxSchemes

Query TaxSchemes in Sage Business Cloud Accounting.

Table Specific Information

Query the available TaxSchemes

Select

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

Columns


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

Transactions

Query Transactions in Sage Business Cloud Accounting.

Table Specific Information

Query the available Transactions

Select

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

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

For example, the following queries are processed server side:

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

Columns


Name	Type	References	Description
Id [KEY]	String		The unique identifier for the item
CreatedAt	Datetime		The datetime when the item was created
DisplayedAs	String		The name of the resource
NumberOfAttachments	String		The number of attachments related to the transaction
TransactionTypeId	String	TransactionTypes.Id	The unique identifier for the item
Deleted	Bool		Indicates whether the transaction has been deleted
Date	Datetime		The date of the transaction
Reference	String		The transaction reference
ContactId	String	Contacts.Id	The unique identifier for the item
UpdatedAt	Datetime		The datetime when the item was last updated
LegacyId	Int		The legacy ID for the item
Total	String		The transaction total
LinksAggregate	String		Links for the resource
OriginDisplayedAs	String		The name of the resource
OriginLegacyId	Int		The legacy ID for the item
OriginId	String		The unique identifier for the item
AuditTrailId	String		The original entity that generated the transaction
FromDate	Datetime
ToDate	Datetime
UpdatedFromDate	Datetime
UpdatedToDate	Datetime
HasAttachments	String

TransactionTypes

Query TransactionTypes in Sage Business Cloud Accounting.

Table Specific Information

Query the available TransactionTypes

Select

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

ValidForBusiness supports the '=' operator.

For example, the following queries are processed server side:

SELECT * FROM TransactionTypes WHERE ValidForBusiness=true'

Columns


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

UnallocatedArtefacts

Query UnallocatedArtefacts in Sage Business Cloud Accounting.

Table Specific Information

Query the available UnallocatedArtefacts

Select

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

ContactId supports the '=' operator.

For example, the following queries are processed server side:

SELECT * FROM UnallocatedArtefacts WHERE ContactId='abc'

Columns


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

Connection String Options

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

For more information on establishing a connection, see Establishing a Connection.

OAuth

Property	Description
OAuthClientId	The client Id assigned when you register your application with an OAuth authorization server.
OAuthClientSecret	The client secret assigned when you register your application with an OAuth authorization server.

SSL

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

Firewall

Property	Description
FirewallType	The protocol used by a proxy-based firewall.
FirewallServer	The name or IP address of a proxy-based firewall.
FirewallPort	The TCP port for a proxy-based firewall.
FirewallUser	The user name to use to authenticate with a proxy-based firewall.
FirewallPassword	A password used to authenticate to a proxy-based firewall.

Proxy

Property	Description
ProxyAutoDetect	This indicates whether to use the system proxy settings or not. This takes precedence over other proxy settings, so you'll need to set ProxyAutoDetect to FALSE in order use custom proxy settings.
ProxyServer	The hostname or IP address of a proxy to route HTTP traffic through.
ProxyPort	The TCP port the ProxyServer proxy is running on.
ProxyAuthScheme	The authentication type to use to authenticate to the ProxyServer proxy.
ProxyUser	A user name to be used to authenticate to the ProxyServer proxy.
ProxyPassword	A password to be used to authenticate to the ProxyServer proxy.
ProxySSLType	The SSL type to use when connecting to the ProxyServer proxy.
ProxyExceptions	A semicolon separated list of destination hostnames or IPs that are exempt from connecting through the ProxyServer .

Logging

Property	Description
LogModules	Core modules to be included in the log file.

Schema

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

Miscellaneous

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

OAuth

This section provides a complete list of the OAuth properties you can configure in the connection string for this provider.

Property	Description
OAuthClientId	The client Id assigned when you register your application with an OAuth authorization server.
OAuthClientSecret	The client secret assigned when you register your application with an OAuth authorization server.

OAuthClientId

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

Remarks

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

OAuthClientSecret

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

Remarks

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

SSL

This section provides a complete list of the SSL properties you can configure in the connection string for this provider.

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

SSLServerCert

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

Remarks

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

This property can take the following forms:


Description	Example
A full PEM Certificate (example shortened for brevity)	-----BEGIN CERTIFICATE----- MIIChTCCAe4CAQAwDQYJKoZIhv......Qw== -----END CERTIFICATE-----
A path to a local file containing the certificate	C:\cert.cer
The public key (example shortened for brevity)	-----BEGIN RSA PUBLIC KEY----- MIGfMA0GCSq......AQAB -----END RSA PUBLIC KEY-----
The MD5 Thumbprint (hex values can also be either space or colon separated)	ecadbdda5a1529c58a1e9e09828d70e4
The SHA1 Thumbprint (hex values can also be either space or colon separated)	34a929226ae0819f2ec14b4a3d904f801cbb150d

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

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

Firewall

This section provides a complete list of the Firewall properties you can configure in the connection string for this provider.

Property	Description
FirewallType	The protocol used by a proxy-based firewall.
FirewallServer	The name or IP address of a proxy-based firewall.
FirewallPort	The TCP port for a proxy-based firewall.
FirewallUser	The user name to use to authenticate with a proxy-based firewall.
FirewallPassword	A password used to authenticate to a proxy-based firewall.

FirewallType

The protocol used by a proxy-based firewall.

Remarks

This property specifies the protocol that the Sync App will use to tunnel traffic through the FirewallServer proxy. Note that by default, the Sync App connects to the system proxy; to disable this behavior and connect to one of the following proxy types, set ProxyAutoDetect to false.


Type	Default Port	Description
TUNNEL	80	When this is set, the Sync App opens a connection to Sage Business Cloud Accounting and traffic flows back and forth through the proxy.
SOCKS4	1080	When this is set, the Sync App sends data through the SOCKS 4 proxy specified by FirewallServer and FirewallPort and passes the FirewallUser value to the proxy, which determines if the connection request should be granted.
SOCKS5	1080	When this is set, the Sync App sends data through the SOCKS 5 proxy specified by FirewallServer and FirewallPort. If your proxy requires authentication, set FirewallUser and FirewallPassword to credentials the proxy recognizes.

To connect to HTTP proxies, use ProxyServer and ProxyPort. To authenticate to HTTP proxies, use ProxyAuthScheme, ProxyUser, and ProxyPassword.

FirewallServer

The name or IP address of a proxy-based firewall.

Remarks

This property specifies the IP address, DNS name, or host name of a proxy allowing traversal of a firewall. The protocol is specified by FirewallType: Use FirewallServer with this property to connect through SOCKS or do tunneling. Use ProxyServer to connect to an HTTP proxy.

Note that the Sync App uses the system proxy by default. To use a different proxy, set ProxyAutoDetect to false.

FirewallPort

The TCP port for a proxy-based firewall.

Remarks

This specifies the TCP port for a proxy allowing traversal of a firewall. Use FirewallServer to specify the name or IP address. Specify the protocol with FirewallType.

FirewallUser

The user name to use to authenticate with a proxy-based firewall.

Remarks

The FirewallUser and FirewallPassword properties are used to authenticate against the proxy specified in FirewallServer and FirewallPort, following the authentication method specified in FirewallType.

FirewallPassword

If you need to connect through a SOCKS proxy or tunnel the connection, see FirewallType.

By default, the Sync App uses the system proxy. If you need to use another proxy, set ProxyAutoDetect to false.

ProxyPort

The TCP port the ProxyServer proxy is running on.

Remarks

The port the HTTP proxy is running on that you want to redirect HTTP traffic through. Specify the HTTP proxy in ProxyServer. For other proxy types, see FirewallType.

ProxyAuthScheme

The authentication type to use to authenticate to the ProxyServer proxy.

Remarks

This value specifies the authentication type to use to authenticate to the HTTP proxy specified by ProxyServer and ProxyPort.

Note that the Sync App will use the system proxy settings by default, without further configuration needed; if you want to connect to another proxy, you will need to set ProxyAutoDetect to false, in addition to ProxyServer and ProxyPort. To authenticate, set ProxyAuthScheme and set ProxyUser and ProxyPassword, if needed.

The authentication type can be one of the following:

BASIC: The Sync App performs HTTP BASIC authentication.
DIGEST: The Sync App performs HTTP DIGEST authentication.
NEGOTIATE: The Sync App retrieves an NTLM or Kerberos token based on the applicable protocol for authentication.
PROPRIETARY: The Sync App does not generate an NTLM or Kerberos token. You must supply this token in the Authorization header of the HTTP request.

If you need to use another authentication type, such as SOCKS 5 authentication, see FirewallType.

ProxyUser

A user name to be used to authenticate to the ProxyServer proxy.

Remarks

The ProxyUser and ProxyPassword options are used to connect and authenticate against the HTTP proxy specified in ProxyServer.

You can select one of the available authentication types in ProxyAuthScheme. If you are using HTTP authentication, set this to the user name of a user recognized by the HTTP proxy. If you are using Windows or Kerberos authentication, set this property to a user name in one of the following formats:

user@domain
domain\user

ProxyPassword

A password to be used to authenticate to the ProxyServer proxy.

Remarks

This property is used to authenticate to an HTTP proxy server that supports NTLM (Windows), Kerberos, or HTTP authentication. To specify the HTTP proxy, you can set ProxyServer and ProxyPort. To specify the authentication type, set ProxyAuthScheme.

If you are using HTTP authentication, additionally set ProxyUser and ProxyPassword to HTTP proxy.

If you are using NTLM authentication, set ProxyUser and ProxyPassword to your Windows password. You may also need these to complete Kerberos authentication.

For SOCKS 5 authentication or tunneling, see FirewallType.

By default, the Sync App uses the system proxy. If you want to connect to another proxy, set ProxyAutoDetect to false.

ProxySSLType

The SSL type to use when connecting to the ProxyServer proxy.

Remarks

This property determines when to use SSL for the connection to an HTTP proxy specified by ProxyServer. This value can be AUTO, ALWAYS, NEVER, or TUNNEL. The applicable values are the following:


AUTO	Default setting. If the URL is an HTTPS URL, the Sync App will use the TUNNEL option. If the URL is an HTTP URL, the component will use the NEVER option.
ALWAYS	The connection is always SSL enabled.
NEVER	The connection is not SSL enabled.
TUNNEL	The connection is through a tunneling proxy. The proxy server opens a connection to the remote host and traffic flows back and forth through the proxy.

ProxyExceptions

A semicolon separated list of destination hostnames or IPs that are exempt from connecting through the ProxyServer .

Remarks

The ProxyServer is used for all addresses, except for addresses defined in this property. Use semicolons to separate entries.

Note that the Sync App uses the system proxy settings by default, without further configuration needed; if you want to explicitly configure proxy exceptions for this connection, you need to set ProxyAutoDetect = false, and configure ProxyServer and ProxyPort. To authenticate, set ProxyAuthScheme and set ProxyUser and ProxyPassword, if needed.

Logging

This section provides a complete list of the Logging properties you can configure in the connection string for this provider.

Property	Description
LogModules	Core modules to be included in the log file.

LogModules

Core modules to be included in the log file.

Remarks

Only the modules specified (separated by ';') will be included in the log file. By default all modules are included.

See the Logging page for an overview.

Schema

This section provides a complete list of the Schema properties you can configure in the connection string for this provider.

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

Location

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

Remarks

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

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

BrowsableSchemas

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

Remarks

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

Tables

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

Remarks

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

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

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

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

Views

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

Remarks

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

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

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

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

Miscellaneous

This section provides a complete list of the Miscellaneous properties you can configure in the connection string for this provider.

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

Specify multiple properties in a semicolon-separated list.

Integration and Formatting


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

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

UserDefinedViews

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

Remarks

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

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

This User Defined View configuration file is formatted as follows:

Each root element defines the name of a view.
Each root element contains a child element, called query, which contains the custom SQL query for the view.

For example:

{
	"MyView": {
		"query": "SELECT * FROM SampleTable_1 WHERE MyColumn = 'value'"
	},
	"MyView2": {
		"query": "SELECT * FROM MyTable WHERE Id IN (1,2,3)"
	}
}

Use the UserDefinedViews connection property to specify the location of your JSON configuration file. For example:

"UserDefinedViews", "C:\\Users\\yourusername\\Desktop\\tmp\\UserDefinedViews.json"

Sage Business Cloud Accounting Connector for CData Sync

Sage Business Cloud Accounting Connector for CData Sync

Overview

Establishing a Connection

Authenticating to Sage Business Cloud Accounting

OAuth

Advanced Features

User Defined Views

SSL Configuration

Firewall and Proxy

Query Processing

Logging

SSL Configuration

Customizing the SSL Configuration

Firewall and Proxy

Connecting Through a Firewall or Proxy

HTTP Proxies

Other Proxies

Data Model

Key Features

Tables

Views

Stored Procedures

Tables

Sage Business Cloud Accounting Connector for CData Sync Tables

Addresses

Table Specific Information

Select

Insert

Update

Delete

Columns

Attachments

Table Specific Information

Select

Update

Delete

Columns

BankAccounts

Table Specific Information

Select

Insert

Update

Delete

Columns

BankDeposits

Table Specific Information

Select

Delete

Columns

BankOpeningBalances

Table Specific Information

Select

Insert

Update

Delete

Columns

BankReconciliations

Table Specific Information

Select

Update

Columns

BankTransfers

Table Specific Information

Select

Insert

Update

Delete

Columns

BusinessExchangeRates

Table Specific Information

Select

Update

Delete

Columns

ContactAllocations

Table Specific Information

Select

Update

Delete