cancel
Showing results for 
Search instead for 
Did you mean: 
elliotmunro

Part 1 - Build a referral tracking system with Microsoft Flow, Azure Functions and your CRM

Microsoft Flow is fantastic for designing business logic workflows in a simple to use interface. It also has a bunch of connectors that allow us to make use of some popular tools, including those within Office 365. When we encounter a step that Microsoft Flow can’t handle, such as connecting to a service that doesn't have an existing Microsoft Flow connector, we can start an Azure Function from within our Flow. The Azure function can do the technical work, and send the data back.

 

This exact scenario won't apply to the majority. However, hopefully it demonstrates the power of using Microsoft Flow with Azure Functions and SharePoint Online to automate your business processes. If you happen to be using Gravity Forms, ConnectWise and Office 365, and you'd like to experiment with Flow and Functions, you can implement this solution yourself using the guide below.

 

 

FeaturedImage.png

 

 

 

This example solution uses the following services: 

 

Microsoft Flow

Microsoft Flow gives us an easy way to coordinate the flow of data between our other services. It authenticates easily against Office 365 services, and is the backbone of the solution

 

Azure Functions

Azure Functions  let us run code in the cloud without a server. Azure Functions do the technical work, such as interfacing with an API that doesn't have a built in connector for Flow. This solution uses 3 Azure Functions - one runs every four hours to start the Flow, the other two are triggered within the Flow.

 

Gravity Forms

Gravity forms is the forms plugin that we use on our Wordpress Site to collect our customer's referrals

 

ConnectWise

We're an IT managed service provider that uses ConnectWise to run our business, it’s a professional services automation (PSA) tool that helps us keep track of open tickets, billing, sales etc.

 

SharePoint Online

We're using SharePoint to keep a register of referrals. We can use this later to track whether our customers have been rewarded for providing referrals.

 

Our solution works like this:

 

  1. Our customer receives a notification from ConnectWise that a ticket of theirs has been closed. The notification includes a request for a referral.
  2. The customer clicks the personalised link and leaves a referral. Their contact ID is submitted to Gravity Forms along with the referral form entry.
  3. An Azure Function retrieves these referrals via the Gravity Forms API and sends them to a Microsoft Flow via a HTTP call.
  4. Microsoft Flow checks a SharePoint list to see whether the form entry exists.
  5. It doesn't exist, an Azure Function is triggered using the contact ID to get the customer's details via the ConnectWise API.
  6. The referral details, along with the referring customers details are added to the SharePoint List
  7. Flow calls another Azure function which creates a new opportunity in ConnectWise with the referral details, on behalf of the referring customer
  8. An email notification is sent to me

 

Create Gravity Form and page in WordPress Site to receive referrals

 

  1. Log into your WordPress site and create a new form in Gravity Forms called Referrals.
  2. Create two hidden form fields. One called contactrecordid, the other called companyrecordid.CreateReferralsForm.png
  3. Open the advanced tab for each hidden field and tick the box 'Allow field to be populated dynamically'. Specify contactrecordid and companyrecordid for the relevant fields. We'll be sending these IDs through from ConnectWise to our form.SetParametersOnHiddenFields.png
  4. You can leave the default values blank if you like. I added my own ContactRecordID and CompanyRecordID from Connectwise as a default just in case the user's details aren't available.CreateReferralsForm.png
  5.  Add in the rest of the fields that you'd like to collect. In this example, we're using the following: 'Their business name', 'Contact person', 'Extra info that might help', 'Phone (if you've got it)'AddRemainingFields.png
  6. Create a new page on WordPress and make note of its URL. I've called ours 'Who else can we help?'CreateNewPageInWordPress.png 
  7. Add your referral form to your WordPress page using its shortcode: GravityFormsShortcode.png

Set up ConnectWise Ticket completion notifications

 

  1. Sign into ConnectWise as an admin user
  2. Navigate to System, Setup Tables
    Setuptables.png
  3. Search for Email FormatsEditEmailFormatsSetupTable.png
  4. Open the template for the email that a customer receives when an email is marked as completed: EditCompletedTicketNotification.png
  5. Add in some text asking for a referral:
    AskForReferral.png
  6. Edit the referral link so that it links to the new form page on your website, and includes the contact ID, contact first name and company ID as URL parameters. You can add this in via the HTML editor in ConnectWise, for example: 
    <a href="https://gcits.com/who-else-can-we-help?contactrecordid=[contactrecordid]&contactfirstname=[contactfirstname]&companyrecordid=[companyrecordid]"><strong>let us know here</strong></a>
  7. You can now close a sample ticket to yourself to test and confirm that this works:ClosedTicketNotification.png
  8. Complete a sample form entry so that we have some data to pull in the next steps.

 

