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

[elliotmunro]

Test your ConnectWiseGetContactByID Function

1. Return to your ConnectWiseGetContactByID function
   In the Request Body window on the right, copy and paste the following JSON and click Save and Run.

   {
       "item": "25"
   }

2. Keep an eye on the Output window, you should see the details for a ConnectWise contact appear in JSON format.
   If this particular contact ID doesn't return a result, do a bit of trial and error with Contact IDs to retrieve a contact that does. Ideally, you want to retrieve the details for a customer's contact record. The ConnectWise member contacts can include some extra JSON schema that doesn't apply to customers.
3. Copy the JSON content from the Output window and save it somewhere. We'll be using this as sample schema in a later step.
4. Copy the Azure Function Authorization URLs from both the ConnectWiseGetContactByID and ConnectWiseAddOpportunity Azure Functions. You can retrieve this by clicking Get function URL, then copying the result.
5. Save these somewhere for later. We'll use these to call Azure Functions from Microsoft Flow.

Complete your Microsoft Flow

1. Return to your Microsoft Flow and remove the action below the Request trigger
2. Add an Apply to each step and add the entries output from the popout menu to the ‘Select an output’ field. Then add a SharePoint – Get Items action into the Apply to each step and select or enter your SharePoint site, then choose the SharePoint list you created earlier.
3. Click Show advanced options and add gfid eq ‘id’ into the Filter Query field. Where ‘id’ is the id output from the Request trigger. Be sure to include the single quotes. This step checks the SharePoint List for existing entries with the same Gravity Forms entry ID.
4. Next add a Condition step, and click Edit in advanced mode. Copy and paste the following into this field:

   @empty(body('Get_items')?['value'])

   This checks whether any items were returned from SharePoint that match that Gravity Forms ID. If there weren’t any, we’ll create one.
5. Your flow layout should now look like this:
6. In the Yes outcome, add in a HTTP - HTTP action, this will call the ConnectWiseGetContactByID function.
7. Choose POST for the method and paste in the Authorization URL for the ConnectWiseGetContactByID function into the Uri field.
8. In the Body field you can enter the following code: 

   {"item":"@{items('Apply_to_each')?['1']}"}}}

   This will work providing that the contact's ID was in the Gravity Forms field with an ID of 1. If it's not, you'll need to update the 1 to the field ID that contains the contact ID. Alternative, you can compose your Request body as per this screenshot. Keep in mind that the current Flow interface can get unweildy when dealing with a lot of outputs in a request body, so you may decide to type the longer bodies using the Workflow Definition Language schema.

9. When run, this step is going to return the JSON output that we copied when testing the ConnectWiseGetContactByID function. To use this returned data, we'll need to parse the JSON into its values. Add a Data Operations - Parse JSON Action. In the Content field, drag in the output from the HTTP - HTTP step, then click Use sample payload to generate schema. Paste in the output that we saved from the ConnectWiseGetContactByID Azure Function and click Done.

10. Now we want to create an item in SharePoint with the referral details, and the details of the contact that provided it. Add a SharePoint - Create Item step and select your site and referrals list. Populate the fields with the corresponding field IDs from your Gravity Form. Use the outputs from the Data Operations - Parse JSON step to add in the referring contact's info.

11. Add an Office 365 - Send Email step with the referral info to notify yourself that a new lead has arrived

12. Add another HTTP - HTTP step. You can rename this one to HTTP - Add Opportunity.

13. Set the Method to POST and paste the ConnectWiseAddOpportunity Authorization Url into the Uri field.

14. For the request body, I recommend you copy and paste this JSON output into the Body field as it can be quite difficult to format a call using this number of outputs. You may need to tweak this JSON to match the specific IDs for your Gravity forms field ID values. 

    {
        "companyId": "@body('Parse_JSON')?['company']?['id']",
        "contactId": "@body('Parse_JSON')?['id']",
        "siteId": "@body('Parse_JSON')?['site']?['id']",
        "opportunityName": "Referral to @{items('Apply_to_each')?['3']} from @{body('Parse_JSON')?['firstName']} @{body('Parse_JSON')?['lastName']}",
        "notes": "Contact Person: @{items('Apply_to_each')?['4']} \r\nPhone: @{items('Apply_to_each')?['6']} \r\nExtra Info: @{items('Apply_to_each')?['7']}"
    }

    It should look like this once you update the Flow:

Test your flow by running your Visual Studio Function App

1. Return to Visual Studio and press F5 to resend your referral form entry to Microsoft Flow
2. You should now see a new entry for the referral in your SharePoint list
3. You should receive an email notification
4. You should also now see a new opportunity in ConnectWise

Publish your local Azure Function App

To make sure that your form data stays up to date, we need to publish our Function App to Azure.

1. Switch to Visual Studio, and update the timer on your function app to ‘0 0 */4 * * *‘ to make sure it doesn’t keep running each minute in the cloud.
2. Now, right click on your project name and click Publish
3. Click Azure Function App, and choose Create New.
4. Since we’re creating a new Azure Function App we need to specify some details. As mentioned earlier, Function Apps are just Azure Web Apps. To deploy them, we need to create or choose an App Service. Give your Function App an App Name, select your Azure subscription, choose or create a Resource Group, App Service Plan and Storage Account. You can use the same App Service Plan that you used for the other Function App. Alternatively, you can just choose the Consumption Plan.
5. Once you’re happy with your settings, click OK, then Create, and wait for your App Service to deploy.
6. When it finishes, click Publish. Your function app is now deploying to Azure.
7. Sign in to https://portal.azure.com to see it in action under Web Apps. By default, your functions are in Read Only mode, and you won’t be able to view or edit the underlying C# code. I Have an extra function here because I'm also importing website enquiries.
8. To keep track of your function’s activity, you can see function runs in the Monitor Section.

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.