Fun eConnect Bug: Check those field lengths!

Today I deployed an updated version of an AR Invoice eConnect integration. The customer has used the integration previously, and it worked fine, but when we ran it with a new batch of data, the Edit List report indicated that some transactions had missing distributions or incorrect distribution amounts.

Puzzled, I checked the transactions and their distributions in Dynamics GP, and everything looked fine.  But when I ran the Edit List again, the same errors were listed--except for any transactions that I had opened and viewed.

Hmmmmm.

So I queried the RM Distribution Work table, RM10101 to see what was going on.

For the one transaction that I had opened and viewed in GP, I saw four distribution lines instead of the two that should be present.  I was pretty sure I didn't import four lines, but I wasn't sure where the other two came from.

After looking closely at the four records, I noticed one discrepancy.  The DOCNUMBR field value for two of the lines was different than the other two distribution lines.  One had the full document number that was imported, but the other had a truncated document number, missing the last 4 characters.  And the account indexes for the two sets of distributions were different.

And that is when it dawned on me.

Back in 2008, I wrote this blog post discussing how eConnect handles character values that exceed the length of the eConnect / GP field.  Normally, it truncates character data values without any warning or error and proceeds happily, letting you discover that you have some data missing.

Well, in this case, the client had an invalid AR invoice number that was 20 characters long, due to a change in data formatting.  eConnect happily imported the invoice, truncating the invoice number to 17 characters.

But, it appears that there is a minor 'bug' in eConnect 10, as the AR distribution imported the full 20 characters into RM10101.  No truncation!

So, in the RM10301 Sales Work table, we have a 17 character invoice number, and in RM10101 Distribution Work table, we have a 20 character invoice number.  Naturally, when the GP edit list tries to match the invoices to distributions, it can't match the two numbers, so it appears as if the invoice does not have any distributions at all.  And when I opened the invoice distribution window, everything looked fine because GP was defaulting distributions based on the customer account settings.  And when the distribution window is opened, GP creates new distribution records, but using the customer default accounts.


In short, the distributions that were imported with the full 20 character invoice number are "orphaned".  They are not associated with any invoice, and even if you delete the invoices, the distributions will not be deleted.  They will have to be cleaned out manually or via Check Links.

So, the lesson here is that you shouldn't rely on eConnect to manage the length of your character field values.  Ideally, you should include field length checks in your integration validation code to detect data values that may exceed a GP field length.

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

http://www.precipioservices.com

New Dynamics GP 2010 Reporting Book and Packt Books Dynamics Month!

Congratulations to Christopher Liley and David Duncan for publishing their new book, Dynamics GP 2010 Reporting.

Reporting in Dynamics GP is not exactly the most user friendly experience, and is often overlooked or given insufficient attention.  As a result, there are dozens of powerful reporting features that are never used by most Dynamics GP customers.  It's great to see that there is now a book dedicated to the topic.


Celebrating the publication of their third Dynamics GP book and fourteenth Dynamics book, Packt has also declared May 2011 "Dynamics Month", so make sure to stop by their site and take advantage of the discounts that they are offering.


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

http://www.precipioservices.com

One Very Hairy Dynamics GP Integration

I develop a lot of custom Microsoft Dynamics GP integrations.  A few are simple and straight forward.  The majority have some quirks that require a few tricks or workarounds in .NET.  And a few, well, they remind me that my imagination definitely has limits when it comes to envisioning complexities with certain businesses processes and related integrations.

I've been working on one particular integration over the course of a few months that has been vastly more challenging and complex than I could have ever possibly imagined.  Believe it or not, it's a Purchasing Receipt import.

I know, I can hear it now:  "Purchasing Receipt?  You kidding?  How hard could that possibly be?"

Let's start with the source data file.  It's an Excel file, which is fine, but it is formatted as a report, with a report header in the first three rows (merged cells) and column headers on row 5.  But there are only 7 column headers for 11 columns of data.  Fine, I can read the file without headers, no problem.

