PowerPlatform Saturday Oslo 2019 – Speaker

So Power Platform Oslo was a unique experience for me, since it was the first time I was on stage at a community event. Marius Agur challenged me to create a presentation that would showcase my view of Dynamics from a business perspective. I am not the most technical person, so I try to create solutions based on simplicity for the end user within a “do it yourself approach”.

3 ways the Power Platform can increase user adoption

I was using our own company as example for this demo, and demonstrating the issues we have had when migrating from HubSpot to Dynamics 365. A journey with lots of bumps in the road, and long nights to figure out the best solution.

1. Connecting Flow to API

Part One showed how we can connect flow to an API and receive real time data from sources like Bisnode, Proff.no or in my case BRREG. I will blog about how to do this soon.

2. Creating a PowerApp for simplicity

Part 2 was showing how you can create a powerapp to simplify not only the user input, but also create a bridge between those who don’t like Dynamics CRM, but accept using a PowerApp, because it is new and fun. Blog about this will also come soon.

3. Integrating to onpremise data source

The last step was showing how we could integrate our CDS/CRM solutions to an onpremise ERP solution without a single line of code. Read more about hit HERE

People to thank

Chris Huntingford: @TattooedCRMGuy
I would never have even thought of doing community or personal exposure if it were not for your exceptional outgoing personality motivating me when you where in Oslo. I know it sounds odd, but I wanted to be like you, and spread excitement and joy to others. Somehow you made something in my brain say “why not” create a blog and speak my mind! MASSIVE thanks..

Megan Walker: @MeganVWalker
Getting to know you and understanding how you use the tools in regards of blog, wordpress, youtube has been very important. You were also so kind to be my first interview subject 🙂

Mark Smith: @nz365guy
Thank you for hosting the 90 day Mentoring Challenge. Even though you were speaking to the masses, you make it personal to participants. I have also shared many conversations with you that mean a lot to me. Thank you for guidance in som many different levels in regards to creating a profile. I might not be the fastest at getting everything done, but I will get there.

Malin Donoso Martnes: @MalinMartnes
Thank you for being persistent in your work. It inspires others to try:) You gave me the final push to submit my talk, and I don’t regret it!

CDS / Dataverse List Records Filter Query using Flow

This week I needed to use the List Records function, and I realized that I had no idea how to use the filters. Thank you Jonas Rapp for creating the FetchXML Builder!! The function “Flow List parameters” saved my day:)

Simple filters

Let’s begin with the simple filters where I get a contact with the last name of Sandsør

Test your search result with the Execute button so see that anything is actually returned. Then open the Flow List Parameters

The tool converts the Fetch XML, and magically gives the correct filter to add in our FLOW query. It can’t get much simpler than that!!

Lookups

Lookups act a little bit different with the syntax, as lookups always to. This got me quite confused before finding this tool, because I was not getting match to my result.

I am searching for contacts with a given GUID. In my case I didn’t know what the GUID was, so I randomly generated a GUID for the formula. In FLOW I substituted the GUID part of query with a dynamic variable.

Filter Query with lookups, you need to add “_” as seen above. When working with lookup you won’t get at match without the “_LookupField_Value”.

Filter linked entity

The last filter is a little more complex, and might not get used due to some limitations of Odata (Must match on unique ID for related).

In this scenario I wanted to locate all contacts with last name “Sandsør” where the regarding accountID = GUID.

Choose the main entity on top, and add “link-entity”

Make sure you have the correct relationship here. Some Lookups support more than one entity, and therefore you make sure you have the correct one.

Again we find the magic with the “Flow List Parameters“.

In this scenario we also get Expand Query result that we need to copy/paste.

Apply to Each

Once you have figured out what filter to use, you can select the “Apply to Each” function, and add custom logic in here.

PowerApps Currency Formatting examples

So far I feel that currency formatting in PowerApps seems to be a little troublesome. Sure everything would be great if we all sold in $ and our offers were all huge! All of the examples out there are based on this. I needed to represent values at 1 NOK and up. In Norway we don’t use “,” for separating thousands.

I am sure there are simpler ways of doing this, and I am hoping someone can fill me inn:)

For US formatting I found this one:

Text(CurrencyField,"[$-en-US]$###,###")

For UK formatting I found this one:

Text(Value(Parent.Default),"[$-en-US]£#,###.00")

For me I need to format PowerApps currency NOK. I have tried manipulating all of the above with no luck at all. Have no idea what I am doing wrong, but that doesn’t matter. I finally found one that worked for me

