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 Power Apps – 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 Power App which we’ll discuss in our next entry to this blog.

If you log in to your Power Apps 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 Power App 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!

For those who are familiar with Microsoft’s popular Dashboard in a Day workshops for Power BI, App in a Day (AIAD) is very similar. The program is an all-day training designed to accelerate attendees’ Power Apps, Microsoft Flow, and CDS for Apps experience. The comprehensive hands-on workshop is led by a certified Microsoft Partner, in this case, your friends at Applied Information Sciences.

The training provides practical experience in a full-day of instructor lead App creation workshops. You learn how to build custom apps that run on mobile devices with which you can share inside your organization securely.

Inside the AIAD Workshop

Full house at the AIAD at Microsoft’s Reston MTC

Trainees at App in a Day

Read More…

I am pleased to announce my latest Pluralsight course on Power Apps (Well…such is the nature of change in the cloud that there has already been a name change since I submitted this course for publication, only a few weeks back. The aspect of Power Apps covered in my course is now referred to as Canvas Apps.)

This course is designed for developers (both citizen and professional developers) interested in a low-code approach for building mobile applications.

Here’s some background on Power Apps, if you haven’t had a chance to play with it yet:

Power Apps is a productive low-code development platform. It allows you to very quickly build business applications that can run inside a web browser, on a phone or a tablet. Power Apps includes a web-based IDE (Power Apps Studio, a set of built-in cross-platform controls), an Excel-like expression language that also includes imperative constructs like variables and loops, and over 130 connectors to talk to any number of data sources — including SQL Server, Office 365, Salesforce, Twitter, etc. You can also use custom connectors to talk to your domain-specific data source.

Beyond the controls, language expression and connectors, Power Apps provides ALM support in the form of app versioning, app publication to various app stores, swim-lanes for development environments, authentication and authorization (via Azure AD), RBAC controls, and security polices like data loss prevention (DLP).  All in all, the Power Apps service seeks to significantly lower the bar for building and distributing cross-platform mobile applications within your enterprise.

For a concrete example of our use of Power Apps, please read how we built a cross-platform event app in less than a week. Also please check out a recent episode of DotNetRocks where we talk about Power Apps.

Finally, as part of the latest spring update, Power Apps is combining with Dynamics 365 for Sales, Marketing, and Talent applications to offer an enterprise high-productivity application platform as a service (known as Microsoft Business Applications platform). What this means for Power Apps developers is that:

  1. They can now take advantage of server-side logic
  2. They have access to a data-centric way of building declarative apps, known as model-driven apps (in contrast to canvas apps, which are built by dragging and dropping controls to a canvas).

For more information on the spring update, please refer to this blog post by Frank Weigel.

I hope you will find this course useful. Please reach out to me via this blog or Twitter if you have any questions or comments.

Calling all SharePoint users! AIS is sponsoring this month’s Meetup for the Triangle SharePoint Users Group at our Durham, North Carolina office.

The session will start with an overview of SharePoint Custom Forms, which can be developed by Angular JS. We’ll review how to build repeating table information stored into parent/child SharePoint lists. You’ll watch a demo of custom dashboards using REST APIs to display data from multiple SharePoint lists, plus walkthroughs of real-world situations for SharePoint Apps and Power Apps.

Space is limited so RVSP here to claim your spot!

When: Thursday, February 15, 2018
5:45 p.m.
 to 8:00 p.m

Where: 4721 Emperor Blvd
Suite 350
Durham, NC 27703

RSVP today!

Join us tomorrow for a free webinar with AIS’ CTO and Microsoft MVP Vishwas Lele on Microsoft Power Apps and Flow. This webinar is designed to show you how to easily create Power Apps applications, and how to best take advantage of the recently introduced Power Apps custom visual for Power BI.

Vishwas will showcase a Power Apps application that is essentially a “portal” for existing Line of Business Enterprise Applications (inventory, contracts, etc.) and Services (Dynamics, O365, DropBox, etc.) Through the use of Power Apps features like the out-of-the-box connectors, integration with Flow and mobile enablement, you’ll learn how easy it is to build an app that allows users to have all the information they need in one location and on the device of their choice.

The webinar kicks off TOMORROW at 10 a.m. ET. Watch it right here or on Microsoft’s Power BI YouTube.

Microsoft Power Apps and Flow have been generally available since late 2016. They’re both tools that allow business users to streamline business processes without the use of code. Microsoft positioned Power Apps as their recommended replacement for InfoPath as the business user’s forms designer, and Flow as their replacement for SharePoint Workflow.

While these are welcomed replacements, both solutions also provide a broader level of support to the Microsoft stack and across a wide array of third-party applications.  I’ve recently been working with Power Apps and Flow to replace some internal applications, as well as to build proof-of-concepts for our existing clients. Here’s what I think of each, both separately and when putting them together… Read More…

powerappsIt all started with an email from a customer: They wanted an event app for an upcoming regional sales summit. The requirements fit the mold of a typical event app (session schedule and downloadable documents, photo sharing, surveys, sync with Outlook, etc.), but there was one small problem. The app needed to be done in less than a week.

Fortunately, we knew exactly what tools to use and how to put them to work for our client.

Read the rest of the story, and find out exactly how we got the job done, over at the Microsoft PowerApps Blog.