And then the fields.  There are only 3 fields related to the receipt transaction in GP:  PO Number, Item Number, and Quantity.

Okay, so I have to lookup the PO number to find the vendor ID, no biggie.  And sure, I can use the vendor ID and item number to lookup the vendor item number.  Not so bad.

Except that some of the current vendor item numbers don't match the vendor item number on the PO.  Sometimes the PO shows the vendor item number, sometimes it's the item number, and sometimes it's something else entirely--perhaps an older vendor item number.  Oh, and sometimes even the item number won't match. 

Okay....so I can add some logic that will figure out which PO line is being received by checking the vendor item number, then the item number, and then, believe it or not, the item description.

Whew, all done, right?  Not so fast.

For some reason, many of the POs have the same item on multiple lines.  Not 2 or 3 lines--we're talking 10 lines or more.  So I can't just send the PO number and item number to eConnect--I have to know which PO line I'm receiving against.  Oooookay, I now have a routine that can figure that out.

Almost done, right?  Nope.

Did I mention that the receipt quantities sometimes exceed the PO quantities, and those excess quantities need to be received too, in some situations?  Well, eConnect allows you to receive excess quantities if a PO line is not fully received, but if the line has already been fully received by a prior receipt, eConnect will refuse to receive additional quantities on that PO line.  So if you have a PO line with a quantity of 20, and 20 have been received, but you need to receive 4 more, what do you do?

Well, I learned that eConnect will not allow you to adjust the quantity of a PO line that has been received.  And it will also apparently not allow you to add a new line to an existing PO.  So that just leaves...creating a new PO to receive the excess quantity.

You still with me?

So now that I've created the new PO, I need to pair it up with the receipt line.  And then I need to write the new PO number back to the Excel file so that it can be traced or researched if necessary.

Oh, did I mention that there are POs that were created before the inventory item records were setup in GP?  So not only do some PO lines have an "incorrect" item type status, but the corresponding vendor item record is not yet setup.  So I need to "fix" those PO lines, and create a vendor item record.

And the list actually goes on and on from there--seriously, there really is alot more, but I assume you get the point.  What at first seemed like a relatively simple endeavor ended up having requirements that have left me in amazement.  Nearly every issue that could exist with the data and receipt process did exist.

I will no longer look at purchasing receipts the same ever again.

Which leads me to the general topic of designing and estimating custom eConnect integrations, which I'll discuss in another post:  "The 90/10 Rule:  90 Percent Source Data, 10 Percent eConnect".


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

http://www.precipioservices.com

The Ultimate Microsoft Dynamics GP Search Tool - Part 1: Introducing Search Master

If you ask several Dynamics GP users what their top concerns, wish list items, or frustrations are with Dynamics GP, I would bet that "searching" or "search" would not be a top item, and may not even be on the list.  Maybe the general concept of search in Dynamics GP would be included, such as the ability to produce a report showing specific data, or being able to "find" certain data.  But based on my experience, I speculate that "search" is not a common topic or feature request among many Dynamics GP customers.

A little over a year ago, I was chatting with a friend who implements a hosted web-based "SaaS" ERP package that competes with Dynamics GP.  He asked me if Dynamics GP had a "global search" feature.  I replied, "A what?"

Honestly, at the time, the concept was foreign to me.  I tried to think about what that would look like in Dynamics GP, but had a hard time envisioning what value it might provide.  I thought that if a GP user wanted to find a vendor, they could use an inquiry window or SmartList or some other existing window or feature to find that vendor.  I didn't think that search was necessarily a "problem" that needed to be solved in Dynamics GP.

My friend then gave me a demo of his ERP package and showed me some examples of how the global search feature worked.  Only when I saw the global search feature did it finally dawn on me how powerful and useful such a feature could be in Dynamics GP, or in any ERP application.

