Removing Sample Company Date Message in Microsoft Dynamics GP v10

This one comes courtesy of Leslie Vail, MVP directly from the Microsoft Dynamics GP Technical Airlift, held in Fargo, North Dakota from May 28 – May 29. Leslie was in awe with a few new tricks she learned from the Dex god — you need to be around long enough to know who this person is, go Perth!

How many times you wish you could vanish that annoying sample companany date message box? How many times you wished you could set the user date to something else when login in? Everyone knows that a customer demo is crucial and 2017 is not quite what most customers have in mind when you open up a demo environment. The answers are all here now! You are looking at a few new Dex.ini settings that allow you to do just that!

Edit the dex.ini file and include one of the following:

SampleDateMsg=FALSE – will prevent the dialog box from being displayed while leaving the date as April 12, 2017

SampleDateMMDDYYYY=00000000 – will prevent the dialog box from being displayed and to use the current system date.

SampleDateMMDDYYYY=MMDDYYYY – will prevent the dialog box from being displayed and to use a user-specified date (replace the placeholder MMDDYYYY with the month, day, and year. For example, May 15, 2015 would be: SAMPLEDATEMMDDYYYY=05152015)

Enjoy these Dex.ini settings! I know I am.

Until next post!

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

Microsoft Dynamics GP 10.0 Service Pack 2 Now Available

What everyone was expecting! Microsoft Dynamics GP 10.0 Service Pack 2 is now available to customers and partners from CustomerSource or PartnerSource, respectively. The following is a summary of all service pack version numbers:

Dynamics GP v10 No Service Pack —————- 10.00.0774
Dynamics GP v10 Service Pack 1 —————— 10.00.0903
Dynamics GP v10 Service Pack 2 —————— 10.00.1061

To locate your current product service pack level, choose the Help > About Microsoft Dynamics GP option.

The following “Important Notes” have been released with Service Pack 2:

**IMPORTANT NOTES**

1. If you use Manufacturing, you will need to run a Rollup and Replace in the Standard Cost Changes window (Cards Manufacturing Inventory Standard Cost Changes) following any patch installation.

2. If you have a multi-lingual environment with English client installations and translated client installations, click HERE to review a table listing the .MSP files that need to be installed for each client. All Microsoft Dynamics GP clients MUST be running at the same version build. If the client installation does not have the matching .MSP file installed, Microsoft Dynamics GP will not launch.

3. It is highly recommended to export your modified reports and forms to package files prior to installing Service Pack 1. The reports and forms can be exported under Tools Customize Customization Maintenance.

4. If you have customized the eConnect PRE or POST procedures, make a backup of those procedures prior to installing any patch for Microsoft Dynamics GP.

For a complete list of fixes and updates available with Service Pack 2 consult the Microsoft Dynamics GP 10 Service Pack 2 Installation Guide.

Until next post!

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

In-Transit Transfer Transactions in Microsoft Dynamics GP 10.0

For those of you who have embarked in the implementation of ITTT, please be aware of the following issues in Microsoft Dynamics GP 10.0:

1. When you post an In-Transit Inventory transaction at the transaction level from the Receivings Transaction Entry window, posting will finish. However, the data in the Inventory tables will be incorrect. You can navigate to the Receivings Transaction Entry window from the Transactions menu, point to Purchasing, and then click Receivings Transaction Entry.

2. When you try to post a Purchasing Transaction batch from the Purchasing Batches window that contains an In-Transit Inventory transaction, the processing stops with the progress bar at 100% and the posting process does not finish. You can navigate to the Purchasing Batches window from the Transaction menu, point to Purchasing, and then click Purchasing Batches.

The above issues have been resolved with the latest patch release for Microsoft Dynamics GP 10.0. (Service Pack 1).

3. When you use the In-Transit Transfer in the Receivings Transaction Entry window the quantity decimal places are being truncated to 0, therefore, only showing a whole number. This is causing the quantity to only partially transfer.

4. When you post an In-Transit Transfer transaction from the Receivings Transaction Entry window or Receiving Batches window, the incorrect account is used when using site segments. The debit and credit both pull from the To Site account.

5. When you post multiple in-transit transactions on one In-Transit inventory receipt, duplicate records are created in the Inventory module without posting the transactions to the Purchase Order Processing tables. This problem occurs when you save the transactions to a batch, and then print the edit list. To work around this issue, follow one of these methods:Use transaction level posting instead of batch postingPost the transaction by batch without printing the edit list

