Monday, November 23, 2009

PartnerSource Funny

I'm sure you never thought you would hear those two words together.

So tonight I go to login to PartnerSource, and apparently Microsoft has decided that I'm now a customer and that my company has moved to Hong Kong!

I don't look forward to trying to figure out how this happened and how to get it fixed!

11/24/09 Update: I sent a message this morning at 8:20am to Voice Support explaining the issue. 40 minutes later, I received a response saying that they would look into the problem.

At 3:30pm I received an update that they had resolved the issue by setting my language preference in my profile to English. Unfortunately, this didn't resolve the issue.

The rep replied immediately and asked me for step by step details on how I am accessing the site. I responded with the step by step instructions, and they resolved the issue quickly.

4:15pm: Problem solved!

Friday, November 20, 2009

Formatting Note Lines with GP Web Services

When importing customer notes into GP 10 via Web Services, a client discovered that typical 'new line' escape sequences were not working properly for Notes. It turns out that Web Services handles formatting differently than eConnect, at least when it comes to new lines in Notes.

With GP 10 Web Services, \r\n, \r, and \n (or vbCr, vbLf, or vbCrLf in Visual Basic) all produce the same results in notes:

Note the vertical bar at the beginning of line 2 and 3. This is a line feed character, which GP Notes do not like.

In eConnect, you can use \r or vbCr to produce a new line, but in Web Services, any of the new line characters are being transformed into a CR and LF.

The solution to this is to use the UTF-8 representation of CR: & #13 ;

.Notes = "Note line #1 & #13 ; Line #2 & #13 ; This is line #3"

