ContactCustomers
Return, update, create and delete a customer contact for an AccountRight company file.
Table Specific Information
Select
MYOB allows a subset of columns to be used in the WHERE clause of a SELECT query. These columns may be used with the following operators: =, !=, >, <, >=, <=. In addition, queries with multiple criterias can be executed by combining the criteria with AND and OR operators. These columns support the above comparison types for server side filtering:
ID | CurrentBalance | FirstName | LastName | CompanyName |
IsActive | IsIndividual | LastModified | Notes | DisplayID |
SellingABN | SellingABNBranch | SellingCreditAvailable | SellingCreditLimit | SellingCreditOnHold |
SellingCreditPastDue | SellingFreightTaxCodeCode | SellingFreightTaxCodeID | SellingHourlyBillingRate | SellingInvoiceDelivery |
SellingItemPriceLevel | SellingMemo | SellingPrintedForm | SellingReceiptMemo | SellingSaleComment |
SellingSaleLayout | SellingShippingMethod | SellingTaxCodeCode | SellingTaxCodeID | SellingTermsBalanceDueDate |
SellingTermsDiscountDate | SellingTermsDiscountForEarlyPayment | SellingTermsMonthlyChargeForLatePayment | SellingTermsPaymentIsDue | SellingTermsVolumeDiscount |
SellingUseCustomerTaxCode | SellingIncomeAccountId | SellingSalesPersonId | PaymentBSBNumber | PaymentBankAccountName |
PaymentBankAccountNumber |
SELECT * FROM ContactCustomers SELECT * FROM ContactCustomers WHERE ID = "1bed306d-2da4-49b3-9023-153c3dc5b2e9" SELECT * FROM ContactCustomers WHERE FirstName = "James" OR FirstName = "John"
Insert
The following attributes are required when performing an insert: CompanyName (if IsIndividual = false), FirstName, LastName, IsIndividual, SellingTaxCodeID and SellingFreightTaxCodeID.
INSERT INTO ContactCustomers (FirstName, LastName, IsIndividual, SellingTaxCodeID, SellingFreightTaxCodeID) VALUES ("James", "Bond", true, "446d2226-254b-4821-acc2-7031842e0166", "446d2226-254b-4821-acc2-7031842e0166")
Update
To update an existing ContactCustomer, along with the addresses, we can either pass a JSON string to the aggregate input value or use a temporary table like below.
INSERT INTO ContactAddressItems#TEMP (City, Country) VALUES ("Paris", "France") UPDATE ContactCustomers SET FirstName = "James", Addresses = "ContactAddressItems#TEMP" WHERE ID = "1bed306d-2da4-49b3-9023-153c3dc5b2e9"
Delete
You must specify the Id of the ContactCustomer to delete it.
DELETE FROM ContactCustomers WHERE Id = '5a3e152b-4910-4cbe-ad27-32a57392ae23'
Columns
Name | Type | ReadOnly | Description |
ID [KEY] | Uuid | True |
Unique identifier in the form of a guid. |
CurrentBalance | Decimal | True |
Contact balance. |
FirstName | String | False |
Contact first name. |
LastName | String | False |
Contact last name. |
CompanyName | String | False |
Contact company name. |
IsActive | Boolean | False |
True indicates the employee contact is active. False indicates the employee contact is inactive. |
IsIndividual | Boolean | False |
True indicates the employee contact represents an individual. False indicates the employee contact represents a company. |
LastModified | Datetime | True |
Extracts the last modification date for the contact resource. |
Notes | String | False |
Notes for the contact. |
PhotoURI | String | False |
Uniform resource identifier associated with a photo image. |
URI | String | True |
Uniform resource identifier encompasses all types of names and addresses that refer to objects on the web. |
DisplayID | String | False |
Display ID for the contact card. |
SellingABN | Sring | False |
ABN Number (Must be 11 digits and formatted as XX XXX XXX XXX). |
SellingABNBranch | String | False |
ABN branch number. |
SellingCreditAvailable | Integer | True |
Credit available. |
SellingCreditLimit | Integer | False |
Credit limit. |
SellingCreditOnHold | Boolean | False |
Credit on hold. |
SellingCreditPastDue | Integer | True |
Past due balance. |
SellingFreightTaxCodeCode | String | True |
3 digit tax code. |
SellingFreightTaxCodeID | Uuid | False |
Unique guid identifier belonging to the assigned tax code. |
SellingFreightTaxCodeURI | String | True |
Uniform resource identifier associated with the tax code object. |
SellingHourlyBillingRate | Double | False |
The customers hourly billing rate. |
SellingInvoiceDelivery | String | False |
Default invoice delivery status assigned. |
SellingItemPriceLevel | String | True |
Item price level, can consist of the following: Base Selling Price, Level A, Level B, Level C, Level D, Level E, Level F. |
SellingMemo | String | False |
Default memo text. |
SellingPrintedForm | String | False |
Named form selected as default printed form. |
SellingReceiptMemo | String | False |
Default receipt memo. |
SellingSaleComment | String | False |
Default selected sale comment. |
SellingSaleLayout | String | False |
Sale Type of the record containing the full default Sale Layout definition: NoDefault, Service, Item, Professional, TimeBilling, Miscellaneous. |
SellingShippingMethod | String | False |
Shipping method text. |
SellingTaxCodeCode | String | True |
3 digit tax code. |
SellingTaxCodeID | Uuid | True |
Unique guid identifier belonging to the assigned tax code. |
SellingTaxCodeURI | String | True |
Uniform resource identifier associated with the tax code object. |
SellingTermsBalanceDueDate | Integer | True |
The date of the entry. |
SellingTermsDiscountDate | Integer | True |
The date of the entry. |
SellingTermsDiscountForEarlyPayment | Double | True |
% discount for early payment. |
SellingTermsMonthlyChargeForLatePayment | Double | True |
% monthly charge for late payment. |
SellingTermsPaymentIsDue | String | True |
Default Terms of Payment definitions: CashOnDelivery, PrePaid, InAGivenNumberOfDays, OnADayOfTheMonth, NumberOfDaysAfterEOM, DayOfMonthAfterEOM. |
SellingTermsVolumeDiscount | Integer | True |
Volume supplier discount. |
SellingUseCustomerTaxCode | Boolean | True |
True indicates to use the customer tax code. False indicates do not use the customer tax code. |
SellingTaxIdNumber | String | False |
Tax id number. |
SellingIncomeAccountId | Uuid | True |
Unique identifier for the income account in the form of a guid. |
SellingIncomeAccountName | String | True |
Name of the income account. |
SellingIncomeAccountDisplayId | String | True |
Income account code format includes separator ie 4-1100. |
SellingIncomeAccountURI | String | True |
Uniform resource identifier associated with the income account object. |
SellingSalesPersonId | Uuid | True |
Unique employee contact identifier in the form of a guid. |
SellingSalesPersonName | String | True |
Selected employee contact name. |
SellingSalesPersonDisplayId | String | True |
Employee contact Card ID, can also be used as a unique employee contact identifier. |
SellingSalesPersonURI | String | True |
Uniform resource identifier associated with the employee contact object. |
PaymentMethod | String | False |
Payment methods must exist in company file, default methods consist of the following: American Express, Bank Card, Barter Card, Cash, Cheque, Diners Club, EFTPOS, MasterCard, Money Order, Other, Visa. |
PaymentCardNumber | String | False |
Last 4 digits only. |
PaymentNameOnCard | String | False |
Default name on card. |
PaymentNotes | String | False |
Default payment notes. |
PaymentBSBNumber | String | False |
Default bank account bsb number. |
PaymentBankAccountName | String | False |
Default bank account name. |
PaymentBankAccountNumber | String | False |
formatted (XX-XXXX-XXXXXXX-XX). |
ForeignCurrencyId | Uuid | True |
This is an AccountRight only field. Foreign Key: Unique identifier for the currency in the form of a guid. |
ForeignCurrencyCode | String | True |
This is an AccountRight only field.The currency code. |
ForeignCurrencyName | String | True |
This is an AccountRight only field. The full name of the currency. |
ForeignCurrencyURI | String | True |
This is an AccountRight only field. Uniform resource identifier associated with the currency object. |
Identifiers | String | True |
Contact Identifiers Details. |
CustomField1Label | String | True |
Custom Field 1 label. |
CustomField1Value | String | True |
Custom Field 1 value. |
CustomField2Label | String | True |
Custom Field 2 label. |
CustomField2Value | String | True |
Custom Field 2 value. |
CustomField3Label | String | True |
Custom Field 3 label. |
CustomField3Value | String | True |
Custom Field 3 value. |
CustomList1Label | String | True |
Custom List 1 label. |
CustomList1Value | String | True |
Custom List 1 value. |
CustomList2Label | String | True |
Custom List 2 label. |
CustomList2Value | String | True |
Custom List 2 value. |
CustomList3Label | String | True |
Custom List 3 label. |
CustomList3Value | String | True |
Custom List 3 value. |
Addresses | String | False |
The following set of information pulls through details for a contact address. |
RowVersion | String | False |
Number value that changes upon a record update, can be used for change control but does does not preserve a date or a time. |
CompanyFileId | String | True |
The ID of the company file. Takes precedence over the CompanyFileId connection property. |