DataFlow – Merge Queries (aka Join Tables)

A long while Back Carina Claesson wrote about joining data with DataFlows. I had created a dataflow with 2 tables and used a specialized “behind the scenes” connecting several “CRM Keys” together. It was a little buggy, so I thought I would give it a try to follow her blog: Combining information from multiple data sources with Power Platform Dataflows – Carina M. Claesson (carinaclaesson.com)

Scenario

In our system for billing, we have the following structure. A debitor (aka Account), can potentially have lots of projects connected. The only way to connect data in DataFlow is via KEY’s in the Dynamics configuration. In our case we have a key “AccountVAT”. This ID is a unique organization number used in Norway.

Both tables know AccountID, but only the Account table holds the AccountVAT number. As you see on the image above, the Project doesn’t know what AccountVAT to connect to. Lets fix that.

DataFlow

So I load up my 2 tables in dataflow. If you wonder how this is done, check my other posts on DataFlow.

Select the MERGE QUERIES option

Here we match the 2 tables based on AccountID. As you see here there are lots of options for matching, but now you also get a nice visual for matching status at the bottom.

Next step is choosing what data to “join” into the projects table.

So I select the AccountVAT number as the field to join.

As you see in the picture below, the Debitor.Foretaksnr is now visible. This column is now joined from the Debitor table to the Project table.

The selected row below is a special field. You see that the Debitor.foretaksnr is a field in another table, and you see the destination field being special. The destination field is of type Lookup, and is expecting “AccountNumber” as matching.

The result is projects record linked to the correct account in CRM 👍💪

Dynamics 365 Storage Changes coming your way

A while back I posted my worries about storage and the DB being eaten up by system tables that we as consultants can’t do anything about.

Have you checked you storage lately?

I created an Idea in the hope that we could come together as a community and make a change, and they have listened!:)

https://experience.dynamics.com/ideas/idea/?ideaid=2d9d1f2f-e56c-ea11-b698-0003ff68dcfc

I applaud Microsoft for listening and moving in the right direction🙏🙌👍, but they seem to still be missing the point of SAAS for Dynamics 🤷‍♂️.

Some argue:

Think of Dynamics like a phone with storage. The OS takes up parts of the storage and you can freely use the rest of the storage.

I argue:

The whole point of SAAS is that I am paying for a service. I am not buying any hardware and installing something from scratch. I am a part of a larger shared solution, where I pay for what I use. Either include the system tables in the price, or reduce the storage included in Dynamics. Either way it is only reasonable for a customer in SAAS to pay for what they produce of data.

Thank you

Thank you for all the votes and the attention that this received, because it only goes to show that Microsoft does actually listen if we come together. In the future I hope we use our voices more often, because the number of votes on these issues are nothing compared to the number of consultants out there. We need to come together in numbers 🙂

Dynamics Relevance Search just got relevant 🙌

MAAAAAASIVE update in the search experience for Dynamics, and I am extremely excited about it. The global search has received a major face-lift and is now looking really good. I personally never used the old relevance search, because I thought it looked bad and was confusing for the end customer.

OLD Search

My customers personally fond this search confusing when the database started to grow. I therefore always told the users to chose categorized search instead.

This way it was easier for the user to find the Accound/Contact/Oppty they were looking for.

New Search experience ✨🎉👍

The new search places the search bar in the middle on top, just like many other office applications. A fairly intuitive placement for the global search, and the results look awesome!!!

Suggested search results on first click
Image from Microsoft Docs!!
Legend for new search experience
Image from Microsoft Docs!!
Suggested search results when you enter search queary
Image from Microsoft Docs!!

Read all about it at the Microsoft Docs Site 🙂

https://docs.microsoft.com/en-us/powerapps/user/relevance-search

Activating the experience

https://admin.powerplatform.microsoft.com/

Open settings

Activate both the Relevance search and the new search experience

Dynamics HR + CDS

This is not the average Dynamics CRM post, but I was challenged by Malin Martnes to see if we could integrate the competency part of of Dynamics HR with Customer Engagement. The reason we wanted to look into the matter was because we thought it would be really easy!!! Turns out we were wrong.. hehe 😂

The first thing we did was figuring out what tables were accessible in the CDS integration. At the time of reading the blog, the number of entities was not a lot. The ones that I needed were not there. The complete list of tables that write directly to CDS can be found here:
https://docs.microsoft.com/en-us/dynamics365/human-resources/hr-developer-entities

A lot of fields available, but just not the ones regarding competency.

What to do?!?!? Docs to the rescue !!

Thanks to a link from DOC’s I learned that Finance and Operations has a link to export data. You can choose whatever dataset you want, and export it to file. This has actually nothing to do with HR, but is a feature from Finance and Operations.

