New Article on MSDynamicsWorld: When It’s Time to Upgrade an Outdated, Customized Microsoft Dynamics GP System…Who You Gonna Call?

It’s been a couple months since I’ve written a business-driven column over at MSDynamicsWorld. This time, my new article looks into an all too common issue: upgrades from older, customized versions of Microsoft Dynamics GP systems.

By “older”, I am not referring to – believe it or not – version 7.0 or 7.5, even though those are very outdated releases. I’m referring to you, still running version C/S+ 3.17, or 4.0 or even GPA. I know you have milked the product as much as you can and you feel it still does what you need it to do, but you are missing out on a unique opportunity to bring your systems up to date and take advantage of the wide array of ISV solutions that will once and for all get you off the development threadmill.

Since it’s Friday, the article is meant to serve as food for thought.

When It’s Time to Upgrade an Outdated, Customized Microsoft Dynamics GP System…Who You Gonna Call?

Leave your comments and feel free to discuss with your peers.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

Reconciling unchanged bank statements

Just recently, I worked on a case with a partner, whose client had a checkbook setup. For the past serveral months, the bank account associated to the checkbook has had no transaction activity. The client still receives a bank statement each month with no transactions.

When the client would try to enter the cutoff date information into the Reconcile Bank Statment window, even though the difference is zero, the system is not letting them reconcile the statement.

Reconcile Bank Statement window

They keep receiving a message that they need to mark the items they want to clear.

Select Bank Transctions window – error when attempting to reconcile

As a result of not being able to complete the reconcile, the Last Reconciled Date field is not being updated on the Checkbook Maintenance window.

Checkbook Maintenance window

To overcome this issue, we had the client enter both an interest income and an other expense adjustment for a penny ($0.01), as shown below:

Reconcile Bank Adjustments

Once we returned to the Reconcile Bank Statements window and clicked on the Reconcile button, the process went through. Since Microsoft Dynamics GP does not post zero balance transactions to the same account in the General Ledger, then we were able to effectively not affect the GL. The added bonus, of course, and the problem needed to be solved – updating the last reconciled date on the checkbook – was taken care of with this workaround.

Reconciled Checkbook

Of course, we could have made the changes directly in SQL by updating the Checkbook Master table (CM00100), but that would have left no audit trail of the reconciliation for the accounting department. As for those pennies… they are just that, pennies. The adjustments were documented with notes that explained clearly that they served just as a workaround so auditors would not throw a fit.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

Disabling Multiple Ledgers functionality in Microsoft Dynamics GP 2010…after the fact

Let’s face it! Like many things in life, configuration decisions are revisited even after going live (rightfully so!) with your system. What was viewed and considered a requirement a few months aback and worked during User Acceptance Testing turns out to be something the business no longer needs today, due to changes in direction, or changes in business conditions.

Just recently, I came across a request for disabling the new reporting ledgers functionality in Microsoft Dynamics GP 2010. While this implementation was not live, this issue was clearly affecting the consulting teams ability to move forward.

The following script should disable the reporting ledgers function:

-- Created by Mariano Gomez, MVP
-- This code is licensed under the Creative Commons 
-- Attribution-NonCommercial-ShareAlike 2.5 Generic license.

UPDATE GL40000 SET Allow_Reporting_Ledgers = 0, UseLedgersForAcctBalance = 0;
DELETE FROM GL40001;

Once the script is executed, go back to the General Ledger Setup window. You will notice that a BASE ledger is created by default, but also notice that the Allow flag is unchecked.



General Ledger Setup

Click the Ok button to continue.

Now, if you open the GL Transaction Entry screen, you will notice that the Ledger ID field is no longer present.

Transaction Entry

Hope you found this post useful.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Microsoft Dynamics GP 10.0 Service Pack 5 now available

The long awaited Microsoft Dynamcis GP 10.0 Service Pack 5 is fresh out of the oven. The service pack is available for English only installations at this time and can be downloaded from:

PartnerSource
Service Pack, Hotfix, and Compliance Update Patch Releases for Microsoft Dynamics GP 10.0

CustomerSource
Service Pack, Hotfix, and Compliance Update Patch Releases for Microsoft Dynamics GP 10.0

One big note of caution: after applying Service Pack 5 for Microsoft Dynamics GP 10.0, you will not be able to upgrade to Microsoft Dynamics GP 2010 until the release of Service Pack 1 for the latter.

Related Articles:

Microsoft Dynamics GP 10.0 Service Pack 5 @ Developing for Dynamics GP

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

