If you didn’t catch the first two parts of this series, you can do that here and here.  In this part, we’ll get a little more technical and use Microsoft Flow to do some pretty cool things. 

Remember when we talked about the size and quality of the images we take with our PowerApp and store as the entity image? When saved as the Entity Image for a CDS/D365 item, the image loses quality and is no longer good for an advertisement photo.  This is done automatically and as far as I can tell, the high-res image is gone once this conversion takes place (someone please correct me if I’m wrong on that!).  On the flip side of that, it doesn’t make a whole lot of sense to put all this tech together only to have my end users be required to take two pictures of an item, one for hi-res and one for low-res.  We don’t want to store a high-res in a relational database for 10,000 plus items because the database could bloat immensely.

Microsoft Flow and SharePoint to the rescue!  

PRO TIP:  Dynamics 365 will crop and resize the image before saving it as the entity image.  All entity images are displayed in a 144 x 144 pixel square.  You can read more about this here.  Make sure to save/retain your original image files.  We’re going to stick ours in a SharePoint Picture Gallery App.

Objective 

Create a Microsoft Flow that handles… 

  • Pulling the original image off the Dynamics record and storing it in SharePoint. 
  • Setting the patch image to the Entity Image for the Dynamics record 
  • Create an advertisement list item for the patch 
  • Save the URLs for the ad and image back to the patch record 

Create the Flow 

We’re going to write this Flow so that it’s triggered by a Note record being created. 

 Flow screenshot with Create from blank highlighted

  • On the next page, click “Search hundreds of connectors and triggers” at the bottom of the page. 
  • Select Dynamics 365 on the All tab for connectors and triggers. 
  • Select the “When a record is created” trigger. 

 Dynamics 365 is highlighted

  • Set the properties for Organization Name and Entity Name.  Entity Name should be “Notes”. 
  • Save the Flow and give it a name. 

Verifying a Few Things 

  • Add a new step and select the Condition item. 
  • The Condition should check to see if the Note has an attachment. We do this using the “Is Document” field.  

 Condition Control is highlighted 

  • In the “Yes” side of the conditional we want to check if the Object Type is a Patch (ogs_patch in this case).  

At this point, if the Flow has made it through both conditionals with a “Yes”, we know we are dealing with a new Note record that has an Attachment and belongs to a Patch record.   

Update the Patch Record 

Now we want to update the batch record’s Entity Image field with the attachment.  First we need to get a handle on the Patch record.  We’ll do that by adding an Action to the Yes branch of our new Conditional. 

  • Add a Dynamics 365 Update a Record Action.
  • Set the Organization Name, Entity Name, and Record identifier accordingly.  For our Patch Record identifier, we’ll use the Regarding field in the Dynamic content window. 

 

  • Click on Show advanced options and find the Picture of Patch field. 
  • For the Picture of Patch field we need to get the document body of the attachment and convert it from Base-64 encoding to binary.  We do this using the “Expression” area again.  Use the “base64ToBinary” function to convert the document body like so. 

 

  • Save your work!  I can’t tell you how many times I had to retype that function. 

Create Our SharePoint Items & Clean-up 

Now that we’ve updated our entity image with the uploaded patch picture we want to do a couple of things, but not necessarily in sequence.  This is where we’ll use a parallel branch in our Flow.   

Dealing with a Parallel Branch 

  • Under the last Update a Record action, add a Conditional.  After adding this Conditional hover over the line between the Update action and the new conditional.  You should see a plus sign that you can hover over and select “Add a parallel branch.” 



  • Select this and add a Compose action.  You may need to search for the Compose action. 

 

PRO TIP:  With Modern Sites in SharePoint, we now have three solid options for displaying images in SharePoint.  The Modern Document Library allows viewing as tiles and thumbnails within a document library, the Picture Library which has often been the place to store images prior to the Modern Document Library, and then we can simply just display an image, or images, on a page directly.

Saving the Attachment as an Image in SharePoint

  • Let’s deal with Compose branch first.  Our compose will have the same function as our Picture of Patch did above for the Input field.  base64ToBinary(triggerBody()?[documentbody’]) 
  • After the Compose, we’ll add a Create File Action for SharePoint and use the name from our Patch record as the name for our image in SharePoint.  I’m using a Picture Gallery App in SharePoint and for now, only using the .JPG file type.  The File Content should use the Output from our Compose Action. 

 

Delete the Note

  • Finally, we want to delete that Note from Dynamics (and the Common Data Service) so that the image attachment is no longer taking up space in our Common Data Service.  Add a Dynamics Delete a Record Action after the SharePoint Create file action.  Set the Organization Name, Entity Name, and use the Dynamics content for Note as the Item identifier.

 

