I'm Hungry. For GP.

So I read about Mark Polino's new Microsoft Dynamics GP 2010 Cookbook through several of the GP blogs, including, naturally, Mark's own DynamicAccounting.net blog which does a great job of highlighting tasty morsels of GP goodness cooked up by MVPs and other top GP chefs.

As soon as I heard about the book, I got in touch with the good folks at Packt Publishing and pleaded for a review copy as soon as it was available. 

Unfortunately, I was a little bit ambitious.  Between the in-laws visiting from China and my wife getting ready to have our second child in the next few weeks while my two year old demands more Dora,things have been hectic, to say the least. (Insane, perhaps?)  I received the book, all shiny and crisp, but it had to sit on my desk for a few weeks until my sanity levels returned to normal.

So, finally, at 4:30am this morning, after I dropped off the in laws to go on a Las Vegas + Grand Canyon tour, I figured it was the perfect, and rare quiet opportunity to finally give The Cookbook the time that it deserved.

First, the "Cookbook" title is excellent, as it is written from the perspective of the user who wants to use GP.  It isn't a dry or tedious reference manual that needs to be studied and digested (pun!).  It's a book with great suggestions on actually using GP, getting the most out of GP, and pointing out those features that many users never knew existed, but would make their lives so much easier.

For example, have you configured My Reports?  Or are you still wasting time navigating through the Reports menu while taking a call on your rotary phone?

And did you know that there is a way to fix AutoComplete errors without having to clear all of the AutoComplete entries?  Seriously?  I just learned about that from The Cookbook, and honestly I'm a bit embarrassed.  With all of the repetitive entry I have to do for testing, and with all of the mistakes I make doing that data entry, I need this feature!

Hmmm, do you know how to quickly and fully create new inventory items without having to go into the 5 or 6 windows required to fully setup a new item?  Page 69 has step by step instructions on how and when to use this feature that, trust me, will save a lot of time.

Are you wasting time waiting for data to export from SmartLists into Excel?  Pages 138 - 154 offer three great ways to bypass SmartLists and get your data directly into Excel, which is especially powerful for users who regularly need to extract thousands of records out of GP.  I've had clients that regularly exported over 100,000 records from GP for analysis in Excel, so whenever I see someone try and export more than 1,000 records, I definitely recommend one or two of the approaches Mark has laid out in detail in his Cookbook (and I just learned about a new one!).

And to wrap up my early morning review, I see that The Cookbook dutifully covers how to use the Reconcile to GL feature.  I can't count the number of times I've received calls from clients who discovered that their subledgers don't tie to their trial balance.  They don't know where to start, or understand how to perform the subledger reconciliation in GP.  This is a process that every consultant or accounting manager should be familiar with.

Even if you think you know everything about GP (in which case you are fooling yourself), this book at a minimum reminds you of many features and functions that you may have forgotten or not used in a while.  Collectively, the valuable and concise content on the 297 pages will provide value to you and your organization.

If you have invested in Dynamics GP, you've already spent thousands, or tens of thousands of dollars in software, consulting, and training.  And I'll bet that the Microsoft Dynamics GP 2010 Cookbook will humble you once you realize how many useful features there are in GP that you haven't bothered to use or that you never even knew about.  It's well worth the investment and should be standard issue for any consultant or GP customer.   

In fact, for you consultants out there, it would be a great gift for your clients, and a great excuse to visit them and have a conversation to discuss if they are using GP as efficiently and effectively as possible.

Hats off to Mark for putting together such a valuable handbook for the GP community!

Code Review: Catching Unhandled Exceptions in .NET

This is a post about a very obscure topic that I normally would avoid, but since I spent the last several days dealing with it, I thought I would share the love.  If I ran into this problem with a very simple .NET eConnect integration, anyone could run into it.

I recently deployed three eConnect 9 imports at a client site, just like I've done with dozens and dozens of .NET eConnect integrations.

The client uses Citrix, and has two load balanced application servers for Dynamics GP.  To install my three imports, we had to install them on both of the Citrix servers.  The installs went fine, the apps all appeared to work well, and I thought my work was done.

Until the key business user launches one of the imports and gets an error.  The app doesn't launch, no windows appear, she just sees a "crash" error message.

After further testing, we find that the app launches fine on Citrix server #1, but gives her the error on Citrix server #2.  Exact same app.

I then check Event Viewer and see some arcane error messages related to the application crash.  They are basically generic .NET errors that don't provide any guidance as to the cause of the crash.

EventType clr20r3, P1 invoiceimport.exe, P2 1.0.2.0, P3 4c374d29, P4 mscorlib, P5 2.0.0.0....

I find out that these are very generic .NET errors that are occurring because I do not have an error handler at the very beginning of my application code.  After further research, I learn about "Unhandled Exceptions".  Although it is possible to handle most exceptions with a Try Catch block in the main program code, there is also a dedicated Unhandled Exception event handler. 

My current interpretation is that this is one way to handle exceptions for situations where you don't have a Try Catch block, or where an error occurs during initialization, such as referencing a DLL or external assembly.

        static void Main()
        {
            AppDomain.CurrentDomain.UnhandledException += new UnhandledExceptionEventHandler(CurrentDomain_UnhandledException);
           
            Application.SetCompatibleTextRenderingDefault(false);
            Application.Run(new MainForm());
        }

        static void CurrentDomain_UnhandledException(object sender, UnhandledExceptionEventArgs e)
        {
            Exception ex = (Exception)e.ExceptionObject;
            MessageBox.Show("An unexpected error (unhandled exception) occured trying to launch " + Properties.Settings.Default.appName + ": \r\n\r\n" + ex.InnerException.ToString());
        }

So in my case, the goal was to at least return some type of detail about the cause of the crash.  After installing the new version with the UnhandledException handler, we received this message.

Certainly better than the event viewer message, but still pretty cryptic.  Since I have become more familiar with the .NET exception messages, I knew to start at the top and work my way down, or just look at the InnerException.

So I focused on the System.Drawing.Icon.Initialize error.  My apps are basic business apps and aren't doing anything special with icons, so this didn't mean much to me.  After quite a few Google searches, I stumbled across an MSDN Forum post that described the same error.  The resolution in that case was to replace the icon on a window.

Seriously?  Since all three of the applications used the same icon, and since the exact same app worked on the other Citrix server, I was pretty doubtful that replacing the icon would solve the problem.  But it's a  simple fix to try.  I removed the icon from all of the forms in my application, saved and closed Visual Studio, then recreated the icon file using an icon editor, and then added the icon back to the forms.  I then recompiled the app.

Today we installed the new version of the import, and voila, it worked.

It still doesn't make any sense to me, but it is nice to be able to resolve such a random and obscure error for once.

401k Match, Make Sure You Get Your Money!

In recent versions, the ability of Dynamics GP to handle the most common 401(k) match scenarios has improved tremendously. But there are several pieces involved, so I thought it might be helpful to list them out here.

Take the example of a 50% match up to 5%. The employer matches 50% of the employee's contribution up to 5% of their gross wages. To accomplish this, you will need to set up both a deduction (to capture the employee contribution) and benefit (to capture the employer match).

First, set up the deduction (Microsoft Dynamics GP>>Tools>>Setup>>Payroll>>Deduction). It is important that you configure the deduction with a "Percent of Gross Wages" calculation method. And don't forget to set the annual maximums as applicable.

Next, set up the benefit (Microsoft Dynamics GP>>Tools>>Setup>>Payroll>>Benefit). Here is where the setup gets a bit tricky. First, you will want to set the calculation method to "Percent of Deduction" and enter the match percent (in our example, 50%). Then, in the bottom left hand corner make sure the Based On field is set to "Deduction". Set the radio button to "Selected" and then insert the deduction code you set up previously. Enter the 5% match maximum in the Employer Maximum field. Viola! You are done.

A few notes:

• You must use the Percent of Deduction method on the benefit for the Employer Maximum field to be available.
• For the Employer Maximum to work, the benefit must be based on a deduction that uses the "Percent of Gross Wages" calculation method.
• Remember to limit the Based On for the benefit to only the 401(k) deduction.
• Remember to include both the deduction and benefit in your payroll build.

Hope this helps clarify the setup for anyone needing 401(k) match tracking in GP. Feel to post your own tips, tricks, or questions.

Happy set up!

Christina

Lesson in Code Re-Use: Vendor Item Number Error Importing POP Reciepts Using eConnect 9

UPDATE:  I found the problem, and it was the guy sitting at my keyboard!  It turns out that I had re-used some code from a prior PO receipt import, and that code was the source of the problem.  The old code was designed to read PO receipt XML source data files that did not contain the vendor item number.  The code was dutifully using the vendor ID and item number to query GP, get the current vendor item number, and then update the XML with the current vendor item number value just before sending it off to eConnect.  So that explains why the vendor item number value was changing from the time I validated it, to the time eConnect was validating it.  D'oh!

Nothing to see here folks, please move along...

I've spent the last puzzling hour trying to figure out why I am seeing strange errors with a POP Receipt import that I've developed using eConnect 9.

When importing a particular receipt, eConnect returns an error saying that the vendor item number was invalid.

Okay, no problem, I added some validation to check the vendor ID, item number, and vendor item number of the receipt data so that a validation error would be logged in case there was a discrepancy.

But after adding my validation, the receipt data validated successfully, sent the receipt on to eConnect, and eConnect once again returned the same vendor item number error.

Thinking that my validation code was somehow being skipped, I added detailed logging to try and see how the validation code was being skipped.  To my surprise, I saw that it was not being skipped.  It was running, validating the receipt, and the data appeared to be valid.

Validating receipt 234376027 Item 5948-267764
Validating vendor item for PO PO100585, Item 5948-267764, Vendor Item D81639D
--Vendor item IS valid

I then logged into GP and checked the PO, and sure enough, the PO showed the same item number and the same vendor item number.

Puzzled, I then went back to the eConnect error.  After looking at it more closely, I saw the oddity.

Error Number = 9343  Stored Procedure taPopRcptLineInsert  Error Description = Item/vendor item/vendor combination is not available to receive from the PO Line                          
POPTYPE = 1
POPRCTNM = 234376027
PONUMBER = PO100585
ITEMNMBR = 5948-267764
VNDITNUM = 296059816397
VENDORID = GPINGRAM


Notice anything fishy?

Notice how the vendor item number in the eConnect error data does not match the vendor item number that I validated?

I then looked up the vendor item number record in GP, and sure enough, it showed 296059816397, and not D81639D.

From what I can tell at this point, eConnect  my old code! is replacing the vendor item number in my receipt transaction with the current vendor item number setup in GP.  Because this current vendor item does not match the vendor item number on the PO, the import is failing. 

In GP 9, I am able to manually enter the receipt fine by using the vendor item number from the PO, so this is obviously a problem with my code!  behavior appears to be an "issue" with eConnect 9.  If I need to change my vendor item number, I don't want to invalidate outstanding POs, so this behavior seems puzzling.

I don't know if this behavior is the same with eConnect 10, and unfortunately probably won't have the time to test it and find out.

I'm now having to figure out if there is a workaround for this.  Never a dull moment!


Steve Endow is a Dynamics GP Certified Trainer and Dynamics GP Certified Professional.  He is also the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

http://www.precipioservices.com

Speedy Speedy Payroll Entry

I have been in the midst of a payroll implementation, and led a training yesterday that got me to thinking about payroll quick entry tips. So here are some of my favorite ways to speed up the entry of payroll transactions.

#1. Payroll Mass Transaction Entry (Transactions>>Payroll>>Mass Entry)

I think this has to be one of the most underutilized windows in Dynamics GP. Enter or select a Batch ID to be used for the transactions. Then select a pay code, deduction, or benefit and enter a date range and range of employees by employee ID, class, department or position . You can enter an amount if applicable, or leave it set to zero and you can edit them individually in the standard payroll transaction entry window. You can then preview the transactions to be created (the system will only create transactions for employees in the range who are already assigned to the selected code) and delete the transactions that should not be created. In the Preview Mass Entry Transactions window, click Build Batch to create the transactions. You can then edit the transactions, including the amounts using the standard Payroll Transaction Entry window.