RW – Working with POSTNET barcodes for US zip codes

The Postal Numeric Encoding Technique (POSTNET) is a barcoding system developed by the United States Postal Service to assist with automatic mail sorting and routing. The POSTNET barcode uses a combination of half-bars and full-bars to encode the zip code that appears on a mail piece. The barcode starts and ends with a full bar (often called a guard rail or frame bar and represented as the letter “S” in the USPS TrueType Font).

In the United States, zip codes can be of 5 digits, 9 digits (also known as Zip+4), or 11 digits in the case of a specific delivery point. Each digit in the zip code is represented by 5 bars. Therefore a 5-digit zip code is represented by 25 bars. In addition, to ensurePOSTNET accuracy during mail processing, a check digit, which is five bars, is calculated and added to the zip code. Hence a 5-digit zip code would render a total of 32 bars: 25 bars for the 5-digit zip code + 5 bars for the check digit + the 2 enclosing guard rail bars. Hence, Zip+4 uses 52 bars, and a delivery point uses 62 bars.

To calculate the check digit, each digit in the zip code is added. The result is then subtracted from the nearest multiple of 10. For example, if each digit in the zip code 33076 is added, 3+3+0+7+6 = 19, and we subtract from the nearest multiple of 10, number 20, then the check digit is 1. Mathematically speaking 10 – (digit-sum mod 10). This will yield the following S330761S. This number is then represented by the following barcode (including the guard rails, represented by “S”):

While based on a binary system, the weight of each bar is different than in standard binary. The weight of each bar is as follows:

The following illustration shows are representation of all numbers:

To add zip code barcoding capabilities to a Report Writer report, for example an Invoice or a Check, you must first download and install the fonts. You can get a set of fonts from here. To install the fonts (the TTF files in the zipped file), extract the fonts to your My Documents folder, then copy them to your Windows\Fonts folder.

1) Open Report Writer and select the SOP Blank Invoice Form. Click the Layout button to open the report in the Layout window.

2) Add the RM_Customer_MSTR_ADDR.Zip field to both Report Header and Page Header sections. Set the fields properties to Invisible.

3) Create a string calculated field called (C) Postnet. Assign the RM_Customer_MSTR_ADDR.Zip field to this calculated field as shown in the picture:

4) Add the (C) Postnet calculated field to the Report Header and Page Header sections. Change the font to IDAutomationSPOSTNET. By now, your report layout should look like this:

5) Add the report to VBA. Go to Tools > Add Report to VBA.

6) Select all 4 fields, the 2 on the report header and the 2 on the page header and add them to VBA. Go to Tools > Add Fields to VBA.

7) Open the VBA Editor, locate the SOPBlankInvoiceForm (Report) object in the Project Explorer , double-click and add the following code:

Public Function EncodeZip(pZip As String) As String
    Dim i As Integer
    Dim sZip As String
    Dim digitSum As Integer
    Dim checkDigit As Integer
   
    sZip = "S"
    digitSum = 0
   
    For i = 1 To Len(pZip)
        If IsNumeric(Mid(pZip, i, 1)) Then
            ' Concat the digit
            sZip = sZip & Mid(pZip, i, 1)
           
            ' Add up the digits
            digitSum = digitSum + CInt(Mid(pZip, i, 1))
        End If
    Next i
   
    ' Calculate check digit
    checkDigit = 10 - (digitSum Mod 10)
    sZip = sZip & CStr(checkDigit) & "S"
   
    EncodeZip = sZip
End Function

Private Sub Report_BeforePH(SuppressBand As Boolean)
    Dim sZip As String
   
    sZip = Zip
    sZip = EncodeZip(sZip)
    CPostnet = sZip
End Sub

Private Sub Report_BeforeRH(SuppressBand As Boolean)
    Dim sZip As String
   
    sZip = Zip
    sZip = EncodeZip(sZip)
    CPostnet = sZip
End Sub

Compile the code.

8) Return to Microsoft Dynamics GP saving all changes as you exit.

9) Grant security to the modified report. Print an invoice to test.

I hope you found this article interesting and the implementation very simple. You can find the fonts and package file for this project at the bottom of this article.

Downloads

Postnet Fonts – Click here
v10 SOP Blank Invoice Form report – Click here

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com

Applying Microsoft Dynamics GP v10 service pack 4: Tales from the trench

It’s a gloomy and rainy day in Atlanta, so I figured I could do something productive while under lockdown. I currently run Microsoft Dynamics GP build number is 10.00.1193 — service pack 3, which was installed using the Feature Pack 1 DVD image available on CustomerSource and PartnerSource under the Product Release section. This build installs the Dexterity dictionary 10.0.320.