Creating Our Advertisement

Let’s jump back to the new Conditional we added after the Update a record Action where we set the entity image. 

  • Set the conditional to check for the Generate Advertisement field being set to true. 
  • If this is true, add a SharePoint Create Item Action and let’s set some values.  What we’re doing here is creating a new SharePoint List Item that will contain some starter HTML for a Patch advertisement. 
  • Save our work! 

 

 

Updating Our Patch Record With Our URLs From SharePoint

  • Under the SharePoint Create Item Action for creating the Ad, AND after the SharePoint Create file action for creating the picture in the Picture Gallery, we’re going to add Dynamics Update record Actions that will be identical with one difference. 
  • The Organization Name, Entity Name, Record Identifier (set to Dynamic Content “Regarding”) should be the same. 
  • On the Ad side, the Update record should set the SharePoint Ad for Patch field to “Link to Item”. 

 

  • On the image side, the Update record should set the SharePoint Image for Patch to the “Path” 

 

Seeing It In Action 

Of course, I’ve been saving my work so let’s go ahead and give this a whirl. 

  • At the top right of your Flow you’ll see a Test button.  We’re going to click that and select “I’ll perform the trigger action.” 
  • To make this more interesting, I’m going to run this from SharePoint! I’ll update a patch and kickoff my Flow from the embedded PowerApps Canvas App on my SharePoint home page. 

 

  • I select the patch, then I click the edit button (pencil icon at the top right). 
  • Notice the Attach file link and the Generate Advertisement switch.  We’ll use the first for our image and the second for generating our ad item in SharePoint. 

 

  • Finally, I click the checkmark at the top right to save my changes.  This kicks off our Flow in less than a minute, and when we navigate back over to the Flow we can see that it completed successfully. 

 Verifying the flow

  • I’ll hop back over to SharePoint to make sure that my ad was created and my entity image was set.  I’ll also make sure the high-quality image made it to the SharePoint Picture Library and the Note was deleted from the Patch record in Dynamics.  I also want to make sure the URLs for the ad and image in SharePoint were set back to the Patch record. 

verifying in SharePoint Verifying in SharePoint image

One last thing: When we store the image in a SharePoint Picture Gallery App we can retain the dimensions, size, and quality of the original image, unlike when storing the image as a Dynamics 365 entity image.  Check out the properties in the next screen shot and compare that to the properties on the SharePoint page in the same screen shot.   


Comparing image file sizes

Conclusion 

I hope you are enjoying this series and continue to tune in as the solution for our dad’s beloved patch collection grows.  I constantly see updates and upgrades to the Power Platform so I know Microsoft is working hard on making it even better. 

If you didn’t catch the first part of this series, you can read that here.  In this part, things should get a little more interesting as we set up a PowerApp and our SharePoint site.

Objectives

  1. Create SharePoint Online Team Site.
  2. Create a PowerApp for team members to collect data with.
  3. Embed this PowerApp into a SharePoint page so we can do more work in a single environment.

Creating Our SharePoint Team Site

Using our existing SharePoint Online instance, I’m going to create a new Team site off of the root site collection.

  1. From the home page of the root site collection, click the “Create site” button.

Create site screenshot

2. Select Team site on the panel that opens.

Create a Site screen with Team Site hightlighted3. Fill in the information on the next page and click Next.

Team Site information input page

4. Add any additional users and click Finish.

Add additional users screenshot

If all went well, you should be redirected to the new site.

Screenshot of new Team Site

Creating the PowerApp

To be clear, we are going to create a “canvas” PowerApp as opposed to a model-driven PowerApp.  To get this going I’ll navigate to my PowerApps environment and create a new app.

Screenshot of PowerApps environment

    1. From your PowerApps home page, click Create.
    2. On this page you can see various templates for making both model-driven and canvas apps. We’re going to use the “Start from data” Canvas app.Screenshot with Start From Data option highlighted
    3. On the next page we see multiple choices for apps that start with our data. I’m going to use the Common Data Service here.Screenshot with Common Data Service hightlighted
    4. With my Common Data Service account instance selected under “Connections,” I scroll to find and select the “Patches” table, then click “Connect.”

Screenshot of Connections

PRO TIP:  If you get to this point and you still don’t see your data, make sure to check the “Environment” at the top right of the window.  You may not have the correct environment selected where your data is stored.  You can read more about Environments in PowerApps here.
Screenshot with Environments highlighted

        1. Once PowerApps is done creating the new app, the app designer will appear.

      Screenshot of App Designer

