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