接続の確立

Sync App アプリケーションの接続ページに移動し、接続の追加 パネルで対応するアイコンを選択して、Sage Business Cloud Accounting への接続を作成します。Sage Business Cloud Accounting アイコンが利用できない場合は、Add More アイコンをクリックしてCData サイトからSage Business Cloud Accounting コネクタをダウンロードおよびインストールします。

必須プロパティは［設定］タブにリストされています。［Advanced］タブには、通常は必要ない接続プロパティが表示されます。

Sage Business Cloud Accounting への認証

Sage Business Cloud Accounting はユーザー認証にOAuth 標準を使用しています。

OAuth

認証方法については、OAuth 認証の使用を参照してください。

高度な機能

このセクションでは、Sage Business Cloud Accounting Sync App の高度な機能を厳選して説明します。

ユーザー定義ビュー

Sync App を使用すると、事前設定されたクエリによって内容が決定されるユーザー定義ビューと呼ばれる仮想テーブルを定義できます。このビューは、ドライバーに発行されるクエリを直接制御できない場合に有効です。カスタムビューの作成と設定の概要については、ユーザー定義ビューを参照してください。

SSL の設定

SSL の設定を使用して、Sync App が証明書のネゴシエーションをどのように扱うかを調整します。さまざまな証明書形式を選択できます。詳しくは、接続文字列オプションにあるSSLServerCert プロパティを参照してください。

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

Windows プロキシとHTTP プロキシを含むファイアウォールとプロキシに合致するようSync App を設定します。トンネル接続を設定することもできます。

クエリ処理

Sync App は、Sage Business Cloud Accounting にできるだけ多くのSELECT ステートメント処理をオフロードし、残りのクエリをクライアント側のインメモリで処理します。

詳しくはクエリ処理を参照してください。

ログ

CData ログを調整するために使用可能な設定の概要については、ログを参照してください。基本的なロギングでは、次の2つの接続プロパティを設定するだけです。LogModules 接続プロパティを使用してログに記録する情報のサブセットを選択できる、より洗練されたロギングをサポートする多数の機能があります。

SSL の設定

SSL 設定のカスタマイズ

デフォルトでは、Sync App はサーバーの証明書をシステムの信頼できる証明書ストアと照合して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 の実行について詳しく説明します。

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

主要機能

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

テーブル

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

ビュー

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

ストアドプロシージャ

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

Sync App は、Sage Business Cloud Accounting API にできるだけ多くのSELECT 構文処理をオフロードし、残りのクエリをインメモリで処理します。 Sync App がクライアントサイドのインメモリ処理でAPI 制限を回避する方法の詳細については、SupportEnhancedSQL を参照してください。

テーブル

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

一般的には、Sage Business Cloud Accounting テーブルのクエリは、リレーショナルデータベースのテーブルのクエリと同じです。時には特別なケースもあります。例えば、テーブルの特定のカラムデータを取得するために特定のカラムをWHERE 句に含める必要がある場合などです。これは通常、特定のカラムを取得するために行ごとに個別のリクエストを行う必要がある場合に必要です。これらの特別な状況は、以下にリンクされているテーブルページの上部に明確に文書化されています。

Sage Business Cloud Accounting Connector for CData Sync テーブル


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

ビュー

ビューは、カラムと疑似カラムで構成されます。ビューは、データを示すという点でテーブルに似ていますが、ビューでは更新はサポートされません。通常、ビューとして表されるエンティティは、読み取り専用のエンティティです。多くの場合、これらのデータはストアドプロシージャを使用することで更新できます（その機能がデータソースに適用できる場合）。

ビューに対しては、通常のテーブルと同じようにクエリを実行でき、このときに返されるデータも同様です。

ビューとして公開されるクエリなどの動的ビューや、project_team ワークアイテムの特定の組み合わせを検索するためのビューがサポートされています。

Sage Business Cloud Accounting Connector for CData Sync ビュー


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

接続文字列オプション

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

接続を確立する方法について詳しくは、接続の確立を参照してください。

OAuth

プロパティ	説明
OAuthClientId	OAuth 認証サーバーを使用してアプリケーションを登録する場合に割り当てられたクライアントId。
OAuthClientSecret	OAuth 認証サーバーにアプリケーションを登録する場合に割り当てられたクライアントシークレット。

