Recently, I have had to import lots of different data for clients. They give me Excel worksheets that are full of names, numbers and telephone numbers. If you are working with Dynamics NAV or GP, you know that some of the fields can be very fussy in the import. Dynamics AX is more forgiving. I clean up the data, and get it ready.
Most clients ask me how I clean up columns, especially phone numbers. If you use Excel a lot, you will know these formulas or tricks. This post is for those who do not have to do this very often, and need some help.
1. Using Excel to change Words from All CAP to All lowercase, or to PROPER
- you can use the UPPER, LOWER, or PROPER functions to automatically change the case of existing text to uppercase, lowercase, or proper case.
- First, insert a temporary column next to the column that contains the text you want to convert
- In the temporary column, for example, cell B2, type =PROPER(A2)
- The other choices are =UPPER(A2) or =LOWER(A2)
Now, all of the names, countries, states, provinces, etc. can have standardized type.
2. Removing spaces and symbols in a phone number
- Often we get a list of telephone numbers that look like this
- To get rid of the (, ), –, Ext. 0000, use the find and replace function
- To get rid of the space, use the find and replace, using the space bar
- Press Ctrl + Space to select all cells in a column.
- Press Ctrl + H to open the “Find & Replace” dialog box.
- Press Space bar in the Find What field and make sure the “Replace with” field is empty.
3. Filling in the extension on a phone number
- Now you want to add trailing zeroes to make all the numbers with a fixed length, such as 14 digits for a telephone number in Canada and the US
- Create a blank column right of the existing phone number
- Use the EXCEL function =A2&REPT(“0”,14-LEN(A2)) where “A” represents the column with the telephone number in it
- The results are
Posted in Dynamics AX, Dynamics GP, Dynamics NAV, Excel, How To, Microsoft, Working
- Tagged Dynamics AX, Dynamics GP, Dynamics NAV, Excel, How To, Tips and Tricks
There are several different articles in different blogs that talk about the looming date of October 2018 which brings the end of Dynamics AX 2009 support.
Well, lots of Microsoft software have pending dates when support will be ending. Windows 7 is one of the software packages that no longer has support. Yet people still use it.
You wonder, then, why there is so much concern over Dynamics AX 2009. The answer, for me, is in Henrik Marx Larsen’s article https://www.linkedin.com/pulse/dynamics-ax-2009-out-support-2018-you-ready-henrik-marx-larsen.
I think the main idea presented is that companies are going to need to start thinking about their short term and long term strategies for business infrastructure. The questions are:
Do we prepare for moving to the cloud now and get ready for the change before October 2018? Or, do we wait until into 2018, and see what the organizations in our industry are doing, and then make a decision?
It is a tough set of questions. What is your organization going to do?
The title above is also the HEADLINE for Mary Jo Foley’s blog of August 25 … you can see her blog here http://www.zdnet.com/article/microsoft-offers-free-windows-server-2016-licenses-to-vmware-switchers/?ftag=TRE624278d&bhid=24421659496900511552450458228080
This is great news for Microsoft Partners and clients that wish to set up data centers. Here is the feature differentiation comparison from the article.
Check it out to see if this will make a difference to your organization.
CU 10 for Microsoft Dynamics AX 2012 R3 has been released. Dann Maurno of MSDynamicsWorld.com has published a blog that describes all of the new features. Check out his blog at http://msdynamicsworld.com/story/cu-10-microsoft-dynamics-ax-2012-r3-available-what%E2%80%99s-new.
The piece that I found interesting is the listing of features now available for Transportation Management. The list below comes from Dann’s blog.
Transportation Management Enhancements
- Create scheduled routes. Scheduled routes can now be generated from a route plan, where the segments of the scheduled route correspond to the hub configuration associated with the route plan. A batch job is used to create the scheduled routes depending on date range, particular days of the week and a load template.
- Improvements to auto-release to warehouse process. When using the auto-release to warehouse process it is now possible to consolidate multiple transfer orders based on To warehouse and From warehouse into the same shipment.
- Integration to project. It is now possible to plan transportation for purchase and sales orders linked to projects. It is possible to access the load planning workbench from the item requirements screen.
Great article, Dann!
This is posted on the AXUG site: Dynamics AX Training: Creating custom workflows in AX 2012
See this training from Lana Vernovsky at https://www.linkedin.com/groups/1836680/1836680-6068043555648065537
Video discussing the workforce planning in Dynamics AX 2012 is now available on VIMEO from Corey Bakhtiary. Here is the link. https://vimeo.com/145321390.
Moving forward in the Microsoft Windows Server world, one sees that Windows PowerShell is being used more and more. I have been learning that there are some sweet things you can do with it. Here is a blog about “Who Has Access To This Directory?” by MVP Thomas Rayner, from CANITPRO (Canadian IT Pro Connection). Thanks Thomas, for posting this. Everyone else – enjoy!
Posted in Azure, Dynamics AX, DYNAMICS CRM, Dynamics NAV, How To, Microsoft, Windows Server
- Tagged ActiveDirectory, PowerShell, PowerShell Administration, Security, Windows Server 2014, Windows Server 2016
Figure 1 – picture from Microsoft Virtual Academy, from the course “What’s New In Windows Server 2016 Preview”
Microsoft is releasing a lot of new technology between June and December 2015 including Dynamics AX 7. All of the new releases are built to work together in the cloud.
Some of us don’t need to install Windows Server, but would like to have an understanding of the features and how the features might be used to support what we are doing in ERP or CRM. Microsoft’s course in the Virtual Academy gives an overview of some cool features.
- Explore enhanced virtualization functionality.
- Explore automated processes and configuration to help you spin up compute, storage, and networking resources faster.
- Preview new features that reduce system downtime.
- Find out how rolling upgrades can help you adopt updates and operating systems faster for Hyper-V and Scale-Out File Server.
- Learn about the zero-footprint, cloud-optimized Nano Server technology, along with scripting with the new PowerShell Desired State Configuration features.
Dynamics AX 7 Release Info
The following information comes from the Blog at 365freelance.com (http://blog.365freelance.com/).
The final version of AX 7 is expected be announced at Convergence 2015 EMEA in Barcelona S (between November 30th and December 2nd). The pricing model will be announced as well. General availability will follow Convergence.
At first, AX 7 will only be deployable on Azure through Lifecycle Services (LCS). This means that Dynamics AX consultants who haven’t used LCS yet will need to become familiar with it.
A list of resources referencing Dynamics AX 7 as of today:
Note: Remember that links can change over time as servers and connections change.
As Dynamics AX implementations become more complex because of changing software technologies, you may benefit from this “how to” guide by long time AX implementers, Yogesh Kasat of Real Dynamics and JJ Yadav of Ignify. The book is available from Packt Publishing at https://www.packtpub.com/application-development/microsoft-dynamics-ax-implementation-guide.
The Table of Contents covers:
Table of Contents
1: PREPARING FOR A GREAT START
2: GETTING INTO THE DETAILS EARLY
3: INFRASTRUCTURE PLANNING AND DESIGN
4: INTEGRATION PLANNING AND DESIGN
5: DATA MIGRATION – SCOPING THROUGH DELIVERY
6: REPORTING AND BI
7: FUNCTIONAL AND TECHNICAL DESIGN
8: CONFIGURATION MANAGEMENT
9: BUILDING CUSTOMIZATIONS
10: PERFORMANCE TUNING
11: TESTING AND TRAINING
12: GO-LIVE PLANNING
13: POST GO-LIVE
This book is very useful for newcomers to the Dynamics AX world.