Power BI Connector for Sage 200

Build 20.0.7587

Customers

Customers are one of the most important entities within Sage 200 as they are associated with many important resources within the application and underpin most of the main features (e.g. sales orders, payment receipts, etc). When making larger requests for customer information, the Customer Views resource can be a faster alternative to this resource.

Table Specific Information

Select

The driver uses the Sage 200c Standard Online API to process search criteria. All filters are processed server side except: CountryCode,CountryCodeName,CountryCodeEuMember,MainAddress_1,MainAddress_2,MainAddress_3,MainAddress_4,MainAddressCity,MainAddressCounty,MainAddressPostcode,Contacts columns.

Supported operators are: =, < , <= , > , >= , != , AND, LIKE , IS NULL , IS NOT NULL.

For example, the following queries are processed server side:

SELECT * FROM Customers WHERE Id=27825
SELECT * FROM Customers WHERE Reference='A1D001'
SELECT * FROM Customers WHERE Name='A1 Design Services'
SELECT * FROM Customers WHERE ShortName='A1 Desig'
SELECT * FROM Customers WHERE Balance=1120.92
SELECT * FROM Customers WHERE OnHold=false
SELECT * FROM Customers WHERE AccountStatusType='AccountStatusActive'
SELECT * FROM Customers WHERE CurrencyId=2103
SELECT * FROM Customers WHERE ExchangeRateType='ExchangeRateSingle'
SELECT * FROM Customers WHERE TelephoneCountryCode='44'
SELECT * FROM Customers WHERE TelephoneAreaCode='01742'
SELECT * FROM Customers WHERE TelephoneSubscriberNumber='876234'
SELECT * FROM Customers WHERE FaxCountryCode='44'
SELECT * FROM Customers WHERE FaxAreaCode='01742'
SELECT * FROM Customers WHERE FaxSubscriberNumber='876236'
SELECT * FROM Customers WHERE Website='www.sage.co.uk'
SELECT * FROM Customers WHERE CreditLimit=0
SELECT * FROM Customers WHERE DefaultTaxCodeId=1729
SELECT * FROM Customers WHERE VatNumber='GB238 3839 38'
SELECT * FROM Customers WHERE DunsCode='abc'
SELECT * FROM Customers WHERE AnalysisCode1='Trade'
SELECT * FROM Customers WHERE AnalysisCode2='George'
SELECT * FROM Customers WHERE AnalysisCode3='Lancashire'
SELECT * FROM Customers WHERE AnalysisCode4='abc'
SELECT * FROM Customers WHERE AnalysisCode5='abc'
SELECT * FROM Customers WHERE AverageTimeToPay=0
SELECT * FROM Customers WHERE ValueOfCurrentOrdersInSop=340.38
SELECT * FROM Customers WHERE CountryCodeId=13
SELECT * FROM Customers WHERE DateTimeUpdated='2018-03-05T11:19:18.317+01:00'

Columns

Name Type References Description
Id [KEY] Long Customer unique Id.
Reference String Customer account reference. NOTE: Not required if customer reference is set to
Name String Customer name.
ShortName String Customer short name.
Balance Decimal Customer account balance.
OnHold Bool True if customer account is on hold, else False.
AccountStatusType String The status of the customer account (Sage 200c Standard and versions of Extra/Professional released after July 2017). For example, this can either be 'Active' or 'Hidden'. See account_status_types
CurrencyId Long

Currencies.Id

Currency record Id. See currencies
ExchangeRateType String The type of exchange rate used on the customer account. See exchange_rate_types
TelephoneCountryCode String Telephone country code (Sage 200c Professional and Sage 200 Extra Online Only).
TelephoneAreaCode String Telephone area code (Sage 200c Professional and Sage 200 Extra Online Only).
TelephoneSubscriberNumber String Telephone subscriber number (Sage 200c Professional and Sage 200 Extra Online Only).
FaxCountryCode String Fax country code.
FaxAreaCode String Fax area code.
FaxSubscriberNumber String Fax subscriber number.
Website String Website address.
CreditLimit Decimal Credit limit for the customer.
DefaultTaxCodeId Long

TaxCodes.Id

Default tax code record Id. See tax_codes
VatNumber String VAT registration number.
DunsCode String DUNS number.
AnalysisCode1 String Analysis code 1.
AnalysisCode2 String Analysis code 2.
AnalysisCode3 String Analysis code 3.
AnalysisCode4 String Analysis code 4.
AnalysisCode5 String Analysis code 5.
AnalysisCode6 String Analysis code 6.
AnalysisCode7 String Analysis code 7.
AnalysisCode8 String Analysis code 8.
AnalysisCode9 String Analysis code 9.
AnalysisCode10 String Analysis code 10.
AnalysisCode11 String Analysis code 11.
AnalysisCode12 String Analysis code 12.
AnalysisCode13 String Analysis code 13.
AnalysisCode14 String Analysis code 14.
AnalysisCode15 String Analysis code 15.
AnalysisCode16 String Analysis code 16.
AnalysisCode17 String Analysis code 17.
AnalysisCode18 String Analysis code 18.
AnalysisCode19 String Analysis code 19.
AnalysisCode20 String Analysis code 20.
AverageTimeToPay Int The customer average time to pay.
ValueOfCurrentOrdersInSop Decimal Value of current sales orders for this customer.
CountryCodeId Long Country code record Id.
CountryCode String The customers country code.
CountryCodeName String The customers country code name.
CountryCodeEuMember Boolean Shows if the customer is Eu Member.
MainAddress_1 String The customers main address line 1.
MainAddress_2 String The customers main address line 2.
MainAddress_3 String The customers main address line 3.
MainAddress_4 String The customers main address line 4.
MainAddressCity String The customers main address city.
MainAddressCounty String The customers main address county.
MainAddressPostcode String The customers main address postcode.
Contacts String Customer contacts. See customer_contacts
DateTimeUpdated Datetime The date and time this entity was last updated (UTC).

Copyright (c) 2020 CData Software, Inc. - All rights reserved.
Build 20.0.7587