Before beginning the installation of SP4, I created a backup copy of my current GP installation folder just in case I needed to revert to it, and of course, backed up all forms, reports, and VBA projects, and my DYNAMICS and company databases. I also backed up the registry key corresponding to the GP installation, HKLM > Software > Microsoft > Business Solutions > Great Plains.

The upgrade operation began by launching the Service Pack 4 msp installation file.

After 5 minutes of the “10.0 is being configured on your computer” process, the installation script began “Gatherting required information” to calculate the installation steps required to deal with my environment’s configuration.

That went fairly fast, until the progress bar reached 90%. This is when things slowed down again for approximately another 2 minutes.

Once planning phase was up, another progress bar began letting me know that “10.0 is being configured” on my computer, presenting a progress bar with the time remaining.

Approximately 1 second before the “Time remaining” phase was up, I got an error saying “DEX.DIC version 10.0.320 is not compatible with executable version 10.0.324.0“.The error showed up one more time and I clicked the OK button to continue. No other indications, except for the message that anything was wrong.

The process went along and completed “successfully” with all service pack files copied into the GP installation folder. All the Dexterity Shared components were replaced.

I then lauched Dynamics Utilities…remember that DEX.DIC error? There it was again! At this point, the only safe recovery option to protect my data’s integrity was restoring all previous backups: databases, reports, forms, registry entries, and application folders.

I then downloaded the latest hotfix prior to SP4, 10.00.1328 (KB971014), and repeated the installation process. Everything went fine. I did not receive the pesky Dexterity dictionary version check error, all system and company tables upgraded without a hitch. All my reports and forms upgraded successfully. I noticed the Dexterity dictionary version for this hotfix was 10.0.320. The question now was, would I receive the same error attempting to install SP4?

I repeated all the installation steps I previously described. The good news this time, the installation of the Service Pack 4 components completed without a hitch.

I onced again launched Utilities, this time to upgrade all system and company tables as required. The product validation went on successfully. SP4 rightfully detected the existance of a previous version. I clicked Next to continue with the system database upgrade. This operation completed successfully with the account framework synchronization.

Next was the company update process…this also went substantially smooth and fast. As to the error, I cannot say for sure whether the installation steps call to be on a specific Microsoft Dynamics GP hotfix build prior to beginning the installation of Service Pack 4, or if this is something that is unique to my environment configuration, but at least the word is out. If you find yourself in this situation, you may be forced to upgrade to the latest hotfix prior to the release of SP4 to correct this situation.

There is also another issue with the original Service Pack 4 installation, with SmartList Builder and Excel Report Builder. If you attempt to save a calculation, you will receive the error “Wrong number of arguments to ‘Replace_Text'”.

Fortunately, all you need to do is download a chunk file and apply to solve this problem. You will find a link to the chunk download by clicking on a link under the Current Release Downloads secition of the Service Pack, Hotfix, and Compliance page.

You are now directed to the error page, where you can download a zip file with the fix, under the Hot Topics area.

Applying the chunk was very simple, but is always recommended that you backup your data.

[Updated 09/02/2009]
Chad A., a user on the Dynamics GP newgroup reported having the same problem. This was his reply:

Found the issue! Looks like Microsoft changed the name of the dictionary from dex.dic to dex_us.dic (there is also a dex_int.dic) back in the day. My GP 10 SP3 install had both files (dex.dic and dex_us.dic) at the same version (10.0.320). SP4 upgraded dex_us.dic but didn’t upgrade dex.dic. I checked the dynamic.set, dynutils.set, and dex.ini file but didn’t see it listed anywhere so I just renamed dex_us.dic to dex.dic. That stoped the error message.

We started on GP 7.5 way back in the day. I wonder if that name change got
stuck in the system because we upgrade each time.

Microsoft should probably look into this. I will wait a little bit to see if they catch it, otherwise I may have to send an email to some internal Microsoft GP people I know.

Hope you find this response useful.

Until next post!

MG.-
Mariano Gomez, MIS, MCP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Microsoft Dynamics GP v10 Service Pack 4 now available

The highly anticipated Microsoft Dynamics GP v10 Service Pack 4 is hot out of the oven! Service Pack 4 had been expected by the end of this week, but the development and tesing teams worked around the clock to beat the deadline.

Service Pack 4 addresses a number of issues and delivers new features, such as International ACH Transactions (IAT) and Extender enhancement capabilities for forms and detail forms.

