Use Power Platform with On-Premise SQL Databases

Power platform is all built around data. Luckily, this data can reside anywhere, thanks to the Connections feature provided by the platform. If you want your Apps to interact with data from a local database hosted on a server somewhere then this is how you do it. This has a lot of potential from master data management to compliance to regulations and many more. I will go through a simple example starting from creating the database then to building a Canvas App around the data.

Step 1: Create the database and a table (if you don’t have one already). In your server, create a database or use an existing one. In this case, I created a DB called Master Accounts.

To simulate a CRUD operation later, I created a table called Accounts in this database using the following script. Make sure to specify a Primary Key or Canvas App will make your app read only without the ability to add/delete records.

Step 2: Create a user that can access this database. This is achieved by creating a “login” in the security database in your server and assigning this login the created database.

Step 3: To give the Power Platform the ability to interact with you local database, you need to install the On-Premises Data gateway. Notice that this gateway can be configured to work with all the Power Platform apps or only Power BI, of course we need the former option. After you are done, the gateway interface will look like the following image.

Step 4: Sign in with you Power Platform Admin Account:

When done, this is what you should get:

Step 5: Connect to the database from Power Platform. Visit make.powerapps.com and on the left pane, select Data and Connections. Select the New Connection from the command bar.

In the new Connection wizard, select the type of connection to be SQL Server and you should see a window similar to the following:

Of course, you can authenticate in different way, but I choose the SQL Server Authentication mechanism. Plug the values we created in the first two steps. Make sure that the correct gateway is selected.

If all is well, you should see a new connection in the list, mine looks like this:

Now let’s go and create a canvas app from data and see the magic!

Step 6: Create a Canvas App starting from SQL Server Data.

Select the “Accounts” table we created before and hit Connect:

And now you should end up with a Canvas app the can perform CRUD Operations on the local database. It will require some redesign though:)

The nice thing is that this connection is a Power Platform Connection now so you can use it with Flow!

This capability opens a lot of doors for the organizations that are hesitant to move their data up in the clouds, so go experiment with it and use it!

Power Platform and Change Management

Let’s face it, switching users from using their Excel sheets or Access databases toward using one monolithic Dynamics 365 application can be a hard change management process if you have so many users to convince. Sometimes,even the upper management can’t force that change depending on what type of organization it is.

With the new Power platform capabilities , the change management seems to be getting easier and easier because now we have options that we didn’t have before (or we did have but the are currently improved). Once the organization decides that this is the platform to go with, then here are some options that will make it easier to convince the user base to switch.

The simple approach that can be used right away is using the model-driven apps capability of dividing your applications into verticals. If you have one huge application with so many entities, then create multiple apps that are used by different business units or group of users. Each business unit or group should only see what they need to see and in this way, the probability users getting lost in the application is reduced and the amount of training needed for the users is reduced. This also means that error rate will be reduced as well because their options are more limited to what they need only.

With Model-driven apps, and in addition to limiting what entities a user can see, you can also limit what forms, views, charts, dashboards and business process flows. So when you have an entity (like the Case) that is used by multiple groups then each group can see their own forms and views and charts without being overwhelmed with everything else. I won’t call this a security layer but a way of organizing components.

Image result for model driven apps"

If model-driven apps are not enough, then the Canvas Apps are to the rescue. Canvas Apps are new and their concept is new. Unlike model-driven app that seem intuitive to someone who knows the previous versions of Dynamics, Canvas App require a shift in the design mentality. Now we are not talking about a single application that can do many things, but about an application and many other little helper separate applications around it that all feed the same data layer (Common Data Model). So when you create data using a Canvas App, it is possible to view it from Dynamics and vice versa.

The introduction of Canvas Apps adds a new question during to the design process: “Should we implement this module in Dynamics or using a Canvas App?“. This question is becoming an important one because it doesn’t only affect the application architecture but also the user on-boarding experience, training time, error rate and user confidence.

Canvas apps are great when there is a user or group of users who do a limited set of functionalities that can be separated away. Take an example of a service call center agent who just answers the calls, log a ticket and try to solve it or escalate it. You don’t need to train this agent on the whole almighty Dynamics for customer service but only on a screen or two of the Canvas App that she and her team has access to. Keep in mind that Canvas Apps can have more complicated use cases.

So to make the change management process easier, you don’t need to take the users away from their Excel sheet into an application that is a 100 times the size of their Excel sheet but to an application that is almost the same size as their Excel sheet. Success is almost guaranteed in this case.

Using the Calendar Control View in the Unified Interface

Often, we get asked to show records in a calendar view. I personally used the JavaScript-based Full Calendar many times in the past to do that. If your requirement is just showing the records on a calendar with basic functionality then the Calendar control in the unified interface might be your answer.

