Microsoft recently announced Azure App Service, a new Azure service which integrates Web Apps, Mobile Apps, Logic Apps and API Apps in one service. Scott Guthrie’s blog has a very good article that explains this new service.
In this article I will talk about how to use Azure App Services to integrate with Salesforce. For this example I will develop a small solution based on a real case scenario. I will assume the reader is more familiarized with Microsoft’s technology so I will spend more time explaining the details about Salesforce.
The Scenario
The scenario is the following: suppose that our client is an insurance company that uses Salesforce as its CRM. Insured's call the Call Center department to requests assistances depending on the policy coverage they have contracted, for example: assistance on the road (for automotive policies), or request for professional assistance - e.g. a plumber, locksmith, etc. – at home (for home insurance policies). All assistances are attended by third party service providers (car shops, locksmiths, car towing companies, etc.) The idea is that service requests are made in the CRM, and then third party service providers can attend these requests and mark them as completed once the service has been delivered. It would be ideal that external providers could have access to the CRM to see all the pending requests and do updates directly in the system. However, in our case it won’t be that easy: there are many service providers and we don’t want to buy Salesforce licenses for them (Salesforce licenses are not precisely cheap), and besides, we don’t want external users to have access to our CRM. You could create a Community for this and be done with it, but in this case, and for the sake of illustration, I will instead show how you can develop a small web portal for service providers which we will synchronize with CRM using Azure App Services.
Our solution will have the following flow:
- Call Center users will receive assistance requests by insured’s. These requests will be entered in the CRM (Salesforce)
- At regular intervals, the Logic App will get from the CRM all pending requests using a Salesforce Connector. These requests will be sent to the database of the service provider web portal using a SQL Server connector
- New requests will appear in the service provider portal
- Service providers will take requests and deliver them. Once delivered, they will change the request’s status to completed
- At regular intervals the Logic App will get all completed requests and synchronize them back to the CRM using a Salesforce Connector
- Finally, CRM users will be able to see all the external service provider deliveries completed
In the diagram, we can see that we will require a bidirectional synchronization between Salesforce and the web app we will create.
It is worth noting that the goal of this article is to describe how to use Azure App Service to do integrations with Salesforce. The solution we will develop in this article does not pretend to be a complete solution, but a simple solution that will allow us to see the most important aspects to consider when doing these kind of integrations. A complete solution would probable have a better state workflow associated to requests, notifications (email, SMS and/or push) of new assignments to third party providers, notifications to insured’s of the status of their requests, etc. To maintain things simple, and the article not as long, we will focus on the most important aspects and leave some of the details of a real solution as homework.
Requirements
In order to follow along and create the solution we will need:
- An Azure subscription. If you don’t have one you can get one free trial from the Azure web site
- A Salesforce development environment. You can get a free Salesforce Developer instance from the Salesforce Developer’s web site. When you register you will get an email with information about how to connect to the instance.
- Visual Studio 2013 Community Edition (Update 4 at the time if this writing). You can download it free from the Visual Studio web site. You will also need the Azure SDK (version 2.5.1 at the this of this writing).
Development of the Salesforce Application
The first thing we’ll do is creating the necessary objects in our Salesforce developer instance. If you requested a new development instance and is the first time you get into Salesforce you will directly enter the Salesforce setup area. From here we can customize the system and create new objects. In our case we want an object called Assistance.
From the menu on the left, we will go the the “Build” section, then “Create”, and then “Objects”. You can also use the search box on the top and enter a keyword to filter the options available on the menu. On the “Custom Objects” screen click the “New Custom Object” button. On the screen to create the object fill the required data as shown in the following screen. For the rest of the information leave the default value.
Notice the “Object Name” is Assistance. Every object in Salesforce has an API Name, which is the name that we will use to identify the object in development. As a rule, Salesforce adds the __c suffix to the name of all custom objects. So, the real name of our newly created object is Assistance__c.
Click on the “Save” button to create the object. In Salesforce objects are immediately available. We will proceed to create some fields to get the data required for an assistance. On the same screen of the object, on the “Custom Fields & Relationships” section, click the “New” button and launch the new field wizard. We will first create the relationship between assistances and contacts: a contact could have many assistances and an assistance belongs to a unique contact. Salesforce comes out-of-the-box with an object called Contact (here the name doesn’t have the __c suffix as this is a standard object and not a custom object, all out-of-the-box objects are standard objects). To create the relationship between assistance and contact we need to create a field of type “Lookup Relationship”, we will choose this option on the wizard screen and click “Next”. On the next step, in the “Related To” field choose the Contact object. On the next step specify Contact as the name of the field, and leave the default values for the rest.
Click on “Next” and leave the default options for the rest of the steps and finalize the wizard clicking on “Save & New” to restart the wizard to create a new field. We will repeat the wizard to create all the fields described below:
| Data Type |
Label |
Name |
Observations |
| Date |
Date |
Date |
In the field “Default Value” enter the function TODAY() |
| Text Area (Long) |
Description |
Description |
|
| Text |
Provider |
Provider |
Enter 50 in the field “Length” |
| Picklist |
Status |
Status |
In the field “Enter values for the picklist…” enter the following values: New
Completed Mark the field “Use first value as default value” |
| Checkbox |
Synced |
Synced |
|
After you finish creating the fields, these should look as follows:
Note that, as with the name of the custom object, the names of custom fields have also the __c suffix. Every object in Salesforce, custom or standard, has two fields called Id and Name. The first one is the primary key of the object, and the second is a text that works as the descriptor for the record. Both fields do not have the __c suffix, as they’re considered standard fields (even in custom objects).
Test the Application by Creating a Few Records
We already have our object for assistances. To test it we will create a new assistance. From the top menu choose the tab for “Contacts” to see a list of recent contacts (if it is the first time we use this tab, we will not have any recent contact and the list is empty). To see the list of all contacts, from the “View” dropdown select the “All Contacts” list and click the “Go” button. Select any of the contacts in the list to see his/her details. At the end of the form we should see the section for “Assistances”. Click on the “New Assistance” button to create a new assistance record. Notice how some of the fields are automatically filled based on default values: the Contact field gets filled since we created the record from a contact record, the Date field has today’s date as a default, and the Status is set to New as is the first value introduced in the picklist. Enter a name and description and save the assistance.
Create one or two more assistances so we have enough data for the rest of the tests that will follow.
If we go back to the contact record we should see the requests we just created. However, in the list we only see the name of the request. To see additional fields we need to modify the form. At the top of the page there is a link called “Edit Layout” which opens the form editor.
On the edit layout screen look at the end for the list of assistances and with the mouse hover on the title of the list until a wrench icon appears. When clicking this icon you will see a screen where you can select the fields you want to display on the list. Select fields Date, Status and Provider and then click the “OK” button. To save the layout click the “Save” button at the top of the layout editor, returning back to the contact record. You will now be a able to see more details in the list of assistances:
Development of the External Service Provider’s Portal
We will now proceed with the creation of the provider’s portal. For this we will create an MVC application using Visual Studio, and later publish it in Azure as a Web App with a SQL Server database.
Open Visual Studio and create a new project of type “ASP.Net Web Application”. I have called mine ProvidersPortal. Use the MVC project template and choose “Individual User Accounts” as the authentication method. Once the project is created we will enable Entity Framework Migrations. From the Package Manager console enter the following commands:
enable-migrations
add-migration Initial
update-database
Next, we will create a new entity to represent assistances. In the Models directory, add a new class and call the file Assistance.cs and overwrite the content of the file with the following code:
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Web;
namespace ProvidersPortal.Models
{
public class Assistance
{
[MaxLength(20)]
[Key]
public string Id { get; set; }
[MaxLength(80)]
public string Name { get; set; }
public string Description { get; set; }
[MaxLength(50)]
public string Provider { get; set; }
[MaxLength(20)]
public string Status { get; set; }
public DateTime? Date { get; set; }
public bool? Synced { get; set;}
}
}
To expose this object to Entity Framework we need to add a property in the class for the context. To keep things simple we will use the same database used by ASP.Net Identity. Open the file Models\IdentityModels.cs and add the highlighted line to the class ApplicationDbContext:
public class ApplicationDbContext : IdentityDbContext<ApplicationUser>
{
public ApplicationDbContext()
: base("DefaultConnection", throwIfV1Schema: false)
{
}
public static ApplicationDbContext Create()
{
return new ApplicationDbContext();
}
public DbSet<Assistance> Assistances { get; set; }
}
Next, add a new migration for the new object. In the Package Manager console enter the following commands:
add-migration Assistance
update-database
Now, we need a controller and a view for the assistance object. On the Controllers folder, add a new controller of type “MVC 5 Controller with views, using Entity Framework”. Fill the data as shown in the following figure:
Visual Studio will create (scaffold) files for the controller and the views for the typical CRUD operations. By default the controller is created allowing anonymous access to it, and in our case we want only authenticated users (external service providers) to have access to the assistance requests. Open the controller, the file Controllers\AssistanceController.cs and add the Authorize attribute to it:
[Authorize]
public class AssistancesController : Controller
Now all we have left is to add a menu option to access the assistances. Open the file Views\Shared\_Layout.cshtml and add a new option to the menu, as shown in the following highlighted code:
<ul class="nav navbar-nav">
<li>@Html.ActionLink("Home", "Index", "Home")</li>
<li>@Html.ActionLink("Assistances", "Index", "Assistances")</li>
<li>@Html.ActionLink("About", "About", "Home")</li>
<li>@Html.ActionLink("Contact", "Contact", "Home")</li>
</ul>
Compile and execute the application. After registering you should be able to see the assistances screen (which will be empty at the moment):
Publishing of the Portal as a Web App
Now we’ll proceed to publishing the portal as a Web App in Azure. We will let Visual Studio do all the work for us. In the “Solution Explorer”, on the project node, right click and choose “Publish…”. Select “Microsoft Azure Web Apps” as the target. Visual Studio will ask you for credentials to connect to your Azure subscription. Click on the “New…” button to create a new application. I have called mine ProvidersPortal, and to keep things well organized I’ve decided to create a App Service plan as well as a new resource group for this application. We also need a database, so select the option to create a new database server. Click the “Create” button to let Visual Studio create the app in Azure.
Write down the name of the App Service plan and the resource group, you will need these when you create the rest of the applications (Logic Apps and API Apps) as they need to have the same plan and group so they can see each other. In my case, both the plan and the group is called ProvidersPortal.
Once the web application is created in Azure, the wizard will continue with the publishing. In the “Settings” section make sure you select “Execute Code First Migrations (runs on application start)” in order to provision the Azure database with the schema required by our application.
Finally, click the “Publish” button and when the wizard finishes will open the provider’s portal executing on the cloud! Register a user and browse the Assistances menu item so Entity Framework will create the database schemas for the app.
Integration using a Logic App
We already have our two applications executing independently. Now comes the fun part: the integration of both applications using a Logic App in Azure. We will use the Azure portal to do all the steps that follow.
First, we will create the API Apps that will allow us to connect to Salesforce and SQL Server.
Creation of API Apps
We need two API Apps, one for Salesforce and the other for SQL Server. Both apps will be created from the Azure Marketplace.
Salesforce API App
On the Azure portal, click on “New”, select “Web + Mobile” and then “Azure Marketplace”. Select the “API Apps” category. On the search field enter “salesforce” and press enter. From the search results click on “Salesforce Connector”. A blade will open with the details of the connector and with links to its documentation. Click on the “Create” button. A new blade will open with the properties of the connector. From here select the “Package Settings” section. In this section you can specify the Salesfoce objects you would like to access. Remove the objects that appear by default and enter the name of the object we created in Salesforce: Assistance__c (remember the __c suffix, which is part of the name):
In the section “App Service Plan” select the plan created before when publishing the web application. The service plan must be the same so all the parts can work together. In my case the service plan is called ProvidersPortal. Click on “Create” and let Azure provision the API App. After a minute approximately you will see a notification telling the app is ready.
By the way, if you want to modify the connector configuration parameters later, you need to do it from the Host of the API App (you have a link to the host on the connector details blade)
SQL Server API App
As we did before, from the Azure Marketplace search for “sql” and in the search results click on “Microsoft SQL Connector”. A blade will open with the connector details and a link to its documentation. Click the “Create” button to open a new blade with the configuration of the connector. From here select “Package Settings” section. In this section you can specify the connection details to the database. If you have doubts about what are the right values to put here you can look at the connection string that was created in the publishing profile of the web project when we published it to Azure (in Visual Studio, in Solution Explorer, go to Properties\PublishProfiles\<projectname>.pubxml). For the “Tables” specify Assistances. Make sure to select the same service plan we’ve been using. This is how the properties look in my case:
Click “Create” and let Azure provision the API App. After a minute approximately you will see a notification telling the app is ready. As with the previous API app, if you need to modify the configuration of the connector (because they changed or because you made a mistake) you will have to do it from the Host of the API App.
Creation of the Logic App
Let’s stop here for a moment and analyze what we want to accomplish. We want a bidirectional synchronization between Salesforce and the SQL Server database.
For the synchronization from Salesforce to SQL Server we want to do the following:
- First we find all those assistances that are in Salesforce which have not been synced and with a status of “New”
- For each one of these assistances we get its details
- For each one of these assistances we will add a new record in the provider portal’s database
- If the insertion is successful we mark the assistance as synced in Salesforce
For the synchronization from SQL Server to Salesforce we want to do the following:
- We will look for all the assistance records that have been completed and have not been synced yet
- For each one of these records we will update Salesforce
- If the update is successful we will mark the record as synced in the portal
Since (for the moment) the logic inside Logic Apps is lineal (the current step always depends on the previous) we will need to create two Logic Apps, one for each direction of the synchronization.
Note: in this case we will use a boolean field to know what records need to be synced from one system to the other. In a real application this is probably not the best since each record will be synced only once between systems. A better approach would be to base the synchronization on a timestamp field that has the last modification date made to a record.
Synchronization from Salesforce to SQL Server
We will now proceed to create the synchronization between both applications. For this we will create a Logic App and use the connectors created before. On the Azure portal, click on “New”, then on “Web + Mobile” and select “Logic App”. A new blade will open where you can enter the name of the app and select the service plan. Again, make sure to select the same service plan specified in the connectors. I called my logic app ProvidersPortalLogic and selected the ProvidersPortal plan. Click on “Create” to provision the application. After a minute approximately you will see a notification telling the app is ready.
Open the application and select the section “triggers and actions” to show the canvas to edit the actions for the app. For now, and to be able to develop and test our app, click on the option “Run this logic manually” which will allow us to run the application on demand as we develop it. On the right section of the edition canvas you should see, among others, the two API applications we created in previous steps: the Salesforce and the SQL Server connectors. If you don’t see the connectors you probably didn’t select the appropriate service plans to be all the same.
Let’s start by adding the first step. On the “API Apps” panel click on the Salesforce connector. This will add a step to the logic. You need to authorize, i.e. provide the connection credentials, the connector so it can connect to the Salesforce instance. Click on the “Authorize” button, a screen requesting Salesforce credentials will appear. Enter your credentials and when asked if you want to give access to the Azure Logic App say yes. If everything went ok the connector will get the metadata from Salesforce and will present you with some options:
It is possible that you get an error saying the the metadata has not been generate (“Error fetching swagger api definition”). You could try remove the step (by clicking on the wrench icon in the upper right menu of the step and selecting “Delete this action”) and try again. If you still get the same error then you can do the following trick: change the editor to the the code view by clicking on the “Code View” icon 
and remove the Salesforce subscription (the one highlighted on the following image) and then try to add the step again.
If the above trick doesn’t fix the problem the you probably have an error in the configuration of the API App (the connector).
From the step actions click on three dots “…” to get more actions. Select the “Execute Query” action. This action allows you to execute a SOQL query against Salesforce. Enter the following query:
select Id from Assistance__c where Synced__c = false and Status__c = 'New'
We want the id of the records that have not yet been synchronized and with a status of “New”. When we validate the step we should see something like this.
When this step gets executed we will get all the assistance records that satisfy the criteria, and they will be stored in a property called result. Next we will add the second step: get the details of each assistance.
Add a new step by clicking on the Salesforce connector in the “API Apps” panel. Select “Get_Assistance__c” as the action. This action will get all the fields of a particular record. We want this step to be executed by each of the records obtained in the previous step. To achieve this, on the step menu, click on the wrench icon and select “Repeat over a list”. A new field “Repeat” will appear allowing you to specify the list to use to get the records. We will use an expression to get the list. Expressions are specified by adding the @ character as a prefix. If we want to get the list of records from the previous step we will use the following expression:
@body(‘salesforceconnector’).result.records
The expression means the following: get the body of the step called salesforceconnector and search for a property called result.records. The body is formatted as JSON and the result property is an object which contains an array property called records, containing all the records returned by the query.
By default, the name of each step is the name of the connector plus an index, so for example, the first step of the Salesforce connector is called salesforceconnector, the second step is called salesforceconnector0, the third is salesforceconnector1, and so on.
In the field “Record Id” we will enter the id of the current record in the loop. To get the current record inside the loop we will use the @repeatItem() expression. So, if we want the id of the record we will use:
@repeatItem().Id
The step should look like this:
Note: we could not add this second step to the logic and instead get the required data by specifying it on the query of the first step, but I’ve done it this way to illustrate the concepts.
Now that we have the data for each record the third step is to insert the record in the database of the web application. For this we will use the SQL Server connector. On the “API Apps” panel click on “Microsoft SQL Connector”. This will add a new step to the logic. Select the action “Insert into Assistances (JSON)”. The connector will ask for parameters for each field in the table. As with the previous step, we want this insertion to be made for each record in the list so click on the wrench icon and select “Repeat over a list”. In this case we get the list using the following expression:
@actions('salesforceconnector0').outputs.repeatItems
salesforceconnector0 is the previous step, from which we want to take the outputs property representing the output of the step (this output is actually many records since the step was also executed for a list). From this output we want the repeatItems property, which is an array with the result of each record it got in the loop. To get a specific field we will use again the expression @repeatItem():
@repeatItem().outputs.body.Id
The above expression would get the field Id from the Salesforce record. If this looks complex to you the best thing is for you to execute the application every time you add a step and analyze the output of each step to see the JSON produced. This will give you an idea on how to get the values you need.
We will repeat the above expression for each field of the table. Remember once again that, because we’re dealing with custom fields, you need to add the __c suffix to the field name, except for the Id and Name fields which are standard fields (even though they belong to a custom object). Fill the fields using the following table:
| Field |
Expression |
| Id |
@repeatItem().outputs.body.Id |
| Name |
@repeatItem().outputs.body.Name |
| Description |
@repeatItem().outputs.body.Description__c |
| Provider |
|
| Status |
@repeatItem().outputs.body.Status__c |
| Date |
** |
| Synced |
|
** The date field should have the following expression: @repeatItem().outputs.body.Date__c, however this value was giving me errors when trying to insert the record in SQL Server, something related with the date format. After trying unsuccessfully with various formats I decided to leave it blank at the moment and later find a solution.
We will leave fields Provider and Synced blank as these will be filled in the provider’s portal. The step should look as follows:
The last step would be to update the record in Salesforce and mark it as synced so the record would not be considered in subsequent runs of the logic app. Add a new step to the logic by clicking on the Salesforce connector in the “API Apps” panel. For this step select the action “Update Assistance__c”. Make the step execute for a list (select “Repeat over a list” from the step menu). The parameters for fields “Repeat” and “Record Id” are the same as in the previous step. Set the Synced field to true.
We have our logic app ready! Make sure to save changes if you haven’t done it yet and close the editor (you could get a message asking if you want to discard your changes even if you have saved them, say yes, your changes are already saved). Execute the application by clicking on the run icon 
. If everything works ok you should be able to see the results of the execution:
You should also be able to see the assistances you created in Salesforce in the provider’s portal:
And records in Salesforce should be marked as synced:
With this we have finished the first part of our solution.
Synchronizing from SQL Server to Salesforce
For the second part of the solution we will create a new Logic App. The steps are the same as the ones we did for the previous app. I’ll just mention that I will call this app SalesforceLogic. Remember to assign the same service plan. To execute this app on demand make sure you select the “Run this logic manually”.
Let’s start with the first step. Add a SQL Server connector to the app and select the action “Select from Assistances (JSON)”. Leave “Fields“ blank to get all the fields of the table. In the “Where” add the following condition:
Status = 'Completed' and Synced is null
The step should look like this:
Now add a second step by clicking on the Salesforce connector. Since this is a new logic app you need to authorize it against the Salesforce instance again. Select the action “Update Assistance__c”. We want to execute the update in Salesforce for each one of the records retrieved in the previous step, so we need to execute this step for a list: select the “Repeat over a list” from the step menu. For the list we will use the body of the previous step with the expression @body(‘microsoftsqlconnector’), and for the “Record Id” use the expression @repeatItem().Id. We need to update the fields Provider__c and Status__c in Salesforce by using the expressions @repeatItem().Provider and @repeatItem().Status, respectively. The step should look as follows:
Finally, we will add a third step using the SQL Server connector to update the Synced field in the portal’s database so it will not be considered when the logic app is executed again. For this we will make this step run for the same list of the first step: @body(‘microsoftsqlconnector’) and set the Synced field to true. This step requires a “Where” condition which needs to be dynamic using the following expression:
@concat('Id = %27',repeatItem().Id,'%27')
Here the trick is to use the URL codification of the ' character (simple quote) using the code '%27'. The reason to do it like this is because we cannot use a simple quote inside a text (even though I have tried different escape sequences). This will produce a condition such as this: Id = 'xxxxxxx'.
We have our logic ready! Make sure to save changes and close the editor.
On the provider’s portal select some of the assistances and fill the Provider field and change the Status to Completed.
Execute the application by clicking on the run icon 
. If everything works ok you should be able to see the records in Salesforce updated:
Also you should see records marked as synced in the portal’s database:
Automatic Execution
So far we have executed both Logic Apps manually on demand. On a real application we will execute both applications automatically at regular intervals. For this we need to uncheck the option “Run this logic manually” and use some application with a trigger. The Salesforce connector doesn’t provide any trigger, but the SQL Server connector does. In both cases one option is to use the “Recurrence” application, which will allow use to automatically execute the application using a predefined time interval. The only inconvenience of this is that, if you use the free plan, the minimum time interval you can specify is one hour (one of the reasons why we chose to run the logic on demand for this article).
Conclusions
As we’ve seen, Azure App Services is a powerful tool. With a great amount of connectors (which is rapidly growing) and with the escalation possibilities that offers Azure, it is very easy to create solutions such as the one created in this article. Definitely a tool that any Software Architect should have in her tool belt when designing integrations solutions.
It is true that some areas could be improved. Documentation lacks some details, and there are some minor errors when designing the application logic, but I’m pretty sure this will evolve and get fixed (if not already) as time goes by. Also, the logic of an App Logic is very lineal, and although you can get around some scenarios, this limits some other scenarios, but again, I’m pretty sure this will change later as they will add more options to it.
So, things can only get better, so I will be following this service and see how it evolves from here.
Resources
You can also see this article in Spanish in the Microsoft MSDN blog for Spain:
