I recently encountered an issue when trying to create an Exact Age column for a contact in Microsoft Dynamics CRM. There were several solutions available on the internet, but none of them was a good match for my specific situation. Some ideas I explored included:

  1. Creating a calculated field using the formula DiffInDays(DOB, Now()) / 365 or DiffInYears(DOB, Now()) – I used this at first, but if the calculated field is a decimal type, then you end up with a value like 23 years old which is not desirable. If the calculated field is a whole number type, then the value is always the rounded value. So, if the DOB is 2/1/1972 and the current date is 1/1/2019, the Age will be 47 when the contact is actually still 46 until 2/1/2019.
  2. Using JavaScript to calculate the Age – The problem with this approach is that if the record is not saved, then the data becomes stale. This one also does not work with a view (i.e., if you want to see a list of client ages). The JavaScript solution seems more geared towards the form of UI experience only.
  3. Using Workflows with Timeouts – This approach seemed a bit complicated and cumbersome to update values daily across so many records.

Determining Exact Age

Instead, I decided to plug some of the age scenarios into Microsoft Excel and simulate Dynamic CRM’s calculations to see if I could come up with any ideas.

Note: 365.25 is used to account for leap years. I originally used 365, but the data was incorrect. After reading about leap years, I decided to plug 365.25 in, and everything lined up.

Excel Formulas

Setting up the formulas above, I was able to calculate the values below. I found that subtracting the DATEDIF Rounded value from the DATEDIF Actual value produced a negative value when the month/day was after the current date (2/16/2019 at the time). This allowed me to introduce a factor of -1 when the Difference was less than or equal to 0.  Using this finding, I set up the solution in CRM.

Excel Calculations

The Solution

  1. Create the necessary fields.
    Field  Data Type  Field Type  Other  Formula 
    DOB  Date and Time  Simple  Behavior: User Local   
    Age Actual  Decimal Number  Calculated  Precision: 10  DiffInDays(new_dob, Now()) / 365.25 
    Age Rounded  Whole Number  Calculated    DiffInDays(new_dob, Now()) / 365.25 
    Age Difference  Decimal Number  Calculated  Precision: 10  new_ageactual – new_agerounded 
    Age  Whole Number  Calculated    See below 
  1. Create a business rule for DOB; setting it equal to birthdate when birthdate contains data. This way when birthdate is set, the DOB is set automatically. This arrangement is necessary for other calculated fields.
    Business Rules
  2. Set up the Age calculated field as follow:
    Age Calculated Field

Once these three steps have been completed, your new Age field should be ready to use. I created a view to verify the calculations. I happened to be writing this post very late on the night of 2/16/2019. I wrote the first part before 12:00 a.m., then I refreshed the view before taking the screenshot below. I was happy to see Age Test 3 record flip from 46 to 47 when I refreshed after 12:00 a.m.

Age Solution Results

Determining Exact Age at Some Date in the Future

The requirement that drove my research for this solution was the need to determine the exact age in the future. Our client needed to know the age of a traveler on the date of travel. Depending on the country being visited and the age of the traveler on the date of departure, different forms would need to be sent in order to prevent problems when the traveler arrived at his or her destination. The solution was very similar to the Age example above:

The Solution

  1. Here is an overview of the entity hierarchy:
    Age at Travel Entities
  2. Create the necessary fields.
    Entity  Field  Data Type  Field Type  Other  Formula 
    Trip  Start Date  Date and Time  Simple  Behavior: User Local   
    Contact  DOB  Date and Time  Simple  Behavior: User Local   
    Trip Contact  Age at Travel Actual  Decimal Number  Calculated  Precision: 10  DiffInDays(contact.dobnew_trip.start) / 365.25 
    Trip Contact  Age at Travel Rounded  Whole Number  Calculated  n/a  DiffInDays(contact.dobnew_trip.start) / 365.25 
    Trip Contact  Age at Travel Difference  Decimal Number  Calculated  Precision: 10  new_ageattravelactual – new_ageattravelrounded 
    Trip Contact  Age at Travel  Whole Number  Calculated  n/a  See below 
  1. Create a business rule for Contact DOB; setting it equal to birthdate when birthdate contains data. This way when birthdate is set, the DOB is set automatically. This arrangement is necessary for other calculated fields.
    Business Rules
  2. Set up the Trip Contact’s Age at Travel calculated field as follow:
    Age at Travel Calculated Field

Once these steps have been completed, your new Age at Travel field should be ready to use. I created a view to verify the calculations.

You’ll notice that in the red example, the trip starts on 8/14/2020. The contact was born on 9/29/2003 and is 16 on the date of travel but turns 17 a month or so later. In the green example, the trip is also on 8/14/2020. The contact was born 4/12/2008 and will turn 12 before the date of travel.

Age at Travel Solution Results

Conclusion

While there are several approaches to the Age issue in Dynamics CRM, this is a great alternative that requires no code and works in real time. I hope you find it useful!