In the classic interface, we used to have a calendar control on the entity that only works in the Phone and Tablet Layouts. This control basically allows us to view the records on a calendar instead of just showing them in a list.

Moving to the unified interface, the “Web” option is now available. To test that, I created a dummy event entity with Start date, End date and Description fields.

A custom Entity with Start date, end date and description fields.

Then from the controls section on the new entity (use the classical interface designer as this is not available yet on the new designer), add a calendar view, enable it for web and bind the start,end and description fields to the fields we just created above. Note that the description field will show on the calendar, you either can bind it to the name of the record or a custom description field if you want to show more information. Save and Publish your changes.

Add the calendar control and bind the values

Now when you go to view the events, instead of the classical view, you will see a nice calendar view.

The calendar control shows instead of the classical view.

If you like to go back to the normal View list, you can do that from the top right corner.

Business Rules for PowerApps Portals – v1

When it comes to customizing Dynamics 365, I don’t care how we do it, I care about enabling the customers to use the system easily after it gets delivered to them. This of course means if we can get things done by OOB configuration and customization wizards, then it is the way to go, the last option is to write code. One example is the use of Business Rules instead of client side scripting, for simple to medium needs, a business rule can save us (and the customer) from nasty JavaScript code and enable them to change it later without worry.

The same problem applies to the Portals side of Dynamics. I’ve never worked on a portal project where the OOB features satisfy the client needs. This means any small change like hiding a field or a section needs to be backed up by some Javascript that lives inside the Entity form or the Web Form Step. Even though the needed Javascript can be simple, not everyone is comfortable doing it specially if the Dynamics Admin is not a technical person and honestly, they don’t need to know Javascript.

I though of a configuration-based solution that I call Portal Business Rules. This solution doesn’t have a fancy designer like the Business Rules in Dynamics Forms, but it is configuration based and it is capable of producing/modifying Javascript without the need to write it yourself. This solution has many of the common functionalities that a project needs. That being said, and similar to how client side scripting is still needed on the Dynamics side even with the existence of Business Rules, complex needs will still require Javascript on the portal and the good news is that this complex Java script can coexist with my proposed solution.

The current functionality of the solution is limited to:

  1. Each rule is governed by a single IF/ELSE condition.
  2. The rule works with Entity forms and Web form steps.
  3. Each rule can have unlimited number of actions. Actions include Show/Hide fields. Disable/Enable Fields, Make fields Required/Not Required, Set Field Value, Prevent Past Date and Prevent Future Date (for Datetime fields), Show/Hide Sections, Show/Hide Tabs.
  4. A rule will parse the XML of the related form or tab and suggest the fields/sections/tabs to be used in the rule logic.
  5. For some of the field types (Option sets and two option sets), a suggested value table shows up for ease of use. So instead of figuring out the integer value of an option set field, they will be listed for the user to select from.
  6. The ability to use “In” and “Not In” Operators. For example you can say if an option set value is in “2^3^4” which means if the option set is either of these 3 values, then the condition will hold true.
  7. You can see the generated Java script directly in a special tab.
  8. The Generated Java script for all the rules gets injected into the Entity form or web form step Custom Java script field and it is decorated with special comments to make it clear that this is generated by the solution and not by hand.
  9. When a rule is deleted or drafted, its logic gets removed automatically from the corresponding entity form or web form step.
  10. Basic error handling is added so that when the operands has the wrong value format, an error will show up to tell the user to fix it.

Here is a quick video showing the installation steps:

Here is a simple rule creation demo that shows/hides a tab based on a two option set value:

Another demo of multi action rule, where the Job Title field is shown and becomes required if the Company Name field is populated:

Another demo of how an option set is used in a rule. How error handling works if the operand value is of wrong format.

And finally, the “In” Operator is one of the advanced operators. Here is an example of how we can populate a field if the condition falls into one of a predetermined list of values:

Of course, there are many other possible operations features that you want to check out if you install the solution. Manipulating section visibility, field states (enabled and disabled) and many more.

Many will notice that we can only have one condition in a single rule for now and I’m currently thinking on the best way to associate other conditions to a rule with either AND or OR logical operators between them, similar to how Dynamics 365 Business Rules behave.

To be fair, the best solution for this problem is not my proposed solution but is to make the Business rules that currently exist for Dynamics forms work on the Portal Forms as well, I can say that this solution needs to be done by Microsoft itself as there no much visibility on the Business Rules engine for us,developers. Based on my knowledge, the business rules in Dynamics seem to be built using the Windows Workflow Foundation (from looking at their XAML).

