はじめに

このページでは、CData Cloud でのSage Business Cloud Accounting への接続の確立のガイド、利用可能なリソースに関する情報、および使用可能な接続プロパティのリファレンスについて説明します。

Sage Business Cloud Accounting への接続

接続の確立は、CData Cloud にデータベースを作成するためのSage Business Cloud Accounting への認証方法と必要な接続プロパティの設定方法について示します。

CData Cloud サービスからデータにアクセス

利用可能な標準サービスを経由してSage Business Cloud Accounting からデータにアクセスする方法と、CData Cloud の管理については、CData Cloud ドキュメントで詳しく説明します。

接続の確立

Database タブで対応するアイコンを選択して、Sage Business Cloud Accounting に接続します。必須プロパティはSettings にリストされています。Advanced タブには、通常は必要ない接続プロパティが表示されます。

Sage Business Cloud Accounting への認証

Sage Business Cloud Accounting は埋め込みOAuth クレデンシャルを提供しており、デスクトップアプリケーションからの接続を簡単にします。Web アプリケーションから接続するには、カスタムOAuth アプリケーションの作成で説明するようにカスタムOAuth アプリケーションを作成する必要があります。

すべての認証フローで、OAuth 経由で接続するにはAuthScheme をOAuth に設定する必要があります。

以下のサブセクションでは、利用可能なOAuth フローでのSage Business Cloud Accounting への認証について詳しく説明します。カスタムOAuth アプリケーションの作成についての情報と、すでに埋め込みOAuth 認証情報を持つ認証フローでもカスタムOAuth アプリケーションを作成したほうがよい場合の説明については、カスタムOAuth アプリケーションの作成を参照してください。

Sage Business Cloud Accounting で利用可能な接続文字列プロパティの全リストは、Connection を参照してください。

アクセストークンの期限が切れたときは、Cloud は自動でアクセストークンをリフレッシュします。

カスタムOAuth アプリケーションの作成

CData はOAuth アプリケーション認証情報を製品に組み込んでおり、デスクトップアプリケーションまたはヘッドレスマシン経由でSage Business Cloud Accounting に接続する際に使用できます。これら2つの認証フローのいずれかから埋め込みOAuth アプリケーションを使用する場合、接続するために必要なことは以下のみです。

AuthScheme をOAuth に設定し、
OAuthAccessToken を取得して設定し、
必要な設定パラメータを設定します。

（OAuthAccessToken およびその他の設定パラメータを取得および設定する方法についての情報は、「Sage Business Cloud Accounting への接続」を参照してください。）

ただし、Web 経由で接続するには、カスタムOAuth アプリケーションの作成が必要です。また、カスタムOAuth アプリケーションは、一般的に使用される3つの認証フローをすべてシームレスにサポートするため、これらの認証フロー用にカスタムOAuth アプリケーションを作成（独自のOAuth アプリケーションクレデンシャルを使用）することもできます。

カスタムOAuth アプリケーションは、次のような場合に有用です。

認証ダイアログのブランディングをコントロールしたいとき
ユーザー認証後にアプリケーションがユーザーをリダイレクトするためのリダイレクトURI をコントロールしたいとき
ユーザーからのリクエストに対する許可をカスタマイズしたいとき

手順

以下の手順では、カスタムOAuth アプリケーションを作成してSage Business Cloud Accounting Developer アカウントに登録し、OAuth クライアントクレデンシャルを取得します。

Sage Business Cloud Accounting Developer Account にログインします。
新しいアプリケーションを作成します。
- デスクトップアプリケーションを開発している場合は、CallbackURL をhttp://localhost:3333 または他の同様のhttp URL に設定します。
- Web サービスを開発している場合は、CallbackUrl をWeb サービスに受信させたいリダイレクトURI に設定します。

カスタムアプリケーションが作成されると、Developer ポータルにOAuthClientId （Client Id）およびOAuthClientSecret（Client Secret）が表示されます。後で使用するために、これらを記録しておきます。

SSL の設定

SSL 設定のカスタマイズ

デフォルトでは、Cloud はサーバーの証明書をシステムの信頼できる証明書ストアと照合してSSL / TLS のネゴシエーションを試みます。

別の証明書を指定するには、利用可能なフォーマットについてSSLServerCert プロパティを参照してください。