Let’s pause and look at this screen for a minute.  On the right, we have our properties and some other items, in the middle we have our design canvas, and on the left we have our Screens Explorer.  In our Screens Explorer we see three screens already created for us: the browse, detail, and edit screens.

If you look under each of those screens you’ll see a primary user interface object that is collapsed (meaning it has content/child nodes underneath it), along with some other user interface elements that usually provide some other functionality or a label.  When you select an element in the Screens Explorer it will also be selected on the design canvas.  In the previous screen shot, the Search Icon is selected under the Browse Screen 1 item.  Consequently, the search hourglass on the design canvas is also selected.

We don’t want to change this up much, but I think we can all agree that it would be a lot more helpful to have something other than the created-on date and item id as our main fields for each row.

PRO TIP:  PowerApps provides us with a super fast way of spinning up apps by automatically building things into our apps like navigation and search controls.  Be careful about changing or removing these unless you plan to replace them with a control of your own that provides the same functionality.  Oftentimes if you remove one of these you render a portion of the app unusable or difficult to access.  To see what a given user interface item does, select it in the designer and check out the “OnSelect” Action in the Advanced tab on the right.

Screenshot with OnSelect option highlighted

      1. Select the bolded date field on the row item on the design canvas and make sure the Advanced tab is selected on the right.
      2. In the Data section under the Advanced tab, we can use the Text field to change what we want displayed in each of these user interface elements. I’m going to change the first to name, the second to city, and the last one to state.Screenshot of text field

Once we’re done tweaking the browser display we’ll want to work on the detail and edit forms.  These work a bit differently.

      1. Select the Detail Form under the Detail Screen node. In the right pane under Properties, select the Fields link that indicates the number of fields currently selected.
      2. Check any fields you would like that aren’t currently on the form. Similarly uncheck any fields the system put on the form that you want to remove.  You can also reorder the fields to your liking.

After a little bit of tweaking to both our detail and edit forms we’re ready to publish this PowerApp.  You can preview the app with the “Play” arrow icon near the top right, or just switch over to your File menu to wrap things up.

      1. Click the File menu and under App Settings give your app a name and feel free to play around with the icon and background color. There are other settings you can explore too but for now we’ll just cover the basics.
      2. Click “Save” to save your app to your gallery.

Screenshot of gallery

In this next screen shot I’ve captured our three screens from left to right: display, detail, and edit. Screenshot of app screens.
You might be wondering what the Generate Advertisement switch is for on the edit screen.  We’ll use that to toggle that specific functionality later in our Flow.

Finally, if you click on the ellipsis for the new app in our PowerApps app gallery, you can click “Details” and get additional helpful information for the app such as the app URL.  This will come in handy for what we do next.

Here are some screen shots from my mobile phone as I helped gather inventory for this massive side project!

Screenshots of app data

Embedding the PowerApp in SharePoint

As mentioned, we don’t want to find ourselves jumping back and forth between application instances for managing inventory.  Thankfully we’re able to embed our PowerApp into the SharePoint page we want so we can do a lot of our common work from that one screen.

      1. Open the SharePoint site we created earlier and edit the home page. I’ve tweaked mine so we only have the Documents Library web part and some empty columns.
      2. Click the plus button in the right column to add a new web part. Find the Microsoft PowerApps web part and select it.Screenshot
      3. Paste the link from your PowerApp into the “App web link or ID” field in the right pane that opens. The app should come up in the new web part.Screenshot
      4. Publish the page.

We can now use the same PowerApp that everyone else will be using on their mobile devices, right here in SharePoint.

Screenshot of PowerApp in SharePoint

Tune in next time, when we’ll use Microsoft Flow to move some data around, automatically generate advertisements, and notify team members of important events.

Part One: Identify, Define, Build, Migrate

An assortment of fire department patchesMy dad passed away in 2015, leaving behind an extensive collection of fire trucks, patches, and other fire department (FD) memorabilia.  Before he passed, he gave us instructions to sell them and some direction on what to do with the money. After a few years of not really wanting to deal with it, my family decided to make a project out of it.  My mom, sister, wife, two daughters, and I are working our way through thousands of patches, hundreds of fire trucks, and who knows how many pendants and other trinket like items, all while working full-time jobs (school for the kiddos) and from different locations.

Dad was great about logging his patches into a Microsoft Access database, but not so good about taking pictures of them, and even worse at logging his fire trucks and other items.  The objective and high-level steps for this project were quickly identified.

The Objective

  1. Help my mom liquidate my dad’s enormous fire department memorabilia collection.