SSL

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

Firewall

プロパティ	説明
FirewallType	プロキシベースのファイアウォールで使われるプロトコル。
FirewallServer	プロキシベースのファイアウォールの名前もしくはIP アドレス。
FirewallPort	プロキシベースのファイアウォールのTCP ポート。
FirewallUser	プロキシベースのファイアウォールに認証するために使うユーザー名。
FirewallPassword	プロキシベースのファイアウォールへの認証に使われるパスワード。

Proxy

プロパティ	説明
ProxyAutoDetect	これは、システムプロキシ設定を使用するかどうかを示します。これは他のプロキシ設定よりも優先されるため、カスタムプロキシ設定を使用するにはProxyAutoDetect をFALSE に設定する必要があります。
ProxyServer	HTTP トラフィックをルートするためのプロキシのホストネームもしくはIP アドレス。
ProxyPort	ProxyServer プロキシが起動しているTCP ポート。
ProxyAuthScheme	ProxyServer プロキシへの認証で使われる認証タイプ。
ProxyUser	ProxyServer プロキシへの認証に使われるユーザー名。
ProxyPassword	ProxyServer プロキシへの認証に使われるパスワード。
ProxySSLType	ProxyServer プロキシへの接続時に使用するSSL タイプ。
ProxyExceptions	ProxyServer 経由での接続が免除される宛先ホスト名またはIP のセミコロン区切りのリスト。

Logging

プロパティ	説明
LogModules	ログファイルに含めるコアモジュール。

Schema

プロパティ	説明
Location	テーブル、ビュー、およびストアドプロシージャを定義するスキーマファイルを格納するディレクトリへのパス。
BrowsableSchemas	このプロパティは、使用可能なスキーマのサブセットにレポートされるスキーマを制限します。例えば、BrowsableSchemas=SchemaA,SchemaB,SchemaC です。
Tables	このプロパティは、使用可能なテーブルのサブセットにレポートされるテーブルを制限します。例えば、Tables=TableA,TableB,TableC です。
Views	使用可能なテーブルのサブセットにレポートされるビューを制限します。例えば、Views=ViewA,ViewB,ViewC です。

Miscellaneous

プロパティ	説明
BusinessId	The ID of your Bussiness.
MaxRows	クエリで集計またはグループ化を使用しない場合に返される行数を制限します。これにより、設計時にパフォーマンスの問題を回避できます。
Other	これらの隠しプロパティは特定のユースケースでのみ使用されます。
Pagesize	Sage Business Cloud Accounting から返されるページあたりの結果の最大数。
PseudoColumns	このプロパティは、テーブルのカラムとして疑似カラムが含まれているかどうかを示します。
Timeout	タイムアウトエラーがスローされ、処理をキャンセルするまでの秒数。
UserDefinedViews	カスタムビューを含むJSON コンフィギュレーションファイルを指すファイルパス。

OAuth

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

プロパティ	説明
OAuthClientId	OAuth 認証サーバーを使用してアプリケーションを登録する場合に割り当てられたクライアントId。
OAuthClientSecret	OAuth 認証サーバーにアプリケーションを登録する場合に割り当てられたクライアントシークレット。

OAuthClientId

OAuth 認証サーバーを使用してアプリケーションを登録する場合に割り当てられたクライアントId。

解説

OAuth アプリケーションの登録の一環として、コンシューマキーとも呼ばれるOAuthClientId 値、およびクライアントシークレットOAuthClientSecret が提供されます。

OAuthClientSecret

OAuth 認証サーバーにアプリケーションを登録する場合に割り当てられたクライアントシークレット。

解説

OAuth アプリケーションの登録の一環として、コンシューマキーとも呼ばれるOAuthClientId が提供されます。また、コンシューマーシークレットと呼ばれるクライアントシークレットも提供されます。クライアントシークレットをOAuthClientSecret プロパティに設定します。

SSL

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

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

SSLServerCert

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

解説

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

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

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

Firewall

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

プロパティ	説明
FirewallType	プロキシベースのファイアウォールで使われるプロトコル。
FirewallServer	プロキシベースのファイアウォールの名前もしくはIP アドレス。
FirewallPort	プロキシベースのファイアウォールのTCP ポート。
FirewallUser	プロキシベースのファイアウォールに認証するために使うユーザー名。
FirewallPassword	プロキシベースのファイアウォールへの認証に使われるパスワード。