What can you do with this? Create holiday transactions for all of your salaried employees, create commission transactions for everyone in the sales department, and even create a year end batch of auto allowance benefits for employees in the manager position.

#2 Recurring payroll batches (Transactions>>Payroll>>Batches)

By default, when you set up a transaction batch, the frequency is set to Single Use. This means that once the batch is printed and posted in a payroll, it will disappear. But pick a frequency other than single use, and the batch will recur after posting with updated dates based on the frequency. You can then edit the batch and make any necessary changes before the next payroll. This is perfect in situations where the amounts may not change from month to month, or there are only minor changes. For example, deductions for uniforms, benefits for life insurance premiums, or regular bonus payments.

#3 Data Entry Defaults (Transactions>>Payroll>>Transaction Entry>>Data Entry Options)

Ever wonder what the Data Entry Default option on Cards>>Payroll>>Deduction (and Setup>>Payroll>>Deduction) means? Well, it comes in to play quite nicely on pay codes, deductions, and benefits that require transactions (in the case of benefits and deductions, the Transaction Required option must be marked). In Payroll Transaction Entry, click on the Options button in the Data Entry Options section and choose "Use Data Entry Defaults" and select which records to default. Then when you select an employee in the Payroll Transaction Entry scrolling window, the records that are marked as data entry defaults will automatically appear. This is a great option when you generally enter the same transactions for employees each time, and the amounts vary. For example, if you manually key timesheets in to the system, you could set up both the hourly and overtime pay codes as data entry defaults and then you only need to select the employee to create the transactions and then enter the amounts.

#4 Import it!

I am only half joking, integration manager is a GREAT tool for imports of payroll transactions from flat files. So if you have a download from a timekeeping system, or even just payroll transactions that you have calculated in Excel, think about using Integration Manager to reduce the keying.

Please share your own shortcuts, I would love to hear them!

Code Review: .NET Coding Tip

I was doing a code review with a friend earlier this week and while we were discussing some application design topics, he noticed how I was coding calls to certain classes and offered a neat suggestion.  I'm far from a .NET guru, and am still constantly learning new syntax options since switching to C#, so this may be obvious to some/most .NET developers.

I often have classes that perform routine, discrete functions, such as getting preformatted dates, running regex routines, or querying SQL Server.  In the routine that my friend saw, I was using a standard data access class that I use for querying a GP database.

The following two lines create an instance of the "GP" class, and then call the ExecuteScalar method.  But he noticed that the GP instance was only being called once in my routine.

GP gp = new GP(gpUserID, gpPassword);

string result = gp.ExecuteScalar(database, CommandType.Text, commandText, sqlParameters);

He showed me the following syntax:

string result = new GP(gpUserID, gpPassword).ExecuteScalar(database, CommandType.Text, commandText, sqlParameters);

With this approach, you instantiate the class and call the method in one line.  Pretty slick.

This reminds me somewhat of how I sometimes add SQL a parameter directly to a command object and assign the value all in the same line.

sqlCmd.Parameters.Add("@param", SqlDbType.VarChar, 21).Value = "some value";

These types of tricks can sometimes dramatically reduce the number of lines required for a given operation.  I'm not a fan of compactness just for compactness' sake, but I do appreciate tricks that improve readability.

Consultant Tools Series: TrueCrypt

Several non-technical friends and colleagues have asked me how to manage and secure sensitive or confidential data.

One is an independent CPA that has client information in his tax preparation and accounting software.  Another is involved in high stakes business transactions and legal proceedings, and travels to dozens of countries every year to meet with investors, attorneys, and governments.  Another friend has a business that provides health insurance, retirement plans, and other employee benefits to businesses, and has to store a lot of very sensitive medical and financial information.

In the Dynamics GP world, the most sensitive data that I've worked with are client databases with full HR and Payroll records, or client data files that have confidential employee information, including name, address, phone, SSN, etc.  Just today I received some files containing employee information for an HR and Payroll integration to Dynamics GP, so I immediately wanted to encrypt the files.

While there is no single answer to the question of how such confidential information should be managed, when most people think of securing such data, they often use two phrases:  "password protect" and "encrypt".  It's a good start, but that's often the limit of their knowledge.