Enable the Gravity Forms API to retrieve the form info

 

  1. Return to your site’s WordPress Admin Panel and visit the Settings section of Gravity Forms
  2. Enable the API. Retrieve and make a note of the Public API Key and Private API Key.EnableGravityFormsAPI.png
  3. Set the Impersonate account user. Your Function App will have the same form access permissions as the user that you choose here
  4. Return to your referral form and make a note of the Form ID (eg. 4)ReferralsFormID.png

     

  5. Make a note of all the fields in the form. We’ll be adding these as columns to a SharePoint List.

  6. It’s also worth making a note of each field’s corresponding ID (eg. 1, 2, 3 etc) since the JSON representation of each field uses this and not the field name.RetrieveFieldIds.png

Create a SharePoint List to receive the form data

 

  1. Sign into your SharePoint site with your Office 365 Account.
  2. Visit Site Contents and create a new SharePoint list with an appropriate name. I've called ours Referrals.
  3. Create columns to match the field names in your Gravity Forms form. Here’s the field names and types that we’re using (In this case Title will be the Business Name):CreateSharePointListWithColumns.png

 

Create a Function App in Visual Studio 2017

 

Azure Functions can be created and tested online via the Azure Portal or locally on your own computer. We'll be using Visual Studio to test and deploy the C# Azure Function that pulls the form entries from Gravity Forms.

 

  1. Open Visual Studio 2017 and make sure that it’s up to at least version 15.3. You’ll need to ensure that Azure Development tooling is installed, and that you can create Azure Function Apps. See here for a list of prerequisites.
  2. Go to File, New Project, Visual C#, Cloud, Azure Functions then create a Function App and give it a name.CreateNewFunctionApp.png

  3. If Visual Studio is completely up to date, you’ve installed all the prerequisites, but you still can’t see an option to create Azure Functions, you may need to go to Tools > Extensions and Updates > Updates > Visual Studio Marketplace and install the Azure Functions and Web Jobs Tools update.UpdateVisualStudioForFunctionApp.png
  4. An Azure Function app is pretty much an Azure Web App, and each Function App can contain multiple functions. To add a Function to our Function App, right click on your project in the Solution Explorer, choose Add, New Item.AddNewItemToProjectInVisualStudio.png
  5. Then select Azure Function and give your function a name – I’ve called this one GravityForms_Referrals. Click Add.AddNewAzureFunctionToProject.png
  6. Choose Timer trigger. You’ll also want to specify how often you’d like the function app to run using CRON scheduling. The default value means that your function will execute every 5 minutes. In our published function, we’re going to check for form entries every 4 hours. While we’re debugging, we’re checking every minute – just until we’re ready to publish.ScheduleFunctionApp.png
  7. Your Function should look something like thisNewFunction.png
  8. Copy and paste the following code into your function. Replace the string placeholders in the RunAsync method with your own API Keys, referral form ID, and website domain, and make sure that you update your namespace and function app name (if you didn’t choose GravityForms_Referrals too).
using System;
using Microsoft.Azure.WebJobs;
using Microsoft.Azure.WebJobs.Host;
using System.Net.Http;
using System.Threading.Tasks;
using System.Web;
using System.Security.Cryptography;
using System.Net.Http.Headers;
using System.Text;
 
namespace GCITSFunctions
{
    public static class GravityForms_Referrals
    {
        [FunctionName("GravityForms_Referrals")]
        public static void Run([TimerTrigger("0 0 */4 * * *")]TimerInfo myTimer, TraceWriter log)
        {
            //Change the Timer Trigger above to "0 * * * * *" when debugging to avoid waiting too long for it to execute.
 
            log.Info($"C# Timer trigger function executed at: {DateTime.Now}");
             
            string content = RunAsync().Result;
 
            // Remove the comments from the below four lines once you've retrieved the HTTP POST URL from Microsoft Flow and added it in.
            //HttpClient client = new HttpClient();
            //HttpContent jsoncontent = new StringContent(content, Encoding.UTF8, "application/json");
            //string flowRequest = "<Enter flow HTTP POST URL HERE>";
            //var result = client.PostAsync(flowRequest, jsoncontent).Result;
        }
 
         
        static async Task<string> RunAsync()
        {
            HttpClient client = new HttpClient();
            // Add the public and private keys for Gravity Forms
            string publicKey = "<Enter Gravity Forms Public API Key>";
            string privateKey = "<Enter Gravity Forms Private API Key>";
            string method = "GET";
            // Specify the form ID of the form you're retrieving entries for
            string formId = "1";
            string route = string.Format("forms/{0}/entries", formId);
            /* Paging specifies the number of entries that will be retrieved from your form in this call, eg. 1000. You can make this higher or lower if you like. 
            It will retrieve the most recent entries first. */
            string paging = "&paging[page_size]=1000";
            string expires = Security.UtcTimestamp(new TimeSpan(0, 1, 0)).ToString();
            string signature = GenerateSignature(publicKey, privateKey, method, route);
            /* Replace gcits.com with your own domain name. If the call doesn't work initially, you may need to make sure that 'pretty' permalinks are enabled on your site.
            See here for more information: https://www.gravityhelp.com/documentation/article/web-api/ */
            string url = string.Format("//gcits.com/gravityformsapi/{0}?api_key={1}&signature={2}&expires={3}{4}", route, publicKey, signature, expires, paging);
            client.BaseAddress = new Uri(url);
            client.DefaultRequestHeaders.Accept.Clear();
            client.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));
            var response = await client.GetAsync(client.BaseAddress);
 