The High-Level Steps

  1. Identify the technologies to be used. Easy!
    1. Microsoft Dynamics 365 & Common Data Service – our foundation.
    2. Microsoft PowerApps – mobile app for inventory capture.
    3. Microsoft Flow – move data and attachments around, auto-create ads.
    4. Microsoft SharePoint – store ads, images. Keep large files out of CDS.
  2. Complete a first-cut of the data schema and migrate the patches data from the Microsoft Access database.
  3. Configure a software solution for the family to use so we can all capture data to a single database. Solution must be user friendly!
  4. Configure processes that streamline the creation of advertisements and other data processing.
  5. Start capturing data and creating ads!

The Players

Not everyone in an organization has the same skill level and this will certainly lead to some challenges.  With that in mind, let’s look at the players involved in our project.

  1. Mom – Low technical skill – Capable of using anything “Excel-like” to capture data.
  2. Sister – Low-to-Medium – Arguably more advanced than mom, works on a Mac. Enough said.
  3. Wife – Medium – Works around Excel with ease, understands what I do from a high level.
  4. Kids – Low-to-Medium – two daughters, ages 12 and 10. Both are geniuses on any touch device but have no clue how to work around Excel.
  5. Me – High – developer and technology enthusiast!

I’ve spent the better part of my career as a .Net developer working in SharePoint and Dynamics, among other things, so it was easy for me to decide on a path forward.  Let’s get rolling!

Configure Data Schema and Migrate Microsoft Access Data

Just so no one thinks I’m lying here for the sake of this blog, let’s see what my dad was working with back in the day.  Yes, he was ND alum.

Screenshot of patch entry form in Microsoft AccessPatch data in Microsoft Access

Side note: You see that column named “Patch Locator” highlighted in that last screen shot?  My dad kept his patches in old-school photo albums that he then stored in boxes.  This ‘locator’ field was his way of finding the patch once a box was full and stored away.  Genius dad!

As you can see defining the schema for patches was pretty much done.  If we run into anything along the way, we can certainly add it.

  1. In Dynamics I created an un-managed solution named “Fire Department Items Solution” and added two custom entities, “Patch” and “Fire Truck.”
  2. I added all the fields my dad had in his Access database, and then I made sure that the out of box field “EntityImage” was available for displaying an image of the patch.

PRO TIP:  Dynamics 365 only allows you to have one image field on an entity and it is not configured out of the box.  To use this field, create a new field on your entity and use the data type “Image”.  This will automatically set the name of your field to “EntityImage” and the image you set there will be used as your entity image at the top of the entity form.

Screenshot of PowerAppsPowerApps details

  1. Before we save and publish, we need to enable Notes functionality for our entities. To do this select the entity from the left pane in the solution explorer, then make sure the “Notes (includes attachments)” checkbox is selected.

PRO TIP:  When you save an image to the EntityImage filed it loses a lot of its quality.  Because we are using this data for inventory, including creating ads, we don’t want to lose the quality of our images.  For this reason, we will use the attachments collection for our entity to capture the actual high-quality image.  We will then use Microsoft Flow to take that image and store it as the EntityImage (which will lose quality) but also store the high-quality version in a SharePoint library.

PowerApps note functionality

  1. Finally, be sure to publish your customizations.

Migrating the Data

Now it’s time to migrate the data.  Since this was such a simple schema, I opted to use the out-of-box data import functionality that Dynamics 365 provides.  With that said, however, there are a few different ways to accomplish this migration. For me it was easy to simply export the Microsoft Access database to Excel, then use that file to import into Dynamics 365.

    1. Export your data into an Excel file from Microsoft Access.
      1. Export your data into an Excel file from Microsoft Access.
    2. In Excel you’ll want to Save a Copy and save it as a CSV file.
      Save a copy as a CSV file
    3. Open the Patch View in Dynamics and use the out-of-box Import from Excel functionality to load our data.

3. Open the Patch View in Dynamics and use the out-of-box Import from Excel functionality

    1. Choose the CSV file we just created when we saved the copy in Excel.

Choose your CSV file

    1. On this next screen, let’s click the button to Review our Field Mappings.

Review Field Mappings

    1. Here you’ll see some of my fields are mapped and some aren’t. Let’s get those shored up before we proceed.

Resolve mapped items

    1. Now that I’ve resolved all the field mappings, you’ll see we have green check marks across the board and we’re ready to import. Click the Finish Import button and you’re off.

Finish Import button

    1. You can check out the progress of the import by navigating to Settings à Data Management à

View Import progress

Summary & Next Steps

