Sunday, September 28, 2014

SQL Query Fundamentals (Part 1)

When I was a fledgling consultant, SQL was not the ubiquitous backbone of Dynamics GP that it is today.  Back then, Great Plains ran on C-Tree and Btrieve databases engines.  Microsoft Access, connected to these databases via ODBC, was the power tool for data analysis and custom reporting.

Using Microsoft Access was fairly straight-forward.  Adding tables to a query immediately displayed the fields in the table. Position the main table on the left, and the related table(s) to the right, and drag an arrow from the common fields in one table to the other(s), and instant join. If you didn't get the data you wanted, simply right click on the connecting arrow and choose from options to connect the tables displayed in English.

Now, one of the most versatile tools in the Dynamics GP toolkit, is SQL Query Analyzer. In stark contrast to Microsoft Access, when you open SQL Query Analyzer, you are greeted with a blank screen.  This is reminiscent of the days of DOS, when you started your computer, and were rewarded with a blinking command prompt.

The safest of SQL Scripts are Select Statements.  This doesn't mean they are foolproof or without risk. If you execute a select statement on a big enough table, or link a few large tables together and throw in a WHERE clause with an AND or an OR, and you can cripple SQL performance.

Here's a sample SQL script for to illustrate some key concepts:


If you analyze the results of this query, you'll notice columns are returned side-by-side; the first iteration of each column has been transformed using various methods and the second is displayed as it normally appears without modification. 

Doc_ID vs. SOPNUMBE 
Doc_Type in text vs. SOPTYPE as integers
Doc_Date in US Format vs. DOCDATE in SQL Date/Time format 
Line_No in Integers vs. LNITMSEQ in increments of 16,384

So let's talk fundamentals:

The first step in query design process is to select some data from a table. This is typically accomplished using a simple select statement, akin to "Select COLUMN from TABLE," or "Select * from TABLE." What is important to know is informing SQL what table(s) you desire to select data from allows SQL to help you with the process.  Once SQL knows the table(s) you're querying against, it will prompt you with valid column names, which belong to these tables.

Enter the humble asterisk, Shift+8 or *. A.K.A. "star" as in *.* or "star dot star." In the DOS days, *.* was code for show me everything.  The humble asterisk performs much the same function in SQL. 

Starting with all fields is advisable, as this will allow you to see all the columns and data in a table, which will help you build a better query.  If the table is big, you'll want to add a WHERE clause (at the end of the query) to restrict the data set, so you're not tying up system resources - see WHERE clauses below.

In the end, you won't need every column in every table you've joined, you're typically looking for very specific data, which is why you joined the tables in the first place. 

Returning specific fields requires being very specific.  To return only the columns you desire, you need to call them out by name in TABLE.COLUMN format. It's not always absolutely necessary, but becomes absolutely critical when your joined tables contain the same columns, and your're returning this data. 

Failure to spell out the complete table and column can result in errors about ambiguity. Errors about ambiguous data is SQL saying, I see you're asking for SOPNUMBE, but that column is in both tables, which one did you mean? Since joining tables requires matching columns, it is typical for the tables to have duplicate columns. You should separate columns with a comma (i.e. TABLE-A.COLUMN-A as ALIAS-1, TABLE-A.COLUMN-B as ALIAS-2)

Additionally, you're going to want to produce usable data, and not all data in SQL makes sense as it is stored.  So, lets talk transformation...

Aliases are your friend.  It is easy to ALIAS a column, which changes the display name, not the actual name.  Just type whatever name you'd like to appear as the column name (header) to the right of the specific column.  

The same method can be applied at the table level.  You can type a more understandable and/or easier to remember name to the right of the table(s) you are selecting data from. You may have noticed, I typed AS between the table name and the associated aliases. When creating an alias, the word AS is totally optional.

Cases are sometimes absolutely necessary.  A CASE evaluates the values in the a table and performs actions, like transformation based on these values.  In my example query, the CASE statement looks at the value returned in the SOPTYPE field and converts it to understandable text.  Of course you need to know what these values equate to, in order to write your case, so you'll want to do your research prior to finalizing your query.

Conversions and equations can help transform data from one type or format to another.  In this query, conversions and equations were used to change the default date format "YYYY-MM-DD HH:MM:SS.HoS" for the DOCDATE field to a common and clearer US date format "MM/DD/YYYY".  Additionally, the LNITMSEQ field is converted from increments of 16,384 to simple and understandable integers. Conversions can also be used to trim trailing spaces, select part, rather than all of a data set and other very useful operations.

