Automate MYOB AccountRight Integration Tasks from PowerShell

The CData Cmdlets for MYOB AccountRight are standard PowerShell cmdlets that make it easy to accomplish data cleansing, normalization, backup, and other integration tasks by enabling real-time and bidirectional access to MYOB AccountRight.

PowerShell Cmdlets or ADO.NET Provider?

The Cmdlets are not only a PowerShell interface to MYOB AccountRight, but also an SQL interface; this tutorial shows how to use both to create, retrieve, update, and delete MYOB AccountRight data. We also show examples of the ADO.NET equivalent, which is possible with the CData ADO.NET Provider for MYOB AccountRight. To access MYOB AccountRight data from other .NET applications, like LINQPad, use the CData ADO.NET Provider for MYOB AccountRight.

Once you have acquired the necessary connection properties, accessing MYOB AccountRight data in PowerShell can be enabled in three steps.

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

• CompanyFileId: You can find this by starting MYOB, opening your data file, and selecting Help -> About MYOB
• User: The username associated with your company file.
• Password: The password associated with your company file.

Connecting to a Cloud Instance

To connect to a cloud instance of MYOB, you can use the embedded OAuth credentials or create an OAuth app with MYOB. This process is detailed in the Help documentation.

Connecting to an On-Premise instance:

When connecting to an on-premise instance, set the following connection property in addition to those above:

• InitiateOauth: Set this to OFF.
• Url: The Url of your MYOB instance.

PowerShell

1. Install the module:

   Install-Module MYOBCmdlets

2. Connect:

   $myob = Connect-MYOB -OAuthClientId "$OAuthClientId" -OAuthClientSecret "$OAuthClientSecret" -CompanyFileId "$CompanyFileId" -CallbackURL "$CallbackURL" -User "$User" -Password "$Password" -InitiateOAuth "$InitiateOAuth"
   

3. Search for and retrieve data:

   $type = "Bank"
   $accounts = Select-MYOB -Connection $myob -Table "Accounts" -Where "Type = `'$Type`'"
   $accounts
   

   You can also use the Invoke-MYOB cmdlet to execute SQL commands:

   $accounts = Invoke-MYOB -Connection $myob -Query 'SELECT * FROM Accounts WHERE Type = @Type' -Params @{'@Type'='Bank'}
   

ADO.NET

1. Load the provider's assembly:

   [Reflection.Assembly]::LoadFile("C:\Program Files\CData\CData ADO.NET Provider for MYOB AccountRight\lib\System.Data.CData.MYOB.dll")
    

2. Connect to MYOB AccountRight:

    
   $conn= New-Object System.Data.CData.MYOB.MYOBConnection("OAuthClientId=YourClientId; OAuthClientSecret=YourClientSecret; CompanyFileId=yourCompanyFileId; CallbackURL=http://localhost:33333; User=companyFileUser; Password=companyFilePassword;InitiateOAuth=GETANDREFRESH;")
   $conn.Open()
   

3. Instantiate the MYOBDataAdapter, execute an SQL query, and output the results:

   $sql="SELECT Id, Name from Accounts"
   
   $da= New-Object System.Data.CData.MYOB.MYOBDataAdapter($sql, $conn)
   $dt= New-Object System.Data.DataTable
   $da.Fill($dt)
   
   $dt.Rows | foreach {
   	Write-Host $_.id $_.name
   }
    

Update MYOB AccountRight Data

PowerShell

Update-MYOB -Connection $MYOB -Columns @('Id','Name') -Values @('MyId', 'MyName') -Table Accounts -Id "MyId"

ADO.NET

$cmd = New-Object System.Data.CData.MYOB.MYOBCommand("UPDATE Accounts SET Type='Bank' WHERE Id = @myId", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.MYOB.MYOBParameter("@myId","10456255-0015501366")))
$cmd.ExecuteNonQuery()

Insert MYOB AccountRight Data

PowerShell

Add-MYOB -Connection $MYOB -Table Accounts -Columns @("Id", "Name") -Values @("MyId", "MyName") 

ADO.NET

$cmd = New-Object System.Data.CData.MYOB.MYOBCommand("INSERT INTO Accounts (Type) VALUES (@myType)", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.MYOB.MYOBParameter("@myType","Bank")))
$cmd.ExecuteNonQuery()

Delete MYOB AccountRight Data

PowerShell

Remove-MYOB -Connection $MYOB -Table "Accounts" -Id "MyId"

ADO.NET

$cmd = New-Object System.Data.CData.MYOB.MYOBCommand("DELETE FROM Accounts WHERE Id=@myId", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.MYOB.MYOBParameter("@myId","001d000000YBRseAAH")))
$cmd.ExecuteNonQuery()

CodeProject