Extender Forms Enhancements

• Create new data entry forms for master files and transactions
• Create SmartLists for each new form
• Add Note windows and Note List windows
• Create conditional and calculated fields
• Use lookups to link to any Microsoft Dynamics GP or third party table
• Generate the next master ID numbers automatically
• Add up to 50 extra windows for each form
• Open associated applications from files
• Use templates to default fields and increase data entry speed
• Create multicurrency forms
• Create views
• Import data into forms

Extender Detail Forms Enhancements

• Create scrolling windows with up to ten user defined fields
• Import data into detail forms
• Add detail forms as quick links on the Home Page

SP4 will bring the Dynamics dictionary build number to 10.00.1368, but be sure to read the Installation Guide topics. There is an issue with Service Pack 4 that changes the OLEPath in the Dex.ini to the default local path. If you use OLE Notes and have a path to a network share, make sure to review the Dex.ini at each client workstation following the hotfix installation to ensure the path is correct. This issue is currently under review for a fix release. The default location for the Dex.ini is C:\Program Files\Microsoft Dynamics\GP\Data.

You can download Microsoft Dynamics GP v10 Service Pack 4 from:

CustomerSource – Click here
PartnerSource – Click here

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com

International ACH Transaction (IAT) functionality for Microsoft Dynamics GP 10.0

For those of you awaiting key EFT enhancements in Microsoft Dynamics GP, here is a bit of news published back in July — a bit late, but still worth sharing.

The National Automated Clearing House Association (NACHA) and Office of Foreign Assets Control (OFAC) have published new specifications for International ACH Transactions (IAT). As of September 18, 2009 the new IAT code will need to be in place that requires participants to identify international transitions and to code these transactions as IAT transactions.

Additional information regarding the specification can be found at
http://www.nacha.org/IAT_Industry_Information/

Microsoft Dynamics GP 10 will support the new International ACH Transaction (IAT) ACH format changes with the release of Microsoft Dynamics GP 10 service pack 4. The electronic file transfer (EFT) formats will be changed for Microsoft Dynamics GP Payables and Receivables modules.

Note: The Royal Bank of Canada file format specification changes will also be included in 10 service pack 4.

The latest word on service pack 4 is that it will be available on the week of August 24.

Until next post!

MG.-
Mariano Gomez, MIS
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

US Payroll Tax Update Rounds

Having a tough time correlating the Last Tax Update date with the actual Payroll Tax Round number released by Microsoft?

Setup > System > Payroll > Payroll Tax
Sweat it no more! The following is a list of payroll tax round numbers with their corresponding dates:

Round 7 – Last Tax Update 7/3/2009
Round 6 – Last Tax Update 6/22/2009
Round 5 – Last Tax Update 4/27/2009
Round 4 – Last Tax Update 3/23/2009
Round 3 – Last Tax update 2/26/2009
Round 2 – Last Tax Update 1/21/2009
Round 1 – Last Tax Update 12/19/2008

Remember, you can always use the automated tax update feature in Microsoft Dynamics GP to get the latest tax updates.

1) Go to MSDGP > Maintenance > US Payroll Updates > Check for Tax Updates, and choose the Automatic update mode from the wizard.

2) Enter your company Authorization Number (usually your main phone number) to continue.

Click on the Log in button to continue. Dynamics GP will then download the latest tax update and apply the latest fixes required to fix bugs found in processing of payroll taxes.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com

Securing VBA projects

It seems that nowadays we can add security to pretty much every aspect of the Microsoft Dynamics GP application, but when it comes to VBA projects things are not too clear for many developers and Dynamics GP systems administrators.

Follow these simple steps to secure your VBA project.

1. Open the Visual Basic Editor. Go to Microsoft Dynamics GP > Tools > Customize > Visual Basic Editor, or press ALT+F11 from your keyboard.

2. Open the project’s Properties window. Right-click on the project within Project Explorer and select the project Properties option. You can also access the project Properties window by highlighting the project and using the Tools menu.

3. Click on the Protection tab, then click on the Lock project for viewing checkmark.

4. Enter a password to prevent unauthorized access, then confirm the password entered.

5. Press Ok to apply the settings.

6. Save your VBA project and exit Dynamics GP. When you access the application next time and attempt to access your VBA project you will be prompted to enter a password to display it’s content.

If you have multiple projects, you will need to apply security individually to each by following the above outlined steps. Hopefully you will use the same password across to make things easier 🙂

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com