ファイアウォールとプロキシ

Firewall またはProxy 経由の接続

HTTP プロキシ

Windows のシステムプロキシ経由の接続では、接続プロパティを追加で設定する必要はありません。他のプロキシに接続するには、ProxyAutoDetect をfalse に設定します。

さらにHTTP プロキシへの認証には、ProxyServer とProxyPort に加えてProxyAuthScheme、ProxyUser、およびProxyPassword を設定します。

その他のプロキシ

次のプロパティを設定します。

プロキシベースのファイヤーウォールを使用するには、FirewallType、FirewallServer、およびFirewallPort を設定します。
接続をトンネルするには、FirewallType をTUNNEL に設定します。
認証するには、FirewallUser とFirewallPassword を設定します。
SOCKS プロキシへの認証には、さらにFirewallType をSOCKS5 に設定します。

データモデル

このセクションでは、利用可能なAPI オブジェクトを示し、Sage Business Cloud Accounting API へのSQL の実行について詳しく説明します。

ほとんどのデータベースオブジェクトのスキーマは、シンプルなテキストベースのコンフィギュレーションファイルで定義されます。

主要機能

Cloud はBankAccounts、Contacts、Journals などのSage Business Cloud Accounting エンティティをリレーショナルテーブルやビューとしてモデル化し、SQL を記述してSage Business Cloud Accounting データを選択、挿入、更新、および削除できるようにします。
ストアドプロシージャを使用すると、アクセストークンの取得やOAuth 2.0でのリフレッシュの維持など、Sage Business Cloud Accounting の操作を実行できます。
これらのオブジェクトへのライブ接続により、Cloud を使用するとSage Business Cloud Accounting アカウントへのあらゆる変更が即座に反映されます。

テーブル

ビューでは、利用可能なテーブルを説明します。テーブルは、BankAccounts、Contacts、Journals などを静的にモデル化するように定義されています。

ビュー

ビューでは、利用可能なビューを説明します。ビューは、TaxOffices、Currencies、SalesInvoiceLineItems などを静的にモデル化するように定義されています。

ストアドプロシージャ

ストアドプロシージャは、Sage Business Cloud Accounting のファンクションライクなインターフェースです。ストアドプロシージャを使用すると、オブジェクトのダウンロードやエンベロープの移動など、Sage Business Cloud Accounting の操作を実行できます。

テーブル

Cloud はSage Business Cloud Accounting のデータを、標準のSQL ステートメントを使用してクエリできるリレーショナルデータベースのテーブルのリストとしてモデル化します。

CData Cloud - Sage Business Cloud Accounting テーブル


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 使用できる値は次のとおりです。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 使用できる値は次のとおりです。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 使用できる値は次のとおりです。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		使用できる値は次のとおりです。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 使用できる値は次のとおりです。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

ビュー

ビューは、データを示すという点でテーブルに似ていますが、ビューは読み取り専用です。

クエリは、ビューに対して通常のテーブルと同様に実行することができます。

CData Cloud - Sage Business Cloud Accounting ビュー


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 使用できる値は次のとおりです。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 使用できる値は次のとおりです。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 使用できる値は次のとおりです。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 使用できる値は次のとおりです。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 使用できる値は次のとおりです。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 使用できる値は次のとおりです。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 使用できる値は次のとおりです。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 使用できる値は次のとおりです。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 使用できる値は次のとおりです。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

ストアドプロシージャ

ストアドプロシージャはファンクションライクなインターフェースで、Sage Business Cloud Accounting の単純なSELECT/INSERT/UPDATE/DELETE 処理にとどまらずCloud の機能を拡張します。

ストアドプロシージャは、パラメータのリストを受け取り、目的の機能を実行し、プロシージャが成功したか失敗したかを示すとともにSage Business Cloud Accounting から関連するレスポンスデータを返します。

CData Cloud - Sage Business Cloud Accounting ストアドプロシージャ


Name	Description

システムテーブル

このセクションで説明されているシステムテーブルをクエリして、スキーマ情報、データソース機能に関する情報、およびバッチ操作の統計にアクセスできます。

スキーマテーブル

以下のテーブルは、Sage Business Cloud Accounting のデータベースメタデータを返します。