https://docs.microsoft.com/en-us/dynamics365/human-resources/hr-admin-integration-recurring-data-export

So I followed the blog but I used Flow instead of Logic Apps.

First start off by creating an export function in HR. Whenever this is called, it will answer your call with a file.

Export Workers data project

The flow looks like the logic app in structure, but I do a little extra in the end to extract the .zip file to excel.

The first hard part was actually finding the URL for HR. If you ask me it’s quite hidden. I had to have some help from Malin to figure out what my URL to the environment was.

The “Export Workers” has to be identical to the name of the export package. Then you have to decide what the name of the file I wanted to export was.

{
"definitionGroupId":"Export Workers",
"packageName":"talent_package.zip",
"executionId":"",
"reExecute":false,
"legalEntityId":"USMF"
}

The delay is there because the service can sometimes use a little time with the response. Just let it work:)

This part of the flow was just to see if .zip file had been completed. When it was complete, we could get the actual package.

At this point I had absolutely no idea what I was doing, but it was working. The body of the HTTP GET function returned a .zip file. This I could create directly in a OneDrive connector.

The last step was then to extract the file from the .zip, and voila. I now had an automated Excel export from Dynamics HR.

For the last part I could simply connect a DataFlow for importing to Dynamics:)

Was there even a point to this?

Well.. Yes, and no.. The positive thing about this was learning that Finance and Operations has an export function that I could use for extremely simple integrations. This I might be able to use at some later timer.

Later Malin told me that they had released the Virtual entities for Dynamics HR and CDS. You can read more about these here:
https://appsource.microsoft.com/en-us/product/dynamics-365/mscrm.d365hr_virtual_entity?tab=Overview
and
https://query.prod.cms.rt.microsoft.com/cms/api/am/binary/RE4HfPD

I don’t know enough about the virtual entities yet, but I will have a go at that soon to see how it works with using the HR data within Dynamics.

The end goal is still to match competencies from HR to Field Service Dynamics 👍

Dynamics 365 – Rich text editor

Thanks to my good friend Rasmus he pointed out that wave 2 is announcing a PCF component for rich text editing. I had to try it out see what it actually was.

After adding the multiple lines field to the form, I added a few examples. Regular Text, Color Text, link to a site, and image via Copy/Paste

Definitely cool to see that we now can store Ritch Text with images in CRM. First copy/paste images to Emails, and then copy/paste images to regular forms. Getting more and more flexible every day!:)

How is it stored?

First off I thought it might store the data of image directly in the multiple lines of text as Base64, but to my surprise it doesn’t.

Is it storing the image in Azure Blob storage? Would love to learn more about this 🙂

<div data-wrapper="true" style="font-size:14px;font-family:'Segoe UI','Helvetica Neue',sans-serif;"> 

<div>Regular text</div>

<div><span style="background-color:#27ae60;">Color Text</span></div>

<div><a href="https://crmkeeper.com">Link</a></div>

<div>&nbsp;</div>

<div>Image</div> <div><img height="131" loading="lazy" src="/api/data/v9.0/msdyn_richtextfiles(23cdb641-d10c-eb11-a813-000d3ab395a9)/msdyn_imageblob/$value?size=full" width="157"></div> </div>

Microsoft MVP ❤

After 13 years of Dynamics 365 (CRM) I can finally say that I am a Microsoft MVP🎉🎊🎈

I had no idea this would mean so much to me, but it really really does!! The amount of work behind this title is just overwhelming, making me respect the existing MVP’s even more. I am so proud to be a part of a unique community where sharing the knowledge is the most important thing you do. A community where you are awarded with praise for lifting others up and making them better.

This is not by any means a solo effort, and is only possible because other people have given of their free time to make me understand how I can contribute.

Malin Martnes
While working together at Point Taken you taught me all I needed to know regarding platform and exposure. You were the first one that pushed me into speaking, and you have been pushing me ever since. Thank you!

Mark Christie
Thank you for pointing me in the right direction when I needed help, and opening new doors for me. I am also glad to have you as a friend.

Chris Huntingford
Thank you for being the most inspiring personality I have ever met. After our first encounter in Oslo, you made me believe that anything was possible. You lift everyone spirits up, and make everyone feel that they are part of something special.

Mark Smith
I attended many of your cohorts, and you provided me with lots of great tips for creating my own personal journey. You taught me the value of making people feel good. Something I try to practice every day!

Marius Agur
While working at Microsoft building the partner channel, Marius approached me wanting to start the Norwegian community. He put in the hard work and hours, and created what we today call our Power Platform User Group. Marius didn’t care about personal gains, but wanted to build community. Everyone that has been on the stage in Norway (me included) can thank him for this.

Project Oakdale (CDS Lite) – Exploring Data Migration