6. When you use the in-transit transfer functionality in the Receivings Transaction Entry window, the currency amounts are rounded to 2 decimal places instead of remaining at 3 decimal places.

Because of these issues, it is recommended that you do not use the In-Transit Transfer functionality in Microsoft Dynamics GP 10.0 at this time. Microsoft has confirmed that this is a problem with the Inventory module and is working to correct the issue, which will probably be delivered with Feature Pack 2 (that’s right a Service Pack with more functionality!).

Until next post!

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

Displaying the Account Number Separator Character for SmartLists Created With SmartList Builder

This one was rescued from the Microsoft Dynamics GP community newsgroup and I have to admit, it’s perhaps one of the darn best Dynamics GP tips I have seen in a while — and trust me, I have been around that long. Transcript of the question (courtesy of Bob Cool) and the response (courtesy of Habib Salim) in the newsgroup is as follow:

Bob asks:

I’m trying to figure out how to define a SmartList using Builder so that when you search on the Account Number, you get the Value box that splits the account into its associated components.

For example, our account numbers consist of three segments. If I enter a search criteria for the account number in any one of the standard SmartLists, the Value box has three distinct pieces, each separated by a hyphen.

If I use the GL00101 table in Builder, I would have to create a calculated field, concatenating all 3 segments together – but that won’t show up as the 3 separate fields with hyphens between them in the Value box – all I get is a blank box to enter a string value.
—
Bud Cool, Accounting System Manager
HDA, Inc. Hazelwood, MO
GP 9.0, SP2

Habib Responds:

Bud,Pre-built smartlists show these columns as having an “Account Number” data type. However SLB does not allow you to define a string as an account number type.

However, you can achieve what you want. Add the account index. In the display definition (right arrow) select the “Account Index – show account number ” option. SLB will translate the account index into an account number for you, and it will show you that separator char in the value box. This will work for any account index such as IV00101.IVIVINDXHS.

Habib Salim is a Microsoft MVP and a regular contributor to the Microsoft Dynamics GP community newsgroup.

Until next post!

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

Retrieving an eConnect XML Message from MSMQ

A common issue with eConnect’s development is the ability to retrieve messages extracted with the eConnect Requester service from an MSMQ queue. While most developers will be able to setup the Requester Service to extract data from Microsoft Dynamics GP, most will find it hard to retrieve those messages from their integrating solutions. The following VB.NET code shows a quick and efficient way of doing just that.

Private Sub GetMessage()
  'Create queue object to retrieve messages from the default outgoing queue
  Dim MyQueue As New MessageQueue(".\private$\econnect_outgoing9")

  'Create an MSMQ formatter and transaction objects
  MyQueue.Formatter = New ActiveXMessageFormatter

  Dim MyTransaction As New MessageQueueTransaction

  'Create a message object
  Dim MyMessage As Message

  Try
    'Retrieve a message from the queue
    'This example assumes there is always a message waiting in the queue

    MyTransaction.Begin()
    MyMessage = MyQueue.Receive(MyTransaction)

    MyTransaction.Commit()

    'Retrieve the string from the message

    Dim MyDocument As [String] = CType(MyMessage.Body, [String])
    'Load the string into an XML document object

    Dim MyXml As New XmlDocument
    MyXml.LoadXml(MyDocument)

    'Display the XML from the queue message
    MessageText.Text = MyXml.InnerXml
  Catch err As SystemException

     ErrorText.Text = err.InnerException.ToString()
  End Try
End Sub

To summarize, it will be necessary to perform the following steps to extract a message from an MSMQ queue with your .NET application:

1) Create the queue object pointing to the eConnect queue
2) Create an MSMQ formatter and the correspoding transaction objects
3) Create a Message object
4) Retrieve the message from the queue
5) Retrieve the particular strings from within the message

From there on you can do anything with the information retrieved. In my example, I just chose to display the XML content of the message, but you can chose to pass this information to your application to perform any actions or store into some database table along with other info.

Until next post!

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

Navigating the Sea of Emerging Reporting Tools for Microsoft Dynamics GP