I then spoke with my colleague Andrew Dean at Envisage Software, who developed the fantastic Post Master for Dynamics GP product.  When I presented the concept to him, he instantly saw the potential and began working on designing a new product.

After many months of research, hard work, and testing, we are proud to present Search Master for Dynamics GP, which we believe is the ultimate search tool for Microsoft Dynamics GP.

Search Master is a .NET client application that allows a GP user to search for any value, in any Dynamics GP company database, and then instantly displays the results.  No waiting, no browser windows, no long running queries.  And there is no need to tell Search Master whether you are searching for a vendor, an inventory item, a customer, or a sales transaction--it will instantly find every Dynamics GP record that contains the specified keywords.

Results are grouped by database and by category, allowing the user to quickly find the data they are looking for.  With a single click on any search result, Search Master will open the appropriate Dynamics GP inquiry or edit window and take the user directly to the record.

Rather than continuing to discuss it, I'll point you to our brief video demonstrating how Search Master works and the power of a instant, global search tool for Dynamics GP.  The video window below is pretty small, so I recommend clicking on the video or on the link below to view in a larger window on YouTube.

Click here to view the Search Master Demo Video directly on YouTube

We believe that Search Master will redefine how you interact with Dynamics GP and how think about accessing your Dynamics GP data.

Rather than clicking on a menu, opening a window, switching to a Navigation Pane, using SmartLists or performing a lookup to find a record, you can instead use Search Master to instantly find any record, transaction, or data in Dynamics GP, and then jump directly to the record in GP with a single click.  And since Search Master can find and display data in any Dynamics GP company database, users no longer need to worry about what company they are currently using in GP--search results can include records from all company databases.  All instantly.

Several years ago, I had a Dynamics GP client with 22 company databases.  Because they had a centralized accounting department, the accounting staff was constantly having to switch between GP companies to enter and look for data.  If the AP department received a phone call from a vendor, they sometimes didn't know which of the 22 companies the vendor was associated with--it could be one, or it could be 5, so finding a specific invoice or check was a time consuming chore, all while the vendor was on the phone waiting.  I was able to create some multi-company SmartLists, but it was alot of work, required numerous custom views and maintenance scripts, and required the user to navigate to SmartLists, click several times to add a filter to find what they were looking for, and then potentially switch GP companies to locate the vendor transactions.

Search Master solves this challenge completely.  An AP clerk can now type just a few letters of the vendor name and instantly find the vendor and every transaction related to that vendor, even if they exist in multiple company databases.  With a single click, Search Master will switch to the appropriate company database and display the desired information.

Besides eliminating navigation and mouse clicks, one of the most enjoyable features of Search Master is that it returns results instantly.  As soon as you type a word or query, you see the results.  There is not "Submit" button, there are no web pages to load, there are no time consuming queries, and there is zero waiting for search results.  Searching is performed in real time without any delays.  Think about how often Dynamics GP users use menus, navigation panes, lists, SmartLists, inquiries, navigation buttons, and lookups to find specific data--and then think about eliminating all of that wasted time.

Andrew Dean and I will be at the Envisage Software Solutions booth at the Decisions Spring 2011 Virtual Conference Dynamics GP Day on June 15th to present Search Master, perform demos, and answer any questions.  Please make sure to sign up for the conference and visit our booth!

If you think that Search Master would be valuable for your company or one of your Dynamics GP clients, please contact us.  I can be reached at steve@precipioservices.com, or via telephone at (949) 735-4640, or you can visit the Search Master web site.

In Part 2 of this series, I'll discuss Search Master's powerful query syntax that allows you to search for much more than just basic keywords.

How do you backup your HyperV virtual machines?

By Steve Endow

NOTE: This is an old post, and I have since purchased Veeam Backup & Replication for Hyper-V.  It is so much more effective, reliable, and feature filled than the process below that I would strongly recommend not wasting your time on scripting and invest the time in evaluating Hyper-V backup solutions. 

