Quick Look – The New Dynamics AX Working with Excel

Reading Time: 9 minutes

Share:

Share on facebook
Share on twitter
Share on linkedin

[fusion_builder_container background_color=”” background_image=”” background_parallax=”none” enable_mobile=”no” parallax_speed=”0.3″ background_repeat=”no-repeat” background_position=”left top” video_url=”” video_aspect_ratio=”16:9″ video_webm=”” video_mp4=”” video_ogv=”” video_preview_image=”” overlay_color=”” video_mute=”yes” video_loop=”yes” fade=”no” border_size=”0px” border_color=”” border_style=”” padding_top=”20″ padding_bottom=”20″ padding_left=”” padding_right=”” hundred_percent=”no” equal_height_columns=”no” hide_on_mobile=”no” menu_anchor=”” class=”” id=””][fusion_builder_row][fusion_builder_column type=”1_1″ layout=”1_1″ background_position=”left top” background_color=”” border_size=”” border_color=”” border_style=”solid” spacing=”yes” background_image=”” background_repeat=”no-repeat” padding=”” margin_top=”0px” margin_bottom=”0px” class=”” id=”” animation_type=”” animation_speed=”0.3″ animation_direction=”left” hide_on_mobile=”no” center_content=”no” min_height=”none” last=”no” hover_type=”none” link=”” border_position=”all”][fusion_youtube id=”8v3AuLmCwZY?list=PLOgOmXCGOFC7alBIaF0iKCp9l56d3-Kmb” width=”600″ height=”350″ autoplay=”no” api_params=”” class=”” /][fusion_separator style_type=”none” top_margin=”20px” bottom_margin=”” sep_color=”” border_size=”” icon=”” icon_circle=”” icon_circle_color=”” width=”” alignment=”center” class=”” id=”” /][/fusion_builder_column][fusion_builder_column type=”1_1″ layout=”1_1″ background_position=”left top” background_color=”” border_size=”” border_color=”” border_style=”solid” spacing=”yes” background_image=”” background_repeat=”no-repeat” padding=”” margin_top=”0px” margin_bottom=”0px” class=”” id=”” animation_type=”” animation_speed=”0.3″ animation_direction=”left” hide_on_mobile=”no” center_content=”no” min_height=”none” last=”no” hover_type=”none” link=”” border_position=”all”][fusion_title size=”2″ content_align=”left” style_type=”single solid” sep_color=”” margin_top=”” margin_bottom=”” class=”” id=””]Quick Look -The New Dynamics AX Working with Excel[/fusion_title][fusion_text]Hello, this is Mark Vent with a “Quick Look at The New Dynamics AX Working with Excel.”

About Sikich

I work for Sikich, a professional services firm with divisions for Technology, Advisory, and accounting.  Sikich has been in business for over 30 years and has over 700 employees working from 11 regional offices.  Besides being a great place to work and a great regional accounting firm, Sikich is a Microsoft partner in the top 1% of all technology solution providers in the world.  We are a Gold Certified Partner, member of the President’s Club and the Inner Circle.

Challenges with Editing Records

Some of the challenges that companies’ face, when it comes to editing records within an enterprise system, revolves around the number of records they’re trying to update.  If we’re updating one or two records, obviously we’re going to do that right within the user interface of the enterprise system; but if we’re trying to update, you know, dozens, or hundreds, or even thousands of records, you need a tool to do that, because it’s too difficult and too time consuming to do that within the user interface.  With some enterprise systems you’re forced to use some clumsy tools, some kind of editor tools that are not easy to use nor easy to train users, and if you do spend the time training users, that that training is not portable; it’s very specific to the enterprise system.  So those are the challenges were going to address today using Excel with the New Dynamics AX.

Dynamics AX Working with Excel Demo

Let’s log in to the new Dynamics AX and start the process. I’m going to use the customer table as an example of how a user would edit a large number of records. I know that there are not many customers in the sample database here, that I’m going to show you, but you will be able to see from the examples how effective it is to use Excel for that.

I’m going to click on ‘sales order processing and inquiry’ and I have added a tile to this screen that allows me to jump from here to my customer table; so I’ll click on that tile. Now I see a list of all the customer records in my system.  Now there’s a couple of things I want to show you.  First is that if I go to this icon on the top menu, which is the ‘Open Microsoft Office,’ this system will give me a couple of options here. The export to Excel is simply a one-way download of the information to the Excel spreadsheet. So when I click on download, you’ll see it prompts me “You want to save this file, or open it?”  I’m going to open it, so we can review the data, and I’m going to enable editing on this.  This is a great example of just opening the data that you would see in an AX form into an Excel document. A user would do this if the purpose was to take this data and do some additional analysis on that data, or perhaps generate some graphs, or analyze the data in another way.  As I mentioned, this is a one-way download, which means that any updates I make here will not be reflected within the New Dynamics AX, it just allows me to take the data out into a tool that I’m comfortable and familiar with.

