Sunday, October 15, 2017

PowerApps and CRM optionsets

In this blog, I will walk you through the steps on how to work with the Dynamics CRM option sets in PowerApps. I will be extending the app I built in my last blog CRM and PowerApps revisited.

Problem Statement

The problem is that if you use out of the box Dynamics 365 Connector for PowerApps, it only retrieves the numeric value of the optionset. It does not return the text value of the optionsets. Below, I have added a Preferred Method of Contact field to the DetailScreen1.
As shown in the screenshot above, Preferred Method of Contact of 1 means nothing to a business user. It should display email, phone, mail, etc.


The Dynamics CRM connector for PowerApps does not display the text values of the optionsets or the metadata of the entities. So, there is no straight forward solution for this problem, but we can use something similar to a data maps table that contains what these numeric values mean.

Here are the steps:

Create a collection

We will create a collection ( to store the optionset values and the corresponding text.
1. Open the BrowseScreen1, choose On Start from the Actions Menu and add the following code.
Collect(Myc, {optionset:"PreferedContact", OptionValue:1,DisplayText:"Any"},{optionset:"PreferedContact", OptionValue:2,DisplayText:"Email"},{optionset:"PreferedContact", OptionValue:3,DisplayText:"Phone"},{optionset:"PreferedContact", OptionValue:3,DisplayText:"Fax"},{optionset:"PreferedContact", OptionValue:3,DisplayText:"Mail"})
The above code will create a collection Myc.
  • DisplayText- displays the text of the optionset
  • OptionValue- displays the numeric value of the optionset
  • Optionset- displays the name of the optionset field. This field will be useful to the store the values for the multiple optionset fields in the same collection.


    Display the Optionset Text

    We will be using the PowerApps Lookup function to display the text for the optionset.
    1. Open the DetailScreen1 and select the optionset field, as shown on the screen in the screenshot below.

    2. Click on the Unlock the change properties.
    3. Change the Data property to LookUp(Myc,((OptionValue=preferredcontactmethodcode)&&(optionset="PreferedContact")),DisplayText).
    4. The DetailScreen1 will display the text associated with the optionset numeric value.


      Editing the Optionsets in PowerApps

      The above solution can show the text, but won’t let the user edit the values in the app. The solution to allow users to edit the optionsets values in PowerApps is little tricky.
      Here are the steps to achieve this.
      1. Add the Preferred Method of Contact optionset to EditScreen1.
      2. Change the display for the field to Allowed Values as shown in the screenshot below. It will change the field to a dropdown field.

      3. Click on the Unlock the change properties.
      4. Select the dropdown field and change the Items property to collection Myc. In the dropdown properties pane, select the column to display in the Value field. By default, the first column of the collection is selected.

      5. Change the Default property to LookUp(Myc,((OptionValue=preferredcontactmethodcode)&&(optionset="PreferedContact")),DisplayText).
      6. Select the optionset card and change the Update property to DataCardValue4.Selected.OptionValue. This will tell the card to use the OptionValue field of the collection to update in CRM.

      7. Save the application and test it.
      I hope this helps.

      Wednesday, October 11, 2017

      CRM and PowerApps revisited

      Last year in June, I had written a blog about PowerApps. I created a simple app that could update a few fields and the entity image of Contact records, but there were a few problems with the functionality. Microsoft Flow, CDS and PowerApps are getting a lot of attention these days, so I decided to revisit my original blog and create the same app with latest PowerApps functionality. Since the PowerApps studio has changed from the original version, I have a made a few changes to the app as well. I am using PowerApps app installed on my Windows 10 machine.

      Here are the steps:

      Creating an app using Dynamics 365 as data source

      Create connection and generate a default app

      1. In PowerApps, Select New  >> Start with your data” >> Dynamics 365 , as shown in the screenshot below.

      2. Create a new connection to your CRM Online organisation. In my case, I already have connections, as shown in the screenshot. Click on the dataset for your organisation (in my case it is HPE).

      3. Select Contacts from the dropdown and click Connect, as shown in the screenshot.

      4. PowerApps will create an app. The app will have 3 Screens:
        • BrowseScreen1 (Home Page with a list of the contacts and a search box)
        • DetailScreen1 (Displays the details of the selected item on BrowseScreen1)
        • EditScreen1 (Opens the selected record/new record in edit mode)

      Update search and sort conditions on the browse screen

      1. On BrowseScreen1, select the browse gallery. the editor will display the Items properties.

      2. Change the value of the “Items” property to SortByColumns(Search(Contacts, TextSearchBox1.Text, "fullname"), "lastname", If(SortDescending1, Descending, Ascending)). It will change the search criteria to “fullname” and sort the records by “lastname”.

      Select fields to display in the gallery

      1. Select the fields in the first item of the gallery and choose the CRM field you would like to display in that control, as shown in the screenshot below.

      2. Choose the other fields to display in the gallery. The image control on the gallery is already set to display the entityimage of the contact.


      Select the fields on Detail and Edit screen

      1. Select DetailScreen1 and then select Data sources from the View Menu. It will display the Data window as shown in the screenshot below.

      2. Select DetailForm1. It will display all the available fields in the Data pane as shown in the screenshot. Click on the purple eye in the options pane to add or remove the fields on the form.


      Change the theme of the app

      1. You can change the color schema for the app by selecting predefined themes under Home>>Themes.


      Save and Share the app

      1. Save the app by providing the name of the app. You have an option to save the app on the cloud or on the local machine.

      2. Share your app with your colleagues by selecting Share this app.


      Testing the App

      I am testing this app on iPhone 7S. To run PowerApps on your mobile device, install PowerApps from the app store.
      1. Logon to the PowerApps and choose your app.

      2. The app will display the browse screen.

      3. Select the record to view the detail screen. All the fields are disabled on this screen.

      4. Click on the edit icon to edit the record.

      5. Click on Change Picture to upload a picture. On iPhone, you will get the following options.

      6. Take a new picture/select an existing picture and press save.

      7. Have a look at the browse screen. It should display a picture on the edited record.



      In general, I think PowerApps is great to build something quick instead of spending months to go live, but performance must be improved to make it usable in the real world.


      There are a few improvements in the latest version of PowerApps.
      1. Image control can display CRM entity images. This was not working a year ago.
      2. Image control can access iPhone camera. In my last attempt, I had to add the camera control to take the picture.
      3. The performance is better than a year ago.


      1. Photos take a long time to load. On the browser screen, the data fields load very quickly but photos appear after a few minutes.

      2. Users can’t control the orientation of the photos selected in the image control. Some photos get loaded with the wrong orientation. I have read on the PowerApps community forum that the PowerApps team is working on it.

      3. CRM connector does not have an option to retrieve specific columns instead of all the columns.

      Sunday, October 8, 2017

      CRM Web API and Certificates Issues

      I have not written a blog in last 8 months as I have been travelling nonstop for work. In this blog, I am going to share some behavior I came across a few weeks ago.

      So here is the story. We are using CRM Web API for integration between the on-premise system and CRM online. We are calling the CRM Web API from JavaScript running on the on-premise server using OAuth authentication. We used a process very similar to what is described in the following article:

      Generally, we don’t need to manually install SSL certificates of public sites on our personal machines, as most browsers and operating systems have already installed the root CA certificate of well known certificate authorities like Symantec, Digicert, Microsoft, etc.

      For security reasons, in an enterprise environment most of the root CA certificates/trusted public authorities are removed from the integration servers.

      To make the successful calls to CRM Web APIs, you have to install the following certificates onto your integration severs.
      1. Certificate to communicate with
      2. Dynamics CRM 365 Certificate
      Now the real story. We installed the certificates, and everything was working as expected for a couple of months, when suddenly it stopped working. After some investigation, we found out that we are not getting the authentication token from A bit more investigation, and we found out that the issue is related to the certificate. The server certificate presented by was different than the certificate we installed on our integration server. The old certificate was not expired, but still replaced by a new one. We replaced the certificates and everything is back to normal. I have attached the screen shots of both the certificates.

      Old Certificate


      New Certificate


      If you look at the certificates, the original certificate was issued by Symantec and was expiring on March 18th 2018, while the new certificate is issued by Microsoft itself. I hope this won’t happen again. So if your calls to CRM Web APIs stop working, check your certificates.

      Thursday, January 19, 2017

      Dynamics 365 and Logic Apps–Part 2

      In the first part of the blog, I had created a Logic App that serves as a callable endpoint to create a record in CRM. In this blog, we will look at adding some exception handling to the same app. Please read the first part Using Logic Apps to create records in Dynamics 365 - Part 1 to understand this part.

      Current Solution

      The process for the current solution in depicted in the following diagram


      If the Create a new record is successful the Response action will return the customer else the Logic app will through an error message.

      What will happen when CRM throws an error

      For this blog, I have created a synchronous workflow that will throw an error message if the Last Name is not provided in the request.

      Error 1 (ResponseTimeOut)

      I am sending the following request without the last name.
        "customer": {  
         "firstName": "MSCRMShop",  
         "streetAddress": "21 2nd Street",  
         "city": "New York"  
      The Logic App returned the following error message.
        "error": {  
         "code": "ResponseTimeout",  
         "message": "The server did not received a timely response from the upstream server. Request tracking id '08587170410911854011608943569'."  


      The reason behind the timeout error is that there is timeout limit of 90 seconds for a single http request in Logic Apps. The other question is that why CRM would take so long to return the error message. The reason is that CRM is not taking that long to throw the error message. It is the default retry policy of the Logic Apps that will try 5 times before throwing the error message.

      For verification, open the Logic App in Azure portal and look at the failed run. The action will display the message shown in the following screen shot.


      The App run is showing that the execution duration is 1.92 minutes. The error captured in Create a new record is the actual error thrown by CRM
      Now log on to CRM and check the execution history of the workflow. It will display that it has been executed 5 times.



      The solution in this scenario is to set Retry Policy to None for the action Create a new record. Switch to code view of the app and add the retry policy section as shown in the following screen shot.


      Error 2 (NoResponse)

      After fixing the Error 1, call the Logic App again by passing same body as in Error 1. This time the app will return the following error.
        "error": {  
         "code": "NoResponse",  
         "message": "The server did not received a response from an upstream server. Request tracking id '08587169819578381630235279815'."  


      The reason is that there is no response defined in our current solution to handle the error. The only response defined in the Logic App is to return the customer Id on successful creation of the record in CRM. Every action in Logic Apps have a section named “runAfter” which defined when this action will be executed.



      The solution in this scenario is to add another Response action that will be executed after the failed execution of Create a new record action.
      1. Add a new Response action to the app and set the properties as shown in the screen shot below.


        Status Code is set to output of Create a new Record, Headers section contains the content-type and Body is set to body of Output of Create a new Record.
      2. At this stage Response 2 will be fired on successful execution of Response action.
      3. Switch to the code view of the app. Scroll down to Response 2 definition.
      4. Change the runAfter section as shown below.

      5. Save the app.
      6. Switch back to designer mode and if there is no error in the app. It will look like the following screen.

      7. Test the Logic App again without passing the last name in the request body. This time Logic app will return the following message.


      This solution will only capture the errors thrown by CRM (Create_a_new_record action). If the error occurs before the call that won’t be captured by the solution.

      More Resources

      Check the following resources for advanced error handling information.

      Monday, January 9, 2017

      Using Logic Apps to create records in Dynamics 365 - Part 1

      According to Microsoft documentation, Logic Apps is a service to simplify and implement scalable integration and workflows in the cloud. As a CRM professional I was aware of Microsoft Flow but not of Logic Apps. Microsoft Flow is actually built on Logic App Most of the features and API connections are the same.

      The major difference is that Microsoft Flow is targeted for business users whereas Logic Apps is targeted towards IT professionals and developers. Logic Apps can have some additional connections like BizTalk APIs. The other major difference is that Microsoft Flow workflows are created in production but Logic Apps provides the DevOps and security assurance.

      The following link details the comparisons between Microsoft Flow and Logic Apps
      For this Blog I am building a Logic App that will act as endpoints to create records in Dynamics 365. The good thing is that we don’t need to write code to create the endpoints.

      Create a Logic App

      1. Logon to the Microsoft Azure Portal.
      2. Create a new Logic App by Navigating to New>>Web +Mobile>>Logic App as shown in the screen shot below.

      3. Enter the app name and other information as required and click Create. (I generally checked “Pin to Dashboard” to open the app straight from the dashboard)

      4. Open the newly created app.

      5. Click on Edit to open the designer.

      6. Select Request from the list of the managed APIs as shown in the screen shot.

      7. Enter the JSON schema for the request. I have created a customer object with first name, last name, street address and city.

      8. Click Save. It will populate the URL field of the request. This URL will be used as endpoints to call the Logic App.
      9. Click New step and choose Add an action.

      10. Select Dynamics 365 – Create a new record from the managed APIs list.

      11. Choose the Organisation Name, choose the Contacts as the entity name and map the first name, last name, street address and city with the JSON schema added in step 7.

      12. Click New step and choose Add an action as shown in the screen shot below.

      13. Select Response from the list.
      14. Enter the following information as shown in the screen shot below
        • Status Code of 200 (Which represents successful call).
        • Enter the “content-type” in the Headers section.
        • Enter the JSON in the body section. I am returning the contactid of the newly created contact.2017-01-08_17-52-28
      15. Save the app and test it.

      Testing the Logic App

      For testing we need to call Logic App using URL created for Request step 8 of Create a Logic App.


      For this blog, I am not creating a new application to call the endpoints(Logic App). I am using PostMan to create and send the request. Here are the steps.

      1. Open the PostMan application.

      2. Enter the URL copied from the Request.
      3. Enter the Headers as shown in the screen shot below. The endpoints are expecting JSON.

      4. Enter the Body and Click Send. The  Logic App will return the customer id as shown in the screen shot below.

      5. Open the CRM and check the contact is created.
      6. If there is an error, go to Azure portal and look the app summary and troubleshoot the error.


      That is it . You got your Logic App as callable endpoints.