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

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/

Read More

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/

Read More

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/

Read More