Win+V: Super Cut & Paste

“Manual data entry builds character,”
Joel Leichty
“Character gives us qualities, but it is in our actions–what we do–that we are happy…or the reverse,”
Aristotle

If you need to paste multiple values into multiple places, WIN+V should be your new best friend. Where regular CTRL+C copies something and CTRL+V pastes that last thing you copied, WIN+V allows you to select from your history if previous items copied.

Continue reading

Navigating ERP Environments: DEV, TEST, & PROD

Multiple environments are necessary during ERP implementations. An ERP implementation usually involve at least three environments, though additional environments might be added depending on implementation project requirements. DEV, TEST, and PROD, are the most common names for these three environments

PROD (May also be called GOLD)

After implementation, PROD refers to the live ERP environment. Prior to implementation, this is a transaction-less environment where tested configurations are setup. Transaction-less because ERP systems don’t have a “delete posted transactions” button, so you want to keep this environment clean from test transactions. So where does transaction testing happen?

TEST (May also be called UAT for User Acceptance Testing)

During the implementation, subject matter experts and end-users will setup sample configurations and model business processes with test transactions in the TEST environment. The desired, approved configurations will then be setup in the PROD (GOLD) environment.

DEV (Mostly just called DEV)

DEV environments are necessary as ERP projects frequently require customizations. A developer can build and deploy his developments to the DEV environment without interrupting the flow of user testing. Once any technical bugs are smoothed out, the customized code from the DEV environment can be promoted to the TEST environment, where it will be validated/approved by subject matter experts or other functional users.

Continue reading

Data Violates Integrity Constraints – Data Management in D365

Failure is no fun.

“Data Violates Integrity Constraints” is an error I encounter too often. If you get this error, perhaps this post will help you push past your failure.

In the scenario below, you try to import excel data through a data entity, but get a failure error.

That’s fine you say, I’m a professional. So you click view execution log to troubleshoot the issue.

If you try to view the staging data, you’ll see no staging data has loaded.
If you click on Staging log details, you’ll get a curious error message:
The data value violates integrity constraints.

This is curious, because it seems you haven’t loaded any data at all, let alone some data that would have triggered a validation error. You re-check your excel file, and the data you wanted to load seems correct.

So What’s The Problem? How do you fix it?

Frequently, the issue is there are some “blank” rows at the bottom of your excel data that are throwing off the upload. Data Management is trying to prevent you from importing those blank rows. Highlight the row below your data, press CTRL+SHIFT+DOWN and delete all the rows below your data. To be safe, you can do the same thing to the far right to clean up any columns that may be causing trouble as well.

Then, just like wherever else we encounter failure, set it up and try, try again.

Good luck!

Power Query Demo – Connecting D365 Tables in Excel

A D365 Finance and Operations reporting issue I had recently:
How do I report on all published WBS activities by their related projects?

Well, the activities table (smmactivities) doesn’t have a project id field.
If you are here for that specific question, the necessary table relationships are:

  • smmActivities
  • HierarchyTreeTable
  • HierarchyLinkTable
  • ProjTable

But I was interested in the process of doing this more than the specific reporting task. A basic way to do this could be to dump those tables into excel, then use a bunch of vlookups to add the associated project data to the activity rows.

Nothing wrong with that, but the final product would be a bit clunky…the whole smmActivities table, with a few extra columns. You could then delete or hide the unnecessary columns, but if you need to create the report again you’ll have to redo those same operations. If you need to add some attribute, you’ll have to do more vlookups (or xlookups, or index matches, whatever your preferred Excel operation). Is there a better way?

Introducing Power Query

Power Query is a tool to get and transform data in excel. In the example below, I exported the 4 desired tables, pointed to them as data sources, joined them together in Power Query, and then used Power Query to clean up my reporting output.

Here’s a video of that effort:

Reasons to Use Power Query

