Return all purchase order types for an AccountRight company file.
Table Specific Information
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, AppliedToDate, Date, Freight, BalanceDueAmount, IsReportable, IsTaxInclusive, JournalMemo, Number, ShipToAddress, Status, SupplierInvoiceNumber, Subtotal, TotalAmount, TotalTax, CategoryID, CategoryDisplayID, CategoryName, FreightTaxCodeID, FreightTaxCodeCode, SupplierID, SupplierDisplayID, SupplierName, TermsBalanceDueDate, TermsDiscount, TermsDiscountDate, TermsDiscountExpiryDate, TermsDiscountForEarlyPayment, TermsDueDate, TermsPaymentIsDue. All the other columns and operators are processed client side.
OrderType supports only equality comparison.
SELECT * FROM PurchaseOrders WHERE ID = "992163e5-f46d-4b45-9296-965aa89a2a71" SELECT * FROM PurchaseOrders WHERE AppliedToDate = 40 SELECT * FROM PurchaseOrders WHERE OrderType = "Service"
To update an existing order, along with its Lines, we can either pass a JSON string to the Lines value or use a temporary table like below.
Note: this will replace all the current Lines with the ones below.
INSERT INTO PurchaseOrderItems#TEMP(Total, AccountID, TaxCodeID) values (444.21, "44a7b2bc-0a0e-4381-86fc-9b2c7a479f8f", "bd0f9821-4437-4ec2-9c95-c1bdbdd26c7b") UPDATE PurchaseOrders SET OrderType = "Service", Lines = "PurchaseOrderItems#TEMP" WHERE ID = "992163e5-f46d-4b45-9296-965aa89a2a71"
The following attributes are required when performing an insert: OrderType, Date, FreightTaxCodeID, SupplierID.
INSERT INTO PurchaseOrders(OrderType, Date, FreightTaxCodeID, SupplierID) VALUES ("Service", "01/01/2019", "446d2226-254b-4821-acc2-7031842e0166", "9421b774-20fa-4c5f-bced-fbb6ecd42ebd")
Unique identifier in the form of a guid.
Amount currently applied to the purchase order.
The date of the entry.
Tax inclusive freight amount applicable to the purchase order.
Amount still payable on the purchase order.
ONLY applicable for AU region. True indicates the transaction is reportable taxable payment. Falseindicates the transaction is not reportable taxable payment.
True indicates the transaction is set to tax inclusive. False indicates the transaction is not tax inclusive.
Memo text for the object.
Purchase order number.
Type of the order. One of: Item,Service,Professional,Miscellaneous
ShipTo address of the purchase order.
Purchase Order status: Open, ConvertedToBill.
Supplier invoice number.
If IsTaxInclusive = false then sum of all tax exclusive line amounts. If IsTaxInclusive = true then sum of all tax inclusive line amounts.
Total amount of the purchase order.
Total of all tax amounts applicable to the purchase order.
Number value that changes upon a record update, can be used for change control but does does not preserve a date or a time.
Uniform resource identifier encompasses all types of names and addresses that refer to objects on the web.
Unique category identifier in the form of a guid.
Display id for the category.
Name of the category.
Uniform resource identifier associated with the category object.
Unique guid identifier belonging to the assigned tax code.
3 digit tax code.
Uniform resource identifier associated with the tax code object.
Purchase Order Comment
Transaction Promised Date.
Unique guid identifier belonging to the assigned supplier contact.
Customer contact Card ID, can also be used as a unique supplier contact identifier.
Name of the supplier contact.
Uniform resource identifier associated with the supplier contact object.
If PaymentIsDue = CashOnDelivery, PrePaid, InAGivenNumberOfDaysNumber or NumberOfDaysAfterEOM then set the number of days within which the customer has to pay invoices. If PaymentIsDue = OnADayOfTheMonth or DayOfMonthAfterEOM then set the day of the month within which the payment must be made to pay invoices.
Discount amount that will apply if payment is made in full by the discount date.
If PaymentIsDue = CashOnDelivery, PrePaid, InAGivenNumberOfDaysNumber or NumberOfDaysAfterEOM then set the number of days within which the customer must pay 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.
Date in which payment must be paid in full in quote to receive discount.
% discount for early payment.
Date in which payment is due.
Default Terms of Payment definitions: CashOnDelivery, PrePaid, InAGivenNumberOfDays, OnADayOfTheMonth, NumberOfDaysAfterEOM, DayOfMonthAfterEOM.
An array of line order information.
The ID of the company file. Takes precedence over the CompanyFileId connection property.