I will keep calling it CDS Lite in the smallest hope that this one day might get the name CDS Lite. It just makes perfect sense to give it a name like this. Allright, enough about the hope for a better naming future…

I know the system is only in preview and many things will change in the next months, but I thought I would give it a go at importing data from CDS to CDS Lite. I had no idea if this was possible, but I was curious to see how it would react.

At the moment there is no connecting to the database through normal tools I have had before

  1. XRM Toolbox
  2. SSIS – KingsWaySoft
  3. Excel Import

Every time I try to connect I get the response saying that I have to use an Oauth method to connect.

PS: Notice how Microsoft has put “LITE” in the error message? It’s a small hope, but it’s a hope 😂

So the only way I found out how to insert data in the Preview was via flow

Flow to the rescue

I am pretty sure that the current doc recommends using Flow for data to CDS Lite (Oakdale), but I am sure this will change in the future somehow. How else are you going to fill 2 million records (estimates from docs on the 2 gig DB size). Flow is not intended for data migration, but I had to use the tools I had available at the time 🙂

First I imported 5694 accounts to my CDS database (top row headings)

The CDS Lite (Oakdale) was empty for accounts, and only included the following columns.

The flow was a little tricky. I actually had to create the flow from the CDS Lite (Oakdale) environment. It did not work otherwise. Current environment connector was fine for writing to CDS – Lite (Oakdale).

The flow will only do 512 records if you don’t open up the paging. Go to settings of the connector and increase the threshold.

I started the flow and patiently waited.

27 minutes later the accounts had been migrated with a warning. I could only find 1 error in the migration, so I consider that a success. All of my accounts were now over to the CDS Lite (Oakdale).

What next

This is all just a beginning of a longer journey for the CDS Lite (Oakdale). Everything is in preview, so there are many things that will change once this is released, and a few years from now. I just wanted to see how to get data in to the system. The reason why might be more clear in a later post where I describe what I see in a future customer scenario for CDS and CDS Lite.

Dynamics 365 Customer Service in 1 hour or less ⏰

Did you know that it is possible to setup a fully functional Customer Service installation in less than one hour?

I am proud to present all 3 parts
1. The community solution – Download
2. The Video for installation
3. The Video for demonstrating the product

I have put a lot of hours into this configuration, because I wanted other consultants to see how nice and easy Customer Service could be delivered. I have been using countless hours every time I wanted to show a new demo, so I finally put in the extra work to make it complete. This will hopefully save you a LOT of work next time you want to demo Customer Service, or deliver a good solution to your customer.

1. Download

Go get my free community solution for Customer Service.

https://github.com/thomassandsor/CustomerService

2. Install the solution

See how install the solution within one hour

3. Demo the solution

This is a showcase of how I would demo the solution. Find your own pitch and make yourself comfortable with it. Just make sure you have good demo data!! Good Luck 😀

New Email Signature in preview. Is it really an upgrade? 🤷‍♂️

In the preview for release wave 2020 2 Microsoft is moving the signature to UI. While doing this, they have the possibility to brush up on some old tech. Almost every piece of the old Dynamics moved over has had been given at least a small upgrade, of not a total overhaul. If you want to read about my take on signature in Dynamics, head on over here.

Limitations of the old signature:
1. Personal signature only. Could not define a signature for the organization or group.
2. No images could be added

So what has changed?

Old

The old signature did not have a rich editor, and limited us to primitive text editing. Exactly like the old email editor.

If you tried to enter a picture, you would get an error message. For this example, I just wrote the text “no picture possible”.

The signature would be added to the email as expected if it was defined as default signature, but again with little formatting.

New

The new signature is available in the new UI

I added a picture of the blue color, just to demonstrate that there can be a picture in the signature.

Opening a new email will automatically add the signature as expected, but now with the picture included. 👍

Well?

Does the signature support images?
YES, it finally does.

It is stored as a Base64, so no need to consider where the actual image is stored (notes, azure, blob etc etc). Migration from system A – B would work fine.

Can we use the signature multiple users?
Well… Yes, but No.. It seems every signature is meant to be personal, but you can search for other signatures if you want to. But in regards to automatic signature added to email, you need to create one for each user.

Thoughts 🤷‍♂️

As of now, I could only find the signature in the Customer Service Hub, and that confuses me a bit. Why not start off by adding it to the Sales Hub. If the signatures are personal, why not let the sales people use this first. In customer service I would have thought that a organization signature would make more sense.

While this is a great update to the last signature experience, I hope they are able to add global signatures in the future for groups of users. This would make the administrative experience a little better when you have hundreds of users.

If you read my article on signatures, I provide a different way of handling the signature experience. I try to centralize the management of the signature for faster changes. The good thing is that we have both solutions available, so pick and choose what suits your need.

Maybe in the next version we might see some structural changes to the way signatures are connected to users/groups.