Part 2 - Build a referral tracking system with Microsoft Flow, Azure Functions and your CRM
Get your Gravity Form entries as JSON
In order for us to use your referral form entries in Microsoft Flow, we’ll need to show Flow what your form entries look like in JSON format. To do this, we’ll use Fiddler.
- Download Fiddler from www.telerik.com/fiddler and install and run it.
- Fiddler allows you to analyse your computer’s internet traffic, as well as a bunch of other things. You might see a lot of activity from irrelevant processes, you can right click and filter these processes out
- Once your Fiddler stream is a little less busy, we’ll run the function app. Return to Visual Studio and press F5.
- You’ll see the Azure Functions Core Tools window appear. This is a local version of the Azure Functions runtime that allows you to debug Azure Functions on your own computer before you deploy them.
- Wait for your Azure function to execute. It should display some text that looks a bit like this:
- Now switch over to Fiddler and locate the call that it just made to your website. If all goes well, you should see a row with a result of 200 to your domain.
- Click this row, and choose to decode it on the bottom right.
- Select the Raw tab, and triple click the JSON result at the bottom to select it all, then copy this into Notepad for later. This is the JSON representation of your Gravity Forms form entries that we can use in Microsoft Flow.
Create a Microsoft Flow to receive the Gravity Forms entries
- Visit flow.microsoft.com and sign in with your Office 365 account.
- Create a new Blank flow, give it a name and start it with a Request Trigger. Then click Use sample payload to generate schema
- Paste the JSON payload that we saved from Fiddler and click Done.
- Add an action step so that we can save the flow and retrieve the HTTP POST URL. In this example I added a Notification action.
- Click Create Flow, then copy the URL that was created next to HTTP POST URL.
Switch back over to Visual Studio 2017 and paste the HTTP POST URL in the placeholder for the flowRequest string variable. Next uncomment out the last four lines of the Run method.
- Run the function again to confirm that it’s sending the JSON payload to Microsoft Flow. You should see a row in Fiddler that looks like this:
Inspecting the call on the top right under the JSON tab shows that it sent the JSON payload:
- When you return to Microsoft Flow, you should see a recent successful Flow run.
- Open the flow run to see that the payload was received by the Request step.
Set up an Integrator Login in ConnectWise
We need to create a new Integrator Login for ConnectWise that we can use in our Azure Functions
- Sign into ConnectWise as an admin user, and navigate to System, Setup Tables on the left menu.
- Search for Integrator Login and open it.
- Create a new Integrator Login and give it a new username and password. Note these details down somewhere.
- Give the new Integrator user access to at least the Opportunity, Member, Contact and Company APIs. You can give it access to more APIs if you plan to use this in later Flows and Functions.
- Save and close the new user record.
Create another Azure Function App via the Azure Portal
We'll now create another Azure Function app so that we can connect to ConnectWise via the API. This time, we'll do it via the Azure Portal. In this case, we'll be connecting to ConnectWise via HTTP Triggered PowerShell functions. Flow will pass the contact and opportunity details to these functions via HTTP Post Calls. The functions will contact Connectwise, perform the required task and send back the results to Flow.
- Log into https://portal.azure.com
- Click New on the left menu and search for Function App.
- Create a new Function app. You can create or choose a resource group. For the Hosting Plan, you can choose App Service Plan or Consumption. I recommend using Hosting plan since it allows you to create functions that can run for longer than 5 minutes if required, and it's quite cheap anyway. You can create a new App Service or choose an existing one.
Once deployed, open your function app, go to Platform Features, click Application Settings, and change your platform to 64 bit (if it's not already).
- Scroll down to Application Settings and add your ConnectWise Integrator username and password using the following keys:
- You'll also need to add your ConnectWise Company name and Site URL. Your ConnectWise company name is the name that appears when you log into ConnectWise under the company field. If your ConnectWise instance is hosted by ConnectWise, your site URL is region specific. Ours is https://api-au.myconnectwise.net - other examples include https://api-eu.myconnectwise.net and https://api-na.myconnectwise.net.
- Save your changes
Create a function that retrieves ConnectWise contact details
- Create a new Function by clicking the + next to Functions on the left menu:
- Create a new HTTP Triggered PowerShell function. Call it ConnectWiseGetContactByID and set the Authorization level to Function
- Copy and paste the following code, replacing the $impersonationMember variable value with the ConnectWise user that you'd like the integrator login to act on behalf of. In my case, I'm just using my own ConnectWise username.
# POST method: $req $requestBody = Get-Content $req -Raw | ConvertFrom-Json $impersonationMember = "emunro" $method = "GET" $endpoint = "company/contacts" $item = $requestBody.item $Global:CWinfo = New-Object PSObject -Property @{ company = $Env:ConnectWiseCompanyName user = $Env:ConnectWiseIntegrator password = $Env:ConnectWisePassword siteURL = $Env:ConnectWiseSiteURL } function Get-CWKeys { [string]$BaseUri = "$($CWInfo.siteURL)/v4_6_release/apis/3.0/system/members/$ImpersonationMember/tokens" [string]$Accept = "application/vnd.connectwise.com+json; version=v2015_3" [string]$ContentType = "application/json" [string]$Authstring = $CWInfo.company + '+' + $CWInfo.user + ':' + $CWInfo.password #Convert the user and pass (aka public and private key) to base64 encoding $encodedAuth = [Convert]::ToBase64String([Text.Encoding]::ASCII.GetBytes(($Authstring))); #Create Header $header = @{ Authorization = ("Basic {0}" -f $encodedAuth) Accept = $Accept Type = "application/json" 'x-cw-usertype' = "integrator" }; $body = "{`"memberIdentifier`":`"$ImpersonationMember`"}" #execute the the request $response = Invoke-RestMethod -Uri $Baseuri -Method Post -Headers $header -Body $body -ContentType $contentType; #return the results return $response; } $global:CWCredentials = Get-CWKeys $BaseUri = "$($CWInfo.siteURL)/v4_6_release/apis/3.0/$endpoint/$item" $Accept = "application/vnd.connectwise.com+json; version=v2015_3" $ContentType = "application/json" $Authstring = $CWInfo.company + '+' + $CWCredentials.publicKey + ':' + $CWCredentials.privateKey $encodedAuth = [Convert]::ToBase64String([Text.Encoding]::ASCII.GetBytes(($Authstring))); $Headers = @{ "Authorization" = "Basic $encodedAuth" } if ($body) { $JSONResponse = Invoke-RestMethod -URI $BaseURI -Headers $Headers -ContentType $ContentType -Method $method -Body $body } else { $JSONResponse = Invoke-RestMethod -URI $BaseURI -Headers $Headers -ContentType $ContentType -Method $method } If ($JSONResponse) { $JSONResponse = $JSONResponse | ConvertTo-Json Out-File -Encoding Ascii -FilePath $res -inputObject $JSONResponse } Else { Out-File -Encoding Ascii -FilePath $res -inputObject "Didn't work" }
Create another Function that creates a ConnectWise Opportunity
This function receives the information required to create a new opportunity in ConnectWise.
- To get this to work, we'll need the ConnectWise Member ID of the primarySalesRep. To retrieve this, you can run the following code locally on your computer in Visual Studio Code or PowerShell ISE. Make sure you update the CWInfo object with your ConnectWise company name, Integrator Login, Integrator Password and ConnectWise Site URL. Also update the $impersonationMember and the search conditions
$impersonationMember = "emunro" $method = "GET" $endpoint = "system/members" $body = @{ conditions = "firstname LIKE `"Elliot`" AND lastname LIKE `"Munro`"" } $Global:CWinfo = New-Object PSObject -Property @{ company = "<enter your company ID>" user = "<enter your Integrator Login>" password = "<enter your Integrator Password>" siteURL = "<enter your SITE URL>" } function Get-CWKeys { [string]$BaseUri = "$($CWInfo.siteURL)/v4_6_release/apis/3.0/system/members/$ImpersonationMember/tokens" [string]$Accept = "application/vnd.connectwise.com+json; version=v2015_3" [string]$ContentType = "application/json" [string]$Authstring = $CWInfo.company + '+' + $CWInfo.user + ':' + $CWInfo.password #Convert the user and pass (aka public and private key) to base64 encoding $encodedAuth = [Convert]::ToBase64String([Text.Encoding]::ASCII.GetBytes(($Authstring))); #Create Header $header = @{ Authorization = ("Basic {0}" -f $encodedAuth) Accept = $Accept Type = "application/json" 'x-cw-usertype' = "integrator" }; $body = "{`"memberIdentifier`":`"$ImpersonationMember`"}" #execute the the request $response = Invoke-RestMethod -Uri $Baseuri -Method Post -Headers $header -Body $body -ContentType $contentType; #return the results return $response; } $global:CWCredentials = Get-CWKeys $BaseUri = "$($CWInfo.siteURL)/v4_6_release/apis/3.0/$endpoint/$item" $Accept = "application/vnd.connectwise.com+json; version=v2015_3" $ContentType = "application/json" $Authstring = $CWInfo.company + '+' + $CWCredentials.publicKey + ':' + $CWCredentials.privateKey $encodedAuth = [Convert]::ToBase64String([Text.Encoding]::ASCII.GetBytes(($Authstring))); $Headers=@{ "Authorization"="Basic $encodedAuth" } Invoke-RestMethod -URI $BaseURI -Headers $Headers -ContentType $ContentType -Method $method -Body $body
- Note the returned member's ID value for use in the Azure Function
Create another new HTTP Triggered PowerShell Function called ConnectWiseAddOpportunity and set the Authorization level to Function
- Copy and paste the following code. Remember to replace the $impersonationMember variable value with the Connectwise user you'd like the function to act as. I'm just using my own. Be sure to replace the primarySalesRep value with the member ID of the person who you'd like to assign the opportunity to.
$requestBody = Get-Content $req -Raw | ConvertFrom-Json $impersonationMember = "emunro" [int]$companyId = $requestBody.companyid [int]$contactId = $requestBody.contactid [int]$siteId = $requestBody.siteid $notes = $requestBody.notes $opportunityName = $requestBody.opportunityName $opportunity = @{ name = $opportunityName primarySalesRep = @{ id = 151 } company = @{ id = $companyId } contact = @{ id = $contactId } site = @{ id = $siteId } notes = $notes } $opportunity = $opportunity | ConvertTo-Json $Global:CWinfo = New-Object PSObject -Property @{ company = $Env:ConnectWiseCompanyName user = $Env:ConnectWiseIntegrator password = $Env:ConnectWisePassword siteURL = $Env:ConnectWiseSiteURL } function Get-CWKeys { [string]$BaseUri = "$($CWInfo.siteURL)/v4_6_release/apis/3.0/system/members/$ImpersonationMember/tokens" [string]$Accept = "application/vnd.connectwise.com+json; version=v2015_3" [string]$ContentType = "application/json" [string]$Authstring = $CWInfo.company + '+' + $CWInfo.user + ':' + $CWInfo.password #Convert the user and pass (aka public and private key) to base64 encoding $encodedAuth = [Convert]::ToBase64String([Text.Encoding]::ASCII.GetBytes(($Authstring))); #Create Header $header = @{ Authorization = ("Basic {0}" -f $encodedAuth) Accept = $Accept Type = "application/json" 'x-cw-usertype' = "integrator" }; $body = "{`"memberIdentifier`":`"$ImpersonationMember`"}" #execute the the request $response = Invoke-RestMethod -Uri $Baseuri -Method Post -Headers $header -Body $body -ContentType $contentType; #return the results return $response; } $global:CWCredentials = Get-CWKeys $BaseUri = "$($CWInfo.siteURL)/v4_6_release/apis/3.0/sales/opportunities" $Accept = "application/vnd.connectwise.com+json; version=v2015_3" $ContentType = "application/json" $Authstring = $CWInfo.company + '+' + $CWCredentials.publicKey + ':' + $CWCredentials.privateKey $encodedAuth = [Convert]::ToBase64String([Text.Encoding]::ASCII.GetBytes(($Authstring))); $Method = "POST" $Headers = @{ "Authorization" = "Basic $encodedAuth" } $JSONResponse = Invoke-RestMethod -URI $BaseURI -Headers $Headers -ContentType $ContentType -Method $Method -Body $opportunity If ($JSONResponse) { $JSONResponse = $JSONResponse | ConvertTo-Json Out-File -Encoding Ascii -FilePath $res -inputObject $JSONResponse } Else { Out-File -Encoding Ascii -FilePath $res -inputObject "Didn't work" }
That's it for part 2. In Part 3, we bring it all together in a single Microsoft Flow.
Elliot Munro is an Office 365 MCSA and Partner at GCITS - See the GCITS knowledge base for scripts and articles on administering Office 365 for managed service providers.
*This post is locked for comments