MYOB Connection Details¶

Introduction¶

Connector Version

This documentation is based on version 23.0.8804 of the connector.

Get Started¶

MYOB Version Support

The connector leverages the MYOB API to enable bidirectional access to MYOB.

Establish a Connection¶

Connect to MYOB¶

These properties are required when connecting to a company file (both for On-Premises and Cloud instances).

• User: The username associated with your company file.

• CompanyFileId: The ID of the company file. If not specified, the ID of the first returned company file will be used. You can find this by querying the CompanyFiles view:

  SELECT ID FROM CompanyFiles
  

Connect to an On-Premises instance¶

When connecting to an on-premises instance, you will need to set the following connection properties in addition to those above:

• InitiateOAuth: Set this to OFF.
• URL: The URL of your MYOB instance.
• Password: The password associated with your company file.

Connect to a Cloud Instance¶

You can connect after setting required connection properties for your user credentials and obtaining required access tokens using a tool such as Postman.

See Using OAuth Authentication for an authentication guide and details of the OAuth authentication flow.

Create a Custom OAuth Application¶

Create a Custom OAuth Application¶

embeds OAuth Application Credentials with branding that can be used when connecting to MYOB via a desktop application or a headless machine. If you want to use the embedded OAuth application, all you need to do to connect is to:

• set AuthScheme to OAuth,
• get and set the OAuthAccessToken, and
• set the necessary configuration parameters.

(For information on getting and setting the OAuthAccessToken and other configuration parameters, see the Desktop Authentication section of "Connecting to MYOB".)

However, you must create a custom OAuth application to connect to MYOB via the Web. And since custom OAuth applications seamlessly support all three commonly-used auth flows, you might want to create custom OAuth applications (use your own OAuth Application Credentials) for those auth flows anyway.

Custom OAuth applications are useful if you want to:

• control branding of the authentication dialog;
• control the redirect URI that the application redirects the user to after the user authenticates; or
• customize the permissions that you are requesting from the user.

Procedure¶

To register a custom OAuth application and derive the OAuthClientId and OAuthClientSecret:

1. Log in to your MYOB account.

2. From the Home Page, select Developer.

3. Click Register App.

4. Enter a name and description for the new application. Also specify a Redirect Uri, which specifies where the

   user should be redirected after they authorize your application. For desktop and headless machine applications, set this to something like http://localhost:33333.

After you complete the registration, the new application's key (Client ID) and secret (Client Secret) are displayed onscreen. Record these for future use.

Important Notes¶

Configuration Files and Their Paths¶

• All references to adding configuration files and their paths refer to files and locations on the Harmony Agent where the connector is installed. These paths are to be adjusted as appropriate depending on the agent and the operating system. If multiple agents are used in an agent group, identical files will be required on each agent.

Advanced Features¶

This section details a selection of advanced features of the MYOB connector.

User Defined Views

The connector allows you to define virtual tables, called user defined views, whose contents are decided by a pre-configured query. These views are useful when you cannot directly control queries being issued to the drivers. See User Defined Views for an overview of creating and configuring custom views.

SSL Configuration

Use SSL Configuration to adjust how connector handles TLS/SSL certificate negotiations. You can choose from various certificate formats; see the SSLServerCert property under "Connection String Options" for more information.

Proxy

To configure the connector using Private Agent proxy settings, select the Use Proxy Settings checkbox on the connection configuration screen.

Query Processing

The connector offloads as much of the SELECT statement processing as possible to MYOB and then processes the rest of the query in memory (client-side).

User Defined Views¶

The MYOB connector allows you to define a virtual table whose contents are decided by a pre-configured query. These are called User Defined Views, which are useful in situations where you cannot directly control the query being issued to the driver, e.g. when using the driver from Jitterbit. The User Defined Views can be used to define predicates that are always applied. If you specify additional predicates in the query to the view, they are combined with the query already defined as part of the view.

There are two ways to create user defined views:

• Create a JSON-formatted configuration file defining the views you want.
• DDL statements.

Define Views Using a Configuration File¶

User Defined Views are defined in a JSON-formatted configuration file called UserDefinedViews.json. The connector automatically detects the views specified in this file.

You can also have multiple view definitions and control them using the UserDefinedViews connection property. When you use this property, only the specified views are seen by the connector.

This User Defined View configuration file is formatted as follows:

• Each root element defines the name of a view.
• Each root element contains a child element, called query, which contains the custom SQL query for the view.

For example:

{
    "MyView": {
        "query": "SELECT * FROM Accounts WHERE MyColumn = 'value'"
    },
    "MyView2": {
        "query": "SELECT * FROM MyTable WHERE Id IN (1,2,3)"
    }
}

Use the UserDefinedViews connection property to specify the location of your JSON configuration file. For example:

"UserDefinedViews", "C:\Users\yourusername\Desktop\tmp\UserDefinedViews.json"

