Tip #972: Data import with date field fails

Recently we were struggling with import of a spreadsheet into Dynamics 365. If the date field was included, the import would fail. The user doing the import was set to US format, but the date format was set to dd/mm/yyyy format.

We found that we could get past the issue by saving the import spreadsheet as a CSV file. After saving as CSV, the file could be successfully imported, but it could not in XLSX format, even after reformatting the date column.

I’ve noticed that others in the Dynamics Forums have reported issues importing dates via the import utility from xlsx files, but that saving in CSV format works. You may also have success with Wayne Walton’s suggestion to format the dates in the spreadsheet in ISO standard YYYY/MM/DD format.

 

 

Tip #971: Be careful when using unsupported solutions

When the wheels are falling offIt does not happen often but it does happen. After some retrospection, I urge everyone to be very careful when following Joel’s advice on using attachment extractor solution. (Some say one needs to be careful when following any Joel’s advice but that’d be a discussion for another day).

My arguments (both of them):

  1. It is not supported.
  2. It does not come with the source code meaning you cannot support it yourself either.

Unless either of these change, i.e. Microsoft Labs folks have a change of heart and release it as an open source, or offer some kind of support, you’re risking of being seriously stuck if something goes wrong. (The only reasonable option is to call Joel, his direct number is 555-365-HELL). The solution does not move existing files and we cannot enhance it without going through some shady activities of reverse engineering the code.

What are the options?

  1. Third-party solutions. Paid and supported.
  2. Open source. One of the upsides is availability for on-premises. Not that on-premises storage is expensive or scarce but it’d be an interesting option to minimise the SQL consumption prior to the migration to online.

Tip #970: When attribute and entity collide

This tip is from Martin Tölk.

If the name of an attribute on a custom entity matches the name of the entity itself then you will not find that attribute in the metadata returned by WebAPI. Dynamics 365 will play the games with your brain by adding ‘1’ to the attribute name, presumably to avoid clashes.

For example, if you define an entity:

Entity name

and include an attribute with the same name (we use primary attribute, the same as Martin did, but the problem is reproducible on any attribute):

Attribute name

and then attempt to get the data using WebAPI: https://org.crm.dynamics.com/api/data/v8.2/new_locationcodes?$select=new_locationcode, you will get an error “Could not find a property named ‘new_locationcode'”.

Try getting all attributes by removing $select (and adding $top to keep it short): https://org.crm.dynamics.com/api/data/v8.2/new_locationcodes?$top=1, and you’ll find that the attribute was magically renamed to new_locationcode1:

{
  "@odata.context":".../$metadata#new_locationcodes",
  "value":[
  {
    "@odata.etag":"W/\"69645573\"",
    ...
    "new_locationcode1":"Foobar",
    ...
  }]
}

Ugh. The easiest way to avoid this headache is to avoid naming clashes in the first place.

Be smart, be like Martin and send us your tips via Facebook Messenger or simply email jar@crmtipoftheday.com.

Tip #969: The problem with sharing

Does Microsoft have guidance about how much sharing is too much? — CRMTOD reader

I find it hard to believe we have gone 968 tips without talking about the risks of excessive sharing in Dynamics 365. Few features in CRM parallel sharing on the “this is the best thing ever/this is my worst nightmare” scale.

How record based security can be used to control access to records in Microsoft Dynamics 365.

The good

Sharing is a good feature, because it gives administrators and users with the appropriate permission the ability to grant specific permissions to specific records, and is useful for handling exceptions to the rule. Need to have salesperson 2 handle salesperson 1’s accounts while she is out for a month filming Survivor season 83? Sharing can do that. Sharing can also be automated, meaning that if you have a need for a specific condition to automatically share records with a user or team, simple plugins, workflow assemblies, or Scribe can be used to do that. This has been the answer to many Dynamics clients funky security requirements.

The bad

