The Dynamics GP Blogster’s best articles of 2010

Wow! 2010 is on its way out and this mark the second year I have the opportunity to publish a list of the best articles I wrote this year. The Best of 2009 was a hit, so let’s give this year’s a chance:

1. Using Business Alerts with Extender Tables (Jan 12) – This one came straight out of the newsgroups with Microsoft’s Dawn Langlie providing a neat solution.

2. Autogenerating Customer IDs (Feb 28). Many of you seemed to have found this one very useful for whaterver projects you were working on. Up to today I still receive offline notes of thank you! for this script.

3. Microsoft Dynamics GP Architectural Foundations featuring Tim Brookins (Mar series). Many of you who had been in the channel for a while, enjoyed reading this forgotten and hard to find whitepaper, which is still today one of the best produced on the topic, even though the Dynamics GP architecture has evolved over the years. Unfortunately, Tim Brookins was not available for the closing remarks on the series.

4. Microsoft Dynamics Convergence 2010 (Apr series). For those of you who could not go to Convergence, I made an interesting series of articles with tons of pictures of the event and tapped out with the closing materials for the sessions presented by David Musgrave and I. Better yet, we will be present at Convergence 2011 Atlanta!

5. Urban Legends – “I cracked Dynamics GP encryption algorithm!” (May 21). This article was created in response to a widely circulated article claiming at the time week password encryption in Microsoft Dynamics GP. Followed were a slur of articles and official response from MS calming the fears raised among customers. In all fairness that post was later updated and corrected by the author.

6. Dexterity and Intellisense. (Jun 23) In an attempt to get a response from the community and MS, I launched a campain and created a product suggestion to implement this feature in the Dexterity. Low and behold, my friend Tim Gordon developed DexSense to fill the void.

7. Enforcing Password Policy with Microsoft Dynamics GP (Jul 15). In this article featured a SQL script that many of you who administer Microsoft Dynamics GP have found useful, as it allows you to track what GP user passwords will expire.

8. Using SQL CLR stored procedures to integrate Microsoft Dynamics GP and Microsoft CRM (Aug series). In this article, I featured a technique to integrate Microsoft Dynamics GP with Microsoft Dynamics CRM using SQL CLR stored procedures. All I can say is, the client cannot be any happier!

9. Microsoft Dynamics GP 2010 Word Templates summary (Sep and Oct series). Working on a series of Word Template topics, I had a chance to exchange ideas with the none other than Microsoft’s Rob Wagner, a.k.a. the Word Templates god. This wrap up containes links to all the articles published in the series.

10. How to change your Microsoft Dynamics GP 2010 Map Services (Nov 12). As the Third Law of Newton’s physics suggests, this article was created in response to a bug in the map button available next to all address ID fields in Microsoft Dynamics GP. Since I published this article, I have had requests to create interfaces to some truly obscure map services. The answer remains the same… let me think about it!

11. Do I have to use those “Z-” currency IDs in GP?. Not even 2 weeks into publishing this article and I have had a number of comments and emails flowing into my inbox with a common theme: really? I did not know that! Well, now you know.

I hope you enjoyed the selection and if you think I missed something just chime in with some of your best articles.

Until next post!

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

Season Greetings to all the Microsoft Dynamics GP community!

Our Christmas Tree here at home

As many of you prepare to celebrate these festivities in company of family and friends, I would like to take this opportunity to wish you the best throughout these Holidays on behalf of my wife Marina and children.

Thanks for all the great comments posted throughout the year and the support you have shown to The Dynamics GP Blogster and all the different outlets provided. It keeps me going and working to bring you the some of the best content you will find about all things Dynamics GP: the events, the technical articles, the series, and much more.

You can always count on 2011 bringing new and exciting articles with innovative approaches for using Microsoft Dynamics GP and getting the best out of your investment.

Until next post!

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

From the newsgroups: Tracking COBRA in Microsoft Dynamics GP Payroll (US only)

This week’s answer comes courtesy of Microsoft’s Aaron Richards over at the Partner Online Technical Community, but first the question — no names given to protect the innocent 🙂