FirewallType

プロキシベースのファイアウォールで使われるプロトコル。

解説

このプロパティは、Sync App がFirewallServer プロキシ経由でトンネルトラフィックを使うためのプロトコルを指定します。デフォルトでは、Sync App はシステムプロキシに接続します。この動作を無効化し次のプロキシタイプのどれかで接続するには、ProxyAutoDetect をfalse に設定します。


タイプ	デフォルトポート	説明
TUNNEL	80	これが設定されている場合、Sync App はSage Business Cloud Accounting への接続を開き、プロキシを経由して通信が行われます。
SOCKS4	1080	これが設定されている場合、Sync App はデータをFirewallServer およびFirewallPort で指定されたSOCS 4 プロキシ経由で送信し、接続リクエストが許容されるかどうかを決定します。
SOCKS5	1080	これが設定されている場合、Sync App はデータをFirewallServer およびFirewallPort で指定されたSOCS 5 プロキシ経由で送信します。プロキシに認証が必要な場合には、FirewallUser およびFirewallPassword をプロキシが認識する認証情報に設定します。

HTTP プロキシへの接続には、ProxyServer およびProxyPort ポートを使ってください。HTTP プロキシへの認証には、ProxyAuthScheme、ProxyUser、およびProxyPassword を使ってください。

FirewallServer

プロキシベースのファイアウォールの名前もしくはIP アドレス。

解説

ファイアウォールトラバーサルを許容するために設定するIP アドレス、DNS 名、もしくはプロキシホスト名を指定するプロパティです。プロトコルはFirewallType で指定されます。このプロパティとFirewallServer を使って、SOCKS 経由での接続、もしくはトンネリングが可能です。HTTP プロキシへの接続には、ProxyServer を使用します。

Sync App はデフォルトでシステムプロキシを使うので注意してください。他のプロキシを使う場合には、ProxyAutoDetect をfalse に設定してください。

FirewallPort

プロキシベースのファイアウォールのTCP ポート。

解説

ファイアウォールトラバーサルを許容するために設定するプロキシベースのファイアウォールのTCP ポート。名前もしくはIP アドレスを指定するには、FirewallServer を使います。FirewallType でプロトコルを指定します。

FirewallUser

プロキシベースのファイアウォールに認証するために使うユーザー名。

解説

FirewallUser およびFirewallPassword プロパティは、FirewallType により指定された認証方式に則り、FirewallServer、およびFirewallPort で指定されたプロキシに対しての認証に使われます。

FirewallPassword

SOCKS プロキシを経由して接続する、もしくは接続をトンネルするには、FirewallType を参照してください。

デフォルトで、Sync App はsystem プロキシを使います。他のプロキシを使う場合には、ProxyAutoDetect をfalse に設定します。

ProxyPort

ProxyServer プロキシが起動しているTCP ポート。

解説

HTTP トラフィックをリダイレクトするHTTP プロキシが実行されているポート。ProxyServer でHTTP プロキシを指定します。その他のプロキシタイプについては、FirewallType を参照してください。

ProxyAuthScheme

ProxyServer プロキシへの認証で使われる認証タイプ。

解説

この値は、ProxyServer およびProxyPort で指定されるHTTP プロキシに認証するために使われる認証タイプを指定します。

Sync App は、デフォルトでsystem proxy settings を使い、追加での設定が不要です。他のプロキシへの接続をする場合には、ProxyServer およびProxyPort に加え、ProxyAutoDetect をfalse に設定します。認証するには、ProxyAuthScheme を設定し、必要な場合にはProxyUser およびProxyPassword を設定します。

認証タイプは、次のどれかになります。

BASIC： Sync App はHTTP BASIC 認証を行います。
DIGEST： Sync App はHTTP DIGEST 認証を行います。
NEGOTIATE： Sync App は認証において有効なプロトコルに応じて、NTLM もしくはKereros トークンを取得します。
PROPRIETARY： Sync App はNTLM もしくはKerberos トークンを発行しません。このトークンを、HTTP リクエストのAuthorization ヘッダーに含める必要があります。

SOCKS 5 認証のような他の認証タイプを使用するには、FirewallType を参照してください。

ProxyUser