Joins allow related tables to be linked, so reports can include all the required data, not just the data in the core table.  In this instance, the tables have been joined using an "Inner Join" on Sales Order Number and Sales Order Type. The SOP10100 table contains Sales Order Header information and the SOP10200 table contains Sales Order Lines.  These tables are the Dynamics GP Sales Order open tables, and do not contain historical data. 

This query makes use of an Inner Join.  Inner Joins include data in both linked tables where there are matching records in the linked fields in both tables. There are many kinds of Joins, with various uses.  The chart below is one of the best I've seen on explaining these various joins and their purpose. The Inner join is visually represented in the center of the illustration below, which shows the data set returned is only data which overlaps in the two tables. In my career, I have found a practical use for every join illustrated below.


Finally, WHERE clauses allow you to specify/restrict the data you would like displayed. Because of the WHERE clause, used in the sample query, the query only displays data for one Sales Order. WHERE clauses are also useful when defining date ranges, or identifying orders containing specific items, or entered on for specific customers, etc.

This overview should provide the fundamentals for writing queries to retrieve data from Dynamics GP.  In order to make use of this tool, you must know or identify where to find the data you're looking for.  I can think of no better source for such information than the following link.


There are plenty of table reference sites out there, but Victoria's site is well organized, and contains other useful information like the translations for statuses and document type field data, which is instrumental in writing CASE statements.




Thursday, September 25, 2014

Management Reporter Installation Checklist

I have noticed numerous SOLID blog posts, which walk through the installation steps for Management Reporter!  The Dynamics Community does not disappoint - the level of sharing and collaboration frankly astounds me.   These blog posts include screen shots, and detailed instructions. 

Management Reporter installation woes still seem to be the source of many posts on the community forums.  The reason, the DEVIL is in the DETAILS.

What follows is a checklist I use when installing Management Reporter.  Prior to putting this checklist together, I routinely encountered issues when installing and configuring Management Reporter.   Without further adieu:

Make sure install user is Domain and Machine Administrator
Add Server Role - Web Server
Add Server Role - Application Server
Enable .Net 3.5.1 ASP.Net
Enable IIS 6 Metabase Compatibility Feature for IIS 7
Enable WCF HTTP Activation
Disable User Account Control
Reboot Machine (can try iisreset if not practical)
Run installation from a folder with short path-name
Run installation as administrator
Do NOT select NT AUTHORITY accounts - use an Active Directory Account created for this purpose.
Ensure the user you have running the application pool has "log on as batch job" rights in the local security policy of the machine.
Remember to Register Management Reporter - default installation is Evaluation Copy, no reminder to register, until the trial period expires!


Wednesday, September 24, 2014

Mission Critical

I have been eyeballing a Dynamics GP Community forum post for weeks. This evening I finally broke down and posted a reply to the forum member, who was soliciting advice on running Dynamics GP 2013 on a Windows 2003 Server.   The forum member is well aware this is not a supported configuration.  

This kind of decision is akin to not performing routine maintenance on your vehicle, and wondering why your engine seizes up.  Worse yet, running your engine with oil and gas, which doesn't conform to the manufacturer's specifications.

Most business systems are "Mission Critical."  

These systems support business transactions like, invoicing customers and receiving payments, making bank deposits, reconciling cash accounts, managing inventory, etc., etc. etc.  They are also the source of data and reporting, which empower stakeholders to make informed business decisions.

Why would you let a "Mission Critical" tool lapse into disrepair, worse yet, why would you void your manufacturer's warranty?  When your business system requires support, one of the first things any software publisher is going to establish is are you on a supported version or configuration.  If you are not, the publisher cannot and will not help you.

Here is a link to the original post: 

https://community.dynamics.com/gp/f/32/t/138454.aspx

My comments are as follows...

I have passed by this post on a few occasions, and have not commented, despite having a strong opinion based on experience.  Periodically, I receive referrals involving companies who have "painted themselves into a corner."  
The situation is always a variation on the following theme.  They are using an unsupported version of Dynamics GP or SL and something terrible has gone wrong.  They desperately need assistance to get back up and running right NOW!  Microsoft Tech Support cannot and will not help them because they are not on a supported configuration.  
I don't think there is a solid business case for building your business system on a foundation of sand.  Do yourself a favor and upgrade the server to a supported version, or acquire a new server, if you have another system which requires Windows 2003.

My strong advice to companies about their business system software and the hardware it calls home, is to treat it with the same respect you would your pet, automobile or favorite article of clothing.  For much the same reasons.

You wouldn't forego vaccinations for your dog or cat, would you?

You wouldn't be surprised if your car stopped running if you never changed the oil?

I would also highly recommend you not toss your dry-clean only pants in the washing machine. I know how this turns out, and you're not going to like it much. 

If you really don't think your ERP system is not mission critical, unplug it for a day or two.

Tuesday, September 23, 2014

