From the Newsgroups: Configuring Business Alerts to report posting errors

Welcome to another edition of From the Newsgroups. In this installment we will look at a question posted in the Partner Online Technical Community newsgroup. The partner needed to know if it was possible to configure Microsoft Dynamics GP Business Alerts to submit posting errors to application adminstrators when using an auto-posting tool.

Q: Is it possible to setup a GP Business Alert to notify someone when a scheduled SOP batch (or any other GP batch) fails to post? The background to this is we are using MC2 GP Agent module to autopost the imported SOP invoice batches. This will happen many times a day. To avoid the need for a person monitoring the automated posting process, we want an alert to be generated when the posting process fails.

The response comes courtesy of Dave Bader, Moderator of the Partner Online Technical Community, enhanced by me with pictures to facilitate the explanation.

I have been looking at the SY00500 which is the Posting Definitions Master and I have an option that may work for you on the Business Alert.

1. Create a new Business Alert.

2. Select the Series as Company.
3. Select the Posting Definitions Master as the Table.

4. In the Define Alert Formula window select the table and use the column name Batch Error Messages 1 and add that to the formula.
5. Select the symbol.
6. In the Constants field enter in 2 single quotes ”.
7. Select the OR symbol.
8. Select Batch Error Messages 2 and add that field.
7. Select the symbol.
8. In the Constants field enter in 2 single quotes ”.

The formula should look like the following.

SY00500_T1.BCHEMSG1 ” OR SY00500_T1.BCHEMSG2 ”

9. Click next and select the notification information and the message text.

10. Click next and enter the schedule information.

11. Click Finish.

Below are the values that can be held in the error message fields.

Batch Error
Messages 1: Indicates a general batch error causing the batch to not post.
1 – Recurring batch posted too many times
2 – Series is missing or invalid
3 – Missing or invalid system-posting settings
4 – Batch currently being posted
5 – Batch currently being edited
6 – User posting access denied
7 – Next Audit Trail Code invalid
8 – No transactions found to post
9 – Batch Source is missing or invalid
10 – Batch Number is missing or invalid
11 – GL posting date is invalid
12 – Batch has not been approved for posting
13 – Actual batch total does not equal the control batch total
14 – Actual transaction number does not equal the control transaction number
15 – Fiscal period for the posting date does not exist
16 – Fiscal period for the posting date is closed
17 – The General Ledger module is not registered
18 – The Receivables Management module is not registered
19 – The Payables Management module is not registered
20 – The Inventory Control module is not registered
21 – The Invoicing module is not registered
22 – The United States Payroll module is not registered
23 – Fatal posting error occurred. Please revert to backup.
24 – Fatal posting error occurred. Please revert to backup.
25 – Actual number of journal entries does not equal the control number.
26 – Another user is editing this batch. Some information may not be updated.
27 – The Sales Order Processing module is not registered.
28 – The Bill of Materials module is not registered.

Batch Error Messages 2:
1 – The Receivables Management module setup information is missing or damaged.
2 – The Payables Management module setup information is missing or damaged.
3 – The General Ledger module setup information is missing or damaged.
4 – The Inventory Control module setup information is missing or damaged.
5 – The Invoicing module setup information is missing or damaged.
6 – The United States Payroll module setup information is missing or damaged.
7 – The Inventory Control module is being reconciled.
8 – The Inventory Control module is changing valuation methods.
9 – The Inventory Control module is changing decimal places.
10 – The Inventory Control module is performing a year end close.
11 – Not enough system resources. Please close some windows.
12 – The Multicurrency setup information is missing or damaged.
13 – The functional currency is missing.
14 – This company does not have access to the selected functional currency.
15 – The functional currency for this company is inactive.
16 – The Purchase Order Processing module setup information is missing or damaged.
17 – The General Ledger posting date is missing or invalid.
18 – The Bill Of Materials module setup information is missing or damaged.
19 – There is no Released transaction.
20 – The Euro Currency ID is inactive.

I hope this information is helpful to you. Please post back if we can be of any further assistance.

Business Alerts can be a powerful tool to assist Microsoft Dynamics GP administrators in their daily tasks by keeping an eye on a number of activities ocurring throughout the system. Definitely, this is one to keep in the arsenal of tips and tricks and one that I expect to see in a future edition of 50 MORE Tips in 50 Minutes session presented by my friend MVP Mark Polino.