sys_catalogs：利用可能なデータベースをリスト。
sys_schemas：利用可能なスキーマをリスト。
sys_tables：利用可能なテーブルおよびビューをリスト。
sys_tablecolumns：利用可能なテーブルおよびビューのカラムについて説明。
sys_procedures：利用可能なストアドプロシージャについて説明。
sys_procedureparameters：ストアドプロシージャパラメータについて説明。
sys_keycolumns：主キーおよび外部キーについて説明。
sys_indexes：利用可能なインデックスについて説明。

データソーステーブル

以下のテーブルは、データソースへの接続方法およびクエリ方法についての情報を返します。

sys_connection_props：利用可能な接続プロパティについての情報を返す。
sys_sqlinfo：Cloud がデータソースにオフロードできるSELECT クエリについて説明。

クエリ情報テーブル

次のテーブルは、データ変更クエリのクエリ統計を返します。

sys_identity：バッチ処理または単一の更新に関する情報を返す。

sys_catalogs

利用可能なデータベースをリストします。

次のクエリは、接続文字列で決定されるすべてのデータベースを取得します。

SELECT * FROM sys_catalogs

Columns


Name	Type	Description
CatalogName	String	データベース名。

sys_schemas

利用可能なスキーマをリストします。

次のクエリは、すべての利用可能なスキーマを取得します。

          SELECT * FROM sys_schemas

Columns


Name	Type	Description
CatalogName	String	データベース名。
SchemaName	String	スキーマ名。

sys_tables

利用可能なテーブルをリストします。

次のクエリは、利用可能なテーブルおよびビューを取得します。

          SELECT * FROM sys_tables

Columns


Name	Type	Description
CatalogName	String	テーブルまたはビューを含むデータベース。
SchemaName	String	テーブルまたはビューを含むスキーマ。
TableName	String	テーブル名またはビュー名。
TableType	String	テーブルの種類（テーブルまたはビュー）。
Description	String	テーブルまたはビューの説明。
IsUpdateable	Boolean	テーブルが更新可能かどうか。

sys_tablecolumns

利用可能なテーブルおよびビューのカラムについて説明します。

次のクエリは、SampleTable_1 テーブルのカラムとデータ型を返します。

SELECT ColumnName, DataTypeName FROM sys_tablecolumns WHERE TableName='SampleTable_1'

Columns


Name	Type	Description
CatalogName	String	テーブルまたはビューを含むデータベースの名前。
SchemaName	String	テーブルまたはビューを含むスキーマ。
TableName	String	カラムを含むテーブルまたはビューの名前。
ColumnName	String	カラム名。
DataTypeName	String	データ型の名前。
DataType	Int32	データ型を示す整数値。この値は、実行時に環境に基づいて決定されます。
Length	Int32	カラムのストレージサイズ。
DisplaySize	Int32	指定されたカラムの通常の最大幅（文字数）。
NumericPrecision	Int32	数値データの最大桁数。文字データおよび日時データの場合は、カラムの長さ（文字数）。
NumericScale	Int32	カラムのスケール（小数点以下の桁数）。
IsNullable	Boolean	カラムがNull を含められるかどうか。
Description	String	カラムの簡単な説明。
Ordinal	Int32	カラムのシーケンスナンバー。
IsAutoIncrement	String	カラムに固定増分値が割り当てられるかどうか。
IsGeneratedColumn	String	生成されたカラムであるかどうか。
IsHidden	Boolean	カラムが非表示かどうか。
IsArray	Boolean	カラムが配列かどうか。
IsReadOnly	Boolean	カラムが読み取り専用かどうか。
IsKey	Boolean	sys_tablecolumns から返されたフィールドがテーブルの主キーであるかどうか。

sys_procedures

利用可能なストアドプロシージャをリストします。

次のクエリは、利用可能なストアドプロシージャを取得します。

          SELECT * FROM sys_procedures

Columns


Name	Type	Description
CatalogName	String	ストアドプロシージャを含むデータベース。
SchemaName	String	ストアドプロシージャを含むスキーマ。
ProcedureName	String	ストアドプロシージャの名前。
Description	String	ストアドプロシージャの説明。
ProcedureType	String	PROCEDURE やFUNCTION などのプロシージャのタイプ。

sys_procedureparameters