We are using Dynamics GP2010. How do we track COBRA in Payroll. We are not using HR.

Specifically, we are trying to track what the government reimbureses us. For example, we’re trying to track 70% that we pay to the insurer on the 941 that we send to the government which eventually reimburses back from the government that we had paid.

Please advise or point me to a reference for utilizing this.

As stated by the partner, the customer is not using the HR module which has full COBRA tracking capabilities. Here is what Aaron had to say:

Thank you for using Microsoft Online Communities. My name is Aaron and I will be assisting you with your questions today. This information was released when we started tracking COBRA in the hotfix pdf.

It stated the following:

Consolidated Omnibus Reconciliation Act (COBRA) changes

The recently-passed economic stimulus legislation (American Recovery and Reinvestment Act of 2009) establishes an employer-provided 65% COBRA premium subsidy for certain workers who lost their jobs between Sept. 1, 2008, and Dec. 31, 2009. The employer is reimbursed for the subsidy by claiming a credit on quarterly federal tax returns (Form 941). For details of the Act’s provisions relating to COBRA, see http://www.irs.gov/newsroom/article/0,,id=204505,00.html at the IRS site, and http://www.dol.gov/ebsa/COBRA.html at the Department of Labor site. The March 2009 Round 4 U.S. Payroll Tax Update contains the following Payroll and Human Resources changes to support the COBRA premium subsidy.

The Payroll Setup window contains new fields that allow you to select and display a COBRA subsidy benefit code.

The Cobra Premiums and Payments window in Human Resources contains a new field that allows you to indicate whether a payment is an employer-paid COBRA Premium Subsidy. If it is, you can enter or select a Batch ID for the payment (the batch lookup will display only manual check batches). These fields will be accessible only if you have assigned a COBRA subsidy benefit code in the Payroll Setup window.

The Quarterly 941 Preparation report and the Form 941 report have been changed to display COBRA subsidy information. Line 12a shows the total of the posted COBRA subsidy benefits for all employees during the associated quarter. Line 12b shows the number of employees receiving COBRA subsidy benefits during the associated quarter. The changes are included on both single-company and cross-company quarterly 941 reports.

Additionally, lines Line 7d through 7g are removed from Form 941, and Line 7h (Total Adjustments) becomes Line 7d. The form layout is changed to reflect the new field positions.

The Act specifies that employers must notify certain current and former participants and beneficiaries about the premium reduction. The U.S. Department of Labor has posted links to four model notices at http://www.dol.gov/ebsa/COBRAmodelnotice.html. You will need to decide which notification to use. A link to the Department of Labor Web site has been added to the Human Resources COBRA Recipients Lists window. There are no changes to the COBRA notifications generated by Human Resources.

March 2009 Round 4 U.S. Payroll Tax Update – 4

Setting up the COBRA subsidy
1. In the Benefit Setup window, create a new benefit code to track the employer COBRA subsidy.

2. In the Payroll Posting Accounts Setup window, assign General Ledger account codes for the employer subsidy payment (Benefit Expense and Benefit Payable).

If you do not want to post the amount to General Ledger, you can use the same account for both codes.

3. In the Payroll Setup window, assign the new benefit code as the COBRA subsidy benefit.

4. For any employees electing to receive COBRA benefits and eligible for the 65% subsidy, assign the new benefit code to the employee, using the Employee Benefit Maintenance window. Mark the Transaction Required option.

5. Inactivate the employee pay records, as well as all other deduction, benefit, and tax records for the employee.

6. Keep the employee record as well as the new COBRA benefit active.

Processing a COBRA subsidy if you are using only Payroll

1. In the Payroll Manual Check-Adjustment Entry window, enter an adjustment transaction for the employee.

2. In the Payroll Manual Check Transaction Entry window, specify the COBRA benefit code and the amount of the subsidy.

3. Post the adjustment transaction, which updates the benefit code.
4. At quarter end, print the Quarter End 941 report, which includes the COBRA subsidy amount.

I hope this in-depth review helps with your COBRA strikes.

Until next post!

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

Add-In Initialization Error when launching Microsoft Dynamics GP 2010 – Follow up

