SSIS Components for Sage 50 UK

Build 24.0.9062

Contacts

Create, Update, Delete, and Query Contacts within Sage UK 50 Accounts.

Table Specific Information

The Contacts table allows you to select, insert, update, and delete contacts within a Sage 50 UK company dataset.

Select

The Contacts table returns the contacts contained within a Sage 50 UK company dataset.

SELECT * FROM Contacts WHERE TradingAccountReference = 'ABC123'

Insert

To create a new Contacts record, the TradingAccountReference and Type fields are required.


INSERT INTO Contacts (Title, FirstName, MiddleName, FamilyName, Suffix, TradingAccountReference, Type, PostalAddressAddress1, PostalAddressTownCity, PostalAddressStateRegion, PostalAddressZipPostCode, PostalAddressType, PostalAddressDescription, Phone1Text, Phone1Type, Phone2Text, Phone2Type, EmailAddress) 
VALUES (True, 'Dr.', 'INSERT', 'ME', 'NOW', 'Jr.', 'CNTCTEST', 'Customer Delivery Contact', '123 Main St', 'Durham', 'NC', '12345', 'Shipping', 'NEW Description', '123-456-7890', 'Business Phone', '555-444-1111', 'Fax', '[email protected]')

Update

Any field that is not read-only can be updated. Requirements exist for some fields of a Contact record:

  • To update an existing postal address, PostalAddressUUID is required. Otherwise if postal address is null, specify the details and leave the UUID empty.
  • To update an existing Phone1, Phone1UUID is required. Otherwise if Phone1 is empty, specify the details and leave the UUID empty.
  • To update an existing Phone2, Phone2UUID is required. Otherwise if Phone2 is empty, specify the details and leave the UUID empty.
  • To update an existing email address, EmailUUID is required. Otherwise if email is empty, specify the details and leave the UUID empty.


UPDATE Contacts SET EmailAddress = '[email protected]', Phone1Text = '999-999-9999'
WHERE ContactUUID = '96df2e1e-72c6-4b52-b39a-eeb42c840f87' AND Phone1UUID = 'bd621624-f78e-4dec-abf3-b8b94d8fe7a3' AND EmailUUID = 'a3f6ff1b-a80b-4019-ae5e-b1fa1bb4044d'

Delete

Deleting a Contact record will also delete all postal addresses, phone numbers, and emails associated with the Contact.


DELETE FROM Contacts WHERE ContactUUID = '96df2e1e-72c6-4b52-b39a-eeb42c840f87'

Columns

Name Type ReadOnly Description
ContactUUID [KEY] String True

Contact UUID

TradingAccountReference String False

Contact Trading Account Reference

Reference2 String False

Contact Reference2

Type String False

Contact Type

FullName String False

Contact Full Name. In the form: [Title] [FirstName] [MiddleName] [FamilyName] [Suffix].

Title String False

Contact Title

FirstName String False

Contact First Name

MiddleName String False

Contact Middle Name

FamilyName String False

Contact Family Name

Suffix String False

Contact Suffix

TaxReference String False

Contact Tax Reference

PrimacyIndicator Boolean False

Contact Primacy Indicator

PostalAddressUUID String True

Contact PostalAddress UUID

PostalAddressActive Boolean False

Contact PostalAddress Active Flag

PostalAddressReference String False

Contact PostalAddress Reference

PostalAddressName String False

Contact PostalAddress Name

PostalAddressDescription String False

Contact PostalAddress Description

PostalAddressAddress1 String False

Contact PostalAddress Address 1

PostalAddressAddress2 String False

Contact PostalAddress Address 2

PostalAddressTownCity String False

Contact PostalAddress TownCity

PostalAddressCounty String False

Contact PostalAddress StateRegion

PostalAddressZipPostCode String False

Contact PostalAddress ZipPost Code

PostalAddressCountry String False

Contact PostalAddress Country

PostalAddressPrimacyIndicator Boolean False

Contact PostalAddress Primacy Indicator

PostalAddressType String False

Contact PostalAddress Type

Phone1UUID String True

Contact PhoneNumber UUID

Phone1Active Boolean False

Contact PhoneNumber Active Flag

Phone1Reference String False

Contact PhoneNumber Reference

Phone1Reference2 String False

Contact PhoneNumber Reference2

Phone1Type String False

Contact PhoneNumber Type

Phone1Name String False

Contact PhoneNumber Name

Phone1Text String False

Contact PhoneNumber Text

Phone1PrimacyIndicator Boolean False

Contact PhoneNumber Primacy Indicator

Phone2UUID String True

Contact PhoneNumber UUID

Phone2Active Boolean False

Contact PhoneNumber Active Flag

Phone2Reference String False

Contact PhoneNumber reference

Phone2Reference2 String False

Contact PhoneNumber Reference2

Phone2Type String False

Contact PhoneNumber Type

Phone2Name String False

Contact PhoneNumber Name

Phone2Text String False

Contact PhoneNumber Text

Phone2PrimacyIndicator Boolean False

Contact PhoneNumber Primacy Indicator

EmailUUID String True

Contact Email UUID

EmailActive Boolean False

Contact Email Active Flag

EmailReference String False

Contact Email Reference

EmailReference2 String False

Contact Email Reference

EmailType String False

Contact Email Type

EmailAddress String False

Contact Email Address

EmailUrl String False

Contact Email mailto:URL

EmailPrimacyIndicator Boolean False

Contact Email Primacy Indicator

EmailLabel String False

Contact Email Label

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name Type Description
Updated Datetime

An input used internally to filter records updated at a specific datetime.

Copyright (c) 2024 CData Software, Inc. - All rights reserved.
Build 24.0.9062