Long gone are the days when all you could cheer for was Report Writer and Advanced Financial Analysis. Now, your colleagues seem to be talking sophistication: FRx, Crystal Reports, SQL Server Reporting Services (SSRS) and SmartLists. You may wonder how it is that time went by so quickly and how you missed the boat.

In this article, I explain the most important tools that have emerged over the last couple years, in terms of where each stands in the reporting spectrum.

Reporting tools are divided into three categories:

1. Financial Reporting Tools. These tools allow the creation of reports designed to show your company’s financial performance during any given period. Financial reporting tools work by interacting directly with the GL tables. Examples of such reports include the balance sheet, income statement and cash flow reports.

2. General-Purpose Reporting Tools. These tools adopt a “free style” approach to report development and supply the user with virtually endless possibilities for reporting transactional data. Examples of such reports include an aged trial balance report, a sales order document, a customer or a vendor list, among others.

3. Analytical Tools. Analytical tools are specialized reporting interfaces designed to take advantage of decision support software. These tools are generally used to perform time-based trend analysis of critical information, such as sales, purchases, inventory, and even financial information. Data is then rolled up and stored in cubes that can be rotated or “sliced and diced” by the user in numerous ways that general-purpose reports cannot imitate.

Available today are a number of reporting tools that can be catalogued as internal or external to the application. These tools provide various degrees of capabilities based on the expected outcome.

1. Report Writer. Internal to Microsoft Dynamics GP, Report Writer is the first and most commonly used reporting option available to users. In fact, Dynamics GP includes more than 1,500 out-of-the-box reports, most of which can be customized to fit a particular business need.

2. Advanced Financial Analysis (AFA). The first internally built financial reporting tool available for Microsoft Dynamics GP. AFA is a relatively intuitive easy to use financial reporting tool with simple controls that allow for basic financial reports, such as balance sheets, P&Ls, and cash flows.

3. Crystal Reports. Crystal Reports is an external reporting application used to design and generate reports from a wide range of data sources. Several applications, such as Microsoft Dynamics GP and Microsoft Visual Studio bundle an OEM version of Crystal Reports as a general purpose reporting tool. Crystal Reports became the defacto report writer when first released with Microsoft Visual Basic.

4. FRx Financial Reporting Extender. FRx has become a standard financial reporting tool that enables users to create management and financial reports based on their general ledger. FRx integrates to a wide array of accounting systems, including Microsoft Dynamics GP, Dynamics SL, Dynamics AX, as well as with non-Microsoft ERP systems such as Sage’s MAS 500 and Epicor Vantage. FRx also combines GL information with other sources such as Microsoft Excel spreadsheets.

5. SQL Server Reporting Services. Microsoft SQL Server 2005 Reporting Services (SSRS) delivers enterprise, Web-enabled reporting functionality so users can create reports with content from a variety of data sources, allowing publishing in various formats, based on centrally managed security and subscriptions. With the release of Microsoft Dynamics GP 10, Microsoft has bundled a set of predefined SSRS reports, initiating the transition away from Report Writer-based reports.

6. SmartLists and SmartLists Builder. These are two data delivery and reporting tools clearly designed with the end user in mind. They are an alternative to raw SQL Server queries and hardcore development. SmartLists deliver a powerful set of predefined queries that are customizable through the addition of columns and implementation of filters on pre-existing data.

SmartLists Builder has been positioned as the “point-and-click” query builder for delivering new lists not included with standard Dynamics GP application. Both tools are internal to the application and require little to no configuration making them the true “plug and play” query and reporting tools. SmartLists offers automatic exports to Microsoft Word and Microsoft Excel.

7. Analysis Cubes for Microsoft Dynamics GP. Analysis Cubes in Microsoft Dynamics GP enables users to take full advantage of SQL Server Analysis Services by providing out-of-the-box preconfigured OLAP cubes. Analysis Cubes provides virtually unlimited ways to look at data with interactive analysis, using familiar tools such as Microsoft Excel.

Keep in mind that the more tools available to you, the better, since each serves different purposes and has different capabilities, so Buen Viento y Buena Mar, set sales and free your imagination by making the most of your data.

Published on MSDynamicsWorld.com on May 26, 2008

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

Integration Manager v9.0 & v10.0 Problem with Microsoft Excel 2007 DSNs