Define Views Using DDL Statements¶

The connector is also capable of creating and altering the schema via DDL Statements such as CREATE LOCAL VIEW, ALTER LOCAL VIEW, and DROP LOCAL VIEW.

Create a View¶

To create a new view using DDL statements, provide the view name and query as follows:

CREATE LOCAL VIEW [MyViewName] AS SELECT * FROM Customers LIMIT 20;

If no JSON file exists, the above code creates one. The view is then created in the JSON configuration file and is now discoverable. The JSON file location is specified by the UserDefinedViews connection property.

Alter a View¶

To alter an existing view, provide the name of an existing view alongside the new query you would like to use instead:

ALTER LOCAL VIEW [MyViewName] AS SELECT * FROM Customers WHERE TimeModified > '3/1/2020';

The view is then updated in the JSON configuration file.

Drop a View¶

To drop an existing view, provide the name of an existing schema alongside the new query you would like to use instead.

DROP LOCAL VIEW [MyViewName]

This removes the view from the JSON configuration file. It can no longer be queried.

Schema for User Defined Views¶

User Defined Views are exposed in the UserViews schema by default. This is done to avoid the view's name clashing with an actual entity in the data model. You can change the name of the schema used for UserViews by setting the UserViewsSchemaName property.

Work with User Defined Views¶

For example, a SQL statement with a User Defined View called UserViews.RCustomers only lists customers in Raleigh:

SELECT * FROM Customers WHERE City = 'Raleigh';

An example of a query to the driver:

SELECT * FROM UserViews.RCustomers WHERE Status = 'Active';

Resulting in the effective query to the source:

SELECT * FROM Customers WHERE City = 'Raleigh' AND Status = 'Active';

That is a very simple example of a query to a User Defined View that is effectively a combination of the view query and the view definition. It is possible to compose these queries in much more complex patterns. All SQL operations are allowed in both queries and are combined when appropriate.

SSL Configuration¶

Customize the SSL Configuration¶

By default, the connector attempts to negotiate SSL/TLS by checking the server's certificate against the system's trusted certificate store.

To specify another certificate, see the SSLServerCert property for the available formats to do so.

Data Model¶

Overview

This section shows the available API objects and provides more information on executing SQL to MYOB APIs.

Key Features

• The connector models MYOB entities like accounts, purchaseorders, and salesorders as relational tables and views, allowing you to write SQL to query MYOB data.
• Stored procedures allow you to execute operations to MYOB, including retrieving the access token and keeping it refreshed in OAuth 2.0.
• Live connectivity to these objects means any changes to your MYOB account are immediately reflected when using the connector.

Tables

Tables describes the available tables. Tables are statically defined to model Customers, PurchaseOrders, SalesOrders, and more.

Views

Views are available for read-only access to data from MYOB, including AccountRegister and Currencies.

Stored Procedures

Stored Procedures are function-like interfaces to MYOB. Stored procedures allow you to execute operations to MYOB, including downloading and uploading objects.

Tables¶

The connector models the data in MYOB as a list of tables in a relational database that can be queried using standard SQL statements.

MYOB Connector Tables¶

AccountBudgets¶

Return and update general ledger account budgets.

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: FinancialYear, LastMonthInFinancialYear. All the other columns and operators are processed client side.

SELECT * FROM AccountBudgets WHERE FinancialYear = 13
SELECT * FROM AccountBudgets WHERE LastMonthInFinancialYear = 41

Columns¶

Accounts¶

Return, update, create and delete accounts 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, Classification, CurrentBalance, Description, DisplayID, IsActive, IsHeader, Level, Name, Number, OpeningBalance, Type, LastReconciledDate, BankingBSBNumber, BankingAccountName, BankingAccountNumber, BankingCode, BankingCompanyTradingName, BankingCreateBankFiles, BankingDirectEntryUserId, BankingIncludeSelfBalancingTransaction, BankingStatementParticulars, ForeignCurrencyID, ForeignCurrencyCode, ForeignCurrencyCurrencyName, ParentAccountID, ParentAccountDisplayID, ParentAccountName, TaxCodeID, TaxCodeCode. All the other columns and operators are processed client side.

SELECT * FROM Accounts WHERE ID = REPLACE
SELECT * FROM Accounts WHERE Classification = "criteria"
SELECT * FROM Accounts WHERE CurrentBalance = 40.72729281968018

Insert¶

The following attributes are required when performing an insert: Number, Type, TaxCodeID.

INSERT INTO Accounts (Number, Type, TaxCodeID) VALUES (101, "bank", "2619bed9-3750-45b5-b353-18f5651beb01")

Columns¶

Activities¶

Return, update, create and delete a list of time billing activities 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, Description, DisplayID, IsActive, Name, Status, Type, UnitOfMeasurement, IncomeAccountID, IncomeAccountDisplayID, IncomeAccountName, ChargeableRate, UseDescriptionOnSales, TaxCodeID, TaxCodeCode. All the other columns and operators are processed client side.