While a very useful feature, sharing has a dark side.

  • Performance: sharing is facilitated by the Principal Object Access (POA) table. When you share a record with a user or team, a record is created in the POA table containing the ID of the user, the ID of the record, and the permission that he or she should have. But that’s not all! The cascading nature of sharing means that if a parental or configurable cascading relationship exists that is sharing enabled, the child records in those relationships will also be shared with the user or team (and additional records will be added to POA). There is also a bunch of murky reparent/inherited share scenarios that can also create records, which can cause the POA table to grow quickly.  This becomes a performance issue when the table gets extremely large (somewhere between 20 million and 2 billion records). When you query CRM, such as opening a view, advanced find, or viewing a chart, the results are filtered by the POA table. If the table is quite large or indexes are not optimized, this can lead to very slow system performance.
  • Administrator’s nightmare: Quick–show me which records are shared with Bob. You can’t do it. While you can click on a record and show who it is directly shared with, there is no way to easily do that for all records. Also, cascading/inherited shares don’t show in the sharing dialog on the record. Without opening each record in the context of that user, it is virtually iimpossible to know if your sharing strategy is working correctly
  • Forgotten shares: Remember that sales rep you shared the records with while her buddy was off for a month? Odds are you will forget to unshare those records. Got a workflow that automatically shares record with Tim if they are in Seskatchewan and the plumbing industry? Well Tim moved to a different industry vertical last month. Did your workflow remember to unshare all of those records?
  • Can’t “make it right”: After you use the system for a year or two, you may find that things get a bit off or you decide to make a wholesale change to your sharing strategy, so you want to run a “make it right” batch job to set/update all records with the appropriate sharing permission. There is no easy way to do this with sharing.

The answer

So to answer the question, yes, Microsoft does offer some guidance (if you are on premises) to optimizing and controlling growth of your POA table. But probably the best guide to understanding the POA table comes from this classic post from Scott Sewell. In it he explains how to decode the structure of the POA table and understand how your sharing strategy will impact database size. He also offers a Excel based decoder tool to encode/decode the POA table. Unfortunately the link in that post to the secret decoder ring is no longer valid, but Scott has located that file and you can download it here.

So now that you understand how the POA works, what are some steps that you can take to avoid the dark side of sharing?

  • Team ownership: In the old days, teams couldn’t own records, so we had to share to grant multiple people access to a record, without granting access to the entire business unit. With team ownership, you can assign records to teams of users in multiple business units.
  • Share with teams, not users. If you share a record with 10 users, 10 POA records are created, X 10 POA records for each child cascading shared record. If you share the record with a team with ten users, only one POA record is created (along with 1 POA record for each cascading share). This will dramatically reduce the size of your POA table. Want to take away a user’s permissions? Remove them from the team.
  • Use access teams for controlled sharing. So you can’t do owner teams, but you still want to be able to grant ad-hoc access to records to specific users. Some you want to just read the record, some you want read/write. Access teams can handle that, and you can have multiple access team profiles, one for read, one for read/write. Access teams are designed with performance in mind, so they don’t actually create the team and share the record until you add the first member of the team.

The real beauty of the team approach, be it owner or access team, is that it makes it much easier to see what records a user has, just by seeing what teams he or she is a member of. If you want to run a “make it right” batch job to reset all sharing permissions, you can do that by wiping out your team membership and then running a SSIS or Scribe job to populate the teams based on the new logic.

So please share your toys and Dynamics record access, but do it wisely.

Tip #968: Should I modify system reports?

There are 26 system SSRS reports (not counting subreports) that come with Dynamics 365. If you find one that is close to what you need (like the quote report), should you modify the system report?

I say no. Here is why:

  • System reports currently in Dynamics 365 are the same system reports that were in Dynamics CRM 3.0 (for the most part). They were written by someone who is probably no longer at Microsoft. When you dig into the RDL you will find that the most basic system reports include a lot of overhead and things like system reporting parameters that are not documented in the SDK.
  • Even though the system reports have not changed much over the years, upgrades will sometimes overwrite/republish system reports. So if you modify the system reports, there is a possibility that a future upgrade could overwrite your changes.
  • If you are online, you will find that some system reports still use T-SQL queries, while that is not allowed for us mortals. Microsoft sometimes gives themselves special dispensation to to things that we can’t. So if you want to copy the report and modify it, you will likely need to convert your copy to FetchXML.

For these reasons, other than minor/cosmetic changes to reports, my recommendation is do not modify the system reports. A good SSRS report writer can recreate the system report faster than she or he will be able to decode what somebody who is no longer at Microsoft was doing 10 years ago.

Tip #967: UI testing for Dynamics 365

Software testing is important and Dynamics 365 is no exception. Fundamentally, developing for Dynamics 365 is different from developing, say, an ASP.NET MVC application but, users don’t really care, do they? It’s a software that hopefully delivers business value, deal with it.

Developers who recognize the importance of the process, have always been making inroads into unit testing of the code they create, be it for the plugins to deliver magic on the server or javascript for the client-side functionality. Plenty of frameworks to choose from. For the C#, some developers prefer Moqs, some like Microsof Fakes, some even created CRM-specific agnostic frameworks. For javascript zealots, QUnit can be used for browser-based testing, something much more elaborate to step outside the browser, or, more recently, using Jasmine.