Driving value, lowering costs, and building your organization’s future with Microsoft’s next great business technology

Lately, I’ve been helping folks understand the Microsoft Power Platform (MPP) by sharing two simple diagrams.

The first one is below and is my stab (others have made theirs) at contextualizing the platform’s various components in relation to one another.

The Common Data Service (CDS) is the real magic, I tell people. No matter which app you are using, the data lives there in that one CDS across your entire environment. (And no, folks outside your organization don’t get to use it.) This means that data available to one of your apps can be re-used and re-purposed by your other apps, no wizardry or custom integration required. I promise, it just works. Think expansively about the power of this in your organization, and you’ll come up with some cockamamie/brilliant ideas about what you can do.

These are the types of data-driving-business-function that geeks like me always dreamed of.

A diagram of Microsoft Power Platform components

Then there’s PowerApps, in purple. Most folks think of this as a low-code/no-code app development tool. It is, but it’s more. Imagine that there are three flavors of PowerApps:

  1. Dynamics 365, which in the end is a set of really big PowerApps developed by Microsoft
  2. COTS apps developed by Microsoft partners (including AIS), available for organizations to license and use
  3. Custom apps you build yourself

Point Microsoft PowerBI at all of this, then mash it up with data from outside of your CDS that you get to via hundreds of out-of-the-box connectors, automate it all together with workflows in Flow…and you’ve got Power Platform in a nutshell.

When I’m presenting this to a group, I turn to my next slide pretty quickly at this point.

A rearranged look at Microsoft Power Platform

Here I’ve essentially re-arranged the pieces to make my broader point: When we think about the Power Platform, the emphasis needs to be on the Platform bit. When your organization invests in this technology, say via working with an implementation partner such as AIS or purchasing PowerApps P1/P2 licenses, you’re not just getting a product or a one-off app solution.

What you’re getting is a platform on which to build your modern business. You’re not just extending Office 365. Instead, you’re creating a future where your organization’s data and business processes are deeply integrated with, driving, and learning intelligently from one another.

The more you leverage the platform, the higher the ROI and the lower the marginal costs of those licenses become. A central goal of any implementing partner ought to be guiding organizations on the journey of migrating legacy systems onto the platform (i.e., retiring legacy licensing + O&M costs) and empowering workers to make the platform even more valuable.

We don’t invest in one-off apps anymore, i.e. a CRM in one corner of your network where you run your sales, something in another where you manage your delivery, clunky Human Resources Management off over there where you take care of your people, etc.. No, what we care about here is the platform where you integrate all of the above — not through monolithic one-size-fits-all ERP — but rather through elegant app experiences across all your users’ devices that tie back to that magical Common Data Service.

This is what I mean when I tell folks sky’s the limit, and thinking about your entire business is what’s called for here. It’s because Power Platform gives us the ability to learn and grow with our customers, constituents, vendors, employees, and other stakeholders like never before.

That’s what has everyone at Microsoft so excited. I am as well.

I want to learn from you. How do you make Power Platform understandable to those who haven’t thought about it too deeply? How does your organization make it valuable as a platform rather than just a product? I love to build beautiful things, so inspire me!

The business intelligence, automation, and enterprise application landscape is changing dramatically.

In the previous incarnation of enterprise technology, line-of-business owners were forced to choose between pre-baked commercial off the shelf (COTS) software, which was difficult to customize and often did not truly meet the business’s unique needs, or custom solutions that (though flexible and often tailor-made to the business needs of the moment) cost more and were far riskier to develop and deploy.

Furthermore, certain classes of applications do not have a COTS answer, nor do they justify the cost of custom software development. In the chasm between the two arose a generation of quasi-apps: the homegrown Excel spreadsheets, Access databases, Google docs, and all manner of other back-of-the-napkin “systems.” End users developed these quasi-apps to fill the gaps between the big software IT provided and what users actually needed to do their jobs.

We’ve all been there: The massive spreadsheet that tracked a decade’s worth of employee travel but was always one accidental click away from oblivion. Or the quirky asset management database living on your officemate’s desktop (and still named after an employee who left the company five years ago); the SharePoint site full of sensitive HR data, or the shared network drive that had long been “shared” a bit too liberally. A generation of do-it-yourself workers grew up living on the edge of catastrophe with their quasi-apps.

Thankfully, three trends have converged to shatter this paradigm in 2019, fundamentally changing the relationship between business users, technologists, and their technology.

Connectivity of Everything

The new generation of business applications is hyper-connected to one another. They allow for connections between business functions previously considered siloed, unrelated, or simply not feasible or practical. This includes travel plans set in motion by human resources decisions, medical procedures scheduled based on a combination of lab results and provider availability, employee recruiting driven by sales and contracts.

Citizens’ Uprising

Business users long settled for spreadsheets and SharePoint, but new “low-code/no-code” tools empower these “citizen creators” with the capability to build professional grade apps on their own. Airport baggage screeners can develop mobile apps that cut down on paperwork, trainers and facilitators put interactive tools in the hands of their students, and analysts and researchers are no longer dependent on developers to “pull data” and create stunning visualizations.