ストアドプロシージャパラメータについて説明します。

次のクエリは、SampleProcedure ストアドプロシージャのすべての入力パラメータについての情報を返します。

SELECT * FROM sys_procedureparameters WHERE ProcedureName='SampleProcedure' AND Direction=1 OR Direction=2

Columns


Name	Type	Description
CatalogName	String	ストアドプロシージャを含むデータベースの名前。
SchemaName	String	ストアドプロシージャを含むスキーマの名前。
ProcedureName	String	パラメータを含むストアドプロシージャの名前。
ColumnName	String	ストアドプロシージャパラメータの名前。
Direction	Int32	パラメータのタイプに対応する整数値：input (1)。input/output (2)、またはoutput(4)。input/output タイプパラメータは、入力パラメータと出力パラメータの両方になれます。
DataTypeName	String	データ型の名前。
DataType	Int32	データ型を示す整数値。この値は、実行時に環境に基づいて決定されます。
Length	Int32	文字データの場合は、許可される文字数。数値データの場合は、許可される桁数。
NumericPrecision	Int32	数値データの場合は最大精度。文字データおよび日時データの場合は、カラムの長さ（文字数）。
NumericScale	Int32	数値データの小数点以下の桁数。
IsNullable	Boolean	パラメータがNull を含められるかどうか。
IsRequired	Boolean	プロシージャの実行にパラメータが必要かどうか。
IsArray	Boolean	パラメータが配列かどうか。
Description	String	パラメータの説明。
Ordinal	Int32	パラメータのインデックス。

sys_keycolumns

主キーおよび外部キーについて説明します。

次のクエリは、SampleTable_1 テーブルの主キーを取得します。

         SELECT * FROM sys_keycolumns WHERE IsKey='True' AND TableName='SampleTable_1'

Columns


Name	Type	Description
CatalogName	String	キーを含むデータベースの名前。
SchemaName	String	キーを含むスキーマの名前。
TableName	String	キーを含むテーブルの名前。
ColumnName	String	キーカラムの名前
IsKey	Boolean	カラムがTableName フィールドで参照されるテーブル内の主キーかどうか。
IsForeignKey	Boolean	カラムがTableName フィールドで参照される外部キーかどうか。
PrimaryKeyName	String	主キーの名前。
ForeignKeyName	String	外部キーの名前。
ReferencedCatalogName	String	主キーを含むデータベース。
ReferencedSchemaName	String	主キーを含むスキーマ。
ReferencedTableName	String	主キーを含むテーブル。
ReferencedColumnName	String	主キーのカラム名。

sys_foreignkeys

外部キーについて説明します。

次のクエリは、他のテーブルを参照するすべての外部キーを取得します。

         SELECT * FROM sys_foreignkeys WHERE ForeignKeyType = 'FOREIGNKEY_TYPE_IMPORT'

カラム


名前	タイプ	説明
CatalogName	String	キーを含むデータベースの名前。
SchemaName	String	キーを含むスキーマの名前。
TableName	String	キーを含むテーブルの名前。
ColumnName	String	キーカラムの名前
PrimaryKeyName	String	主キーの名前。
ForeignKeyName	String	外部キーの名前。
ReferencedCatalogName	String	主キーを含むデータベース。
ReferencedSchemaName	String	主キーを含むスキーマ。
ReferencedTableName	String	主キーを含むテーブル。
ReferencedColumnName	String	主キーのカラム名。
ForeignKeyType	String	外部キーがインポート（他のテーブルを指す）キーかエクスポート（他のテーブルから参照される）キーかを指定します。

sys_primarykeys

主キーについて説明します。

次のクエリは、すべてのテーブルとビューから主キーを取得します。

         SELECT * FROM sys_primarykeys

Columns


Name	Type	Description
CatalogName	String	キーを含むデータベースの名前。
SchemaName	String	キーを含むスキーマの名前。
TableName	String	キーを含むテーブルの名前。
ColumnName	String	キーカラムの名前。
KeySeq	String	主キーのシーケンス番号。
KeyName	String	主キーの名前。

sys_indexes

利用可能なインデックスについて説明します。インデックスをフィルタリングすることで、より高速なクエリ応答時間でセレクティブクエリを記述できます。