How to Implement Change Effectively

People who implement change know the basic steps -- or should.  Summed up... Plan your work, work your plan, test, rework, test, deploy, train and support system users.

A few years ago, I was part of an amazing implementation team -- we implemented more meaningful change in less time, than I ever thought possible. We identified issues (or were presented problems by users), prioritized them (don't skip this step), defined solutions, received approval, and implemented the approved solutions -- all was right with the world.

Then there was a disturbance in the force.  We lost a team-member, who was of course, summarily replaced.   The new team-member was affable and capable, they just had one proverbial fatal flaw.  A flaw it took months to identify, and by then it was too late, we'd achieved stagnation, and lost team-members to more promising projects...   

The fatal flaw was simple, Great was never Good enough! 

It has been said, Good is the enemy of Great!  This is the mantra of organizational changologists everywhere -- yep, I just made that word up. What is often forgotten by these agents of change is Perfect is the enemy of Great!   

What will always ever be true is there is no such thing as a PERFECT system. If you want to be effective implementing change, it is absolutely critical to maintain the discipline to deliver on good enough.  

If you find yourself in the midst of a stalled implementation, take a look at what you're trying to deliver.  Is pursuit of perfection the problem; if so, act quickly to determine what good enough is, before the pursuit of perfection ends in project failure.

It took a bit of doing, and a couple of years, but we managed to put the band back together. Good team-members with great chemistry still require a healthy environment to thrive. Projects stagnated or floundering because of poor decisions or unreasonable expectations are not healthy environments.




Monday, September 22, 2014

Dynamics GP Troubleshooting Checklist - Troubleshooting 101


If you encounter a significant repeatable problem with Dynamics GP, the following checklist should help you resolve the issue.  The core of this list is borrowed from Jivtesh Singh, as it most closely approximated the list in my head.   I have added my own spin on things, and more than a few extra steps and included a link to Jivtesh's original blog post.


http://www.jivtesh.com/2008/09/checklist-of-troubleshooting-gp-problem.html
  • Note the exact error Message - Critical! Get a screenshot. (Alt+Prt sc)
  • If there is a details button, click on it, and get another screenshot. 
  • Determine exactly what the user was doing in what application/window when the error occurred.
  • Search CustomerSource or PartnerSource Knowledgebase
  • Google the Exact Error preceding it with Dynamics GP
  • Google the Heart of the Error (error message #, etc.)
  • Google the Error and Leave out Dynamics GP
  • Search Microsoft Dynamics GP Groups/Forums
  • Does the problem occur only on one machine?
  • Does the problem occur only for one user?
  • Does the problem occur when logged in as SA user on the machine?
  • Does the problem occur when logged on machine/domain admin?
  • Does the problem occur when the application is run with elevated permissions?
  • Does the logged in user have restricted permissions?
  • Does the user have required permissions in the required folders on their machine and on the network?
  • Has User Account Control (UAC) been disabled?
  • Check the user's security, try the process with a user with full permissions
  • Check Hard Disk Space on drives.
  • Gather the following information from the machine that has a problem
  • Check Event Log
  • Generate a DexSQL Log, review it, and post it in a GP forum.
  • Get a copy of Dex.ini, Dynamics.set and IM.ini files (compare to working machine).
  • Check and record version of software (GP, FRx, MR, IM, Biz Portal, 3rd Parties, SQL)
  • Check Windows version and service pack.
  • What 3rd party products are being used?
  • What customization are in use?
  • Disable customization and 3rd parties.
  • Check the ODBC (32bit vs 64bit)
  • Create a new ODBC connection
  • Is the user using named printers?
  • Is anti-virus software's running on the machine?
  • Create a specific exception for the application experiencing problems.
  • Have any changes been made to this machine recently? Share this information.
  • Try and replicate the problem on the test server for the company.
  • Try and replicate the problem on your test server.
  • Check queries fired in Profiler.
  • Use the Support Debugging tool.
  • Ask your partner for assistance.
  • Acquire a partner if don't have one.
  • Open a support ticket with Microsoft Support.

Solver and eOne Team-Up! Try to Take Over the World...

In case you missed it, Solver and eOne recently announced a partnership to provide Business Intelligence solutions to the Dynamics Community.  Solver and eOne have been major players separately in the Dynamics Marketplace, this combination has tons of potential.

Solver brings Business Intelligence acumen, which extends across multiple product lines.  We've deployed BI360 on both Dynamics GP and Dynamics SL.  Imagine what eOne might do with the collaboration of a partner who has a deep understanding of multiple Dynamics products.  I can't wait to see how this pans out.

Here is a link to the announcement:

http://www.sys-con.com/node/3149608

eOne Announces new features for SmartList Builder

eOne, the original makers of SmartLists and SmartList Builders have recently announced the release of new features in SmartList Builder.

I am super excited about Two of these features!

1. Easy modification of EXISTING SmartLists... Quite possibly the most requested feature in SmartList history.  The ability to use existing SmartLists as a foundation for modification is revolutionary.

2. SQL data pulls on the fly.  If you've ever used SQL to create a view and then deployed a new SmartList using the view, you'll understand why I am so excited about the addition of this feature.

Check out this blog post from eOne to get description of all the new features of SmarList Builder:

http://blog.eonesolutions.com/2013/12/look-whats-coming-to-smartlist-builder.html

Saturday, September 20, 2014

10 Things I Hate About Dynamics GP

I want all who read this to understand this post for what it is, a list of MY frustrations with Microsoft Dynamics GP.   I have been implementing and supporting Dynamics GP, by choice, since 1996.  Like many consultants, Dynamics GP is not the only solution I have implemented and supported.  I have worked with satisfied clients on Macola, Intuitive, Solomon and Platinum.

I freely share my list with potential clients during demonstrations in an effort to set expectations correctly.  I also suggest potential clients ask for a similar list from the people performing the demonstration of competing products.  As it turns out all other software is perfect, zero defects, nothing they can do better, just ask them.

If you would have asked me for this list, when I was a fledgling consultant, my list would have started with Btrieve error 20.  A couple of years ago the top item on my list was the Year-End Close window.  Here's an interesting and important observation about my list, it is ever-evolving.   

Great Plains and ultimately Microsoft have managed to consistently resolve the issues I find most objectionable.  Sometimes solutions are a long-time coming, like having to choose your desired printer prior to printing a report, which previously checked in at number four on my list.  Other issues, I never knew I had, until an improvement was introduced like SmartLists.

My list follows...  You will note some of the list has been struck through - these are items Microsoft resolved with the release of GP 2013.  So, without further adieu here is MY list of 10 Things I Hate About Dynamics GP:

  1. Year-End Close window is (was) poorly designed; No Progress Bar, No Confirmation, No Options.
  2. Required Fields Error.  On save, GP should shift focus to the problem field or list required fields with NULL values.  It does not.
  3. There is no Account / Sub-Account relationship in Dynamics GP, so users must create every possible posting account combination.
  4. Desired printer must be selected before print.  Standing ovation on announcement leads to awkward moment.
  5. Expansion of some GP windows sometimes give you 4 more inches of Grey Border, instead of more columns of data.
  6. Expansion of the SmartLists window was poorly executed.  User could not suppress navigation tree, stretching the window produced little benefit.
  7. Discard Changes option is not universal. You open a transaction, review and close it.  GP asks you to save it, but you don't remember changing anything. What do you do now?  Delete the transaction?  Save an undesirable change?  How about a third option, no matter where you are in the system; Discard Changes?
  8. Weak Active Directory Integration.  Management Reporter, Web Client, SSRS Reports all use Active Directory. Dynamics GP still uses SQL Authentication.
  9. Dynamics GP has two Bill of Materials Modules. These modules have different features, functions and tables and are completely incompatible going forward.
  10. State Payroll Reporting has been left to Greenshades, a third party.  Every US business reports to at least one state.
  11. There is a disconnect on some Modular Voids. Transactions that originate in other modules must be voided in these originating modules to maintain accurate accounting records in Microsoft Dynamics GP. If you use Bank Reconciliation to void a transaction that was entered in another module, you receive a warning message that suggests that you void the transaction in the other module. You may continue through this message. However, the original transaction remains in the module in which the transaction was created. Reference KB858473
  12. Standard Navigation's departure from palettes to multi-level chained navigation.  Ah, the good old days.  I look back fondly on the days when one could navigate to Tools > Setup > System > Security and a palette containing all Security related menu options would open (User, User Access, User Security, etc.)  This palette could be pinned down, so you could move effortlessly between these related windows.  Now, it takes four levels of navigation to get to these options, and there is no easy way to transition between these related windows.  If you don't remember this feature, you can't miss it.  You really had to be there.
  13. Report Writer could use a Face Lift. Well-trained folks are all powerful with this tool, novices are typically lost.  There has to be a happy medium.  Easy to use, and incredibly flexible.  And don’t get me started on why a customer needs to buy a third-party solution to replace a default report with an SSRS version.
As you can see, my list is really 13 Things I Hate About Dynamics GP.  I guess Microsoft thought that was an unlucky number and/or 10 Things I Hate About Dynamics GP sounded better.

P.S. One of the best things about Microsoft Dynamics GP has always been the enthusiastic partner channel that works tirelessly to fill the gaps in what always ever will be an imperfect system.  In many ways this asset has made it possible for Microsoft to "take its time" while managing significant change.


Tuesday, September 16, 2014

The Value of Checklists - Dynamics GP 2013 Upgrade Checklist

As a general rule I advocate for the use of checklists when doing anything important.  It doesn't matter how many times a pilot flies, they still get in the plane and run through the "preflight" checklist prior to taking off, because let's face it, planes can and do fall out of the sky when something goes wrong.

Every year, our team goes through the Year-End Checklist prepared by the Microsoft team, adds our in spin on things, and then publishes it to our clients.

The checklist below is a constantly evolving Dynamics GP Upgrade Checklist. It is by no means exhaustive, and is a high-level document, which references technical information without providing significant detail.  Feel free to comment or ask questions.


Dynamics GP 2013 Upgrade

Preparation
Upgrades to GP 2013 Require a Zero Dollar Product Order
Designate a Client IT resource
Distribute Installation and Upgrade instructions to Client IT resource
Create/Validate Admin account for CustomerSource access for Client IT
Obtain Dynamics GP Install Media – Client IT Download to Install Folder
Obtain Dynamics GP Keys – Client IT Download to Install Folder
Obtain SQL Server Media – Client Obtain (validate licensing)
Obtain Management Reporter Media – Client Download to Install Folder
Obtain Management Reporter Key
Client IT - Create Domain/Machine Admin Credentials Install User
Client IT - Run File Maintenance / Check Links on data
Determine 3rd Party Applications installed
Contact 3rd Party support to determine version compatibility
Obtain 3rd Party Product Keys
Review necessary documentation – installation and upgrade instructions for any 3rd Party products
Determine GP Modules Installed  and in use
Identify Reports used to validate upgrade

Installation Prerequisites
Validate Sever Configuration
Add Server Role - Web Server
Add Server Role - Application Server
Enable .Net 3.5.1 ASP.Net
Enable IIS 6 Metabase Compatibility Feature for IIS 7
Enable WCF HTTP Activation
Install SQL Server 2008 r2
Optional – if someone else installed SQL and used Window Only Authentication 
Reconfigure SQL to accept Windows and SQL Authentication
Enable SQL SA User Account
Set SQL SA User Account Password – make sure meets requirements
Distribute SA Password to designated client IT resource
Install SQL Server Reporting Services
Validate SSRS Install
Install Management Reporter
Install Dynamics GP
Install Required 3rd Party Products

Backup Tasks
Backup Databases
Backup Working Workstation Folder for current version
Back Up Application Customizations
Custom Forms
Custom Reports

Optional - if moving Dynamics GP to New Environment
Move Database backups to new machine
Restore Files List Only (Determine DB Configurations)
Create Target Databases using DB Configurations
Restore Databases
Set SQL Compatibility Level (i.e. move from SQL 2005 to 2008)
Configure Security
Run Scripts to Capture Logins from Old Server
Run Script to Create Logins on New Server
Run sp_changeowner DYNSA on GP Databases
Run Grant.sql script on GP Databases
Backup the Databases – make sure you can restart from this point if Upgrade goes sideways
Run DynUtils
Upgrade System Database
Upgrade Company Databases
Enter Registration Keys
Add New Users
Create New Users
Testing -  Log in as user
Share Network Folder (hold shared components)
Set Security Correctly (Read and Execute Permissions)
Create Workstation install package
Install Workstations
Test Workstations
Run reports to compare to validation reports

How to Create and Run a SIMPLE Macro in Dynamics GP

For those new to Dynamics GP, macros require an introduction. There is a tool in Great Plains which allows a user to record their keystrokes and mouse clicks...  Tools > Macro > Record 

What you can do with this tool ranges from the simple to the complex.  

I will start with a high level overview of the macro process.  And like many of my blogs, it begins with a story.   Many moons ago, when I was a fledgling consultant, I was at a Great Plains event. Long before Great Plains was acquired by Microsoft.  A couple of gentlemen named Martin Hildebrand and Sean Mahoney from the Taylor Group, one of the preeminent Great Plains partners at the time, were having a discussion about the use of Macros...  

Apparently, you could record the act of adding an inventory item into Great Plains, then cut and paste the recorded Macro text into Microsoft Word.  In Word you could use a source file of inventory items in Excel (or Text) format in concert with the Mail Merge feature, typically used to automate form letter writing to replicate this simple macro code and combine it with the data from your list.  

The mail merge would duplicate the Macro code for each of the Inventory Items in the source file. Simply, paste the merged documents back into the Macro and run it in Great Plains.  Presto! The unbelievable result was the import of all the items in the source file.

That was it, using the knowledge acquired from this casual conversation, I dumped inventory items into an Excel file from Macola -- yes, Macola - fledgling GP consultants have to come from somewhere. 

Using these inventory records, a Macro and the Mail Merge process in Word, I created my first Macro, and before I boarded my plane, I had uploaded all of my inventory items into a test Great Plains database (which I am pretty sure was running on Btrieve). 

I never looked back – Macros could do things nothing else could.  Have a third party product, Macro.  Need to import data into custom fields, Macro.  Want to update records inserted using integration manager, Macro.  Want to delete a bunch or records from GP, Macro.  Want to update your counts when doing Physical Inventory – you guessed it, Macro!  I went absolutely bonkers for Macros.


This blog-post is meant to provide a simple example of Macro use.  In this instance we used macros to delete a number of customer records for a customer using WennSoft Equipment Management.  Let's call this operation, customer cleanup - subtitled, have the client remove undesirable records before you import them.

The first step in Macro recording is not, as you might suspect, to record entering a transaction.  It is to prepare to record a transaction.  You must ensure you touch every field you will require, in order to be able to map all the data necessary into the system - deleting records is no exception.  

Consequently, you need your import file - this will provide you a complete list of fields you need to find homes in GP for.  Once you have the list of fields, do a dry run on the window, to figure out if any dialogue boxes will pop up - any activity on the screen will be captured by the macro, superfluous activity is undesirable.  Once you've completed your dry run and know you can enter a transaction without errors/dialogues interrupting, you can record your Macro file.

You need to open the window, ensure it is in focus and the cursor is in the starting field and starting field is empty. Type the field value and move directly, via mouse, to the next required field - tabbing through fields will touch fields you don't need in your macro, which increases the length and complexity of your macro code unnecessarily. Worse yet, clicking on a look up button, rather than typing, will add a mess of useless lines to your Macro - trust me, don't do it.

Once you have completed your transaction click on the OK or Save button, in most cases your cursor will return to the starting field on the form.   If not, click clear, or something similar to get it there - it needs to be there, because macros require a loop to work properly.  By loop I mean, in order for Macros to work, they must cleanly move through the form, inserting data, save this data, and return to the first field on the form to begin processing the next record.

Now you have some Macro code - it should look something like this.

# DEXVERSION=11.00.0359.000 2 2
CommandExec dictionary 'default'  form 'Command_Sales' command 'RM_Customer_Maintenance'
NewActiveWin dictionary 'WennSoft Products'  form 'RM_Customer_Maintenance' window 'RM_Customer_Maintenance'
  TypeTo field 'Customer Number' , 'SAMPLE'
  MoveTo field 'Delete Button'
  ClickHit field 'Delete Button'
# Are you sure you want to delete this customer record?
NewActiveWin dictionary 'WennSoft Products'  form DiaLog window DiaLog
  ClickHit field OK 

It will be much longer than this, if you're doing something substantial, but this is an example.  What is important to note, is, as I said, Macros run in loops, so DO NOT cut and paste the entire macro into Word.

What you want is the start and end of your transaction:

NewActiveWin dictionary 'WennSoft Products'  form 'RM_Customer_Maintenance' window 'RM_Customer_Maintenance'
  TypeTo field 'Customer Number' , '«CustomerID»'
  MoveTo field 'Delete Button'
  ClickHit field 'Delete Button'
# Are you sure you want to delete this customer record?
NewActiveWin dictionary 'WennSoft Products'  form DiaLog window DiaLog
  ClickHit field OK 

Now that you have your transaction loop pasted into Microsoft Word, go to the Mailings tab in Word Navigation and Start Mail Merge > Letters. Then Select Recipients > Use an Existing List. Then browse to the Source file and select it.  Now you should have list of Merge Fields to choose from.  Select the area inside the single quotes around the customer ID, 'SAMPLE' you entered and select Insert Merge Field, choosing the <<CustomerID>> merge field - it should now look roughly like the image below.


Now choose the Finish & Merge button from your Word Mailings navigation.   This will create a new Word file with one transaction for each line in your source file.  Select all the records by clicking on Ctrl+A, copy them all by selection Ctrl+C and paste them back into your Macro File using Ctrl+V (Ctrl+P prints things), perform a 'save as' function, so you have your original Macro file as a template should you need it (this does not always go right on the first attempt).

Open Dynamics GP, navigate to the window you want to import your data into (this is critical path).  From the now open window choose Tools > Macro > Play and browse to your the macro file which contains all the records you wish to import.  Great Plains will begin to rapidly enter the information on your behalf, or in this case, delete and confirm the deletion of the customers in your list.

Finer Points:

When editing Macros you might want to use a product like Notepad++.

When troubleshooting macros, which will tell you what line they encounter a problem on, and not much else, you definitely want to be using Notepad++ (Notepad++ does not pay me for endorsements). 

https://notepad-plus-plus.org/

Notepad++ shows the line numbers in a text file, and when a macro stops, the dialogue box will note what line it stopped on. If your macro is thousands of lines long, you'll appreciate not having to manually count the line numbers.

You may also notice, when the macro stops, it stops on the record it was last working on, so if you don't have Notepad++ you can search your macro file for the master record your macro stopped processing on.

When saving a macro file, make sure you do not save it with txt extension, if you do, change it back to the .mac extension prior to running your macro. 

Apple keeps associating .mac files with Apple's QuickTime application - it cannot successfully open them - believe me, I have tried. You do not have to change the file association, as you will be browsing to your macro file from within Dynamics GP, and it will see them, even if the association has been made by Apple.

When editing macros, right click on Macros and select Edit or Open With > Select program, choose Notepad++.

Helpful Hint... When entering a significant number of records, it is not uncommon for something to interrupt the normal processing of a macro (they can be temperamental). If this happens, keep calm. It is possible to find the place your macro stopped, remove the preceding data/lines from the macro, which have already been imported and restart the macro, once you've fixed the problem. 

It is not uncommon to do this multiple times during the testing process. This can be invaluable, as you can note what caused the problems, and fix the problems in the live environment prior to running the macro there, where it should run uninterrupted.

One potential cause of problems is touching the mouse, which can take the focus off the window you're importing data into. When this happens, you'll get an error that roughly states the window is not active/or field does not exist in active window. These problems are best avoided, by not touching the mouse.

NOTE: A word about single quotes (').  For Macros, this character is CRITICAL - it defines the boundaries for the data entered by the macro (i.e. 'DATA').  From time to time, you will experience difficulties with imports, because data contains single quotes (apostrophes). Customer and Vendor Descriptions like O'Doule's, O'Malley & O'Reilly will reek havoc on a macro.  If you experience this type of error, you can perform data transformation, by using scripts to remove the undesirable character, or request the customer clean up the data prior to import.

Be not discouraged... I have been doing this for a very long time, I have had people, in-a-fit, going out of their mind, when a Macro isn't doing what they want, call me exasperated. I can typically look at the macro and tell them what the problem is.  Your initial attempts might fail, but keep after it.  I plan to write a few more blog posts on this subject covering intermediate and advanced applications for Macros.

Finally, A WARNING! DO NOT comment and say you deleted the entire customer list in your Live company with no backup while testing this example... There are so many things wrong with that sentence, I can't begin to enumerate them.  I will indeed laugh at you... No, I won't laugh, I may even cry and commiserate with you. Please leave yourself some options by having a backup handy.

If you're going to run macros, test them. When running them, make a backup first. Macros can update a lot of data, very rapidly. Watch your macro run, if it looks like it's doing something you don't like, you can stop it fairly easily. Almost any movement or click of the mouse will interrupt your process (conversely, when things are going right, leave the mouse alone).



Monday, September 15, 2014

Restore failed for Server '%'. %Exclusive access could not be obtained because the database is in use.%

This article contains steps that pose risk, and should only be implemented by those with an understanding of the technologies and the risks involved.   

This article is specific to Microsoft Dynamics GP users, but contains some information, which is likely relevant to other SQL users in general.  You are here because you may have encountered the following error - or something like it:



When this error occurs, click OK, then Script the restore operation to SQL Query Analyzer, so you can run it after you have addressed the problems with Exclusive access.  Your query should look something like this.

RESTORE DATABASE [DEMO2] 
FROM  DISK = N'C:\TechNotes\2013YEU\TWO_BackUP_09142014.bak' 
WITH  FILE = 1,  
MOVE N'GPSTWODat.mdf' 
TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\GPSDEMO2Dat.mdf',  
MOVE N'GPSTWOLog.ldf' 
TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\GPSDEMO2Log.ldf',  
NOUNLOAD,  REPLACE,  STATS = 10

GO

If you searched for this specific error, you are likely fairly certain you don't have anyone actually accessing the database - BUT:

First - do your due diligence to ensure you have constructive Exclusive access to the database.  

Are users accessing the database?

These are both useful queries to determine what users, if any are accessing the database - both can be run from the master database: 

Select * from Dynamics..Activity -- Users in Dynamics GP

sp_who2 database -- Users accessing SQL Databases via any means

Users who are accessing the database using Great Plains should appear in the Activity Table in the Dynamics database.  

Contact them and make sure they are out.  Reach out and remote into their desktop, or assume control of their Remote Session.   Just make sure you're not terminating the session of a user who is actually using GP - this can have serious consequences.  

When you are sure they are out, you likely have a stuck users.  Delete these record(s) from the Activity database.

delete from Dynamics..Activity -- This query will delete ALL records from the Activity Table

Other users should be visible when using the sp_who2 query, which can be run in any database, and will show detailed information about users' status. 

Specifically, what databases are displayed in the DBName column.



You can see above, the problem  is a sleeping session of the sa user in the DEMO2 database.  

To end the session, you must Kill it, risk, risk, risk...  Open Query Analyzer and execute the query below, where 57 is the SQL Process ID occupied by the sleeping user process - processes should not be terminated lightly, make sure you have correctly identified the problem user.

Kill 57

Since you are experiencing problems obtaining Exclusive Access to the database, I suggest not using the point and click interface of SQL Server Management Studio to do the restore.  If fact, adding a couple of lines to your database restore script, captured above, can alleviate any additional problems you might encounter.

USE master
Alter database DEMO2 set single_user with rollback immediate
RESTORE DATABASE [DEMO2] 
FROM  DISK = N'C:\TechNotes\2013YEU\TWO_BackUP_09142014.bak' 
WITH  FILE = 1,  
MOVE N'GPSTWODat.mdf' 
TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\GPSDEMO2Dat.mdf',  
MOVE N'GPSTWOLog.ldf' 
TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\GPSDEMO2Log.ldf',  
NOUNLOAD,  REPLACE,  STATS = 10

GO

Adding the USE master statement line highlighted above, makes sure you are not accessing and therefore locking your target database.   Altering the target database, prevents other users from connecting to it.

It seems like only once in a 'blue moon' this kind of error occurs, but when it does, it can be more than moderately annoying.  

Since the introduction of SQL Change Tracking with Management Reporter, this type of locking is more common. The default refresh rate of Management Reporter, is a contributing factor and can be changed. Additionally, when restoring a database, you can stop Management Reporter Services using The Configuration Console.

Saturday, September 13, 2014

Dynamics GP Analysis Cubes Deployment - Cutoff Date and Unknown Dates

For those of you new to deploying Dynamics GP Analysis Cubes for use with Business Analyzer, here is a link to an excellent post on doing just that.

Deploying Business Analyzer

I recently deployed GP Analysis Cubes, only to discover the Date fields were not populating correctly, they were coming up "Unknown."  This made it impossible to use date fields as filters, which prevents meaningful analysis of data.

First, I checked BIDS (SQL Business Intelligence Development Studio), and was able to validate I had data in the tables by Double Clicking on the Cubes, Opening the Cube Structure, then Right Clicking on various tables and selecting Explore Data (this previews the tables) - you should see data in Dimensions.  If you can do this, then you know the data is not the problem.

After validating the data was not the problem, I connected to the Microsoft Analysis Server, then Right Clicked on the Dynamics GP Analysis Cubes at the top level and select Process, then click OK.   This refreshed the Entire Analysis Cube Structure.  I was then able to go into Excel and click Refresh Data, and the Unknowns disappeared.

For some reason, using the Master Packages to create the Analysis Cubes, did not produce any errors, and did not successfully completely configure the Cubes.

As an aside - when following the documentation on the configuration tool for Analysis Cubes, you will encounter a window, which covers a cutoff date.  This is asking for a Historical Cut Off Date!   If you pick a date, everything prior to that date will be skipped, during Cube Creation.  This is not clearly spelled out in the documentation.

Thursday, September 11, 2014

Management Reporter - The report has a reference to a dimension called % but this dimension is not available to company %

So, I was working with a client today, who had recently undergone training on Management Reporter.  During this training the client and I built a Balance Sheet and Income Statement for their main entity.  The client's homework was to do the Balance Sheet and Income Statement for a smaller entity on her own. Logically, the client copied components from the main entity, which uses the same Chart of Accounts with minor variations.  Here's where things went off the rails.

When the client tried to validate the report, she ran it, and received the following error:

The report has a reference to a dimension called Natural Acct but this dimension is not available to company HXXX.

I have seen problems with Dynamics SL making the transition from Legacy to Data-mart configuration throwing an eerily similar error, though not identical.  I have never seen this kind of problem with Dynamics GP.  

So, I went into Dynamics GP Setup > Company > Account format for both companies and validated they matched - or so I thought...

Turns out the formats differed ever so slighting in that in the main company database, the Main Account segment was named Natural Acct, and in the other database it was simply Natural.   We changed the segment descriptions to match exactly.  Then in Management Reporter went to Tools > Refreshed Cached Financial Data.  Viola - the report worked.

Be aware, if you are going to use Management Reporter to produce Consolidated financial statements, you want to make sure your companies' Account Formats MATCH exactly, not just constructively.