Power BI Connector for Sage Business Cloud Accounting

Build 22.0.8342

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.

Copyright (c) 2022 CData Software, Inc. - All rights reserved.
Build 22.0.8342