When creating a new DSN using the Microsoft Excel driver (*.xls, *.xlsx, etc.), you may receive the following error “An unhandled win32 exception occurred in Microsoft.Dynamics.GP.IntegrationManager.exe [6392].” followed by a dialog box to Debug or Close Program when attempting to select the Table drop-down in Integration Manager. This problem occurs with Microsoft Excel driver version 12.00.6211.1000.

Microsoft has confirmed this to be an issue with Integration Manager v9.0 and v.10 and has moved this up to the priority list to be fixed with the release of Service Pack 2.

Now, here is the work around I was able to come up with.

1) Create the DSN without building the connection string, then save.

2) Open the DSN again. This time around, click on the Connection String tab, and choose your workbook, then click on Apply.

3) Now, add your source as Simple or Advanced ODBC. Write your query WITHOUT clicking on the Query Builder button and click on the columns tab.

Until next post!

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

Upgrading SOP Entry VBA Customizations from Dynamics GP 9.0 to Dynamics GP 10.0

By now you may have painfully noticed that all the SOP Entry screen buttons have disappeared and replaced by a graceful Actions button drop-down list. That’s cool! Some may say. Less clutter! Others may yell. Yeah, right! But, for you the developer, this means that you are faced with upgrading all the customizations that depended on the Save, Delete, Void, Post, Transfer, and Purchase buttons to manage the new Actions button and spend a few more hours testing.

Approach

To make the changes to your customization code you must use the ActionButton.Value method to find the value of the chosen action (Save, Delete, etc.), however, life is not so simple as this window behaves differently based on the SOP document type, this is, the list of items in the Action button changes, depending on the type of SOP document selected by the user. For instance, assume you wanted to trap for the Delete action. When the document type is Fulfillment Order, choosing Delete causes ActionButton.Value property to be 3.

For a Back Order, choosing Delete causes ActionButton.Value property to be 4. Your VBA customization must now include the TypeTypeID field in the project so its value can be referenced. This is a sample code to test this — the first message boxes displays the value that needs to be watched in the document type and action drop-downs

' Created by Mariano Gomez, MVP
' This code is provided "as is". No warranties express or implied
'
Private Sub ActionButton_AfterUserChanged()

  'Which option was chosen?
  MsgBox "Document type: " + TypeTypeID.Value
  MsgBox "Action value: " + ActionButton.Value

  If (TypeTypeID.Value = 6) And (ActionButton.Value = 4) Then
    MsgBox ("Delete was chosen for a Back Order")
  End If

End Sub

NOTE: The above sample assumes a basic customization. If you have several actions implemented for each document type this may not be an effective approach. Please read below.

Hope this helps in alleviating the transition.

UPDATE – 10/16/2008
David Musgrave just released a new article that details all the key points of working with the new SOP Action button while providing great samples to follow. Be sure to visit his site!

This method uses pass-thru sanScript in VBA by referencing the Dynamics Continuum Integration Library to use the same Dexterity method exposed by David to retrieve the value selected regardless of the document type. That value can be stored in the DUOS and retrieved to execute an action.

' Created by Mariano Gomez, MVP
' Code is provided "as is". No warranties express or implied
' The Actions button will return the following values regardless of document type - these values are stored in the ActionValue variable
' 1 - ACTION_POST
' 2 - ACTION_TRANSFER
' 3 - ACTION_PURCHASE
' 4 - ACTION_CONFIRMPICK
' 5 - ACTION_CONFIRMPACK
' 6 - ACTION_CONFIRMSHIP
' 7 - ACTION_COPY
' 8 - ACTION_DELETE
' 9 - ACTION_VOID

Option Explicit

Dim ParamCollection As DUOSObjects
Dim ParamObject As DUOSObject

Private Sub ActionButton_BeforeUserChanged(KeepFocus As Boolean, CancelLogic As Boolean)
' Dim CompilerApp As New Dynamics.Application
Dim CompilerApp As Object

Dim CompilerMessage As String
Dim CompilerError As Integer
Dim CompilerCommand As String
Dim ActionValue As Integer

ActionValue = 0

' Initialize DUOS
Set ParamCollection = DUOSObjectsGet("Param." & UCase(UserInfoGet.UserID))
Set ParamObject = ParamCollection.Item("SOPEntryAction")
ParamObject.Properties("ActionValue") = Str(ActionValue)

