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

Business Process Flow set Pipeline Phase

I was never a fan of the Business Process Flow (BPF) because of the many flaws. Sometimes I didn’t think it was easy enough, and other times not advanced enough. It simply wasn’t what I needed.

In a recent project I decided to give it another go, because the customer wanted to continue with the BPF they had from their old CRM system.

Opening the PBF now I see that there is so much new features here that I haven’t seen before. I guess most of these features are due to the FLOW platform now taking over the BPF.

Challenge: Setting pipeline phase

I know you can report on the BPF entity, but that is more complex than reporting on the opportunity entity if you ask me.

Can I set the “Phase” field on the opportunity when navigating to the next step in the BPF? Let’s see!!

I wanted to set the Pipeline Phase with BPF

Config

Open the Business Process Flow from your solution and notice a button at the bottom right “Business rules for this stage’s entity”.

Create a new Business Rule for Opportunity “Oppty – Set Pipeline Phase”.

For the first condition we check if the BPF is in the stage named Prospect.

If true, set the Pipeline Phase to “1 – Qualify”.

Repeat for the second step!

The business rule should look something like this:

Result

Let’s get back to the Opportunity. We see that it default is active on the Qualify stage in the beginning of the sales process.

Click the next stage

And there you go!!

New Found Love

So what do I think of the BPF now? Honestly, I liked it. It might be a replacement for many other JavaScripts that I have been using the later years, and that is only a good thing. In this case I was able to skip JavaScript and therefore deliver a solution with a low technical footprint:)

CDS 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.

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

Dynamics 365 – Teams. Love at first SITE (pun intended)..

The Team

Hi everyone, meet Knut and Kjetil.

Knut is a salesperson at Point Taken. He just came across a HUGE deal that he has registered in Dynamics 365. This deal is so big that Knut will need the assistance of several resources to deliver. One of these resources is Kjetil, a SharePoint consultant.

The setup

Knut enters the details needed for the Dynamics 365 deal

Knowing that this opportunity is HUGE, Knut starts by creating a new channel in the Offers Team (name is “General” for demo purposes).

The connection

Knut has now setup the structure for collaboration, and is ready to connect Teams and Dynamics 365 together.
Well done KNUT!! 🙂

Add the Dynamics 365 connection for the tab in the channel

Knut chooses the correct opportunity record.

When Knut is done, he can see the newly created connection message in Teams. The above message indicates that Teams and Dynamics successfully connected.

The harmony begins

Kjetil begins right away with the PowerPoint they will need to win this deal. Kjetil does not have a license for Dynamics, so he creates the PowerPoint in Teams because this is the natural place for collaboration.

Knut can now choose to navigate Teams or Dynamcis 365, because the systems are working with the same document location.

Knut and Kjetil represent 2 different work processes, but are harmonizing well when referring to the ONE TRUTH document. Well done you to!!!

Dynamics and Teams in perfect harmony!

Dynamics 365 – Teams Configuration

The configuration of the client is pretty straight forward. A typical office product, and that is a good thing:) FYI, it does not install a solution file within Dynamics, and I like that a lot… Never been a fan of the overcrowding of solution files (Hint hint portals.. hehe).

Start på opening the system settings from Dynamics and locating the “Enable Dynamics and Teams integration”.

NB! Before starting, I would create a new Team and a new channel just for the sake of simplicity. Also create a new record in CRM that you want to connect to.

It took me about 3 minutes before I received my complete message, so be patient!

Open teams and locate the App Store within teams and search Dynamics. You will find it on the “…” menu on the left side.

Add to the team you created. In my case “Tilbud” and then click Open

Next step is connecting it to the channel in the team, and hit “Set up”.

Now it will ask you for an entity to connect to, so I am going to choose the Opportunity I created before I started the configuration “Teams Opportunity”.

If it all went well, you will now see a new tab in your channel with the name of the record. You also see a message that the record is connected.

The result

Dynamics and Teams working together