SELECT * FROM Activities WHERE ID = REPLACE
SELECT * FROM Activities WHERE Description = "criteria"
SELECT * FROM Activities WHERE DisplayID = "criteria"

Insert¶

The following attributes are required when performing an insert: DisplayID, IncomeAccountID, TaxCodeID, Type, Status.

INSERT INTO Activities (DisplayID, IncomeAccountID, TaxCodeID, Type, Status) VALUES ("test", "0b9b6098-1a9e-499d-bf41-b863a6daad9c", "2619bed9-3750-45b5-b353-18f5651beb01", "Hourly", "Chargeable"

Columns¶

ActivitySlips¶

Return, update, create and delete a list of time billing activity slips 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, ProviderID, ProviderType, ProviderName, CustomerID, CustomerName, ActivityID, ActivityName, JobID, JobNumber, JobName, HourlySalaryPayrollCategoryType, HourlySalaryPayrollCategoryName. All the other columns and operators are processed client side.

SELECT * FROM ActivitySlips WHERE ID = REPLACE
SELECT * FROM ActivitySlips WHERE Rate = 15
SELECT * FROM ActivitySlips WHERE AlreadyBilledAmount = 45.86163291317715

Update¶

To update an existing activity slip, see below.

UPDATE ActivitySlips SET Rate = 40 WHERE ID = "f979f0e7-1cd8-4fb9-b2c2-9d4546440cf7"

Insert¶

The following attributes are required when performing an insert: ProviderID, CustomerID, ActivityID.

INSERT INTO ActivitySlips (Rate, AlreadyBilledAmount, Date, ProviderID, CustomerID, ActivityID) VALUES (50, 100.50, "01/01/2023", "43d9bef9-4f5e-4ef2-88a8-4d93a0b8866d", "e3b36dea-3720-449c-8cb3-197d3a2e5f4a", "5ec16ddc-3344-4bef-a40d-ddbfd6098914")

Columns¶

Build¶

Return's Build transaction journals

Columns¶

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

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¶

ContactEmployees¶

Return, update, create and delete an employee 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:

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

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

Insert¶

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

INSERT INTO ContactEmployees (FirstName, LastName, IsIndividual) VALUES ("James", "Bond", true)

Update¶

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

Delete¶

You must specify the ID of the ContactEmployee to delete it.

DELETE FROM ContactEmployees WHERE ID = '5a3e152b-4910-4cbe-ad27-32a57392ae23'

Columns¶

ContactPersonals¶

Return, update, create and delete a personal 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:

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

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

Insert¶

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

INSERT INTO ContactPersonals (FirstName, LastName, IsIndividual) VALUES ("James", "Bond", true)

Update¶

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

Delete¶

You must specify the ID of the ContactPersonal to delete it.

DELETE FROM ContactPersonals WHERE ID = '5a3e152b-4910-4cbe-ad27-32a57392ae23'

Columns¶

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

| | | | ----------------------------- | ------------------------------------------------- | -------------------------------- | --------------------------- | | ABN | ABNBranch | BuyingABN | BuyingABNBranch | BuyingCostPerHour | | BuyingPrintedForm | BuyingPurchaseComment | BuyingPurchaseLayout | BuyingPurchaseOrderDelivery | BuyingShippingMethod | | BuyingSupplierBillingRate | CompanyName | CreditAvailable | CreditLimit | CreditOnHold | | BuyingIsReportable | BuyingPaymentMemo | CreditPastDue | CurrentBalance | DisplayID | | EmployeePaymentDetailsID | EmployeePayrollDetailsID | EmployeeStandardPayID | ExpenseAccountDisplayID | ExpenseAccountID | | ExpenseAccountName | FirstName | FreightTaxCodeCode | FreightTaxCodeID | HourlyBillingRate | | ID | InvoiceDelivery | IsActive | IsIndividual | LastName | | LastModified | Memo | Notes | PaymentBankAccountName | PaymentBankAccountNumber | | PaymentBSBNumber | PaymentLastModified | PaymentRefundCardNumber | PaymentRefundNameOnCard | PaymentRefundNotes | | PaymentRefundPaymentMethod | PaymentStatementText | PrintedForm | ReceiptMemo | SaleComment | | SaleLayout | ShippingMethod | TaxCodeCode | TaxCodeID | TermsBalanceDueDate | | TermsDiscountDate | TermsDiscountForEarlyPayment | TermsMonthlyChargeForLatePayment | TermsPaymentIsDue | TermsVolumeDiscount | | TimeBillingDetailsCostPerHour | TimeBillingDetailsEmployeeBillingRateExcludingTax | UseCustomerTaxCode | |

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, 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 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¶

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:

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¶

CreditRefunds¶

Return, create and delete credit notes refunded to customers 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, Date, DeliveryStatus, Memo, Number, Payee, Amount, ChequePrinted, AccountID, AccountDisplayID, AccountName, CustomerID, CustomerDisplayID, CustomerName, InvoiceID, InvoiceNumber. All the other columns and operators are processed client side.

SELECT * FROM CreditRefunds WHERE ID = "6d35e53a-5070-432d-b9ec-f4791e353352
SELECT * FROM CreditRefunds WHERE AccountName = "AccountName"
SELECT * FROM CreditRefunds WHERE Amount = 340.44

Insert¶

The following attributes are required when performing an insert: Date, Amount, AccountID, CustomerID, InvoiceID.

INSERT INTO CreditRefunds (Date, Amount, AccountID, CustomerID, InvoiceID) VALUES ("01/02/2019", 54, "3a35e83a-5070-432d-b6ec-f4791f353352", "2619bed9-3750-45b5-b353-18f5651beb01", "f6703af2-7494-4ecc-b0f6-b2bb9c813782")

Columns¶

CreditSettlements¶

Return, create and delete settled customer credits 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, Date, Memo, Number, CreditAmount, CreditFromInvoiceID, CreditFromInvoiceNumber, CustomerID, CustomerDisplayID, CustomerName. All the other columns and operators are processed client side.

SELECT * FROM CreditSettlements WHERE ID = "7cabea25-6250-4df5-bb37-77eb2d38f803"
SELECT * FROM CreditSettlements WHERE CustomerID = "07763f97-43cc-4149-8c00-d92feb4e1404"
SELECT * FROM CreditSettlements WHERE Memo = "memo"

Insert¶

The following attributes are required when performing an insert: Date, CreditFromInvoiceID, CustomerID.

To insert the Lines values into CreditSettlements, we can either pass the JSON data as a string or use a temporary table like below.

INSERT INTO CreditSettlementItems#TEMP (AmountApplied, Type, SaleId) VALUES (444.21, "Invoice", "0229a075-f93a-4b6c-85d7-0ffd5ba43982")
INSERT INTO CreditSettlements (Date, CreditFromInvoiceID, CustomerID, Lines) VALUES ("01/01/2019", "07763f97-43cc-4149-8c00-d92feb4e1404", "6aaccbbf-2a21-44eb-8462-dfff3117dd5a", "CreditSettlementItems#TEMP")

Columns¶

CustomerPayments¶

Return, create and delete customer payments 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, Date, DepositTo, AmountReceived, Memo, PaymentMethod, ReceiptNumber, AccountID, AccountDisplayID, AccountName, CustomerID, CustomerDisplayID, CustomerName. All the other columns and operators are processed client side.

SELECT * FROM CustomerPayments WHERE ID = "7cabea25-6250-4df5-bb37-77eb2d38f803"
SELECT * FROM CustomerPayments WHERE CustomerID = "0229a075-f93a-4b6c-85d7-0ffd5ba43982"
SELECT * FROM CustomerPayments WHERE AmountReceived = 1399.99

Insert¶

The following attributes are required when performing an insert: DepositTo, AccountID, CustomerID, Invoices.

To insert the Invoices values into CustomerPayments, we can either pass the JSON data as a string or use a temporary table like below.

INSERT INTO CustomerPaymentInvoices#TEMP (AmountApplied, ID, Type) VALUES (444.21, " 0229a075-f93a-4b6c-85d7-0ffd5ba43982", "Order")
INSERT INTO CustomerPayments (DepositTo, AccountID, CustomerID, Invoices) VALUES ("Account", "ee9824a1-26d7-4501-8c19-83bc1fd84167", "ee9824a1-26d7-4501-8c19-83bc1fd84167", "CustomerPaymentInvoices#TEMP")

Columns¶

DebitRefunds¶

Return all purchase bill types 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, Amount, Date, DepositTo, Memo, Number, PaymentMethod, BillID, BillNumber, AccountID, AccountDisplayID, AccountName, SupplierID, SupplierDisplayID, SupplierName. All the other columns and operators are processed client side.

SELECT * FROM DebitRefunds WHERE ID = "7cabea25-6250-4df5-bb37-77eb2d38f803"
SELECT * FROM DebitRefunds WHERE Amount = 1600.00
SELECT * FROM DebitRefunds WHERE AccountName = "Processing account"

Insert¶

The following attributes are required when performing an insert: Amount, Date, DepositTo, BillID, AccountID.

INSERT INTO DebitRefunds (Date, Amount, AccountID, DepositTo, BillID) VALUES ("01/02/2019", 54, "3a35e83a-5070-432d-b6ec-f4791f353352", "Account", "f6703af2-7494-4ecc-b0f6-b2bb9c813782")

Columns¶

DebitSettlements¶

Return, create and delete debit settlements 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, Date, Memo, Number, DebitAmount, DebitFromBillID, DebitFromBillNumber, SupplierID, SupplierDisplayID, SupplierName. All the other columns and operators are processed client side.

SELECT * FROM DebitSettlements WHERE ID = "7cabea25-6250-4df5-bb37-77eb2d38f803"
SELECT * FROM DebitSettlements WHERE DebitAmount = 2600
SELECT * FROM DebitSettlements WHERE Memo = "memo"

Insert¶

The following attributes are required when performing an insert: Date, DebitFromBillID, Lines.

To insert the Lines values into DebitSettlements, we can either pass the JSON data as a string or use a temporary table like below.

INSERT INTO DebitSettlementItems#TEMP (AmountApplied, Type, Purchaseid) VALUES (444.21, "Order", "0229a075-f93a-4b6c-85d7-0ffd5ba43982")
INSERT INTO DebitSettlements (Date, DebitFromBillID, Lines) VALUES ("01/01/2019", "07763f97-43cc-4149-8c00-d92feb4e1404", "6aaccbbf-2a21-44eb-8462-dfff3117dd5a", "DebitSettlementItems#TEMP")

Columns¶

EmployeePaymentDetails¶

Return and update employee payment details on employee contact cards 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, BankStatementText, PaymentMethod, EmployeeID, EmployeeDisplayID, EmployeeName. All the other columns and operators are processed client side.

SELECT * FROM EmployeePaymentDetails WHERE ID = "1bed306d-2da4-49b3-9023-153c3dc5b2e9"
SELECT * FROM EmployeePaymentDetails WHERE EmployeeID = "1bed306d-2da4-49b3-9023-153c3dc5b2e9"

Update¶

To update an existing employee payment detail, along with its BankAccounts, we can either pass a JSON string to the BankAccounts value or use a temporary table like below. Note: this will replace all the current BankAccounts with the ones below.

INSERT INTO EmployeeBankAccountItems#TEMP (BSBNumber, BankAccountName, BankAccountNumber, Unit, Value) VALUES ("341-241", "123412341", "Mr A Long", 100, "Percent")
UPDATE EmployeePaymentDetails SET BankAccounts = "EmployeeBankAccountItems#TEMP" WHERE ID = "992163e5-f46d-4b45-9296-965aa89a2a71"

Columns¶

EmployeePayrollDetails¶

Return and update employee payroll details on employee contact cards 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, DateOfBirth, Gender, PaySlipDelivery, PaySlipEmail, StartDate, TimeBillingCostPerHour, TimeBillingEmployeeBillingRateExcludingTax, EmploymentBasis, EmploymentCategory, EmploymentStatus, EmployeeID, EmployeeDisplayID, EmployeeName, EmploymentClassificationID, EmploymentClassificationName, TaxCategoryID, TaxCategoryName, TaxCategoryType, TaxTableID, TaxTableName, TaxFileNumber, TaxTotalRebatesPerYear, TaxWithholdingVariationRate, TaxExtraTaxPerPay, WageAnnualSalary, WageHourlyRate, WageHoursInWeeklyPayPeriod, WagePayBasis, WagePayFrequency, WagesExpenseAccountID, WagesExpenseAccountDisplayID, WagesExpenseAccountName. All the other columns and operators are processed client side.

SELECT * FROM EmployeePayrollDetails WHERE ID = "1bed306d-2da4-49b3-9023-153c3dc5b2e9"
SELECT * FROM EmployeePayrollDetails WHERE TaxTableID = "a3ed306d-2da1-49b3-9023-353c3dffb2e9"
SELECT * FROM EmployeePayrollDetails WHERE StartDate = "01/02/2019"

Update¶

To update an existing payroll detail aggregate list, we can either pass a JSON string to the aggregate input value or use a temporary table like below.

INSERT INTO EmployeePayrollDeductionItems#TEMP (ID) VALUES ("a7ld306u-2da1-49b2-90f3-111c3dffb2e2)
UPDATE EmployeePayrollDetails SET Deductions = "EmployeePayrollDeductionItems#TEMP" WHERE ID = "1bed306d-2da4-49b3-9023-153c3dc5b2e9"

Columns¶

EmployeeStandardPay¶

Return and update employee standard pay details on employee contact cards 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, Memo, HoursPerPayFrequency, PayFrequency, CategoryID, CategoryDisplayID, CategoryName, EmployeeID, EmployeeDisplayID, EmployeeName, PayrollDetailsID. All the other columns and operators are processed client side.

SELECT * FROM EmployeeStandardPay WHERE ID = "1bed306d-2da4-49b3-9023-153c3dc5b2e9"
SELECT * FROM EmployeeStandardPay WHERE EmployeeID = "2aed306d-2da3-49b1-9023-153c3dc5b2ef"
SELECT * FROM EmployeeStandardPay WHERE HoursPerPayFrequency = 11

Update¶

To update an existing employee, along with its PayrollCategories, we can either pass a JSON string to the PayrollCategories value or use a temporary table like below. Note: this will replace all the current PayrollCategories with the ones below.

INSERT INTO EmployeePayrollCategoryItems#TEMP (payrollcategoryid) VALUES ("2ced306d-1da3-49b1-2223-153c3dc5b2ef")
UPDATE EmployeeStandardPay SET PayrollCategories = "EmployeePayrollCategoryItems#TEMP" WHERE ID = "1bed306d-2da4-49b3-9023-153c3dc5b2e9"

Columns¶

InventoryAdjustments¶

Return, update, create and delete inventory adjustments 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, InventoryJournalNumber, Date, IsYearEndAdjustment, Memmo, CategoryID, CategoryDisplayID, CategoryName. All the other columns and operators are processed client side.

SELECT * FROM InventoryAdjustments WHERE ID = "10f623a6-1638-4970-afd5-0394191bf015"
SELECT * FROM InventoryAdjustments WHERE InventoryJournalNumber = "IJ000001"
SELECT * FROM InventoryAdjustments WHERE IsYearEndAdjustment = True

Update¶

To update an existing inventory adjustment, 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 InventoryAdjustmentItems#TEMP (Quantity, AccountID, ItemID, LocationID, RowID, RowVersion) VALUES (500, "2e653a1a-dafe-4e81-a553-f9a56ed3d105", "d5ab0fd1-3bf4-4230-bbaf-90b26ea9afda", "04ad68a3-91f5-4739-8b83-13f86ecd2e33", 656, "8733325201913151488")
UPDATE InventoryAdjustments SET InventoryJournalNumber = "IJ000002", Lines = "InventoryAdjustmentItems#TEMP" WHERE ID = "10f623a6-1638-4970-afd5-0394191bf015"

Insert¶

The following attributes are required when performing an insert: Date.

INSERT INTO InventoryAdjustments (Date, InventoryJournalNumber, IsYearEndAdjustment) VALUES ("01/01/2023", "IJ000003", False)

Columns¶

ItemPriceMatrices¶

Return and update the item price matrix for multiple customer selling prices.

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, ItemID, ItemName, ItemNumber. All the other columns and operators are processed client side.

SELECT * FROM ItemPriceMatrices WHERE ID = "1bed306d-2da4-49b3-9023-153c3dc5b2e9"
SELECT * FROM ItemPriceMatrices WHERE ItemName = "item name"

Update¶

To update an existing item price matrix, along with its SellingPrices, we can either pass a JSON string to the SellingPrices value or use a temporary table like below. Note: this will replace all the current SellingPrices with the ones below.

INSERT INTO ItemSellingPrices#TEMP (quantityover, levelA, LevelB) VALUES (10, 11.5, 12.7)
UPDATE ItemPriceMatrices SET SellingPrices = "ItemSellingPrices#TEMP" WHERE ID = "1bed306d-2da4-49b3-9023-153c3dc5b2e9"

Columns¶

Items¶

Return, update, create and delete inventory items 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, AverageCost, BaseSellingPrice, IsActive, IsBought, IsInventoried, IsSold, Name, Number, CurrentValue, Description, UseDescription, AssetAccountID, AssetAccountDisplayID, AssetAccountName, BuyingUnitOfMeasure, BuyingItemsPerBuyingUnit, BuyingLastPurchasePrice, BuyingStandardCost, RestockingSupplierID, RestockingSupplierDisplayID, RestockingSupplierItemNumber, RestockingSupplierName, RestockingDefaultOrderQuantity, RestockingMinimumLevelForRestockingAlert, BuyingTaxCodeID, BuyingTaxCodeCode, CostOfSalesAccountID, CostOfSalesAccountDisplayID, CostOfSalesAccountName, IncomeAccountID, IncomeAccountDisplayID, IncomeAccountName, ExpenseAccountID, ExpenseAccountDisplayID, ExpenseAccountName, QuantityAvailable, QuantityCommitted, QuantityOnHand, QuantityOnOrder, SellingBaseSellingPrice, SellingCalculateSalesTaxOn, SellingIsTaxInclusive, SellingItemsPerSellingUnit, SellingUnitOfMeasure, SellingTaxCodeID, SellingTaxCodeCode. All the other columns and operators are processed client side.

SELECT * FROM Items WHERE ID = "d5ab0fd1-3bf4-4230-bbaf-90b26ea9afda" OR ID = "59aa54c8-6793-4a63-bda9-55451fa3976e"
SELECT * FROM Items WHERE AverageCost = 21.38 OR BaseSellingPrice = 11.95
SELECT * FROM Items WHERE IsActive = true

Insert¶

The following attribute is required when performing an insert: Number.

INSERT INTO Items (Number) VALUES ("429")

Columns¶

JobBudgets¶

Return and update job budgets.

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, JobID, JobName, JobNumber. All the other columns and operators are processed client side.

SELECT * FROM JobBudgets WHERE ID = "7cabea25-6250-4df5-bb37-77eb2d38f803"
SELECT * FROM JobBudgets WHERE JobName = "job name"

Update¶

To update an existing JobBudget, along with its Budgets, we can either pass a JSON string to the Budgets value or use a temporary table like below. Note: this will replace all the current Budgets with the ones below.

INSERT INTO JobBudgetItems#TEMP (Amount, AccountID) VALUES (444.21, "44a7b2bc-0a0e-4381-86fc-9b2c7a479f8f")
UPDATE JobBudgets SET Budgets = "JobBudgetItems#TEMP" WHERE ID = "992163e5-f46d-4b45-9296-965aa89a2a71"

Columns¶

Jobs¶

Return, update, create and delete a job 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, Contact, Description, StartDate, FinishDate, IsActive, IsHeader, Manager, Name, Number, PercentComplete, StartDate, TrackReimbursables, LinkedCustomerID, LinkedCustomerDisplayID, LinkedCustomerName, LinkedCustomerUri, ParentJobID, ParentJobName, ParentJobNumber, ParentJobUri. All the other columns and operators are processed client side.

SELECT * FROM Jobs WHERE ID = "797755431-e8d1-411f-9859-5ff2a54f97d9"
SELECT * FROM Jobs WHERE Number = "125" OR Name = "Supply of Coolers FH"
SELECT * FROM Jobs WHERE ParentJobNumber = "120"

Insert¶

The following attribute is required when performing an insert: Number.

INSERT INTO Jobs (Number) VALUES ("429")

Columns¶

Journals¶

Return, update, create and delete general journal transactions 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, DateOccurred, DisplayID, GSTReportingMethod, IsTaxInclusive, IsYearEndAdjustment, Memo, CategoryID, CategoryDisplayID, CategoryName, Uri. All the other columns and operators are processed client side.

SELECT * FROM Journals WHERE ID = "7cabea25-6250-4df5-bb37-77eb2d38f803"
SELECT * FROM Journals WHERE CategoryID = "6cbbea25-6256-4df4-bb37-17eb2d21f803
SELECT * FROM Journals WHERE GSTReportingMethod = "Purchase"

Insert¶

The following attribute is required when performing an insert: DateOccurred, Lines.

To insert an existing journal, along with its Lines, we can either pass a JSON string to the Lines value or use a temporary table like below.

INSERT INTO JournalItems#TEMP (AccountID, TaxcodeID, Amount, IsCredit) VALUES ("d3f55ef1-ce77-4ef2-a415-61d04db2c5fc", "e1e27d8e-f89d-451d-97c8-e0c3c1716a91", 11.23, true)
INSERT INTO JournalItems#TEMP (AccountID, TaxcodeID, Amount, IsCredit) VALUES ("d3f55ef1-ce77-4ef2-a415-61d04db2c5fc", "e1e27d8e-f89d-451d-97c8-e0c3c1716a91", 11.23, false)
INSERT INTO Journals (DateOccurred, Lines) VALUES ("2018-03-04", "JournalItems#TEMP")

Columns¶

PayrollWages¶

Return, update, create and delete payroll categories of type wage 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, Name, StpCategory, Type, WageType, PayRate, FixedHourlyRate, AutomaticallyAdjustBaseAmounts, RegularRateMultiplier, OverriddenWagesExpenseAccountID, OverriddenWagesExpenseAccountName, OverriddenWagesExpenseAccountNumber. All the other columns and operators are processed client side.

SELECT * FROM PayrollWages WHERE ID = "e57725b2-c4f0-47ce-8103-299c7a675112"
SELECT * FROM PayrollWages WHERE Name = "Base Hourly"
SELECT * FROM PayrollWages WHERE StpCategory = "NotReportable" OR Type = "Wage"

Insert¶

The following attribute is required when performing an insert: WageType, Name.

INSERT INTO PayrollWages (WageType, Name) VALUES ("Salary", "Example salary")

Columns¶

PurchaseBills¶

Return all purchase bill types 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, AppliedToDate, BalanceDueAmount, BillDeliveryStatus, Comment, Date, Freight, IsReportable, IsTaxInclusive, JournalMemo, Number, ShipToAddress, ShippingMethod, Status, Subtotal, TotalAmount, TotalTax, SupplierInvoiceNumber, CategoryID, CategoryDisplayID, CategoryName, FreightTaxCodeID, FreightTaxCodeCode, OrderID, OrderNumber, SupplierID, SupplierDisplayID, SupplierName, TermsBalanceDueDate, TermsDiscount, TermsDiscountDate, TermsDiscountExpiryDate, TermsDiscountForEarlyPayment, TermsDueDate, TermsPaymentIsDue, TermsMonthlyChargeForLatePayment. All the other columns and operators are processed client side. BillType supports only equality comparison.

SELECT * FROM PurchaseBills WHERE ID = REPLACE
SELECT * FROM PurchaseBills WHERE AppliedToDate = 20
SELECT * FROM PurchaseBills WHERE BalanceDueAmount = 45.86163291317715

Update¶

To update an existing bill, 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 PurchaseBillItems#TEMP (Total, AccountID, TaxCodeID) VALUES (444.21, "44a7b2bc-0a0e-4381-86fc-9b2c7a479f8f", "bd0f9821-4437-4ec2-9c95-c1bdbdd26c7b")
UPDATE PurchaseBills SET BillType = "Service", Lines = "PurchaseBillItems#TEMP" WHERE ID = "f979f0e7-1cd8-4fb9-b2c2-9d4546440cf7"

Insert¶

The following attributes are required when performing an insert: BillType, Date, FreightTaxCodeID, SupplierID.

INSERT INTO PurchaseBills (BillType, Date, FreightTaxCodeID, SupplierID) VALUES ("Item", "01/01/2019", "446d2226-254b-4821-acc2-7031842e0166", "9421b774-20fa-4c5f-bced-fbb6ecd42ebd")

Columns¶

PurchaseOrders¶

Return all purchase order types 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, 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"

Update¶

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"

Insert¶

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")

Columns¶

ReceivingTransactions¶

Return, update, create and delete receive money transactions 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, AmountReceived, ContactDisplayID, Date, DepositTo, IsTaxInclusive, Memo, PaymentMethod, ReceiptNumber, TotalTax, AccountID, AccountDisplayID, AccountName, CategoryID, CategoryDisplayID, CategoryName, ContactID, ContactName, ContactType. All the other columns and operators are processed client side.

SELECT * FROM ReceivingTransactions WHERE ID = "1bed306d-2da4-49b3-9023-153c3dc5b2e9"
SELECT * FROM ReceivingTransactions WHERE AmountReceived = 12.45
SELECT * FROM ReceivingTransactions WHERE Memo = "memo"

Update¶

To update an existing transaction, 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 ReceivingTransactionItems#TEMP (Amount, AccountID, TaxCodeID, RowID, RowVersion) VALUES (22.3, "44a7b2bc-0a0e-4381-86fc-9b2c7a479f8f", "bd0f9821-4437-4ec2-9c95-c1bdbdd26c7b", 656, "8733325201913151488")
UPDATE ReceivingTransactions SET PaymentMethod = "Cash", Lines = "ReceivingTransactionItems#TEMP" WHERE ID = "1bed306d-2da4-49b3-9023-153c3dc5b2e9"

Insert¶

The following attributes are required when performing an insert: Date, DepositTo, Memo, AccountID, ContactID.

INSERT INTO ReceivingTransactions (Date, DepositTo, Memo, AccountID, ContactID) VALUES ("01/01/2019", "Account", "memo", "91e0769a-bdd1-4402-8e4f-95b7743bd733", "4635fd9e-82a7-4612-ae15-32c464ef7c48")

Columns¶

SaleInvoices¶

Return all sale invoice types 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, BalanceDueAmount, Comment, Date, InvoiceDeliveryStatus, IsTaxInclusive, JournalMemo, LastPaymentDate, Number, PromisedDate, ReferralSource, ShipToAddress, ShippingMethod, Status, Subtotal, Freight, TotalTax, TotalAmount, CustomerPurchaseOrderNumber, CategoryID, CategoryDisplayID, CategoryName, SalespersonID, SalespersonDisplayID, SalespersonName, CustomerID, CustomerDisplayID, CustomerName, FreightTaxCodeID, FreightTaxCodeCode, OrderID, OrderNumber, TermsBalanceDueDate, TermsDiscount, TermsDiscountDate, TermsDiscountExpiryDate, TermsDiscountForEarlyPayment, TermsDueDate, TermsFinanceCharge, TermsMonthlyChargeForLatePayment, TermsPaymentIsDue. All the other columns and operators are processed client side. InvoiceType supports only equality comparison.

SELECT * FROM SaleInvoices WHERE ID = "fa024423-e61a-44cd-8a8b-4d52a2f9fc04"
SELECT * FROM SaleInvoices WHERE BalanceDueAmount = 24.3766206457717
SELECT * FROM SaleInvoices WHERE Comment = "commect"
SELECT * FROM SaleInvoices WHERE InvoiceType = "Service"

Update¶

To update an existing invoice, 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 InvoiceType = "Service", Lines = "PurchaseOrderItems#TEMP" WHERE ID = "992163e5-f46d-4b45-9296-965aa89a2a71"

Insert¶

The following attributes are required when performing an insert: InvoiceType, Date, CustomerID.

INSERT INTO SaleInvoices (InvoiceType, Date, CustomerID) VALUES ("item", "01/01/2019", "4635fd9e-82a7-4612-ae15-32c464ef7c48")

Columns¶