Category Archive Uncategorised

ByRob Peledie

Can you really use Azure SQL and add results to a Marketing List?

Simple answer…… yes.

Watch the video for a walkthrough.

Dont forget to Like and Subscribe please 🙂

ByRob Peledie

Competition Time!

October 1st marks the start of our competition! To win a pair of these awesome Beats Earbuds, just follow the instructions below

Subscribe to our YouTube Channel

Watch a video, and leave a comment

Thats it…. thats all you need to do.

Please make sure your subscriptions are public so I can see who’s won!

Draw will take place October 31st

ByRob Peledie

New 5 Minute Friday Video Series

So Starting last week, I have begun a series of 5 minute videos on tech subjects. I have deliberately made the decision to keep them to around 5 minutes (not that easy!) so they can remain snappy, but at the same time, not put a massive load on my already busy schedule. Theyre basically going to be quick how to videos, on subjects that I have had issues with, or learned.

First one is up! please take a look, and if youre so inclined, Subscribe and Like.

Thanks

ByRob Peledie

1 Year of Working From Home

So, give or take a few days, many of us have been almost exclusively working from home for the last year. I know some organisations have allowed a staggered return, with minimal personnel in at the same time, but for the most part, many who work in the world of technology have been remote.

Prior to all this madness, not everyone had the opportunity to work remotely. Many organisations still felt that if you weren’t literally a bum on a seat from 9 – 5, then you couldn’t possibly be working. I had manager some years ago that didn’t agree with working from home as he said “if it was me (him) working from home, I’d be feet up watching the TV”….. well apparently he judged everyone by his own standards.

I’ve been fortunate enough over the last 4 years or so to run my own business and contracted, so the choice to work remotely has been more in my hands. Previously I worked at OVO energy, and they had an awesome view on this subject.

But the reality is, working from home occasionally is very different from working from home every day. Again, I’m very fortunate to have a separate Office cabin, so to some extent, it still feels like ‘Going to work’, but for many, working from home has been – working from the lounge, working from the spare bedroom, so I would imagine challenging at times.

Added to the challenges, is the lack of personal interaction. Yes we’re all dab hands at Teams, Slack and Zoom now, but there is still a gap. I know working in tech, and development specifically often meant it was good to chat things over with your team… bounce ideas around. Whichever way you slice and dice it, it’s not the same doing it over a video call.

On the other side of this argument is the subject of productivity. Personally, I find I am definitely more productive when not in an open office environment. Theres less distraction for a start. When I had a small team of developers, I would often get people coming up to ask them questions or just chat. You could almost guarantee that 5 minute chat lost 20 minutes of productivity, and if it disturbed the whole team, then……. well you can see the problem. So theres definitely less of that.

So what does the future hold for working from home? Well it seems (trying to give a cloud a silver lining) that for some organisations reluctant to allow people to work remotely, it’s been a blinding revelation. It’s very difficult to say “Working from home doesn’t work for us” when plainly we’ve all had to do it for. the last year! I really hope that for some it opens up this opportunity, that while it comes with challenges, definitely will give people a better work life balance. Cutting down on that commute. Cutting down on your carbon footprint! better productivity.

Whether you’re in the all at home camp or hybrid home/office camp, I really hope you get a good balance that works for you for however long this challenge goes on for.

p.s. these two numb nuts have helped

ByRob Peledie

Well……That was 2020

Well that was a challenging year! Thats probably a massive understatement, and Ive been very fortunate, that even though I faced some early challenges as a small consultancy, we have kept working on projects throughout the year, and (at the moment) 2021 looks ok.

I will just say at this point, that I also know many consultants and contractors that have struggled with projects and contracts, and I really hope 2021 is better for you all.

Thinking back though, it’s been a learning curve for many of us. Working from home with all its challenges. New ways of communicating (with the phrase of the year going to “You’re still on mute”). The challenge of not really seeing anyone, and only interacting virtually (I’m not a massive people person, but going from 80% onsite to 100% offsite has been a challenge even for me).

So what does 2021 look like? Well…. if we’re honest, we don’t know. It may improve…..as we sit here today on the last day of 2020, there is some hope on one side of the coin in the vaccines being rolled out….. on the other side, we do have many parts of the UK going in to Tier 4 lockdown…… so who knows.

So while we cant make plans in the same way we may have done in past years, I still think the turning of a year is a good point to reassess, reevaluate and plan for progress and forward movement……..You can always improve on a personal level, even if other things are out of your control.

So how about these 5 to get started….. maybe you have your own:

  1. Learn a new skill
  2. Teach it forward
  3. Do something that takes you out of your comfort zone
  4. Read more books
  5. Get healthier
ByRob Peledie

Query Dynamics 365 / CDS Data With SQL

So fairly recently Microsoft announced the ability (in preview) to query CDs data with SQL – as opposed to Fetchxml as we have been used to with online.

I will write up a fuller discussion of this, once I have had a proper play, but there were some issues with setting this up, so I put together a short (ish) video to walk through the steps.

ByRob Peledie

Consuming an Open API and JSON in Power Apps – 101

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!)

ByRob Peledie

Common Data Model Vs Common Data Service

I thought I would put together a short explanation of Microsoft’s Common Data X conventions, and specifically what the meaning and differences are between CDM and CDS.

One of my drivers for this is that I didn’t fully understand it myself, or at least I find it difficult to articulate the definitions, and there’s surprisingly little about the connection between the terms….

So let’s start off with the “official” definitions of both

Common Data Model