ProxyServer プロキシへの認証に使われるユーザー名。

解説

ProxyUser および ProxyPassword オプションは、ProxyServer で指定されたHTTP プロキシに対して接続および認証するために使用されます。

ProxyAuthScheme で使用可能な認証タイプを選択することができます。HTTP 認証を使う場合、これをHTTP プロキシで識別可能なユーザーのユーザー名に設定します。Windows もしくはKerberos 認証を使用する場合、このプロパティを次の形式のどれかでユーザー名に設定します。

user@domain
domain\user

ProxyPassword

ProxyServer プロキシへの認証に使われるパスワード。

解説

このプロパティは、NTLM(Windows)、Kerberos、もしくはHTTP 認証をサポートするHTTP プロキシサーバーに認証するために使われます。HTTP プロキシを指定するためには、ProxyServer およびProxyPort を設定します。認証タイプを指定するためにはProxyAuthScheme を設定します。

HTTP 認証を使う場合、さらにHTTP プロキシにProxyUser およびProxyPassword を設定します。

NTLM 認証を使う場合、Windows パスワードにProxyUser およびProxyPassword を設定します。Kerberos 認証には、これらを入力する必要があります。

SOCKS 5 認証もしくは、トンネリングは、FirewallType を参照してください。

デフォルトで、Sync App はsystem プロキシを使います。他のプロキシに接続する場合には、これをfalse に設定します。

ProxySSLType

ProxyServer プロキシへの接続時に使用するSSL タイプ。

解説

このプロパティは、ProxyServer で指定されたHTTP プロキシへの接続にSSL を使用するかどうかを決定します。この値は、AUTO、ALWAYS、NEVER、TUNNEL のいずれかです。有効な値は次のとおりです。


AUTO	デフォルト設定。URL がHTTPS URL の場合、Sync App は、TUNNEL オプションを使います。URL がHTTP URL の場合、コンポーネントはNEVER オプションを使います。
ALWAYS	接続は、常にSSL 有効となります。
NEVER	接続は、SSL 有効になりません。
TUNNEL	接続は、トンネリングプロキシを経由します。プロキシサーバーがリモートホストへの接続を開き、プロキシを経由して通信が行われます。

ProxyExceptions

ProxyServer 経由での接続が免除される宛先ホスト名またはIP のセミコロン区切りのリスト。

解説

ProxyServer は、このプロパティで定義されたアドレスを除くすべてのアドレスに使用されます。セミコロンを使用してエントリを区切ります。

Sync App は、追加設定なしにデフォルトでシステムのプロキシ設定を使います。この接続のプロキシ例外を明示的に構成するには、ProxyAutoDetect をfalse に設定して、ProxyServer およびProxyPort を設定する必要があります。認証するには、ProxyAuthScheme を設定し、必要な場合にはProxyUser およびProxyPassword を設定します。

Logging

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

プロパティ	説明
LogModules	ログファイルに含めるコアモジュール。

LogModules

ログファイルに含めるコアモジュール。

解説

指定された（';' で区切られた）モジュールのみがログファイルに含まれます。デフォルトではすべてのモジュールが含まれます。

概要はログページを参照してください。

Schema

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

プロパティ	説明
Location	テーブル、ビュー、およびストアドプロシージャを定義するスキーマファイルを格納するディレクトリへのパス。
BrowsableSchemas	このプロパティは、使用可能なスキーマのサブセットにレポートされるスキーマを制限します。例えば、BrowsableSchemas=SchemaA,SchemaB,SchemaC です。
Tables	このプロパティは、使用可能なテーブルのサブセットにレポートされるテーブルを制限します。例えば、Tables=TableA,TableB,TableC です。
Views	使用可能なテーブルのサブセットにレポートされるビューを制限します。例えば、Views=ViewA,ViewB,ViewC です。

Location

テーブル、ビュー、およびストアドプロシージャを定義するスキーマファイルを格納するディレクトリへのパス。

解説

Sync App のスキーマファイル（テーブルとビューの場合は.rsd ファイル、ストアドプロシージャの場合は.rsb ファイル）を含むディレクトリへのパス。このフォルダの場所は、実行ファイルの場所からの相対パスにすることができます。Location プロパティは、定義をカスタマイズしたり（例えば、カラム名を変更する、カラムを無視するなど）、新しいテーブル、ビュー、またはストアドプロシージャでデータモデルを拡張する場合にのみ必要です。

