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.
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).

JDBC Driver for Sage 200

Customers

Table Specific Information

Select

Columns