MYOB Connection Details

Introduction

Connector Version

This documentation is based on version 25.0.9368 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

OAuth application credentials are embedded 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 Jitterbit 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 supports the use of user defined views, virtual tables whose contents are decided by a pre-configured user defined query. These views are useful when you cannot directly control queries being issued to the drivers. For an overview of creating and configuring custom views, see User Defined Views.

SSL Configuration

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

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

For further information, see Query Processing.

Log

For an overview of configuration settings that can be used to refine logging, see Logging. Only two connection properties are required for basic logging, but there are numerous features that support more refined logging, which enables you to use the LogModules connection property to specify subsets of information to be logged.

User Defined Views

The MYOB connector supports the use of user defined views: user-defined virtual tables whose contents are decided by a preconfigured query. User defined views are useful in situations where you cannot directly control the query being issued to the driver; for example, when using the driver from Jitterbit.

Use a user defined view 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 so that each root element defines the name of a view, and includes 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

In order to avoid a view's name clashing with an actual entity in the data model, user defined views are exposed in the UserViews schema by default. To change the name of the schema used for UserViews, reset 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 TLS with the server. The server certificate is validated against the default system trusted certificate store. You can override how the certificate gets validated using the SSLServerCert connection property.

To specify another certificate, see the SSLServerCert connection property.

Data Model

Overview

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

The CompanyFileId connection property is required to access all tables and views, except for the CompanyFiles view, which you can use to view the company files associated with your account (and their associated IDs). Use this view if you don't know the ID of your company file.

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 criteria 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 criteria 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 criteria 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 criteria 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 criteria 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. These columns only support equality comparisons.

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 its associated addresses, either pass a JSON string to the aggregate input value or use a temporary table:

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 criteria 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
• EmployeePaymentDetailsID
• EmployeePayrollDetailsID
• EmployeeStandardPayID
• TimeBillingDetailsCostPerHour
• TimeBillingDetailsEmployeeBillingRateExcludingTax

All the other columns and operators are processed client side. These columns only support equality comparisons.

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, and IsIndividual.

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

Update

To update an existing ContactEmployee, along with its associated addresses, either pass a JSON string to the aggregate input value or use a temporary table:

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

All the other columns and operators are processed client side. These columns only support equality comparisons.

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, and IsIndividual.

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

Update

To update an existing ContactPersonal, along with its associated addresses, either pass a JSON string to the aggregate input value or use a temporary table:

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 criteria 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, 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 criteria 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. These columns only support equality comparisons.

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 its associated addresses, either pass a JSON string to the aggregate input value or use a temporary table:.

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