The Common Data Model is a declarative specification, and definition of standard entities that represent commonly used concepts and activities across business and productivity applications, and is being extended to observational and analytical data as well. CDM provides well-defined, modular, and extensible business entities such as Account, Business Unit, Case, Contact, Lead, Opportunity, and Product, as well as interactions with vendors, workers, and customers, such as activities and service level agreements. Anyone can build on and extend CDM definitions to capture additional business-specific ideas.

Common Data Service


Common Data Service lets you securely store and manage data that’s used by business applications. Data within Common Data Service is stored within a set of entities. An entity is a set of records used to store data, similar to how a table stores data within a database. Common Data Service includes a base set of standard entities that cover typical scenarios, but you can also create custom entities specific to your organization and populate them with data using Power Query. App makers can then use PowerApps to build rich applications using this data.

There we go…… thats clear Isn’t it?………….

Still doesn’t feel like a clear explanation, so let’s try again…..

I think the clue to understanding this is in the last word of both expressions – Model and Service

A Model provides a framework, or architecture that standardises something….. so for example you might use the expression 
“the project became a model for other schemes” to infer that other projects will be based on this. 

So in this context, the Common Data Model is a standardisation of data concepts.

On the Microsoft GitHub page for CDM, you can find the poster below, which I think really explains the concept


The Common Data Model standard defines a common language for business entities covering, over time, the full range of business processes across sales, services, marketing, operations, finance, talent, and commerce and for the Customer, People, and Product entities at the core of a company’s business processes. The goal of CDM is to enable data and application interoperability spanning multiple channels, service implementations, and vendors. CDM provides self-describing data (structurally and semantically), enabling applications to easily read and understand the data.

I think that starts to focus our understanding a little better.

Now, if you really want to dig in to the Schema, and get a good understanding, then you can use the CDM Entity Navigator to drill in to it

So…….. How does CDS fit in to this? Well as with CDM, let’s look at the last word – Service.

If we view the CDM as the standard of entity schema, then the CDS is the mechanism for enabling you to build that data.

To put its simply……if you were to spin up a new environment of CDS here, then it would build it based on the CDM schema, and supply you with some of those standard Entities and relationships defined in the CDM Schema, but the actual data etc would be provisioned or managed by the Service…CDS.

Hopefully the above has helped to explain the difference between CDM and CDS and how they fit in to the landscape.

ByRob Peledie

Dynamics 365 User Access Report with FetchXml and Power BI

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:

  1. Need to report on Licensed Users and When they Last Accessed Dynamics
  2. 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.

So the first thing I needed was some fetch to give me the last logged in (or Access in Audit entity terms) for users.  I got the following from www.mscrmsolution.com but could have hand crafted in FetchXML Builder in XrmToolbox

<fetch aggregate='true' >
    <entity name='audit' >
    <attribute name='createdon' alias='LastAccessTime' aggregate='max' />
    <filter>
        <condition attribute='operation' operator='eq' value='4' />
        <condition entityname='su' attribute='isdisabled' operator='eq' value='0' />
    </filter>
    <link-entity name='systemuser' from='systemuserid' to='objectid' alias='su' >
        <attribute name='systemuserid' alias='SystemUserId' groupby='true' />
        <attribute name='domainname' alias='DomainName' groupby='true' />
        <attribute name='fullname' alias='FullName' groupby='true' />
    </link-entity>
    </entity>
    </fetch>

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)

Firstly open up your Power BI desktop, and choose Get Data –> Web

When this opens choose the Advanced option

So there a few steps here to get the connection working:

  1. The Service Root URL that you would otherwise use in Power BI https://MYCRMORG.api.crm.dynamics.com/api/data/v9.1/
  2. The PLURAL name of the entity schemaname follow by ?fetchXml=
    Example: audits?fetchXml=
  3. The FetchXML above, encoded as a URL (You can get this encoded at 
    https://www.freeformatter.com/url-encoder.html )
  4. Type in “Prefer” – it is not option you can select
  5. Exactly type: odata.include-annotations=”OData.Community.Display.V1.FormattedValue” (Important that you type in the Quote marks to avoid formatting issues)

Click OK

Open up the Query Editor

Click on “To Table” in the convert area, then click on the expand button (two arrows pointing away from each other)

And there you have it…..A live power BI report showing users access.

I’m not going to go in to how to “Pretty” the report up, I’ll leave that to you! 

The process above would be the same for any FetchXml query, and if you don’t need to hand craft the Fetch, then you can start with an Advanced Find, and export it out.

ByRob Peledie

Learn More With….. Microsoft Learn!

I’m pretty well Microsoft through and through. However a few years ago I had a detour in to the depths of the realm know as Salesforce (It’s fine, I’m ok now)…..

Seriously though, There are a few things that Salesforce do quite well, and one of the things I thought they did well was Trailhead. Trailhead was an online, free learning platform which has a gamification feel about it, and allowed users to acquire the skills needed to be anything from a good user to a Salesforce Dev.

I always thought it was a great concept and executed really well, and something Microsoft could take note of.

Well…….

A few years forward and we have Microsoft Learn!

Now I have to say, the look and feel is very similar to Trailhead, but that’s not a bad thing, after all, Salesforce did it well.

So what can you learn? Well there’s lots of Learning paths you can take including Azure, Dynamics, Power BI and more, but as I’m well and truly entrenched in the Powerapps/Dynamics space, this is the Path I chose, and there really is some great modules.

The modules are so well put together, and really informative. If you’re going through one of the Azure modules, you even get a Sandbox instance right in the browser to work through the lesson

So give it a go…. Learn something new today.