JDBC Driver for MYOB

Build 24.0.9062

Contacts

Return all contact types for an AccountRight company file. This table has been deprecated, and new tables - ContactSupplier, ContactCustomer, ContactPersonal, and ContactEmployee have been introduced as replacements for it.

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 criteria can be executed by combining the criteria with AND and OR operators. These columns support the above comparison types for server side filtering:

ABNABNBranchBuyingABN BuyingABNBranchBuyingCostPerHour
BuyingPrintedFormBuyingPurchaseComment BuyingPurchaseLayoutBuyingPurchaseOrderDelivery BuyingShippingMethod
BuyingSupplierBillingRateCompanyNameCreditAvailableCreditLimitCreditOnHold
BuyingIsReportableBuyingPaymentMemoCreditPastDueCurrentBalanceDisplayID
EmployeePaymentDetailsIDEmployeePayrollDetailsIDEmployeeStandardPayIDExpenseAccountDisplayIDExpenseAccountID
ExpenseAccountNameFirstNameFreightTaxCodeCodeFreightTaxCodeIDHourlyBillingRate
IDInvoiceDeliveryIsActiveIsIndividualLastName
LastModifiedMemoNotesPaymentBankAccountNamePaymentBankAccountNumber
PaymentBSBNumberPaymentLastModifiedPaymentRefundCardNumberPaymentRefundNameOnCardPaymentRefundNotes
PaymentRefundPaymentMethodPaymentStatementTextPrintedFormReceiptMemoSaleComment
SaleLayoutShippingMethodTaxCodeCodeTaxCodeIDTermsBalanceDueDate
TermsDiscountDateTermsDiscountForEarlyPaymentTermsMonthlyChargeForLatePaymentTermsPaymentIsDueTermsVolumeDiscount
TimeBillingDetailsCostPerHourTimeBillingDetailsEmployeeBillingRateExcludingTaxUseCustomerTaxCode

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

SELECT * FROM Contacts WHERE ID = "1bed306d-2da4-49b3-9023-153c3dc5b2e9"
SELECT * FROM Contacts WHERE Type = "Customer"
SELECT * FROM Contacts WHERE FirstName = "James" OR FirstName = "John"

Update

To update an existing contact, along with the addresses, 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 Contacts SET FirstName = "James", Addresses = "ContactAddressItems#TEMP" WHERE ID = "1bed306d-2da4-49b3-9023-153c3dc5b2e9"

Insert

The following attributes are required when performing an insert: Type, CompanyName (if IsIndividual = false), FirstName, LastName, IsIndividual. In addition, for "Customer" and "Supplier" types, "SellingTaxCodeID, SellingFreightTaxCodeID" and "BuyingTaxCodeID, BuyingFreightTaxCodeID" are required respectively. 

INSERT INTO Contacts (Type, FirstName, LastName, IsIndividual, SellingTaxCodeID, SellingFreightTaxCodeID) VALUES ("Customer", "James", "Bond", true, "446d2226-254b-4821-acc2-7031842e0166", "446d2226-254b-4821-acc2-7031842e0166")

Columns

Name Type ReadOnly References Description
ID [KEY] Uuid True

Unique identifier in the form of a guid.

CurrentBalance Decimal False

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 False

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.

Type String False

Contact type. One of: Customer,Supplier,Personal,Employee.

DisplayID String False

Display ID for the contact card.

SellingABN String True

ABN Number (Must be 11 digits and formatted as XX XXX XXX XXX).

SellingABNBranch String True

ABN branch number.

SellingCreditAvailable Integer True

Credit available.

SellingCreditLimit Integer True

Credit limit.

SellingCreditOnHold Boolean True

Credit on hold.

SellingCreditPastDue Integer True

Past due balance.

SellingFreightTaxCodeCode String True

3 digit tax code.

SellingFreightTaxCodeID Uuid True

Unique guid identifier belonging to the assigned tax code.

SellingFreightTaxCodeURI String True

Uniform resource identifier associated with the tax code object.

SellingHourlyBillingRate Double True

The customers hourly billing rate.

SellingInvoiceDelivery String True

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 True

Default memo text.

SellingPrintedForm String True

Named form selected as default printed form.

SellingReceiptMemo String True

Default receipt memo.

SellingSaleComment String True

Default selected sale comment.

SellingSaleLayout String True

Sale Type of the record containing the full default Sale Layout definition: NoDefault, Service, Item, Professional, TimeBilling, Miscellaneous.

SellingShippingMethod String True

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.

BuyingABN String True

ABN Number (Must be 11 digits and formatted as XX XXX XXX XXX).

BuyingABNBranch String True

ABN branch number.

BuyingCostPerHour Double True

