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
toOAuth
, - 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
:
-
Log in to your MYOB account.
-
From the Home Page, select
Developer
. -
Click
Register App
. -
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 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¶
Name | Description |
---|---|
AccountBudgets | Return and update general ledger account budgets. |
Accounts | Return, update, create and delete accounts for an AccountRight company file. |
Activities | Return, update, create and delete a list of time billing activities for an AccountRight company file. |
ActivitySlips | Return, update, create and delete a list of time billing activity slips for an AccountRight company file. |
Build | Return's Build transaction journals |
ContactCustomers | Return, update, create and delete a customer contact for an AccountRight company file. |
ContactEmployees | Return, update, create and delete an employee contact for an AccountRight company file. |
ContactPersonals | Return, update, create and delete a personal contact for an AccountRight company file. |
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. |
ContactSuppliers | Return, update, create and delete a supplier contact for an AccountRight company file. |
CreditRefunds | Return, create and delete credit notes refunded to customers for an AccountRight company file. |
CreditSettlements | Return, create and delete settled customer credits for an AccountRight company file. |
CustomerPayments | Return, create and delete customer payments for an AccountRight company file. |
DebitRefunds | Return all purchase bill types for an AccountRight company file. |
DebitSettlements | Return, create and delete debit settlements for an AccountRight company file. |
EmployeePaymentDetails | Return and update employee payment details on employee contact cards for an AccountRight company file. |
EmployeePayrollDetails | Return and update employee payroll details on employee contact cards for an AccountRight company file. |
EmployeeStandardPay | Return and update employee standard pay details on employee contact cards for an AccountRight company file. |
InventoryAdjustments | Return, update, create and delete inventory adjustments for an AccountRight company file. |
ItemPriceMatrices | Return and update the item price matrix for multiple customer selling prices. |
Items | Return, update, create and delete inventory items for an AccountRight company file. |
JobBudgets | Return and update job budgets. |
Jobs | Return, update, create and delete a job for an AccountRight company file. |
Journals | Return, update, create and delete general journal transactions for an AccountRight company file. |
PayrollWages | Return, update, create and delete payroll categories of type wage for an AccountRight company file. |
PurchaseBills | Return all purchase bill types for an AccountRight company file. |
PurchaseOrders | Return all purchase order types for an AccountRight company file. |
ReceivingTransactions | Return, update, create and delete receive money transactions for an AccountRight company file. |
SaleInvoices | Return all sale invoice types for an AccountRight company file. |
SaleOrders | Returns all sale order types for an AccountRight company file. |
SaleQuotes | Return all sale quote types for an AccountRight company file. |
SpendingTransactions | Return, update, create and delete spend money transactions for an AccountRight company file. |
SupplierPayments | Return, create and delete supplier payments for an AccountRight company file. |
TaxCodes | Tax codes for an AccountRight company file. |
TimesheetLineEntries | Return timesheet entries for an AccountRight company file |
TimesheetLineItems | Return timesheet entries for an AccountRight company file |
Timesheets | Return timesheet entries for an AccountRight company file |
TransferringTransactions | Return, update, create and delete transfer money transactions for an AccountRight company file. |
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¶
Name | Type | ReadOnly | Description |
---|---|---|---|
FinancialYear | Integer | False | Financial year can consist of current FY or next FY only. |
LastMonthInFinancialYear | Integer | True | Number representing the last month of the financial year. For example, 3 indicates March. |
URI | String | True | Uniform resource identifier encompasses all types of names and addresses that refer to objects on the web. |
Budgets | String | False | An array of account budget information. |
CompanyFileId | String | True | The ID of the company file. Takes precedence over the CompanyFileId connection property. |
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¶
Name | Type | ReadOnly | Description |
---|---|---|---|
ID [KEY] | Uuid | False | Unique identifier in the form of a guid. |
Classification | String | False | The account classification can be one of the following: (1) Asset, (2) Liability, (3) Equity, (4) Income, (5) CostOfSales, (6) Expense, (8) OtherIncome, (9) OtherExpense. |
CurrentBalance | Decimal | True | Current balance of the account. Note that this balance will include all future-dated activity. |
Description | String | False | A description of the account. |
DisplayID | String | False | Account code format includes separator ie 1-1100 |
IsActive | Boolean | False | True indicates the account is active. False indicates the account is inactive. A user marks an account as inactive when they no longer need to record transactions to it |
IsHeader | Boolean | False | True indicates the account is a header account.Header accounts are used to organise, group and subtotal accounts in the Accounts List and reports. False indicates the account is a detail account. Only detail accounts can be assigned to transactions. |
Level | Integer | False | The hierarchial level of the account in the Accounts List. Possible values are 1, 2, 3, 4. The highest level accounts are level 1, the lowest 4. You can only assign levels 2 to 4 to a new account. |
Name | String | False | Name of the account. |
Number | Integer | False | Account number for example 1150. Must be a unique four-digit number that does not include the account type classification number and account separator. |
OpeningBalance | Decimal | False | Balance of the account as at the conversion date set for the company file. |
Type | String | False | Depending on the classification of the account (e.g. asset), you can define the account's type, see here for more information on account types. |
LastReconciledDate | Datetime | True | Shows the date that the Account was last reconciled. This will return as null if the Account has never been reconciled. |
URI | String | True | Uniform resource identifier encompasses all types of names and addresses that refer to objects on the web. |
RowVersion | String | True | Number value that changes upon a record update, can be used for change control but does does not preserve a date or a time. |
BankingBSBNumber | String | False | BSB as provided by the financial institution. |
BankingAccountName | String | False | Bank account name setup. |
BankingAccountNumber | Integer | False | Account number as provided by the financial institution. |
BankingCode | String | False | Bank code as provided by the financial institution. |
BankingCompanyTradingName | String | False | Company trading name if applicable for bank account. |
BankingCreateBankFiles | Boolean | False | True indicates the bank account will be used to create bank files (ABA). False indicates the bank account will not be used to create bank files. |
BankingDirectEntryUserId | String | False | Direct entry user ID as provided by the financial institution. |
BankingIncludeSelfBalancingTransaction | Boolean | False | True indicates the bank account requires a self balancing transaction. False indicates the bank account does not require a self balancing transaction. |
BankingStatementParticulars | String | False | Statement particulars assigned to the bank account. |
ForeignCurrencyID | Uuid | True | Unique identifier in the form of a guid. |
ForeignCurrencyCode | String | True | The currency code. |
ForeignCurrencyCurrencyName | String | True | The full name of the currency. |
ForeignCurrencyURI | String | True | Uniform resource identifier associated with the purchase. |
ParentAccountID | Uuid | False | Unique identifier for the account in the form of a guid. |
ParentAccountDisplayID | String | True | Account code. Format includes separator ie 4-1100 |
ParentAccountName | String | True | Name of the account. |
ParentAccountURI | String | True | Uniform resource identifier associated with the account object. |
TaxCodeID | Uuid | False | Unique guid identifier belonging to the assigned tax code. |
TaxCodeCode | String | True | 3 digit tax code. |
TaxCodeURI | String | True | Uniform resource identifier associated with the tax code object. |
CompanyFileId | String | True | The ID of the company file. Takes precedence over the CompanyFileId connection property. |
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¶
Name | Type | ReadOnly | Description |
---|---|---|---|
ID [KEY] | Uuid | True | Unique identifier in the form of a guid. |
Description | String | False | Description of the object. |
DisplayID | String | False | Display ID for the object. |
IsActive | Boolean | False | True or false. |
Name | String | False | Name of the object. |
Status | String | False | Can consist of the following: Chargeable used to include on time billing invoices. NonChargeable used when not charging customers but still wanting to include on activity slip. |
Type | String | False | Hourly or NonHourly. |
UnitOfMeasurement | String | False | Hourly - If Type = Hourly then UnitOfMeasurement = Hour. NonHourly - specify the type of measurement |
IncomeAccountID | Uuid | False | Unique identifier for the account in the form of a guid. |
IncomeAccountDisplayID | String | True | Account code. Format includes separator ie 4-1100 |
IncomeAccountName | String | True | Name of the account. |
IncomeAccountURI | String | True | Uniform resource identifier associated with the account object. |
ChargeableRate | String | True | Rate which can consit of the following: EmployeeBillingRate - sourced from the employee contact card. CustomerBillingRate - sourced from the customer contact card. ActivityRate - entered on activity and is exclusive of tax. |
UseDescriptionOnSales | Boolean | True | True or false. |
TaxCodeID | Uuid | False | Unique guid identifier belonging to the assigned tax code. |
TaxCodeCode | String | True | 3 digit tax code. |
TaxCodeURI | String | True | Uniform resource identifier associated with the tax code object. |
URI | String | True | Uniform resource identifier encompasses all types of names and addresses that refer to objects on the web. |
RowVersion | String | True | Number value that changes upon a record update, can be used for change control but does does not preserve a date or a time. |
CompanyFileId | String | True | The ID of the company file. Takes precedence over the CompanyFileId connection property. |
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¶
Name | Type | ReadOnly | Description |
---|---|---|---|
ID [KEY] | Uuid | True | Unique identifier in the form of a guid. |
DisplayID | String | False | Display ID for the object. |
Date | Datetime | False | The date of the activity slip. |
ProviderID | Uuid | True | Unique provider identifier in the form of a guid. |
ProviderDisplayID | String | False | Display ID for the provider. |
ProviderType | String | True | Type of the provider. |
ProviderName | String | True | Name of the provider. |
ProviderURI | String | True | Uniform resource identifier associated with the provider object. |
CustomerID | Uuid | True | Unique customer identifier in the form of a guid. |
CustomerDisplayID | String | False | Display ID for the customer. |
CustomerName | String | True | Name of the customer. |
CustomerURI | String | True | Uniform resource identifier associated with the customer object. |
ActivityID | Uuid | True | Unique activity identifier in the form of a guid. |
ActivityName | String | True | Name of the activity. |
ActivityURI | String | True | Uniform resource identifier associated with the activity object. |
JobID | Uuid | True | Unique job identifier in the form of a guid. |
JobNumber | String | True | Display ID for the job. |
JobName | String | True | Name of the job. |
JobURI | String | True | Uniform resource identifier associated with the job object. |
UnitCount | Decimal | False | Number of (time) units that the Activity will be billed for (in hours). |
Rate | Decimal | False | The rate that the Activity will be billed at on this Activity Slip. |
AdjustmentAmount | Decimal | False | The Adjustment of how much to bill the customer for in Currency. |
AlreadyBilledAmount | Decimal | False | The Adjustment of how much to bill the customer for in Billing Units. |
AdjustmentCount | Decimal | False | The Adjustment of how much to bill the customer for in Billing Units. |
AlreadyBilledCount | Decimal | False | The amount of Billing Units already billed. |
Notes | String | False | Any notes associated with this Activity Slip. |
StartStopDescription | String | False | The description of the number of hours the Employee spent on this date for each Payroll Category or Activity. |
StartTime | Datetime | False | The exact time this Activity was started. |
EndTime | Datetime | False | The exact time this Activity was ended. |
ElapsedTime | Integer | False | The Elapsed Time for the Activity in Seconds. |
HourlySalaryPayrollCategoryID | Uuid | False | Unique HourlySalaryPayrollCategory identifier in the form of a guid. |
HourlySalaryPayrollCategoryType | String | True | Type of the HourlySalaryPayrollCategory. |
HourlySalaryPayrollCategoryName | String | True | Name of the HourlySalaryPayrollCategory. |
HourlySalaryPayrollCategoryURI | String | True | Uniform resource identifier associated with the HourlySalaryPayrollCategory object. |
PaidToEmployeeAmountDecimal | Decimal | False | The Amount already paid to the Employee for this Activity Slip. |
LastModified | Datetime | False | DateTime of the last time this resource was modified through a direct action to the object. eg a field was updated. |
URI | String | True | Uniform resource identifier encompasses all types of names and addresses that refer to objects on the web. |
RowVersion | String | True | Number value that changes upon a record update, can be used for change control but does does not preserve a date or a time. |
CompanyFileId | String | True | The ID of the company file. Takes precedence over the CompanyFileId connection property. |
Build¶
Return's Build transaction journals
Columns¶
Name | Type | ReadOnly | Description |
---|---|---|---|
ID [KEY] | Uuid | False | Unique guid identifier created for the Build adjustment journal. |
CategoryUID | String | False | Unique guid identifier belonging to the category assigned to the inventory journal. |
CategoryDisplayID | String | False | Display ID for the category |
CategoryName | String | False | Name of the category |
CategoryURI | String | False | Uniform resource identifier associated with the category object |
Date | Datetime | False | Transaction date entry, format YYYY-MM-DD HH:MM:SS |
InventoryJournalNumber | String | False | Inventory journal number, if left null when posting a number will automatically be assigned and incremented based upon last recorded |
Lines | String | False | An array of journal line information |
Memo | String | False | Memo text describing the transaction line |
RowVersion | String | False | ONLY required on PUT for updating an existing inventory adjustment line.NOT required when creating a new inventory adjustment journal. |
URI | String | False | Uniform resource identifier encompasses all types of names and addresses that refer to objects on the web |
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¶
Name | Type | ReadOnly | Description |
---|---|---|---|
ID [KEY] | Uuid | True | Unique identifier in the form of a guid. |
CurrentBalance | Decimal | True | Contact balance. |
FirstName | String | False | Contact first name. |
LastName | String | False | Contact last name. |
CompanyName | String | False | Contact company name. |
IsActive | Boolean | False | True indicates the employee contact is active. False indicates the employee contact is inactive. |
IsIndividual | Boolean | False | True indicates the employee contact represents an individual. False indicates the employee contact represents a company. |
LastModified | String | True | Extracts the last modification date for the contact resource. |
Notes | String | False | Notes for the contact. |
PhotoURI | String | False | Uniform resource identifier associated with a photo image. |
URI | String | True | Uniform resource identifier encompasses all types of names and addresses that refer to objects on the web. |
DisplayID | String | False | Display ID for the contact card. |
SellingABN | Sring | False | ABN Number (Must be 11 digits and formatted as XX XXX XXX XXX). |
SellingABNBranch | String | False | ABN branch number. |
SellingCreditAvailable | Integer | True | Credit available. |
SellingCreditLimit | Integer | False | Credit limit. |
SellingCreditOnHold | Boolean | False | Credit on hold. |
SellingCreditPastDue | Integer | True | Past due balance. |
SellingFreightTaxCodeCode | String | True | 3 digit tax code. |
SellingFreightTaxCodeID | Uuid | False | Unique guid identifier belonging to the assigned tax code. |
SellingFreightTaxCodeURI | String | True | Uniform resource identifier associated with the tax code object. |
SellingHourlyBillingRate | Double | False | The customers hourly billing rate. |
SellingInvoiceDelivery | String | False | Default invoice delivery status assigned. |
SellingItemPriceLevel | String | True | Item price level, can consist of the following: Base Selling Price, Level A, Level B, Level C, Level D, Level E, Level F. |
SellingMemo | String | False | Default memo text. |
SellingPrintedForm | String | False | Named form selected as default printed form. |
SellingReceiptMemo | String | False | Default receipt memo. |
SellingSaleComment | String | False | Default selected sale comment. |
SellingSaleLayout | String | False | Sale Type of the record containing the full default Sale Layout definition: NoDefault, Service, Item, Professional, TimeBilling, Miscellaneous. |
SellingShippingMethod | String | False | Shipping method text. |
SellingTaxCodeCode | String | True | 3 digit tax code. |
SellingTaxCodeID | Uuid | True | Unique guid identifier belonging to the assigned tax code. |
SellingTaxCodeURI | String | True | Uniform resource identifier associated with the tax code object. |
SellingTermsBalanceDueDate | Integer | True | The date of the entry. |
SellingTermsDiscountDate | Integer | True | The date of the entry. |
SellingTermsDiscountForEarlyPayment | Double | True | % discount for early payment. |
SellingTermsMonthlyChargeForLatePayment | Double | True | % monthly charge for late payment. |
SellingTermsPaymentIsDue | String | True | Default Terms of Payment definitions: CashOnDelivery, PrePaid, InAGivenNumberOfDays, OnADayOfTheMonth, NumberOfDaysAfterEOM, DayOfMonthAfterEOM. |
SellingTermsVolumeDiscount | Integer | True | Volume supplier discount. |
SellingUseCustomerTaxCode | Boolean | True | True indicates to use the customer tax code. False indicates do not use the customer tax code. |
SellingTaxIdNumber | String | False | Tax ID number. |
SellingIncomeAccountId | Uuid | True | Unique identifier for the income account in the form of a guid. |
SellingIncomeAccountName | String | True | Name of the income account. |
SellingIncomeAccountDisplayId | String | True | Income account code format includes separator ie 4-1100. |
SellingIncomeAccountURI | String | True | Uniform resource identifier associated with the income account object. |
SellingSalesPersonId | Uuid | True | Unique employee contact identifier in the form of a guid. |
SellingSalesPersonName | String | True | Selected employee contact name. |
SellingSalesPersonDisplayId | String | True | Employee contact Card ID, can also be used as a unique employee contact identifier. |
SellingSalesPersonURI | String | True | Uniform resource identifier associated with the employee contact object. |
PaymentMethod | String | False | Payment methods must exist in company file, default methods consist of the following: American Express, Bank Card, Barter Card, Cash, Cheque, Diners Club, EFTPOS, MasterCard, Money Order, Other, Visa. |
PaymentCardNumber | String | False | Last 4 digits only. |
PaymentNameOnCard | String | False | Default name on card. |
PaymentNotes | String | False | Default payment notes. |
PaymentBSBNumber | String | False | Default bank account bsb number. |
PaymentBankAccountName | String | False | Default bank account name. |
PaymentBankAccountNumber | String | False | formatted (XX-XXXX-XXXXXXX-XX). |
ForeignCurrencyId | Uuid | True | This is an AccountRight only field. Foreign Key: Unique identifier for the currency in the form of a guid. |
ForeignCurrencyCode | String | True | This is an AccountRight only field.The currency code. |
ForeignCurrencyName | String | True | This is an AccountRight only field. The full name of the currency. |
ForeignCurrencyURI | String | True | This is an AccountRight only field. Uniform resource identifier associated with the currency object. |
Identifiers | String | True | Contact Identifiers Details. |
CustomField1Label | String | True | Custom Field 1 label. |
CustomField1Value | String | True | Custom Field 1 value. |
CustomField2Label | String | True | Custom Field 2 label. |
CustomField2Value | String | True | Custom Field 2 value. |
CustomField3Label | String | True | Custom Field 3 label. |
CustomField3Value | String | True | Custom Field 3 value. |
CustomList1Label | String | True | Custom List 1 label. |
CustomList1Value | String | True | Custom List 1 value. |
CustomList2Label | String | True | Custom List 2 label. |
CustomList2Value | String | True | Custom List 2 value. |
CustomList3Label | String | True | Custom List 3 label. |
CustomList3Value | String | True | Custom List 3 value. |
Addresses | String | False | The following set of information pulls through details for a contact address. |
RowVersion | String | False | Number value that changes upon a record update, can be used for change control but does does not preserve a date or a time. |
CompanyFileId | String | True | The ID of the company file. Takes precedence over the CompanyFileId connection property. |
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:
------------------------ | ------------------------ | --------------------- | ----------------------------- | ------------------------------------------------- |
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. 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¶
Name | Type | ReadOnly | Description |
---|---|---|---|
ID [KEY] | Uuid | True | Unique identifier in the form of a guid. |
CurrentBalance | Decimal | True | Contact balance. |
FirstName | String | False | Contact first name. |
LastName | String | False | Contact last name. |
CompanyName | String | False | Contact company name. |
IsActive | Boolean | False | True indicates the employee contact is active. False indicates the employee contact is inactive. |
IsIndividual | Boolean | False | True indicates the employee contact represents an individual. False indicates the employee contact represents a company. |
LastModified | String | True | Extracts the last modification date for the contact resource. |
Notes | String | False | Notes for the contact. |
URI | String | True | Uniform resource identifier encompasses all types of names and addresses that refer to objects on the web. |
PhotoURI | String | False | Uniform resource identifier associated with a photo image. |
DisplayID | String | False | Display ID for the contact card. |
EmployeePaymentDetailsID | Uuid | True | Unique identifier in the form of a guid. |
EmployeePaymentDetailsURI | String | True | Uniform resource identifier encompasses all types of names and addresses that refer to objects on the web. |
EmployeePayrollDetailsID | Uuid | True | Unique identifier in the form of a guid. |
EmployeePayrollDetailsURI | String | True | Uniform resource identifier encompasses all types of names and addresses that refer to objects on the web. |
EmployeeStandardPayID | Uuid | True | Unique identifier in the form of a guid. |
EmployeeStandardPayURI | String | True | Uniform resource identifier encompasses all types of names and addresses that refer to objects on the web. |
TimeBillingDetailsCostPerHour | Double | True | Hourly cost of employee to business. |
TimeBillingDetailsEmployeeBillingRateExcludingTax | Double | True | Hourly billing rate for services provided by employee |
Identifiers | String | True | Contact Identifiers Details. |
CustomField1Label | String | True | Custom Field 1 label. |
CustomField1Value | String | True | Custom Field 1 value. |
CustomField2Label | String | True | Custom Field 2 label. |
CustomField2Value | String | True | Custom Field 2 value. |
CustomField3Label | String | True | Custom Field 3 label. |
CustomField3Value | String | True | Custom Field 3 value. |
CustomList1Label | String | True | Custom List 1 label. |
CustomList1Value | String | True | Custom List 1 value. |
CustomList2Label | String | True | Custom List 2 label. |
CustomList2Value | String | True | Custom List 2 value. |
CustomList3Label | String | True | Custom List 3 label. |
CustomList3Value | String | True | Custom List 3 value. |
Addresses | String | False | The following set of information pulls through details for a contact address. |
RowVersion | String | False | Number value that changes upon a record update, can be used for change control but does does not preserve a date or a time. |
CompanyFileId | String | True | The ID of the company file. Takes precedence over the CompanyFileId connection property. |
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:
-------- | -------------- | ------------ | -------- | ----------- |
ID | CurrentBalance | FirstName | LastName | CompanyName |
IsActive | IsIndividual | LastModified | Notes | DisplayID |
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¶
Name | Type | ReadOnly | Description |
---|---|---|---|
ID [KEY] | Uuid | True | Unique identifier in the form of a guid. |
CurrentBalance | Decimal | True | Contact balance. |
FirstName | String | False | Contact first name. |
LastName | String | False | Contact last name. |
CompanyName | String | False | Contact company name. |
IsActive | Boolean | False | True indicates the employee contact is active. False indicates the employee contact is inactive. |
IsIndividual | Boolean | False | True indicates the employee contact represents an individual. False indicates the employee contact represents a company. |
LastModified | String | True | Extracts the last modification date for the contact resource. |
Notes | String | False | Notes for the contact. |
URI | String | True | Uniform resource identifier encompasses all types of names and addresses that refer to objects on the web. |
PhotoURI | String | False | Uniform resource identifier associated with a photo image. |
DisplayID | String | False | Display ID for the contact card. |
Identifiers | String | True | Contact Identifiers Details. |
CustomField1Label | String | True | Custom Field 1 label. |
CustomField1Value | String | True | Custom Field 1 value. |
CustomField2Label | String | True | Custom Field 2 label. |
CustomField2Value | String | True | Custom Field 2 value. |
CustomField3Label | String | True | Custom Field 3 label. |
CustomField3Value | String | True | Custom Field 3 value. |
CustomList1Label | String | True | Custom List 1 label. |
CustomList1Value | String | True | Custom List 1 value. |
CustomList2Label | String | True | Custom List 2 label. |
CustomList2Value | String | True | Custom List 2 value. |
CustomList3Label | String | True | Custom List 3 label. |
CustomList3Value | String | True | Custom List 3 value. |
Addresses | String | False | The following set of information pulls through details for a contact address. |
RowVersion | String | False | Number value that changes upon a record update, can be used for change control but does does not preserve a date or a time. |
CompanyFileId | String | True | The ID of the company file. Takes precedence over the CompanyFileId connection property. |
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¶
Name | Type | ReadOnly | Description |
---|---|---|---|
ID [KEY] | Uuid | True | Unique identifier in the form of a guid. |
CurrentBalance | Decimal | False | Contact balance. |
FirstName | String | False | Contact first name. |
LastName | String | False | Contact last name. |
CompanyName | String | False | Contact company name. |
IsActive | Boolean | False | True indicates the employee contact is active. False indicates the employee contact is inactive. |
IsIndividual | Boolean | False | True indicates the employee contact represents an individual. False indicates the employee contact represents a company. |
LastModified | String | False | Extracts the last modification date for the contact resource. |
Notes | String | False | Notes for the contact. |
PhotoURI | String | False | Uniform resource identifier associated with a photo image. |
Type | String | False | Contact type. One of: Customer,Supplier,Personal,Employee. |
DisplayID | String | False | Display ID for the contact card. |
SellingABN | String | True | ABN Number (Must be 11 digits and formatted as XX XXX XXX XXX). |
SellingABNBranch | String | True | ABN branch number. |
SellingCreditAvailable | Integer | True | Credit available. |
SellingCreditLimit | Integer | True | Credit limit. |
SellingCreditOnHold | Boolean | True | Credit on hold. |
SellingCreditPastDue | Integer | True | Past due balance. |
SellingFreightTaxCodeCode | String | True | 3 digit tax code. |
SellingFreightTaxCodeID | Uuid | True | Unique guid identifier belonging to the assigned tax code. |
SellingFreightTaxCodeURI | String | True | Uniform resource identifier associated with the tax code object. |
SellingHourlyBillingRate | Double | True | The customers hourly billing rate. |
SellingInvoiceDelivery | String | True | Default invoice delivery status assigned. |
SellingItemPriceLevel | String | True | Item price level, can consist of the following: Base Selling Price, Level A, Level B, Level C, Level D, Level E, Level F. |
SellingMemo | String | True | Default memo text. |
SellingPrintedForm | String | True | Named form selected as default printed form. |
SellingReceiptMemo | String | True | Default receipt memo. |
SellingSaleComment | String | True | Default selected sale comment. |
SellingSaleLayout | String | True | Sale Type of the record containing the full default Sale Layout definition: NoDefault, Service, Item, Professional, TimeBilling, Miscellaneous. |
SellingShippingMethod | String | True | Shipping method text. |
SellingTaxCodeCode | String | True | 3 digit tax code. |
SellingTaxCodeID | Uuid | True | Unique guid identifier belonging to the assigned tax code. |
SellingTaxCodeURI | String | True | Uniform resource identifier associated with the tax code object. |
SellingTermsBalanceDueDate | Integer | True | The date of the entry. |
SellingTermsDiscountDate | Integer | True | The date of the entry. |
SellingTermsDiscountForEarlyPayment | Double | True | % discount for early payment. |
SellingTermsMonthlyChargeForLatePayment | Double | True | % monthly charge for late payment. |
SellingTermsPaymentIsDue | String | True | Default Terms of Payment definitions: CashOnDelivery, PrePaid, InAGivenNumberOfDays, OnADayOfTheMonth, NumberOfDaysAfterEOM, DayOfMonthAfterEOM. |
SellingTermsVolumeDiscount | Integer | True | Volume supplier discount. |
SellingUseCustomerTaxCode | Boolean | True | True indicates to use the customer tax code. False indicates do not use the customer tax code. |
BuyingABN | String | True | ABN Number (Must be 11 digits and formatted as XX XXX XXX XXX). |
BuyingABNBranch | String | True | ABN branch number. |
BuyingCostPerHour | Double | True | Cost per hour of providing the suppliers services when generating an activity slip. |
BuyingCreditAvailable | Double | True | Credit availiable. |
BuyingCreditLimit | Double | True | Credit limit. |
BuyingCreditPastDue | Double | True | Past due balance. |
BuyingExpenseAccountDisplayID | String | True | Account code. Format includes separator ie 4-1100 |
BuyingExpenseAccountID | Uuid | True | Unique identifier for the account in the form of a guid. |
BuyingExpenseAccountName | String | True | Name of the account. |
BuyingExpenseAccountURI | String | True | Uniform resource identifier associated with the account object. |
BuyingFreightTaxCodeCode | String | True | 3 digit tax code. |
BuyingFreightTaxCodeID | Uuid | True | Unique guid identifier belonging to the assigned tax code. |
BuyingFreightTaxCodeURI | String | True | Uniform resource identifier associated with the tax code object. |
BuyingIsReportable | Boolean | True | True indicates the supplier contact is setup for reportable taxable payments. False indicates the supplier contact is not setup for reportable taxable payments. |
BuyingPaymentMemo | String | True | Default payment memo. |
BuyingPrintedForm | String | True | Named form selected as default printed form. |
BuyingPurchaseComment | String | True | Default selected purchase comment. |
BuyingPurchaseLayout | String | True | Purchase type of the record containing the full default purchase layout definition: NoDefault, Service, Item, Professional, Miscellaneous. |
BuyingPurchaseOrderDelivery | String | True | Default supplier delivery status. |
BuyingShippingMethod | String | True | Shipping method text. |
BuyingSupplierBillingRate | Double | True | The suppliers hourly billing rate exclusive of tax. |
BuyingTaxCodeCode | String | True | 3 digit tax code. |
BuyingTaxCodeID | Uuid | True | Unique guid identifier belonging to the assigned tax code. |
BuyingTaxCodeURI | String | True | Uniform resource identifier associated with the tax code object. |
BuyingTermsBalanceDueDate | Integer | True | If PaymentIsDue = CashOnDelivery, PrePaid, InAGivenNumberOfDaysNumber or NumberOfDaysAfterEOM then set the number of days within which payments must be made. If PaymentIsDue = OnADayOfTheMonth or DayOfMonthAfterEOM then set the day of the month within which payments must be made. |
BuyingTermsDiscountDate | Integer | True | If PaymentIsDue = CashOnDelivery, PrePaid, InAGivenNumberOfDaysNumber or NumberOfDaysAfterEOM then set the number of days within which payments must be made to receive discounts. If PaymentIsDue = OnADayOfTheMonth or DayOfMonthAfterEOM then set the day of the month within which the payment must be made to receive discounts. |
BuyingTermsDiscountForEarlyPayment | Double | True | % discount for early payment. |
BuyingTermsPaymentIsDue | String | True | Default Terms of Payment definitions: CashOnDelivery, PrePaid, InAGivenNumberOfDays, OnADayOfTheMonth, NumberOfDaysAfterEOM, DayOfMonthAfterEOM. |
BuyingTermsVolumeDiscount | Double | True | Volume discount. |
BuyingUseSupplierTaxCode | Boolean | True | True or false. |
PaymentBSBNumber | String | True | Default bank account bsb number. |
PaymentBankAccountName | String | True | Default bank account name. |
PaymentBankAccountNumber | String | True | formatted (XX-XXXX-XXXXXXX-XX). |
PaymentLastModified | String | True | Extracts the last modification date for the contact resource. |
PaymentPhotoURI | String | True | Uniform resource identifier associated with a photo image. |
PaymentRefundCardNumber | String | True | Last 4 digits only. |
PaymentRefundNameOnCard | String | True | Default name on card. |
PaymentRefundNotes | String | True | Default refund payment notes. |
PaymentRefundPaymentMethod | String | True | Payment method on refund, must exist in company file, default methods consist of the following: American Express, Bank Card, Barter Card, Cash, Cheque, Diners Club, EFTPOS, MasterCard, Money Order, Other, Visa. |
PaymentRowVersion | String | True | Number value that changes upon a record update, can be used for change control but does does not preserve a date or a time. |
PaymentStatementText | String | True | Default statement text. |
PaymentURI | String | True | Uniform resource identifier encompasses all types of names and addresses that refer to objects on the web. |
EmployeePaymentDetailsID | Uuid | True | Unique identifier in the form of a guid. |
EmployeePaymentDetailsURI | String | True | Uniform resource identifier encompasses all types of names and addresses that refer to objects on the web. |
EmployeePayrollDetailsID | Uuid | True | Unique identifier in the form of a guid. |
EmployeePayrollDetailsURI | String | True | Uniform resource identifier encompasses all types of names and addresses that refer to objects on the web. |
EmployeeStandardPayID | Uuid | True | Unique identifier in the form of a guid. |
EmployeeStandardPayURI | String | True | Uniform resource identifier encompasses all types of names and addresses that refer to objects on the web. |
TimeBillingDetailsCostPerHour | Double | True | Hourly cost of employee to business. |
TimeBillingDetailsEmployeeBillingRateExcludingTax | Double | True | Hourly billing rate for services provided by employee |
Identifiers | String | True | Contact Identifiers Details. |
CustomField1Label | String | True | Custom Field 1 label. |
CustomField1Value | String | True | Custom Field 1 value. |
CustomField2Label | String | True | Custom Field 2 label. |
CustomField2Value | String | True | Custom Field 2 value. |
CustomField3Label | String | True | Custom Field 3 label. |
CustomField3Value | String | True | Custom Field 3 value. |
CustomList1Label | String | True | Custom List 1 label. |
CustomList1Value | String | True | Custom List 1 value. |
CustomList2Label | String | True | Custom List 2 label. |
CustomList2Value | String | True | Custom List 2 value. |
CustomList3Label | String | True | Custom List 3 label. |
CustomList3Value | String | True | Custom List 3 value. |
Addresses | String | False | The following set of information pulls through details for a contact address. |
RowVersion | String | False | Number value that changes upon a record update, can be used for change control but does does not preserve a date or a time. |
CompanyFileId | String | True | The ID of the company file. Takes precedence over the CompanyFileId connection property. |
ContactSuppliers¶
Return, update, create and delete a supplier contact for an AccountRight company file.
Table Specific Information¶
Select¶
MYOB allows a subset of columns to be used in the WHERE clause of a SELECT query. These columns may be used with the following operators: =, !=, >, <, >=, <=. In addition, queries with multiple criterias can be executed by combining the criteria with AND and OR operators. These columns support the above comparison types for server side filtering:
------------------------- | ----------------------------- | ----------------------- | ---------------------------------- | ------------------------ |
ID | CurrentBalance | FirstName | LastName | CompanyName |
IsActive | IsIndividual | LastModified | Notes | DisplayID |
BuyingABN | BuyingABNBranch | BuyingCostPerHour | BuyingCreditAvailable | BuyingCreditLimit |
BuyingCreditPastDue | BuyingExpenseAccountDisplayID | BuyingExpenseAccountID | BuyingExpenseAccountName | BuyingFreightTaxCodeCode |
BuyingFreightTaxCodeID | BuyingIsReportable | BuyingPaymentMemo | BuyingPrintedForm | BuyingPurchaseComment |
BuyingPurchaseLayout | BuyingPurchaseOrderDelivery | BuyingShippingMethod | BuyingSupplierBillingRate | BuyingTaxCodeCode |
BuyingTaxCodeID | BuyingTermsBalanceDueDate | BuyingTermsDiscountDate | BuyingTermsDiscountForEarlyPayment | BuyingTermsPaymentIsDue |
BuyingTermsVolumeDiscount | BuyingUseSupplierTaxCode | PaymentBSBNumber | PaymentBankAccountName | PaymentBankAccountNumber |
PaymentRefundCardNumber | PaymentRefundNameOnCard | PaymentRefundNotes | PaymentRefundPaymentMethod | PaymentStatementText |
All the other columns and operators are processed client side. Type supports only equality comparison.
SELECT * FROM ContactSuppliers
SELECT * FROM ContactSuppliers WHERE ID = "1bed306d-2da4-49b3-9023-153c3dc5b2e9"
SELECT * FROM ContactSuppliers WHERE FirstName = "James" OR FirstName = "John"
Insert¶
The following attributes are required when performing an insert: CompanyName (if IsIndividual = false), FirstName, LastName, IsIndividual, BuyingTaxCodeID and BuyingFreightTaxCodeID.
INSERT INTO ContactSuppliers (FirstName, LastName, IsIndividual, BuyingTaxCodeID, BuyingFreightTaxCodeID) VALUES ("James", "Bond", true, "446d2226-254b-4821-acc2-7031842e0166", "446d2226-254b-4821-acc2-7031842e0166")
Update¶
To update an existing ContactSupplier, along with the addresses, we can either pass a JSON string to the aggregate input value or use a temporary table like below.
INSERT INTO ContactAddressItems#TEMP (City, Country) VALUES ("Paris", "France")
UPDATE ContactSuppliers SET FirstName = "James", Addresses = "ContactAddressItems#TEMP" WHERE ID = "1bed306d-2da4-49b3-9023-153c3dc5b2e9"
Delete¶
You must specify the ID of the ContactSupplier to delete it.
DELETE FROM ContactSuppliers WHERE ID = '35fe2eaa-d941-4312-a427-bd4281670c75'
Columns¶
Name | Type | ReadOnly | Description |
---|---|---|---|
ID [KEY] | Uuid | True | Unique identifier in the form of a guid. |
CurrentBalance | Decimal | True | Contact balance. |
FirstName | String | False | Contact first name. |
LastName | String | False | Contact last name. |
CompanyName | String | False | Contact company name. |
IsActive | Boolean | False | True indicates the employee contact is active. False indicates the employee contact is inactive. |
IsIndividual | Boolean | False | True indicates the employee contact represents an individual. False indicates the employee contact represents a company. |
LastModified | String | True | Extracts the last modification date for the contact resource. |
Notes | String | False | Notes for the contact. |
PhotoURI | String | False | Uniform resource identifier associated with a photo image. |
URI | String | True | Uniform resource identifier encompasses all types of names and addresses that refer to objects on the web. |
DisplayID | String | False | Display ID for the contact card. |
BuyingABN | String | False | ABN Number (Must be 11 digits and formatted as XX XXX XXX XXX). |
BuyingABNBranch | String | False | ABN branch number. |
BuyingCostPerHour | Double | False | Cost per hour of providing the suppliers services when generating an activity slip. |
BuyingCreditAvailable | Double | True | Credit availiable. |
BuyingCreditLimit | Double | False | Credit limit. |
BuyingCreditPastDue | Double | True | Past due balance. |
BuyingExpenseAccountDisplayID | String | True | Account code. Format includes separator ie 4-1100 |
BuyingExpenseAccountID | Uuid | False | Unique identifier for the account in the form of a guid. |
BuyingExpenseAccountName | String | True | Name of the account. |
BuyingExpenseAccountURI | String | True | Uniform resource identifier associated with the account object. |
BuyingFreightTaxCodeCode | String | True | 3 digit tax code. |
BuyingFreightTaxCodeID | Uuid | False | Unique guid identifier belonging to the assigned tax code. |
BuyingFreightTaxCodeURI | String | True | Uniform resource identifier associated with the tax code object. |
BuyingIsReportable | Boolean | False | True indicates the supplier contact is setup for reportable taxable payments. False indicates the supplier contact is not setup for reportable taxable payments. |
BuyingPaymentMemo | String | False | Default payment memo. |
BuyingPrintedForm | String | False | Named form selected as default printed form. |
BuyingPurchaseComment | String | False | Default selected purchase comment. |
BuyingPurchaseLayout | String | False | Purchase type of the record containing the full default purchase layout definition: NoDefault, Service, Item, Professional, Miscellaneous. |
BuyingPurchaseOrderDelivery | String | False | Default supplier delivery status. |
BuyingShippingMethod | String | False | Shipping method text. |
BuyingSupplierBillingRate | Double | False | The suppliers hourly billing rate exclusive of tax. |
BuyingTaxIdNumber | String | False | Tax ID Number. |
BuyingTaxCodeCode | String | True | 3 digit tax code. |
BuyingTaxCodeID | Uuid | False | Unique guid identifier belonging to the assigned tax code. |
BuyingTaxCodeURI | String | True | Uniform resource identifier associated with the tax code object. |
BuyingTermsBalanceDueDate | Integer | False | If PaymentIsDue = CashOnDelivery, PrePaid, InAGivenNumberOfDaysNumber or NumberOfDaysAfterEOM then set the number of days within which payments must be made. If PaymentIsDue = OnADayOfTheMonth or DayOfMonthAfterEOM then set the day of the month within which payments must be made. |
BuyingTermsDiscountDate | Integer | False | If PaymentIsDue = CashOnDelivery, PrePaid, InAGivenNumberOfDaysNumber or NumberOfDaysAfterEOM then set the number of days within which payments must be made to receive discounts. If PaymentIsDue = OnADayOfTheMonth or DayOfMonthAfterEOM then set the day of the month within which the payment must be made to receive discounts. |
BuyingTermsDiscountForEarlyPayment | Double | False | % discount for early payment. |
BuyingTermsPaymentIsDue | String | False | Default Terms of Payment definitions: CashOnDelivery, PrePaid, InAGivenNumberOfDays, OnADayOfTheMonth, NumberOfDaysAfterEOM, DayOfMonthAfterEOM. |
BuyingTermsVolumeDiscount | Double | False | Volume discount. |
BuyingUseSupplierTaxCode | Boolean | False | True or false. |
PaymentBSBNumber | String | False | Default bank account bsb number. |
PaymentBankAccountName | String | False | Default bank account name. |
PaymentBankAccountNumber | String | False | formatted (XX-XXXX-XXXXXXX-XX). |
PaymentRefundCardNumber | String | False | Last 4 digits only. |
PaymentRefundNameOnCard | String | False | Default name on card. |
PaymentRefundNotes | String | False | Default refund payment notes. |
PaymentRefundPaymentMethod | String | False | Payment method on refund, must exist in company file, default methods consist of the following: American Express, Bank Card, Barter Card, Cash, Cheque, Diners Club, EFTPOS, MasterCard, Money Order, Other, Visa. |
PaymentStatementText | String | False | Default statement text. |
PaymentStatementCode | String | False | Default code attached to an electronic payment. ONLY APPLICABLE FOR NZ REGION. |
PaymentStatementReference | String | False | Default reference attached to an electronic payment. ONLY APPLICABLE FOR NZ REGION. |
ForeignCurrencyId | Uuid | True | This is an AccountRight only field. Foreign Key: Unique identifier for the currency in the form of a guid. |
ForeignCurrencyCode | String | True | This is an AccountRight only field.The currency code. |
ForeignCurrencyName | String | True | This is an AccountRight only field. The full name of the currency. |
ForeignCurrencyURI | String | True | This is an AccountRight only field. Uniform resource identifier associated with the currency object. |
Identifiers | String | True | Contact Identifiers Details. |
CustomField1Label | String | True | Custom Field 1 label. |
CustomField1Value | String | True | Custom Field 1 value. |
CustomField2Label | String | True | Custom Field 2 label. |
CustomField2Value | String | True | Custom Field 2 value. |
CustomField3Label | String | True | Custom Field 3 label. |
CustomField3Value | String | True | Custom Field 3 value. |
CustomList1Label | String | True | Custom List 1 label. |
CustomList1Value | String | True | Custom List 1 value. |
CustomList2Label | String | True | Custom List 2 label. |
CustomList2Value | String | True | Custom List 2 value. |
CustomList3Label | String | True | Custom List 3 label. |
CustomList3Value | String | True | Custom List 3 value. |
Addresses | String | False | The following set of information pulls through details for a contact address. |
RowVersion | String | False | Number value that changes upon a record update, can be used for change control but does does not preserve a date or a time. |
CompanyFileId | String | True | The ID of the company file. Takes precedence over the CompanyFileId connection property. |
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¶
Name | Type | ReadOnly | Description |
---|---|---|---|
ID [KEY] | Uuid | True | Unique identifier in the form of a guid. |
Date | Datetime | False | Transaction date. |
DeliveryStatus | String | False | Remittance advise delivery status assigned to credit note: Print = ToBePrinted, Email = ToBeEmailed, PrintAndEmail = ToBePrintedAndEmailed, Nothing = AlreadyPrintedOrSent. |
Memo | String | False | Memo text describing the credit refund. |
Number | String | False | Sales invoice number |
Payee | String | False | Payee address on the credit note belonging to the customer contact. |
Amount | Decimal | False | Total credit amount to be refunded, can be equal to or less than the invoice amount. |
ChequePrinted | Boolean | False | True or false. |
RowVersion | String | True | Number value that changes upon a record update, can be used for change control but does does not preserve a date or a time. |
URI | String | True | Uniform resource identifier encompasses all types of names and addresses that refer to objects on the web. |
AccountID | Uuid | True | Unique identifier for the account in the form of a guid. |
AccountDisplayID | String | True | Account code. Format includes separator ie 4-1100 |
AccountName | String | True | Name of the account. |
AccountURI | String | True | Uniform resource identifier associated with the account object. |
CustomerID | Uuid | True | Unique guid identifier belonging to the assigned customer contact. |
CustomerDisplayID | String | True | Customer contact Card ID, can also be used as a unique customer contact identifier. |
CustomerName | String | True | Name of the customer contact. |
CustomerURI | String | True | Uniform resource identifier associated with the customer contact object. |
InvoiceID | Uuid | False | Unique identifier in the form of a guid. |
InvoiceNumber | String | True | Invoice number. |
InvoiceURI | String | True | Uniform resource identifier associated with the invoice. |
CompanyFileId | String | True | The ID of the company file. Takes precedence over the CompanyFileId connection property. |
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¶
Name | Type | ReadOnly | Description |
---|---|---|---|
ID [KEY] | Uuid | True | Unique identifier in the form of a guid. |
Date | Datetime | False | Transaction date. |
Memo | String | False | Memo text describing the settled credit. |
Number | String | False | Cheque credit number, optional on POST as will auto increment based upon last recorded if left blank. |
RowVersion | String | True | Number value that changes upon a record update, can be used for change control but does does not preserve a date or a time. |
URI | String | True | Uniform resource identifier encompasses all types of names and addresses that refer to objects on the web. |
CreditAmount | Decimal | True | Total credit amount to be applied, can only be less than or equal to CreditFromInvoice amount. |
CreditFromInvoiceID | Uuid | False | Unique identifier in the form of a guid. |
CreditFromInvoiceNumber | String | True | Invoice number. |
CreditFromInvoiceURI | String | True | Uniform resource identifier associated with the invoice. |
CustomerID | Uuid | False | Unique guid identifier belonging to the assigned customer contact. |
CustomerDisplayID | String | True | Customer contact Card ID, can also be used as a unique customer contact identifier. |
CustomerName | String | True | Name of the customer contact. |
CustomerURI | String | True | Uniform resource identifier associated with the customer contact object. |
Lines | String | False | The following set of information pulls through line details for the credit settlement. |
CompanyFileId | String | True | The ID of the company file. Takes precedence over the CompanyFileId connection property. |
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¶
Name | Type | ReadOnly | Description |
---|---|---|---|
ID [KEY] | Uuid | True | Unique identifier in the form of a guid. |
Date | Datetime | False | The date of the entry. |
DepositTo | String | False | If allocating a banking account for the payment specify Account. If using undeposited funds specify UndepositedFunds. |
AmountReceived | Double | False | The amount received. |
Memo | String | False | Memo text for the object. |
PaymentMethod | String | False | One of: American Express, Bank Card, Barter Card, Cash, Cheque, Diners Club, EFTPOS, MasterCard, Money Order, Other, Visa. |
ReceiptNumber | String | False | ID No of payment transaction. |
RowVersion | String | True | Number value that changes upon a record update, can be used for change control but does does not preserve a date or a time. |
URI | String | True | Uniform resource identifier encompasses all types of names and addresses that refer to objects on the web. |
AccountID | Uuid | False | Unique identifier for the account in the form of a guid. |
AccountDisplayID | String | True | Account code. Format includes separator ie 4-1100 |
AccountName | String | True | Name of the account. |
AccountURI | String | True | Uniform resource identifier associated with the account object. |
CustomerID | Uuid | False | Unique guid identifier belonging to the assigned customer contact. |
CustomerDisplayID | String | True | Customer contact Card ID, can also be used as a unique customer contact identifier. |
CustomerName | String | True | Name of the customer contact. |
CustomerURI | String | True | Uniform resource identifier associated with the customer contact object. |
Invoices | String | False | An array of line invoice information. |
CompanyFileId | String | True | The ID of the company file. Takes precedence over the CompanyFileId connection property. |
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¶
Name | Type | ReadOnly | Description |
---|---|---|---|
ID [KEY] | Uuid | True | Unique identifier in the form of a guid. |
Amount | Double | False | Total amount of the debit to be refunded, can be equal to or less than the bill amount. |
Date | Datetime | False | The date of the entry. |
DepositTo | String | False | If allocating a banking account for the payment specify Account. If using undeposited funds specify UndepositedFunds. |
Memo | String | False | Memo text for the object. |
Number | String | False | Purchase bill number. |
PaymentMethod | String | False | Payment method text. |
RowVersion | String | True | Number value that changes upon a record update, can be used for change control but does does not preserve a date or a time. |
URI | String | True | Uniform resource identifier encompasses all types of names and addresses that refer to objects on the web. |
BillID | Uuid | False | Unique identifier in the form of a guid. |
BillNumber | String | True | Bill number. |
BillURI | String | True | Uniform resource identifier associated with the bill. |
AccountID | Uuid | False | Unique identifier for the account in the form of a guid. |
AccountDisplayID | String | True | Account code. Format includes separator ie 4-1100 |
AccountName | String | True | Name of the account. |
AccountURI | String | True | Uniform resource identifier associated with the account object. |
SupplierID | Uuid | False | Unique guid identifier belonging to the assigned supplier contact. |
SupplierDisplayID | String | True | Customer contact Card ID, can also be used as a unique supplier contact identifier. |
SupplierName | String | True | Name of the supplier contact. |
SupplierURI | String | True | Uniform resource identifier associated with the supplier contact object. |
CompanyFileId | String | True | The ID of the company file. Takes precedence over the CompanyFileId connection property. |
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¶
Name | Type | ReadOnly | Description |
---|---|---|---|
ID [KEY] | Uuid | True | Unique identifier in the form of a guid. |
Date | Datetime | False | The date of the entry. |
Memo | String | False | Memo text for the object. |
Number | String | False | ID transaction number, if left blank on POST will auto increment based upon last recorded. |
DebitAmount | Double | False | Total debit amount to be applied, can only be less than or equal to DebitFromBill amount. |
URI | String | True | Uniform resource identifier encompasses all types of names and addresses that refer to objects on the web. |
RowVersion | String | True | Number value that changes upon a record update, can be used for change control but does does not preserve a date or a time. |
DebitFromBillID | Uuid | False | Unique identifier in the form of a guid. |
DebitFromBillNumber | String | True | Bill number. |
DebitFromBillURI | String | True | Uniform resource identifier associated with the bill. |
SupplierID | Uuid | False | Unique guid identifier belonging to the assigned supplier contact. |
SupplierDisplayID | String | True | Customer contact Card ID, can also be used as a unique supplier contact identifier. |
SupplierName | String | True | Name of the supplier contact. |
SupplierURI | String | True | Uniform resource identifier associated with the supplier contact object. |
Lines | String | False | The following set of information pulls through line details for the debit settlement. |
CompanyFileId | String | True | The ID of the company file. Takes precedence over the CompanyFileId connection property. |
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¶
Name | Type | ReadOnly | Description |
---|---|---|---|
ID [KEY] | Uuid | True | Unique identifier in the form of a guid. |
BankStatementText | String | False | Text to appear on employee's bank statement where PaymentMethod = Electronic. |
PaymentMethod | String | False | Payment method can consist of the following Enum values: Cash, Cheque, Electronic. |
EmployeeID | Uuid | False | Unique guid identifier belonging to the assigned employee contact. |
EmployeeDisplayID | String | True | Customer contact Card ID, can also be used as a unique employee contact identifier. |
EmployeeName | String | True | Name of the employee contact. |
EmployeeURI | String | True | Uniform resource identifier associated with the employee contact object. |
RowVersion | String | True | Number value that changes upon a record update, can be used for change control but does does not preserve a date or a time. |
URI | String | True | Uniform resource identifier encompasses all types of names and addresses that refer to objects on the web. |
BankAccounts | String | False | An array of employee bank account information where PaymentMethod = Electronic. |
CompanyFileId | String | True | The ID of the company file. Takes precedence over the CompanyFileId connection property. |
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¶
Name | Type | ReadOnly | Description |
---|---|---|---|
ID [KEY] | Uuid | True | Unique identifier in the form of a guid. |
DateOfBirth | Datetime | False | The employee's date of birth. |
Gender | String | False | Gender of the employee contact. |
PaySlipDelivery | String | False | The methods by which the payslip can be sent: ToBePrinted (Defaults to be printed on PUT if not specified) , ToBeEmailed, ToBePrintedAndEmailed, AlreadyPrintedOrSent. |
PaySlipEmail | String | False | Email address to which payslips should be emailed. |
RowVersion | String | True | Number value that changes upon a record update, can be used for change control but does does not preserve a date or a time. |
StartDate | Datetime | False | Ending date of the period. |
TimeBillingCostPerHour | Double | True | Hourly cost. |
TimeBillingEmployeeBillingRateExcludingTax | Double | True | Hourly billing rate for services provided by employee. |
EmploymentBasis | String | False | Employment basis can consist of the following: Individual (Defaults to individual on PUT if not specified) , Labor Hire, Other. |
EmploymentCategory | String | False | Employment category can consist of the following: Permanent (Defaults to permanent on PUT if not specified), Temporary. |
EmploymentStatus | String | False | Employment status can consist of the following: FullTime (Defaults to full time on PUT if not specified) , PartTime, Other, Casual. |
URI | String | True | Uniform resource identifier encompasses all types of names and addresses that refer to objects on the web. |
EmployeeID | Uuid | False | Unique guid identifier belonging to the assigned employee contact. |
EmployeeDisplayID | String | True | Customer contact Card ID, can also be used as a unique employee contact identifier. |
EmployeeName | String | True | Name of the employee contact. |
EmployeeURI | String | True | Uniform resource identifier associated with the employee contact object. |
EmploymentClassificationID | Uuid | False | Unique identifier for the account in the form of a guid. |
EmploymentClassificationName | String | True | Name of the employment classification. |
EmploymentClassificationURI | String | True | Uniform resource identifier associated with the employment classification object. |
TaxCategoryID | Uuid | False | Unique category identifier in the form of a guid. |
TaxCategoryName | String | True | Name of the category. |
TaxCategoryType | String | True | Indicates the type of payroll category ie: Wage, Deduction, Superannuation. |
TaxCategoryURI | String | True | Uniform resource identifier associated with the category object. |
TaxTableID | Uuid | False | Unique identifier for the account in the form of a guid. |
TaxTableName | String | True | Name of the tax table. |
TaxTableURI | String | True | Uniform resource identifier associated with the tax table object. |
TaxFileNumber | String | False | Employee tax file number (Must be 9 digits and formatted as XXX XXX XXX). |
TaxTotalRebatesPerYear | Double | False | The employee's total rebates offset amount. |
TaxWithholdingVariationRate | Double | False | % rate that applies if selected tax table = Withholding Variation. |
TaxExtraTaxPerPay | Double | True | The employees extra tax withheld from there pay. |
WageAnnualSalary | Decimal | True | Annual salary amount for the employee. |
WageHourlyRate | Decimal | True | Employee hourly rate. |
WageHoursInWeeklyPayPeriod | Decimal | True | Hours worked in a pay period. Defaults to 40 on PUT if not specified. |
WagePayBasis | String | True | PayBasis can consist of the following: Salary (Defaults to salary on PUT if not specified), Hourly. |
WagePayFrequency | String | True | Pay frequency can consist of the following: Weekly (Defaults to weekly on PUT if not specified) , Fortnightly, TwiceAMonth, Monthly. |
WagesExpenseAccountID | Uuid | False | Unique identifier for the account in the form of a guid. |
WagesExpenseAccountDisplayID | String | True | Account code. Format includes separator ie 4-1100 |
WagesExpenseAccountName | String | True | Name of the account. |
WagesExpenseAccountURI | String | True | Uniform resource identifier associated with the account object. |
WageCategories | String | False | An array of wage categories the employee is linked to. |
Entitlements | String | False | The following set of information pulls through details for each linked entitlement category. |
Deductions | String | False | The following set of information pulls through details for each linked deductions category. |
EmployerExpenses | String | False | The following set of information pulls through details for each linked employer expenses category. |
CompanyFileId | String | True | The ID of the company file. Takes precedence over the CompanyFileId connection property. |
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¶
Name | Type | ReadOnly | Description |
---|---|---|---|
ID [KEY] | Uuid | True | Unique identifier in the form of a guid. |
Memo | String | False | Memo text for the object. |
HoursPerPayFrequency | Decimal | True | Hours worked in a pay period. |
PayFrequency | String | True | Pay frequency can consist of the following: Weekly, Fortnightly, TwiceAMonth, Monthly. |
RowVersion | String | True | Number value that changes upon a record update, can be used for change control but does does not preserve a date or a time. |
URI | String | True | Uniform resource identifier encompasses all types of names and addresses that refer to objects on the web. |
CategoryID | Uuid | False | Unique category identifier in the form of a guid. |
CategoryDisplayID | String | True | Display ID for the category. |
CategoryName | String | True | Name of the category. |
CategoryURI | String | True | Uniform resource identifier associated with the category object. |
EmployeeID | Uuid | False | Unique guid identifier belonging to the assigned employee contact. |
EmployeeDisplayID | String | True | Customer contact Card ID, can also be used as a unique employee contact identifier. |
EmployeeName | String | True | Name of the employee contact. |
EmployeeURI | String | True | Uniform resource identifier associated with the employee contact object. |
PayrollDetailsID | Uuid | True | Unique identifier in the form of a guid. |
PayrollDetailsURI | String | True | Uniform resource identifier associated with the object. |
PayrollCategories | String | False | All payroll categories linked to the employee contact, includes all payroll categories of type Wage. |
CompanyFileId | String | True | The ID of the company file. Takes precedence over the CompanyFileId connection property. |
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¶
Name | Type | ReadOnly | Description |
---|---|---|---|
ID [KEY] | Uuid | True | Unique identifier in the form of a guid. |
InventoryJournalNumber | String | False | Inventory journal number, if left null when posting a number will automatically be assigned and incremented based upon last recorded. |
Date | Datetime | False | The date of the entry. |
IsYearEndAdjustment | Boolean | False | True indicates the transaction is a YearEndAdjustment. False indicates the transaction is not a YearEndAdjustment. |
Memo | String | False | Memo text for the object. |
CategoryID | Uuid | False | Unique category identifier in the form of a guid. |
CategoryDisplayID | String | True | Display ID for the category. |
CategoryName | String | True | Name of the category. |
CategoryURI | String | True | Uniform resource identifier associated with the category object. |
URI | String | True | Uniform resource identifier encompasses all types of names and addresses that refer to objects on the web. |
RowVersion | String | True | Number value that changes upon a record update, can be used for change control but does does not preserve a date or a time. |
Lines | String | False | An array of spend money line information. |
CompanyFileId | String | True | The ID of the company file. Takes precedence over the CompanyFileId connection property. |
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¶
Name | Type | ReadOnly | Description |
---|---|---|---|
ID [KEY] | Uuid | True | Unique identifier in the form of a guid. |
ItemID | Uuid | True | Unique identifier in the form of a guid. |
ItemName | String | True | Name of the item. |
ItemNumber | String | True | The number assigned to the item. |
ItemURI | String | True | Uniform resource identifier associated with the item. |
RowVersion | String | True | Number value that changes upon a record update, can be used for change control but does does not preserve a date or a time. |
URI | String | True | Uniform resource identifier encompasses all types of names and addresses that refer to objects on the web. |
SellingPrices | String | False | An array of selling price information. |
CompanyFileId | String | True | The ID of the company file. Takes precedence over the CompanyFileId connection property. |
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¶
Name | Type | ReadOnly | Description |
---|---|---|---|
ID [KEY] | Uuid | True | Unique identifier in the form of a guid. |
AverageCost | Decimal | True | Item's average cost when the quantity on hand is equal to or greater than zero. |
BaseSellingPrice | Decimal | True | Item's base selling price inclusive of tax. |
IsActive | Boolean | False | Please note: Defaults to true if left blank on POST. True indicates the item is active. False indicates the item is inactive. |
IsBought | Boolean | False | True indicates the item is bought. False indicates the item is not bought. |
IsInventoried | Boolean | False | True indicates the item is inventoried. False indicates the item is not inventoried. |
IsSold | Boolean | False | True indicates the item is sold. False indicates the item is not sold. |
Name | String | False | Name of the object. |
Number | String | False | Item number. |
PhotoURI | String | False | Uniform resource identifier associated with a photo image. |
PriceMatrixURI | String | False | Please note: Only available if Item IsSold = true. |
CurrentValue | Decimal | True | Dollar value of units held in inventory. |
Description | String | False | Description of the object. |
UseDescription | Boolean | False | True indicates to use the description text instead of item name on sale invoices and purchase orders. False indicates not to use the item description on sales and purchases. |
AssetAccountID | Uuid | False | Unique identifier for the account in the form of a guid. |
AssetAccountDisplayID | String | True | Account code. Format includes separator ie 4-1100 |
AssetAccountName | String | True | Name of the account. |
AssetAccountURI | String | True | Uniform resource identifier associated with the account object. |
BuyingUnitOfMeasure | String | False | Description of the unit type the item is purchased with, ie: kg, hour. |
BuyingItemsPerBuyingUnit | Decimal | False | Number of items per buying unit. Note: if is IsInventoried = false null is returned. |
BuyingLastPurchasePrice | Decimal | True | The item's tax inclusive price per unit when last purchased. |
BuyingStandardCost | Decimal | False | Standard purchase price for one buying unit of this item. |
RestockingSupplierID | Uuid | False | Unique guid identifier belonging to the assigned supplier contact. |
RestockingSupplierDisplayID | String | True | Customer contact Card ID, can also be used as a unique supplier contact identifier. |
RestockingSupplierItemNumber | String | False | Number or code supplier has assigned to the item. |
RestockingSupplierName | String | True | Name of the supplier contact. |
RestockingSupplierURI | String | True | Uniform resource identifier associated with the supplier contact object. |
RestockingDefaultOrderQuantity | Integer | True | Default number of units to buy on auto reorder. |
RestockingMinimumLevelForRestockingAlert | Integer | True | The minimum number of items on hand before needing to reorder. |
BuyingTaxCodeID | Uuid | False | Unique guid identifier belonging to the assigned tax code. |
BuyingTaxCodeCode | String | True | 3 digit tax code. |
BuyingTaxCodeURI | String | True | Uniform resource identifier associated with the tax code object. |
CostOfSalesAccountID | Uuid | False | Unique identifier for the account in the form of a guid. |
CostOfSalesAccountDisplayID | String | True | Account code. Format includes separator ie 4-1100 |
CostOfSalesAccountName | String | True | Name of the account. |
CostOfSalesAccountURI | String | True | Uniform resource identifier associated with the account object. |
IncomeAccountID | Uuid | False | Unique identifier for the account in the form of a guid. |
IncomeAccountDisplayID | String | True | Account code. Format includes separator ie 4-1100 |
IncomeAccountName | String | True | Name of the account. |
IncomeAccountURI | String | True | Uniform resource identifier associated with the account object. |
ExpenseAccountID | Uuid | False | Unique identifier for the account in the form of a guid. |
ExpenseAccountDisplayID | String | True | Account code. Format includes separator ie 4-1100 |
ExpenseAccountName | String | True | Name of the account. |
ExpenseAccountURI | String | True | Uniform resource identifier associated with the account object. |
QuantityAvailable | Decimal | True | Calculated quantity of the item available for sale. |
QuantityCommitted | Decimal | True | Quantity of the item held in pending sale invoices. |
QuantityOnHand | Decimal | True | Quantity of units held in inventory. |
QuantityOnOrder | Decimal | True | Quantity of the item held in pending purchase orders. |
SellingBaseSellingPrice | Decimal | True | Standard selling price for one selling unit of this item. |
SellingCalculateSalesTaxOn | String | True | ONLY APPLICABLE FOR AU REGION. Sales tax can be calculated on any of the following Enum values: ActualSellingPrice, BaseSellingPrice, LevelA, LevelB, LevelC, LevelD, LevelE, LevelF. |
SellingIsTaxInclusive | Boolean | True | True indicates the selling prices are inclusive of tax. False indicates the selling prices are exclusive of tax. |
SellingItemsPerSellingUnit | Integer | True | Number of items per selling unit. Note: if is IsInventoried = false null is returned. |
SellingUnitOfMeasure | String | True | Description of the unit type the item is sold as, ie: kg, hour. |
SellingTaxCodeID | Uuid | False | Unique guid identifier belonging to the assigned tax code. |
SellingTaxCodeCode | String | True | 3 digit tax code. |
SellingTaxCodeURI | String | True | Uniform resource identifier associated with the tax code object. |
CustomField1Label | String | True | Custom Field 1 label. |
CustomField1Value | String | True | Custom Field 1 value. |
CustomField2Label | String | True | Custom Field 2 label. |
CustomField2Value | String | True | Custom Field 2 value. |
CustomField3Label | String | True | Custom Field 3 label. |
CustomField3Value | String | True | Custom Field 3 value. |
CustomList1Label | String | True | Custom List 1 label. |
CustomList1Value | String | True | Custom List 1 value. |
CustomList2Label | String | True | Custom List 2 label. |
CustomList2Value | String | True | Custom List 2 value. |
CustomList3Label | String | True | Custom List 3 label. |
CustomList3Value | String | True | Custom List 3 value. |
URI | String | True | Uniform resource identifier encompasses all types of names and addresses that refer to objects on the web. |
RowVersion | String | True | Number value that changes upon a record update, can be used for change control but does does not preserve a date or a time. |
CompanyFileId | String | True | The ID of the company file. Takes precedence over the CompanyFileId connection property. |
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¶
Name | Type | ReadOnly | Description |
---|---|---|---|
ID [KEY] | Uuid | False | Unique identifier in the form of a guid. |
RowVersion | String | True | Number value that changes upon a record update, can be used for change control but does does not preserve a date or a time. |
URI | String | True | Uniform resource identifier encompasses all types of names and addresses that refer to objects on the web. |
JobID | Uuid | False | Unique job identifier in the form of a guid. |
JobName | String | True | Name assigned to the job. |
JobNumber | String | True | Number assigned to the job. |
JobURI | String | True | Uniform resource identifier associated with the job object. |
Budgets | String | False | An array of job budget information. |
CompanyFileId | String | True | The ID of the company file. Takes precedence over the CompanyFileId connection property. |
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¶
Name | Type | ReadOnly | Description |
---|---|---|---|
ID [KEY] | Uuid | True | Unique identifier in the form of a guid. |
Contact | String | False | Contact for the job. |
Description | String | False | Description of the object. |
StartDate | Datetime | False | Date the job was started. |
FinishDate | Datetime | False | Date the job was completed. |
IsActive | Boolean | False | Defaults to true if left blank on POST. |
IsHeader | Boolean | False | Defaults to true if left blank on POST. |
Manager | String | False | Manager of the job. |
Name | String | False | Name of the object. |
Number | String | False | Number assigned to the job. |
PercentComplete | Double | False | % of the job completed. |
LastModified | Datetime | True | Date the job was Modified. |
TrackReimbursables | Boolean | False | True indicates a job is used to track reimbursable expenses. False indicates a job is not used to track reimbursable expenses. |
LinkedCustomerID | Uuid | False | Unique guid identifier belonging to the assigned customer contact. |
LinkedCustomerDisplayID | String | True | Customer contact Card ID, can also be used as a unique customer contact identifier. |
LinkedCustomerName | String | True | Name of the customer contact. |
LinkedCustomerUri | String | True | Uniform resource identifier associated with the customer contact object. |
ParentJobID | Uuid | False | Unique job identifier in the form of a guid. |
ParentJobName | String | True | Name assigned to the job. |
ParentJobNumber | String | True | Number assigned to the job. |
ParentJobUri | String | True | Uniform resource identifier associated with the job object. |
URI | String | True | Uniform resource identifier encompasses all types of names and addresses that refer to objects on the web. |
RowVersion | String | True | Number value that changes upon a record update, can be used for change control but does does not preserve a date or a time. |
CompanyFileId | String | True | The ID of the company file. Takes precedence over the CompanyFileId connection property. |
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¶
Name | Type | ReadOnly | Description |
---|---|---|---|
ID [KEY] | Uuid | True | Unique identifier in the form of a guid. |
DateOccurred | Datetime | False | Transaction date entry. |
DisplayID | String | False | Display ID for the object. |
GSTReportingMethod | String | False | Reporting Method used on the general journal transaction which accepts the following: Sale (Supply), Purchase (Acquisition). |
IsTaxInclusive | Boolean | False | True indicates the transaction default status is set to tax inclusiv.e False indicates the transaction status is not tax inclusive. |
IsYearEndAdjustment | Boolean | False | True indicates the transaction is a YearEndAdjustmen.t False indicates the transaction is not a YearEndAdjustment. |
Memo | String | False | Header memo of the general journal entry. |
CategoryID | Uuid | False | Unique category identifier in the form of a guid. |
CategoryDisplayID | String | True | Display ID for the category. |
CategoryName | String | True | Name of the category. |
CategoryURI | String | True | Uniform resource identifier associated with the category object. |
ForeignCurrencyID | Uuid | False | Uniform identifier for the currency in the form of the guid. |
ForeignCurrencyCode | String | True | The currency code. |
ForeignCurrencyName | String | True | The full name of the currency. |
ForeignCurrencyURI | String | True | Uniform resource identifier associated with the currency object. |
Uri | String | True | Uniform resource identifier encompasses all types of names and addresses that refer to objects on the web. |
RowVersion | String | True | Number value that changes upon a record update, can be used for change control but does does not preserve a date or a time. |
Lines | String | False | An array of line item information. |
CompanyFileId | String | True | The ID of the company file. Takes precedence over the CompanyFileId connection property. |
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¶
Name | Type | ReadOnly | Description |
---|---|---|---|
ID [KEY] | Uuid | True | Unique identifier in the form of a guid. |
Name | String | False | Name of the wage category. |
StpCategory | String | False | STP Category for this payroll category. See MYOB help for more info. Valid values are: NotReportable (default), GrossPayments, PAYGWithholding, ExemptForeignIncome, CDEPPayments, AllowanceCar, AllowanceTransport, AllowanceTravel, AllowanceMeals, AllowanceLaundry, AllowanceOther, DeductionUnionProfAssocFees, DeductionWorkPlaceGiving, ReportableFringeBenefitsExemptAmount, ReportableFringeBenefitsTaxableAmount, ETPTaxableComponent, ETPTaxFreeComponent, ETPTaxWithholding, LumpSumPaymentATermination, LumpSumPaymentARedundancy, LumpSumPaymentB, LumpSumPaymentD, LumpSumPaymentE, SuperGuarantee, OrdinaryTimesEarnings, ReportableEmployerSuperContributions. |
Type | String | True | Indicates the type of payroll category ie: Wage, Expense, Deduction. |
WageType | String | True | Indicates whether the wage category is a salary type or an hourly type. Use either Hourly or Salary |
PayRate | String | True | How to calculate the rate, can be either RegularRate or FixedHourly. |
FixedHourlyRate | Decimal | False | Fixed hourly rate for all employees linked to this wage category if PayRate = FixedHourly, otherwise null. |
AutomaticallyAdjustBaseAmounts | Boolean | False | When entering leave amounts on a pay, base hourly or base salary amount will automatically be adjusted if set to True. |
RegularRateMultiplier | Decimal | False | Calculate as a multiple of the hourly rate setup on each employee's card if PayRate = RegularRate, otherwise null. |
OverriddenWagesExpenseAccountID | Uuid | False | Unique identifier for the account in the form of a guid. |
OverriddenWagesExpenseAccountName | String | True | Name of the account. |
OverriddenWagesExpenseAccountNumber | String | True | Account name belonging to the account record. |
OverriddenWagesExpenseAccountURI | String | True | Uniform resource identifier associated with the account object. |
URI | String | True | Uniform resource identifier encompasses all types of names and addresses that refer to objects on the web. |
RowVersion | String | True | Number value that changes upon a record update, can be used for change control but does does not preserve a date or a time. |
Exemptions | String | False | An array of tax and deductions this wage category is exempt from. |
CompanyFileId | String | True | The ID of the company file. Takes precedence over the CompanyFileId connection property. |
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¶
Name | Type | ReadOnly | Description |
---|---|---|---|
ID [KEY] | Uuid | True | Unique identifier in the form of a guid. |
AppliedToDate | Decimal | True | Amount currently applied to the purchase bill. |
BalanceDueAmount | Decimal | True | Amount still payable on the purchase bill. |
BillDeliveryStatus | String | False | Bill delivery status assigned: Print = ToBePrinted, Email = ToBeEmailed, PrintAndEmail = ToBePrintedAndEmailed, Nothing = AlreadyPrintedOrSent. |
BillType | String | False | Type of the bill. One of: Item,Service,Professional,Miscellaneous. |
Comment | String | False | Purchase bill comment. |
Date | Datetime | False | The date of the entry. |
Freight | Decimal | False | Tax inclusive freight amount applicable to the purchase bill. |
IsReportable | Boolean | False | ONLY applicable for AU region. True indicates the transaction is reportable taxable payment. Falseindicates the transaction is not reportable taxable payment. |
IsTaxInclusive | Boolean | False | True indicates the transaction is set to tax inclusive. False indicates the transaction is not tax inclusive. |
JournalMemo | String | False | Memo text for the object. |
Number | String | False | Purchase bill number. |
RowVersion | String | True | Number value that changes upon a record update, can be used for change control but does does not preserve a date or a time. |
ShipToAddress | String | False | ShipTo address of the purchase bill. |
ShippingMethod | String | False | Shipping method text. |
Status | String | True | Bill status: Open, Closed, Debit. |
Subtotal | Decimal | True | If IsTaxInclusive = false then sum of all tax exclusive line amounts. If IsTaxInclusive = true then sum of all tax inclusive line amounts. |
TotalAmount | Decimal | True | Total amount of the purchase bill. |
TotalTax | Decimal | True | Total of all tax amounts applicable to the purchase bill. |
SupplierInvoiceNumber | String | False | Supplier invoice number. |
URI | String | True | Uniform resource identifier encompasses all types of names and addresses that refer to objects on the web. |
CategoryID | Uuid | False | Unique category identifier in the form of a guid. |
CategoryDisplayID | String | True | Display ID for the category. |
CategoryName | String | True | Name of the category. |
CategoryURI | String | True | Uniform resource identifier associated with the category object. |
FreightTaxCodeID | Uuid | False | Unique guid identifier belonging to the assigned tax code. |
FreightTaxCodeCode | String | True | 3 digit tax code. |
FreightTaxCodeURI | String | True | Uniform resource identifier associated with the tax code object. |
OrderID | Uuid | False | Unique identifier in the form of a guid. |
OrderNumber | String | True | The order number. |
OrderURI | String | True | Uniform resource identifier associated with the order. |
SupplierID | Uuid | False | Unique guid identifier belonging to the assigned supplier contact. |
SupplierDisplayID | String | True | Customer contact Card ID, can also be used as a unique supplier contact identifier. |
SupplierName | String | True | Name of the supplier contact. |
SupplierURI | String | True | Uniform resource identifier associated with the supplier contact object. |
TermsBalanceDueDate | Integer | False | If PaymentIsDue = CashOnDelivery, PrePaid, InAGivenNumberOfDaysNumber or NumberOfDaysAfterEOM then set the number of days within which the customer has to pay invoices. If PaymentIsDue = OnADayOfTheMonth or DayOfMonthAfterEOM then set the day of the month within which the payment must be made to pay invoices. |
TermsDiscount | Decimal | True | Discount amount that will apply if payment is made in full by the discount date. |
TermsDiscountDate | Integer | True | If PaymentIsDue = CashOnDelivery, PrePaid, InAGivenNumberOfDaysNumber or NumberOfDaysAfterEOM then set the number of days within which the customer must pay to receive discounts. If PaymentIsDue = OnADayOfTheMonth or DayOfMonthAfterEOM then set the day of the month within which the payment must be made to receive discounts. |
TermsDiscountExpiryDate | Datetime | True | Date in which payment must be paid in full in quote to receive discount. |
TermsDiscountForEarlyPayment | Double | True | % discount for early payment. |
TermsDueDate | Datetime | True | Date in which payment is due. |
TermsPaymentIsDue | String | False | Default Terms of Payment definitions: CashOnDelivery, PrePaid, InAGivenNumberOfDays, OnADayOfTheMonth, NumberOfDaysAfterEOM, DayOfMonthAfterEOM. |
TermsMonthlyChargeForLatePayment | Double | False | % monthly charge for late payment. |
Lines | String | False | An array of line bill information. |
CompanyFileId | String | True | The ID of the company file. Takes precedence over the CompanyFileId connection property. |
PromisedDate | Datetime | False | Transaction Promised Date. |
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¶
Name | Type | ReadOnly | Description |
---|---|---|---|
ID [KEY] | Uuid | True | Unique identifier in the form of a guid. |
AppliedToDate | Double | True | Amount currently applied to the purchase order. |
Date | Datetime | False | The date of the entry. |
Freight | Decimal | False | Tax inclusive freight amount applicable to the purchase order. |
BalanceDueAmount | Decimal | True | Amount still payable on the purchase order. |
IsReportable | Boolean | False | ONLY applicable for AU region. True indicates the transaction is reportable taxable payment. Falseindicates the transaction is not reportable taxable payment. |
IsTaxInclusive | Boolean | False | True indicates the transaction is set to tax inclusive. False indicates the transaction is not tax inclusive. |
JournalMemo | String | False | Memo text for the object. |
Number | String | False | Purchase order number. |
OrderType | String | False | Type of the order. One of: Item,Service,Professional,Miscellaneous |
ShipToAddress | String | False | ShipTo address of the purchase order. |
Status | String | True | Purchase Order status: Open, ConvertedToBill. |
SupplierInvoiceNumber | String | False | Supplier invoice number. |
Subtotal | Decimal | True | If IsTaxInclusive = false then sum of all tax exclusive line amounts. If IsTaxInclusive = true then sum of all tax inclusive line amounts. |
TotalAmount | Decimal | True | Total amount of the purchase order. |
TotalTax | Decimal | True | Total of all tax amounts applicable to the purchase order. |
RowVersion | String | True | Number value that changes upon a record update, can be used for change control but does does not preserve a date or a time. |
URI | String | True | Uniform resource identifier encompasses all types of names and addresses that refer to objects on the web. |
CategoryID | Uuid | True | Unique category identifier in the form of a guid. |
CategoryDisplayID | String | False | Display ID for the category. |
CategoryName | String | True | Name of the category. |
CategoryURI | String | True | Uniform resource identifier associated with the category object. |
FreightTaxCodeID | Uuid | True | Unique guid identifier belonging to the assigned tax code. |
FreightTaxCodeCode | String | False | 3 digit tax code. |
FreightTaxCodeURI | String | True | Uniform resource identifier associated with the tax code object. |
Comment | String | False | Purchase Order Comment |
ShippingMethod | String | False | Shipping Method |
PromisedDate | Datetime | False | Transaction Promised Date. |
SupplierID | Uuid | False | Unique guid identifier belonging to the assigned supplier contact. |
SupplierDisplayID | String | True | Customer contact Card ID, can also be used as a unique supplier contact identifier. |
SupplierName | String | True | Name of the supplier contact. |
SupplierURI | String | True | Uniform resource identifier associated with the supplier contact object. |
TermsBalanceDueDate | Integer | False | If PaymentIsDue = CashOnDelivery, PrePaid, InAGivenNumberOfDaysNumber or NumberOfDaysAfterEOM then set the number of days within which the customer has to pay invoices. If PaymentIsDue = OnADayOfTheMonth or DayOfMonthAfterEOM then set the day of the month within which the payment must be made to pay invoices. |
TermsDiscount | Decimal | True | Discount amount that will apply if payment is made in full by the discount date. |
TermsDiscountDate | Integer | True | If PaymentIsDue = CashOnDelivery, PrePaid, InAGivenNumberOfDaysNumber or NumberOfDaysAfterEOM then set the number of days within which the customer must pay to receive discounts. If PaymentIsDue = OnADayOfTheMonth or DayOfMonthAfterEOM then set the day of the month within which the payment must be made to receive discounts. |
TermsDiscountExpiryDate | Datetime | True | Date in which payment must be paid in full in quote to receive discount. |
TermsDiscountForEarlyPayment | Double | True | % discount for early payment. |
TermsDueDate | Datetime | True | Date in which payment is due. |
TermsPaymentIsDue | String | False | Default Terms of Payment definitions: CashOnDelivery, PrePaid, InAGivenNumberOfDays, OnADayOfTheMonth, NumberOfDaysAfterEOM, DayOfMonthAfterEOM. |
Lines | String | False | An array of line order information. |
CompanyFileId | String | True | The ID of the company file. Takes precedence over the CompanyFileId connection property. |
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¶
Name | Type | ReadOnly | Description |
---|---|---|---|
ID [KEY] | Uuid | True | Unique identifier in the form of a guid. |
AmountReceived | Double | True | The amount received. |
ContactDisplayID | String | True | Contact Card ID, can also be used as a unique contact identifier. |
Date | Datetime | False | The date of the entry. |
DepositTo | String | False | If allocating a banking account for the payment, specify Account. If using undeposited funds, specify UndepositedFunds. |
IsTaxInclusive | Boolean | False | True indicates the transaction is set to tax inclusive with the Amount inclusive of tax. False indicates the transaction is not tax inclusive with the Amount value tax exclusive. |
Memo | String | False | Memo text for the object. |
PaymentMethod | String | False | Payment methods must exist in company file, default methods consist of the following: American Express, Bank Card, Barter Card, Cash, Cheque, Diners Club, EFTPOS, MasterCard, Money Order, Other, Visa. |
ReceiptNumber | String | False | ID No of payment transaction, if left blank on POST will auto increment based upon last recorded #. |
TotalTax | Double | True | Total of all tax amounts applicable to the receive money. |
URI | String | True | Uniform resource identifier encompasses all types of names and addresses that refer to objects on the web. |
RowVersion | String | True | Number value that changes upon a record update, can be used for change control but does does not preserve a date or a time. |
AccountID | Uuid | False | Unique identifier for the account in the form of a guid. |
AccountDisplayID | String | True | Account code. Format includes separator ie 4-1100 |
AccountName | String | True | Name of the account. |
AccountURI | String | True | Uniform resource identifier associated with the account object. |
CategoryID | Uuid | False | Unique category identifier in the form of a guid. |
CategoryDisplayID | String | True | Display ID for the category. |
CategoryName | String | True | Name of the category. |
CategoryURI | String | True | Uniform resource identifier associated with the category object. |
ContactID | Uuid | False | Unique identifier in the form of a guid. |
ContactName | String | True | Name of the contact record. |
ContactType | String | True | Card type of the contact record, can be either Customer, Supplier, Employee or Personal. |
ContactURI | String | True | Uniform resource identifier associated with the contact object. |
Lines | String | False | An array of receive money line information. |
CompanyFileId | String | True | The ID of the company file. Takes precedence over the CompanyFileId connection property. |
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¶
Name | Type | ReadOnly | Description |
---|---|---|---|
ID [KEY] | Uuid | True | Unique identifier in the form of a guid. |
BalanceDueAmount | Decimal | True | Amount still payable on the sale invoice. |
Comment | String | False | Sale invoice comment. |
Date | Datetime | False | The date of the entry. |
InvoiceDeliveryStatus | String | False | Invoice delivery status assigned: Print = ToBePrinted, Email = ToBeEmailed, PrintAndEmail = ToBePrintedAndEmailed, Nothing = AlreadyPrintedOrSent. |
InvoiceType | String | False | Type of the invoice. One of: Item,Service,Professional,TimeBilling,Miscellaneous. |
IsTaxInclusive | Boolean | False | True indicates the transaction is tax inclusive with Total values to be keyed in tax-inclusive. False indicates the transaction is not tax inclusive with Total values to be keyed in tax-exclusive. |
JournalMemo | String | False | Memo text for the object. |
LastPaymentDate | Datetime | True | The date of the entry. |
LastModified | Datetime | True | LastModified date of the entry. |
Number | String | False | Sale invoice number. |
PromisedDate | Datetime | False | The date of the entry. |
ReferralSource | String | False | Referral Source selected on the sale invoice. |
RowVersion | String | True | Number value that changes upon a record update, can be used for change control but does does not preserve a date or a time. |
ShipToAddress | String | False | ShipTo address of the sale invoice. |
ShippingMethod | String | False | Shipping method text. |
Status | String | True | Invoice status: Open, Closed, Credit. |
Subtotal | Decimal | True | If IsTaxInclusive = false then sum of all tax exclusive line amounts. If IsTaxInclusive = true then sum of all tax inclusive line amounts. |
Freight | Decimal | True | Freight applicable to the sale invoice. |
TotalTax | Decimal | True | Total of all tax amounts applicable to the sale invoice. |
TotalAmount | Decimal | True | Total amount of the sale invoice. |
URI | String | True | Uniform resource identifier encompasses all types of names and addresses that refer to objects on the web. |
CustomerPurchaseOrderNumber | String | False | Customer PO number. |
CategoryID | Uuid | False | Unique category identifier in the form of a guid. |
CategoryDisplayID | String | True | Display ID for the category. |
CategoryName | String | True | Name of the category. |
CategoryURI | String | True | Uniform resource identifier associated with the category object. |
SalespersonID | Uuid | False | Unique identifier in the form of a guid. |
SalespersonDisplayID | String | True | Employee contact Card ID. |
SalespersonName | String | True | Selected employee contact name. |
SalespersonURI | String | True | Uniform resource identifier associated with the employee. |
CustomerID | Uuid | False | Unique guid identifier belonging to the assigned customer contact. |
CustomerDisplayID | String | True | Customer contact Card ID, can also be used as a unique customer contact identifier. |
CustomerName | String | True | Name of the customer contact. |
CustomerURI | String | True | Uniform resource identifier associated with the customer contact object. |
FreightTaxCodeID | Uuid | False | Unique guid identifier belonging to the assigned tax code. |
FreightTaxCodeCode | String | True | 3 digit tax code. |
FreightTaxCodeURI | String | True | Uniform resource identifier associated with the tax code object. |
OrderID | Uuid | False | Unique identifier in the form of a guid. |
OrderNumber | String | True | The order number. |
OrderURI | String | True | Uniform resource identifier associated with the order. |
TermsBalanceDueDate | Integer | False | The date of the entry. |
TermsDiscount | Decimal | True | Discount amount that will apply if payment is made in full by the discount date. |
TermsDiscountDate | Integer | True | The date of the entry. |
TermsDiscountExpiryDate | Datetime | True | The date of the entry. |
TermsDiscountForEarlyPayment | Double | False | % discount for early payment. |
TermsDueDate | Datetime | True | The date of the entry. |
TermsFinanceCharge | Decimal | False | Late payment fee to be charged if payment is not made in full by the due date. |
TermsMonthlyChargeForLatePayment | Double | False | % monthly charge for late payment. |
TermsPaymentIsDue | String | False | Default Terms of Payment definitions: CashOnDelivery, PrePaid, InAGivenNumberOfDays, OnADayOfTheMonth, NumberOfDaysAfterEOM, DayOfMonthAfterEOM. |
Lines | String | False | An array of line invoice information. |
CompanyFileId | String | True | The ID of the company file. Takes precedence over the CompanyFileId connection property. |
SaleOrders¶
Returns all sale 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, BalanceDueAmount, Date, IsTaxInclusive, JournalMemo, Number, ReferralSource, Status, Subtotal, TotalAmount, TotalTax, LastPaymentDate, CustomerID, CustomerDisplayID, CustomerName, Freight, FreightTaxCodeCode, FreightTaxCodeID, CategoryID, CategoryDisplayID, CategoryName, SalespersonID, SalespersonDisplayID, SalespersonName, TermsBalanceDueDate, TermsDiscount, TermsDiscountDate, TermsDiscountExpiryDate, TermsDiscountForEarlyPayment, TermsDueDate, TermsFinanceCharge, TermsMonthlyChargeForLatePayment, TermsPaymentIsDue. All the other columns and operators are processed client side. OrderType supports only equality comparison.
SELECT * FROM SaleOrders WHERE ID = "992163e5-f46d-4b45-9296-965aa89a2a71"
SELECT * FROM SaleOrders WHERE BalanceDueAmount = 13
SELECT * FROM SaleOrders WHERE IsTaxInclusive = true
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 SaleOrderItems#TEMP (Total, AccountID, TaxCodeID) VALUES (444.21, "44a7b2bc-0a0e-4381-86fc-9b2c7a479f8f", "bd0f9821-4437-4ec2-9c95-c1bdbdd26c7b")
UPDATE SaleOrders SET OrderType = "Service", Lines = "SaleOrderItems#TEMP" WHERE ID = "992163e5-f46d-4b45-9296-965aa89a2a71"
Insert¶
The following attribute is required when performing an insert: OrderType, CustomerID.
INSERT INTO SaleOrders (OrderType, CustomerID) VALUES ("item", "4635fd9e-82a7-4612-ae15-32c464ef7c48")
Columns¶
Name | Type | ReadOnly | Description |
---|---|---|---|
ID [KEY] | Uuid | True | Unique identifier in the form of a guid. |
AppliedToDate | Decimal | True | The date of the entry. |
BalanceDueAmount | Decimal | True | Amount still payable on the sales order. |
Date | Datetime | True | The date of the entry. |
IsTaxInclusive | Boolean | False | True indicates the transaction is set to tax inclusive. False indicates the transaction is not tax inclusive. |
JournalMemo | String | False | Memo text for the object. |
Number | String | False | Sales Order number. |
OrderType | String | False | Type of the order. One of: Item,Service,Professional,TimeBilling,Miscellaneous. |
ReferralSource | String | False | Referral Source selected on the sale order. |
RowVersion | String | True | Number value that changes upon a record update, can be used for change control but does does not preserve a date or a time. |
Status | String | True | Order status can consist of the following: Open, ConvertedToInvoice. |
Subtotal | Decimal | True | If IsTaxInclusive = false then sum of all tax exclusive line amounts. If IsTaxInclusive = true then sum of all tax inclusive line amounts. |
URI | String | True | Uniform resource identifier encompasses all types of names and addresses that refer to objects on the web. |
TotalAmount | Decimal | True | Total amount of the sale order. |
TotalTax | Decimal | True | Total of all tax amounts applicable to the sale order. |
LastPaymentDate | String | True | The date of the entry. |
CustomerID | Uuid | False | Unique guid identifier belonging to the assigned customer contact. |
CustomerDisplayID | String | True | Customer contact Card ID, can also be used as a unique customer contact identifier. |
CustomerName | String | True | Name of the customer contact. |
CustomerURI | String | True | Uniform resource identifier associated with the customer contact object. |
Freight | Decimal | False | Tax inclusive freight amount applicable to the sale order. |
FreightTaxCodeCode | String | True | 3 digit tax code. |
FreightTaxCodeID | Uuid | True | Unique guid identifier belonging to the assigned tax code. |
FreightTaxCodeURI | String | True | Uniform resource identifier associated with the tax code object. |
CategoryID | Uuid | False | Unique category identifier in the form of a guid. |
CategoryDisplayID | String | True | Display ID for the category. |
CategoryName | String | True | Name of the category. |
CategoryURI | String | True | Uniform resource identifier associated with the category object. |
SalespersonID | Uuid | False | Unique identifier in the form of a guid. |
SalespersonDisplayID | String | True | Employee contact Card ID. |
SalespersonName | String | True | Selected employee contact name. |
SalespersonURI | String | True | Uniform resource identifier associated with the employee. |
TermsBalanceDueDate | Integer | False | If PaymentIsDue = CashOnDelivery, PrePaid, InAGivenNumberOfDaysNumber or NumberOfDaysAfterEOM then set the number of days within which the customer has to pay invoices. If PaymentIsDue = OnADayOfTheMonth or DayOfMonthAfterEOM then set the day of the month within which the payment must be made to pay invoices. |
TermsDiscount | Decimal | True | Discount amount that will apply if payment is made in full by the discount date. |
TermsDiscountDate | Integer | True | If PaymentIsDue = CashOnDelivery, PrePaid, InAGivenNumberOfDaysNumber or NumberOfDaysAfterEOM then set the number of days within which the customer must pay to receive discounts. If PaymentIsDue = OnADayOfTheMonth or DayOfMonthAfterEOM then set the day of the month within which the payment must be made to receive discounts. |
TermsDiscountExpiryDate | Datetime | True | Date in which payment must be paid in full in quote to receive discount. |
TermsDiscountForEarlyPayment | Double | False | % discount for early payment. |
TermsDueDate | Datetime | True | Date in which payment is due. |
TermsFinanceCharge | Decimal | False | Late payment fee to be charged if payment is not made in full by the due date. |
TermsMonthlyChargeForLatePayment | Double | False | % monthly charge for late payment. |
TermsPaymentIsDue | String | False | Default Terms of Payment definitions: CashOnDelivery, PrePaid, InAGivenNumberOfDays, OnADayOfTheMonth, NumberOfDaysAfterEOM, DayOfMonthAfterEOM. |
Lines | String | False | An array of sale line order information. |
CompanyFileId | String | True | The ID of the company file. Takes precedence over the CompanyFileId connection property. |
CustomerPurchaseOrderNumber | String | False | Customer PO number. |
SaleQuotes¶
Return all sale quote 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, Date, IsTaxInclusive, JournalMemo, Number, ReferralSource, Subtotal, TotalAmount, TotalTax, CustomerPurchaseOrderNumber, CustomerID, CustomerDisplayID, CustomerName, Freight, FreightTaxCodeID, FreightTaxCodeCode, CategoryID, CategoryDisplayID, CategoryName, SalespersonID, SalespersonDisplayID, SalespersonName, TermsDiscount, TermsDiscountDate, TermsDiscountExpiryDate, TermsDiscountForEarlyPayment, TermsDueDate, TermsFinanceCharge, TermsMonthlyChargeForLatePayment, TermsPaymentIsDue, TermsBalanceDueDate. All the other columns and operators are processed client side. QuoteType supports only equality comparison.
SELECT * FROM SaleQuotes WHERE ID = "7cabea25-6250-4df5-bb37-77eb2d38f803"
SELECT * FROM SaleQuotes WHERE BalanceDueAmount = 13
SELECT * FROM SaleQuotes WHERE QuoteType = "Service"
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 SaleQuoteItems#TEMP (Total, AccountID, TaxCodeID) VALUES (444.21, "44a7b2bc-0a0e-4381-86fc-9b2c7a479f8f", "bd0f9821-4437-4ec2-9c95-c1bdbdd26c7b")
UPDATE PurchaseBills SET QuoteType = "Service", Lines = "SaleQuoteItems#TEMP" WHERE ID = "f979f0e7-1cd8-4fb9-b2c2-9d4546440cf7"
Insert¶
The following attribute is required when performing an insert: QuoteType, CustomerID.
INSERT INTO SaleQuotes (QuoteType, CustomerID) VALUES ("item", "4635fd9e-82a7-4612-ae15-32c464ef7c48")
Columns¶
Name | Type | ReadOnly | Description |
---|---|---|---|
ID [KEY] | Uuid | True | Unique identifier in the form of a guid. |
BalanceDueAmount | Decimal | True | Amount still payable on the sales quote. |
Date | Datetime | True | The date of the entry. |
IsTaxInclusive | Boolean | False | True indicates the transaction is set to tax inclusive. False indicates the transaction is not tax inclusive. |
JournalMemo | String | False | Journal memo text describing the sale. |
Number | String | False | Sales Quote number. |
QuoteType | String | False | Type of the quote. One of: Item,Service,Professional,TimeBilling,Miscellaneous. |
ReferralSource | String | False | Referral Source selected on the sale quote. |
RowVersion | String | True | Number value that changes upon a record update, can be used for change control but does does not preserve a date or a time. |
Subtotal | Decimal | True | If IsTaxInclusive = false then sum of all tax exclusive line amounts. If IsTaxInclusive = true then sum of all tax inclusive line amounts. |
TotalAmount | Decimal | True | Total amount of the sale quote. |
TotalTax | Decimal | True | Total of all tax amounts applicable to the sale quote. |
URI | String | True | Uniform resource identifier encompasses all types of names and addresses that refer to objects on the web. |
CustomerPurchaseOrderNumber | String | False | Customer PO number. |
CustomerID | Uuid | False | Unique guid identifier belonging to the assigned customer contact. |
CustomerDisplayID | String | True | Customer contact Card ID, can also be used as a unique customer contact identifier. |
CustomerName | String | True | Name of the customer contact. |
CustomerURI | String | True | Uniform resource identifier associated with the customer contact object. |
Freight | Decimal | False | Tax inclusive freight amount applicable to the sale quote. |
FreightTaxCodeID | Uuid | True | Unique guid identifier belonging to the assigned tax code. |
FreightTaxCodeCode | String | True | 3 digit tax code. |
FreightTaxCodeURI | String | True | Uniform resource identifier associated with the tax code object. |
CategoryID | Uuid | False | Unique category identifier in the form of a guid. |
CategoryDisplayID | String | True | Display ID for the category. |
CategoryName | String | True | Name of the category. |
CategoryURI | String | True | Uniform resource identifier associated with the category object. |
SalespersonID | Uuid | False | Unique identifier in the form of a guid. |
SalespersonDisplayID | String | True | Employee contact Card ID. |
SalespersonName | String | True | Selected employee contact name. |
SalespersonURI | String | True | Uniform resource identifier associated with the employee. |
TermsDiscount | Decimal | True | Discount amount that will apply if payment is made in full by the discount date. |
TermsDiscountDate | Integer | False | If PaymentIsDue = CashOnDelivery, PrePaid, InAGivenNumberOfDaysNumber or NumberOfDaysAfterEOM then set the number of days within which the customer must pay to receive discounts. If PaymentIsDue = OnADayOfTheMonth or DayOfMonthAfterEOM then set the day of the month within which the payment must be made to receive discounts. |
TermsDiscountExpiryDate | Datetime | True | Date in which payment must be paid in full in quote to receive discount. |
TermsDiscountForEarlyPayment | Double | False | % discount for early payment. |
TermsDueDate | Datetime | True | Date in which payment is due. |
TermsFinanceCharge | Decimal | True | Late payment fee to be charged if payment is not made in full by the due date. |
TermsMonthlyChargeForLatePayment | Double | False | % monthly charge for late payment. |
TermsPaymentIsDue | String | False | Default Terms of Payment definitions: CashOnDelivery, PrePaid, InAGivenNumberOfDays, OnADayOfTheMonth, NumberOfDaysAfterEOM, DayOfMonthAfterEOM. |
TermsBalanceDueDate | Integer | True | If PaymentIsDue = CashOnDelivery, PrePaid, InAGivenNumberOfDaysNumber or NumberOfDaysAfterEOM then set the number of days within which the customer has to pay invoices. If PaymentIsDue = OnADayOfTheMonth or DayOfMonthAfterEOM then set the day of the month within which the payment must be made to pay invoices. |
Lines | String | False | An array of sale line quote information. |
CompanyFileId | String | True | The ID of the company file. Takes precedence over the CompanyFileId connection property. |
Comment | String | False | Sales Quotes Comment |
ShippingMethod | String | False | Shipping Method |
PromisedDate | Datetime | False | Transaction Promised Date. |
SpendingTransactions¶
Return, update, create and delete spend 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, AmountPaid, ChequePrinted, Date, DeliveryStatus, IsTaxInclusive, Memo, PayFrom, PayeeAddress, PaymentNumber, StatementParticulars, TotalTax, AccountID, AccountDisplayID, AccountName, CategoryID, CategoryDisplayID, CategoryName, ContactID, ContactDisplayID, ContactName, ContactType. All the other columns and operators are processed client side.
SELECT * FROM SpendingTransactions WHERE ID = "1bed306d-2da4-49b3-9023-153c3dc5b2e9"
SELECT * FROM SpendingTransactions WHERE AmountPaid = 120.45
SELECT * FROM SpendingTransactions WHERE AccountID = "44a7b2bc-0a0e-4381-86fc-9b2c7a479f8f"
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 SpendingTransactionItems#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 = "SpendingTransactionItems#TEMP" WHERE ID = "1bed306d-2da4-49b3-9023-153c3dc5b2e9"
Insert¶
The following attributes are required when performing an insert: Date, PayFrom, AccountID, ContactID.
INSERT INTO SpendingTransactions (Date, PayFrom, AccountId, ContactId) VALUES ("01/01/2019", "Account", "c195ee7d-2954-42a6-8a19-6f362442bf89", "4635fd9e-82a7-4612-ae15-32c464ef7c48")
Columns¶
Name | Type | ReadOnly | Description |
---|---|---|---|
ID [KEY] | Uuid | True | Unique identifier in the form of a guid. |
AmountPaid | Double | True | Total of all amounts paid. |
ChequePrinted | Boolean | False | True indicates you don't need to print a cheque for this spend money. False indicates you can print a cheque for spend money . |
Date | Datetime | False | The date of the entry. |
DeliveryStatus | String | False | Delivery status assigned to payment: ToBePrinted, ToBeEmailed, ToBePrintedAndEmailed, AlreadyPrintedOrSent. |
IsTaxInclusive | Boolean | False | True indicates the transaction is set to tax inclusive with the Amount inclusive of tax. False indicates the transaction is not tax inclusive with the Amount value tax exclusive. |
Memo | String | False | Memo text for the object. |
PayFrom | String | False | If allocating a banking account for the payment specify Account. If using electronic payments specify ElectronicPayments. |
PayeeAddress | String | False | Name and address of Payee, if a contact is supplied on POST and PayeeAddress is left blank, Address 1 of the contact will default. If neither Contact or PayAddress are assigned on POST then will default to null. |
PaymentNumber | String | False | ID No of payment transaction, if left blank on POST will auto increment based upon last recorded #. |
StatementParticulars | String | False | ONLY applicable for Electronic Payments. Particulars attached to electronic payment. |
TotalTax | Double | True | Total of all tax amounts applicable to the spend money. |
URI | String | True | Uniform resource identifier encompasses all types of names and addresses that refer to objects on the web. |
RowVersion | String | True | Number value that changes upon a record update, can be used for change control but does does not preserve a date or a time. |
AccountID | Uuid | False | Unique identifier for the account in the form of a guid. |
AccountDisplayID | String | True | Account code. Format includes separator ie 4-1100 |
AccountName | String | True | Name of the account. |
AccountURI | String | True | Uniform resource identifier associated with the account object. |
CategoryID | Uuid | False | Unique category identifier in the form of a guid. |
CategoryDisplayID | String | True | Display ID for the category. |
CategoryName | String | True | Name of the category. |
CategoryURI | String | True | Uniform resource identifier associated with the category object. |
ContactID | Uuid | False | Unique identifier in the form of a guid. |
ContactDisplayID | String | True | Contact Card ID, can also be used as a unique contact identifier. |
ContactName | String | True | Name of the contact record. |
ContactType | String | True | Card type of the contact record, can be either Customer, Supplier, Employee or Personal. |
ContactURI | String | True | Uniform resource identifier associated with the contact object. |
Lines | String | False | An array of spend money line information. |
CompanyFileId | String | True | The ID of the company file. Takes precedence over the CompanyFileId connection property. |
SupplierPayments¶
Return, create and delete supplier 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, AmountPaid, Date, DeliveryStatus, Memo, PayFrom, PayeeAddress, PaymentNumber, StatementParticulars, AccountID, AccountDisplayID, AccountName, SupplierID, SupplierDisplayID, SupplierName, ForeignCurrencyID, ForeignCurrencyCode, ForeignCurrencyName. All the other columns and operators are processed client side.
SELECT * FROM SupplierPayments WHERE ID = "7cabea25-6250-4df5-bb37-77eb2d38f803"
SELECT * FROM SupplierPayments WHERE Memo = "memo"
SELECT * FROM SupplierPayments WHERE AmountPaid = 46.502
SELECT * FROM SupplierPayments WHERE PayeeAddress = "WLJ Real Estate 555 High Street Chatswood NSW 2067 Australia"
Update¶
To update an existing payment, 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 SupplierPaymentItems#TEMP (AmountApplied, PurchaseID) VALUES (444.21, " 0229a075-f93a-4b6c-85d7-0ffd5ba43982")
UPDATE SupplierPayments SET Lines = "supplierpaymentitems#TEMP" WHERE ID = "c3d2350b-733a-4140-8dda-ffa0f34d4297"
Insert¶
The following attributes are required when performing an insert: PayFrom, AccountID, SupplierID, Lines.
INSERT INTO SupplierPaymentItems#TEMP (amountapplied, purchaseid) VALUES (444.21, "415ffd17-9fb7-45b4-aeb1-3af0db11ff84")
INSERT INTO SupplierPayments (PayFrom, AccountId, SupplierId, Lines) VALUES ("Account", "c195ee7d-2954-42a6-8a19-6f362442bf89", "9421b774-20fa-4c5f-bced-fbb6ecd42ebd", "SupplierPaymentItems#TEMP")
Columns¶
Name | Type | ReadOnly | Description |
---|---|---|---|
ID [KEY] | Uuid | True | Unique identifier in the form of a guid. |
AmountPaid | Double | False | Total of all amounts paid to the purchase bill/bills. |
Date | Datetime | False | The date of the entry. |
DeliveryStatus | String | False | Delivery status assigned to payment: Print = ToBePrinted, Email = ToBeEmailed, PrintAndEmail = ToBePrintedAndEmailed, Nothing = AlreadyPrintedOrSent. |
Memo | String | False | Memo text for the object. |
PayFrom | String | False | If allocating a banking account for the payment specify Account. If using electronic payments specify ElectronicPayments. |
PayeeAddress | String | False | Payee name and address of the supplier contact. |
PaymentNumber | String | False | ID No of payment transaction. |
RowVersion | String | True | Number value that changes upon a record update, can be used for change control but does does not preserve a date or a time. |
StatementParticulars | String | False | ONLY applicable for Electronic Payments. Particulars attached to electronic payment. |
URI | String | True | Uniform resource identifier encompasses all types of names and addresses that refer to objects on the web. |
AccountID | Uuid | False | Unique identifier for the account in the form of a guid. |
AccountDisplayID | String | True | Account code. Format includes separator ie 4-1100 |
AccountName | String | True | Name of the account. |
AccountURI | String | True | Uniform resource identifier associated with the account object. |
SupplierID | Uuid | False | Unique guid identifier belonging to the assigned supplier contact. |
SupplierDisplayID | String | True | Customer contact Card ID, can also be used as a unique supplier contact identifier. |
SupplierName | String | True | Name of the supplier contact. |
SupplierURI | String | True | Uniform resource identifier associated with the supplier contact object. |
ForeignCurrencyID | Uuid | True | Unique identifier in the form of a guid. |
ForeignCurrencyCode | String | True | The currency code. |
ForeignCurrencyName | String | True | The full name of the currency. |
ForeignCurrencyURI | String | True | Uniform resource identifier associated with the purchase. |
Lines | String | False | An array of purchase bill/order line information |
CompanyFileId | String | True | The ID of the company file. Takes precedence over the CompanyFileId connection property. |
TaxCodes¶
Tax codes 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, Code, Description, IsRateNegative, LuxuryCarTaxThreshold, Rate, Type, TaxCollectedAccountID, TaxCollectedAccountDisplayID, TaxCollectedAccountName, TaxPaidAccountID, TaxPaidAccountDisplayID, TaxPaidAccountName, WithholdingCreditAccountID, WithholdingCreditAccountDisplayID, WithholdingCreditAccountName, WithholdingPayableAccountID, WithholdingPayableAccountDisplayID, WithholdingPayableAccountName, ImportDutyPayableAccountID, ImportDutyPayableAccountDisplayID, ImportDutyPayableAccountName, LinkedSupplierID, LinkedSupplierDisplayID, LinkedSupplierName. All the other columns and operators are processed client side.
SELECT * FROM TaxCodes WHERE WithholdingPayableAccountID = "e1b278bb-9ba3-4d2a-8dda-a49d09dcc471"
SELECT * FROM TaxCodes WHERE Rate = 46.5
SELECT * FROM TaxCodes WHERE Description = "No ABN Withholding" AND Code = "ABN"
Insert¶
The following attributes are required when performing an insert: Code, Description, Type, TaxCollectedAccountID, TaxPaidAccountID.
INSERT INTO TaxCodes (Code, Description, Type, TaxCollectedAccountID, TaxPaidAccountID) VALUES ("CDE", "Example tax code", "NoABN_TFN", "446d2226-254b-4821-acc2-7031842e0166", "9421b774-20fa-4c5f-bced-fbb6ecd42ebd")
Columns¶
Name | Type | ReadOnly | Description |
---|---|---|---|
ID [KEY] | Uuid | True | Unique identifier in the form of a guid. |
Code | String | False | 3 digit code assigned to the tax code. |
Description | String | False | Description given to the tax code. |
IsRateNegative | Boolean | False | True indicates the tax rate is a negative value. False indicates the tax rate is a positive value. |
LuxuryCarTaxThreshold | Decimal | False | Dollar value which must be exceeded before tax is calculated using this tax code. |
Rate | Decimal | False | Rate of tax assigned. |
Type | String | False | Tax Types consist of the following: ImportDuty, SalesTax GST_VAT (Goods and Services Tax), InputTaxed, Consolidated, LuxuryCarTax, WithholdingsTax, NoABN_TFN. |
TaxCollectedAccountID | Uuid | False | Unique identifier for the account in the form of a guid. |
TaxCollectedAccountDisplayID | String | True | Account code. Format includes separator ie 4-1100 |
TaxCollectedAccountName | String | True | Name of the account. |
TaxCollectedAccountURI | String | True | Uniform resource identifier associated with the account object. |
TaxPaidAccountID | Uuid | False | Unique identifier for the account in the form of a guid. |
TaxPaidAccountDisplayID | String | True | Account code. Format includes separator ie 4-1100 |
TaxPaidAccountName | String | True | Name of the account. |
TaxPaidAccountURI | String | True | Uniform resource identifier associated with the account object. |
WithholdingCreditAccountID | Uuid | False | Unique identifier for the account in the form of a guid. |
WithholdingCreditAccountDisplayID | String | True | Account code. Format includes separator ie 4-1100 |
WithholdingCreditAccountName | String | True | Name of the account. |
WithholdingCreditAccountURI | String | True | Uniform resource identifier associated with the account object. |
WithholdingPayableAccountID | Uuid | False | Unique identifier for the account in the form of a guid. |
WithholdingPayableAccountDisplayID | String | True | Account code. Format includes separator ie 4-1100 |
WithholdingPayableAccountName | String | True | Name of the account. |
WithholdingPayableAccountURI | String | True | Uniform resource identifier associated with the account object. |
ImportDutyPayableAccountID | Uuid | False | Unique identifier for the account in the form of a guid. |
ImportDutyPayableAccountDisplayID | String | True | Account code. Format includes separator ie 4-1100 |
ImportDutyPayableAccountName | String | True | Name of the account. |
ImportDutyPayableAccountURI | String | True | Uniform resource identifier associated with the account object. |
LinkedSupplierID | Uuid | False | Unique guid identifier belonging to the assigned supplier contact. |
LinkedSupplierDisplayID | String | True | Customer contact Card ID, can also be used as a unique supplier contact identifier. |
LinkedSupplierName | String | True | Name of the supplier contact. |
LinkedSupplierURI | String | True | Uniform resource identifier associated with the supplier contact object. |
URI | String | True | Uniform resource identifier encompasses all types of names and addresses that refer to objects on the web. |
RowVersion | String | True | Number value that changes upon a record update, can be used for change control but does does not preserve a date or a time. |
CompanyFileId | String | True | The ID of the company file. Takes precedence over the CompanyFileId connection property. |
TimesheetLineEntries¶
Return timesheet entries for an AccountRight company file
Columns¶
Name | Type | ReadOnly | Description |
---|---|---|---|
TimeSheetId [KEY] | Uuid | True | Unique category identifier in the form of a guid. |
EntryId [KEY] | Uuid | True | Unique guid identifier belonging to the line entry. |
EntryDate | Datetime | False | Date of the entry, format YYYY-MM-DD HH:MM:SS |
EntryHours | Double | False | Number of hours assigned for entry day. |
EntryProcessed | Boolean | True | indicates the timesheet entry has been processed or not as part of payroll |
CompanyFileId | String | True | The ID of the company file. Takes precedence over the CompanyFileId connection property. |
TimesheetLineItems¶
Return timesheet entries for an AccountRight company file
Columns¶
Name | Type | ReadOnly | Description |
---|---|---|---|
ID [KEY] | Integer | False | LineId which represents the index of the line. |
TimeSheetId [KEY] | Uuid | True | Unique category identifier in the form of a guid. |
PayrollCategoryId | Uuid | False | Unique payroll wage category identifier in the form of a guid. |
PayrollCategoryName | String | True | Name of the payroll wage category. |
PayrollCategoryType | String | True | Indicates the type of payroll category ie: Wage, Deduction, Tax. |
PayrollCategoryURI | String | True | Uniform resource identifier associated with the payroll wage category object. |
JobId | Uuid | True | Unique guid identifier belonging to the job for this line of the timesheet entry. |
JobNumber | String | True | Number assigned to the job. |
JobName | String | True | Name assigned to the job. |
JobURI | String | True | Uniform resource identifier associated with the job object. |
ActivityId | Uuid | True | Unique guid identifier belonging to the activity assigned for this line of the timesheet entry. |
ActivityName | String | True | Name of the activity. |
ActivityURI | String | True | Uniform resource identifier associated with the activity object. |
CustomerId | Uuid | True | Unique guid identifier belonging to the customer assigned for this line of the timesheet entry. |
CustomerName | String | True | Name of the customer. |
CustomerDisplayID | String | False | Customer contact Card ID, can also be used as a unique customer contact identifier. |
CustomerURI | String | True | Uniform resource identifier associated with the customer object. |
Notes | String | True | Notes attached to the timesheet entry. |
CompanyFileId | String | True | The ID of the company file. Takes precedence over the CompanyFileId connection property. |
Timesheets¶
Return timesheet entries 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 = operator. In addition, queries with multiple criterias can be executed by combining the criteria with AND and OR operators. These columns support the above comparison type for server side filtering: ID, EmployeeName, EmployeeDisplayId, StartDate, EndDate. All the other columns and operators are processed client side.
SELECT * FROM Categories WHERE ID = "1bed306d-2da4-49b3-9023-153c3dc5b2e9"
SELECT * FROM Timesheets WHERE EmployeeName = "Mary Jones"
SELECT * FROM Timesheets WHERE EmployeeDisplayId = "EMP00001"
SELECT * FROM Timesheets WHERE StartDate = "2019-10-28"
SELECT * FROM Timesheets WHERE EndDate = "2019-11-03"
Columns¶
Name | Type | ReadOnly | Description |
---|---|---|---|
Id [KEY] | Uuid | True | Unique category identifier in the form of a guid. |
EmployeeName | String | True | Name of the employee. |
EmployeeDisplayId | String | True | Employee contact Card ID, can also be used as a unique employee contact identifier. |
EmployeeURI | String | True | Uniform resource identifier associated with the employee object. |
StartDate | Datetime | False | Date when the timesheet period starts, format YYYY-MM-DD HH:MM:SS |
EndDate | Datetime | False | Date when the timesheet period finishes, format YYYY-MM-DD HH:MM:SS |
URI | String | True | Uniform resource identifier associated with the employee object. |
CompanyFileId | String | True | The ID of the company file. Takes precedence over the CompanyFileId connection property. |
TransferringTransactions¶
Return, update, create and delete transfer 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, Amount, TransferNumber, Memo, Date, ToAccountID, ToAccountDisplayID, ToAccountName, FromAccountID, FromAccountDisplayID, FromAccountName, CategoryID, CategoryDisplayID, CategoryName. All the other columns and operators are processed client side.
SELECT * FROM TransferringTransactions WHERE ID = "bfaade84-1adf-4e2d-acfd-629e95d09de7"
SELECT * FROM TransferringTransactions WHERE Amount = 3300
SELECT * FROM TransferringTransactions WHERE TransferNumber = "TR000007" OR TransferNumber = "TR000008"
Insert¶
The following attributes are required when performing an insert: Date, Amount, ToAccountID, FromAccountID.
INSERT INTO TransferringTransactions (Date, Amount, ToAccountID, FromAccountID) VALUES ("01/01/2019", 6700.0, "446d2226-254b-4821-acc2-7031842e0166", "9421b774-20fa-4c5f-bced-fbb6ecd42ebd")
Columns¶
Name | Type | ReadOnly | Description |
---|---|---|---|
ID [KEY] | Uuid | True | Unique identifier in the form of a guid. |
Amount | Double | False | Amount to be allocated to the account, must be non zero. |
TransferNumber | String | False | ID No of payment transaction, if left blank on POST will auto increment based upon last recorded #. |
Memo | String | False | Memo text describing the transfer money transaction. |
Date | Datetime | False | Transaction date entry. |
RowVersion | String | True | Number value that changes upon a record update, can be used for change control but does does not preserve a date or a time. |
URI | String | True | Uniform resource identifier encompasses all types of names and addresses that refer to objects on the web. |
ToAccountID | Uuid | False | Unique identifier for the account in the form of a guid. |
ToAccountDisplayID | String | True | Account code. Format includes separator ie 4-1100 |
ToAccountName | String | True | Name of the account. |
ToAccountURI | String | True | Uniform resource identifier associated with the account object. |
FromAccountID | Uuid | False | Unique identifier for the account in the form of a guid. |
FromAccountDisplayID | String | True | Account code. Format includes separator ie 4-1100 |
FromAccountName | String | True | Name of the account. |
FromAccountURI | String | True | Uniform resource identifier associated with the account object. |
CategoryID | Uuid | True | Unique category identifier in the form of a guid. |
CategoryDisplayID | String | True | Display ID for the category. |
CategoryName | String | True | Name of the category. |
CategoryURI | String | True | Uniform resource identifier associated with the category object. |
CompanyFileId | String | True | The ID of the company file. Takes precedence over the CompanyFileId connection property. |
Views¶
Views are similar to tables in the way that data is represented; however, views are read-only.
Queries can be executed against a view as if it were a normal table.
MYOB Connector Views¶
Name | Description |
---|---|
AccountBudgetItems | Return general ledger account budget items |
AccountRegister | Returns general ledger account activity. |
BalanceSheetSummaryReport | Returns a Balance Sheet Summary. |
BankAccounts | Return the bank accounts for an AccountRight company file. |
BankingTransactions | Return a list of bank statement transactions for an AccountRight company file. |
BuildLineItems | Build Lines |
Categories | Return categories for cost center tracking |
CategoryRegisters | Return transactions grouped with categories |
CompanyFiles | Returns a list of company files. |
CompanyPreferences | Returns company data file preferences for an AccountRight company file. |
ContactAddressItems | Contact address items. |
CreditSettlementItems | Credit settlement items. |
Currencies | View currencies within an Accountright Live company file. |
CustomerPaymentInvoices | Customer payment items for an AccountRight company file. |
DebitSettlementItems | Debit settlement items. |
EmployeeBankAccountItems | Employee bank account items. |
EmployeePayrollAdviceReport | Return a pay advice report showing employee paycheque details for an AccountRight company file. |
EmployeePayrollCategoryItems | Employee payroll category items. |
EmployeePayrollDeductionItems | Employee payroll deduction items. |
EmployeePayrollEntitlementItems | Employee payroll entitlement items. |
EmployeePayrollExpenseItems | Employee payroll expense items. |
EmployeePayrollWageCategoryItems | Employee wage category items. |
InventoryAdjustmentItems | Inventory adjustment items for an AccountRight company file. |
ItemLocations | Return an inventoried item location information |
ItemSellingPrices | Return the item price matrix for multiple customer selling prices. |
JobBudgetItems | Job budget items. |
JournalItems | Journal line items. |
JournalTransactionHistory | Returns a list of Journals and their history for all transaction types |
JournalTransactionHistoryLineItems | Journal Transaction History Items |
JournalTransactionItems | Usage information for the operation JournalTransactionItems.rsd. |
JournalTransactions | Usage information for the operation JournalTransactions.rsd. |
Locations | Return an inventoried item location information. |
NZGSTReport | Returns a GST report for New Zealand AccountRight files. |
PayrollCategories | Returns generic information on all payroll category types for an AccountRight company file. |
PayrollCategorySummaryReport | Returns a Payroll Category Summary report for an AccountRight file. |
PayrollDeductions | Return payroll categories of type deduction for an AccountRight company file |
PayrollEntitlements | Return payroll categories of type entitlement for an AccountRight company file. |
PayrollExpenses | Return payroll categories of type expense for an AccountRight company file. |
PayrollSuperannuations | Return payroll categories of type superannuation for an AccountRight company file. |
PayrollTaxes | Return payroll categories of type tax for an AccountRight company file. |
PayrollTaxTables | Return payroll tax tables loaded into an AccountRight company file. |
ProfitAndLossSummaryReport | Returns a Profit and Loss Summary for an AccountRight file. |
ProfitLossDistributions | Returns the Profit and Loss Distribution of an AccountRight company file. |
PurchaseBillItems | Return all purchase bill types for an AccountRight company file. |
PurchaseOrderItems | Return all purchase order types for an AccountRight company file. |
ReceivingTransactionItems | Receive money transactions for an AccountRight company file. |
SaleInvoiceItems | Return all sale invoice types for an AccountRight company file. |
SaleOrderItems | Returns all sale order types for an AccountRight company file. |
SaleQuoteItems | Return all sale quote types for an AccountRight company file. |
SpendingTransactionItems | Spend money transaction items for an AccountRight company file. |
SuperannuationFunds | Superannuation fund details for an AccountRight company file. |
SupplierPaymentItems | Supplier payment items for an AccountRight company file. |
TaxCodeSummaryReport | Returns a Tax Code Summary Report for AccountRight files. |
TransactionCodingSummaryReport | Returns a report of the total number of coded and uncoded transactions in an AccountRight file. |
AccountBudgetItems¶
Return general ledger account budget items
Table Specific Information¶
Select¶
The connector will process all filters client side.
Columns¶
Name | Type | Description |
---|---|---|
FinancialYear | Integer | Financial year can consist of current FY or next FY only. |
LastMonthInFinancialYear | Integer | Number representing the last month of the financial year. For example, 3 indicates March. |
AccountID | Uuid | Unique identifier for the account in the form of a guid. |
AccountName | String | Name of the account. |
AccountDisplayID | String | Account code. Format includes separator ie 4-1100 |
AccountURI | String | Uniform resource identifier associated with the account object. |
MonthlyBudgets | String | An array of monthly account budget information. |
CompanyFileId | String | The ID of the company file. Takes precedence over the CompanyFileId connection property. |
AccountRegister¶
Returns general ledger account activity.
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, Activity, Adjustment, Month, Year, YearEndActivity, YearEndAdjustment, AccountID, AccountDisplayID, AccountName. All the other columns and operators are processed client side.
SELECT * FROM AccountRegister WHERE AccountID = "d3f55ef1-ce77-4ef2-a415-61d04db2c5fc"
SELECT * FROM AccountRegister WHERE AccountName = "AccountName"
SELECT * FROM AccountRegister WHERE Month = 6
Columns¶
Name | Type | Description |
---|---|---|
ID [KEY] | Uuid | Unique identifier in the form of a guid. |
Activity | Decimal | Net activity within profit and loss account or net movement within balance sheet accounts. |
Adjustment | Decimal | Adjustments. |
Month | Integer | Month in which the activity was generated ie December = 12. |
Year | Integer | Year in which the activity was generated ie 2014. |
YearEndActivity | Decimal | Net activity within profit and loss account or net movement within balance sheet accounts for YearEndAdjustments. |
YearEndAdjustment | Decimal | General Journal entries recorded outside of the 12 month financial year. |
RowVersion | String | Number value that changes upon a record update, can be used for change control but does does not preserve a date or a time. |
URI | String | Uniform resource identifier encompasses all types of names and addresses that refer to objects on the web. |
UnitCount | Decimal | Quantity balance of the account. |
AccountID | Uuid | Unique identifier for the account in the form of a guid. |
AccountDisplayID | String | Account code. Format includes separator ie 4-1100 |
AccountName | String | Name of the account. |
AccountURI | String | Uniform resource identifier associated with the account object. |
CompanyFileId | String | The ID of the company file. Takes precedence over the CompanyFileId connection property. |
BalanceSheetSummaryReport¶
Returns a Balance Sheet Summary.
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: AsOfDate, YearEndAdjust. All the other columns and operators are processed client side. The default behavior is AsOfDate as today's date and YearEndAdjust as false; if the AsOfDate and YearEndAdjust filters are left unset.
SELECT * FROM BalanceSheetSummaryReport
To override this behavior, the values can be set directly in the query. For example:
SELECT * FROM BalanceSheetSummaryReport WHERE AsOfDate = '2021-05-31 00:00:00' AND YearEndAdjust = false
Columns¶
Name | Type | Description |
---|---|---|
AccountTotal | Decimal | Total Amount for the Account for the given date range. |
AccountID | Uuid | Unique identifier for the account in the form of a guid. |
AccountDisplayID | String | Account code. Format includes separator ie 4-1100 |
AccountName | String | Name of the account. |
AccountURI | String | Uniform resource identifier associated with the account object. |
AsOfDate | Date | Date of the period, format YYYY-MM-DD HH:MM:SS |
YearEndAdjust | Boolean | If you are including Year end Adjustments. This is a true or False value. |
CompanyFileId | String | The ID of the company file. Takes precedence over the CompanyFileId connection property. |
BankAccounts¶
Return the bank 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, FinancialInstitution, BankAccountName, BSB, BankAccountNumber, CardName, CardNumber, AccountID, AccountDisplayID, AccountName, LastReconciledDate. All the other columns and operators are processed client side.
SELECT * FROM BankAccounts WHERE ID = REPLACE
SELECT * FROM BankAccounts WHERE FinancialInstitution = "ANZ"
Columns¶
Name | Type | Description |
---|---|---|
ID [KEY] | Uuid | Unique identifier in the form of a guid. |
FinancialInstitution | String | The code for the Financial Institution. |
BankAccountName | String | Bank account name setup. |
BSB | String | BSB as provided by the financial institution. |
BankAccountNumber | Integer | Account number as provided by the financial institution. |
CardName | String | The name on the credit card. |
CardNumber | String | The credit card number. |
AccountID | Uuid | Unique identifier for the account in the form of a guid. |
AccountDisplayID | String | Account code. Format includes separator ie 4-1100 |
AccountName | String | Name of the account. |
AccountURI | String | Uniform resource identifier associated with the account object. |
BankLinkStatus | String | The status of the Bankfeed connection. |
LastReconciledDate | Datetime | Shows the date that the Account was last reconciled. This will return as null if the Account has never been reconciled. |
URI | String | Uniform resource identifier encompasses all types of names and addresses that refer to objects on the web. |
RowVersion | String | Number value that changes upon a record update, can be used for change control but does does not preserve a date or a time. |
CompanyFileId | String | The ID of the company file. Takes precedence over the CompanyFileId connection property. |
BankingTransactions¶
Return a list of bank statement 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, Date, Description, IsCredit, StatementDate, Status, Amount, AccountID, AccountDisplayID, AccountName. All the other columns and operators are processed client side.
SELECT * FROM BankingTransactions WHERE ID = "6d35e53a-5070-432d-b9ec-f4791e353352"
SELECT * FROM BankingTransactions WHERE IsCredit = true
SELECT * FROM BankingTransactions WHERE Amount = 1400 OR Amount = 1800
Columns¶
Name | Type | Description |
---|---|---|
ID [KEY] | Uuid | Unique identifier in the form of a guid. |
Date | Datetime | Transaction date. |
Description | String | Description text for the transaction. |
IsCredit | Boolean | True or false. |
StatementDate | Datetime | Date when the BankStatement was pulled into AccountRight. |
Status | String | One of: Uncoded, Coded, Hidden. |
Amount | Decimal | Dollar amount of the withdrawal or deposit. |
URI | String | Uniform resource identifier encompasses all types of names and addresses that refer to objects on the web. |
RowVersion | String | Number value that changes upon a record update, can be used for change control but does does not preserve a date or a time. |
AccountID | Uuid | Unique identifier for the account in the form of a guid. |
AccountDisplayID | String | Account code. Format includes separator ie 4-1100 |
AccountName | String | Name of the account. |
AccountURI | String | Uniform resource identifier associated with the account object. |
CompanyFileId | String | The ID of the company file. Takes precedence over the CompanyFileId connection property. |
BuildLineItems¶
Build Lines
Columns¶
Name | Type | Description |
---|---|---|
RowID [KEY] | String | Sequence of the entry within the inventory journal set. |
RowVersion | String | Number value that changes upon a record update, can be used for change control but does does not preserve a date or a time. |
AccountDisplayID | String | Account code format includes separator ie 1-1100 |
AccountName | String | Account name belonging to the account record |
AccountUID | String | Unique guid identifier belonging to the account for the item adjustment |
AccountURI | String | Uniform resource identifier associated with the account object |
Amount | String | Dollar amount assigned to the item (Quantity * UnitCost = Amount). |
ItemName | String | Name assigned to the item. |
ItemNumber | String | Number assigned to the item. |
ItemUID | String | Unique identifier for item in the form of a guid |
ItemURI | String | Uniform resource identifier associated with the item object. |
LocationName | String | Name assigned to the location. |
LocationUID | String | Unique identifier for location in the form of a guid. |
LocationURI | String | Uniform resource identifier associated with the location object |
Memo | String | Memo text describing the transaction line |
UnitCost | Double | Unit cost assigned to the item/items, if left blank on POST will default to AverageCost. |
Categories¶
Return categories for cost center tracking
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 = operator. In addition, queries with multiple criterias can be executed by combining the criteria with AND and OR operators. These columns support the above comparison type for server side filtering: ID, DisplayID, Name, Description, IsActive. All the other columns and operators are processed client side.
SELECT * FROM Categories WHERE ID = "1bed306d-2da4-49b3-9023-153c3dc5b2e9"
SELECT * FROM Categories WHERE DisplayID = "TestDisplayID"
SELECT * FROM Categories WHERE Name = "Category 1" OR Description = "Test description"
Columns¶
Name | Type | Description |
---|---|---|
ID [KEY] | Uuid | Unique category identifier in the form of a guid. |
DisplayId | String | Display ID assigned to the category. |
Name | String | Name assigned to the category. |
Description | String | Description text for the category. |
IsActive | Boolean | True indicates the category is active. False indicates the category is inactive. |
RowVersion | String | Number value that changes upon a record update, can be used for change control but does does not preserve a date or a time. |
URI | String | Uniform resource identifier encompasses all types of names and addresses that refer to objects on the web. |
CompanyFileId | String | The ID of the company file. Takes precedence over the CompanyFileId connection property. |
CategoryRegisters¶
Return transactions grouped with categories
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 = operator. In addition, queries with multiple criterias can be executed by combining the criteria with AND and OR operators. These columns support the above comparison type for server side filtering: CategoryID, AccountID, Year, Month, Activity, YearEndActivity. All the other columns and operators are processed client side.
SELECT * FROM CategoryRegisters WHERE CategoryID = "1bed306d-2da4-49b3-9023-153c3dc5b2e9"
SELECT * FROM CategoryRegisters WHERE AccountID = "1bed306d-2da4-49b3-9023-153c3dc5b2e9"
SELECT * FROM CategoryRegisters WHERE Year = "2019"
SELECT * FROM CategoryRegisters WHERE Month = "09"
Columns¶
Name | Type | Description |
---|---|---|
CategoryID | Uuid | Unique category identifier in the form of a guid for the category. |
AccountID | Uuid | Unique category identifier in the form of a guid for the account. |
Year | Integer | Financial year in which the activity was generated ie 2014. |
Month | Integer | Month in which the activity was generated ie December = 12. |
Activity | Decimal | Net activity within profit & loss account or net movement within balance sheet accounts. |
YearEndActivity | Decimal | Net activity within profit & loss account or net movement within balance sheet accounts for YearEndAdjustments. |
RowVersion | String | Number value that changes upon a record update, can be used for change control but does does not preserve a date or a time. |
CompanyFileId | String | The ID of the company file. Takes precedence over the CompanyFileId connection property. |
CompanyFiles¶
Returns a list of company files.
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. All the other columns and operators are processed client side.
SELECT * FROM CompanyFiles WHERE ID = "68556195-8f87-4e1a-85e9-069f5fc52497"
Columns¶
Name | Type | Description |
---|---|---|
ID [KEY] | Uuid | Unique identifier in the form of a guid. |
CheckedOutBy | String | my.MYOB user that has checked out the file offline. |
CheckedOutDate | Datetime | Date the online file was checked out offline. |
Country | String | The 2 character country code. EG: AU or NZ |
LibraryPath | String | Path name to the company file.s |
Name | String | Name of the object. |
ProductVersion | String | MYOB AccountRight Live version this company file was created with. EG: 2013.3. |
ProductLevelCode | Integer | AccountRight Live product code. |
ProductLevelName | String | AccountRight Live product name, ie Basics, Standard, Plus. |
SerialNumber | String | The 12 digit serial number of the AccountRight file. |
UiAccessFlags | Integer | The file accessible product type identifier. 0-LocalAccountRight, 1-OnlineAccountRight, 2-New Essentials, 3-AccountRight and NewEssentials |
Uri | String | Uniform resource identifier encompasses all types of names and addresses that refer to objects on the web. |
CompanyPreferences¶
Returns company data file preferences for an AccountRight company file.
Table Specific Information¶
Select¶
The connector will process all filters client side.
Columns¶
Name | Type | Description |
---|---|---|
PreferToReceiveMoneyIntoUndepositedFunds | Boolean | True indicates the preference When I receive money, I prefer to Group with Other Undeposited funds is turned on. False indicates the preference When I receive money, I prefer to Group with Other Undeposited funds is turned off. |
PurchasesTermsBalanceDueDate | Integer | The balance due date which includes EOM (End of Month). |
PurchasesTermsCreditLimit | Decimal | Default customer contact credit limit. |
PurchasesTermsDiscountDate | Integer | The discount date which includes EOM (End of Month). |
PurchasesTermsDiscountForEarlyPayment | Double | % discount for early payment. |
PurchasesTermsMonthlyChargeForLatePayment | Double | % monthly charge for late payment. |
PurchasesTermsPaymentIsDue | String | Default Terms of Payment definitions: CashOnDelivery, PrePaid, InAGivenNumberOfDays, OnADayOfTheMonth, NumberOfDaysAfterEOM, DayOfMonthAfterEOM. |
PurchasesTermsPriceLevel | String | Default price level. |
PurchasesTermsUseCustomerTaxCode | Boolean | True or false. |
PurchasesTermsUseSupplierTaxCode | Boolean | True or false. |
PurchasesTermsFreightTaxCodeID | Uuid | Unique guid identifier belonging to the assigned tax code. |
PurchasesTermsFreightTaxCodeCode | String | 3 digit tax code. |
PurchasesTermsFreightTaxCodeURI | String | Uniform resource identifier associated with the tax code object. |
PurchasesTermsTaxCodeID | Uuid | Unique guid identifier belonging to the assigned tax code. |
PurchasesTermsTaxCodeCode | String | 3 digit tax code. |
PurchasesTermsTaxCodeURI | String | Uniform resource identifier associated with the tax code object. |
ReportsAndFormsReportTaxablePayments | Boolean | True indicates that report taxable payments made to contractors is turned on. False indicates that report taxable payments made to contractors is turned off. |
RowVersion | String | Number value that changes upon a record update, can be used for change control but does does not preserve a date or a time. |
SalesTermsBalanceDueDate | Integer | The balance due date which includes EOM (End of Month). |
SalesTermsCreditLimit | Decimal | Default customer contact credit limit. |
SalesTermsDiscountDate | Integer | The discount date which includes EOM (End of Month). |
SalesTermsDiscountForEarlyPayment | Double | % discount for early payment. |
SalesTermsMonthlyChargeForLatePayment | Double | % monthly charge for late payment. |
SalesTermsPaymentIsDue | String | Default Terms of Payment definitions: CashOnDelivery, PrePaid, InAGivenNumberOfDays, OnADayOfTheMonth, NumberOfDaysAfterEOM, DayOfMonthAfterEOM. |
SalesTermsPriceLevel | String | Default price level. |
SalesTermsUseCustomerTaxCode | Boolean | True or false. |
SalesTermsUseSupplierTaxCode | Boolean | True or false. |
SalesTermsFreightTaxCodeID | Uuid | Unique guid identifier belonging to the assigned tax code. |
SalesTermsFreightTaxCodeCode | String | 3 digit tax code. |
SalesTermsFreightTaxCodeURI | String | Uniform resource identifier associated with the tax code object. |
SalesTermsTaxCodeID | Uuid | Unique guid identifier belonging to the assigned tax code. |
SalesTermsTaxCodeCode | String | 3 digit tax code. |
SalesTermsTaxCodeURI | String | Uniform resource identifier associated with the tax code object. |
SystemCategoryTracking | String | Can consist of the following values: Off , OnAndNotRequired, OnAndRequired. |
SystemLockPeriodPriorTo | String | Lock period disabling entries prior to a given date. |
SystemTransactionsCannotBeChangedMustBeReversed | Boolean | True indicates that transctions cannot be changed or deleted, they must be reversed. False indicates transctions can be either changed or deleted. |
TimesheetsUseTimesheetsFor | String | Can consist of either TimeBillingAndPayroll or Payroll. |
TimesheetsWeekStartsOn | String | Day the week starts on ie: Monday, Tuesday, Wednesday. |
CompanyFileId | String | The ID of the company file. Takes precedence over the CompanyFileId connection property. |
ContactAddressItems¶
Contact address items.
Table Specific Information¶
Select¶
The connector will process all filters client side.
Columns¶
Name | Type | Description |
---|---|---|
ID [KEY] | Integer | LineId which represents the index of the line. |
ContactID [KEY] | Uuid | Unique identifier in the form of a guid. |
City | String | City of address record. |
ContactName | String | Contact Name on address record. |
Country | String | Country of location for address record. |
Email | String | Email contact of address record. |
Fax | String | Fax number of address record. |
Location | Integer | One contact can have up to five address records. |
Phone1 | String | Phone number 1 of address record. |
Phone2 | String | Phone number 2 of address record. |
Phone3 | String | Phone number 3 of address record. |
PostCode | String | Postcode of address record. |
Salutation | String | Salutation text for address record. |
State | String | State of address record. Updated in 2018.1, this field is required for addresses with a country that is blank or Australia. Where required, valid values are: AAT , ACT , NSW , NT , QLD , SA , TAS , VIC, WA. |
Street | String | Full content of Address field. |
CompanyFileId | String | The ID of the company file. Takes precedence over the CompanyFileId connection property. |
CreditSettlementItems¶
Credit settlement items.
Table Specific Information¶
Select¶
The connector will process all filters client side.
Columns¶
Name | Type | Description |
---|---|---|
ID [KEY] | Integer | LineId which represents the index of the line. |
CreditSettlementID [KEY] | Uuid | Unique identifier in the form of a guid. |
AmountApplied | Decimal | Credit amount applied to sales transactions, amount must be greater than 0. |
Type | String | Sale type which can consist of the following: Invoice, Order. |
SaleID | Uuid | Unique identifier in the form of a guid. |
SaleNumber | String | Sale number. |
SaleURI | String | Uniform resource identifier associated with the invoice. |
CompanyFileId | String | The ID of the company file. Takes precedence over the CompanyFileId connection property. |
Currencies¶
View currencies within an Accountright Live 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, Code, CurrencyName, CurrencyRate. All the other columns and operators are processed client side.
SELECT * FROM Currencies WHERE Code = "AED"
SELECT * FROM Currencies WHERE CurrencyName = "Emirati Dirham"
Columns¶
Name | Type | Description |
---|---|---|
ID [KEY] | Uuid | Unique identifier in the form of a guid. |
Code | String | Three letter ISO 4217 code for this currency. |
CurrencyName | String | Full currency name. |
CurrencyRate | Decimal | Conversion rate from local currency. |
RowVersion | String | Number value that changes upon a record update, can be used for change control but does does not preserve a date or a time. |
URI | String | Uniform resource identifier encompasses all types of names and addresses that refer to objects on the web. |
CompanyFileId | String | The ID of the company file. Takes precedence over the CompanyFileId connection property. |
CustomerPaymentInvoices¶
Customer payment items for an AccountRight company file.
Table Specific Information¶
Select¶
The connector will process all filters client side.
Columns¶
Name | Type | Description |
---|---|---|
CustomerPaymentID [KEY] | Uuid | Unique identifier in the form of a guid. |
ID [KEY] | Uuid | Unique guid identifier belonging to the invoice. |
AmountApplied | Double | Amount applied to invoice. |
Number | String | Invoice number. |
RowID | Integer | Sequence of the entry within the customer payment set. |
Type | String | Invoice type: Invoice, Order. |
URI | String | Uniform resource identifier associated with the invoice object. |
CompanyFileId | String | The ID of the company file. Takes precedence over the CompanyFileId connection property. |
DebitSettlementItems¶
Debit settlement items.
Table Specific Information¶
Select¶
The connector will process all filters client side.
Columns¶
Name | Type | Description |
---|---|---|
ID [KEY] | Integer | LineId which represents the index of the line. |
DebitSettlementID [KEY] | Uuid | Unique identifier in the form of a guid. |
Type | String | Purchase type which can consist of the following: Bill, Order. |
AmountApplied | Double | Debit amount applied to purchases transactions, amount must be greater than 0. |
PurchaseID | Uuid | Unique identifier in the form of a guid. |
PurchaseNumber | String | Purchase number. |
PurchaseURI | String | Uniform resource identifier associated with the purchase. |
CompanyFileId | String | The ID of the company file. Takes precedence over the CompanyFileId connection property. |
EmployeeBankAccountItems¶
Employee bank account items.
Table Specific Information¶
Select¶
The connector will process all filters client side.
Columns¶
Name | Type | Description |
---|---|---|
ID [KEY] | Integer | LineId which represents the index of the line. |
EmployeeID [KEY] | Uuid | Unique identifier in the form of a guid. |
BSBNumber | String | Required Only: for updating an existing employee's payment details where PaymentMethod = Electronic. Format looks like |
BankAccountName | String | Required Only: for updating an existing employee's payment details where PaymentMethod = Electronic. |
BankAccountNumber | String | Required Only: for updating an existing employee's payment details where PaymentMethod = Electronic. |
Unit | String | Units can consist of the following Enum values: Percent, Dollars, RemainingAmount (read-only for the last account in the collection). |
Value | Integer | Net pay amount to be transferred to employee's account using either Dollars (13.6) or Percent (0.00 - 100.00) |
CompanyFileId | String | The ID of the company file. Takes precedence over the CompanyFileId connection property. |
EmployeePayrollAdviceReport¶
Return a pay advice report showing employee paycheque details for an AccountRight company file.
Table Specific Information¶
Select¶
The connector will process all filters client side.
Columns¶
Name | Type | Description |
---|---|---|
AnnualSalary | Decimal | Annual salary amount for the employee (at the time of the API call). |
ChequeNumber | String | Cheque number or reference type. |
DateOfBirth | Datetime | Employees date of birth. |
EmployerABNOrTFN | String | ABN or TFN as entered within the Company Information window or Company endpoint. |
EmployerCompanyName | String | Company name as entered within the Company Information window or Company endpoint. |
EmployerURI | String | Uniform resource identifier associated with the company file. |
GrossPay | Decimal | Gross Wages for pay period (total amount of money before deductions) for the employee. |
HourlyRate | Decimal | Employee's hourly rate. |
NetPay | Decimal | Net Wages for pay period (total amount of money after deductions) for the employee. |
PayFrequency | String | Pay frequency can consist of the following: Weekly, Fortnightly, TwiceAMonth, Monthly. |
PayPeriodEndDate | Datetime | Finishing date of the pay period. |
PayPeriodStartDate | Datetime | Starting date of the pay period. |
PaymentDate | Datetime | Date when the paycheque was processed and paid, . |
SuperannuationFundID | Uuid | Unique guid identifier belonging to the superannuation fund. |
SuperannuationFundName | String | Name of the superannuation fund. |
SuperannuationFundURI | String | Uniform resource identifier associated with the superannuation fund. |
EmployeeID | Uuid | Unique guid identifier belonging to the assigned employee contact. |
EmployeeDisplayID | String | Customer contact Card ID, can also be used as a unique employee contact identifier. |
EmployeeName | String | Name of the employee contact. |
EmployeeURI | String | Uniform resource identifier associated with the employee contact object. |
Amount | Decimal | Amount processed for payroll category i.e. $ amounts for Wages, Superannuation, Deductions and Hours for entitlements. |
PayrollCategoryID | Uuid | Unique category identifier in the form of a guid. |
PayrollCategoryName | String | Name of the category. |
PayrollCategoryType | String | Indicates the type of payroll category i.e. Wage, Deduction, Expense, Tax. |
PayrollCategoryURI | String | Uniform resource identifier associated with the category object. |
Hours | Integer | Number of hours paid on hourly payroll category or accrued for entitlements. Note: If payroll category is of type salary then null is returned. |
CalculationRate | Decimal | The rate an hourly wage category is calculated, Note: If wage is of type salary then null is returned. |
YearToDate | Decimal | YTD amounts accrued this payroll year based upon pays recorded. |
CompanyFileId | String | The ID of the company file. Takes precedence over the CompanyFileId connection property. |
EmployeePayrollCategoryItems¶
Employee payroll category items.
Table Specific Information¶
Select¶
The connector will process all filters client side.
Columns¶
Name | Type | Description |
---|---|---|
EmployeeID [KEY] | Uuid | Unique identifier in the form of a guid. |
Amount | Double | The total amount. |
IsCalculated | Boolean | True or false. |
PayrollCategoryID [KEY] | Uuid | Unique category identifier in the form of a guid. |
PayrollCategoryName | String | Name of the category. |
PayrollCategoryType | String | Type of the payroll category. |
PayrollCategoryURI | String | Uniform resource identifier associated with the category object. |
CompanyFileId | String | The ID of the company file. Takes precedence over the CompanyFileId connection property. |
EmployeePayrollDeductionItems¶
Employee payroll deduction items.
Table Specific Information¶
Select¶
The connector will process all filters client side.
Columns¶
Name | Type | Description |
---|---|---|
PayrollDetailsID [KEY] | Uuid | Unique identifier in the form of a guid. |
ID [KEY] | Uuid | Unique identifier in the form of a guid. |
Name | String | Name of the deduction. |
Type | String | Type of the deduction. |
URI | String | Uniform resource identifier associated with the expense deduction. |
CompanyFileId | String | The ID of the company file. Takes precedence over the CompanyFileId connection property. |
EmployeePayrollEntitlementItems¶
Employee payroll entitlement items.
Table Specific Information¶
Select¶
The connector will process all filters client side.
Columns¶
Name | Type | Description |
---|---|---|
PayrollDetailsID [KEY] | Uuid | Unique identifier in the form of a guid. |
CarryOver | Decimal | The number of hours carried over from the previous payroll year. |
EntitlementCategoryID [KEY] | Uuid | Unique category identifier in the form of a guid. |
EntitlementCategoryName | String | Name of the category. |
EntitlementCategoryType | String | Indicates the type of payroll category ie: Wage, Deduction, Superannuation. |
EntitlementCategoryURI | String | Uniform resource identifier associated with the category object. |
IsAssigned | Boolean | True or false. |
Total | Decimal | The number of hours available on the entitlement (Total = CarryOver + YearToDate). |
YearToDate | Decimal | Net number of hours accrued this payroll year. |
CompanyFileId | String | The ID of the company file. Takes precedence over the CompanyFileId connection property. |
EmployeePayrollExpenseItems¶
Employee payroll expense items.
Table Specific Information¶
Select¶
The connector will process all filters client side.
Columns¶
Name | Type | Description |
---|---|---|
PayrollDetailsID [KEY] | Uuid | Unique identifier in the form of a guid. |
ID [KEY] | Uuid | Unique identifier in the form of a guid. |
Name | String | Name of the expense. |
Type | String | Type of the expense. |
URI | String | Uniform resource identifier associated with the expense object. |
CompanyFileId | String | The ID of the company file. Takes precedence over the CompanyFileId connection property. |
EmployeePayrollWageCategoryItems¶
Employee wage category items.
Table Specific Information¶
Select¶
The connector will process all filters client side.
Columns¶
Name | Type | Description |
---|---|---|
PayrollDetailsID [KEY] | Uuid | Unique identifier in the form of a guid. |
ID [KEY] | Uuid | Unique category identifier in the form of a guid. |
Name | String | Name of the category. |
Type | String | Type of the category. |
URI | String | Uniform resource identifier associated with the category object. |
CompanyFileId | String | The ID of the company file. Takes precedence over the CompanyFileId connection property. |
InventoryAdjustmentItems¶
Inventory adjustment items for an AccountRight company file.
Table Specific Information¶
Select¶
The connector will process all filters client side.
Columns¶
Name | Type | Description |
---|---|---|
AdjustmentID | Uuid | Unique identifier in the form of a guid. |
Amount | Double | Dollar amount assigned to the item. |
Quantity | Double | The quantity of items to be adjusted. |
UnitCost | Double | Unit cost assigned to the item/items. |
RowID | Integer | Sequence of the entry within the inventory journal set. |
Memo | String | Memo text describing the transaction line. |
RowVersion | String | RowVersion. Required during update to identify the line item. |
ItemID | Uuid | Unique item identifier in the form of a guid. |
ItemName | String | Name assigned to the item. |
ItemNumber | String | Number assigned to the item. |
ItemURI | String | Uniform resource identifier associated with the item object. |
AccountID | Uuid | Unique identifier for the account in the form of a guid. |
AccountDisplayID | String | Account code. Format includes separator ie 4-1100 |
AccountName | String | Name of the account. |
AccountURI | String | Uniform resource identifier associated with the account object. |
JobID | Uuid | Unique job identifier in the form of a guid. |
JobName | String | Name assigned to the job. |
JobNumber | String | Number assigned to the job. |
JobURI | String | Uniform resource identifier associated with the job object. |
LocationID | Uuid | Unique identifier for the location in the form of a guid. |
LocationIdentifier | String | Identifier assigned to the location. |
LocationName | String | Name of the location. |
LocationURI | String | Uniform resource identifier associated with the location object. |
CompanyFileId | String | The ID of the company file. Takes precedence over the CompanyFileId connection property. |
ItemLocations¶
Return an inventoried item location information
Columns¶
Name | Type | Description |
---|---|---|
ID [KEY] | Uuid | Unique identifier for location in the form of a guid. |
ItemID [KEY] | Uuid | Unique identifier in the form of a guid. |
Identifier | String | Identifier assigned to the location. |
QuantityOnHand | Decimal | Quantity of units held in inventory |
Name | String | Name assigned to the location. |
URI | String | Uniform resource identifier associated with the location object. |
ItemSellingPrices¶
Return 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: ItemPriceMatrixID. All the other columns and operators are processed client side.
SELECT * FROM ItemSellingPrices WHERE ItemPriceMatrixID = "3a35e83a-5070-432d-b6ec-f4791f353352"
Columns¶
Name | Type | Description |
---|---|---|
ID [KEY] | Integer | LineId which represents the index of the line. |
ItemPriceMatrixID [KEY] | Uuid | Unique identifier in the form of a guid. |
QuantityOver | Integer | The first QuantityOver must always be 0, for each additional quantity break specify the item quantity. |
LevelA | Double | Item price Level a, defaults to items Base Selling Price. |
LevelB | Double | Item price Level b, defaults to items Base Selling Price. |
LevelC | Double | Item price Level c, defaults to items Base Selling Price. |
LevelD | Double | Item price Level d, defaults to items Base Selling Price. |
LevelE | Double | Item price Level e, defaults to items Base Selling Price. |
LevelF | Double | Item price Level f, defaults to items Base Selling Price. |
CompanyFileId | String | The ID of the company file. Takes precedence over the CompanyFileId connection property. |
JobBudgetItems¶
Job budget items.
Table Specific Information¶
Select¶
The connector will process all filters client side.
Columns¶
Name | Type | Description |
---|---|---|
JobBudgetID | Uuid | Unique identifier in the form of a guid. |
Amount | Decimal | Budget amount for job. |
AccountID | Uuid | Unique identifier for the account in the form of a guid. |
AccountDisplayID | String | Account code. Format includes separator ie 4-1100 |
AccountName | String | Name of the account. |
AccountURI | String | Uniform resource identifier associated with the account object. |
CompanyFileId | String | The ID of the company file. Takes precedence over the CompanyFileId connection property. |
JournalItems¶
Journal line items.
Table Specific Information¶
Select¶
The connector will process all filters client side.
Columns¶
Name | Type | Description |
---|---|---|
JournalID [KEY] | Uuid | Unique identifier in the form of a guid. |
Amount | Double | Dollar amount posted to the line of the transaction. |
IsCredit | Boolean | True indicates the amount posted to the account as a credit. False indicates the amount posted to the account as a debit. |
IsOverriddenTaxAmount | Boolean | True indicates the tax amount has been altered. False indicates the tax amount has not been altered. |
Memo | String | Memo text applied to the line of the transaction. |
RowID [KEY] | Integer | Sequence of the entry within the general journal set. |
TaxAmount | Double | Tax amount for the line of the journal if using an applicable TaxCode. |
RowVersion | String | Row version for the item. Required to udpate an existing line. |
AccountID | Uuid | Unique identifier for the account in the form of a guid. |
AccountDisplayID | String | Account code. Format includes separator ie 4-1100 |
AccountName | String | Name of the account. |
AccountURI | String | Uniform resource identifier associated with the account object. |
JobID | Uuid | Unique job identifier in the form of a guid. |
JobNumber | String | Number assigned to the job. |
JobName | String | Name assigned to the job. |
JobURI | String | Uniform resource identifier associated with the job object. |
TaxCodeID | Uuid | Unique guid identifier belonging to the assigned tax code. |
TaxCodeCode | String | 3 digit tax code. |
TaxCodeURI | String | Uniform resource identifier associated with the tax code object. |
CompanyFileId | String | The ID of the company file. Takes precedence over the CompanyFileId connection property. |
JournalTransactionHistory¶
Returns a list of Journals and their history for all transaction types
Columns¶
Name | Type | Description |
---|---|---|
UID [KEY] | Uuid | Unique identifier in the form of a guid. |
DateOccurred | Date | Transaction date entry, format YYYY-MM-DD HH:MM:SS |
DatePosted | Datetime | Date timestamp for day the transaction was entered, format YYYY-MM-DD HH:MM:SS |
Description | String | Journal memo assigned to the transaction. |
DisplayID | String | Journal transaction id. |
GroupUID | String | UID of the first version of the transaction. |
JournalType | String | Full list of journal types:General Sale Purchase CashPayment CashReceipt Inventory |
Lines | String | An array of journal line information |
OperationType | String | Operation types:Added System Edited Deleted Reversed. |
RowVersion | String | Number value that changes upon a record update, can be used for change control but does does not preserve a date or a time. |
SourceTransactionTransactionType | String | Transaction types consist of the following: Bill Invoice SupplierPayment CustomerPayment SpendMoneyTxn ReceiveMoneyTxn TransferMoneyTxn GeneralJournal InventoryAdjustment CreditRefund CreditSettlement DebitRefund DebitSettlement |
SourceTransactionUID | String | Unique identifier for the source transaction in the form of a guid. |
SourceTransactionURI | String | Uniform resource identifier associated with the transaction object. |
URI | String | Uniform resource identifier encompasses all types of names and addresses that refer to objects on the web. |
JournalTransactionHistoryLineItems¶
Journal Transaction History Items
Columns¶
Name | Type | Description |
---|---|---|
ID [KEY] | Integer | LineId which represents the index of the line. |
JournalTransactionID [KEY] | Uuid | Unique identifier in the form of a guid. |
AccountID | Uuid | Unique identifier for the account in the form of a guid |
AccountDisplayID | String | Account code format includes separator ie 1-1100 |
AccountName | String | Account name belonging to the account record. |
AccountURI | String | Uniform resource identifier associated with the account object. |
Amount | Decimal | Dollar amount posted to the Account object for each line of the transaction. |
IsCredit | Boolean | Indicates whether the amount posted a credit to the Account object. |
JobID | Uuid | Unique guid identifier belonging to the job for the line of the service sale. |
JobName | String | Name assigned to the job |
JobNumber | String | Number assigned to the job |
JobUri | String | Uniform resource identifier associated with the job object |
LineDescription | String | Line description for each line of the transaction if one has been entered. |
JournalTransactionItems¶
Usage information for the operation JournalTransactionItems.rsd.
Table Specific Information¶
Select¶
Only the JournalTransactionID column, when using the equality comparison, is supported for server side filtering.
Columns¶
Name | Type | Description |
---|---|---|
ID [KEY] | Integer | LineId which represents the index of the line. |
JournalTransactionID [KEY] | Uuid | Unique identifier in the form of a guid. |
AccountID | Uuid | |
AccountDisplayID | String | |
AccountName | String | |
AccountURI | String | |
Amount | Decimal | |
IsCredit | Boolean | |
JobID | Uuid | |
JobName | String | |
JobNumber | String | |
JobUri | String | |
LineDescription | String | |
CompanyFileId | String | The ID of the company file. Takes precedence over the CompanyFileId connection property. |
JournalTransactions¶
Usage information for the operation JournalTransactions.rsd.
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, DisplayID, Description, DateOccurred, DatePosted, JournalType, SourceTransactionID, SourceTransactionTransactionType. All the other columns and operators are processed client side.
SELECT * FROM JournalTransactions WHERE ID = "7cabea25-6250-4df5-bb37-77eb2d38f803"
SELECT * FROM JournalTransactions WHERE Description = "description" AND SourceTransactionTransactionType = "type"
Columns¶
Name | Type | Description |
---|---|---|
ID [KEY] | Uuid | |
DisplayID | String | |
Description | String | |
DateOccurred | Datetime | |
DatePosted | Datetime | |
JournalType | String | |
RowVersion | String | |
SourceTransactionID | Uuid | |
SourceTransactionTransactionType | String | |
SourceTransactionURI | String | |
URI | String | |
CompanyFileId | String | The ID of the company file. Takes precedence over the CompanyFileId connection property. |
Locations¶
Return an inventoried item location information.
Columns¶
Name | Type | Description |
---|---|---|
ID [KEY] | Uuid | Unique location identifier in the form of a guid. |
AddressCity | String | City of address Record. |
AddressContactName | String | ContactName on Address record. |
AddressCountry | String | Country of the Location for adress Record. |
AddressEmail | String | Email contact of address record. |
AddressFax | String | Fax number of address record. |
AddressLocation | Integer | One location can have one address. |
AddressPhone1 | String | Phone number 1 of address record. |
AddressPhone2 | String | Phone number 2 of address record. |
AddressPhone3 | String | Phone number 3 of address record. |
AddressPostCode | String | Post Code of address record. |
AddressSalutation | String | Salutation text for address record. |
AddressState | String | State of Address record. |
AddressStreet | String | Full content of address field. |
AddressWebsite | String | World wide Webaddress for contact. |
CanSell | Boolean | True indicates items inventoried in the location can be sold, False indicates items inventoried in the location can not be sold |
DisplayID | String | |
Identifier | String | Identifier of the location. |
IsActive | Boolean | True indicates location is active, False indicates location is inactive. |
IsIndividual | Boolean | |
Name | String | Name for an induvidual location. |
Notes | String | Notes for the location. |
RowVersion | String | Number value that changes upon a record update, can be used for change control but does does not preserve a date or a time. |
URI | String | Uniform resource identifier encompasses all types of names and addresses that refer to objects on the web. |
NZGSTReport¶
Returns a GST report for New Zealand AccountRight files.
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: EndDate, ReportingPeriod, ReportingBasis, YearEndAdjust. All the other columns and operators are processed client side. The default behavior is EndDate as today's date, ReportingPeriod as 6m, ReportingBasis as Cash, and YearEndAdjust as false; if the EndDate, ReportingPeriod, ReportingBasis, and YearEndAdjust filters are left unset.
SELECT * FROM NZGSTReport
To override this behavior, the values can be set directly in the query. For example:
SELECT * FROM NZGSTReport WHERE EndDate = "01/04/2019" AND ReportingBasis = "Cash" AND ReportingPeriod = "6m" AND YearEndAdjust = false
Columns¶
Name | Type | Description |
---|---|---|
Address | String | The Address of the Company. |
CompanyName | String | The name of the Business. |
CreditAdjustments | Decimal | The Credit Adjustments amount for the given date range. |
DebitAdjustments | Decimal | The Debit Adjustments amount for the given date range. |
IrdNumber | String | The IRD number of the Company, format xxx-xxx-xxx. |
NetGSTSales | Decimal | The Net GST Sales amount for the given date range. |
PaymentAmount | Decimal | Total Payment Amount for the given date range. |
PhoneNumber | String | The Phone number for the Company. |
TotalGSTCollected | Decimal | Total GST Collected for the given date range. |
TotalGSTCollectedOnPurchases | Decimal | Total GST Collected on Purchases for the given date range. |
TotalGSTCollectedOnSales | Decimal | Total GST Collected on Sales for the given date range. |
TotalGSTCredit | Decimal | Total GST Credit for the given date range. |
TotalPurchases | Decimal | Total Amount of Purchases for the given date range. |
TotalSales | Decimal | Total Amount of Sales for the given date range. |
URI | String | Uniform resource identifier encompasses all types of names and addresses that refer to objects on the web. |
ZeroRatedSupplies | Decimal | Total Zero Rated Supplies for the given date range. |
EndDate | Date | Starting date of the period. |
ReportingPeriod | String | Reporting Period for the report. This will either be 1m, 2m or 6m. |
ReportingBasis | String | Reporting basis for the period, will either be Cash or Accural. |
YearEndAdjust | Boolean | If you are including Year end Adjustments. This is a true or False value. |
CompanyFileId | String | The ID of the company file. Takes precedence over the CompanyFileId connection property. |
PayrollCategories¶
Returns generic information on all payroll category 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, Name, Type. All the other columns and operators are processed client side.
SELECT * FROM PayrollCategories WHERE ID = "3a35e83a-5070-432d-b6ec-f4791f353352"
SELECT * FROM PayrollCategories WHERE Name = "criteria"
Columns¶
Name | Type | Description |
---|---|---|
ID [KEY] | Uuid | Unique identifier in the form of a guid. |
Name | String | Name of the payroll category. |
Type | String | Indicates the type of payroll category ie: Wage, Expense, Deduction, Tax, Superannuation, Entitlement. |
URI | String | Uniform resource identifier encompasses all types of names and addresses that refer to objects on the web. |
RowVersion | String | Number value that changes upon a record update, can be used for change control but does does not preserve a date or a time. |
CompanyFileId | String | The ID of the company file. Takes precedence over the CompanyFileId connection property. |
PayrollCategorySummaryReport¶
Returns a Payroll Category Summary report for an AccountRight 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: EndDate, StartDate, ReportingBasis, YearEndAdjust. All the other columns and operators are processed client side. The default behavior is EndDate as today's date, StartDate as the first day of the year, ReportingBasis as Cash, and YearEndAdjust as false; if the EndDate, StartDate, ReportingBasis, and YearEndAdjust filters are left unset.
SELECT * FROM PayrollCategorySummaryReport
To override this behavior, the values can be set directly in the query. For example:
SELECT * FROM PayrollCategorySummaryReport WHERE StartDate = "12/01/2018" AND EndDate = "01/04/2019" AND ReportingBasis = "Cash" AND YearEndAdjust = false
Columns¶
Name | Type | Description |
---|---|---|
Amount | Decimal | Total amount for the Payroll Category for that date range. |
Hours | Decimal | Total Hours for the Payroll Category for that date range. |
PayrollCategoryID | Uuid | Unique category identifier in the form of a guid. |
PayrollCategoryName | String | Name of the category. |
PayrollCategoryType | String | Payroll Category Type. |
PayrollCategoryURI | String | Uniform resource identifier associated with the category object. |
EndDate | Date | Starting date of the period. |
StartDate | Date | Ending date of the period. |
ReportingBasis | String | Reporting basis for the period, will either be Cash or Accural. |
YearEndAdjust | Boolean | If you are including Year end Adjustments. This is a true or False value. |
CompanyFileId | String | The ID of the company file. Takes precedence over the CompanyFileId connection property. |
PayrollDeductions¶
Return payroll categories of type deduction 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, CalculationBasisPercentageOf, CalculationBasisType, CalculationFixedDollarsOf, LimitAccrualPeriod, LimitFixedDollarsOf, LimitType, Name, StpCategory, Type, PayableAccountID, PayableAccountDisplayID, PayableAccountName. All the other columns and operators are processed client side.
SELECT * FROM PayrollDeductions WHERE ID = "PayrollEntitlements"
SELECT * FROM PayrollDeductions WHERE CalculationBasisPercentageOf = 28
SELECT * FROM PayrollDeductions WHERE PayableAccountName = "PayableAccountName"
Columns¶
Name | Type | Description |
---|---|---|
ID [KEY] | Uuid | Unique identifier in the form of a guid. |
CalculationBasisPercentageOf | Decimal | Percentage of super to calculate if CalculationBasis.Type = PercentageOfPayrollCategory. |
CalculationBasisType | String | The type of calculation to use can be any of the following 3: UserEntered - use amount on employees standard pay when processing. PercentageOfPayrollCategory - Equals x percentage, calculate as a % of gross wages or another payroll category. FixedDollars - Equals x dollar to contribute for all linked employees. |
CalculationFixedDollarsOf | Decimal | Nominated deduction amount CalculationBasis.Type = FixedDollars. |
LimitAccrualPeriod | String | How the maximum expense is calculated if Limit.Type = FixedDollars. Can consist of any of the following: PayPeriod, Month, Year, Hour. |
LimitFixedDollarsOf | Decimal | Maximum expense amount if Limit.Type = FixedDollars. |
LimitType | String | The type of calculation to use can be any of the following 3: NoLimit - no limits in place on expense calculations. PercentageOfPayrollCategory - Equals x percentage, limit is calculated as a % of gross wages or another payroll category. FixedDollars - Equals x dollar limit set. |
Name | String | Name of the object. |
StpCategory | String | STP Category for this payroll category. See MYOB help for more info. Valid values are: NotReportable (default), GrossPayments, PAYGWithholding, ExemptForeignIncome, CDEPPayments, AllowanceCar, AllowanceTransport, AllowanceTravel, AllowanceMeals, AllowanceLaundry, AllowanceOther, DeductionUnionProfAssocFees, DeductionWorkPlaceGiving, ReportableFringeBenefitsExemptAmount, ReportableFringeBenefitsTaxableAmount, ETPTaxableComponent, ETPTaxFreeComponent, ETPTaxWithholding, LumpSumPaymentATermination, LumpSumPaymentARedundancy, LumpSumPaymentB, LumpSumPaymentD, LumpSumPaymentE, SuperGuarantee, OrdinaryTimesEarnings, ReportableEmployerSuperContributions. |
Type | String | Indicates the type of payroll category ie: Wage, Expense, Deduction. |
PayableAccountID | Uuid | Unique identifier for the account in the form of a guid. |
PayableAccountDisplayID | String | Account code. Format includes separator ie 4-1100 |
PayableAccountName | String | Name of the account. |
PayableAccountURI | String | Uniform resource identifier associated with the account object. |
URI | String | Uniform resource identifier encompasses all types of names and addresses that refer to objects on the web. |
RowVersion | String | Number value that changes upon a record update, can be used for change control but does does not preserve a date or a time. |
CompanyFileId | String | The ID of the company file. Takes precedence over the CompanyFileId connection property. |
PayrollEntitlements¶
Return payroll categories of type entitlement 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, PrintOnPayAdvice, StpCategory, Type, CalculationAccrualPeriod, CalculationFixedHoursOf, CalculationBasisType, CarryEntitlementOverToNextYear. All the other columns and operators are processed client side.
SELECT * FROM PayrollEntitlements WHERE ID = "3a35e83a-5070-432d-b6ec-f4791f353352"
SELECT * FROM PayrollEntitlements WHERE Name = "criteria"
Columns¶
Name | Type | Description |
---|---|---|
ID [KEY] | Uuid | Unique identifier in the form of a guid. |
Name | String | Name of the object. |
PrintOnPayAdvice | Boolean | True indicates accrued leave hours will show on pay advices. False indicates accrued leave hours will not be shown on pay advices. |
StpCategory | String | STP Category for this payroll category. See MYOB help for more info. Valid values are: NotReportable (default), GrossPayments, PAYGWithholding, ExemptForeignIncome, CDEPPayments, AllowanceCar, AllowanceTransport, AllowanceTravel, AllowanceMeals, AllowanceLaundry, AllowanceOther, DeductionUnionProfAssocFees, DeductionWorkPlaceGiving, ReportableFringeBenefitsExemptAmount, ReportableFringeBenefitsTaxableAmount, ETPTaxableComponent, ETPTaxFreeComponent, ETPTaxWithholding, LumpSumPaymentATermination, LumpSumPaymentARedundancy, LumpSumPaymentB, LumpSumPaymentD, LumpSumPaymentE, SuperGuarantee, OrdinaryTimesEarnings, ReportableEmployerSuperContributions. |
Type | String | Indicates the type of payroll category ie: Wage, Expense, Deduction. |
CalculationAccrualPeriod | String | How the specified hours are accrued if CalculationBasis.Type = FixedHours. Can consist of any of the following: PayPeriod, Month, Year, Hour. |
CalculationFixedHoursOf | Decimal | Number of hours to accrue if CalculationBasis.Type = FixedHours. |
CalculationBasisType | String | The type of calculation to use can be any of the following 3: UserEntered - use amount on employees standard pay when processing. PercentageOfPayrollCategory - Equals x percentage, calculate as a % of gross wages or another payroll category. FixedDollars - Equals x dollar to contribute for all linked employees. |
CarryEntitlementOverToNextYear | Boolean | True indicates to carry over any unused leave hours when starting a new payroll year. False indicates not to carry over unused leave hours when starting a new payroll year. |
URI | String | Uniform resource identifier encompasses all types of names and addresses that refer to objects on the web. |
RowVersion | String | Number value that changes upon a record update, can be used for change control but does does not preserve a date or a time. |
PayrollWages | String | An array of linked wage category information, when recording hours for selected linked wage on an employee's pay, the entitlement balance will reduce accordingly. |
CompanyFileId | String | The ID of the company file. Takes precedence over the CompanyFileId connection property. |
PayrollExpenses¶
Return payroll categories of type expense 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, LimitAccrualPeriod, LimitFixedDollarsOf, LimitType, PrintOnPayAdvice, Threshold, Name, StpCategory, Type, PayrollCategoryID, PayrollCategoryName, PayrollCategoryType, CalculationBasisPercentageOf, CalculationBasisType, ExpenseAccountID, ExpenseAccountDisplayID, ExpenseAccountName, PayableAccountID, PayableAccountDisplayID, PayableAccountName. All the other columns and operators are processed client side.
SELECT * FROM PayrollExpenses WHERE PrintOnPayAdvice = true
SELECT * FROM PayrollExpenses WHERE LimitFixedDollarsOf = 350
SELECT * FROM PayrollExpenses WHERE StpCategory = "NotReportable"
Columns¶
Name | Type | Description |
---|---|---|
ID [KEY] | Uuid | Unique identifier in the form of a guid. |
LimitAccrualPeriod | String | How the maximum expense is calculated if Limit.Type = FixedDollars. Can consist of any of the following: PayPeriod, Month, Year, Hour. |
LimitFixedDollarsOf | Decimal | Maximum expense amount if Limit.Type = FixedDollars. |
LimitType | String | The type of calculation to use can be any of the following 3: NoLimit - no limits in place on expense calculations. PercentageOfPayrollCategory - Equals x percentage, limit is calculated as a % of gross wages or another payroll category. FixedDollars - Equals x dollar limit set. |
PrintOnPayAdvice | Boolean | True indicates accrued expenses will show on pay advices. False indicates accrued expenses will not be shown on pay advices. |
Threshold | Decimal | Amount if expense is only payable when wages exceed a certain $$ per month. |
Name | String | Name of the wage category. |
StpCategory | String | STP Category for this payroll category. See MYOB help for more info. Valid values are: NotReportable (default), GrossPayments, PAYGWithholding, ExemptForeignIncome, CDEPPayments, AllowanceCar, AllowanceTransport, AllowanceTravel, AllowanceMeals, AllowanceLaundry, AllowanceOther, DeductionUnionProfAssocFees, DeductionWorkPlaceGiving, ReportableFringeBenefitsExemptAmount, ReportableFringeBenefitsTaxableAmount, ETPTaxableComponent, ETPTaxFreeComponent, ETPTaxWithholding, LumpSumPaymentATermination, LumpSumPaymentARedundancy, LumpSumPaymentB, LumpSumPaymentD, LumpSumPaymentE, SuperGuarantee, OrdinaryTimesEarnings, ReportableEmployerSuperContributions. |
Type | String | Indicates the type of payroll category ie: Wage, Expense, Deduction. |
PayrollCategoryID | Uuid | Unique category identifier in the form of a guid. |
PayrollCategoryName | String | Name of the category. |
PayrollCategoryType | String | Indicates the type of payroll category ie: Wage, Expense, Deduction. |
PayrollCategoryURI | String | Uniform resource identifier associated with the category object. |
CalculationBasisPercentageOf | Decimal | Percentage of super to calculate if CalculationBasis.Type = PercentageOfPayrollCategory. |
CalculationBasisType | String | The type of calculation to use can be any of the following 3: UserEntered - use amount on employees standard pay when processing. PercentageOfPayrollCategory - Equals x percentage, calculate as a % of gross wages or another payroll category. FixedDollars - Equals x dollar to contribute for all linked employees. |
ExpenseAccountID | Uuid | Unique identifier for the account in the form of a guid. |
ExpenseAccountDisplayID | String | Account code. Format includes separator ie 4-1100 |
ExpenseAccountName | String | Name of the account. |
ExpenseAccountURI | String | Uniform resource identifier associated with the account object. |
PayableAccountID | Uuid | Unique identifier for the account in the form of a guid. |
PayableAccountDisplayID | String | Account code. Format includes separator ie 4-1100 |
PayableAccountName | String | Name of the account. |
PayableAccountURI | String | Uniform resource identifier associated with the account object. |
URI | String | Uniform resource identifier encompasses all types of names and addresses that refer to objects on the web. |
RowVersion | String | Number value that changes upon a record update, can be used for change control but does does not preserve a date or a time. |
CompanyFileId | String | The ID of the company file. Takes precedence over the CompanyFileId connection property. |
PayrollSuperannuations¶
Return payroll categories of type superannuation 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, ContributionType, Exclusion, PrintOnPayAdvice, Threshold, Type, LimitType, Name, PayrollCategoryID, PayrollCategoryName, PayrollCategoryType, CalculationBasisPercentageOf, CalculationBasisType, ExpenseAccountID, ExpenseAccountDisplayID, ExpenseAccountName, PayableAccountID, PayableAccountDisplayID, PayableAccountName. All the other columns and operators are processed client side.
SELECT * FROM PayrollSuperannuations WHERE Threshold = 450
SELECT * FROM PayrollSuperannuations WHERE LimitType = "NoLimit"
SELECT * FROM PayrollSuperannuations WHERE Exclusion = 23
Columns¶
Name | Type | Description |
---|---|---|
ID [KEY] | Uuid | Unique identifier in the form of a guid. |
ContributionType | String | The type of contribution basis, consist of the following: SuperannuationGuarantee, EmployeeAdditional, EmployerAdditional, Productivity, Redundancy, SalaySacrifice, Spouse. |
Exclusion | Decimal | Amount to reduce an employees wage by before calculating superannuation. |
PrintOnPayAdvice | Boolean | True indicates accrued superannuation will show on pay advices. False indicates accrued superannuation will not be shown on pay advices. |
Threshold | Decimal | Amount if superannuation is only payable when wages exceed a certain $$ per month. |
Type | String | Indicates the type of payroll category ie: Wage, Expense, Deduction. |
LimitType | String | The type of calculation to use can be any of the following 3: NoLimit - no limits in place on contribution calculations. PercentageOfPayrollCategory - Equals x percentage, limit is calculated as a % of gross wages or another payroll category. FixedDollars - Equals x dollar limit set. |
Name | String | Name of the object. |
PayrollCategoryID | Uuid | Unique category identifier in the form of a guid. |
PayrollCategoryName | String | Name of the category. |
PayrollCategoryType | String | Indicates the type of payroll category ie: Wage, Expense, Deduction. |
PayrollCategoryURI | String | Uniform resource identifier associated with the category object. |
CalculationBasisPercentageOf | Decimal | Percentage of super to calculate if CalculationBasis.Type = PercentageOfPayrollCategory. |
CalculationBasisType | String | The type of calculation to use can be any of the following 3: UserEntered - use amount on employees standard pay when processing. PercentageOfPayrollCategory - Equals x percentage, calculate as a % of gross wages or another payroll category. FixedDollars - Equals x dollar to contribute for all linked employees. |
ExpenseAccountID | Uuid | Unique identifier for the account in the form of a guid. |
ExpenseAccountDisplayID | String | Account code. Format includes separator ie 4-1100 |
ExpenseAccountName | String | Name of the account. |
ExpenseAccountURI | String | Uniform resource identifier associated with the account object. |
PayableAccountID | Uuid | Unique identifier for the account in the form of a guid. |
PayableAccountDisplayID | String | Account code. Format includes separator ie 4-1100 |
PayableAccountName | String | Name of the account. |
PayableAccountURI | String | Uniform resource identifier associated with the account object. |
URI | String | Uniform resource identifier encompasses all types of names and addresses that refer to objects on the web. |
RowVersion | String | Number value that changes upon a record update, can be used for change control but does does not preserve a date or a time. |
Exemptions | String | An array of wage categories the superannuation category is exempt from. |
CompanyFileId | String | The ID of the company file. Takes precedence over the CompanyFileId connection property. |
PayrollTaxes¶
Return payroll categories of type tax 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, TaxTableRevisionDate, PayableAccountID, PayableAccountDisplayID, PayableAccountName. All the other columns and operators are processed client side.
SELECT * FROM PayrollTaxes WHERE PayableAccountID = "02c3f70b-2570-4f2b-bd51-560c65ecb1df"
SELECT * FROM PayrollTaxes WHERE Name = "PAYG Withholding"
SELECT * FROM PayrollTaxes WHERE StpCategory = "PAYGWitholding"
Columns¶
Name | Type | Description |
---|---|---|
ID [KEY] | Uuid | Unique identifier in the form of a guid. |
Name | String | Name of the wage category. |
StpCategory | String | STP Category for this payroll category. See MYOB help for more info. Valid values are: NotReportable (default), GrossPayments, PAYGWithholding, ExemptForeignIncome, CDEPPayments, AllowanceCar, AllowanceTransport, AllowanceTravel, AllowanceMeals, AllowanceLaundry, AllowanceOther, DeductionUnionProfAssocFees, DeductionWorkPlaceGiving, ReportableFringeBenefitsExemptAmount, ReportableFringeBenefitsTaxableAmount, ETPTaxableComponent, ETPTaxFreeComponent, ETPTaxWithholding, LumpSumPaymentATermination, LumpSumPaymentARedundancy, LumpSumPaymentB, LumpSumPaymentD, LumpSumPaymentE, SuperGuarantee, OrdinaryTimesEarnings, ReportableEmployerSuperContributions. |
Type | String | Indicates the type of payroll category ie: Wage, Expense, Deduction. |
TaxTableRevisionDate | Datetime | The date payroll tax tables loaded in AccountRight were last updated. |
PayableAccountID | Uuid | Unique identifier for the account in the form of a guid. |
PayableAccountDisplayID | String | Account code. Format includes separator ie 4-1100 |
PayableAccountName | String | Name of the account. |
PayableAccountURI | String | Uniform resource identifier associated with the account object. |
URI | String | Uniform resource identifier encompasses all types of names and addresses that refer to objects on the web. |
RowVersion | String | Number value that changes upon a record update, can be used for change control but does does not preserve a date or a time. |
CompanyFileId | String | The ID of the company file. Takes precedence over the CompanyFileId connection property. |
PayrollTaxTables¶
Return payroll tax tables loaded into 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. All the other columns and operators are processed client side.
SELECT * FROM PayrollTaxTables WHERE ID = "3a35e83a-5070-432d-b6ec-f4791f353352"
SELECT * FROM PayrollTaxTables WHERE Name = "criteria"
Columns¶
Name | Type | Description |
---|---|---|
ID [KEY] | Uuid | Unique identifier in the form of a guid. |
Name | String | Name of the payroll tax table. |
RowVersion | String | Number value that changes upon a record update, can be used for change control but does does not preserve a date or a time. |
URI | String | Uniform resource identifier encompasses all types of names and addresses that refer to objects on the web. |
CompanyFileId | String | The ID of the company file. Takes precedence over the CompanyFileId connection property. |
ProfitAndLossSummaryReport¶
Returns a Profit and Loss Summary for an AccountRight 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: EndDate, StartDate, ReportingBasis, YearEndAdjust. All the other columns and operators are processed client side. The default behavior is EndDate as today's date, StartDate as the first day of the year, ReportingBasis as Cash, and YearEndAdjust as false; if the EndDate, StartDate, ReportingBasis, and YearEndAdjust filters are left unset.
SELECT * FROM ProfitAndLossSummaryReport
To override this behavior, the values can be set directly in the query. For example:
SELECT * FROM ProfitAndLossSummaryReport WHERE StartDate = "12/01/2018" AND EndDate = "01/04/2019" AND ReportingBasis = "Cash" AND YearEndAdjust = false
Columns¶
Name | Type | Description |
---|---|---|
AccountTotal | Decimal | Total Amount for the Account for the given date range. |
AccountID | Uuid | Unique identifier for the account in the form of a guid. |
AccountDisplayID | String | Account code. Format includes separator ie 4-1100 |
AccountName | String | Name of the account. |
AccountURI | String | Uniform resource identifier associated with the account object. |
EndDate | Date | Starting date of the period. |
StartDate | Date | Ending date of the period. |
ReportingBasis | String | Reporting basis for the period, will either be Cash or Accural. |
YearEndAdjust | Boolean | If you are including Year end Adjustments. This is a true or False value. |
CompanyFileId | String | The ID of the company file. Takes precedence over the CompanyFileId connection property. |
ProfitLossDistributions¶
Returns the Profit and Loss Distribution of an AccountRight company file.
Table Specific Information¶
Select¶
The connector will process all filters client side.
Columns¶
Name | Type | Description |
---|---|---|
Entity | String | Entity of the Profit and Loss Distribution. |
HeaderAccountID | Uuid | Unique identifier for the header account in the form of a guid. |
HeaderAccountDisplayID | String | Header Account code. Format includes separator ie 4-1100 |
HeaderAccountName | String | Name of the header account. |
HeaderAccountURI | String | Uniform resource identifier associated with the header account object. |
RetainedEarningsAccountID | Uuid | Unique identifier for the retained earnings account in the form of a guid. |
RetainedEarningsAccountDisplayID | String | Retained Earnings Account code. Format includes separator ie 4-1100 |
RetainedEarningsAccountName | String | Name of the retained earnings account. |
RetainedEarningsAccountURI | String | Uniform resource identifier associated with the retained earnings account object. |
CurrentEarningsAccountID | Uuid | Unique identifier for the current earnings account in the form of a guid. |
CurrentEarningsAccountDisplayID | String | Current Earnings Account code. Format includes separator ie 4-1100 |
CurrentEarningsAccountName | String | Name of the current earnings account. |
CurrentEarningsAccountURI | String | Uniform resource identifier associated with the current earnings account object. |
Value | Double | Value to be allocated to the account. |
Unit | String | Unit that the Value is measured in. |
RowVersion | String | Number value that changes upon a record update, can be used for change control but does does not preserve a date or a time. |
CompanyFileId | String | The ID of the company file. Takes precedence over the CompanyFileId connection property. |
PurchaseBillItems¶
Return all purchase bill types for an AccountRight company file.
Table Specific Information¶
Select¶
Only the PurchaseBillId and BillType columns, when using the equality comparison, are supported for server side filtering. If left unspecified, the BillType column will default to 'Item'.
Columns¶
Name | Type | Description |
---|---|---|
PurchaseBillId | Uuid | Unique identifier in the form of a guid. |
Date | Datetime | Transaction date entry. |
Description | String | Description text for the line. |
Total | Decimal | Total amount for the line item only. |
Type | String | Bill line type, can consist of the following: Transaction - All fields detailed in Lines array are consumable. Header - Only allows you to POST to Description element. Subtotal - Calculated value that is read-only. |
BillQuantity | Decimal | The quantity of goods shipped. |
ReceivedQuantity | Decimal | The quantity of goods received. |
BackorderQuantity | Decimal | Please note: To be implemented when Purchase Order functionality is available. |
UnitPrice | Decimal | Price per unit. |
DiscountPercent | Double | Discount rate applicable to the line item. |
AccountID | Uuid | Unique identifier for the account in the form of a guid. |
AccountDisplayID | String | Account code. Format includes separator ie 4-1100 |
AccountName | String | Name of the account. |
AccountURI | String | Uniform resource identifier associated with the account object. |
JobID | Uuid | Unique job identifier in the form of a guid. |
JobName | String | Name assigned to the job. |
JobNumber | String | Number assigned to the job. |
JobURI | String | Uniform resource identifier associated with the job object. |
TaxCodeID | Uuid | Unique guid identifier belonging to the assigned tax code. |
TaxCodeCode | String | 3 digit tax code. |
TaxCodeURI | String | Uniform resource identifier associated with the tax code object. |
ItemID | Uuid | Unique identifier in the form of a guid. |
ItemName | String | Name of the item. |
ItemNumber | String | The number assigned to the item. |
ItemURI | String | Uniform resource identifier associated with the item. |
RowID | Integer | Sequence of the entry within the item. |
RowVersion | String | Number value that changes upon a record update, can be used for change control but does does not preserve a date or a time. |
BillType | String | Type of the bill. One of: Item, Service, Professional, Miscellaneous. |
CompanyFileId | String | The ID of the company file. Takes precedence over the CompanyFileId connection property. |
PurchaseOrderItems¶
Return all purchase order types for an AccountRight company file.
Table Specific Information¶
Select¶
Only the PurchaseOrderId and OrderType columns, when using the equality comparison, are supported for server side filtering. If left unspecified, the OrderType column will default to 'Item'.
Columns¶
Name | Type | Description |
---|---|---|
PurchaseOrderId | Uuid | Unique identifier in the form of a guid. |
BillQuantity | Decimal | The quantity of goods shipped. |
Description | String | Description text for the line. |
DiscountPercent | Double | Discount rate applicable to the line item. |
ReceivedQuantity | Decimal | Received quantity. |
Total | Decimal | Total amount for the line item only. |
Type | String | Line type, can consist of the following: Transaction - All fields detailed in Lines array are consumable. Header - Only allows you to POST to Description element. Subtotal - Calculated value that is read-only. |
UnitPrice | Decimal | Price per unit. |
AccountID | Uuid | Unique identifier for the account in the form of a guid. |
AccountDisplayID | String | Account code. Format includes separator ie 4-1100 |
AccountName | String | Name of the account. |
AccountURI | String | Uniform resource identifier associated with the account object. |
JobID | Uuid | Unique job identifier in the form of a guid. |
JobName | String | Name assigned to the job. |
JobNumber | String | Number assigned to the job. |
JobURI | String | Uniform resource identifier associated with the job object. |
TaxCodeID | Uuid | Unique guid identifier belonging to the assigned tax code. |
TaxCodeCode | String | 3 digit tax code. |
TaxCodeURI | String | Uniform resource identifier associated with the tax code object. |
ItemID | Uuid | Unique identifier in the form of a guid. |
ItemName | String | Name of the item. |
ItemNumber | String | The number assigned to the item. |
ItemURI | String | Uniform resource identifier associated with the item. |
RowID | Integer | Sequence of the entry within the item. |
RowVersion | String | Number value that changes upon a record update, can be used for change control but does does not preserve a date or a time. |
OrderType | String | Type of the order. One of: Item, Service, Professional, Miscellaneous. |
CompanyFileId | String | The ID of the company file. Takes precedence over the CompanyFileId connection property. |
ReceivingTransactionItems¶
Receive money transactions for an AccountRight company file.
Table Specific Information¶
Select¶
The connector will process all filters client side.
Columns¶
Name | Type | Description |
---|---|---|
TransactionID | Uuid | Unique identifier in the form of a guid. |
Amount | Double | Amount to be allocated to the account. |
Memo | String | Memo text describing the transaction line. |
RowID | Integer | Sequence of the entry within the spend money set. |
RowVersion | String | RowVersion. Required during update to identify the line item. |
AccountID | Uuid | Unique identifier for the account in the form of a guid. |
AccountDisplayID | String | Account code. Format includes separator ie 4-1100 |
AccountName | String | Name of the account. |
AccountURI | String | Uniform resource identifier associated with the account object. |
JobID | Uuid | Unique job identifier in the form of a guid. |
JobName | String | Name assigned to the job. |
JobNumber | String | Number assigned to the job. |
JobURI | String | Uniform resource identifier associated with the job object. |
TaxCodeID | Uuid | Unique guid identifier belonging to the assigned tax code. |
TaxCodeCode | String | 3 digit tax code. |
TaxCodeURI | String | Uniform resource identifier associated with the tax code object. |
CompanyFileId | String | The ID of the company file. Takes precedence over the CompanyFileId connection property. |
SaleInvoiceItems¶
Return all sale invoice types for an AccountRight company file.
Table Specific Information¶
Select¶
Only the SaleInvoiceId and InvoiceType columns, when using the equality comparison, are supported for server side filtering. If left unspecified, the InvoiceType column will default to 'Item'.
Columns¶
Name | Type | Description |
---|---|---|
SaleInvoiceId | Uuid | Unique identifier in the form of a guid. |
DiscountPercent | Double | Discount rate applicable to the line of the sale invoice. |
Description | String | Description text for the sale line. |
CostOfGoodsSold | Decimal | Cost Of the Goods Sold |
RowID | Integer | Sequence of the entry within the item sale invoice set. |
RowVersion | String | The RowVersion or the line item. |
ShipQuantity | Decimal | The quantity of goods shipped. |
Total | Decimal | Invoice line type, can consist of the following: Transaction - All fields detailed in Lines array are consumable. Header - Only allows you to POST to Description element. Subtotal - Calculated value that is read-only. |
Type | String | The type of the invoice. |
UnitPrice | Decimal | Price per unit. |
AccountID | Uuid | Unique identifier for the account in the form of a guid. |
AccountDisplayID | String | Account code. Format includes separator ie 4-1100 |
AccountName | String | Name of the account. |
AccountURI | String | Uniform resource identifier associated with the account object. |
ItemID | Uuid | Unique identifier in the form of a guid. |
ItemName | String | Name of the item. |
ItemNumber | String | The number assigned to the item. |
ItemURI | String | Uniform resource identifier associated with the item. |
TaxCodeID | Uuid | Unique guid identifier belonging to the assigned tax code. |
TaxCodeCode | String | 3 digit tax code. |
TaxCodeURI | String | Uniform resource identifier associated with the tax code object. |
JobID | Uuid | Unique guid identifier belonging to the job for the line of the item sale. |
JobNumber | String | Number assigned to the job. |
JobName | String | Name assigned to the job. |
JobURI | String | Uniform resource identifier associated with the job object. |
LocationID | Uuid | Unique identifier for location in the form of a guid. |
LocationIdentifier | String | Identifier assigned to the location. |
LocationName | String | Name assigned to the location. |
LocationURI | String | Uniform resource identifier associated with the location object. |
InvoiceType | String | The type of the invoice. One of: Item, Service, Professional, Miscellaneous. |
CompanyFileId | String | The ID of the company file. Takes precedence over the CompanyFileId connection property. |
SaleOrderItems¶
Returns all sale order types for an AccountRight company file.
Table Specific Information¶
Select¶
Only the SaleOrderId and OrderType columns, when using the equality comparison, are supported for server side filtering. If left unspecified, the OrderType column will default to 'Item'.
Columns¶
Name | Type | Description |
---|---|---|
SaleOrderId | Uuid | Unique identifier in the form of a guid. |
DiscountPercent | Double | Discount rate applicable to the line of the sale order. |
Description | String | Description text for the sale line. |
RowID | Integer | Sequence of the entry within the item sale order set. |
RowVersion | String | The RowVersion or the line item. |
ShipQuantity | Decimal | The quantity of goods shipped. |
Total | Decimal | Order line type, can consist of the following: Transaction - All fields detailed in Lines array are consumable. Header - Only allows you to POST to Description element. Subtotal - Calculated value that is read-only. |
Type | String | Line type, can consist of the following: Transaction - All fields detailed in Lines array are consumable. Header - Only allows you to POST to Description element. Subtotal - Calculated value that is read-only. |
UnitPrice | Decimal | Price per unit. |
AccountID | Uuid | Unique identifier for the account in the form of a guid. |
AccountDisplayID | String | Account code. Format includes separator ie 4-1100 |
AccountName | String | Name of the account. |
AccountURI | String | Uniform resource identifier associated with the account object. |
ItemID | Uuid | Unique identifier in the form of a guid. |
ItemName | String | Name of the item. |
ItemNumber | String | The number assigned to the item. |
ItemURI | String | Uniform resource identifier associated with the item. |
TaxCodeID | Uuid | Unique guid identifier belonging to the assigned tax code. |
TaxCodeCode | String | 3 digit tax code. |
TaxCodeURI | String | Uniform resource identifier associated with the tax code object. |
OrderType | String | Type of the order. One of: Item, Service, Professional, TimeBilling, Miscellaneous. |
CompanyFileId | String | The ID of the company file. Takes precedence over the CompanyFileId connection property. |
Comment | String | Sales Order Comment |
ShippingMethod | String | Shipping Method |
PromisedDate | Datetime | Transaction Promised Date. |
SaleQuoteItems¶
Return all sale quote types for an AccountRight company file.
Table Specific Information¶
Select¶
Only the SaleQuoteId and QuoteType columns, when using the equality comparison, are supported for server side filtering. If left unspecified, the QuoteType column will default to 'Item'.
Columns¶
Name | Type | Description |
---|---|---|
SaleQuoteId | Uuid | Unique identifier in the form of a guid. |
DiscountPercent | Double | Discount rate applicable to the line of the sale quote. |
Description | String | Description text for the sale line. |
RowID | Integer | Sequence of the entry within the item sale quote set. |
RowVersion | String | The RowVersion or the line item. |
ShipQuantity | Decimal | The quantity of goods shipped. |
Total | Decimal | Total amount for the line item only. |
Type | String | Quote line type, can consist of the following: Transaction - All fields detailed in Lines array are consumable. Header - Only allows you to POST to Description element. Subtotal - Calculated value that is read-only. |
UnitPrice | Decimal | Price per unit. |
AccountID | Uuid | Unique identifier for the account in the form of a guid. |
AccountDisplayID | String | Account code. Format includes separator ie 4-1100 |
AccountName | String | Name of the account. |
AccountURI | String | Uniform resource identifier associated with the account object. |
ItemID | Uuid | Unique identifier in the form of a guid. |
ItemName | String | Name of the item. |
ItemNumber | String | The number assigned to the item. |
ItemURI | String | Uniform resource identifier associated with the item. |
TaxCodeID | Uuid | Unique guid identifier belonging to the assigned tax code. |
TaxCodeCode | String | 3 digit tax code. |
TaxCodeURI | String | Uniform resource identifier associated with the tax code object. |
QuoteType | String | The type of the quote. One of: Item, Service, Professional, TimeBilling, Miscellaneous. |
CompanyFileId | String | The ID of the company file. Takes precedence over the CompanyFileId connection property. |
SpendingTransactionItems¶
Spend money transaction items for an AccountRight company file.
Table Specific Information¶
Select¶
The connector will process all filters client side.
Columns¶
Name | Type | Description |
---|---|---|
TransactionID | Uuid | Unique identifier in the form of a guid. |
Amount | Double | Amount to be allocated to the account. |
Memo | String | Memo text describing the transaction line. |
RowID | Integer | Sequence of the entry within the spend money set. |
RowVersion | String | RowVersion. Required during update to identify the line item. |
AccountID | Uuid | Unique identifier for the account in the form of a guid. |
AccountDisplayID | String | Account code. Format includes separator ie 4-1100 |
AccountName | String | Name of the account. |
AccountURI | String | Uniform resource identifier associated with the account object. |
JobID | Uuid | Unique job identifier in the form of a guid. |
JobName | String | Name assigned to the job. |
JobNumber | String | Number assigned to the job. |
JobURI | String | Uniform resource identifier associated with the job object. |
TaxCodeID | Uuid | Unique guid identifier belonging to the assigned tax code. |
TaxCodeCode | String | 3 digit tax code. |
TaxCodeURI | String | Uniform resource identifier associated with the tax code object. |
CompanyFileId | String | The ID of the company file. Takes precedence over the CompanyFileId connection property. |
SuperannuationFunds¶
Superannuation fund details 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, EmployerMembershipNumber, Name, PhoneNumber, Website. All the other columns and operators are processed client side.
SELECT * FROM SuperannuationFunds WHERE ID = "a05b34e5-5d10-465a-98ae-aa2a7eb65a35"
SELECT * FROM SuperannuationFunds WHERE Name = "AMP Life"
Columns¶
Name | Type | Description |
---|---|---|
ID [KEY] | Uuid | Unique identifier in the form of a guid. |
EmployerMembershipNumber | String | Employer Membership number. |
Name | String | Name of the superannuation fund. |
PhoneNumber | String | Superannuation fund contact phone number. |
Website | String | World wide web address for superannuation fund. |
RowVersion | String | Number value that changes upon a record update, can be used for change control but does does not preserve a date or a time. |
URI | String | Uniform resource identifier encompasses all types of names and addresses that refer to objects on the web. |
CompanyFileId | String | The ID of the company file. Takes precedence over the CompanyFileId connection property. |
SupplierPaymentItems¶
Supplier payment items for an AccountRight company file.
Table Specific Information¶
Select¶
The connector will process all filters client side.
Columns¶
Name | Type | Description |
---|---|---|
SupplierPaymentID [KEY] | Uuid | Unique identifier in the form of a guid. |
AmountApplied | Double | Amount applied to the purchase bill or order. |
Type | String | Purchase type, can consist of the following: Bill, Order. |
PurchaseID | Uuid | Unique guid identifier belonging to the supplier purchase bill or order. |
PurchaseNumber | String | Purchase bill/order number. |
PurchaseURI | String | Uniform resource identifier associated with the purchase bill/order object. |
RowID | Integer | Sequence of the entry within the supplier payment set. |
RowVersion | String | Incrementing number that can be used for change control but does does not preserve a date or a time. |
CompanyFileId | String | The ID of the company file. Takes precedence over the CompanyFileId connection property. |
TaxCodeSummaryReport¶
Returns a Tax Code Summary Report for AccountRight files.
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: EndDate, StartDate, ReportingBasis, YearEndAdjust. All the other columns and operators are processed client side. The default behavior is EndDate as today's date, StartDate as the first day of the year, ReportingBasis as Cash, and YearEndAdjust as false; if the EndDate, StartDate, ReportingBasis, and YearEndAdjust filters are left unset.
SELECT * FROM TaxCodeSummaryReport
To override this behavior, the values can be set directly in the query. For example:
SELECT * FROM TaxCodeSummaryReport WHERE StartDate = "12/01/2018" AND EndDate = "01/04/2019" AND ReportingBasis = "Cash" AND YearEndAdjust = false
Columns¶
Name | Type | Description |
---|---|---|
PurchasesTotal | Decimal | Total Purchases amount for a Tax Code. |
SalesTotal | Decimal | Total Tax Collected amount for a Tax Code. |
TaxCodeCode | String | 3 digit tax code. |
TaxCodeID | Uuid | Unique guid identifier belonging to the assigned tax code. |
TaxCodeURI | String | Uniform resource identifier associated with the tax code object. |
TaxCollected | Decimal | Total Tax Collected amount for a Tax Code. |
TaxPaid | Decimal | Total Tax Paid amount for a Tax Code. |
TaxRate | Decimal | Tax Rate for the Tax Code. |
StartDate | Date | Ending date of the period. |
EndDate | Date | Starting date of the period. |
ReportingBasis | String | Reporting basis for the period, will either be Cash or Accural. |
YearEndAdjust | Boolean | If you are including Year end Adjustments. This is a true or False value. |
CompanyFileId | String | The ID of the company file. Takes precedence over the CompanyFileId connection property. |
TransactionCodingSummaryReport¶
Returns a report of the total number of coded and uncoded transactions in an AccountRight file.
Table Specific Information¶
Select¶
The connector will process all filters client side.
Columns¶
Name | Type | Description |
---|---|---|
Year | Integer | The calender year. |
Month | Integer | Number representing the month of the year. For example: 5 indicates May. |
TotalReceived | Integer | Total number of bankfeed transactions received. |
AutoCoded | Integer | Total number of transaction that have been coded using BankFeed Rules. |
Uncoded | Integer | Total number of bankfeed transactions that have not been coded |
CompanyFileId | String | The ID of the company file. Takes precedence over the CompanyFileId connection property. |
Stored Procedures¶
Stored procedures are function-like interfaces that extend the functionality of the connector beyond simple SELECT/INSERT/UPDATE/DELETE operations with MYOB.
Stored procedures accept a list of parameters, perform their intended function, and then return any relevant response data from MYOB, along with an indication of whether the procedure succeeded or failed.
MYOB Connector Stored Procedures¶
Name | Description |
---|---|
GetOAuthAccessToken | Gets an authentication token from MYOB. |
GetOAuthAuthorizationURL | Gets the authorization URL that must be opened separately by the user to grant access to your application. Only needed when developing Web apps. You will request the OAuthAccessToken from this URL. |
RefreshOAuthAccessToken | Refreshes the OAuth access token used for authentication with MYOB. |
GetOAuthAccessToken¶
Gets an authentication token from MYOB.
Input¶
Name | Type | Required | Description |
---|---|---|---|
AuthMode | String | False | The type of authentication mode to use. Select App for getting authentication tokens via a desktop app. Select Web for getting authentication tokens via a Web app. The allowed values are APP, WEB. The default value is APP. |
Scope | String | False | A comma-separated list of permissions to request from the user. Please check the MYOB API for a list of available permissions. |
CallbackUrl | String | False | The URL the user will be redirected to after authorizing your application. This value must match the Redirect URL you have specified in the MYOB app settings. Only needed when the Authmode parameter is Web. |
Verifier | String | False | The verifier returned from MYOB after the user has authorized your app to have access to their data. This value will be returned as a parameter to the callback URL. |
State | String | False | Indicates any state which may be useful to your application upon receipt of the response. Your application receives the same value it sent, as this parameter makes a round-trip to the MYOB authorization server and back. Uses include redirecting the user to the correct resource in your site, nonces, and cross-site-request-forgery mitigations. |
Result Set Columns¶
Name | Type | Description |
---|---|---|
OAuthAccessToken | String | The access token used for communication with MYOB. |
OAuthRefreshToken | String | The OAuth refresh token. This is the same as the access token in the case of MYOB. |
ExpiresIn | String | The remaining lifetime on the access token. A -1 denotes that it will not expire. |
GetOAuthAuthorizationURL¶
Gets the authorization URL that must be opened separately by the user to grant access to your application. Only needed when developing Web apps. You will request the OAuthAccessToken from this URL.
Input¶
Name | Type | Required | Description |
---|---|---|---|
CallbackUrl | String | False | The URL the user will be redirected to after authorizing your application. This value must match the Redirect URL in the MYOB app settings. |
Scope | String | False | A comma-separated list of scopes to request from the user. Please check the MYOB API documentation for a list of available permissions. |
State | String | False | Indicates any state which may be useful to your application upon receipt of the response. Your application receives the same value it sent, as this parameter makes a round-trip to the MYOB authorization server and back. Uses include redirecting the user to the correct resource in your site, nonces, and cross-site-request-forgery mitigations. |
Result Set Columns¶
Name | Type | Description |
---|---|---|
URL | String | The authorization URL, entered into a Web browser to obtain the verifier token and authorize your app. |
RefreshOAuthAccessToken¶
Refreshes the OAuth access token used for authentication with MYOB.
Input¶
Name | Type | Required | Description |
---|---|---|---|
OAuthRefreshToken | String | True | Set this to the token value that expired. |
Result Set Columns¶
Name | Type | Description |
---|---|---|
OAuthAccessToken | String | The authentication token returned from MYOB. This can be used in subsequent calls to other operations for this particular service. |
OAuthRefreshToken | String | This is the same as the access token. |
ExpiresIn | String | The remaining lifetime on the access token. |
System Tables¶
You can query the system tables described in this section to access schema information, information on data source functionality, and batch operation statistics.
Schema Tables¶
The following tables return database metadata for MYOB:
- sys_catalogs: Lists the available databases.
- sys_schemas: Lists the available schemas.
- sys_tables: Lists the available tables and views.
- sys_tablecolumns: Describes the columns of the available tables and views.
- sys_procedures: Describes the available stored procedures.
- sys_procedureparameters: Describes stored procedure parameters.
- sys_keycolumns: Describes the primary and foreign keys.
- sys_indexes: Describes the available indexes.
Data Source Tables¶
The following tables return information about how to connect to and query the data source:
- sys_connection_props: Returns information on the available connection properties.
- sys_sqlinfo: Describes the SELECT queries that the connector can offload to the data source.
Query Information Tables¶
The following table returns query statistics for data modification queries:
- sys_identity: Returns information about batch operations or single updates.
sys_catalogs¶
Lists the available databases.
The following query retrieves all databases determined by the connection string:
SELECT * FROM sys_catalogs
Columns¶
Name | Type | Description |
---|---|---|
CatalogName | String | The database name. |
sys_schemas¶
Lists the available schemas.
The following query retrieves all available schemas:
SELECT * FROM sys_schemas
Columns¶
Name | Type | Description |
---|---|---|
CatalogName | String | The database name. |
SchemaName | String | The schema name. |
sys_tables¶
Lists the available tables.
The following query retrieves the available tables and views:
SELECT * FROM sys_tables
Columns¶
Name | Type | Description |
---|---|---|
CatalogName | String | The database containing the table or view. |
SchemaName | String | The schema containing the table or view. |
TableName | String | The name of the table or view. |
TableType | String | The table type (table or view). |
Description | String | A description of the table or view. |
IsUpdateable | Boolean | Whether the table can be updated. |
sys_tablecolumns¶
Describes the columns of the available tables and views.
The following query returns the columns and data types for the Accounts table:
SELECT ColumnName, DataTypeName FROM sys_tablecolumns WHERE TableName='Accounts'
Columns¶
Name | Type | Description |
---|---|---|
CatalogName | String | The name of the database containing the table or view. |
SchemaName | String | The schema containing the table or view. |
TableName | String | The name of the table or view containing the column. |
ColumnName | String | The column name. |
DataTypeName | String | The data type name. |
DataType | Int32 | An integer indicating the data type. This value is determined at run time based on the environment. |
Length | Int32 | The storage size of the column. |
DisplaySize | Int32 | The designated column's normal maximum width in characters. |
NumericPrecision | Int32 | The maximum number of digits in numeric data. The column length in characters for character and date-time data. |
NumericScale | Int32 | The column scale or number of digits to the right of the decimal point. |
IsNullable | Boolean | Whether the column can contain null. |
Description | String | A brief description of the column. |
Ordinal | Int32 | The sequence number of the column. |
IsAutoIncrement | String | Whether the column value is assigned in fixed increments. |
IsGeneratedColumn | String | Whether the column is generated. |
IsHidden | Boolean | Whether the column is hidden. |
IsArray | Boolean | Whether the column is an array. |
IsReadOnly | Boolean | Whether the column is read-only. |
IsKey | Boolean | Indicates whether a field returned from sys_tablecolumns is the primary key of the table. |
sys_procedures¶
Lists the available stored procedures.
The following query retrieves the available stored procedures:
SELECT * FROM sys_procedures
Columns¶
Name | Type | Description |
---|---|---|
CatalogName | String | The database containing the stored procedure. |
SchemaName | String | The schema containing the stored procedure. |
ProcedureName | String | The name of the stored procedure. |
Description | String | A description of the stored procedure. |
ProcedureType | String | The type of the procedure, such as PROCEDURE or FUNCTION. |
sys_procedureparameters¶
Describes stored procedure parameters.
The following query returns information about all of the input parameters for the SelectEntries stored procedure:
SELECT * FROM sys_procedureparameters WHERE ProcedureName='SelectEntries' AND Direction=1 OR Direction=2
Columns¶
Name | Type | Description |
---|---|---|
CatalogName | String | The name of the database containing the stored procedure. |
SchemaName | String | The name of the schema containing the stored procedure. |
ProcedureName | String | The name of the stored procedure containing the parameter. |
ColumnName | String | The name of the stored procedure parameter. |
Direction | Int32 | An integer corresponding to the type of the parameter: input (1), input/output (2), or output(4). input/output type parameters can be both input and output parameters. |
DataTypeName | String | The name of the data type. |
DataType | Int32 | An integer indicating the data type. This value is determined at run time based on the environment. |
Length | Int32 | The number of characters allowed for character data. The number of digits allowed for numeric data. |
NumericPrecision | Int32 | The maximum precision for numeric data. The column length in characters for character and date-time data. |
NumericScale | Int32 | The number of digits to the right of the decimal point in numeric data. |
IsNullable | Boolean | Whether the parameter can contain null. |
IsRequired | Boolean | Whether the parameter is required for execution of the procedure. |
IsArray | Boolean | Whether the parameter is an array. |
Description | String | The description of the parameter. |
Ordinal | Int32 | The index of the parameter. |
sys_keycolumns¶
Describes the primary and foreign keys.
The following query retrieves the primary key for the Accounts table:
SELECT * FROM sys_keycolumns WHERE IsKey='True' AND TableName='Accounts'
Columns¶
Name | Type | Description |
---|---|---|
CatalogName | String | The name of the database containing the key. |
SchemaName | String | The name of the schema containing the key. |
TableName | String | The name of the table containing the key. |
ColumnName | String | The name of the key column. |
IsKey | Boolean | Whether the column is a primary key in the table referenced in the TableName field. |
IsForeignKey | Boolean | Whether the column is a foreign key referenced in the TableName field. |
PrimaryKeyName | String | The name of the primary key. |
ForeignKeyName | String | The name of the foreign key. |
ReferencedCatalogName | String | The database containing the primary key. |
ReferencedSchemaName | String | The schema containing the primary key. |
ReferencedTableName | String | The table containing the primary key. |
ReferencedColumnName | String | The column name of the primary key. |
sys_foreignkeys¶
Describes the foreign keys.
The following query retrieves all foreign keys which refer to other tables:
SELECT * FROM sys_foreignkeys WHERE ForeignKeyType = 'FOREIGNKEY_TYPE_IMPORT'
Columns¶
Name | Type | Description |
---|---|---|
CatalogName | String | The name of the database containing the key. |
SchemaName | String | The name of the schema containing the key. |
TableName | String | The name of the table containing the key. |
ColumnName | String | The name of the key column. |
PrimaryKeyName | String | The name of the primary key. |
ForeignKeyName | String | The name of the foreign key. |
ReferencedCatalogName | String | The database containing the primary key. |
ReferencedSchemaName | String | The schema containing the primary key. |
ReferencedTableName | String | The table containing the primary key. |
ReferencedColumnName | String | The column name of the primary key. |
ForeignKeyType | String | Designates whether the foreign key is an import (points to other tables) or export (referenced from other tables) key. |
sys_primarykeys¶
Describes the primary keys.
The following query retrieves the primary keys from all tables and views:
SELECT * FROM sys_primarykeys
Columns¶
Name | Type | Description |
---|---|---|
CatalogName | String | The name of the database containing the key. |
SchemaName | String | The name of the schema containing the key. |
TableName | String | The name of the table containing the key. |
ColumnName | String | The name of the key column. |
KeySeq | String | The sequence number of the primary key. |
KeyName | String | The name of the primary key. |
sys_indexes¶
Describes the available indexes. By filtering on indexes, you can write more selective queries with faster query response times.
The following query retrieves all indexes that are not primary keys:
SELECT * FROM sys_indexes WHERE IsPrimary='false'
Columns¶
Name | Type | Description |
---|---|---|
CatalogName | String | The name of the database containing the index. |
SchemaName | String | The name of the schema containing the index. |
TableName | String | The name of the table containing the index. |
IndexName | String | The index name. |
ColumnName | String | The name of the column associated with the index. |
IsUnique | Boolean | True if the index is unique. False otherwise. |
IsPrimary | Boolean | True if the index is a primary key. False otherwise. |
Type | Int16 | An integer value corresponding to the index type: statistic (0), clustered (1), hashed (2), or other (3). |
SortOrder | String | The sort order: A for ascending or D for descending. |
OrdinalPosition | Int16 | The sequence number of the column in the index. |
sys_connection_props¶
Returns information on the available connection properties and those set in the connection string.
When querying this table, the config connection string should be used:
jdbc:cdata:myob:config:
This connection string enables you to query this table without a valid connection.
The following query retrieves all connection properties that have been set in the connection string or set through a default value:
SELECT * FROM sys_connection_props WHERE Value <> ''
Columns¶
Name | Type | Description |
---|---|---|
Name | String | The name of the connection property. |
ShortDescription | String | A brief description. |
Type | String | The data type of the connection property. |
Default | String | The default value if one is not explicitly set. |
Values | String | A comma-separated list of possible values. A validation error is thrown if another value is specified. |
Value | String | The value you set or a preconfigured default. |
Required | Boolean | Whether the property is required to connect. |
Category | String | The category of the connection property. |
IsSessionProperty | String | Whether the property is a session property, used to save information about the current connection. |
Sensitivity | String | The sensitivity level of the property. This informs whether the property is obfuscated in logging and authentication forms. |
PropertyName | String | A camel-cased truncated form of the connection property name. |
Ordinal | Int32 | The index of the parameter. |
CatOrdinal | Int32 | The index of the parameter category. |
Hierarchy | String | Shows dependent properties associated that need to be set alongside this one. |
Visible | Boolean | Informs whether the property is visible in the connection UI. |
ETC | String | Various miscellaneous information about the property. |
sys_sqlinfo¶
Describes the SELECT query processing that the connector can offload to the data source.
Discovering the Data Source's SELECT Capabilities¶
Below is an example data set of SQL capabilities. Some aspects of SELECT functionality are returned in a comma-separated list if supported; otherwise, the column contains NO.
Name | Description | Possible Values |
---|---|---|
AGGREGATE_FUNCTIONS | Supported aggregation functions. | AVG , COUNT , MAX , MIN , SUM , DISTINCT |
COUNT | Whether COUNT function is supported. | YES , NO |
IDENTIFIER_QUOTE_OPEN_CHAR | The opening character used to escape an identifier. | [ |
IDENTIFIER_QUOTE_CLOSE_CHAR | The closing character used to escape an identifier. | ] |
SUPPORTED_OPERATORS | A list of supported SQL operators. | = , > , < , >= , <= , <> , != , LIKE , NOT LIKE , IN , NOT IN , IS NULL , IS NOT NULL , AND , OR |
GROUP_BY | Whether GROUP BY is supported, and, if so, the degree of support. | NO , NO_RELATION , EQUALS_SELECT , SQL_GB_COLLATE |
STRING_FUNCTIONS | Supported string functions. | LENGTH , CHAR , LOCATE , REPLACE , SUBSTRING , RTRIM , LTRIM , RIGHT , LEFT , UCASE , SPACE , SOUNDEX , LCASE , CONCAT , ASCII , REPEAT , OCTET , BIT , POSITION , INSERT , TRIM , UPPER , REGEXP , LOWER , DIFFERENCE , CHARACTER , SUBSTR , STR , REVERSE , PLAN , UUIDTOSTR , TRANSLATE , TRAILING , TO , STUFF , STRTOUUID , STRING , SPLIT , SORTKEY , SIMILAR , REPLICATE , PATINDEX , LPAD , LEN , LEADING , KEY , INSTR , INSERTSTR , HTML , GRAPHICAL , CONVERT , COLLATION , CHARINDEX , BYTE |
NUMERIC_FUNCTIONS | Supported numeric functions. | ABS , ACOS , ASIN , ATAN , ATAN2 , CEILING , COS , COT , EXP , FLOOR , LOG , MOD , SIGN , SIN , SQRT , TAN , PI , RAND , DEGREES , LOG10 , POWER , RADIANS , ROUND , TRUNCATE |
TIMEDATE_FUNCTIONS | Supported date/time functions. | NOW , CURDATE , DAYOFMONTH , DAYOFWEEK , DAYOFYEAR , MONTH , QUARTER , WEEK , YEAR , CURTIME , HOUR , MINUTE , SECOND , TIMESTAMPADD , TIMESTAMPDIFF , DAYNAME , MONTHNAME , CURRENT_DATE , CURRENT_TIME , CURRENT_TIMESTAMP , EXTRACT |
REPLICATION_SKIP_TABLES | Indicates tables skipped during replication. | |
REPLICATION_TIMECHECK_COLUMNS | A string array containing a list of columns which will be used to check for (in the given order) to use as a modified column during replication. | |
IDENTIFIER_PATTERN | String value indicating what string is valid for an identifier. | |
SUPPORT_TRANSACTION | Indicates if the provider supports transactions such as commit and rollback. | YES , NO |
DIALECT | Indicates the SQL dialect to use. | |
KEY_PROPERTIES | Indicates the properties which identify the uniform database. | |
SUPPORTS_MULTIPLE_SCHEMAS | Indicates if multiple schemas may exist for the provider. | YES , NO |
SUPPORTS_MULTIPLE_CATALOGS | Indicates if multiple catalogs may exist for the provider. | YES , NO |
DATASYNCVERSION | The Data Sync version needed to access this driver. | Standard , Starter , Professional , Enterprise |
DATASYNCCATEGORY | The Data Sync category of this driver. | Source , Destination , Cloud Destination |
SUPPORTSENHANCEDSQL | Whether enhanced SQL functionality beyond what is offered by the API is supported. | TRUE , FALSE |
SUPPORTS_BATCH_OPERATIONS | Whether batch operations are supported. | YES , NO |
SQL_CAP | All supported SQL capabilities for this driver. | SELECT , INSERT , DELETE , UPDATE , TRANSACTIONS , ORDERBY , OAUTH , ASSIGNEDID , LIMIT , LIKE , BULKINSERT , COUNT , BULKDELETE , BULKUPDATE , GROUPBY , HAVING , AGGS , OFFSET , REPLICATE , COUNTDISTINCT , JOINS , DROP , CREATE , DISTINCT , INNERJOINS , SUBQUERIES , ALTER , MULTIPLESCHEMAS , GROUPBYNORELATION , OUTERJOINS , UNIONALL , UNION , UPSERT , GETDELETED , CROSSJOINS , GROUPBYCOLLATE , MULTIPLECATS , FULLOUTERJOIN , MERGE , JSONEXTRACT , BULKUPSERT , SUM , SUBQUERIESFULL , MIN , MAX , JOINSFULL , XMLEXTRACT , AVG , MULTISTATEMENTS , FOREIGNKEYS , CASE , LEFTJOINS , COMMAJOINS , WITH , LITERALS , RENAME , NESTEDTABLES , EXECUTE , BATCH , BASIC , INDEX |
PREFERRED_CACHE_OPTIONS | A string value specifies the preferred cacheOptions. | |
ENABLE_EF_ADVANCED_QUERY | Indicates if the driver directly supports advanced queries coming from Entity Framework. If not, queries will be handled client side. | YES , NO |
PSEUDO_COLUMNS | A string array indicating the available pseudo columns. | |
MERGE_ALWAYS | If the value is true, The Merge Mode is forcibly executed in Data Sync. | TRUE , FALSE |
REPLICATION_MIN_DATE_QUERY | A select query to return the replicate start datetime. | |
REPLICATION_MIN_FUNCTION | Allows a provider to specify the formula name to use for executing a server side min. | |
REPLICATION_START_DATE | Allows a provider to specify a replicate startdate. | |
REPLICATION_MAX_DATE_QUERY | A select query to return the replicate end datetime. | |
REPLICATION_MAX_FUNCTION | Allows a provider to specify the formula name to use for executing a server side max. | |
IGNORE_INTERVALS_ON_INITIAL_REPLICATE | A list of tables which will skip dividing the replicate into chunks on the initial replicate. | |
CHECKCACHE_USE_PARENTID | Indicates whether the CheckCache statement should be done against the parent key column. | TRUE , FALSE |
CREATE_SCHEMA_PROCEDURES | Indicates stored procedures that can be used for generating schema files. |
The following query retrieves the operators that can be used in the WHERE clause:
SELECT * FROM sys_sqlinfo WHERE Name = 'SUPPORTED_OPERATORS'
Note that individual tables may have different limitations or requirements on the WHERE clause; refer to the Data Model section for more information.
Columns¶
Name | Type | Description |
---|---|---|
NAME | String | A component of SQL syntax, or a capability that can be processed on the server. |
VALUE | String | Detail on the supported SQL or SQL syntax. |
sys_identity¶
Returns information about attempted modifications.
The following query retrieves the Ids of the modified rows in a batch operation:
SELECT * FROM sys_identity
Columns¶
Name | Type | Description |
---|---|---|
Id | String | The database-generated ID returned from a data modification operation. |
Batch | String | An identifier for the batch. 1 for a single operation. |
Operation | String | The result of the operation in the batch: INSERTED, UPDATED, or DELETED. |
Message | String | SUCCESS or an error message if the update in the batch failed. |
Advanced Configurations Properties¶
The advanced configurations properties are the various options that can be used to establish a connection. This section provides a complete list of the options you can configure. Click the links for further details.
Property | Description |
---|---|
User | The MYOB user account used to authenticate. |
Password | The password used to authenticate the user. |
Property | Description |
---|---|
Instance | Whether to use On-Premise instance or Cloud instance when connecting to MyOB. |
CompanyFileId | The ID of the company file. If not specified, the ID of the first returned company file will be used. |
URL | The URL of the MYOB instance. |
Property | Description |
---|---|
InitiateOAuth | Set this property to initiate the process to obtain or refresh the OAuth access token when you connect. |
OAuthClientId | The client ID assigned when you register your application with an OAuth authorization server. |
OAuthClientSecret | The client secret assigned when you register your application with an OAuth authorization server. |
OAuthAccessToken | The access token for connecting using OAuth. |
OAuthSettingsLocation | The location of the settings file where OAuth values are saved when InitiateOAuth is set to GETANDREFRESH or REFRESH . Alternatively, you can hold this location in memory by specifying a value starting with 'memory://' . |
CallbackURL | The OAuth callback URL to return to when authenticating. This value must match the callback URL you specify in your app settings. |
OAuthVerifier | The verifier code returned from the OAuth authorization URL. |
OAuthRefreshToken | The OAuth refresh token for the corresponding OAuth access token. |
OAuthExpiresIn | The lifetime in seconds of the OAuth AccessToken. |
OAuthTokenTimestamp | The Unix epoch timestamp in milliseconds when the current Access Token was created. |
Property | Description |
---|---|
SSLServerCert | The certificate to be accepted from the server when connecting using TLS/SSL. |
Property | Description |
---|---|
Location | A path to the directory that contains the schema files defining tables, views, and stored procedures. |
BrowsableSchemas | This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA, SchemaB, SchemaC. |
Tables | This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA, TableB, TableC. |
Views | Restricts the views reported to a subset of the available tables. For example, Views=ViewA, ViewB, ViewC. |
Property | Description |
---|---|
MaxRows | Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses. |
Other | These hidden properties are used only in specific use cases. |
Pagesize | The maximum number of results to return per page from MYOB. |
PseudoColumns | This property indicates whether or not to include pseudo columns as columns to the table. |
Timeout | The value in seconds until the timeout error is thrown, canceling the operation. |
UserDefinedViews | A filepath pointing to the JSON configuration file containing your custom views. |
Authentication¶
This section provides a complete list of authentication properties you can configure.
Property | Description |
---|---|
User | The MYOB user account used to authenticate. |
Password | The password used to authenticate the user. |
User¶
The MYOB user account used to authenticate.
Data Type¶
string
Default Value¶
""
Remarks¶
Together with Password, this field is used to authenticate against the MYOB server.
Password¶
The password used to authenticate the user.
Data Type¶
string
Default Value¶
""
Remarks¶
The User and Password
are together used to authenticate with the server.
Connection¶
This section provides a complete list of connection properties you can configure.
Property | Description |
---|---|
Instance | Whether to use On-Premise instance or Cloud instance when connecting to MyOB. |
CompanyFileId | The ID of the company file. If not specified, the ID of the first returned company file will be used. |
URL | The URL of the MYOB instance. |
Instance¶
Whether to use On-Premise instance or Cloud instance when connecting to MyOB.
Data Type¶
string
Default Value¶
Cloud
Remarks¶
Whether to use On-Premise instance or Cloud instance when connecting to MyOB
CompanyFileId¶
The ID of the company file. If not specified, the ID of the first returned company file will be used.
Data Type¶
string
Default Value¶
""
Remarks¶
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
URL¶
The URL of the MYOB instance.
Data Type¶
string
Default Value¶
""
Remarks¶
The URL of the MYOB instance.
OAuth¶
This section provides a complete list of OAuth properties you can configure.
Property | Description |
---|---|
InitiateOAuth | Set this property to initiate the process to obtain or refresh the OAuth access token when you connect. |
OAuthClientId | The client ID assigned when you register your application with an OAuth authorization server. |
OAuthClientSecret | The client secret assigned when you register your application with an OAuth authorization server. |
OAuthAccessToken | The access token for connecting using OAuth. |
OAuthSettingsLocation | The location of the settings file where OAuth values are saved when InitiateOAuth is set to GETANDREFRESH or REFRESH . Alternatively, you can hold this location in memory by specifying a value starting with 'memory://' . |
CallbackURL | The OAuth callback URL to return to when authenticating. This value must match the callback URL you specify in your app settings. |
OAuthVerifier | The verifier code returned from the OAuth authorization URL. |
OAuthRefreshToken | The OAuth refresh token for the corresponding OAuth access token. |
OAuthExpiresIn | The lifetime in seconds of the OAuth AccessToken. |
OAuthTokenTimestamp | The Unix epoch timestamp in milliseconds when the current Access Token was created. |
InitiateOAuth¶
Set this property to initiate the process to obtain or refresh the OAuth access token when you connect.
Possible Values¶
OFF
, GETANDREFRESH
, REFRESH
Data Type¶
string
Default Value¶
OFF
Remarks¶
The following options are available:
OFF
: Indicates that the OAuth flow will be handled entirely by the user. An OAuthAccessToken will be required to authenticate.GETANDREFRESH
: Indicates that the entire OAuth Flow will be handled by the connector. If no token currently exists, it will be obtained by prompting the user via the browser. If a token exists, it will be refreshed when applicable.REFRESH
: Indicates that the connector will only handle refreshing the OAuthAccessToken. The user will never be prompted by the connector to authenticate via the browser. The user must handle obtaining the OAuthAccessToken and OAuthRefreshToken initially.
OAuthClientId¶
The client ID assigned when you register your application with an OAuth authorization server.
Data Type¶
string
Default Value¶
""
Remarks¶
As part of registering an OAuth application, you will receive the OAuthClientId
value, sometimes also called a consumer key, and a client secret, the OAuthClientSecret.
OAuthClientSecret¶
The client secret assigned when you register your application with an OAuth authorization server.
Data Type¶
string
Default Value¶
""
Remarks¶
As part of registering an OAuth application, you will receive the OAuthClientId, also called a consumer key. You will also receive a client secret, also called a consumer secret. Set the client secret in the OAuthClientSecret
property.
OAuthAccessToken¶
The access token for connecting using OAuth.
Data Type¶
string
Default Value¶
""
Remarks¶
The OAuthAccessToken
property is used to connect using OAuth. The OAuthAccessToken
is retrieved from the OAuth server as part of the authentication process. It has a server-dependent timeout and can be reused between requests.
The access token is used in place of your user name and password. The access token protects your credentials by keeping them on the server.
OAuthSettingsLocation¶
The location of the settings file where OAuth values are saved when InitiateOAuth is set to GETANDREFRESH or REFRESH. Alternatively, you can hold this location in memory by specifying a value starting with 'memory://'
.
Data Type¶
string
Default Value¶
%APPDATA%\CData\Acumatica Data Provider\OAuthSettings.txt
Remarks¶
When InitiateOAuth is set to GETANDREFRESH
or REFRESH
, the driver saves OAuth values to avoid requiring the user to manually enter OAuth connection properties and to allow the credentials to be shared across connections or processes.
Instead of specifying a file path, you can use memory storage. Memory locations are specified by using a value starting with 'memory://'
followed by a unique identifier for that set of credentials (for example, memory://user1). The identifier can be anything you choose but should be unique to the user. Unlike file-based storage, where credentials persist across connections, memory storage loads the credentials into static memory, and the credentials are shared between connections using the same identifier for the life of the process. To persist credentials outside the current process, you must manually store the credentials prior to closing the connection. This enables you to set them in the connection when the process is started again. You can retrieve OAuth property values with a query to the sys_connection_props
system table. If there are multiple connections using the same credentials, the properties are read from the previously closed connection.
The default location is "%APPDATA%\CData\Acumatica Data Provider\OAuthSettings.txt" with %APPDATA%
set to the user's configuration directory. The default values are
- Windows: "
register://%DSN
" - Unix: "%AppData%..."
where DSN is the name of the current DSN used in the open connection.
The following table lists the value of %APPDATA%
by OS:
Platform | %APPDATA% |
---|---|
Windows | The value of the APPDATA environment variable |
Linux | ~/.config |
CallbackURL¶
The OAuth callback URL to return to when authenticating. This value must match the callback URL you specify in your app settings.
Data Type¶
string
Default Value¶
""
Remarks¶
During the authentication process, the OAuth authorization server redirects the user to this URL. This value must match the callback URL you specify in your app settings.
OAuthVerifier¶
The verifier code returned from the OAuth authorization URL.
Data Type¶
string
Default Value¶
""
Remarks¶
The verifier code returned from the OAuth authorization URL. This can be used on systems where a browser cannot be launched such as headless systems.
Authentication on Headless Machines¶
See to obtain the OAuthVerifier
value.
Set OAuthSettingsLocation along with OAuthVerifier
. When you connect, the connector exchanges the OAuthVerifier
for the OAuth authentication tokens and saves them, encrypted, to the specified location. Set InitiateOAuth to GETANDREFRESH to automate the exchange.
Once the OAuth settings file has been generated, you can remove OAuthVerifier
from the connection properties and connect with OAuthSettingsLocation set.
To automatically refresh the OAuth token values, set OAuthSettingsLocation and additionally set InitiateOAuth to REFRESH.
OAuthRefreshToken¶
The OAuth refresh token for the corresponding OAuth access token.
Data Type¶
string
Default Value¶
""
Remarks¶
The OAuthRefreshToken
property is used to refresh the OAuthAccessToken when using OAuth authentication.
OAuthExpiresIn¶
The lifetime in seconds of the OAuth AccessToken.
Data Type¶
string
Default Value¶
""
Remarks¶
Pair with OAuthTokenTimestamp to determine when the AccessToken will expire.
OAuthTokenTimestamp¶
The Unix epoch timestamp in milliseconds when the current Access Token was created.
Data Type¶
string
Default Value¶
""
Remarks¶
Pair with OAuthExpiresIn to determine when the AccessToken will expire.
SSL¶
This section provides a complete list of SSL properties you can configure.
Property | Description |
---|---|
SSLServerCert | The certificate to be accepted from the server when connecting using TLS/SSL. |
SSLServerCert¶
The certificate to be accepted from the server when connecting using TLS/SSL.
Data Type¶
string
Default Value¶
""
Remarks¶
If using a TLS/SSL connection, this property can be used to specify the TLS/SSL certificate to be accepted from the server. Any other certificate that is not trusted by the machine is rejected.
This property can take the following forms:
Description | Example |
---|---|
A full PEM Certificate (example shortened for brevity) | -----BEGIN CERTIFICATE----- MIIChTCCAe4CAQAwDQYJKoZIhv......Qw== -----END CERTIFICATE----- |
A path to a local file containing the certificate | C:\\cert.cer |
The public key (example shortened for brevity) | -----BEGIN RSA PUBLIC KEY----- MIGfMA0GCSq......AQAB -----END RSA PUBLIC KEY----- |
The MD5 Thumbprint (hex values can also be either space or colon separated) | ecadbdda5a1529c58a1e9e09828d70e4 |
The SHA1 Thumbprint (hex values can also be either space or colon separated) | 34a929226ae0819f2ec14b4a3d904f801cbb150d |
If not specified, any certificate trusted by the machine is accepted.
Certificates are validated as trusted by the machine based on the System's trust store. The trust store used is the 'javax.net.ssl.trustStore' value specified for the system. If no value is specified for this property, Java's default trust store is used (for example, JAVA_HOME\lib\security\cacerts).
Use '*' to signify to accept all certificates. Note that this is not recommended due to security concerns.
Schema¶
This section provides a complete list of schema properties you can configure.
Property | Description |
---|---|
Location | A path to the directory that contains the schema files defining tables, views, and stored procedures. |
BrowsableSchemas | This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA, SchemaB, SchemaC. |
Tables | This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA, TableB, TableC. |
Views | Restricts the views reported to a subset of the available tables. For example, Views=ViewA, ViewB, ViewC. |
Location¶
A path to the directory that contains the schema files defining tables, views, and stored procedures.
Data Type¶
string
Default Value¶
%APPDATA%\MYOB Data Provider\Schema
Remarks¶
The path to a directory which contains the schema files for the connector (.rsd files for tables and views, .rsb files for stored procedures). The folder location can be a relative path from the location of the executable. The Location
property is only needed if you want to customize definitions (for example, change a column name, ignore a column, and so on) or extend the data model with new tables, views, or stored procedures.
If left unspecified, the default location is "%APPDATA%\MYOB Data Provider\Schema" with %APPDATA%
being set to the user's configuration directory:
Platform | %APPDATA% |
---|---|
Windows | The value of the APPDATA environment variable |
Mac | ~/Library/Application Support |
Linux | ~/.config |
BrowsableSchemas¶
This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC.
Data Type¶
string
Default Value¶
""
Remarks¶
Listing the schemas from databases can be expensive. Providing a list of schemas in the connection string improves the performance.
Tables¶
This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA,TableB,TableC.
Data Type¶
string
Default Value¶
""
Remarks¶
Listing the tables from some databases can be expensive. Providing a list of tables in the connection string improves the performance of the connector.
This property can also be used as an alternative to automatically listing views if you already know which ones you want to work with and there would otherwise be too many to work with.
Specify the tables you want in a comma-separated list. Each table should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Tables=TableA,[TableB/WithSlash],WithCatalog.WithSchema.`TableC With Space`.
Note that when connecting to a data source with multiple schemas or catalogs, you will need to provide the fully qualified name of the table in this property, as in the last example here, to avoid ambiguity between tables that exist in multiple catalogs or schemas.
Views¶
Restricts the views reported to a subset of the available tables. For example, Views=ViewA,ViewB,ViewC.
Data Type¶
string
Default Value¶
""
Remarks¶
Listing the views from some databases can be expensive. Providing a list of views in the connection string improves the performance of the connector.
This property can also be used as an alternative to automatically listing views if you already know which ones you want to work with and there would otherwise be too many to work with.
Specify the views you want in a comma-separated list. Each view should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Views=ViewA,[ViewB/WithSlash],WithCatalog.WithSchema.`ViewC With Space`.
Note that when connecting to a data source with multiple schemas or catalogs, you will need to provide the fully qualified name of the table in this property, as in the last example here, to avoid ambiguity between tables that exist in multiple catalogs or schemas.
Miscellaneous¶
This section provides a complete list of miscellaneous properties you can configure.
Property | Description |
---|---|
MaxRows | Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses. |
Other | These hidden properties are used only in specific use cases. |
Pagesize | The maximum number of results to return per page from MYOB. |
PseudoColumns | This property indicates whether or not to include pseudo columns as columns to the table. |
Timeout | The value in seconds until the timeout error is thrown, canceling the operation. |
UserDefinedViews | A filepath pointing to the JSON configuration file containing your custom views. |
MaxRows¶
Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.
Data Type¶
int
Default Value¶
-1
Remarks¶
Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.
Other¶
These hidden properties are used only in specific use cases.
Data Type¶
string
Default Value¶
""
Remarks¶
The properties listed below are available for specific use cases. Normal driver use cases and functionality should not require these properties.
Specify multiple properties in a semicolon-separated list.
Integration and Formatting¶
Property | Description |
---|---|
DefaultColumnSize | Sets the default length of string fields when the data source does not provide column length in the metadata. The default value is 2000. |
ConvertDateTimeToGMT | Determines whether to convert date-time values to GMT, instead of the local time of the machine. |
RecordToFile=filename | Records the underlying socket data transfer to the specified file. |
Pagesize¶
The maximum number of results to return per page from MYOB.
Data Type¶
int
Default Value¶
400
Remarks¶
The Pagesize
property affects the maximum number of results to return per page from MYOB. Setting a higher value may result in better performance at the cost of additional memory allocated per page consumed.
PseudoColumns¶
This property indicates whether or not to include pseudo columns as columns to the table.
Data Type¶
string
Default Value¶
""
Remarks¶
This setting is particularly helpful in Entity Framework, which does not allow you to set a value for a pseudo column unless it is a table column. The value of this connection setting is of the format "Table1=Column1, Table1=Column2, Table2=Column3". You can use the "*" character to include all tables and all columns; for example, "*=*".
Timeout¶
The value in seconds until the timeout error is thrown, canceling the operation.
Data Type¶
int
Default Value¶
60
Remarks¶
If Timeout
= 0, operations do not time out. The operations run until they complete successfully or until they encounter an error condition.
If Timeout
expires and the operation is not yet complete, the connector throws an exception.
UserDefinedViews¶
A filepath pointing to the JSON configuration file containing your custom views.
Data Type¶
string
Default Value¶
""
Remarks¶
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
Note that the specified path is not embedded in quotation marks.