ContactSuppliers

Return, update, create and delete a supplier 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
BuyingABN	BuyingABNBranch	BuyingCostPerHour	BuyingCreditAvailable	BuyingCreditLimit
BuyingCreditPastDue	BuyingExpenseAccountDisplayID	BuyingExpenseAccountID	BuyingExpenseAccountName	BuyingFreightTaxCodeCode
BuyingFreightTaxCodeID	BuyingIsReportable	BuyingPaymentMemo	BuyingPrintedForm	BuyingPurchaseComment
BuyingPurchaseLayout	BuyingPurchaseOrderDelivery	BuyingShippingMethod	BuyingSupplierBillingRate	BuyingTaxCodeCode
BuyingTaxCodeID	BuyingTermsBalanceDueDate	BuyingTermsDiscountDate	BuyingTermsDiscountForEarlyPayment	BuyingTermsPaymentIsDue
BuyingTermsVolumeDiscount	BuyingUseSupplierTaxCode	PaymentBSBNumber	PaymentBankAccountName	PaymentBankAccountNumber
PaymentRefundCardNumber	PaymentRefundNameOnCard	PaymentRefundNotes	PaymentRefundPaymentMethod	PaymentStatementText

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

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

Insert

The following attributes are required when performing an insert: CompanyName (if IsIndividual = false), FirstName, LastName, IsIndividual, BuyingTaxCodeID and BuyingFreightTaxCodeID.

INSERT INTO ContactSuppliers (FirstName, LastName, IsIndividual, BuyingTaxCodeID, BuyingFreightTaxCodeID) VALUES ("James", "Bond", true, "446d2226-254b-4821-acc2-7031842e0166", "446d2226-254b-4821-acc2-7031842e0166")

Update

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

Delete

You must specify the Id of the ContactSupplier to delete it.

DELETE FROM ContactSuppliers WHERE Id = '35fe2eaa-d941-4312-a427-bd4281670c75'

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.
BuyingABN	String	False	ABN Number (Must be 11 digits and formatted as XX XXX XXX XXX).
BuyingABNBranch	String	False	ABN branch number.
BuyingCostPerHour	Double	False	Cost per hour of providing the suppliers services when generating an activity slip.
BuyingCreditAvailable	Double	True	Credit availiable.
BuyingCreditLimit	Double	False	Credit limit.
BuyingCreditPastDue	Double	True	Past due balance.
BuyingExpenseAccountDisplayID	String	True	Account code. Format includes separator ie 4-1100
BuyingExpenseAccountID	Uuid	False	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	False	Unique guid identifier belonging to the assigned tax code.
BuyingFreightTaxCodeURI	String	True	Uniform resource identifier associated with the tax code object.
BuyingIsReportable	Boolean	False	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	False	Default payment memo.
BuyingPrintedForm	String	False	Named form selected as default printed form.
BuyingPurchaseComment	String	False	Default selected purchase comment.
BuyingPurchaseLayout	String	False	Purchase type of the record containing the full default purchase layout definition: NoDefault, Service, Item, Professional, Miscellaneous.
BuyingPurchaseOrderDelivery	String	False	Default supplier delivery status.
BuyingShippingMethod	String	False	Shipping method text.
BuyingSupplierBillingRate	Double	False	The suppliers hourly billing rate exclusive of tax.
BuyingTaxIdNumber	String	False	Tax id Number.
BuyingTaxCodeCode	String	True	3 digit tax code.
BuyingTaxCodeID	Uuid	False	Unique guid identifier belonging to the assigned tax code.
BuyingTaxCodeURI	String	True	Uniform resource identifier associated with the tax code object.
BuyingTermsBalanceDueDate	Integer	False	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	False	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	False	% discount for early payment.
BuyingTermsPaymentIsDue	String	False	Default Terms of Payment definitions: CashOnDelivery, PrePaid, InAGivenNumberOfDays, OnADayOfTheMonth, NumberOfDaysAfterEOM, DayOfMonthAfterEOM.
BuyingTermsVolumeDiscount	Double	False	Volume discount.
BuyingUseSupplierTaxCode	Boolean	False	True or false.
PaymentBSBNumber	String	False	Default bank account bsb number.
PaymentBankAccountName	String	False	Default bank account name.
PaymentBankAccountNumber	String	False	formatted (XX-XXXX-XXXXXXX-XX).
PaymentRefundCardNumber	String	False	Last 4 digits only.
PaymentRefundNameOnCard	String	False	Default name on card.
PaymentRefundNotes	String	False	Default refund payment notes.
PaymentRefundPaymentMethod	String	False	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.
PaymentStatementText	String	False	Default statement text.
PaymentStatementCode	String	False	Default code attached to an electronic payment. ONLY APPLICABLE FOR NZ REGION.
PaymentStatementReference	String	False	Default reference attached to an electronic payment. ONLY APPLICABLE FOR NZ REGION.
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.