次のクエリは、主キーでないすべてのインデックスを取得します。

          SELECT * FROM sys_indexes WHERE IsPrimary='false'

Columns


Name	Type	Description
CatalogName	String	インデックスを含むデータベースの名前。
SchemaName	String	インデックスを含むスキーマの名前。
TableName	String	インデックスを含むテーブルの名前。
IndexName	String	インデックス名。
ColumnName	String	インデックスに関連付けられたカラムの名前。
IsUnique	Boolean	インデックスが固有の場合はTrue。そうでない場合はFalse。
IsPrimary	Boolean	インデックスが主キーの場合はTrue。そうでない場合はFalse。
Type	Int16	インデックスタイプに対応する整数値：statistic (0)、clustered (1)、hashed (2)、またはother (3)。
SortOrder	String	並べ替え順序：A が昇順、D が降順。
OrdinalPosition	Int16	インデックスのカラムのシーケンスナンバー。

sys_connection_props

利用可能な接続プロパティと、接続文字列に設定されている接続プロパティに関する情報を返します。

このテーブルをクエリする際は、config 接続文字列を使用する必要があります。

jdbc:cdata:sagebcaccounting:config:

この接続文字列を使用すると、有効な接続がなくてもこのテーブルをクエリできます。

次のクエリは、接続文字列に設定されている、あるいはデフォルト値で設定されているすべての接続プロパティを取得します。

SELECT * FROM sys_connection_props WHERE Value <> ''

Columns


Name	Type	Description
Name	String	接続プロパティ名。
ShortDescription	String	簡単な説明。
Type	String	接続プロパティのデータ型。
Default	String	明示的に設定されていない場合のデフォルト値。
Values	String	可能な値のカンマ区切りリスト。別な値が指定されていると、検証エラーがスローされます。
Value	String	設定した値またはあらかじめ設定されたデフォルト。
Required	Boolean	プロパティが接続に必要かどうか。
Category	String	接続プロパティのカテゴリ。
IsSessionProperty	String	プロパティが、現在の接続に関する情報を保存するために使用されるセッションプロパティかどうか。
Sensitivity	String	プロパティの機密度。これは、プロパティがロギングおよび認証フォームで難読化されているかどうかを通知します。
PropertyName	String	キャメルケースの短縮形の接続プロパティ名。
Ordinal	Int32	パラメータのインデックス。
CatOrdinal	Int32	パラメータカテゴリのインデックス。
Hierarchy	String	このプロパティと一緒に設定する必要がある、関連のある依存プロパティを表示します。
Visible	Boolean	プロパティが接続UI に表示されるかどうかを通知します。
ETC	String	プロパティに関するその他のさまざまな情報。

sys_sqlinfo

Cloud がデータソースにオフロードできるSELECT クエリ処理について説明します。

SQL 構文の詳細については、SQL 準拠を参照してください。

データソースのSELECT 機能

以下はSQL 機能のサンプルデータセットです。 SELECT 機能のいくつかの側面がサポートされている場合には、カンマ区切りのリストで返されます。サポートされていない場合、カラムにはNO が入ります。