A Better Way to Get Data
Power Query can connect to other tables in your excel file (like in my example above). Power Query can also be used to connect to other external data sources. In a more sophisticated reporting example, there is probably a way I could connect directly to a D365 data entity. I’ll look into that more in the future.

A Better Way to Transform Data
I found Power Query to be a really pleasant experience for transforming data. A frequent D365 data migration issue is you’re given some legacy file, with some legacy file headers, which needs to be formatted into a D365 structure of columns and headers.

Instead of hacking away at the source data, which would be my first excel instinct. Power Query lets you add columns or rename headers programmatically…making it simple to redo the changes if you later get a NEW file of legacy data that also needs to be transformed.

I particularly like that Power Query shows a list of “applied steps”, which makes it easy to follow the transformations that take place between the original and transformed data.

Power Query shows applied steps in the process of transforming data

My first-time user experience is that Power Query is a great tool, and I can see it becoming an integral part of improving D365 data migration projects.

Additional Resources

My D365 Excel Add-in Disappeared – What do I Do?

The excel add-in connection is one of my favorite Dynamics features. What brings me more joy than updating a bunch of records through the excel add-in, or using the template to load a journal entry with tons of lines? Well, maybe hearing my son say “I love you too” brings me slightly more joy, but the excel add-in thing is a close second.

That said, sometimes I unintentionally close the excel add-in. Frustrated, I used to redownload my desired template from D365 and redo my work. This isn’t necessary though!

Here’s how you can get the excel add-in back:

Does anyone else frequently close this before they mean to?

  1. Click Insert
  2. Click My Add-ins
  3. Click Microsoft Dynamics Add-in
  4. Click Add

Why Edge Is My Preferred D365 Browser

My favorite Chrome shortcut for D365:

Ctrl+F to find menu item
Ctrl+Enter to click on found menu item

My favorite Edge shortcut for D365:

Ctrl+Shift+K to duplicate tab
(great for looking up multiple records at same time)

What I just learned in Edge for D365

Ctrl+F to find menu item (duh)
Esc, Enter to click on found menu item (ooohhh…)

Thank you for coming to my Ted Talk.

What Do You Do?

Featured

I hate this question. It’s a terrible question.  It’s the first question at every party, but it’s still a very bad question.  Yet some law of human nature states any two strangers standing next to each other long enough will eventually ask – whether for reasons of genuine curiosity or to evaluate relative status –  “What do you do?”

“I am a Dynamics 365 Finance and Operations Functional Consultant.”

I’m met with a blank stare.  I’ve said words and communicated nothing.  Only once have these particular words been sufficient. I was on a plane.  The woman next to me lit up, “Oh! I’m a Dynamics 365 Finance and Operations Functional Consultant too!”.  Then she paused, “How do you explain what you do to your family?”

Continue reading

FastTabs in D365 Finance

FastTabs are blocks in D365 forms that can be expanded or collapsed. Like a filing cabinet or library card catalog (remember card catalogs?), FastTabs allow lots of information to be compressed into a tiny space and opened for access when needed.

Below are some tips for navigating FastTabs in D365 Finance:

Personalization and Saved Views

If you routinely need access to certain field positioned lower on a form, the Move personalization feature can be used to move the more utilized field into a more prominent FastTab, or even to move an entire FastTab higher on the form.

Right-click and select personalize on a field and you can also select Show in Header, which will display that field attribute on the right side of the FastTab whether the FastTab is expanded or collapsed.

Expand All

If you are looking for a field on a form, but unsure which FastTab it is filed under, Expand all can be your friend. Right-clicking on the fasttab header gives you the option to Expand all FastTabs on the form. Used in conjunction with Ctrl+F, this can be a powerful method to quickly find the field you are searching for.

Less Words, Moar Video

I made a quick video of this. Let me know if this is helpful, and whether you’d like me to make more videos like this.

DynamicsCon Is Coming!

DynamicsCon is just 2 weeks away, happening March 16-18.

What is DynamicsCon?