            string content = response.Content.ReadAsStringAsync().Result;
            return content;
 
        }
 
        public static string GenerateSignature(string publicKey, string privateKey, string method, string route)
        {
            string expires = Security.UtcTimestamp(new TimeSpan(0, 1, 0)).ToString();
            string stringToSign = string.Format("{0}:{1}:{2}:{3}", publicKey, method, route, expires);
            var sig = Security.Sign(stringToSign, privateKey);
            return (sig);
        }
 
 
    }
 
    public class Security
    {
 
        public static string UrlEncodeTo64(byte[] bytesToEncode)
        {
            string returnValue
                = System.Convert.ToBase64String(bytesToEncode);
 
            return HttpUtility.UrlEncode(returnValue);
        }
 
        public static string Sign(string value, string key)
        {
            using (var hmac = new HMACSHA1(Encoding.ASCII.GetBytes(key)))
            {
                return UrlEncodeTo64(hmac.ComputeHash(Encoding.ASCII.GetBytes(value)));
            }
        }
 
        public static int UtcTimestamp(TimeSpan timeSpanToAdd)
        {
            TimeSpan ts = (DateTime.UtcNow.Add(timeSpanToAdd) - new DateTime(1970, 1, 1, 0, 0, 0));
            int expires_int = (int)ts.TotalSeconds;
            return expires_int;
        }
    }
} 
  1. Update the TimerTrigger to ‘0 * * * * *’ while we debugUpdateTimerTrigger.png

     

  2. Add a reference to your function for System.Web by right clicking on your project and choosing Add, Reference.

    AddReferenceToFunction.png
  3. Scroll down to System.Web, check the box and click OK.AddSystemWebReference.png
  4. Next we need to add a connection string for an Azure Storage account into the local.settings.json file. You can retrieve a connection string from an existing storage account via the Azure Portal, or by downloading Azure Storage Explorer from www.storageexplorer.com, signing in and copying the Connection String from the bottom left properties section. I recommend downloading Storage Explorer anyway since it’s a handy tool for working with Azure Storage accounts. If you don’t have a storage account, you’ll need to make one.RetrieveConnectionString.png

     

  5. Once you’ve got the Connection string, paste it in the AzureWebJobsStorage value of the local.set

    AddConnectionString.png

     

That's it for part 1. In part 2, we set up the Azure Functions required to connect to Gravity Forms and ConnectWise. 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.

 

About the Author
  • Experienced Consultant with a demonstrated history of working in the information technology and services industry. Skilled in Office 365, Azure, SharePoint Online, PowerShell, Nintex, K2, SharePoint Designer workflow automation, PowerApps, Microsoft Flow, PowerShell, Active Directory, Operating Systems, Networking, and JavaScript. Strong consulting professional with a Bachelor of Engineering (B.E.) focused in Information Technology from Mumbai University.
  • I am a Microsoft Business Applications MVP and a Senior Manager at EY. I am a technology enthusiast and problem solver. I work/speak/blog/Vlog on Microsoft technology, including Office 365, Power Apps, Power Automate, SharePoint, and Teams Etc. I am helping global clients on Power Platform adoption and empowering them with Power Platform possibilities, capabilities, and easiness. I am a leader of the Houston Power Platform User Group and Power Automate community superuser. I love traveling , exploring new places, and meeting people from different cultures.
  • Read more about me and my achievements at: https://ganeshsanapblogs.wordpress.com/about MCT | SharePoint, Microsoft 365 and Power Platform Consultant | Contributor on SharePoint StackExchange, MSFT Techcommunity
  • Encodian Owner / Founder - Ex Microsoft Consulting Services - Architect / Developer - 20 years in SharePoint - PowerPlatform Fan
  • Founder of SKILLFUL SARDINE, a company focused on productivity and the Power Platform. You can find me on LinkedIn: https://linkedin.com/in/manueltgomes and twitter http://twitter.com/manueltgomes. I also write at https://www.manueltgomes.com, so if you want some Power Automate, SharePoint or Power Apps content I'm your guy 🙂
  • I am the Owner/Principal Architect at Don't Pa..Panic Consulting. I've been working in the information technology industry for over 30 years, and have played key roles in several enterprise SharePoint architectural design review, Intranet deployment, application development, and migration projects. I've been a Microsoft Most Valuable Professional (MVP) 15 consecutive years and am also a Microsoft Certified SharePoint Masters (MCSM) since 2013.
  • Big fan of Power Platform technologies and implemented many solutions.
  • Passionate #Programmer #SharePoint #SPFx #M365 #Power Platform| Microsoft MVP | SharePoint StackOverflow, Github, PnP contributor
  • Web site – https://kamdaryash.wordpress.com Youtube channel - https://www.youtube.com/channel/UCM149rFkLNgerSvgDVeYTZQ/