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.

Cmdlets for Sage 50 UK