If you read my previous article on some table open operation issues I was faced with at a client, you know by now that it’s important to have the proper writer permissions on the local %temp% folder. What I did not mention in that article was an issue we were also facing with the new Dynamics Online Services application.

Upon launching Microsoft Dynamics GP 2010 we were also receiving an add-in initialization error as shown below:

Add-In Initalization Error

Since last month I wrote an article about this same issue, I wanted to make sure I kept this topic separated from the table open operation issues, even though they are directly related by the solution. As it turned out, writing permissions (or the lack thereof) on the %temp% folder caused the Dynamics Online Services application to fail as well with an add-in initialization error message. This error popped up a large window (shown above) detailing the nature of the problem. However, after re-enabling access to the %temp% folder the error no longer presented itself — Another one bites the dust!

Until next post!

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

An open operation on table XXX failed because path does not exist

Who hasn’t come across this error? If you have been long enough in the Microsoft Dynamics GP world, you will probably even have a prescribed set of steps to address this issue:

1. Restart the SQL Server service to clear all tempdb database tables from memory
2. Clear out the local temp folder
3. Making sure the temp folder actually exists
4. Making sure the %TEMP% environment variable exists
5. Making sure your anti-virus software is not placing some sort of restriction on the folder

And then some more…

Just recently I ran across this issue with Microsoft Dynamics GP 2010 and I thought, how bad can this be? I started by walking through the prescribed steps as mentioned above, but still could not resolve the issue. I also remembered that my friend Allan Cahill over at Developing for Dynamics GP had blogged about this in his article Unexplained Temp Table Errors so I went checking his findings. In his case the issue had to do with the Anti-virus software interfering with the temp directory during scanning for viruses. So, we did the same at the client with no positive outcome.

I then remembered that my also friend David Musgrave had written a more recent article on the issue and in his article he detailed that the same issues could occur if the user lacked write permissions to the folder specified by the %TEMP% environment variable. I went inquiring with the client’s IT department and it turned out they had just rolled out some new Group Policies which inadvertly disabled access to the %TEMP% folder.

The client proceeded to make the necessary adjustments via Group Policy and had all users reboot their computers. Now Microsoft Dynamics GP was happy! No more errors! It’s amazing how complex things have become since my first days with v3.15 and Windows 95. There’s sure much more involved today and much more to look out for. If you don’t believe me, read my last month’s article Microsoft Dynamics GP – More than just Program Files!

Following this post, I will also publish an issue that we found as a result of the permissions issue on the %TEMP% folder.

Until next post!

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

SmartList Builder "Display as a negative value based on field" option not working as expected

Just recently on a newsgroup forum, a partner brought up an issue affecting SmartList Builder. The partner had just recently upgraded the client from version 9 and was testing the Smartlists the customer had built prior to the upgrade to ensure they were still working as expected. In the partner’s own words:

“It was working when we were on GP9, but now we’re on GP2010 SP1 none of my fields set up with Negative Values are showing up with Negative Values. When I run the smartlist from the old GP9 server, amounts are showing negative amounts for Return documents. But when we run the smartlist on GP2010, the amounts are now positive amounts, and when I check the Set Field Options window, they are still set up to “Display as negative based on field” when SOP Type = Return.”

In order to verify this I launched GP 2010 and recreated the SmartList mentioned above by the partner, setting the field option for the Document Amount to display a negative value based on a SOP Type of Return:


Set Field Options window

After saving the SmartList Builder option, I launched SmartList and had it build the newly created option. Once I checked the entry I had just created, effectively Returns were no displaying as negative.



BUG: Returns document amounts not displayed as negative

This bug seems to be confined to Microsoft Dynamics GP 2010 RTM (Build 11.00.1247) and Microsoft Dynamics GP 2010 SP1 (Build 11.00.1524). I could not replicate this issue for version 10.0 SP5 (10.00.1579) or earlier.

As a workaround, you can create a SmartList Builder calculated field to change the sign of the amount being displayed based on the SOP Type, as follows:

Add Calculated Field

