Category Archive Dynamics 365

It’s been a while since I wrote anything too technical, and I’ve had a chance during this lockdown to brush up on some stuff I haven’t really played with in Power Apps, so thought I would look at something a little different….. connecting and consuming an Open API, and consuming the results in a Power App.

Before I get started, there is also a video to go with this article

There are plenty of open API’s you could use to have a play around, but I decided to use the one from CoinGecko, which gives lots of options for real time Cryptocurrency prices. You don’t need to sign up to it, and there’s no authentication required, which makes life easier if this is your first venture.

If you navigate to the CoinGecko API site, you’ll see the complete list of options, and get a chance to test out your requests right there. For example the first, and most basic is to ping the api.

So for the example we’re going to build, let’s keep it nice and simple. A Power App that retrieves the current prices for Bitcoin in 3 currencies, then allows you to choose your preferred currency, your holding, and show your live value.

Firstly lets create the Flow that gets the data from the CoinGecko API. Go to flow.microsoft.com and log in. Once logged in, go to your flows and click New –> Instant – from blank

You’ll then get the opportunity to choose your trigger, and in this case we want to choose PowerApps, as an action in our PowerApp will call the flow and API

Once you’ve clicked create, your flow will open, and the PowerApps trigger will be shown as the first step

Before we go any further, lets just step through the process that we’re going to do. This is the finished flow

So if we break it down in to simple steps……. The Power App calls the flow. this then uses the HTTP action to call the API. The JSON results are Parsed, and the Responses captured.

This then gives us the resulting data to be consumed in the Power App.

So let’s break it down. Firstly that HTTP Step. If I open up that step this is what you’ll see

So the Method we’re going to use here is GET – because we’re getting data! Seriously though, if you want to know more about these methods, then check out this.

So the URI is where we form our request, and in this case it’s a really simple request for the Price of Bitcoin in 3 currencies – USD, GBP and EUR.

Since theres no Authentication for this API, we can easily see if this request works by posting that URI in a web browser

So the results you see there are in a JSON format. We now need to Parse them, and break them down in to their parts so we can consume and use them in a readable format.

Here we add the Parse JSON action.

So firstly you’ll see that we want to pick up the Body as Content – that’s the payload or data we are retrieving. The Schema may seem a little daunting, but there’s a neat little trick here……. “Generate from sample”

In this, we can just paste in the returned data that we got before in our test with the URL, and the flow will just generate the schema for you!

Finally we just add the Response action, and you’ll see it’s almost identical to the Parse – just with the addition of the status code of 200 which effectively means OK. This then allows the response to be consumed in your Power App

So, that should be your flow created. Don’t forget to name it and save it. Next let’s create a simple Power App and consume that data.

Go to make.powerapps.com and create a new Canvas app from blank. The first thing we’ll do is add a button – for this simple example the button is going to call the flow which in turn calls the CoinGecko api. I’ve just renamed mine to “Update Prices” .

Now there’s a few things we want to do in order to grab those prices. In the formula box I have added the following

ClearCollect(getrates,GetCoinGecko.Run())