Here is my evaluation of two Hyper-V backup solutions:

http://dynamicsgpland.blogspot.com/2015/02/how-do-you-backup-your-hyper-v-virtual.html

/////////////////////////////////////

Backups are one of the least glamorous IT tasks, but fortunately improvements in technology, software, and the abundance of inexpensive disk space has made them a lot easier.

I currently use SugarSync to backup all of my frequently used files, such as client files and zipped source code, which also syncs those files between my laptop, desktop, and iPhone.  I also use Carbonite as an additional measure to back up all of those same files, plus all of my large files, such as Outlook PST files, database backups, and MP3 files, etc.

But those backups seem trivial and quaint compared to the challenge of backing up virtual machines.  Despite my occasional effort to shrink the VHD files, a quick glance tells me that most of my VHD files are in the 20GB - 30GB range.  While I have enough disk space for those files, backing up such large files is still a time consuming and space hogging endeavor.  Copying such a large file to a backup drive or server takes quite a long time, and if you want to maintain multiple backups, the copies take up a lot of disk space.

I'm assuming there are some fancy backup solutions that can backup HyperV VMs (I've never bothered to check), but when it comes to backups, I avoid fancy, and prefer instead to stick with simple, basic, trusted tools.

In the past, I've manually saved or shut down my VMs and then manually used WinRAR to create a RAR backup file on my NAS storage device.  This worked, but was manual and took forever.  I would try and do the backups in the evening before going to bed, but never did them consistently or on any set schedule.  I've heard that Volume Shadow Copy Service (VSS) and even Windows Backup can supposedly backup running HyperVs, but I'm not a fan of any of the versions of Windows Backup, and am fine taking the additional step of saving my VMs prior to backup to be on the safe side.

After switching over to 7Zip and seeing how much better the compression could be, I finally wrote some batch files to automatically zip the VHDs to 7z files with a date time stamp.  You can use WinRAR or WinZip if you prefer, but given how rarely I need to recover a VHD backup, I want the best compression I can get.  While this 7z batch file worked well, I still had to manually save or shutdown the virtual machine.

Only recently did I finally take the time to track down the VBS scripts that would automatically save my VMs.  With this final piece, I have automated the backups of my virtual machines.  It's not glamorous, and I am sure that there "better" approaches, but this is my first pass, is simple, and was fairly easy to do.  I know that PowerShell offers alot of promise for this type of task, but seriously, have you seen PowerShell scripts?  It may look like C, but the last thing I have time to do is learn yet another proprietary scripting language and yet another object model that I won't use regularly, so I've ruled out PowerShell for the time being.


So here are my current backup scripts and steps, likely subject to change as I use it more and perhaps learn of other techniques.  I'm interested in hearing how others deal with this challenge.  At the moment, I speculate that many businesses do not have any backup strategy or solution for regularly backing up their virtual machines.


Step 1: Use a VBS script to save the VM.  Unfortunately, it seems that VBS only has the ability to Stop, Start, or Save HyperV VMs, and does not have the ability to "Shut down" the guest OS like PowerShell does.  But from my experience, Save is adequate for backing up HyperV vhd files.  I found this script on some web site and modified it slightly to meet my needs--I just use a fixed VM name rather than accepting a command line parameter like the original script.  In these examples, I'm backing up my VM called "GPDEV1".

Option Explicit
Dim arg, targetVM, WMIService, VMs, InputKey

targetVM = "GPDEV1"

If Right((LCase(WScript.FullName)),11) <> "cscript.exe" then
  WScript.Echo "Use cscript.exe"
  WScript.Quit
End if

Set WMIService = GetObject("winmgmts:\\.\root\virtualization") 
Set VMs = WMIService.ExecQuery("SELECT * FROM Msvm_ComputerSystem WHERE ElementName='" & targetVM & "'")

