Establishing a Connection

With the CData Cmdlets users can install a data module, set the connection properties, and start scripting. This section provides examples of using our SugarCRM Cmdlets with native PowerShell cmdlets, like the CSV import and export cmdlets.

Installing and Connecting

If you have PSGet, installing the cmdlets can be accomplished from the PowerShell Gallery with the following command. You can also obtain a setup from the CData site.

Install-Module SugarCRMCmdlets

The following line is then added to your profile, loading the cmdlets on the next session:

Import-Module SugarCRMCmdlets;

You can then use the Connect-SugarCRM cmdlet to create a connection object that can be passed to other cmdlets:

$conn = Connect-SugarCRM -URL 'http://mySugarCRM.com' -User 'myUser' -Password 'myPassword'

Connecting to SugarCRM

To establish a connection to SugarCRM, set URL to the URL associated with your SugarCRM account in the form http://{sugar crm instance}.com.

Authenticating to SugarCRM

To authenticate the cmdlet to SugarCRM, set the following:

User: The user of the SugarCRM account.
Password: The password associated with the SugarCRM user.
Platform (optional): If you're encountering a login conflict during authentication, set this to one of the platforms that you have created in the SugarCRM UI.

By default, SugarCRM uses the "sugar" client id for authentication, but users can optionally specify the client Id and client secret values of a custom app. See Creating a Custom OAuth Application for more information.

OAuthClientId: Client Id of your custom OAuth app.
OAuthClientSecret: Client secret of your custom OAuth app.

Caching SugarCRM Metadata

Note that retrieving SugarCRM metadata can be expensive. It is advised that you store the metadata locally as described in Caching Metadata.

Retrieving Data

The Select-SugarCRM cmdlet provides a native PowerShell interface for retrieving data:

$results = Select-SugarCRM -Connection $conn -Table "Accounts" -Columns @("Name, Industry") -Where "Industry='Manufacturing'"

The Invoke-SugarCRM cmdlet provides an SQL interface. This cmdlet can be used to execute an SQL query via the Query parameter.

Piping Cmdlet Output

The cmdlets return row objects to the pipeline one row at a time. The following line exports results to a CSV file:

Select-SugarCRM -Connection $conn -Table Accounts -Where "Industry = 'Manufacturing'" | Select -Property * -ExcludeProperty Connection,Table,Columns | Export-Csv -Path c:\myAccountsData.csv -NoTypeInformation

You will notice that we piped the results from Select-SugarCRM into a Select-Object cmdlet and excluded some properties before piping them into an Export-CSV cmdlet. We do this because the CData Cmdlets append Connection, Table, and Columns information onto each row object in the result set, and we do not necessarily want that information in our CSV file.

However, this makes it easy to pipe the output of one cmdlet to another. The following is an example of converting a result set to JSON:

 
PS C:\> $conn  = Connect-SugarCRM -URL 'http://mySugarCRM.com' -User 'myUser' -Password 'myPassword'
PS C:\> $row = Select-SugarCRM -Connection $conn -Table "Accounts" -Columns (Name, Industry) -Where "Industry = 'Manufacturing'" | select -first 1
PS C:\> $row | ConvertTo-Json
{
  "Connection":  {

  },
  "Table":  "Accounts",
  "Columns":  [

  ],
  "Name":  "MyName",
  "Industry":  "MyIndustry"
}

Deleting Data

The following line deletes any records that match the criteria:

Select-SugarCRM -Connection $conn -Table Accounts -Where "Industry = 'Manufacturing'" | Remove-SugarCRM

Modifying Data

The cmdlets make data transformation easy as well as data cleansing. The following example loads data from a CSV file into SugarCRM, checking first whether a record already exists and needs to be updated instead of inserted.

Import-Csv -Path C:\MyAccountsUpdates.csv | %{
  $record = Select-SugarCRM -Connection $conn -Table Accounts -Where ("Id = `'"+$_.Id+"`'")
  if($record){
    Update-SugarCRM -Connection $conn -Table Accounts -Columns @("Name","Industry") -Values @($_.Name, $_.Industry) -Where "Id  = `'$_.Id`'"
  }else{
    Add-SugarCRM -Connection $conn -Table Accounts -Columns @("Name","Industry") -Values @($_.Name, $_.Industry)
  }
}

Cmdlets for SugarCRM