(In the above examples, I have added spaces between the characters so that they aren't parsed out of the blog post as HTML. When you use them, you will remove the spaces between the ampersand, pound sign, and before the semi-colon.)

With this change, the notes are imported with proper line breaks, but without the extra vertical bar characters.

And there you go!

Thursday, November 19, 2009

Don't be a Propellerhead

I like technology. I like computers and software. I like that they are constantly changing, meaning that there is always something new. I like the innovation, which brings new concepts and ideas to the marketplace and gives me endless opportunities to learn new things. It's constant work to continuously learn and stay current, but I'm certainly never bored.

Sometimes innovation brings simplicity and time savings. But sometimes innovation occurs because there are complex problems that require complex solutions to solve. When those complex problems occur, it's great to have a solution that is suited for the task.

But I would argue that many business issues, even if unique, are fundamentally not terribly complex. In the Dynamics GP marketplace, we aren't designing terrain mapping radar, modelling global weather patterns, designing a jumbo jet, or creating new pharmaceuticals. We're helping businesses meet business requirements, fulfill obligations, make promises, and manage information related to their accounting, inventory, sales, payroll, and purchasing, among other things.

Sure, things like sales commissions can become a tangled mess because of convoluted incentive programs, but ultimately they come down to fundamentally simple principles that use basic math to perform calculations on basic data sets.

The vast majority of the customizations, automations, and integrations that I provide to clients are fundamentally pretty simple. Import customers. Display estimated shipping weight of an order. Allow the customer to manage their item costs. Automate the creation of a project. Import journal entries.

Most of the individual Dynamics GP projects I work on are under 40 hours. They just aren't that complex, and they generally don't need complex solutions. My concern is offering a reasonable estimate, getting the project done on time and under budget, and exceeding the customer's expections. Using cool new tools or technologies is not one of my primary concerns.

When designing and developing these solutions, I have many tools at my disposal. SQL Server, Modifier & VBA, VS Tools and .NET, eConnect, Web Services, Dexterity, SQL Reporting Services, Excel, SmartList Builder, Extender, and the list goes on. And each tool can usually be used in several different ways. In the case of SQL Server, I can create a query, a view, a stored procedure, a trigger, an SSIS package, a user defined function, or even use CLR.

Sometimes there is a particular design approach that is obvious, and is clearly the best for the given requirements. But oftentimes, there are two or three, or many different design options that might work, with no obvious winner. In those cases, you have to make choices based on the information you have available.

What are the stated requirements? What is the goal or intended outcome of the solution? Who will be using the solution and what is their skill level? How often does it need to be used? Is the solution temporary or will it be used long term? what are my skills and competence with the tools at my disposal? What is my budget? When does the solution need to be delivered?

These and many other factors usually play into your design choices, assuming you are putting the client's concerns first.

But a several times now, I have inherited projects from other contractors, consultants, or firms that, I believe, did not put the client's concerns first. In my humble opinion, they were "propellerheads", making choices based on technological 'cool' factor rather than business concerns.

One extreme example was a simple eConnect integration that had an impressive user interface, with lots of tabs and controls and status fields. It even had fancy graphics with their consulting company logo prominently displayed on the user interface. (Since I don't spend alot of time on elaborate user interface design in .NET, I honestly couldn't figure out how they did the graphics). It looked great. But there was a funny thing about the consulting firm that developed pretty program. The client had fired them.

Why? The project was over budget and the integration didn't work. It couldn't successfully import a single transaction into GP.

So the GP partner for the client calls me and asks me to fix the integration. When I look into the .NET code, I see lots of user interface, and a fair amount of code, but I see things like distribution lines where the DR and CR are being set to the same account number. And I saw hard coded values, such as GL accounts. And invalid SQL statements that weren't properly querying the source data. And writing XML out to files instead of using a Memory Stream.

This particular project demonstrated many forms of incompetence, but it also demonstrated that the developer spent too much time on technical details that didn't matter, and too little on the business concerns that did matter.

Another example was a project that required a single window that would allow the GP client to categorize their HR training courses into groups. A single window used by maybe 3 GP users. I inherited a Visual Studio solution with multiple projects, lots of classes, and a 3-tier architecture. What was going on in the mind of that developer that they thought they needed all of that technology for such a simple business requirement? Did they used to work at NASA or JPL? To give you an idea of what was delivered to the client, the installation instructions for the application were 14 pages long. Did I mention it was ONE WINDOW that worked with two SQL tables? After having it for a few years, the client had never implemented it. Why? It was never completed and did not meet their original business requirements. And, maybe you're seeing the theme already: The consulting firm that developed the solution was fired.

Finally, I recently helped a client modify an eConnect integration that they had been using for a few years. When I looked at the .NET code, I had to call a friend to do a code review with me to make sure I wasn't imaginging things. The integration used inheritance on every eConnect class, reproducing every eConnect class in a custom class, which then ended up in a class library, which then ended up as a reference in the integration project. And what was the wild and crazy business requirement that produced this over engineered design? Importing journal entries. Yup, 34 megabytes (zipped!!) of project files and supporting files, all to import a journal entry.

As far as we could tell, not a single practical feature of inheritance had actually been used, but the eConnect objects went through so many classes and transformations that it was nearly impossible to trace through the code. Many convenient features of eConnect and its serialization libraries had effectively been re-written. Literally thousands of lines of code in the entire project was re-written in a single subroutine with just 275 lines in one hour. No inheritance required, and using a single project. And, here's a shocker for you: The original project was significantly over budget, and the client will never use that consulting firm again.

So the next time you work on a project and start to get excited about using a new tool or technology, please ask yourself: Are you being a propellerhead?

Monday, November 16, 2009

Dance With The One Who Brung 'Ya - Correcting Transactions in Dynamics GP

So often it seems like many of the questions on the Dynamics GP forums deal with how to correct transactions. Or, even more often, how correcting a transaction has led to more issues. As a trainer, I teach my students to "Dance with the one who brung 'ya" which is my way of emphasizing the importance of correcting transactions in the module where they originated from. And when I am lucky enough to find myself on phone support duty for the day, I always end up with a few "how do I fix this or that" cases. So I always ask my standard questions..

  • What steps have been taken so far? Often times, I find that attempts to correct the issue (sometimes even multiple posted corrections) have contributed to the current issue.
  • What is the current state of the issue? What remains to be corrected (e.g., the GL is incorrect but the RM aging is correct)? Getting to this bottom line can often be helpful in solidifying the real issue.

But we want to avoid these issues in the first place, right? So getting back to the dancing. Here are the common correction points that I emphasize to students and users, to point out the importance of correcting transactions where they originated.

Sales Order Processing

  • To correct a posted invoice (Transactions>>Sales>>Sales Transaction Entry).
  • Enter and post a return (Transactions>>Sales>>Sales Transaction Entry).
  • Updates inventory (if applicable), sales history by item, receivables management, and general ledger.
  • Do not void using Receivables Management (Transactions>>Sales>>Posted Transactions), as this will only update the general ledger and receivables management.

Purchase Order Processing

  • To correct a posted receipt or invoice (Transactions>>Purchasing>>Receivings Trx Entry, Enter/Match Invoices).
  • Enter and post a return (Transactions>>Purchasing>>Returns Trx Entry).
  • Updates inventory (if applicable), purchasing history by item, payables management, and general ledger.
  • Do not void using Payables Management (Transactions>>Purchasing>>Void Open), as this will only update the general ledger and payables management.

Project Accounting (Employee Expenses, Miscellaneous Logs, Timesheets, and Equipment Logs)

  • To correct a non-purchasing transaction (purchasing transactions are corrected in POP per the example above), return to the original entry window (Transactions>>Project>>Timesheet Entry, Employee Expense Entry, etc).
  • Enter a Referenced type transaction and use negative quantities to decrease the original posting. You can use the PA Trx Adjustment tool to assist with this process (Transactions>>Project>>PA Trx Adjustment). Using either method will result in an update to project accounting, payables management (if applicable), payroll (if applicable), and the general ledger.
  • Do not correct the transactions in payroll, or by voiding the transaction in payables, as these methods will not update the project accounting module.

Well, that's it for tonight. I promise that my next post will get in to the finer points of correcting project accounting billings and inventory transactions, which have more variations than the examples I have provided above.

But, in the meantime, please share any tips and tricks you have gathered over the years to assist users in understanding how to dance with the one who brung 'em :)

Friday, November 6, 2009

No Longer (Still) a Mystery: Invalid column name 'ACTNUMBR_6' when creating TWO / Fabrikam

(Update: Reader Jessica experienced the same issue, and provided feedback indicating that using Run As Administrator for GP Utilities should resolve this issue.)

So while installing GP 10 SP4 for the millionth time on a new development server, I ran into these four fantastically fun error message while trying to create the TWO company:

The following SQL statement produced an error:
declare @MXNUMSEG int, @Counter int, @AcctSQLCmd varchar(500), @StrLinked char(20), @StrNotLinked char(20) select @Counter = 0 set @AcctSQLCmd = space(1) set @StrLinked = '''Linked''' set @StrNotLinked = '''Not Linked''' select @MXNUMSEG = MXNUMSEG from DYNAMICS..SY003001 while @Counter <> @MXNUMSEG begin select @AcctSQLCmd = @AcctSQLCmd +',' +'GL00100.ACTNUMBR_' +ltrim(str(@Counter+1)) set @Counter = @Counter + 1 end exec('create view AAG00200FL as SELECT GL00100.ACTINDX'+@AcctSQLCmd + ', GL00100.ACTDESCR, GL00100.ACCTTYPE, GL00100.ACTIVE, AAG00200.aaAcctClassID, AAG00201.aaAccountClass

ERROR [Microsoft][SQL Native Client][SQL Server]Invalid column name 'ACTNUMBR_6'.

The following SQL statement produced an error:

ERROR [Microsoft][SQL Native Client][SQL Server]Cannot find the object 'AAG00200FL', because it does not exist or you do not have permission.

Whoa there! That's quite a mouthful.

Since I've had many, many, many random errors, crashes, and problems occur over the years when creating the TWO / Fabrikam database, when I see an error, I just roll my eyes, then re-launch GP Utilities, and have it resume the database setup, or recreate TWO. Well, I tried that approach, but after the 3rd time, it was clear that this wasn't your average TWO setup failure.

I tried resuming, I tried re-creating, and I tried deleting both the Dynamics and TWO databases and starting from scratch, to no avail. But despite what I tried, I noticed that at least the error was consistent.

As an additional test, I tried to create a new empty company to see if it was something about TWO, or if it was a more fundamental issue. But sure enough, even creating a new, blank company produced the same errors.

So I finally resorted to SQL Profiler so that I could capture the entire SQL statement that was causing the initial error. The relevant portion is:

while @Counter <> @MXNUMSEG

So the SQL first queries the number of GL segments from the SY003001 table, and then performs a loop based on the number of segments.

So why was the statement later failing to find the column ACTNUMBR_6? When I launched GP Utilities, I specified an account framework of 66 characters and 10 segments, so there should be nothing special about segment 6.

But when I queried GL00100, to my surprise, there were only 5 account segments in the TWO database, which explains why the statements were failing.

Since I've installed GP tons of times and never had this problem, I didn't know where to start. Several more times I tried deleting Dynamics and TWO then launching GP Utilities to set them back up. During those subsequent tries, I figured that there are only two things that could possibly be affecting the segment field setup and causing the issue.

First, I was entering the account framework, specifying max length of 66 characters and 10 segments. The second item was that I was electing to not sort by account segment. Neither of these settings should cause the install to fail, but I couldn't see what else it could be.

So I then tried 60 and 10, with no segment sorting. No luck. I then tried several permutations of 60 and 10 with and without sorting, and 66 and 10 with and without sorting.

At some point, after over a dozen attempts and having completely lost track of what I had already tried, for some reason, it suddenly worked. TWO setup completed successfully.

Puzzled, I tried deleting both Dynamics and TWO, and ran GP Utilities again with my original options: 66 and 10, with no segment sorting.

But, once again, the TWO setup was successful. No errors.

From this, I can only assume that the account framework and segment sorting may not have been the cause, or may have only been one of several factors that was contributing to the error.

I wish I knew what the cause was, but if I never see that error again, I won't complain either.

During my testing, I noticed something that gave me at least a tiny bit of insight into the process of how TWO is created, and what else may have contributed to the problem.

When the GL00100 table is first created in TWO, it contains only 5 segments. But a few steps later in the setup process, apparently a separate script alters the table to insert the additional 5 segment fields. Based on this sequence, one theory is that the alter script was failing, being skipped, or wasn't completing successfully for some reason--but no error message occurs. If that alter script is not run successfully, when GP Utilities tries to create some analytical accounting views, the create view scripts will fail.

And with that nail biter of a story, have a good weekend, and I'll hopefully see some of you at the Dynamics GP Technical Conference in Fargo next week!

VBA Error: Class not registered. Looking for object with CLSID:{AC9F2F90-E877-11CE-9F68-00AA00574A4F}

I know, I know, it's such an obvious error, right? I mean, really, is there anybody left in the Dynamics GP world that wouldn't scoff at you for asking about this obvious class ID?

So I recently created a new development virtual server for a new project, something I've done dozens of times without issue. But for some reason, this time I ran into a few strange problems with Dynamics GP. One of them was this VBA error:

This was on a fresh Windows Server 2008 x64 virtual server, SQL Server 2005 SP3, and Dynamics GP 10 FP1, with SP4 installed. I did not have Visual Studio or Office installed, which I'm pretty sure is the reason for the error.

The error occurred whenever I tried to import a user form file into VBA, or whenever I tried to insert a new user form.

I later found that this error is apparently a variant of the one described in MBS KB Article 961568:

"Errors occurred during load" or when you import a package file that has a user form: Class {C62A69F0-16DC-11CE-9E98-00AA00574A4F} of control frmXXX was not a loaded control class.

At the time, I knew that this likely meant that a DLL wasn't registered on the server, but although those lovely GUIDs are a joy to see, they convey nothing to the average human as to which DLL is not registered.

As a shortcut, I switched over to a server that I knew did not have any problems with user forms in VBA and searched the registry for that GUID. That search told me that the problem was that the FM20.dll file was not registered.

After Googling for that file name, I learned that FM20.dll is a file needed to allow VBA to work with windows forms. This lovely KB article describes it's usage, but emphasizes that FM20.dll is "NOT redistributable" (they had to make the NOT all caps and bold for those of us who were unclear and confused about the lowercase non-bold version of the word 'not'), and that it can only be obtained by installing one of several Microsoft applications that happens to distribute it.

It must have taken a team of high priced lawyers thousands of dollars of billable hours to come up with that brilliant scheme that would surely prevent someone from copying a single DLL file and manually registering it. Looking to avoid the wrath of those same summer intern legal scholars, I did not simply copy the DLL from another machine and register it on my new server. Instead, I used the link in that same KB article to download the ever-so-popular "Microsoft ActiveX Control Pad", which installs the much coveted FM20.dll for me.

(Of course, the MBS KB article says to simply copy the FM20.dll file from an MS Office CD. Skirting the rules, are we, MBS?)

And, with that, the error went away and I was able to insert user forms and import form files into my VBA project.

One note--if you are working on Windows Server 2008 x64, the 32-bit file is installed in the C:\Windows\SysWOW64 directory.

Wednesday, November 4, 2009

Emerging from the Curriculum Caverns

So, I have to start out with an apology of sorts, as I have been heads down for the past 6 weeks working on curriculum updates for Microsoft. So this has become an orphan blog, which Steve so gently reminded me of. So here I am to give you a bit of an update and share what has been keeping me away for so long. I have been updating courseware for the upcoming release of Microsoft Dynamics Sure Step 2010, primarily working on the courseware forProject Managing Microsoft Dynamics® AX, Microsoft Dynamics® GP, and Microsoft Dynamics® NAV Implementations with Microsoft Dynamics® Sure Step (which will be course 80199) and also doing some support around the Using Microsoft Dynamics Sure Step course (80047).

The Project Managing course is a case study based course, which I think is a great collaborative learning experience with students participating in the implementation of a fictional company and working with many of the key tools, templates, and guidance provided in Sure Step. I have experience teaching the course on earlier releases, and I found the shift in perception very interesting. Students often arrived in class aware of Sure Step and its capabilities, but unaware of how to apply the capabilities to their implementation process. After two days with the case study, however, the possibilities became clear and students would leave excited to take what they had learned and apply it practically (and quickly). As an instructor, I love to see that sort of return on the training investment!

The Using Sure Step course is meant to serve as a more general Sure Step training, great for those that need the nuts and bolts approach to understanding the components and benefits of the Sure Step model. This is the courseware we have used internally to train our team members, and I find myself referring to it regularly when I need to refresh my own memory :)

Being the geek girl that I am, I have to say that I came out of the project with a (even for me, an enternally enthusiastic person when it comes to new releases) surprising level of anticipation for the new release. For those of you not yet working with Sure Step, it presents a variety of additional touchpoints to engage you that I hope you will find immediately applicable to your business. And for those of you already working with it, I think you will find that Microsoft listened to the feedback and incorporated more tools and guidance to allow you to take the tool even further in to your implementation processes.

Some key new features (borrowed from the US Partner Executive Program News for Microsoft Dynamics - October 2009):
  • Industry and Cross-Product Content - Positioning and deploying Microsoft Dynamics solutions in a given Industry or its related segments. Industry Pilots for AX Process Manufacturing and CRM Public Sector including the launch of an Industry Playbook for solution selling. Also available will be guidance on xRM platform due-diligence and deployment.
  • Agile Project Type – The addition of this project type which will be especially suitable for implementations where the Microsoft Dynamics solution is positioned as a platform rather than as a commercial-off-the-shelf (COTS) system.
  • Organizational Change Management Discipline – New guidance on managing organizational change, to address this common challenge in any ERP or CRM deployment.
  • ISV Guidance – New content that applies to ISV solution positioning and deployment and engagement with the VARs and SIs during sales and implementation. Also, introducing Certified for Microsoft Dynamics alignment recommendations.
  • More collaboration and project tracking capabilities through enhanced SharePoint integration based on Sales, Implementation and Optimization engagements.
  • Enhancements to the Diagnostic Phase including a link between Sure Step and Unleash Your Potential, an Industry Playbook to assist the Sales Roles focused on selling Microsoft Dynamics solutions catered to a specific industry/vertical, a consolidated set of Decision Accelerator Offerings, estimation tools and an enhanced ROI calculator.
  • Enhancements to the Optimization Offerings including further alignment and integration to Support and Services from Microsoft and additional proactive and post go-live offerings.

If you are interested in checking out the upcoming release, I encourage you to check out the latest information available on PartnerSource under Partner Essentials>>Sure Step.

Also, don't forget about these resources for Sure Step as well:

I would love to hear back from those of you who are using Sure Step, thinking about using it, or wondering how/if it can apply to your organization and implementation practices!

In the meantime, I promise more frequent posts in the upcoming couple months (at least until my due date, 1/7/'s a girl for those of you who don't already know).

Take care,


Tuesday, November 3, 2009

Creating a Dynamics GP DSN on 64-bit Windows

aka "Why isn't my DSN showing up in GP? It's right there in the Data Sources window!"

This is one of those things that I am familiar with, but since I haven't run into it in a long time, I had to do some digging to find specifics of the solution. I'll post it here in case anyone else has the issue for the first time, and for myself when I forget again.

So far, I have used 32-bit Windows for my development virtual servers, but Windows 2008 R2 is only available in 64-bit, so I'll probably be using 64-bit Windows going forward for any new virtual servers.

As you know, Dynamics GP requires an ODBC Data Source Name (DSN) to be setup on the Windows machine where it is installed. It uses this to connect to SQL Server, and is usually very simple to setup.

But, if you install GP on a 64-bit version of Windows and manually setup a DSN, when you try and launch GP or GP Utilities for the first time, the "Server" drop down list will be blank. You'll then go back and make sure you aren't imagining things and confirm that you did setup the DSN.

If you setup the DSN by navigating to Administrative Tools --> Data Sources, you actually went into the 64-bit ODBC Administrator tool. GP is only able to 'see' 32-bit System DSNs, and will not see the 64-bit System DSNs.

In order to setup the DSN for GP, you will need to launch the 32-bit ODBC Administrator tool. The two windows appear to be indistinguishable, with no visible differences between them.

Here is a Microsoft Support KB article discussing the two versions.

From what I have found, you have to launch it by navigating to:


Confusingly, the 64-bit and 32-bit versions of the tool have the same file name, which includes "32". Yet the 32-bit version is in the SysWoW64 folder. Crystal clear, right?

Once you locate the file, I would recommend creating a shortcut to it on the desktop or start menu, or somewhere else where it will be pretty visible. You should also consider naming it something like "32-bit Data Sources" to differentiate it, and might also want to rename the Data Sources icon under Administrative tools to "64-bit Data Sources" to further reduce confusion.

Once you launch the 32-bit Administrator tool and setup your DSN, it should show up in GP and GP Utilities.

And for those that are wondering why I don't just use the DSN setup option that is part of the GP 10 installation, I tried using it when GP 10 was first released, but it didn't work for some reason and gave me errors, so I haven't tried it again since. Since it only takes a few seconds to set up a DSN, I just continue to do it manually, which also gives me the opportunity to test it and make sure it is working properly and that I didn't mistype anything.

Lastly, as the MS KB article suggests, if you need to use both 64-bit and 32-bit DSNs, consider naming them with 32 or 64 in the name to differentiate them.