DynamicsCon is the #1 Free, Virtual, Super-hero themed D365 conference in the world. Over three days, Microsoft Dynamics & Power Platform professionals across the globe will connect to learn, share ideas, and develop new skills.

I’m excited to participate in DynamicsCon, presenting a session of my own on Tuesday the 16th (Saved Views, Custom Fields & Developer-less Developments).

Why am I participating in DynamicsCon?

There are three reasons why I am participating in DynamicsCon:

  • Community – The D365 community is great. It is fun to meet and interact with the other D365 and Power Platform professionals. I’m grateful to the others who have helped me learn and apply new technologies and I’d like to be a part of improving the experiences of others.
  • Video – I have written on this blog for a couple years, but video is a new medium for me. DynamicsCon presentations involve 40 minutes of recorded video – committing to present stretched me out of my comfort zone, but this is a good thing. I hope to create more video content in the future.
  • Fun – DynamicsCon is fun. Just look at the super-awesome super-hero masks they made for Calvin and me! And don’t just take my word for it – check out this avid endorsement from Grammy award winning musical artist Kenny G

Why should you participate in DynamicsCon?

If you are a super-hero and/or Kenny G fan, I think the reasoning is self-evident. It is a harder sell if you are not, but the value is still there. DynamicsCon is a great opportunity to meet new people and learn new things around Microsoft’s D365 and Power Platform technologies. Even if you are unable attend the live event, it is worth signing up and watching the recordings afterwards.

Also, did I mention it’s FREE?!?

REGISTER HERE: DynamicsCon | Sign-up

Pin D365 Finance Workspaces to Your Taskbar

On the topic of Hunter Bars, a somber farewell to The Big Hunt in Washington DC.

Longtime readers of this blog know I’m a big fan of Workspaces. I’m such a fan, I gave an entire Workspace Presentation at last year’s User Group Summit.


Why Workspaces?

Here is my pitch for Workspaces:

If bookmarking the D365 Default Dashboard, in order to perform an action you need to 1) Open your browser 2) Go to D365 Default Dashboard and then 3) Goto the D365 form where you will perform your action.

(eg yourcompanyd365url.operations.dynamics.com/),

Bookmarking specific workspaces, utilizing the workspace’s tiles, listpages and links gets you straight to the action you want to perform. Your steps are now 1) Open your browser and 2) Go to the D365 form where you will perform your action.

(eg yourcompanyd365url.operations.dynamics.com/?mi=VendInvoiceWorkspace),

Default Dashboard

Vendor invoice entry workspace


Why Pin Workspaces to Taskbar?

Advancing this concept, Edge makes it easy to pin a workspace to your taskbar. If I click on the purple finance and operations logo on the far right of my taskbar, the vendor invoice entry workspace opens immediately.

The steps are now 1) Go to the D365 form where you want to perform your action and 2)…profit!


How to Pin Workspace to Taskbar

The Edge browser makes this easy. Go to your desired workspace and click the ellipsis on the top right of the Edge Browser. Select more tools, and then click pin to taskbar,

Pin Vendor invoice entry workspace to taskbar


Advanced Concepts

Limited navigation

If you add “&limitednav=true” to the end of your workspace URL, the menu item on the left disappears (zoom into the two Vendor invoice entry workspace screenshots above and look at the urls to see the difference). This gives you a few more pixels to work with (a small benefit), but also cuts down on distractions in the workspace (a larger benefit, possibly).

I like using this limited navigation with the pinned workspace because makes the D365 workspace feel more like a desktop application.

Change Icon

If you hold shift and click on the pinned shortcut in your taskbar, you can select properties to change the icon (the pinned website’s favicon is the shortcut’s default icon). This can be helpful if you want to pin multiple shortcuts to your taskbar and easily distinguish between them. The new icon image must be in .ico format – fortunately there are multiple sites that make it easy to convert standard jpg or png images to ico.


Happy Hunting…