Excel Add-In for MYOB

Build 23.0.8839

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:

IDCurrentBalanceFirstNameLastNameCompanyName
IsActiveIsIndividualLastModifiedNotesDisplayID
SellingABNSellingABNBranchSellingCreditAvailableSellingCreditLimitSellingCreditOnHold
SellingCreditPastDueSellingFreightTaxCodeCodeSellingFreightTaxCodeIDSellingHourlyBillingRateSellingInvoiceDelivery
SellingItemPriceLevelSellingMemoSellingPrintedFormSellingReceiptMemoSellingSaleComment
SellingSaleLayoutSellingShippingMethodSellingTaxCodeCodeSellingTaxCodeIDSellingTermsBalanceDueDate
SellingTermsDiscountDateSellingTermsDiscountForEarlyPaymentSellingTermsMonthlyChargeForLatePaymentSellingTermsPaymentIsDueSellingTermsVolumeDiscount
SellingUseCustomerTaxCodeSellingIncomeAccountIdSellingSalesPersonIdPaymentBSBNumberPaymentBankAccountName
PaymentBankAccountNumber

All the other columns and operators are processed client side. Type supports only equality comparison. 

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

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