The second process we’re going to look at is the “Open in Excel” option. I’m going to click on customers here. While that’s opening, let me explain that this is an Excel data connector add in, that comes with the new Dynamics AX and it allows Excel to interact with the Dynamics AX ‘O data’ services that exposes some public data entities.  I’m going to hit download it, open the file. I will enable editing and then provide my credentials to login to the Microsoft data set.  What I am seeing now is a link to the Microsoft Dynamics AX table using Excel.  So I have the ability to make changes here, and then publish those back to the database to update Dynamics AX.

Let’s look at a couple of examples of how users might utilize this tool again, assuming I’m looking at a large number of Records, I could do some things like filtering values, such as the name.  Let’s say we want to apply text filter to find all customer names that include the word “retail”. That filters my list down to something a little more manageable.  The next thing I may want to do is sort the customer group field, A to Z, and that way I can quickly see that there’s a couple that come up here to the top ‘Contoso Retail Portland’ and ‘Contoso Retail USA’ where the customer group is currently defined differently than the balance of them.  That could be a mistake, so let’s do a quick Excel update to those.  I want to do a ‘Ctrl C’ copy and then ‘Ctrl-V’ to paste those values into the fields that are incorrect, and I have now fixed those two issues.

Let’s do a different kind of filter. Maybe I now want to look at my wholesale customers. So I’m going to change this to a wholesale value, and it will show me all my customers with wholesale in them. And again, this is another filter’ A to Z.’ Notice that at the bottom of this list now, I see that “Desert Wholesales” and “Green Wholesales” also have the wrong customer group. Again ‘Ctrl C’ paint them, ‘Ctrl V’, and I’ve updated those records.

Now I could remove my name filter, and let’s sort these one more time, just to see if any odd records will come to the top or the bottom.  Now I see that my wholesales customers are all in the ‘wholesale customer’ group.  I see that my retail customers are in the ‘retail customers’ and there are a couple of exceptions. Here is a ‘Joy Global Surface Mining’ customer that’s my customers 20.  Perhaps that’s correct. I’ll leave it as it is. But my point is I can now sort and filter the data in Excel to find outliers and make corrections.  Having completed my edits and changes to the records in Excel, I can now publish this back to the new Dynamics AX.  I’m going to hit the publish option here, let it analyze and update those in AX.  Now that I’m done, I go ahead and close my Excel spreadsheet, and I don’t have any need to save this.

Now, that I’m back looking at my customer list in Dynamics AX, we could do some double checking here to make sure the updates happened as expected.  For example, let’s go to our customer group and sort ‘A to Z’ and confirm that we see the same kind of changes here that we did in Excel. I updated my wholesale customers to use customer group 10.  As I scroll down I also see that my retail customers are using customer group 30, and I still have those few outliers at the bottom that we talked about where they were in unique customer groups and we decided that that was correct.

Summary

To summarize, we have used Excel in two different ways within the new Dynamics AX.  We used it just to export data into Excel, to do further analysis, and generate graphs. We also used it to open the data within Excel, to edit data within Excel, in any way we need to, and then publish those changes back to the Dynamics AX table.

Some of the benefits of using Excel like this, is that it is familiar. Everybody knows how to use Excel; it’s a good tool. The business logic of the Dynamics AX is enforced on any changes that we would make an attempt to publish back to AX, so that prevents us from doing something that is not correct, in terms of business logic, and that training is very portable.  We don’t have to retrain people on how to use Excel. We show them how to connect of course, give them permissions so they can do what they need with records within Excel.

Thank you for your time! If you have any questions about Sikich or about the topic of this presentation, feel free to give me an email at the email address below.

Thank you![/fusion_text][/fusion_builder_column][/fusion_builder_row][/fusion_builder_container]

This publication contains general information only and Sikich is not, by means of this publication, rendering accounting, business, financial, investment, legal, tax, or any other professional advice or services. This publication is not a substitute for such professional advice or services, nor should you use it as a basis for any decision, action or omission that may affect you or your business. Before making any decision, taking any action or omitting an action that may affect you or your business, you should consult a qualified professional advisor. You acknowledge that Sikich shall not be responsible for any loss sustained by you or any person who relies on this publication.

SIGN-UP FOR INSIGHTS

Join 14,000+ business executives and decision makers

Upcoming Events

Latest Insights

About The Author