In this video, we look at building a report based on project financial dimensions using the excel data connector.
Note to future self:
In the data management workspace, if you don’t see a data entity that you expect to see, try refreshing the entity list. This is particularly the case when a new data entity is created, or a trail environment is first spun up.
Go to Data management workspace
Continue readingNot sure what to get that special D365 accountant in your life? Waited till the last minute and now you don’t have time to buy a gift? Show your appreciation by passing along this list of journal entry tips. Better yet, send a link to our whole series on shortcuts.
#D365Blogs: the gifts that keeps on giving – even during the holidays.
These example screenshots will be from the general ledger journal form, but are applicable across journal types in D365.
Alt+N can create a new journal header, and also create new journal lines once the journal lines are opened.
Continue reading
“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.
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
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?
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 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 readingFailure 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.
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!
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:
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?
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:
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.
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.
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?
Ctrl+F to find menu item
Ctrl+Enter to click on found menu item
Ctrl+Shift+K to duplicate tab
(great for looking up multiple records at same time)
Ctrl+F to find menu item (duh)
Esc, Enter to click on found menu item (ooohhh…)
Some law of human nature demands that any two strangers, gathered for a sufficient period of time, will eventually ask – whether from genuine curiosity or to evaluate relative status – “What do you do?”
I never know what to say.
“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!”. She paused, “…How do you explain it to your family?”
Continue reading