NOTE: You can perform a similar CASE statement for any other SmartList you have created based on the field(s) criteria you are using to render the value a negative value.

This problem has been reported to Microsoft and a bug report is being written up to address the issue as of the publishing date of this article. For more information, contact Microsoft Dynamics GP Support.

Until next post!

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

How to display an image at the User Interface level

One of the questions I often get is, can you dynamically display an image on a Microsoft Dynamics GP form or report. The answer is No, it’s not possible. The reason for this is, Microsoft Dexterity, the tool used to developed your beloved Microsoft Dynamics GP, is only capable of displaying images stored within the dictionary itself. Since the picture control in Dexterity is binded to these pre-stored images, this obviously would indicate that there is no room for dynamic images to be added. In addition, the Dexterity picture control cannot be referenced or manipulated programatically as Dexterity does not treat this object like it does with any other fields.

One *exception* may be the adding these pictures via Modifier, but even so, this is not a dynamic manipulation of a picture control. In summary, not possible!

Of course, you can always resort to VBA. The objective of this post is to see how you can use VBA to aid in displaying images where needed. One typical application is displaying product images or employee pictures. Today’s example will look at displaying product images. For this customization we will add a button to the Item Maintenance window, which in turn will call a VBA user form. Our user form contains a picture control which will display the product image once the window opens. We will use the image link on the Item Internet Information window to store the path to the actual image file.

NOTE: The Item Internet Information window can certainly display a picture, but it calls the default application associated with the extension of the file being loaded. Not practical if you want to avoid users damaging the actual image file.

1. Modify the Item Maintenance window to add a button control. To accomplish this, we will use Modifier to add the button control. Once the control has been added, we will return to Microsoft Dynamics GP to grant security to the modified window.



Item Maintenance window in Modifier Note the button control added next to the other buttons (click image to enlarge)

Upon returning to Microsoft Dynamics GP, we grant security to the modified window and the result should be as follows:

Item Maintenance window

2. For the next step, we will add the window, the Item Number field, and the newly added Image button to Visual Basic for Applications. We can then proceed to create a simple user form with a picture control as shown below:

For this example, we have renamed the picture control object to ctrlImage and the user form to frmImage.

3. The following piece of code is added to the Image button’ BeforeUserChanged event on the ItemMaintenance object:

Image_BeforeUserChanged()

' Created by Mariano Gomez, MVP
'  This code is licensed under the Creative Commons 
'  Attribution-NonCommercial-ShareAlike 2.5 Generic license.
Private Sub Image_BeforeUserChanged(KeepFocus As Boolean, CancelLogic As Boolean)
    If ItemNumber.Empty = False Then
        frmImage.Show
    Else
        MsgBox "Please select an Item Number to continue"
    End If
End Sub

The above code opens the frmImage user form if the ItemNumber field is not empty.

4. We can now add the code to load the image based on the path stored in the Internet Addresses table (dbo.SY01200) in the company database. For this, we will use the UserInfoGet object to open an ADO connection to the company database and use a recordset to retrieve the path stored for the specific item. In addition, we will use the LoadPicture() function to read the file containing the actual image into the picture control.

UserForm_Activate()

' Created by Mariano Gomez, MVP
'  This code is licensed under the Creative Commons 
'  Attribution-NonCommercial-ShareAlike 2.5 Generic license.
Private Sub UserForm_Activate()
    Dim oCn As New ADODB.Connection
    Dim oCmd As New ADODB.Command
    Dim oRst As New ADODB.Recordset, picRst As New ADODB.Recordset
    Dim oStream As New ADODB.Stream
    
    ' Establish connection to company database
    Set oCn = UserInfoGet.CreateADOConnection()
    With oCn
        .CursorLocation = adUseClient
        .DefaultDatabase = UserInfoGet.IntercompanyID
    End With
    
    If oCn.State = adStateOpen Then
        With oCmd
            .ActiveConnection = oCn
            .CommandType = adCmdText
                        
            ' Command to retrieve image path
            .CommandText = "SELECT INET4 FROM SY01200 WHERE Master_ID = '" & ItemMaintenance.ItemNumber & "' AND Master_Type = 'ITM'"
                    
            Set oRst = .Execute
                    
            If Not oRst.EOF Then
                ctrlImage.Picture = LoadPicture(oRst!INET4)
                ctrlImage.PictureSizeMode = fmPictureSizeModeStretch
                
            Else
                MsgBox "Could not find an image file for this item"
            End If
                    
            oRst.Close
        End With
    Else
        MsgBox "Could not connect to company database " & UserInfoGet.IntercompanyID, vbCritical, "Connection Error"
        Set oCn = Nothing
    End If