CompilerCommand = CompilerCommand & "clear table SY_User_Object_Store; " & vbCrLf
CompilerCommand = CompilerCommand & "'ObjectType' of table SY_User_Object_Store = """ & "Param." & UCase(UserInfoGet.UserID) & """; " & vbCrLf
CompilerCommand = CompilerCommand & "'ObjectID' of table SY_User_Object_Store = """ & "SOPEntryAction" & """; " & vbCrLf
CompilerCommand = CompilerCommand & "'PropertyName' of table SY_User_Object_Store = """ & "ActionValue" & """; " & vbCrLf
CompilerCommand = CompilerCommand & "change table SY_User_Object_Store; " & vbCrLf
CompilerCommand = CompilerCommand & "'PropertyValue' of table SY_User_Object_Store = str(itemdata('Action Button' of window SOP_Entry of form SOP_Entry, 'Action Button' of window SOP_Entry of form SOP_Entry)); " & vbCrLf
CompilerCommand = CompilerCommand & "save table SY_User_Object_Store; " & vbCrLf
CompilerCommand = CompilerCommand & "check error; " & vbCrLf

' Shows you the sanScript code to be executed -- comment out for your project
MsgBox CompilerCommand

' Execute SanScript

Set CompilerApp = CreateObject("Dynamics.Application")
CompilerError = CompilerApp.ExecuteSanscript(CompilerCommand, CompilerMessage)
If CompilerError  0 Then
MsgBox CompilerMessage
Else
' Retrieve return values from DUOS
Set ParamCollection = DUOSObjectsGet("Param." & UCase(UserInfoGet.UserID))
Set ParamObject = ParamCollection.Item("SOPEntryAction")
ActionValue = Val(ParamObject.Properties("ActionValue"))
ParamCollection.Remove ("SOPEntryAction")
End If

Set CompilerApp = Nothing
' Do your thing here
MsgBox ActionValue
End Sub

NOTE: This customization uses a method of calling pass-thru sanScript that is not supported by Microsoft.

Until next post!

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

Using the New UserInfo Object in VBA for Microsoft Dynamics GP 10.0

With the release of MS Dynamics GP 10.0, the RetrieveGlobals.dll and RetrieveGlobals9.dll libraries available for releases 8.0 and 9.0 respectively, are no longer available to VBA customization developers.

Instead, VBA has been boosted with a new UserInfo object that will allow developers direct access to login information for the current user signed in into the application, providing a more transparent and integrated approach than the traditional RetrieveGlobals external objects.

The following code snippet shows how to make use of the UserInfo object and pass its information to an ADODB.Connection object.

Window_BeforeOpen() Event

Private Sub Window_BeforeOpen(ByVal OpenVisible As Boolean)
  cn = UserInfoGet.CreateADOConnection
  cn.DefaultDatabase = UserInfoGet.IntercompanyID
  cmd.ActiveConnection = cn
  cmd.CommandType = adCmdText
End Sub

In the above example, the Window_BeforeOpen event will initialize the connection information.

SomeField_Changed() Event

Option Explicit On
Dim UserInfoObj As UserInfo
Dim CompanyName As String

Dim cn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim sqlstring As String

Private Sub SomeField_Changed()
  If SomeField = \"\" Then
     Exit Sub
  Else
    cmd.CommandText = \"SELECT * FROM dbo.YourTable WHERE(SomeColumn ='\" &_
    SomeField & \"')\"
    rst = cmd.Execute

    ' -- Do something with the result set
  End If
End Sub

The Changed() event script will then consume the connection created in Window_BeforeOpen() and run a query against a database. The result is stored in a recordset.

Enjoy the new UserInfo object in VBA and make go make those changes to your customizations.

Until next post!

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

OLE Notes Path and Microsoft Dynamics GP Performance

Microsoft Dynamics GP’s performance may be affected if the OLE Notes path is not consistent among all workstations where Dynamics GP is installed. The performance behavior is consistent with that of slow network connectivity, which can lead to troubleshooting down the wrong path.

To verify each workstation’s OLE path, open the DEX.INI file (located under the Microsoft Dynamics GP installation folder for versions prior to 10.0 or in the Data folder under the installation folder for Dynamics GP 10.0 and above) and check the OLEPath setting (Click on image to enlarge).

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