指定しない場合、デフォルトの場所は"%APPDATA%\\CData\\SageBCAccounting Data Provider\\Schema" となり、%APPDATA% はユーザーのコンフィギュレーションディレクトリに設定されます：

BrowsableSchemas

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

解説

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

Tables

このプロパティは、使用可能なテーブルのサブセットにレポートされるテーブルを制限します。例えば、Tables=TableA,TableB,TableC です。

解説

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

このプロパティは、作業したいビューがすでにわかっていて、ビューが多すぎる場合に、ビューを自動的にリストする代わりに使用することもできます。

カンマ区切りのリストで使用したいテーブルを指定します。各テーブルは、角かっこ、二重引用符、またはバッククオートを使用してエスケープされた特殊文字列を含む有効なSQL 識別子である必要があります。例えば、Tables=TableA,[TableB/WithSlash],WithCatalog.WithSchema.`TableC With Space` です。

複数のスキーマまたはカタログを持つデータソースに接続する場合は、複数のカタログやスキーマに存在するテーブル間の曖昧さを避けるため、最後の例のように、このプロパティにテーブルの完全修飾名を指定する必要があることに注意してください。

Views

使用可能なテーブルのサブセットにレポートされるビューを制限します。例えば、Views=ViewA,ViewB,ViewC です。

解説

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

このプロパティは、作業したいビューがすでにわかっていて、ビューが多すぎる場合に、ビューを自動的にリストする代わりに使用することもできます。

カンマ区切りのリストで使用したいビューを指定します。各ビューは、角かっこ、二重引用符、またはバッククオートを使用してエスケープされた特殊文字列を含む有効なSQL 識別子である必要があります。例えば、Views=ViewA,[ViewB/WithSlash],WithCatalog.WithSchema.`ViewC With Space` です。

複数のスキーマまたはカタログを持つデータソースに接続する場合は、複数のカタログやスキーマに存在するテーブル間の曖昧さを避けるため、最後の例のように、このプロパティにテーブルの完全修飾名を指定する必要があることに注意してください。

Miscellaneous

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

プロパティ	説明
BusinessId	The ID of your Bussiness.
MaxRows	クエリで集計またはグループ化を使用しない場合に返される行数を制限します。これにより、設計時にパフォーマンスの問題を回避できます。
Other	これらの隠しプロパティは特定のユースケースでのみ使用されます。
Pagesize	Sage Business Cloud Accounting から返されるページあたりの結果の最大数。
PseudoColumns	このプロパティは、テーブルのカラムとして疑似カラムが含まれているかどうかを示します。
Timeout	タイムアウトエラーがスローされ、処理をキャンセルするまでの秒数。
UserDefinedViews	カスタムビューを含むJSON コンフィギュレーションファイルを指すファイルパス。

複数のプロパティをセミコロン区切りリストで指定します。

統合およびフォーマット


DefaultColumnSize	データソースがメタデータにカラムの長さを提供しない場合に、文字列フィールドのデフォルトの長さを設定します。デフォルト値は2000です。
ConvertDateTimeToGMT	日時の値を、マシンのローカルタイムではなくGMT グリニッジ標準時に変換するかどうかを決定します。
RecordToFile=filename	基底のソケットデータ転送を指定のファイルに記録します。

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

UserDefinedViews

カスタムビューを含むJSON コンフィギュレーションファイルを指すファイルパス。

解説

ユーザー定義ビューは、UserDefinedViews.json というJSON 形式のコンフィギュレーションファイルで定義されています。Sync App は、このファイルで指定されたビューを自動的に検出します。

また、複数のビュー定義を持ち、UserDefinedViews 接続プロパティを使用して制御することも可能です。このプロパティを使用すると、指定されたビューのみがSync App によって検知されます。

このユーザー定義ビューのコンフィギュレーションファイルは、次のようにフォーマットされています。

各ルートエレメントはビューの名前を定義します。
各ルートエレメントには、query と呼ばれる子エレメントが含まれており、この子エレメントにはビューのカスタムSQL クエリが含まれています。

次に例を示します。

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

UserDefinedViews 接続プロパティを使用して、JSON コンフィギュレーションファイルの場所を指定します。次に例を示します。

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