End Sub

5. Finally, some sample data… I will be using a picture of a processor, stored in the path indicated by the window field:

Internet Information window

Once we are done, we can test our customization by clicking on the Image button on the Item Maintenance window:

Item Maintenance window with Item Image user form window

It’s great to see how VBA can help with everyday tasks that may otherwise seem complex. Hope you enjoyed the techniques used in this customization and provide your feedback. There are a few enhancements I can think of, but this should get you started in the right direction.

Downloads:

Item Maintenance and User Form package and Image zip file – Click here

Notes:

a) To test, change the path of the image on the item Internet Information card.
b) You may require a reference to Microsoft ActiveX Data Objects under the Visual Basic Editor to have this code execute properly.
c) This customization is compatible with Microsoft Dynamics GP versions 10.0 and 2010

Until next post!

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

Do I have to use those "Z-" currency IDs in GP?

I know this seems like a trivia question, but recently I have come across a number of clients who have been using Microsoft Dynamics GP for a number of years and have been told by their former partners that they *must* use the predefined currency IDs created after adding the sample company, with no real explanation to support these claims. Case in point, my new client in Saudi Arabia, has been struggling for years to understand why they were told to prefix all currency IDs with the “Z-“. So they went ahead and setup “Z-SR” for the Saudi Real.

If you are familiar with Microsoft Dynamics GP and the sample company, Fabrikam, you may have noticed by now that after adding the sample company (which is not a requirement by any means), you will get a number of predefined currencies such as Z-US$ (US Dollars), Z-UK (British Pounds), Z-C$ (Canadian Dollars), Z-AUD (Australian Dollars), and so forth, accompanied by a number of exchange rate tables. If you are not familiar with the sample company, then we have a totally different set of problems :-).

So here are some common questions and the answers I usually provide to them:

1. What are these “Z-” currency IDs in GP?


These currencies were created as part of the sample company, Fabrikam (formerly The World Online, Inc). The reason these currencies exist is so you can test transactions in various currencies in the sample company without worrying about additional setups, i.e, exchange rate tables. Because currencies are a system wide setting, they can be used across all companies created in the system.

2. Why is every currency ID prefixed with a “Z-“?

If you add true currency IDs following standard ISO currency IDs, you will notice that all the prefixed “Z-”  currencies provided with the test company will show up at the bottom of the Currency Lookup window, whether you are using a Dictionary Order Case Insensitive (DOCI) or Binary sorting at the database level. Microsoft SQL Server sort order establishes the behavior of many lookups in GP. In other words, if a currency ID is setup as say AUD or USD, these will take precedence over the currencies that begin with “Z-” making it easier for a user or system administrator to find information in the lookup.

3. Do I have to use the sample company currency IDs for my production environment?

Categorically No! These currencies have been provided for test purposes with Fabrikam, hence the sample company designation. However, you may find that working with these *out of the box*, preconfigured values may save you time in setting up other companies IF you decide to go down this route. Nonetheless, you will need to evaluate whether the configurations suite your company specific needs.

I generally tend to recommend setting up currency IDs using standard International Standard Organization (ISO) codes. ISO standard 4217 lists all currency IDs and their corresponding numeric code. Please use this when possible as it facilitates other configurations in the system, for example Customer and Vendor records.

4. If I choose not to use the sample company currencies, can my new USD currency coexists with the sample company’s Z-US$?

Categorically Yes! So long you assign the proper currency ID to your production company and, if using multicurrency, setup the proper exchange rate tables referencing all other currencies you may use and configure, then there’s no reason for interference with the preconfigured, sample currencies used by Fabrikam.