Until next post!

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

SQL Mail to be deprecated in Microsoft SQL Server 2008 R2

If your organization is considering upgrading to Microsoft SQL Server 2008 R2 and relies on SQL Mail to generate Business Alerts notifications for Microsoft Dynamics GP, then this is for you.

With the introduction of Microsoft SQL Server 2008 R2, a number of database features will be deprecated, this is, the feature will not be available in a future release of SQL Server — SQL Mail being one of them. You can find the complete list of features that will be deprecated here:

Deprecated Database Features in SQL Server 2008 R2

A couple things about SQL Mail:

1. SQL Mail runs in-process to SQL Server service. The issue here was, if SQL Mail crashed so did your SQL Server. Certainly, not an optimal situation in a production environment.

2. Using SQL Mail requires installing an extended MAPI client component on your server. Most of you would have typically resorted to install Microsoft Outlook to overcome this situation, as without a MAPI client SQL Mail would not run.

So here comes Database Mail:

1. Unlike SQL Mail, Database Mail runs in a separate process thread. If Database Mail goes down, it will not take down SQL Server with it.

2. Database Mail does not require an extended MAPI client component for it to run.

3. Database Mail can be configued with multiple SMTP accounts and with multiple profiles. This increases the robustness of the system. In a realtime environment, if one mail server goes down, Database Mail will still be able to send emails through the other configured SMTP accounts.

4. In Database Mail, all mail events are logged keeping history of emails.

5. You can configure Database Mail to limit file sizes, disallowing large attachments and filtering of file extensions such as .exe or .bat

To configure Database Mail please check the following Microsoft video:

Video source: Technet (http://technet.microsoft.com/en-us/sqlserver/dd939167.aspx)

Be sure to include Database Mail setup as part of your SQL Server 2008 R2 implementation and to test all your Microsoft Dynamics GP Business Alerts in the process.

Until next post!

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

Microsoft Dynamics Top 100 Most Influential People

For the second year in a row, I have been selected as one of the Microsoft Dynamics Top 100 Most Influential People for 2010 by the folks over at DynamicsWorld in the United Kingdom. I have to say that this award is given based on your nominations and consequent votes on the nominations, so to all of you who participated in the process and voting a big Thank You! Your votes have placed me in position #31 in the list, a superb move up the list from last year’s #82.

I happen to share this honor with an extraordinary group of professionals: MVP Mark Polino (#28), MVP Victoria Yudin (#51), Jordanian MVP Mohammad R. Daoud (#44), former MVP Richard Whaley (#91), and partner in crime Microsoft’s David Musgrave (#78).

For the complete list, click here.

Until next post!

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

VBA – Opening a lookup window in expanded mode

Just recently, I came across a question from a partner asking if it was possible to have the scrolling window on a lookup form open in expanded mode, this is, showing information in detail. Traditionally, lookup windows in Microsoft Dynamics GP have always opened showing summary information, take for example the Item Lookup window shown below.

The partner wanted to see the window as shown below when first opened. Note that the scrolling window displays the additional fields added to the big line. This is accomplished by clicking on the switch arrow button that expands the scrolling window.

Of course, this is possible. The following VBA code can be used to open a Dexterity scrolling window in expanded mode by using pass-through Dexterity to set the switch value and run the expand window Dexterity statement.

Window_BeforeOpen()

Private Sub Window_BeforeOpen(OpenVisible As Boolean)    
Dim CompilerApp As Object    
Dim CompilerMessage As String    
Dim CompilerError As Integer    
Dim Commands As String

' Create link without having reference marked    
Set CompilerApp = CreateObject("Dynamics.Application")

Commands = ""    
Commands = Commands & "if 'ASI_LU_Shrink_Expand_Switch' of window IV_Item_Number_Lookup of form IV_Item_Number_Lookup  = 2 then "
Commands = Commands & "   abort script;"    
Commands = Commands & "end if;"    
Commands = Commands & "{set shrink switch out}"    
Commands = Commands & "set 'ASI_LU_Shrink_Expand_Switch' of window IV_Item_Number_Lookup of form IV_Item_Number_Lookup to 2;"    Commands = Commands & "expand window IV_Item_Number_Scroll of form IV_Item_Number_Lookup, true;"

' Execute SanScript    
CompilerApp.CurrentProductID = 1493 ' SmartList    
CompilerApp.CurrentProduct = CompilerApp.CurrentProduct & ""

CompilerError = CompilerApp.ExecuteSanscript(Commands, CompilerMessage)    
If CompilerError  0 Then        
   MsgBox CompilerMessage    
End If

End Sub

NOTE: This script uses a method of calling Dexterity from VBA that is not supported by Microsoft.

Finally, this script can be enhanced by creating a generic function that would allow you to pass the name of the expansion button, the name of the form, main window, and scrolling window, which would then allow you to use it across almost all lookup windows throughout the system.

Until next post!

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

Urban Legends – "I cracked Dynamics GP encryption algorithm!"

It is just about right that I start out this article with the definition of Urban Legend. According to Wikipedia, “An urban legend, urban myth, urban tale, or a contemporary legend, is a form of modern folklore consisting of apocryphal stories believed by their tellers to be true. As with all folklore and mythology, the designation suggests nothing about the story’s factuality or falsehood, but merely that it is in non-institutional circulation, exhibits variation over time, and carries some significance that motivates the community in preserving and propagating it.” The definition seems just about right for what you will read next.

If you are the type who believes everything you read without questioning it, then for your own sake, please stay off the Internet! Rumors — as they can only be referred to — began circulating today on claims of someone cracking the Microsoft Dynamics GP user password encryption algorithm — if you are interested in the original article, click here.

Instead of ranting about the misleading content of the article, I will provide my unbiased, fact-based knowledge of the user password encryption algorithm and Microsoft Dynamics GP security.

Fact 1 – Microsoft Dynamics GP user password encryption algorithm takes into account things like the actual database server’s host name as part of the encrypted password. Hence the reason why passwords need to be reset when the application databases are transferred from one server environment to another without the use of the famous Capture_Logins.sql script.

Fact 2 – Microsoft Dynamics GP user passwords are encrypted on SQL Server using a proprietary encryption code. Hence, the encryption algorithm is not commercially available to any other software vendor or ratherly available on the Internet.

Fact 3 – Having the Microsoft Dynamics GP source code DOES NOT give you access to the password encryption or decryption algorithms.

Fact 4 – You cannot access Microsoft Dynamics GP system or company databases via ODBC with the SQL Server logins corresponding to the Microsoft Dynamics GP users. As a result of Fact 2, a user attempting to establish a connection to SQL Server would be required to authenticate with the encrypted password. The clear-text version of their password, used to authenticate to GP simply DOES NOT WORK. The only way to achieve a connection to GP from an external application is by obtaining a copy of the GPConnectNet.dll .NET assembly or the GPConnect.dll COM component by opening a support case with the Tools team.

Fact 5 – While not impossible, it is virtually impossible to decrypt a Microsoft Dynamics GP user password without having access to the algorithm itself… good luck getting a copy of it anywhere!

Fact 6 – Having access to the Microsoft Dynamics GP system password IS NOT a guarantee of access to the system setup – you should — by now — be taking advantage of the new Role Based pessimistic security model. The fact is, the system password had more relevance in the days of palette menus when options could not be hidden from a user based on their security settings.

Fact 7 – You do not need Microsoft SQL Server ‘sa’ to perform all administrative tasks in GP. In fact, any company who provides their Microsoft Dynamics GP application administrators with the ‘sa’ password should consider firing their database administrators. ‘sa’ is only required to setup new companies and occassionally — read, very occassionally — run third party setup code that has been hardcoded to setup tables and stored procedures with the ‘sa’ user… oh, yes! You know who you are out there.

Other Factual Resources

Microsoft Dynamics GP POWERUSER role vs Microsoft SQL Server sysadmin role @ this blog
Why does Microsoft Dynamics GP encrypt passwords? @ Developing for Dynamics GP
KB article 878449 – How to tranfer an existing Microsoft Dynamics GP installation to a new server

Until next post!

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

CRM – How to match product information between CRM and GP

I have had the fortune to work on a very challenging Microsoft Dynamics CRM and Microsoft Dynamics GP multi-system integration for the past 3 months, requiring lots of custom code to manage above and beyond the common interfaces that can be had with some of the tools available in today’s market. Of course, one of the main challenges has been for the folks building the reports. So today I start a series of quick and easy queries showing how to obtain data from both Microsoft Dynamics CRM and GP, that hopefully will help you build some quick reports, but also begin to find similarities between the two products. Today features the Product catalog from CRM and the Item Master from GP.

select a.ProductNumber, a.ProductId, a.Name, b.ITEMTYPE, b.STNDCOST, b.CURRCOST, b.ITMCLSCD
from ProductBase a
 inner join IV00101 b on (a.ProductNumber = b.ITEMNMBR)

If you have custom fields in CRM for your Product, you can use the following query to tie these in.

select a.ProductNumber, a.ProductId, a.Name, c.ITEMTYPE, c.STNDCOST, c.CURRCOST, c.ITMCLSCD
from ProductBase a
 inner join ProductExtensionBase b on (a.ProductId = b.ProductId)
 inner join IV00101 c on (a.ProductNumber = c.ITEMNMBR)

Note that in the above query, I have not included any columns from the ProductExtensionBase table. The reason? It’s custom! You can include any name for the custom columns you have created here.

If you are one of those who are comfortable using the CRM views, then this query will provide a one-stop shop to obtain all the information from both the ProductBase and the ProductExtensionBase tables.

select a.ProductNumber, a.ProductId, a.Name, b.ITEMTYPE, b.STNDCOST, b.CURRCOST, b.ITMCLSCD
from Product a
 inner join IV00101 b on (a.ProductNumber = b.ITEMNMBR)

Note: the above query assumes you have a linked server connection between your Microsoft Dynamics GP and CRM servers and that you have created synonyms for the objects referenced or that you have at least arranged for these tables to be replicated to a single location.

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

From the Newsgroups: Map icon becomes unavailable on Windows Server 2008 64-bit

In another edition of From the Newsgroups we explore an issue found in Microsoft Dynamics GP 10.0 on 64-bit Windows Server 2008 or Windows Server 2008 R2. This issue was discovered as a result of a recent migration from Windows Server 2003 to Windows Server 2008.

Q: Regarding the map icon in GP10 – for example, where you can open a map of a customer’s address in MapPoint or in Bing Maps…

My customer has just deployed new 64 bit Windows 2008 servers on their Citrix farm to replace the existing 32 bit Windows 2003 servers. I’ve migrated GP10 (SP4) over to the new servers, however the map icon is no longer visible. Also if I go to User Preferences, the drop-down list at the bottom is grayed out (usually allows you to specify whether to display maps in MapPoint or MapPoint Web). Anyone come across this? Is [this] not compatible with 64 bit machines or Windows [Server] 2008?

The answer given to this question by David Musgrave, Escallation Engineer at Microsoft will surprise you a bit, but will also enlight you as to what’s going on with your MapPoint icon:

There are two issues at play here.

One is that Dexterity only can only identify specific operating systems that were available when that version of Dexterity was released.

The Dexterity function Runtime_GetOSInfo() for v10.0 understands up to Windows Server 2008. The v11.0 (GP 2010) function also understands Windows 7 or Windows Server 2008 R2.

The second issue is the code in the MapPointOptionEnabled() of form syMapPoint function which is used to decide if MapPoint functionality should be enabled.

The v10.0 (SP4) code is looking for specific OS values and this does not include Windows Server 2008 (even though Dexterity v10.0 understands that OS).

The GP 2010 code is looking for any OS equal to or later than Windows XP and so no longer needs to be updated every time Dexterity is updated to support a new OS.

In summary, v10.0 MapPoint will not be enabled on Windows Server 2008. This is fixed in GP 2010.

While I would say this is a bug, it is already fixed in the current build and will not have a high enough priority to get into a v10.0 Service pack.

Even if you don’t take advantage of the mapping functionality in Microsoft Dynamics GP 10.0, it’s always good to understand why you are not seeing the icon when you are on Windows Server 2008.

Until next post!

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

Decisions 2010 Spring Conference – Day 1

I woke up this morning thinking to myself Decisions 2010 Spring Conference would be “just another webinar”. When I logged on to the Conference site I was confronted with a true virtual conference environment with expo halls, booths, and even a lounge where you could “hang out” and chat with other users.

The idea of being able to go to the different virtual spaces was amazing to say the least. The application platform was simply breath taking. I then decided to take a tour through the different exhibit halls. I quickly rushed into the East Exhibit Hall where I “came across” the folks of Accellos, FastPath, and Flexible Solutions.

One thing to note about the virtual expo environments, is the background sound of people transiting around the expo and talking, conveying the sense of activity.

I stopped over at the Flexible Solutions booth where I chatted with MVP Victoria Yudin and Mickie Stamm. The team seemed a bit busy, so I left after 5 minutes to visit other areas of the Conference.

After visiting the East Exhibit Hall, I “went” to the West Exhibit Hall area. I probably spent some 15 to 20 minutes touring around the exhibits and saying hi to a number of people who I recognized by name.

Like with any real-world expo halls, navigating through these and saying hi to everyone just seemed a bit tiresome. So, off I went to the Lounge area. The Lounge was very busy with a lots of users and partners exchanging experiences via the chat platform.

I introduced myself to a few individuals and had to quickly switch my focus to my session. My session was scheduled to begin at 1:00 PM EDT with tech check at 12:30 EDT. Tech check went well with some issues around the sound quality of the VoIP phone in my hotel here in Las Vegas. Little before the conference was scheduled to begin I asked the magic question: how do I share my screen with the attendees? The silence on the other side of the line told the whole story. I won’t have that option.

Regardless of the issue at hand, I decided to move forward. My session began with opening introduction by Mr. David Gumpert, Editorial Director at MSDynamicsWorld and one of the individuals responsible for the organization of the Decisions 2010 Spring Conference. Once the introduction was over, I began talking through my slides explaining the benefits of managing security with the Support Debugging Tool. I covered two main topics: the Security Profiler and the improved Security Information window, and believed to have gotten the central message of my presentation across: “use Support Debugging Tool to administer your Microsoft Dynamics GP security… it just makes all the sense in the world and it’s free!”. Still, I walked away from the session with a bit of frustration for not being able to deliver the demo I had planned.

Attendance to the session was 65-strong, with questions revolving around the future of Support Debugging Tool, new features to be released in upcoming builds, and deployment options. The session ended very quick as the allotted time was only 30 minutes.

Resources

Support Debugging Tool on this blog – Click here
Support Debugging Tool Portal on Developing for Dynamics GP – Click here
Support Debugging Tool past conference materials – Click here

For more coverage on Decisions 2010 Spring Conference day 1, please read the following articles by fellow blogger Jivtesh Singh:

Jeff McDowell’s ‘Are You Ready for Your Next Audit with Microsoft Dynamics GP?’
Amber Bell’s ‘Discovering Dynamics GP Features that Add Value Now’ Presentation
Mariano Gomez’s Dynamics GP Security Administration
Decisions 2010 – Booths and Dynamics GP Sessions

It’s still not late for you to visit the Decisions 2010 Spring Conference site, after all it’s a virtual environment. You still have a chance to catch the wrap up of the conference with interesting interactive user sessions.

Until next post!

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

Decisions 2010 Spring Conference

Ok, so you missed Microsoft Dynamics Convergence 2010… as hard as that it is to believe, I do understand. But what’s your excuse for not attending Decisions Spring 2010? May 19 – 20, MSDynamicsWorld launches the largest, free online conference for anyone who owns (or leases, or borrows) a computer with access to the Internet and wishing to learn something about Microsoft Dynamics… yes, not only GP, but also all the other products.

You will also have a chance to listen to some of the well known names in the community: MVP Mark Polino, MVP Frank Hamelly, and yes, me! Now, just click on the picture to the left to register. This is not just another webinar, it’s a fully fledge online interactive experience, with virtual booths and sponsors. Please register and attend our sessions.

Until next post!

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

New article on MSDynamicsWorld: Why the Phaseout of the Microsoft Dynamics Public Newsgroups Is a Good Thing

“Personally, I believe this transition is the best outcome for the Microsoft Dynamics GP community in general…”

My new MSDynamicsWorld article is out! Since I featured the article on the newsgroups being phased out on this blog, I have received numerous inquiries on the subject, so I just decided to expand on the article over at MSDynamicsWorld.

Please be sure to read the follow up here.

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