NOK PowerApps Currency Formatting:

Text(ThisItem.'Est. Revenue',"[$-nb-NO]## ### kr", "nb-NO")

None of this is really making much sense to me. If you have more information about this, please do share!!:)

PowerApps + CSS

I know the title be a little misleading. PowerApps doesn’t support CSS as of now (maybe sometime in future), but you can create a CSS light for simple tings while we await the custom Themes for PowerApps.

I find it extremely annoying when creating a large app and you need to customize visuals. If you make a change to one “screen” you have to make the same change over and over again.

In this example I declared a few variables in the beginning of the PowerApp, and one of these is the button color “RED”.

As you can see, my button color will be read when I choose to give them this color.

So at some point I decide that I want the color to be the same as my heading, and all I have to change is the color code in the app “onload”.

The result is matching buttons without having to manually change each color setting!:)

NB! after changes in the “onload”, you have to refresh the browser to see the effect.

Using Flow to copy Document Location

One confusing obstacle with a new CRM system, is the connection between Dynamics CRM and SharePoint. As a consultant I often have to demonstrate what is really happening behind the scenes to justify why this isn’t straight forward. We have to tell the customer about different folders, different entities etc, while they simply don’t care. They just want it to work together.

One particularly interesting point is when they move between entities. Why can you work with documents in the Lead, and when you navigate over to Opportunity, you no longer see your sales documents? Technically I understand why, but from a functional perspective I understand the customer.

Recently I ran into this scenario when implementing Field Service. Sales produced documents what were important throughout the whole cycle Sales -> WorkOrder -> Assett. Navigating between the entities takes to long, so the challenged me to find a solution.

FLOW TO THE RESCUE ❤

When the opportunity was done, we needed to create 1 or more WO in Field Service. Important factor was to maintain all the work done in Opportunity with the documents, so the Field Service Technicians could see everything in one entity (WO).

Challenge: When I create a new Work Order from Opportunity, I need to copy the document location from the originating Opportunity.

Flow begins with a simple trigger. When a WO is created.

Then I check to see if the related Opportunity contains data. This is just to make sure that the WO actually is created from a Opportunity.

Then I needed to get the document location regarding the Opportunity. As you can see here I am only returning 1. There won’t be any more document locations on Opportunity the way they are using it now. If you wonder how to filter, check my other post LIST CDS RECORDS IN FLOW

This is where I create a new Document location, and set the Work Order as regarding. Flow will automatically add “FOR EACH” to loop through. In my case it doesn’t matter, because I only have 1 document location from Opportunity.

What the user sees

This is a new Opportuinty

The opportunity has documents related to it.

I then convert the opportunity to Work Order via the button in ribbon. I get a message when this is complete.

I open the document location of Work Order, and VIOLA, the same documents.

Personally I love finding quick wins like this that mean a lot for simplicity. Any CRM application will be complex for the end user, so these parts help:)

PowerApps license change opening up for a 10$ CRM system?

Power Platform is maturing rapidly, and users are really loving the new platform for citizen development. The only natural thing to do for Microsoft was to alter the licensing model now that they see how users are working with the platform.

Normally I am the first one to hate on all changes, but this time I am not sure I hate it. I am actually seeing some opportunities here that I didn’t see before.

https://powerapps.microsoft.com/en-us/blog/new-licensing-options-for-powerapps-and-flow/ <– Read about the statement here.

I am seeing this from a Dynamics perspective, and not Office. There are 2 different ways of viewing this change.

From what I read, we can now setup a 10$ pr user pr month and create a sales app with both Model Driven and Canvas AND Portal. Within the app we are also capable to run as many flows as we want without the need for a FLOW plan.

You might still need one Enterprise or Proessional license in the bottom for the entities to be installed on the CDS, but after that you can create your own simple apps.

NB!
I am sure that Microsoft will introduce some other restrictions later on to separate the apps (Sales, Customer Service etc), but currently this is not the case.

Anyone share their ideas about this change? 🙂

Composite fields in PCF?

Ohhhh how I miss the Composite fields in dynamics<3

It was introduced in 2013, but was never given much love. It was one of those changes the customers loved, but always wanted to configure. This resulted in many strange javascripts that I personally refused to use. Now that the new UI is being forced upon us, the field will disappear it seems, and that is too bad. The new UI is fine, but it is not as efficient with screen space yet, fields like this actually make a lot of sense.

https://community.dynamics.com/crm/b/cosmanscrmjourney/posts/crm-composite-fields
https://community.dynamics.com/crm/b/cosmanscrmjourney/posts/crm-composite-fields