名前	説明	有効な値
AGGREGATE_FUNCTIONS	サポートされている集計関数。	AVG, COUNT, MAX, MIN, SUM, DISTINCT
COUNT	COUNT 関数がサポートされているかどうか。	YES, NO
IDENTIFIER_QUOTE_OPEN_CHAR	識別子をエスケープするための開始文字。	[
IDENTIFIER_QUOTE_CLOSE_CHAR	識別子をエスケープするための終了文字。	]
SUPPORTED_OPERATORS	サポートされているSQL 演算子。	=, >, <, >=, <=, <>, !=, LIKE, NOT LIKE, IN, NOT IN, IS NULL, IS NOT NULL, AND, OR
GROUP_BY	GROUP BY がサポートされているかどうか。サポートされている場合、どのレベルでサポートされているか。	NO, NO_RELATION, EQUALS_SELECT, SQL_GB_COLLATE
OJ_CAPABILITIES	サポートされている外部結合の種類。	NO, LEFT, RIGHT, FULL, INNER, NOT_ORDERED, ALL_COMPARISON_OPS
OUTER_JOINS	外部結合がサポートされているかどうか。	YES, NO
SUBQUERIES	サブクエリがサポートされているかどうか。サポートされていれば、どのレベルでサポートされているか。	NO, COMPARISON, EXISTS, IN, CORRELATED_SUBQUERIES, QUANTIFIED
STRING_FUNCTIONS	サポートされている文字列関数。	LENGTH, CHAR, LOCATE, REPLACE, SUBSTRING, RTRIM, LTRIM, RIGHT, LEFT, UCASE, SPACE, SOUNDEX, LCASE, CONCAT, ASCII, REPEAT, OCTET, BIT, POSITION, INSERT, TRIM, UPPER, REGEXP, LOWER, DIFFERENCE, CHARACTER, SUBSTR, STR, REVERSE, PLAN, UUIDTOSTR, TRANSLATE, TRAILING, TO, STUFF, STRTOUUID, STRING, SPLIT, SORTKEY, SIMILAR, REPLICATE, PATINDEX, LPAD, LEN, LEADING, KEY, INSTR, INSERTSTR, HTML, GRAPHICAL, CONVERT, COLLATION, CHARINDEX, BYTE
NUMERIC_FUNCTIONS	サポートされている数値関数。	ABS, ACOS, ASIN, ATAN, ATAN2, CEILING, COS, COT, EXP, FLOOR, LOG, MOD, SIGN, SIN, SQRT, TAN, PI, RAND, DEGREES, LOG10, POWER, RADIANS, ROUND, TRUNCATE
TIMEDATE_FUNCTIONS	サポートされている日付および時刻関数。	NOW, CURDATE, DAYOFMONTH, DAYOFWEEK, DAYOFYEAR, MONTH, QUARTER, WEEK, YEAR, CURTIME, HOUR, MINUTE, SECOND, TIMESTAMPADD, TIMESTAMPDIFF, DAYNAME, MONTHNAME, CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, EXTRACT
REPLICATION_SKIP_TABLES	レプリケーション中にスキップされたテーブルを示します。
REPLICATION_TIMECHECK_COLUMNS	レプリケーション中に更新判断のカラムとして使用するかどうかを、（指定された順に）チェックするカラムのリストを含む文字列の配列。
IDENTIFIER_PATTERN	識別子としてどの文字列が有効かを示す文字列値。
SUPPORT_TRANSACTION	プロバイダーが、コミットやロールバックなどのトランザクションをサポートしているかどうかを示します。	YES, NO
DIALECT	使用するSQL ダイアレクトを示します。
KEY_PROPERTIES	Uniform データベースを特定するプロパティを示します。
SUPPORTS_MULTIPLE_SCHEMAS	プロバイダー用に複数のスキームが存在するかどうかを示します。	YES, NO
SUPPORTS_MULTIPLE_CATALOGS	プロバイダー用に複数のカタログが存在するかどうかを示します。	YES, NO
DATASYNCVERSION	このドライバーにアクセスするために必要な、CData Sync のバージョン。	Standard, Starter, Professional, Enterprise
DATASYNCCATEGORY	このドライバーのCData Sync カテゴリ。	Source, Destination, Cloud Destination
SUPPORTSENHANCEDSQL	API で提供されている以上の、追加SQL 機能がサポートされているかどうか。	TRUE, FALSE
SUPPORTS_BATCH_OPERATIONS	バッチ操作がサポートされているかどうか。	YES, NO
SQL_CAP	このドライバーでサポートされているすべてのSQL 機能。	SELECT, INSERT, DELETE, UPDATE, TRANSACTIONS, ORDERBY, OAUTH, ASSIGNEDID, LIMIT, LIKE, BULKINSERT, COUNT, BULKDELETE, BULKUPDATE, GROUPBY, HAVING, AGGS, OFFSET, REPLICATE, COUNTDISTINCT, JOINS, DROP, CREATE, DISTINCT, INNERJOINS, SUBQUERIES, ALTER, MULTIPLESCHEMAS, GROUPBYNORELATION, OUTERJOINS, UNIONALL, UNION, UPSERT, GETDELETED, CROSSJOINS, GROUPBYCOLLATE, MULTIPLECATS, FULLOUTERJOIN, MERGE, JSONEXTRACT, BULKUPSERT, SUM, SUBQUERIESFULL, MIN, MAX, JOINSFULL, XMLEXTRACT, AVG, MULTISTATEMENTS, FOREIGNKEYS, CASE, LEFTJOINS, COMMAJOINS, WITH, LITERALS, RENAME, NESTEDTABLES, EXECUTE, BATCH, BASIC, INDEX
PREFERRED_CACHE_OPTIONS	使用したいcacheOptions を指定する文字列値。
ENABLE_EF_ADVANCED_QUERY	ドライバーがEntity Framework の高度なクエリをサポートしているかどうかを示します。サポートしていなければ、クエリはクライアントサイドで処理されます。	YES, NO
PSEUDO_COLUMNS	利用可能な疑似カラムを示す文字列の配列。
MERGE_ALWAYS	値がtrue であれば、CData Sync 内でMerge Model が強制的に実行されます。	TRUE, FALSE
REPLICATION_MIN_DATE_QUERY	レプリケート開始日時を返すSELECT クエリ。
REPLICATION_MIN_FUNCTION	サーバーサイドでmin を実行するために使用する式名を、プロバイダーが指定できるようになります。
REPLICATION_START_DATE	レプリケート開始日を、プロバイダーが指定できるようになります。
REPLICATION_MAX_DATE_QUERY	レプリケート終了日時を返すSELECT クエリ。
REPLICATION_MAX_FUNCTION	サーバーサイドでmax を実行するために使用する式名を、プロバイダーが指定できるようになります。
IGNORE_INTERVALS_ON_INITIAL_REPLICATE	初回のレプリケートで、レプリケートをチャンクに分割しないテーブルのリスト。
CHECKCACHE_USE_PARENTID	CheckCache 構文を親キーカラムに対して実行するかどうかを示します。	TRUE, FALSE
CREATE_SCHEMA_PROCEDURES	スキーマファイルの生成に使用できる、ストアドプロシージャを示します。