Before discussing details, I usually ask the person what concern or risk they are trying to address.  How sensitive or confidential is the data?  Is it only of interest to competitors?  Would criminals want it?  Would law enforcement want it?  Would lawyers or private investigators want it?  Would a government want it?


Most people just don't want the data wandering around publicly, and don't want it exposed if a computer is hacked or stolen.  But some people do legitimately need to ensure that certain files cannot be accessed by a government agency, even if the computer is confiscated.

Although there are probably lots of different options, I usually offer the following choices:

1) Put a password on the Excel or Word file.  This is usually adequate to prevent inadvertent disclosure of sensitive information, such as a list of tentative pay raises, bonuses, or terminations at a company.  The passwords on Microsoft Office files can either be stripped out or cracked by various software packages, so the Office passwords only provide a low level of security.  And one significant downside is that each file must have a password, so if you use different passwords, don't access the file regularly, or have to open a file that is several years old, it is common for people to forget the password (myself included).

2) Use WinZip or WinRAR to compress one or more files, and then use a password on the Zip or RAR file to encrypt and secure the files.  This has some benefits, such as being able to secure multiple files with one password, and the ability to secure files that don't have their own encryption (like a CSV or text file).  But such passwords really aren't any more secure than an Office file password, as password crackers can attack zip files as well.  Another downside to using WinZip with a password is that although the compressed files may be encrypted, anyone who opens the zip file can see its contents, which I personally don't like.

3) If those basic options are insufficient, I then jump straight to TrueCrypt.  TrueCrypt is a free, open source encryption application that provides very secure on-the-fly file and disk encryption.  It was created in 2004, and is widely recognized as one of the best disk encryption options available today.  The fact that it is free and open source means that I don't have to purchase upgrades as new versions of Windows are released, as encryption techniques change, or as the software vendor goes bankrupt or is acquired like at least one of the other disk encryption products I have tried.

TrueCrypt is available for Windows, Mac, and Linux, and if you review the features and documentation, I think you'll see how seriously the product addresses security.

TrueCrypt recently made news when the Brazilian government and the FBI were unable to crack hard drives encrypted using TrueCrypt.  While few people need to legitimately hide their data from such organizations, it's reassuring to know that the solution works when used properly.

There are a few key things that I like about TrueCrypt.

1) It is very easy to use.  Even if you don't understand how it works, the TrueCrypt beginner's tutorial walks you through the very simple process of creating a new encrypted container.  Once that container is setup, it's simply a matter of mounting the file and entering a password, and you have a new drive letter in Windows.

2) Because it is volume based, many applications can use TrueCrypt volumes transparently.  For small businesses or CPAs that run Lacerte tax software or QuickBooks, it's very simple to have applications read and write all of their data and files to a TrueCrypt volume, just like any other hard drive.

3) TrueCrypt doesn't require extra steps to encrypt data.  Because it encrypts and decrypts data on the fly as it is read and written to disk, there are no extra steps to secure the data, which is a huge benefit for a non-technical user.  Once the volume is dismounted, it just becomes an anonymous file on your hard drive.

4) TrueCrypt supports "key files" in addition to passwords.  Instead of trying to remember multiple 10-20 character secure passwords, you can use just about any file, or combination of files, to serve as your 'password'.  If you have a hundred MP3 or JPG files on your computer, you can randomly choose one or more of those files to serve as your key.  Just make sure to keep those MP3 or JPG files safely backed up!

5) Once you have your TrueCrypt container setup, you can use online backup services to back it up in the cloud, without worrying about whether the backup provider is really encrypting your data or has adequate safeguards to ensure that your data can't be viewed. 


This morning, after receiving a CSV file containing the data for over 900 employees, I immediately created a new TrueCrypt container and then moved the files to the mounted TrueCrypt volume.  It took maybe 2 minutes, literally, and that data is now encrypted, maintenance free.

In this case, I chose to use a standard password for the container, and then stored that password in a RoboForm secure note for safe keeping, since it may be days or weeks before I need to access the files again.


And with that, YCpRz37dTkC4Vh5PLIjuyQmBslgBB4/Oy+LPGjajHao=

(That's "Have a good weekend!", in 128-bit AES)