What about PCF?

First of you need to see that PCF controls can do. Free community examples have been release here: https://pcf.gallery/ – by Guido Preite

So can PCF controls be the answer to this? I have no idea, but would bee cool if someone with developer experience could answer me:)

Interview – Julian Sharp

Julian Sharp was recently in Norway to teach the App in A Day. I was one of the lucky few that got a spot in his class. Sure I have been in Dynamics since 2007 and should know it all, but that is the only once certainty. I will never know it all, and there is always something to learn from a person that has a different perspective on things.

Thank you Julian for the great class, and the advice during the interview! See you again soon in Oslo for the PowerSaturday August 24th.

Follow Julian in the following channels
Blog
Twitter

On-Premise Data Gateway integration

My state of mind at this moment is hard to describe, but Malin Martnes sendt me this link from Urban dictionary 😂

This is Part 2 of the Gateway SETUP

The final settings

This is where we left off in the last post:

We need to do one config in CRM/CDS before we continue. For the integration to work we need to create a KEY for it to match on. The source doesn’t know of GUID, so I create a key for “Account Number”. In Norway we are lucky that this number is unique and applies to all organization’s.

Open the entity you are integration to (Account for me), and create a new field called KEY. I am choosing to use the “Account Number” field here as my Unique KEY. Remember to publish changes!!

First thing I do is limit the number of accounts while testing.

Then I remove all the blank fields in the KEY, to make it equal to the user input in Dynamics. You don’t have to do this, but chose to for simplicity.

In the next step you choose the entity to connect to in CDS/CRM, and map the field. I am choosing to only map “Name” and “Account Number” during the test.

And the magic continues. Here you can setup how often you wan this awesome sync to happen. Our data is fairly static, so once a week is fine:)

Wait until the query is done, and check out the newly created/updated Accounts in Dynamics. This is just a gamechanger for me.

Pricing (a little uncertain)

As far as I can see/understand, this is using Azure Message bus, and it prices everything to messages pr hour. Below is a snipp from https://azure.microsoft.com/en-us/pricing/details/service-bus/ In my case this would practically be “free”.

Basic
Operations$0.05 per million operations
Standard
Base charge 1$0.0135/hour
First 13M ops/month Included
Next 87M ops (13M–100M ops)/month $0.80 per million operations
Next 2,400M ops (100M–2,500M ops)/month $0.50 per million operations
Over 2,500M ops/month $0.20 per million operations

On-Premise Data Gateway setup

So the On-Premise data Gateway itself might not be the most awesome thing in history, but in combination with the data integration of PowerApps it is just incredible! Carina wrote about this earlier, but I had to see it for myself 🙂

My example is based on the need to integrate my On-Prem ERP (SQL) server with Dynamics 365 online (aka PowerApps).

This method does require that you have username/password credentials to a view in SQL that will allow you to read data. After the setup, Part 2 – Final Finnish.

I need data from On-premise to Online

I needed to integrate my ERP system (On-Premise) to D365 in the cloud. There are several ways to complete this normally with code, SSIS, Scribe etc. I wanted to learn what the PowerPlatform was capable of.

I am not a developer, so I am always seeking for solutions considered No-Code, Low-Code. Integrations was something I always had to involve developers to complete.

Until now!!

Installing software

Install latest Onprem Gateway software – Be sure to update this from time to time due to function/feature/security updates from Microsoft.

The software should be installed on a server, because of the need for 24/7 uptime. While testing, you can easily install the software on your personal computer as long as it is in the same network as the SQL database you are trying to connect to.

Open the software, and set it up. I chose to use my login credentials for this action. These credentials where also the ones that were “creators” in Dynamics.

Remember the Gateway Name and Password

When this is done you should find the Gateway in your PowerApps.
NB!!! It will only install under the Default instance for now!!

Check connection with PowerApps

Last step is to open up your browser to PowerApps and see if we can retrieve the data. Open PowerApps

Make sure you navigate to the Dynamics Production environment

Then you open a new integration project

From here you connect to the On-Prem SQL DataGateway.
Don’t worry, the credentials and IP are not real here:)

So the important thing on the next step is to use the credentials for your SQL server. These credentials only need to be read from a database. This means that you might have to ask someone to create read credentials for your database.

Choose the tables you want to sync. Debitor is Accounts in our ERP system.

If you are lucky, you will see the following result!! You are now one step closer to actually complete a NO-Code integration with an onprem SQL server.

HOW COOL IS THAT!!!!!

Part 2 Final Integration