In summary, the problem I’m trying to solve is reducing the need for code further, similar to how Business Rules reduced the need for client side scripting on the Dynamics 365 side. If code is still needed, then my solution and custom code can still live together.

Please refer to my repository on Github for installation steps. Feedback is really appreciated.

NOTE: For the Java script functions that I call in the back-end, I use this existing library on GitHub developed by Aung Khaing .

Update October 16, 2019

During some search, I found out that a company called North52 has a similar solution that was done before and they inject Javascript the same way I do but of course with a nicer interface :). I have a bit more functionality provided. Here is the Link

Slim Solution, a Plugin for XrmToolbox

Recently, I was given many unmanaged Dynamics 365 solutions to maintain. The thing I hate about solution is that they can become messy with a click of a button when you add an existing component to it. By Messy I mean there are a lot of things that are not needed but added to the solution. If you add the Case entity, many developers do add the whole case entity even though one or 2 fields are needed to be modified, the rest of the information is confusing and it is not very straight forward to clean that up.

The problem gets worse when you want to build a managed solution out of the unmanaged one. The managed solution needs to be very clear on what it does to what parts of the system. If the managed solution changes one case field, adds a new relationship then those the only changes that need to exist in the solution.

While cleaning out the solutions manually, by looking at their managed exports (you can know if a component has changed by looking at its managed XML export). I decided to write a very small XrmToolbox plugin that helps me in that. I wrote the plugin sometime ago but it took a while to validate it as XrmToolbox has a new lengthy validation process.

The basic idea of the plugin is that it checks all the managed entities that are added into the solution, find which field, form or view is either customized or added to that managed entity. Then it will tell you which components need to be in the solution so that you can remove the rest.

The plugin is called SlimSolution and it is currently available for download in the XrmToolbox. It is still lacking many of the features I want such as checking other component types but I will be adding those in the near future.

An example of the usage of this plugin would be something like this. You create a solution (or other developers do) and you want to clean it up from the unwanted components. As an example, I created the below solution that has:

  1. A custom unmanaged entity
  2. 3 managed entities in which I did add all components to them and metadata.
  3. I modified/added some fields in the account and KB article entities and did nothing to the Agreement entity.

What I want is to clean up this solution by only keeping the managed entities that have been customized.

When you open the SlimSolution plugin, you first load the solutions and hit Check Solution. A somewhat nice summary appears on the right with some details and suggestions on what are the changes that need to stay in those managed entities. Of course and as I mentioned above, the plugin only checks for Forms, Views and Fields for now and gives you the list of components that need to stay in the solution.

You can see that the unmanaged entity is not mentioned because the plugin assumes unmanaged entities are created to be included in the solution (not always the case though but this is the assumption here). You will see information about what needs to stay from the account and KB entities because they were modified. You don’t see anything for the agreement entity which means that whole entity can be removed from the solution.

In addition to the above, if the solution contains some inactive processes/BPFs/Dialogs, it will alert you to remove them from the solution. The code for this plugin is constructed in away that makes adding component validators an easy task which I will do in the near future as I have some other validator ideas in mind.

Tips on Dynamics 365 Plugin Code validation for AppSource Submissions

Not long ago, I was involved in submitting a really complex application built on top of Dynamics 365 to Microsoft AppSource. The application contains a lot of plugins and code activities that perform some complex tasks and automation. The team faced some issues that I think are worth sharing with others to save your time if you you are working on such a submission.

Microsoft, provides us with tools such as the Solution Checker that validates your solution including your plugin and web resource code. The problem is, that’s not all. When you submit an application to the AppSource team, it goes through a rigorous manual and automatic checks using tools that are not publicly available to us, developers. If there are issues in your code, your submission will be rejected with explanation on what to fix and with the list of issues ordered based on their priorities. To pass the submission, all critical and high priority issues need to be fixed (if you can convince the AppSource team that somethings needs to be done a certain way and can’t be done another way, they will mostly make an exception).

After the first submission, the app got rejected with tons of things to modify/fix (even after running the solution checker on all the solutions). To be honest, the documents they sent were scary (1000+) pages with explanations on the issues. After looking at the issue list, it turned out that 90% of the critical/high priority issues are related to writing thread safe plugins. Luckily, the fix was very easy for those issues but it cost us around 2 weeks of time to do another submission and get it verified again. The following are the most common critical issues.

Variables May Cause Threading Issues

A plugin in Dynamics, is a simple class that implements the IPlugin interface, and thus, has a single Execute method as a minimum. Almost always, you need to create the organization service, the tracing service, the context and maybe other object. A bare bone plugin that builds, will look something like this:

public class SomePlugin : IPlugin
{
    public void Execute(IServiceProvider serviceProvider)
    {
        throw new NotImplementedException();
    }
}

A useful plugin, will have extra objects created so that we can communicate with the Dynamics organization,

public class SomePlugin: IPlugin {
 // Obtain the tracing service
 ITracingService tracingService = null;
 IPluginExecutionContext context = null;
 // Obtain the execution context from the service provider.  

 public void Execute(IServiceProvider serviceProvider) {
  tracingService =
   (ITracingService) serviceProvider.GetService(typeof(ITracingService));

  context = (IPluginExecutionContext)
  serviceProvider.GetService(typeof(IPluginExecutionContext));
 }
}

Now what’s wrong with the above plugin code? In a normal .NET application, this is a normal thing to do, but in a Dynamics plugin, it is not. To understand why, we need to understand how plugins get executed on our behalf behind he scenes. When a plugin runs for the first time (because of some trigger), most of the plugin global variables get cached, this happens when the constructor of the plugin is first executed. This means, in the next run, the same tracing service and context “may” be shared with the next run. This applies on any variable you define outside your function as a global variable in your plugin class. Ultimately, this causes threading issues (multiple runs of the same plugin instance compete for the same cached variable) and you may end up with extremely difficult-to-debug errors and unexplained deadlocks. The fix for the above, is very simple, just create your variables locally in the execute function, so each run of the plugin executes its own set of local variables.

public class SomePlugin: IPlugin {
 public void Execute(IServiceProvider serviceProvider) {
 ITracingService  tracingService =
   (ITracingService) serviceProvider.GetService(typeof(ITracingService));

  IPluginExecutionContext context = (IPluginExecutionContext)
  serviceProvider.GetService(typeof(IPluginExecutionContext));
 }
}

This by default means, that any helper function in your plugin should get what it needs from its parameters and not from global variables. Assume you have a function that needs the tracing service, and this function get’s called from the Execute method, pass the tracing service that was created in the execute method to that function and don’t make it a global object.

public class SomePlugin: IPlugin {
 public void Execute(IServiceProvider serviceProvider) {
 ITracingService  tracingService =
   (ITracingService) serviceProvider.GetService(typeof(ITracingService));

  IPluginExecutionContext context = (IPluginExecutionContext)
  serviceProvider.GetService(typeof(IPluginExecutionContext));
// do work here
HelperFunction(tracingService,1,2,"string");
 }

private void HelperFunction(ITracingService tracingService, int param1, int param2, string param3)
{
//use tracing service here
}
}

On the other hand, anything that is read only (config string, some constant number) is safe to stay as a global class member.

Plugins That Trigger on Any Change

This problem is more common. The filtering attributes of a plugin, are a way to limit when that plugin executes. Try to have as few as possible of those filtering attributes, don’t specify all of them. At that time I was involved in that submission, the solution checker wasn’t able to detect such problem but it may have improved now.

Image result for filtering attributes

Plugins That Update the Record Or Retrieve Attributes Again

This is also a common issue, when a plugin is triggered on an update of an entity record, it is really a bad idea to issue another update request to the same record again. An example of this can be the need to update fieldX based on the value of fieldY. When the plugin triggers on fieldY change, you issue an service.Update(entity) with the new value of fieldX. This implicates the performance of the whole organization and even worse, it can cause an infinite loop if the filtering attributes are not set properly. Another, bad use case is to issue a retrieve attributes query for the same record when pre-images and post images can be used to remedy that.

To be clear, sometimes, there is no way around issuing another retrieve inside the plugin or sending a self-update request, we had some of those cases and we were able to convince the AppSource team that our way was the only way.

Slow Plugins

As a general rule of thumb, your plugin should be slim and does a very small thing and does it fast. Plugins have some upper limit on the time they can run within and your plugin should never exceed that time (or not even half of it). When your plugin does exceed the time allocated for it, it is time for redesigning it.

Conclusion

While those issues have simple fixes in general, they can cause slowness and unexplained errors and a rejection from AppSource. Even if you are not submitting anything to AppSource, make sure that you set some ground rules for the developers working on the same code base on how to write good plugins. More on plugins best practices can be found here.

What this blog is about?

Digitally transforming your business is a daunting task. It requires revamping the whole organization processes and systems to meet the never ending demands of today’s digital world. We all face problems with this kind of transformation, and since many of those problems are technical, we would love to help!

We are a group of professionals working with the latest technologies in the Business Intelligence, Business Applications, Cloud Computing, Data Science, Machine Learning and Software Development fields. Our goal is to help the community by providing solutions to problems we face in our day to day jobs that we think other people may face too.