Select Case VMs.ItemIndex(0).EnabledState
Case 2
    WScript.StdOut.Write targetVM & " is saving." 
    VMs.ItemIndex(0).RequestStateChange(32769)
    Do while VMs.ItemIndex(0).EnabledState <> 32769
      Set VMs = WMIService.ExecQuery("SELECT * FROM Msvm_ComputerSystem WHERE ElementName='" & targetVM & "'")
      WScript.StdOut.Write(".") 
      WScript.sleep 1000
    Loop
    WScript.Echo "Completed." 
Case Else
    WScript.Echo targetVM & " is not running." 
End Select

Step 2:  Once the VM is saved, it can be backed up.  To save space on my backup drive, I prefer to zip the VHD files.  My NAS device is relatively slow, so the time difference of copy vs. zip + copy is negligible for me.

This script will save the 7z file with a date prefix.  If you want to keep multiple backups, such as several monthly backups, this one is handy.  The "V:" drive is my mapped NAS drive.

FOR /F "TOKENS=1* DELIMS= " %%A IN ('DATE/T') DO SET XDate=%%B
For /f "tokens=2-4 delims=/ " %%a in ('date /t') do (set XDate=%%c-%%a-%%b)

7z a -t7z "V:\GPDEV1\%XDate% GPDEV1.7z" "E:\HyperVServers\GPDEV1\GPDEV1.vhd"


This script just saves the 7z file with the same name every time.  I currently use this for my weekly backups.


7z a -t7z "V:\GPDEV1\GPDEV1 Weekly Backup.7z" "E:\HyperVServers\GPDEV1\GPDEV1.vhd"



Step 3:  Create a Windows Task Scheduler task to save the VMs.  Note that Task Scheduler does not appear to run VBS files directly, so you will need to create a batch file that calls the CScript engine.  SaveGPDEV1.vbs is the file containing the VBS script listed above.

cscript C:\Scripts\SaveGPDEV1.vbs

Note that you must include the full path to your VBS file in your batch file, otherwise the command will fail if run by Task Scheduler (since it will assume a path of C:\Windows\System32\ for the vbs).


Step 4:  As a second step in your new task, call the backup batch file that zips and/or copies the VHD file to your backup drive.


I currently have a weekly backup job that backs up a few VMs on Wednesday night at 11pm, and then another job that backs up a few more VMs on Friday night at 11pm.  Those 7z files retain the same name and are updated each week.

I'll probably add a few more tasks that perform monthly backups with a date stamp in the file name.

This backup approach won't win any awards for cutting edge technology, but it was simple enough for me to pull together with minimal time investment, and I'm pretty confident in its reliability.


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

Payroll and Tips (Run Screaming Away, NOW!)

Okay, so I rank payroll and tips up there with sales tax and analytical accounting in my list of "not so favorite" things to deal with.  But I did learn an interesting tidbit about how tips are impacted by tax sheltered (TSA) deductions in Dynamics GP.  Take the following example:

Adam Smith (Setup)

• Pay Code-HOUR
• $15/hr
• Pay Code- TIPS
• Reported Tips
• Deduction- HEALTH
• $10/fixed amount per pay period
• Tax Sheltered from Federal and FICA

Adam Smith (Payroll 5/15)

• Gross Pay
• HOUR- $150
• TIPS- $50
• Deductions
• HEALTH- $10

Now, here is where it gets interesting.  Let's say that when the deduction (HEALTH) was set up (Setup>>Payroll>>Deduction), it was left as the default Based on Pay Codes- All (noted in screenshot below).

Here is what happens when you review a payroll summary for the employee:

• Gross Pay $200
• Federal Wages $142.50
• Reported Tips $50
• Federal Tips $47.50

Huh?  Well, because you said that the deduction is based on all pay codes (including the TIPS code), then GP prorates the tax sheltered deduction across all pay codes (wages and tips).  So, in this example, tips accounted for 25% of the gross and wages were 75%.  So the $10 deduction was allocated 25% ($2.50) to reduce the taxable tips, and 75% ($7.50) to reduce the taxable wages.

