This all started out with solving a work problem where we wanted to be able present a customer 360 view to our customer service reps without having to deal with data storage issues. There were a couple of ways to do this but taking this approach would let me surface external data, search on it and be able to report on it without storing that data in Salesforce and without any custom development on the Salesforce side. Sounds pretty unbelievable but it’s actually doable with Salesforce Connect and MuleSoft.
The heavy lifting for this approach would be on the MuleSoft side. MuleSoft API’s are not OData enabled by default and in order to use Salesforce Connect you need to point to an OData compatible end point. You need to install an additional plug-in that enables you to create OData compatible API’s from Anypoint Studio. Then you need to define an odata.raml specification file which will define your external data source data structure. This odata file will be used to generate MuleSoft flows which will contain the actual logic for getting, searching, updating and deleting data (depending on which flows you need to do implement for your use case). Detailed instructions for setting this up from MuleSoft with are available here.
At a high level the following overview of what was needed in each system:
- Salesforce Connect license
- External objects in Salesforce
- Setup page layouts, tabs and other configuration for consuming the data in Salesforce
MySQL Database hosted in Azure that has Policy and Claims data (our external data source)
- Install the APIkit OData Extension in Anypoint Studio
Create an API specification file
- Auto-generate the Mule flows to handle OData requests from specification.
- After configuring a data source and adding logic to the flows, deploying the app exposes a RESTful API and an OData API accessible through different URLs.
- Deployed API to personal Anypoint Platform account to make the API externally accessible to Salesforce
In my use case, my data source was data from two tables from an Azure MySQL database. I defined the odata.raml specification in Mule to match the schema of these tables as below:
#%RAML 1.0 Library uses: odata: library/odataLibrary.raml types: policies: (odata.remote): Policy properties: PolicyID: type: number (odata.key): true (odata.nullable): false format: int32 PolicyName: type: string (odata.key): true (odata.nullable): false required: true EffectiveDate: type: date-only (odata.nullable): false (odata.key): false required: false ExpirationDate: type: date-only (odata.nullable): false (odata.key): false required: false SubmittedDate: type: date-only (odata.nullable): false (odata.key): false required: false CoverageType: type: string (odata.nullable): true (odata.key): false required: false RequestType: type: string (odata.nullable): true (odata.key): false required: false PolicyState: type: string (odata.nullable): true (odata.key): false maxLength: 2 Account: type: string (odata.nullable): true (odata.key): false maxLength: 12 required: false claims: (odata.remote): Claims properties: ClaimID: type: string (odata.key): true (odata.nullable): false maxLength: 8 ClaimDate: type: date-only (odata.nullable): false (odata.key): false required: false Status: type: string (odata.nullable): false (odata.key): false required: false AssignedTo: type: string (odata.nullable): false (odata.key): false required: false Account: type: string (odata.nullable): true (odata.key): false maxLength: 12 required: false
After writing the specification above I right click on my MuleSoft project and selected “Generate OData API from RAML Types“. This will generate an api.xml file which will contain the basic logic for the Mule flow.
There will be a main api flow with the API Kit Router, an api console flow and then get, put and delete methods for each of the objects defined in the odata.raml specification.
The methods will have the basic pluming but you will need to provide your own logic for the get, put and/or delete methods to in your use and perform any transformations on it before sending back the result to Salesforce.
Below, is the implementation I put in for the get method for policies and claims.
Once the project is implemented we need to deploy the API to CloudHub. Right click on your project and go to Anypoint Platform and then Deploy to CloudHub as shown in screenshot below:
Once your API is deployed to CloudHub, you should get an endpoint for that API. This is the endpoint that you will to consume External Objects via the OData API in Salesforce.
Next step is to go into Salesforce, then go to Setup –> External Data Sources and to create a new External Data Source. Configuration is shown in screenshot below:
Once you have setup the external data source you will see option to “Validate and sync“. Click this button and it will validate that your data source is accurate and will show you any external objects that it finds at this end point. You can then check the selected external objects you want to bring in and then sync them with your org – in my use case it was claims and policy objects I had defined in my odata.raml specification and their data source is the MySQL Server Database.
Once you sync the external objects, the data model for those objects should be created in Salesforce automatically based on the specifications you had defined in odata.raml. You can modify the external objects to add lookup relationships to standard and custom objects in Salesforce.
In this use case, I will link the policy and claim external objects to the standard Account object in Salesforce. In order to do this you need to create an Indirect Lookup Relationship to the Account object. The Indirect Lookup will need to point to any External ID field on your Account object. Once you setup this relationship you will be able to view policy and claim data related to that account in the Account page layout as a related list. See screenshot below:
The policies look like they are a part of your native Salesforce org and you can view the data for them just like you can view data for any standard/custom object in Salesforce.
We can also create a custom tab for the external object to view all the policy and claim records as a separate tab. See screenshot below:
As if this was not enough, you can also search and report on this data!!!!
We were able to do all this without writing a single line of code on the Salesforce side!
Hope you all found this blog post useful 🙂