I’m not going to go into all the details of this but essentially we are creating a collection, calling it “getrates” and the data that feeding it is coming via the flow we previously created (Mine is named “GetCoinGecko”

If you want to check your flow is working, just check your collection

I have also added a few labels….. 3 to identify the currency, and 3 to hold the data we pull back

so far then we have a mechanism for getting the data from the API via flow, and triggered by the button. it’s being stored in the collection. We now have to show it in the label fields.

In the formula for the label for GBP I have added the following

First(getrates).bitcoin.gbp

This formula calls the first record in the collection and in this case the GBP value and displays it

Repeat for the USD and EUR data, and there you have it!

Have a play…… why not add a drop down and option to put your holding to show your value (I wish I had 100!)

You can start to see the countless use cases for this sort of flexibility. And when this is available for Canvas apps (September/October 2019) the potential for creating is boundless.

Now, to be 100% upfront and honest here, this isn’t a ‘drag n drop’ exercise. This is going to require you to have some development experience. There are some great articles and videos that might help you get started – and I’ll list them at the end.

There is also and awesome resource – The PCF Gallery. Here you’ll find some examples of what people are doing, and as is normal in this great community, it’s free to use and share.

Links

So a few days ago (Early June 2019), Microsoft started to roll out previews of the AI Builder in PowerApps.

What is AI Builder?

According to the Documentation (and it’s worth bearing in mind, that because this is in preview mode, the documentation may change), AI Builder is:

“A new Power Platform capability that allows you to easily automate processes and predict outcomes to help improve business performance. AI Builder is a turnkey solution that brings the power of Microsoft Artificial Intelligence through a point and click experience. Using AI Builder, you can add intelligence to your apps even if you have no coding or data science skills.”

As it stands right now, this is in preview, and you have to enable it in your PowerApps settings.

For this quick article I thought I would go through a really simple example of scanning a business card, and then creating a new Lead record in Dynamics 365.

Just worth saying at this point, that while the following example is simple, it does show a really good use of AI and Microsoft’s Cognitive services. OCR or Optical Character Recognition, has been around for many years, but in the example below, the AI is doing more than just converting an image in to text….. it’s then analysing that text and making a decision as to what it is…. so looking at a word, and deciding it is a first name as opposed to a last name or company name. Putting that sort of power in our hands is awesome, and will get better and better!

To start with, create a new Canvas app in PowerApps, and use the Phone layout. If you have enabled the AI Builder Preview, then you should see the options in the insert menu

AI Builder

Choose the Business Card Reader option. This will place the Business Card Reader component on the Screen

The Business Card Reader currently can extract the following if a Business Card is detected:

CleanedImage: The image after processing where the business card appears cropped and enhanced from the original image.

CompanyName: The company name in the business card, if found.

Department: The organization department found in the business card, if found.

Email: The contact email found in the business card if any.

FirstName: The contact first name in the business card, if found.

FullAddress: The contact full address in the business card, if found.

FullName: The contact full name in the business card, if found.

JobTitle: The contact job title in the business card, if found.

LastName: The contact last name in the business card, if found.

OriginalImage: The original image before processing.

Phone1: The first phone or fax number detected in the business card, if found.

Phone2: The second phone or fax number detected in the business card, if found.

Phone3: The third phone or fax number detected in the business card, if found.

Website: The website detected in the business card, if found.

I decided just to keep things nice and Simple for this demo, so just included the following fields that I would use to create a new Lead in Dynamics 365:

Company Name

First Name

Last Name

Email Address

I also included a text field that I would map over to the Description field in Dynamics (I’ll show you later how I configure the Microsoft Flow to concatenate the Company Name – Last name for the Topic on the Lead record.

The fields I added were Text Input fields, so If I needed to change anything on the captured Data I could.

The formula for each field just references the Business Card Reader Component Name and the field, so for Company Name it was

BusinessCardReader1.CompanyName

After you have completed these steps, Save and Publish your app. If you’ve followed all the steps above, then you should be able to open the App, Take a photo of a business card and after processing, retrieve the data in to the fields.

So the next stage is to grab that data from our app, and use it to create a new lead.

For this we need to trigger a flow from a button on our screen. I added a Button and called it “Submit to D365”

Once the Button is on the screen, it needs to trigger a flow, which we’ll create next. In the Action Tab of PowerApps, choose Flows, then click Create a new flow. This will open up the Flow designer, which should already have the connector to your PowerApp (Thus inheriting that the button will trigger it)

So in the image above, you’ll see that I have added the “Create a New Record” Action in the flow, and chosen the Org Name and Entity – Lead for this example.

What you’ll also notice is that In the right hand side, there are currently no fields available, but instead have the “Ask In PowerApps” option. This is so that the flow can grab the relevant fields from the App. So If I Choose the Last Name field in the Create a Record Activity then Ask in PowerApps, it will return a holding field for reference:

You’ll also see in the image above that I have concatenated some fields for the Topic.

Once you’ve done this….. Save your Flow and go back to the App.

In the App, highlight your Submit button, and use the following formula

I wanted to put together a solution where an external system could send a JSON payload through an Azure Service Bus Queue, and a new record created in Dynamics 365.

There are no doubt better, or at least different ways to approach a scenario like this, but I wanted to brush up on some skills, so thought it was a nice use case, and one I will be needing soon for integrating an external Oracle system.

So, basically this is the running order of steps:

Create Azure Service Bus Queue

Create Logic App

Test (Using Postman to send JSON messages)

So, nothing too complex, but just thinking about this sort of scenario a few years ago (not that many to be fair), would have required a fair amount more development skills, but pretty well all of the above can be achieved in a codeless manner. As I have said before, “Don’t use a Sledgehammer to crack a walnut”. I have also tried to add some links to useful references within this blog.

So firstly, setup an Azure Service bus, and make sure you note the Primary Connection string, Queue name, and primary key.

If you’re planning on using something like Postman to test, the you’ll need to create a SAS Key.

Open up a Cloud Shell and use the following to create a SAS Key.

You’ll need the Service Bus URI and the Queue Name, as well as the policy name and Key.

Microsoft Flow is a tool that can integrate cloud-based apps and services so they interact with each other seamlessly. According to Microsoft, this cloud-based tool improves efficiency and productivity by enabling virtually anyone in an organization to automate many tedious and time-consuming business tasks and processes without developer intervention.

Flic Buttons are a Smart Button that can be linked to a number of services. The button can be programmed to perform actions via an app or a hub, and can even trigger Microsoft Flows

I know with the Flic button a message with your coordinates can be sent, so I was interested to see if we could Parse that message through Microsoft Flow, run it through the What3Words API then action something with the results.

The resulting flow takes looks like this:

So lets run through it.

Theres a couple of things you need to do with the Flic button to set this up.

In the Flic App on your phone or other device, you need to setup your connection to Microsoft Flow, and its really important that you choose to send location data:

Once this is done, log in to Microsoft Flow and create a new flow from Blank. You will then be able to add a Flic button as a trigger – the first time you do this, you’ll need to add your Flic account as a connection. You’ll then get to choose which button and which action

Next add an HTTP action. If your connection to Flic is successful, then you’ll have the output from the button press as options to feed in to your HTTP (in this case it is to access the What3Words API ( this is assuming you have already signed up to the What3Words API here and have an API key.

So here we have added the What3Words URI, and as you’ll see added the output from the Flic button as the coordinates. You’ll also need to add your API key as shown in the URI after &Key= and in the Value field.

Once this is completed, the returning JSON needs to be Parsed, so add the Parse JSON action.

For the Schema, I used the “Use Sample Payload” option and the example on the What3Words Documentation here but just in case, here it is:

Ok, so that’s not the snappiest of titles, but essentially it’s my lazy way of covering a couple of topics in one blog post:

Need to report on Licensed Users and When they Last Accessed Dynamics

Showing that in a Power BI Report using FetchXML

It’s worth noting at this point, how fantastic the Dynamics 365, and generally the Microsoft community is. Most of what I did has already been done and blogged about for free. I’ve tried to reference everyone who’s blog posts helped me.

Couple of things worth noting here. Operation = 4 which is the Access record in the Audit (as opposed to Update, Create etc)

So at this point, I had the first piece done, as all I initially wanted was a way to see how long it was since users logged in. To get a quick view I just pasted the results in Excel and used a formula to calculate the days.

What I actually wanted to do was create a Power BI report that would connect to Dynamics, and use this FetchXml dynamically.

Ulrik Carlsson (CRMChartGuy) has a great post on this, and all credit to him for the following. (You may want to read his post in entirety, as I have summarized some bits)

I’ve been using Flow for quite a while, but recently started utilising Azure Logic Apps for some pieces of work. On one of the forums I frequent, someone asked the question “What’s the difference between Flow and Logic Apps”.

Well the quick and simple answer is that in a lot of ways, they’re the same thing……

However, that’s not the real answer or the complete picture. There are a few differences that might help you decide which to use in specific circumstances.

There’s a really good overview of the similarities and differences here, but it’s summed up nicely by this:

Microsoft Flow and Logic Apps are both designer-first integration services that can create workflows. Both services integrate with various SaaS and enterprise applications.Microsoft Flow is built on top of Logic Apps. They share the same workflow designer and the same connectors.Microsoft Flow empowers any office worker to perform simple integrations (for example, an approval process on a SharePoint Document Library) without going through developers or IT. Logic Apps can also enable advanced integrations (for example, B2B processes) where enterprise-level Azure DevOps and security practices are required. It’s typical for a business workflow to grow in complexity over time. Accordingly, you can start with a flow at first, and then convert it to a logic app as needed.

There’s loads of resources available to help get started, and decide which way to go. Here’s a couple of quick summaries

As I have mentioned before, Microsoft Learn has some great resources, and there is a really good Flow Learning Path to get you started. Nothing as yet on Learn for Logic Apps, but I’m sure there will be soon.

For me, when I looked at an integration with an external API, to be used across the whole enterprise, I decided to base it on Logic Apps. Whereas when I wanted a small solution to move attachments from Dynamics 365 to OneDrive (on my own companies Dynamics 365 instance) Flow was fine.

As we move to a more Serverless architecture, SaaS and PaaS solutions like Flow and Logic Apps are giving the power of integration and communication back in to the hands of the Power User, and not just the remit of the coder. The turn around and potential for Tech Debt is reduced (in my opinion), and is a very exciting step forward.

I had a situation recently that an instance (not one I managed I hasten to add!) had started to grow in storage, to the point that it was at nearly 200% capacity. I was asked to look at it. Turned out that Plugin Tracing had been switched on, and a plugin was throwing errors. Unfortunately there were now so many records in the Log file that it wouldn’t open (Usual generic unhelpful SQL Error).

Taking a quick look at organisation insights confirmed that this was consuming nearly 60gb of storage, and a quick record count (Using Record Counter in XrmToolBox) indication there was approaching 10,000,000 records.

Normally I would use the standard Bulk Delete option, however for a couple of reasons I didn’t think this was a viable option:

It would take too long

It kept falling over after 1000 deletes (probably due to the number of records, but I didn’t get to the bottom of it)

In the end I decided to go back to trusty old SSIS, and (again) the lovely KingswaySoft solution for Dynamics 365.