New Ways of Looking at the World (& Your Data)

This isn’t just about business intelligence (BI) and data visualization tools far outpacing anything else that was recently available. It’s not even just about business users’ ability to harness and extend those tools. This is about the ability of tools like Microsoft Power BI to splice together, beautifully visualize, and help users interpret data that their organizations already own — data to which you’ve connected using one of the hundreds of native connectors to third-party services, and data generated every second of every minute of every day from the connected devices that enable the organization’s work.

It’s an exciting time. I’ve explored these trends further, plus how Microsoft’s Power Platform has become the go-to platform for organizations mastering the new landscape in my whitepaper, Microsoft’s Power Platform and the Future of Business Applications. We’re way past CRM. I hope you’ll read it and share your thoughts with me!

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!

AIS’ work with the NFL Players Association (NFLPA) was showcased as a Microsoft Featured Case Study. This customer success story was our most recent project with NFLPA, as they’ve sought our help to modernize multiple IT systems and applications over the years. We were proud to tackle the latest challenge: Creating a single, shared player management system, using Dynamics 365, for the NFLPA and all its sister organizations.

The Challenge

This case study was featured on Microsoft. Click here to view the case study.As the nonprofit union for NFL players, the NFLPA constantly looks for ways to better serve its members—current and former NFL players—during and after their football careers. But multiple player management systems across the associated support organizations resulted in poor customer service and missed opportunities for NFLPA members. Valuable data captured by one department wasn’t accessible to another, causing headaches and delays when licensing opportunities arose, and limited the organization’s ability to be proactive about the challenges members face after retirement.

The Solution: A Single Source

We used Microsoft Dynamics 365 to create a single, shared player management system, called PA.NET, for all the NFLPA organizations. We customized Dynamics 365 extensively to meet the unique needs of the NFLPA and integrated it with the organization’s Office 365 applications.

At the same time, we shifted all legacy IT systems (websites, financial applications, and others) to Microsoft Azure, giving NFLPA an entirely cloud-based business.

The Results: More Opportunities, More Time, Fewer Costs

With one master set of player data and powerful reporting tools that employees use to find answers to their own questions, the NFLPA can uncover marketing and licensing opportunities for more players and identify other ways to help its members.

Because PA.NET automates so many previously manual processes, it frees up hours of drudge work each week for NFLPA employees, which they convert to creative problem solving for members. And its IT staff has freed up 30 percent more time by not having to babysit infrastructure, time it uses to come up with new technology innovations.

By moving its business systems to the cloud, the NFLPA can scale its infrastructure instantly when traffic spikes—such as when football season ends and licensing offers heat up. No more over-provisioning servers to meet worst-case needs. In fact, no more servers, period. With cloud-based systems, the NFLPA no longer has to refresh six-figure server and storage systems every few years.

Read the full Microsoft Featured Case Study here to learn more about our work and more about great work the NFLPA does on behalf of its members.

SCORE LIKE NFLPA. WORK WITH AIS. Transformation is on the horizon for your organization. All it takes is the right partner. With the experience, talent, and best practices to lead you to success, AIS is the right partner for you.

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…

sharepoint 2013 logoIf you’ve ever had the need to add document management capabilities for your entities in CRM, you already know that CRM 2013 and CRM online rely on SharePoint for this functionality. This out of the box integration point is well documented and available for configuration in the CRM administrative interface. When set up, users can create, upload, view and delete documents in SharePoint locations that correspond to entity instances in CRM.

This post will discuss a different integration point – using search in SharePoint 2013 to expose CRM entity data. When setup properly, SharePoint 2013 can provide a robust, enterprise level search capability that can be tailored to your needs. Also, it seems to fill a current functionality gap in CRM that often requires a third party tool. Granted, you will need SharePoint 2013 Enterprise to realize this setup, but if this is available to you there should be no need to look anywhere else for search. Read More…

Dynamics_crm_logoRecently, I worked on a project that required me to programmatically set up Field Security in Microsoft Dynamics CRM 2011. Field Security allows you to designate selected fields (of selected entities) to be “secure” – which means only a certain group of users can have access to it. This access is made up of three operations: read, update, and create, each of which can be granted separately. MSDN does a pretty good job giving an overview of how it works: https://msdn.microsoft.com/en-us/library/gg309608(v=crm.5).aspx   Read More…
Attachments, Notes, and Annotations

How do you handle document storage and management in CRM? While this is a prominent feature in SharePoint, it is not as obvious or as easy to use in CRM. However, if you have a need to attach and manage documents in CRM, there is a provided option.

CRM offers a Notes field that can be turned on and associated to any entity. This Notes field is actually a reference to an entity called Annotation. The Annotation entity holds your file attachment and a reference ID back to the entity that the attachment belongs to. This feature is turned on by default for some of the default entities, but you need to turn it on yourself for custom entities.  Read More…