Automate Acumatica Integration Tasks from PowerShell

The CData Cmdlets for Acumatica 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 Acumatica.

PowerShell Cmdlets or ADO.NET Provider?

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

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

Set the following connection properties to connect to Acumatica:

• User: Set this to your username.
• Password: Set this to your password.
• Company: Set this to your company.
• Url: Set this to your Acumatica URL, in the format http://{Acumatica ERP instance URL}/entity/{Endpoint name}/{Endpoint version}/.
  For example: https://acumatica.com/entity/Default/17.200.001/

See the Getting Started guide in the CData driver documentation for more information.

PowerShell

1. Install the module:

   Install-Module AcumaticaCmdlets

2. Connect:

   $acumatica = Connect-Acumatica -Url "$Url" -User "$User" -Password "$Password" -Company "$Company"
   

3. Search for and retrieve data:

   $id = "1"
   $events = Select-Acumatica -Connection $acumatica -Table "Events" -Where "Id = `'$Id`'"
   $events
   

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

   $events = Invoke-Acumatica -Connection $acumatica -Query 'SELECT * FROM Events WHERE Id = @Id' -Params @{'@Id'='1'}
   

ADO.NET

1. Load the provider's assembly:

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

2. Connect to Acumatica:

    
   $conn= New-Object System.Data.CData.Acumatica.AcumaticaConnection("Url = https://try.acumatica.com/ISV/entity/Default/17.200.001/;User=user;Password=password;Company=CompanyName;")
   $conn.Open()
   

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

   $sql="SELECT Id, location_displayname from Events"
   
   $da= New-Object System.Data.CData.Acumatica.AcumaticaDataAdapter($sql, $conn)
   $dt= New-Object System.Data.DataTable
   $da.Fill($dt)
   
   $dt.Rows | foreach {
   	Write-Host $_.id $_.location_displayname
   }
    

Update Acumatica Data

PowerShell

Update-Acumatica -Connection $Acumatica -Columns @('Id','location_displayname') -Values @('MyId', 'Mylocation_displayname') -Table Events -Id "MyId"

ADO.NET

$cmd = New-Object System.Data.CData.Acumatica.AcumaticaCommand("UPDATE Events SET Id='1' WHERE Id = @myId", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.Acumatica.AcumaticaParameter("@myId","10456255-0015501366")))
$cmd.ExecuteNonQuery()

Insert Acumatica Data

PowerShell

Add-Acumatica -Connection $Acumatica -Table Events -Columns @("Id", "location_displayname") -Values @("MyId", "Mylocation_displayname") 

ADO.NET

$cmd = New-Object System.Data.CData.Acumatica.AcumaticaCommand("INSERT INTO Events (Id) VALUES (@myId)", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.Acumatica.AcumaticaParameter("@myId","1")))
$cmd.ExecuteNonQuery()

Delete Acumatica Data

PowerShell

Remove-Acumatica -Connection $Acumatica -Table "Events" -Id "MyId"

ADO.NET

$cmd = New-Object System.Data.CData.Acumatica.AcumaticaCommand("DELETE FROM Events WHERE Id=@myId", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.Acumatica.AcumaticaParameter("@myId","001d000000YBRseAAH")))
$cmd.ExecuteNonQuery()

CodeProject