次のクエリは、WHERE 句で使用できる演算子を取得します。

SELECT * FROM sys_sqlinfo WHERE Name = 'SUPPORTED_OPERATORS'

WHERE 句では、個々のテーブルの制限や要件が異なる場合がありますので注意してください。詳しくは、データモデルセクションを参照してください。

Columns


Name	Type	Description
NAME	String	SQL 構文のコンポーネント、またはサーバー上で処理できる機能。
VALUE	String	サポートされるSQL またはSQL 構文の詳細。

sys_identity

試行された変更に関する情報を返します。

次のクエリは、バッチ処理で変更された行のId を取得します。

         SELECT * FROM sys_identity

Columns


Name	Type	Description
Id	String	データ変更処理から返された、データベース生成Id。
Batch	String	バッチの識別子。1 は単一処理。
Operation	String	バッチ内の処理の結果：INSERTED、UPDATED、またはDELETED。
Message	String	SUCCESS、またはバッチ内の更新が失敗した場合のエラーメッセージ。

接続文字列オプション

接続文字列プロパティは、接続を確立するために使用できるさまざまなオプションです。このセクションでは、本プロバイダーの接続文字列で設定できるオプションの一覧を示します。詳細については各リンクをクリックしてください。

SSL

プロパティ	説明
SSLServerCert	TLS/SSL を使用して接続するときに、サーバーが受け入れ可能な証明書。

Logging

プロパティ	説明
Verbosity	ログファイルの記述をどの程度の詳細さで記載するかを決定するverbosity レベル。

Schema

プロパティ	説明
BrowsableSchemas	このプロパティは、使用可能なスキーマのサブセットにレポートされるスキーマを制限します。例えば、BrowsableSchemas=SchemaA,SchemaB,SchemaC です。

Miscellaneous

プロパティ	説明
BusinessId	The ID of your Bussiness.
MaxRows	クエリで集計またはGROUP BY を使用しない場合に返される行数を制限します。これはLIMIT 句よりも優先されます。
Pagesize	Sage Business Cloud Accounting から返されるページあたりの結果の最大数。
PseudoColumns	このプロパティは、テーブルのカラムとして疑似カラムが含まれているかどうかを示します。
Timeout	タイムアウトエラーがスローされ、処理をキャンセルするまでの秒数。

SSL

このセクションでは、本プロバイダーの接続文字列で設定可能なSSL プロパティの全リストを提供します。