That’s all great but, technically, users don’t really care because with all these frameworks we’re not testing user experience.

Great news though, we now have a preview release of the UI Automation Library for Dynamics 365 CE and CRM. It comes from the Dynamics 365 team at Microsoft and is based on Selenium. I’m just going to copy their description verbatim because it sums up the purpose and the functionality really well:

The purpose of this library is to provide Dynamics customers the ability to facilitate automated UI testing for their projects. These API’s provide an easy to use set of commands that make setting up UI testing quick and easy. The functionality provided covers the core CRM commands that end users would perform on a typical workday and working to extend that coverage to more functionality.

This is great news and, in fact, it looks like the last missing piece of the fully automated testing pipeline.

Tip #966: E-mail integration in team or department deployment

MailboxesJoel has been producing tips by a truckload, I don’t think he’ll notice if I sneak this one in, especially when a fellow comrade developer David “Xrm.Tools” Yack is in pain.

David

Anyone have any suggestions for where let’s say a Team/Department gets CRM in their own subscription but their e-mail is still managed by a their corporate email server totally outside of the CRM subscription and their control.

They would like to have some of the goodness of e-mail integration with Dynamics 365, but have no ability to influence the corporate email strategy.

Any creative suggestions?

Joel

I have done that exact scenario with a forward mailbox. If the forward mailbox is on the domain and the email address of the forwarded email matches the address on a queue or user, the emails can be created in CRM.

Tîpp Jäår

Joel has written about forward mailbox many moons ago, worth another read.

Tip #965: Pass Parameters to Quick Create Form

If additional initialization logic is required when a form is opened, Dynamics 365 offers a choice between setting default field values (easy) and configuring custom parameters (not as easy but straightforward).

Quick forms also take parameters when opened. But, as Daryl “Always Raising” LaBar has discovered, sometimes it just does not work.

Short version: If you’re adding form parameters to a quick create form, always remember to add them to the default form as well, even if it “works” on your machine.

Long version: Go and read Daryl’s adventures in the local storage, if you feel like reading about a good lesson in investigative development.

Tip #964: Use latest language features

I often find that developers like comfort zones. They tend to use the constructs that they already know and are comfortable with. That includes language features as well. However, programming languages are evolving all the time and new features are introduced with every new release. Those features aren’t just cool, they make developer’s life much easier in a lot of cases.

One of my favorites is interpolated strings, introduced almost 3 years ago in C# 6.0. Instead of:

Trace.WriteLine(string.Format(
  "Name is {0} {1}", c.firstname, c.lastname));

you can now write:

Trace.WriteLine(
  $"Name is {c.firstname} {c.lastname}");

(Did I also tell you to stop using Console.WriteLine and start using Trace.WriteLine instead? No? That’d be the tip for another day then)

The way I see it, not only this new syntax allows for cleaner and more concise code, it implictly adds value to everything you create by sprinkling your code with dollar signs.

Of course, there are a lot more goodies in version 6.0 than just interpolated strings. There are null-conditional operators, exception filters, index initializers, to name but a few.

And don’t forget C# version 7 either, with out parameters, pattern matching, is-expressions, tuple types and literals, deconstruction, and many others. Like, how cool is this:

(string, string) LookupName(Guid id)
{
  // ... get those names
    return (first, last);
}

Languages evolve and so should your knowledge of them. So, what’s your favorite feature?

Tip #963: Minimum portal licensing requirements

Only someone as thorough as Feridun “Best Twitter Handle for CRM MVP” Kadir would spend their time analysing changes in the new (July 2017) edition of Dynamics 365 Enterprise edition Licensing Guide. As it turned out, it was a very good idea.

Until now, one could purchase just a single Team Member license, and get themselves a Dynamics 365 instance, a sandbox, and a portal merely for $10. Well, not anymore. Straight from the document, broken down to the separate bullet points:

  • Starting August 1st, 2017, access to the first included portal for the tenant, customers will be required to purchase a minimum of 5 Full User licenses of Dynamics 365 Customer Engagement Plan, standalone Dynamics 365 Applications (Sales, Customer Service, Field Service or Project Service Automation) or a combination.
  • Existing customers will not be impacted with this change until renewal.
  • New customers who need to purchase less than 5 users, may purchase the Portal “Add-on”.
  • Team Member Licenses will not contribute to the minimum user requirement

In other words: if you were legitimately using single team member license to get the portal, you are good until the next renewal, at which time you’d have to buy either portal add-on, of 5 full user licenses.