In some of my previous posts/articles I have written about Webhooks. Today I’d like to discuss how you can convert a Powershell script to an .exe file that extracts specific data from the GoldMine SQL database and posts that data to a Webhook.
PowerShell allows you to import .NET namespaces and libraries and, in effect, you can write C# code to solve any problem you may have. The PowerShell’s libraries, cmd-lets are very comprehensive but you can always come against something not “natively” available. And this is where PoSH shines with its interoperability with the .NET framework. Today I’ll show you how to use PoSh to connect to SQL Server, run a SQL query and load the data to a DataSet and then create a data payload for posting to a Webhook. But first lets discuss why you might want to do this and how this might be useful.
There are many uses/scenarios where one might want to extract/export data from the GoldMine database and import/insert it into another application. It could be as simple as wanting to subscribe a GoldMine contact to a Mailchimp or Constant Contact email list or maybe you want to add the current contact record in GoldMine to your online accounting system without having to manually copy/paste the contact information every time. What ever your situation/scenario is, there very likely is a way to simplify or even fully automate the workflow process.
It’s also important to note that in some cases you may need to utilize www.Zapier.com. Zapier is a web-based service that allows end users to integrate the web applications they use. Zapier has a Webhooks feature that allows you to post your data payload to a Webhook provided by Zapier and then you can use that data with Zapiers many different integrations to hundreds of other web applications.
So let’s jump in and take a look at a Powershell script that extracts data from the GoldMine database using a SQL Query and posts the Payload to a Webhook URL.
The following is a screen shot of a SAMPLE Powershell script I wrote in notepad.
The first few lines in this sample Powershell script are variables that are used to store values that will be used in our script.
The first variable, $accountno is used to store the current GoldMine contact records Accountno value. The Accountno will be passed to the script as a command line parameter. The following argument “$($args[0])” is used to grab the first command line parameter passed to this script.
[string] $accountno = “$($args[0])”
The next few variables ($Server, $Database, $username, $password) are used to store the SQL Server connection information. This is where you’ll want to replace these with your own SQL Server information. If you don’t know your SQL Server Name, SQL Database Name and SQL Server Username and Password. You may need to contact your database administrator or GoldMine admin.
[string] $Server= “SQL SERVER NAME”
[string] $Database = “SQL DATABASE NAME”
[string] $username = “SQL SERVER USERNAME”
[string] $password = “SQL SERVER PASSWORD”
After the SQL Server connection variables we see a variable for $SqlQuery. The SQL Query statement is the part of the script that selects what information we want to grab from the GoldMine database. In this example, I am grabbing the GoldMine contact records Company, the Primary Contacts Name, Primary E-mail Address, City, State, Zip and Country. Again this SQL Query can be written to grab just about any information we want from the GoldMine database. In my example I am only selecting some basic contact information that will be imported into another application via a webhook.
[string] $SqlQuery = $(“SELECT C1.Company, C1.Contact, isnull(CS.contsupref,”) + isnull(CS.address1,”) as Email, C1.City, C1.State, C1.Zip, C1.Country
FROM CONTACT1 AS C1
JOIN (SELECT DISTINCT Accountno, Contsupref, Address1
FROM CONTSUPP
WHERE (CONTSUPP.RECTYPE = ‘P’ AND CONTSUPP.CONTACT = ‘E-mail Address’ and CONTSUPP.ZIP like ‘_1%’)) CS
ON CS.Accountno = C1.Accountno and C1.Accountno = ‘$accountno'”)
Moving down the script, the next few lines of code use .NET to make a connection to the SQL Server, execute the SQL Query Statement and stores the results in a DataSet object.
$Connection = New-Object System.Data.SQLClient.SQLConnection
$Connection.ConnectionString = “server=’$Server’;database=’$Database’; User Id=’$username’; Password=’$password’;”
$Connection.Open()
$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $Connection
$Command.CommandText = $SQLQuery
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $Command
$DataSet = New-Object System.Data.Dataset
$SqlAdapter.Fill($DataSet)
$Connection.Close()
$DataSet.Tables[0]
We then create a $Payload variable that is an array of values. The values in this case is the first row of the DataSet object which contains the
Company, Contact, Email, City, State, Zip and Country values of the contact record in GoldMine.
$payload = @{
‘Company’ = $Dataset.Tables[0].Rows[0][“Company”].ToString();
‘Contact’ = $Dataset.Tables[0].Rows[0][“Contact”].ToString();
‘Email’ = $Dataset.Tables[0].Rows[0][“Email”].ToString();
‘City’ = $Dataset.Tables[0].Rows[0][“City”].ToString();
‘State’ = $Dataset.Tables[0].Rows[0][“State”].ToString();
‘Zip’ = $Dataset.Tables[0].Rows[0][“Zip”].ToString();
‘Country’ = $Dataset.Tables[0].Rows[0][“Country”].ToString();
}
Finally we use the Invoke-WebRequest PowerShell cmdlet to POST our $Payload to the Webhook URL we want to send this data to after converting it to Json with the ConvertTo-Json setting.
Invoke-WebRequest -Uri ‘https://hooks.zapier.com/hooks/catch/535090/6zg234239h/’ -Method POST -Body (ConvertTo-Json -Compress -InputObject $payload)
The only thing left to do is to save this as a Powershell script by using the .ps1 file extension.
Next we’ll want to convert our Powershell script into an .exe file which can easily be sent to other users or run by other users without requiring knowledge about how to run Powershell scripts. In order to convert our Powershell script into an .exe we’ll need to use a Powershell ISE add0n called ISESteroids. ISESteroids is a PowerShell module, not a separate application. It seamlessly integrates into the ISE Editor. There is a free download available here.
Once you’ve got ISESteroids installed you can launch Powershell ISE and type in Start-Steroids and click the Run Script icon. Your Powershell ISE now has many advanced features including the ability to create .exe files from your Powershell scripts.
Now what we’ll want to do is create a Taskbar item in GoldMine for an external application, in this case our .exe created using ISESteroids.
Notice the Command Line: -accountno ‘**accountno**’ setting. This is the setting that will pass the current contact records Accountno value to the .exe file when the Taskbar item is clicked in GoldMine. Our Powershell script will store the Accountno value passed to it in the $accountno variable using the “$($args[0])” argument.
Now, every time I am on a record in GoldMine that I want to export out of GoldMine and into my other application, I simply need to click the Taskbar item that I setup and my Powershell script will do all the work of posting the data to my Webhook.
PS. If you’re not familiar with how Webhooks work, please read the following article.