プロパティ	説明
SSLServerCert	TLS/SSL を使用して接続するときに、サーバーが受け入れ可能な証明書。

SSLServerCert

TLS/SSL を使用して接続するときに、サーバーが受け入れ可能な証明書。

データ型

string

デフォルト値

""

解説

TLS/SSL 接続を使用する場合は、このプロパティを使用して、サーバーが受け入れるTLS/SSL 証明書を指定できます。コンピュータによって信頼されていない他の証明書はすべて拒否されます。

このプロパティは、次のフォームを取ります：


説明	例
フルPEM 証明書（例では省略されています）	-----BEGIN CERTIFICATE----- MIIChTCCAe4CAQAwDQYJKoZIhv......Qw== -----END CERTIFICATE-----
証明書を保有するローカルファイルへのパス。	C:\cert.cer
公開鍵（例では省略されています）	-----BEGIN RSA PUBLIC KEY----- MIGfMA0GCSq......AQAB -----END RSA PUBLIC KEY-----
MD5 Thumbprint （hex 値はスペースおよびコロン区切り）	ecadbdda5a1529c58a1e9e09828d70e4
SHA1 Thumbprint （hex 値はスペースおよびコロン区切り）	34a929226ae0819f2ec14b4a3d904f801cbb150d

これを指定しない場合は、マシンが信用するすべての証明書が受け入れられます。

すべての証明書の受け入れを示すには、'*'を使用します。セキュリティ上の理由から、これはお勧めできません。

Logging

このセクションでは、本プロバイダーの接続文字列で設定可能なLogging プロパティの全リストを提供します。

プロパティ	説明
Verbosity	ログファイルの記述をどの程度の詳細さで記載するかを決定するverbosity レベル。

Verbosity

ログファイルの記述をどの程度の詳細さで記載するかを決定するverbosity レベル。

データ型

string

デフォルト値

"1"

解説

Verbosity レベルは、Cloud がLogfile にどの程度の詳細さで記載するかを決定します。1 から5 までのVerbosity レベルがサポートされています。より詳しい説明は、ログページに記載されています。

Schema

このセクションでは、本プロバイダーの接続文字列で設定可能なSchema プロパティの全リストを提供します。

プロパティ	説明
BrowsableSchemas	このプロパティは、使用可能なスキーマのサブセットにレポートされるスキーマを制限します。例えば、BrowsableSchemas=SchemaA,SchemaB,SchemaC です。

BrowsableSchemas

このプロパティは、使用可能なスキーマのサブセットにレポートされるスキーマを制限します。例えば、BrowsableSchemas=SchemaA,SchemaB,SchemaC です。

データ型

string

デフォルト値

""

解説

スキーマをデータベースからリストすると、負荷がかかる可能性があります。接続文字列でスキーマのリストを提供すると、パフォーマンスが向上します。

Miscellaneous

このセクションでは、本プロバイダーの接続文字列で設定可能なMiscellaneous プロパティの全リストを提供します。

プロパティ	説明
BusinessId	The ID of your Bussiness.
MaxRows	クエリで集計またはGROUP BY を使用しない場合に返される行数を制限します。これはLIMIT 句よりも優先されます。
Pagesize	Sage Business Cloud Accounting から返されるページあたりの結果の最大数。
PseudoColumns	このプロパティは、テーブルのカラムとして疑似カラムが含まれているかどうかを示します。
Timeout	タイムアウトエラーがスローされ、処理をキャンセルするまでの秒数。

Timeout の有効期限が切れても処理が完了していない場合は、Cloud は例外をスローします。

Cloud

Sage Business Cloud Accounting - CData Cloud

概要

主要機能

はじめに

Sage Business Cloud Accounting への接続

CData Cloud サービスからデータにアクセス

接続の確立

Sage Business Cloud Accounting への認証

カスタムOAuth アプリケーションの作成

カスタムOAuth アプリケーションの作成

手順

SSL の設定

SSL 設定のカスタマイズ

ファイアウォールとプロキシ

Firewall またはProxy 経由の接続

HTTP プロキシ

その他のプロキシ

データモデル

主要機能

テーブル

ビュー

ストアドプロシージャ

テーブル

CData Cloud - Sage Business Cloud Accounting テーブル

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