Let’s look at what we’ve done here.  On the surface it would appear we’ve simply gone into Dynamics 365 and configured a couple of entities.  But as we know, Dynamics 365 v9 was built on the Common Data Service (CDS) and that means our Dynamics data is now available to any other application that can connect to the CDS.  Why is this important for this project you might ask?  That answer will become clear in the next part of this blog.  For now, here are some screen shots on how things look now that we have our patch data migrated.

A look at the imported data

Keep in mind, come end of January 2019 everyone will need to switch over to Microsoft’s Unified Interface and that’s what we’re using here for our patches.  This is an example of a model-driven PowerApp which we’ll discuss in our next entry to this blog.

If you log in to your PowerApps environment using the same credentials as your Dynamics 365 environment, you should see your entities and the data migrated in this environment too.  Remember, once it’s in Dynamics, it’s available through the CDS.

A view of the migrated data

One thing to note, if you have 10,000-plus records like I do for patches, CDS in the browser may freeze trying to display them all.  I would hope MS resolves this at some point so that it handles paging and displaying of data as gracefully as the D365 web client does.

Stay tuned for my next entry where we’ll set up our SharePoint Online site, create a simple canvas PowerApp for inventory management on our mobile devices, and then set up a Flow to help move some things around and automate the creation of our online advertisements.

Thanks for reading!

While it isn’t quite as good as having complete control of your CSS, Dynamics CRM (2015 Online Update 1, and On-Prem 2016) now offers a feature called Themes. Themes enable the organization to customize their CRM Web interface to some degree, although we still don’t have complete control of the styling.

There are plenty of good blogs on Dynamics CRM themes, but I’ve yet to find one that includes good tips on determining the hex values for the colors you need. This blog will help you determine these values, including using a color picker to pull a color’s hex value from an image. Read More…

Creating SharePoint Sites From a Dynamics CRM Plugin

With more and more companies adopting both Dynamics CRM and SharePoint into their corporate technology stacks, I’ve found myself integrating the two technologies more often than ever before. It’s quite cool. These are two extremely powerful systems and they both do what they are designed to do very well. I’m not going to go in-depth about what the differences are between these products…they are in totally different software “genres.” Apples and oranges. However, I want to point out one thing SharePoint gives you that many clients ask for again and again: Subsites.

Subsites are SharePoint sites that belong to a root SharePoint Site Collection. An example of this would be a corporate intranet SharePoint Site Collection (https://intranet) with SharePoint Subsites for each division in the corporation, e.g. Marketing (https://intranet/sites/marketing), Accounting (https://intranet/sites/accounting), etc. There are pros and cons to breaking these Subsites out into their own Site Collections (so they can have their own content database), but quite often you see this hierarchical approach.

Another very common use of SharePoint Subsites is for things like item level management, for instance a project. You may have a SharePoint Site Collection named Projects (https://projects), and you may require a new SharePoint Team Site for any new projects that come online so as to provide an environment where a team can collaborate on said project, and maintain any data or documents about the project in a central location. This can best be described with URLs —https://projects/sites/Project1, https://projects/sites/Project2 and so on.

So how does Dynamics CRM play into my scenario?  What if you’d like to leverage this same functionality for things like Accounts, Contacts or Leads?  Pick an Entity from CRM — for the purposes of this blog the Entity is somewhat arbitrary, but we’ll use the Account Entity so we have something to focus on. Every new Account I create may or may not require an environment where teams can collaborate and store data and documents for this new Account. Dynamics CRM gives us SharePoint Document Management out of the box. But what if I need more? What if my company maintains a site ‘template’ that can be used to create a new, standardized website (SharePoint Subsite) for any new Accounts that come online? A place where a team can collaborate and have the freedom to deal with unstructured data and content in a central location?

In this blog I’ll show you how to leverage the SharePoint Client Object Model in a Dynamics CRM Plugin to create a SharePoint Team Site for new Accounts that come online.

Read More…

Microsoft Dynamics CRM 2011 has come a long way from its predecessors in many areas.  For companies using both CRM 2011 and SharePoint 2010, the improvements can be appreciated even more since Dynamics CRM 2011 has native SharePoint 2010 document management capabilities right out of the box.

This functionality is apparent in a couple of different places in the CRM 2011 web interface.  First, there is a page under the Settings section for Document Management.  Second, some entities will have a Documents area available on their forms.  Please see the screen shot in Figure 1 below. (Click on any image to see full-size.)

Figure 1 - The Document Management page in Microsoft Dynamics 2011

For this write-up, I’ll be focusing on the Document Management page and the required steps for configuring access to SharePoint 2010 as well as installing the SharePoint List Component.

Read More…