cancel
Showing results for 
Search instead for 
Did you mean: 

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.

 

  1. Download Fiddler from www.telerik.com/fiddler and install and run it.
  2. 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 UseFiddlerToFilter.png

     

  3. Once your Fiddler stream is a little less busy, we’ll run the function app. Return to Visual Studio and press F5.
  4. 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.TestFunctionApp.png
  5. Wait for your Azure function to execute. It should display some text that looks a bit like this:
    RunAzureFunctionAndConfirm.png
  6. 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.GetCallInFiddler.png
  7. Click this row, and choose to decode it on the bottom right.DecodeResponseBody.png
  8. 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.CopyCallBody.png

 

 

Create a Microsoft Flow to receive the Gravity Forms entries

 

  1. Visit flow.microsoft.com and sign in with your Office 365 account. 
  2. Create a new Blank flow, give it a name and start it with a Request Trigger. Then click Use sample payload to generate schemaCreateFlow.png
  3. Paste the JSON payload that we saved from Fiddler and click Done.SamplePayload.png
  4. 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.SaveFlowToGetURL.png
  5. Click Create Flow, then copy the URL that was created next to HTTP POST URL.FlowURL.png

     

  6. 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.SaveURLinFunctionApp.png

  7. 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:FiddlerRow.png

     

  8. Inspecting the call on the top right under the JSON tab shows that it sent the JSON payload:

  9. PayloadSent.png
  10. When you return to Microsoft Flow, you should see a recent successful Flow run.FlowRan.png
  11. Open the flow run to see that the payload was received by the Request step.FlowResult.png

 

 

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

 

  1. Sign into ConnectWise as an admin user, and navigate to System, Setup Tables on the left menu.
  2. Search for Integrator Login and open it.IntegratorLoginSetupTable.png
  3. Create a new Integrator Login and give it a new username and password. Note these details down somewhere.CreateNewIntegratorLogin.png
  4. 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.
  5. 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.

 

  1. Log into https://portal.azure.com
  2. Click New on the left menu and search for Function App.
  3. 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.CreateNewFunctionAppInAzure.png

     

  4. 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).

    SwitchTo64Bit.png
  5. Scroll down to Application Settings and add your ConnectWise Integrator username and password using the following keys:ApplicationSettingsForFunction.png
  6. 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.netCompanyNameAndSiteURL.png
  7. Save your changes

 

Create a function that retrieves ConnectWise contact details

 

  1. Create a new Function by clicking the + next to Functions on the left menu:NewFunctionAppInBrowser.png
  2. Create a new HTTP Triggered PowerShell function. Call it ConnectWiseGetContactByID and set the Authorization level to FunctionCreateHTTPTriggeredPowerShellFunction.png
  3. 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.

 

  1. 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
  2. Note the returned member's ID value for use in the Azure FunctionmemberID.png

     

  3. Create another new HTTP Triggered PowerShell Function called ConnectWiseAddOpportunity and set the Authorization level to Function

  4. 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.

 

Meet Our Blog Authors
  • Co-founder of https://plumsail.com, Office 365 and SharePoint expert. Passionate about design and development of easy to use, convenient and flexible products.
  • Microsoft Business Apps MVP. Owner of ThriveFast, an Office 365 consulting company.
  • 7x Microsoft Business Solutions MVP (CRM)
  • Solution Architect with Slalom, and organizer of the Boston Office 365 User Group, and long term SharePoint/Office 365 veteren. Find more at http://www.davidlozzi.com. Follow @DavidLozzi
  • I'm keen in MS technologies, SharePoint, Office 365 and development for them
  • Michelle is an Office 365 solution architect in Twin Cities, MN. She has been delivering business collaboration solutions for years with her focus on SharePoint and Office 365. Michelle is a recent board member of the Minnesota Office 365 User Group and has been a member of the SharePoint community since 2009. She is a frequent speaker at MNSPUG and SharePoint Saturday and co-chaired the Legal SharePoint User Group for 4 years. Her most frequent projects have involved rolling out a large deployment of Office 365, SharePoint Online intranet, build of a "CHAMPS" Office 365 user adoption program and most recently, SharePoint On-Premise to Online Migration. Michelle is very excited about cloud technology as it is shifting her IT Pro focus to collaboration strategy and technical adoption.
  • I'm a Microsoft Office Servers and Services MVP with a special interest in SharePoint, Office 365, Microsoft Flow, Microsoft Teams and PowerApps. I work at Triad Group Plc ( https://triad.co.uk)
>