Don't you go closing my year...

Just thought I would send out a gentle reminder of the major myths regarding year end closing in Dynamics GP. As I find they surface every year, even among users who have used the system for years. So here is my top five list, please post your comments and I will update the list accordingly.

I have to wait until I have ALL my adjusting entries before I can close the GL
This is the biggest myth of all. You can actually have your cake and eat it too. GP allows you to post to the most recent historical year. So, for example, if I close 2009 I can still post adjusting entries to it. I just can no longer post adjustments to 2008. The two requirements to post to the most recent historical year are that you must mark "allow posting to history" in GL setup (Tools>>Setup>>Financial>>General Ledger) and the fiscal period must be open in Fiscal Period Setup (Tools>>Setup>>Company>>Fiscal Periods).

I am running my general ledger year end close, and it is stuck
I promise you the odds are that it is not stuck. The year end close is a pretty intensive process, so it is fairly common for a workstation to show as not responding. Please be patient. Using Ctrl-Alt-Delete to cancel out of the process is NOT recommended, and will REQUIRE you to restore a backup. And if you don't have a backup, well, that is just not fun...and will involve a nice little database fixing service from Professional Services at Microsoft. So, two lessons: have a backup and be patient.

I can view all my audit entries because I set up an audit period
This is a popular trick, to set up a 13th period in your Fiscal Period setup with the same date range as the December period. You then close December, and with the 13th audit period open, transactions will post in to that audit period. Sounds brilliant right? Well, it is...as long as you never run financial reconcile (Tools>>Utilities>>Financial>>Reconcile) on that year. As it will reattribute the postings to the earliest period with the same start date (assuming both periods were closed when you ran reconcile) or all postings (including your original Dec postings) will be attributed to the open period (if the 13th is open, and the 12th is closed). Ugh. Better choice? Set up a source document code (Tools>>Setup>>Posting>>Source Document) called AJ for Adjusting Entries and select it whenever you enter an adjusting entry instead of GJ. Then use the Cross Reference by Source Document report to view your AJs for a time period (Reports>>Financial>>Cross Reference>>Source Document).

Fixed Assets year end is so easy, I just change the year on the book right?
Nooooo! You must run the fixed asset year end closing routine (Tools>>Routines>>Fixed Assets>>Year End) in order to close the year properly. Changing the year directly on the books themselves is not recommended and will require you to restore a backup if you process transactions in the new year if you do so. Also, keep in mind, Fixed Assets is one module where you must complete all activity for the prior year and close the year BEFORE you can process activity in the new year.

I can close Receivables and Payables whenever I get around to it
Unfortunately, the payables management and receivables management year end closes are not date sensitive. This means that you need to run them as close to the actual end of year (after posting as much of the prior year activity as possible, but before you have posted to the new year) to get the most accurate close as possible. Now, the good news is that these closes only impact the "Amounts Since Last Close" summary figures for current year -vs- last year that are tracked for reports, inquiries, and Smartlist. The close will move all posted transactions to last year (regardless of their actual date) for the summaries. And the current year bucket will start over with any transactions posted after the close, again regardless of their actual posting date.

For example, let's say that on 1/1/2010 I post an entry of $500 to payables and date it 1/1/2010. I then close the year for payables on 1/2/2010. That $500 will be moved to the last year column in the "Amounts Since Last Close" summary figures even though it was posted to the 1/1/2010 date simply because it was actually posted BEFORE the year end close. By that same logic, let's say on 1/3/2010 you enter another payables entry for $1000 and post it back to 12/20/2009. Since the close has already been performed, that $1000 will be tracked in the current year column for the "Amounts Since Last Close" summary figures.

For most folks, these amount to minor differences that they generally do not notice. However, keep in mind that if you are looking at a vendor or customer summary field in SmartList, it will be using the "Amounts Since Last Close" summary figures. And if you are looking at current year -vs- last year, there may be discrepancies for the actual detail.

Well, there is my mythbusting. Please share your own myths, and how you like to bust them :) Not sure how many more blog posts I will make before the little one arrives (January 7th is coming quick) so I will go ahead and wish everyone happy holidays!

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!

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!

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?

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 :)

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:
GRANT SELECT , INSERT , DELETE , UPDATE ON [dbo].[AAG00200FL] TO [DYNGRP]

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:

select @MXNUMSEG = MXNUMSEG from DYNAMICS..SY003001
while @Counter <> @MXNUMSEG
begin


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.