Cost per hour of providing the suppliers services when generating an activity slip.

BuyingCreditAvailable Double True

Credit availiable.

BuyingCreditLimit Double True

Credit limit.

BuyingCreditPastDue Double True

Past due balance.

BuyingExpenseAccountDisplayID String True

Account code. Format includes separator ie 4-1100

BuyingExpenseAccountID Uuid True

Unique identifier for the account in the form of a guid.

BuyingExpenseAccountName String True

Name of the account.

BuyingExpenseAccountURI String True

Uniform resource identifier associated with the account object.

BuyingFreightTaxCodeCode String True

3 digit tax code.

BuyingFreightTaxCodeID Uuid True

Unique guid identifier belonging to the assigned tax code.

BuyingFreightTaxCodeURI String True

Uniform resource identifier associated with the tax code object.

BuyingIsReportable Boolean True

True indicates the supplier contact is setup for reportable taxable payments. False indicates the supplier contact is not setup for reportable taxable payments.

BuyingPaymentMemo String True

Default payment memo.

BuyingPrintedForm String True

Named form selected as default printed form.

BuyingPurchaseComment String True

Default selected purchase comment.

BuyingPurchaseLayout String True

Purchase type of the record containing the full default purchase layout definition: NoDefault, Service, Item, Professional, Miscellaneous.

BuyingPurchaseOrderDelivery String True

Default supplier delivery status.

BuyingShippingMethod String True

Shipping method text.

BuyingSupplierBillingRate Double True

The suppliers hourly billing rate exclusive of tax.

BuyingTaxCodeCode String True

3 digit tax code.

BuyingTaxCodeID Uuid True

Unique guid identifier belonging to the assigned tax code.

BuyingTaxCodeURI String True

Uniform resource identifier associated with the tax code object.

BuyingTermsBalanceDueDate Integer True

If PaymentIsDue = CashOnDelivery, PrePaid, InAGivenNumberOfDaysNumber or NumberOfDaysAfterEOM then set the number of days within which payments must be made. If PaymentIsDue = OnADayOfTheMonth or DayOfMonthAfterEOM then set the day of the month within which payments must be made.

BuyingTermsDiscountDate Integer True

If PaymentIsDue = CashOnDelivery, PrePaid, InAGivenNumberOfDaysNumber or NumberOfDaysAfterEOM then set the number of days within which payments must be made to receive discounts. If PaymentIsDue = OnADayOfTheMonth or DayOfMonthAfterEOM then set the day of the month within which the payment must be made to receive discounts.

BuyingTermsDiscountForEarlyPayment Double True

% discount for early payment.

BuyingTermsPaymentIsDue String True

Default Terms of Payment definitions: CashOnDelivery, PrePaid, InAGivenNumberOfDays, OnADayOfTheMonth, NumberOfDaysAfterEOM, DayOfMonthAfterEOM.

BuyingTermsVolumeDiscount Double True

Volume discount.

BuyingUseSupplierTaxCode Boolean True

True or false.

PaymentBSBNumber String True

Default bank account bsb number.

PaymentBankAccountName String True

Default bank account name.

PaymentBankAccountNumber String True

formatted (XX-XXXX-XXXXXXX-XX).

PaymentLastModified String True

Extracts the last modification date for the contact resource.

PaymentPhotoURI String True

Uniform resource identifier associated with a photo image.

PaymentRefundCardNumber String True

Last 4 digits only.

PaymentRefundNameOnCard String True

Default name on card.

PaymentRefundNotes String True

Default refund payment notes.

PaymentRefundPaymentMethod String True

Payment method on refund, 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.

PaymentRowVersion String True

Number value that changes upon a record update, can be used for change control but does does not preserve a date or a time.

PaymentStatementText String True

Default statement text.

PaymentURI String True

Uniform resource identifier encompasses all types of names and addresses that refer to objects on the web.

EmployeePaymentDetailsID Uuid True

Unique identifier in the form of a guid.

EmployeePaymentDetailsURI String True

Uniform resource identifier encompasses all types of names and addresses that refer to objects on the web.

EmployeePayrollDetailsID Uuid True

Unique identifier in the form of a guid.

EmployeePayrollDetailsURI String True

Uniform resource identifier encompasses all types of names and addresses that refer to objects on the web.

EmployeeStandardPayID Uuid True

Unique identifier in the form of a guid.

EmployeeStandardPayURI String True

Uniform resource identifier encompasses all types of names and addresses that refer to objects on the web.

TimeBillingDetailsCostPerHour Double True

Hourly cost of employee to business.

TimeBillingDetailsEmployeeBillingRateExcludingTax Double True

Hourly billing rate for services provided by employee

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.

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