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

Read More

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/

Read More

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

Read More

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/

Read More

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/

Read More

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/

Read More

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/

Read More