Well, I hope these simple answers help with your Z-currency nightmares.

Until next post!

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

Adobe PDF Converter error when sending report to PDF in Microsoft Dynamics GP

Just recently, I was working on a few Report Writer reports for a client and assisting with installing the latest Adobe Acrobat Standard version for them. After Adobe was installed, we decided to run a few tests to attempt to send some of the modified reports output via email using the Send To > Mail Recipient PDF option on the Report Output window.

Send To > Mail Recipient PDF

Upon choosing the Mail Recipient PDF option, we received the following error:


Adobe PDF Converter error



The error seems to come from the Adobe PDF Converter application which suggested that our PDF conversion process must rely on the system fonts and use document fonts. In addition, the message provided the path to address the issue as well, so we followed.

Initially, you must go to the Devices and Printers panel in Microsoft Windows, then right click on the Adobe PDF Converter printer and choose Printer Properties.

Devices and Printers panel

Once the Adobe PDF Converter Properties window opens, click on the Preferences button

Adobe PDF Converter Properties window

The Preferences button will now open the Adobe PDF Converter Printing Preferences window, where the option referring to the specific error comes to surface:

Adobe PDF Converter Printing Preferences window

Once the checkbox was unmarked, we were able to re-test our reports and everything worked just as intended.

If you ever run across this error, just know that the fix is very simple.

Until next post!

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

Enabling a Report Writer document as a Word Template

Ok, this is directly or indirectly a Report Writer subject, but nonetheless a topic related to Report Writer. In this example, I take the General Posting Journal transaction document and enable it under Word Templates. However, this example will not develop the Word Template itself, but rather show how easy it is to make the journal document template enabled.

The steps are very simple and in fact, with a little bit of creativity, a developer could potentially write code that quickly enables critical reports.

To enable an existing Microsoft Dynamics GP report:

1. Register a trigger against the IsTemplateEnabledReport() function of the syReportLookup form. Template enabled reports can be selected in the Reports lookup window.

Startup

{ Created by Mariano Gomez, MVP
  This code is licensed under the Creative Commons 
  Attribution-NonCommercial-ShareAlike 2.5 Generic license.
}
local integer l_result;

pragma (disable warning LiteralStringUsed);

l_result = Trigger_RegisterFunction(function IsTemplateEnabledReport of form syReportLookup, TRIGGER_AFTER_ORIGINAL, function glPostingJournalReport);
if l_result <> SY_NOERR then
  warning "Trigger registration for General Posting Journal report failed.";
end if;

pragma (enable warning LiteralStringUsed);

Note: You will need to save the Startup script first (CTRL+S), and compile the dictionary when the trigger processing function is implemented.

2. Now that we have our Startup script and the trigger registered, we can implement the trigger processing function. In this case, I have labeled this function glPostingJournalReport().

glPostingJournalReport()

{ Created by Mariano Gomez, MVP
  This code is licensed under the Creative Commons 
  Attribution-NonCommercial-ShareAlike 2.5 Generic license.
}
function returns boolean result;

in 'Product ID' nProdID;
in Resid nResID;

if nProdID = DYNAMICS then

 {It's our report. Indicate that it is template enabled.}
 case nResID
  in [resourceid(report 'General Posting Journal')]
   result = true;
 end case;
end if;

Note: In the above code I am using the dictionary constant DYNAMICS for the product ID as our report belongs to the core dictionary. Also, rather than hardcoding the resource Id for the report, I am using SanScript’s resourceid() function to obtain the resource Id for the report.

3. Open Dexterity Utilities to extract and autochunk your dictionary into a final product. The following are the settings I used for this particular project:



Auto-Chunk and Product Information windows

4. Drop your chunk file in the Dynamics GP folder and make sure that you can see your trigger in action in the Reports lookup window under Report Template Maintenance.



Reports Lookup window


Now that we have won half the battle, I will show in my follow up post, how to actually implement the template and make it all work together.

Downloads:

Extracted dictionary – Click here
Chunk file – Click here

Until next post!

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