Easy enough to change if you don't want it to reduce tips, simply change the Based on Pay Codes setting for the deduction to Selected, and insert all pay codes except the tips pay codes.  Viola! It will only prorate across the included pay codes.

Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a supervising consultant with BKD Technologies, providing training, support, and project management services to new and existing Microsoft Dynamics customers.

Implementation Trauma Continued

I promised a couple of weeks ago that I would dive deeper in to my top five implementation "sins" for both customers and consultants.  I am a bit behind obviously, but let's take a look at #1 on both lists:
For consultants:  Assuming you are the sole reason for the success/failure of the project

For customers:  Assuming that the consulting team is the sole reason for the success/failure of the project

What is wrong with taking sole responsibility for a project? Anyone?  Anyone?  Well, there are number of issues with this approach from both the consultant and customer's perspective. But, let's start with a story about Suzy.  Suzy is a fabulous consultant, and the customer loves her.  Suzy takes care of them, completing many complicated tasks on her own without their input.  She even completed much of the configuration without their assistance, after all, she knows their business so well.  In many ways, the system seems to magically configure and take of itself because Suzy is so efficient.  In this way, Suzy has treated the implementation as if it "her system", fixing and configuring it as needed.

Although Suzy should be applauded for cultivating such a great relationship with the client, we have to question the wisdom of Suzy taking full responsibility for the implementation-- making decisions, completing configuration tasks, and resolving issues with little to no customer input.  And, in this case, the customer loves her for it.  However, does this best serve the customer and the implementation in the long run?  Absolutely not, and here's why:

1. As much as the customer loves Suzy, she is not their employee.  She does not work in the business on a daily basis, nor will she be around after the implementation is complete. This inherently limits her knowledge of their business, her control over the situation, and her ability to motivate adoption within the ranks.  Customer investment (in terms of resources) is critical to the project's success.  In the end, the system belongs to the customer, not to Suzy.
2. Suzy is taking responsibilities and tasks away from customer resources, and reducing their opportunity to become involved in the implementation.  If customer resources are given only limited opportunities to participate in the decisions and tasks related to the implementation, a significant opportunity to create excitement about the project is lost.  Without internal advocacy, projects fail.  
3. With all that Suzy takes care of, when she leaves (as they always do) the system can become "difficult" and "confusing" if there has not been the almost-constant involvement of customer resources in the decision making and configuration process.  From day one, customers must cultivate their own "experts", learning as much as they can from Suzy before she leaves.  Suzy made so many decisions, fixed so many things, and no one is left with that knowledge or understanding once she moves on to another project.  As much as a consultant brings expertise in the software (and hopefully, industry) to the project, it is the customer's perspective that ensures that decisions and processes are owned and supported by the appropriate internal personnel.

It is a natural inclination, I think, for a consultant to want to make an implementation "easy" on a customer. And that natural inclination often leads to situations like Suzy's.  However, as with many things in life, less is learned when we are spared the effort of learning and making decisions. 

As consultants, we need to ensure that the customer is in a position to take responsibility for the success of the implementation with our assistance.  We need to give them the tools, the knowledge, and the resources to do this well.   For this reason, I take the title "consultant" seriously.  I like to think we are not hired to complete a list of specific tasks, but rather to "consult" the customer in how best to complete that list. 

As customers, we want a system that works for us, that our employees feel is beneficial, and that grows with us over time.  To achieve these goals, we must invest early and intensely (in resources, time, and effort) in the success of the project.  To trust any single person, no matter how qualified or competent, is to limit the opportunity for success.  Find your trusted consultants and let them "consult" you on how to reach these goals.

More on #2 on the lists in the coming days :)

Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a supervising consultant with BKD Technologies, providing training, support, and project management services to new and existing Microsoft Dynamics customers.