Friday, May 30, 2008

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!

Mariano Gomez, MIS, MCP, PMP
Maximum Global Business, LLC

Thursday, May 29, 2008

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:


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!

Mariano Gomez, MIS, MCP, PMP
Maximum Global Business, LLC

Wednesday, May 28, 2008

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!

Mariano Gomez, MIS, MCP, PMP
Maximum Global Business, LLC

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:

Search Accounts window

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!

Mariano Gomez, MVP
IntellPartners, LLC

Tuesday, May 27, 2008

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

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

MyMessage = MyQueue.Receive(MyTransaction)


'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

'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!

Mariano Gomez, MIS, MCP, PMP
Maximum Global Business, LLC

Monday, May 26, 2008

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 on May 26, 2008

Mariano Gomez, MIS, MCP, PMP
Maximum Global Business, LLC

Saturday, May 24, 2008

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!

Mariano Gomez, MIS, MCP, PMP
Maximum Global Business, LLC

Thursday, May 22, 2008

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.


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

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
' 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!

Mariano Gomez, MIS, MCP, PMP
Maximum Global Business, LLC

Monday, May 19, 2008

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
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!

Mariano Gomez, MIS, MCP, PMP
Maximum Global Business, LLC

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).

Mariano Gomez, MIS, MCP, PMP
Maximum Global Business, LLC

Accessing Microsoft Dynamics Knowledgebase Articles Directly from the Browser Address Bar

If you know the Knowledgebase article that you want to access in Microsoft Dynamics Support, there is no need to navigate through CustomerSource or PartnerSource to reach such article. You can paste the following link in your browser:;en-us;XXXXXX

Replace the XXXXXX for the six digit article number and press Enter on your keyboard. Very useful trick when time is of the essence.

Until next post!

Mariano Gomez, MIS, MCP, PMP
Maximum Global Business, LLC

Saturday, May 17, 2008

Sorting Records in Integration Manager v10.

After performing an upgrade to IM v10, from a previous release, users and developers alike must be aware that records will not automatically sort in the same order they appear in the file, thus making visual comparisons between source files and GP a bit more difficult.

To overcome this issue, open the Microsoft.Dynamics.GP.IntegrationManager.ini file found under your Integration Manager installation folder and change the UseOptimizedFiltering value to False.

This issues has been documented under KB article 924857.

Mariano Gomez, MIS, MCP, PMP
Maximum Global Business, LLC

Friday, May 16, 2008

Maximize Your Financial Investment in Dynamics During Uncertain Economic Times - Three Ways to Get More from Your Microsoft Dynamics System

You don't need a sophisticated computer application to tell you that economic conditions are deteriorating: customers not paying on time, vendors demanding payments sooner than usual, sales targets not being met, higher freight charges due to gas prices, inventory piling up, and so on.

On top of all those issues, you purchased your Microsoft Dynamics system and paid top consulting dollars, with the hope of better management and insight into your company's business activities, as well as to alleviate some transaction processing pains experienced prior to the implementation. Yet everything seems to be moving at about the same pace as it was before you made your investment.

The underlying problem may be that you are not taking full advantage of your Microsoft Dynamics system to help your organization thrive during these economic times. In fact, many organizations embark on the expensive and time-consuming task of implementing ERP systems without understanding the benefits of doing so. But, what to do now that you have the systems up and running? How can you leverage your investment to move your business forward? Here are three suggestions:

1. Invest smart in user training. It's hard to tell business owners to spend more money on top of the original investment that seems not to be paying off. However, there are inexpensive ways of improving your staff's skills without breaking the bank. Computer Based Training (CBT) has become an effective tool across many organizations. It allows employees to "attend" classes online and at their own pace, while still being able to perform their daily activities - all without leaving the office!

A survey conducted at one of my customers showed that, on average after the initial systems training, users retained 65% of the content presented, but substantially improved their knowledge when exposed to CBTs. Training must be cyclical, especially in organizations experiencing a high turnover. Make this your number one priority as it will help your organization achieve its goals faster.

2. Assess the current usage of your Microsoft Dynamics system. In a recent survey conducted among my customers, I found that, on average, 60% of the features of any given module are fully utilized, while overall, 40% of the capabilities of the entire system are truly understood by users and the business. What a waste of resources!

Microsoft Dynamics offers expanded features from configuration, to transaction processing, to reporting, to customization, to analytics that companies and users simply don't know or don't understand, but more so, they translate into an investment that is not being maximized. Are your reports currently not telling you enough? Why are production orders delayed? Is cash collection not fast enough? These may all be symptoms of an underutilized Microsoft Dynamics application. Engage your Microsoft Dynamics partner in assisting you with optimizing your system usage.

3. Step back and look at the big picture. It's never too late to gather pencil and paper to calculate your Microsoft Dynamics implementation's ROI. According to Nucleus Research, "71 percent of enterprises using Microsoft Dynamics GP realize a positive ROI within an average payback period of 21 months". Your ROI will depend on the Microsoft Dynamics solution implemented, long-term business goals, operating efficiencies, business benefits, and other intangible factors. With the assistance of your CFO and Microsoft Dynamics partner you can quickly establish an ROI baseline.

Economic downturns present a good opportunity to make a pit-stop and evaluate your Microsoft Dynamics investment. Understanding your system's capabilities, and working closely with the user community and your Microsoft partner, are keys in establishing the factors that will quickly allow your business to thrive during hard times.

Published on on May 16, 2008.

Mariano Gomez, MIS, MCP, PMP
Maximum Global Business, LLC

Monday, May 12, 2008

Surviving as an Independent Consultant - 5 Suggestions to Improve your Chances

As these economic times appear to be more and more difficult, consultants around the world are bracing for significant declines in current revenue stream. However, there is a particular niche of consultants – the Microsoft Dynamics group of independents – who will perhaps feel the pinch a little more. So, what to do when things turn sour?

1. Learn or improve upon existing skills. If you are not in tune or familiar with particular aspects of the Dynamics application you currently support, you can sign up for online classes, read manuals and participate in discussion boards. You will soon discover that the aspects of the application you have been neglecting are now a part of your arsenal of skills, and allow you to market yourself better among new and existing customers.

Don’t be afraid of learning another Dynamics application! Believe it or not, the financial and distribution concepts are not that far apart in each solution, plus you will enjoy doing so as much as you did learning the current product you support.

2. Network with others. The best way to market your skills and to become known in the business community is by attending industry seminars, specialized Microsoft events, and partner tradeshows. However, it will be also necessary to dust off the rolodex and begin contacting peers and customers with whom you have conducted business in the past. You never know, maybe there goes your next upgrade!

3. Partner with other independent consultants. You will find that no one individual possesses all the skills required or can provide all services demanded by a customer. This will also give you the opportunity to learn new skills, while offering those you are currently in command of. I have found that this experience lead many independents to become founders of new business ventures.

4. Keep your prospects close and your customers closer. It’s important to make new prospects feel valued, hence follow up with courtesy calls after an initial meeting. A follow up will allow your prospect to ask any questions that may have been omitted during the first meeting, while introducing professionalism into the newly forged relationship.

A big part of an independent consultant’s revenue stems from new business with existing customers. Make sure you listen to your customers’ needs while keeping an eye open for new opportunities. However, don’t pretend to know everything! Customers value honesty. If you find yourself in a situation where you are asked to perform work outside of your range of skills, simply say no and consider partnering with someone (see suggestion number 3).

5. Be professional in your documentation. Nothing says “I care” more than the documentation you produce for your customers and prospects. Take the time to produce quality quotes, statements of work, interim project documentation, and project closing documents. Invest time in learning some of these techniques as they will come in handy and say a great deal about you and your delivery skills.

These times are very challenging, but certainly an opportunity to boost your skills. If you invest in training, cultivate your professional network, and remain attentive to prospects and customers, you will be in a unique position for when things turn around.

Mariano Gomez, MIS, MCP, PMP
Maximum Global Business, LLC

Dropping Timestamps from SQL Server and Text File Data Sources in Integration Manager

As promissed, the following post deals with managing those rogued timestamps in SQL Server and text file data sources. This problem, contrary to what anyone would think, is very common among Integration Manager developers and has haunted newsgroups and message boards with questions on how to effect such conversions. I have devised two methods that are effective in removing timestamps from data sources. The first method deals with the problem at its source -- the source query. The second method utilizes VBScript to remove the timestamp.

SQL Server Query Method (Source Query)

To drop a timestamp from a SQL Server data source, you will need to setup a simple ODBC or advance ODBC data source. The query will have to make use of the SQL Server convert function to remove the timestamp from the datetime field as follows:

select field1, field2,..., convert(datetime, convert(char(15), thedatefield, 101)) as newdatefield from your_table where your_condition

Note the dual conversion, from date to char and back to a datetime expression.

VBScipt Method (Date Field Script)

In order to apply this method, you will need to open the script editor for the date field and enter the following script:

tDate = SourceFields("SourceQuery.thedatefield")
newDateField = right("00" & Month(tDate), 2) & "/" & right("00" &_
Day(tDate), 2) & "/" & year(tDate)
CurrentField.Value = newDateField

The neatness of this script rely on the ability to set the dows (leading zeroes) in front of the month and day values without extending the script beyond 3 lines.

Hope you liked these two simple, yet useful methods of dropping timestamps from data sources.

Until next post!

Mariano Gomez, MIS, MCP, PMP
Maximum Global Business, LLC

Thursday, May 8, 2008

Supported Date Formats in Integration Manager

The topic of date formats supported by Integration Manager comes up very often in discussion boards and online community forums around the web, especially overseas where date formats tend to be distinct from the typical US format. Even here in the US, it is necessary to make certain adjustments to the date format if characters other than the forward slash ("/") are introduced as separators.

Currently, Integration Manager will accept date formats that are in the order of month-day-year. Using the date 05/01/08, the following formats will be acceptable within your source file or table:

MM/DD/YY 05/01/08
MM/DD/YYYY 05/01/2008
M/D/YY 5/1/08
M/D/YYYY 5/1/2008
MMDDYY 050108
MMDDYYYY 05012008

When converting other date formats, it will be necessary to introduce VBScript into the mapping of the date field to standardize to any of the aforementioned formats.

Let’s assume a transaction date of '20080501' (YYYYMMDD) is given in a file, to be converted to the traditional US format of MM/DD/YYYY. The following VBScript would be added to the transaction date field, as follow:

' Created by Mariano Gomez, MVP
' No warranties conferred, express or implied

' Assign the source date to a local script variable
sDate = SourceFields("Query.Transaction_Date")
fDate = Mid(sDate, 5, 4) & Mid(sDate, 1, 4) ' Move the year at the end of the date string
CurrentField.Value = fDate ' Map the transposed date field to the transaction field

Now let's assume your date format is DD/MM/YYYY. The following script will properly format the date for Integration Manager to MM/DD/YYYY:

' Created by Mariano Gomez, MVP
' No warranties conferred, express or implied

' Assign the source date to a local script variable
sDate = SourceFields("Query.Transaction_Date")
fDate = Mid(sDate, 4, 2) & Mid(sDate, 1, 2) & Mid(sDate, 7, 4)
CurrentField.Value = fDate ' Map the transposed date field to the transaction field

This post has dealt with the topic of date formatting. My next topic will talk about dropping timestamps from source date fields using SQL and VBScript, depending on the source.

As always, drop me a note letting me know what you think about this post or commenting on your personal experiences with the subject.

Until then,

Mariano Gomez, MIS, MCP, PMP
Maximum Global Business, LLC

Macros in Microsoft Dynamics GP v 10.0

Yesterday, I was asked why macros that seemed pretty straight forward and worked well in previous releases of Microsoft Dynamics GP, all in a sudden, seem not to work in Dynamics GP 10. Case in particular, a macro was previously written to automate the checklinks of the Sales Work table group and automatically close the application after completing the operation, but now seemed to hung when attempting to close.

After running a few tests, as I suspected, the reason Microsoft Dynamics GP is not quiting is 3-fold:

1) The Navigation Pane feature is an internal Dexterity window. When control is returned from the check links process, the focus is immediately returned to the navigation pane, not the work area form (BuiLtin form), causing the macro command to fail.

To fix this, the navigation pane needs to be disabled first when the macro begins its execution:

CommandExec dictionary 'default' form 'Command_System' command NavigationPane

2) Macros and Progress Windows are enemies. This one is not new, however, it is a common occurrence since macros attempt to restore the focus to the progress window itself once the progress field vanishes (when it reaches 100%).

3) Report output to screen is also a point of failure for macros. The macro will need to bypass the screen output to a file if possible, since the Report output screen is an internal Dexterity window.

Saving to a file (appending always) will avoid trying to close the screen output window, hence cancelling interaction with the internal Dexterity form.

The following is the complete macro code:

CommandExec dictionary 'default' form 'Command_System' command NavigationPane
CommandExec dictionary 'default' form 'Command_System' command 'SY_Check_Links'
NewActiveWin dictionary 'default' form 'SY_Check_Links' window 'Check Links'
ClickHit field 'File Series' item 2 # 'Sales'
MoveTo field Source item 0
ClickHit field Source item 19 # 'Sales Work'
MoveTo field 'Insert>> Button I'
ClickHit field 'Insert>> Button I'
MoveTo field 'OK Button'
ClickHit field 'OK Button'
NewActiveWin dictionary 'default' form 'Report_Destination' window 'Report_Destination'
MoveTo field 'Print to File' # 'FALSE'
ClickHit field 'Print to File' # 'TRUE'
MoveTo field 'File Export Name'
TypeTo field 'File Export Name' , 'test.txt'
MoveTo field 'Export File Type' item 0
ClickHit field 'Export File Type' item 1 # 'Text file'

MoveTo field 'OK Button'
ClickHit field 'OK Button'
# NewActiveWin dictionary 'default' form sheLL window sheLL
# ActivateWindow dictionary 'default' form 'Progress_Control' window 'Progress_Window'
# NewActiveWin dictionary 'default' form sheLL window sheLL
# Print To File: 'File Maintenance Error Report'
CommandExec form BuiLtin command cmdQuitApplication

Highlighted in red are the key aspects that need to occur in the macro to bypass any user interaction and for the macro to be successful in its execution; note the exclusion of the Progress_Control window. Highlighted in orange is the bypass of the screen output in favor of a less interactive file export.

Hope this helps all of you who are trying to figure out this new Dynamics GP 10 user interface.

Please drop your comments about this article.

Mariano Gomez, MIS, MCP, PMP
Maximum Global Business, LLC

Wednesday, May 7, 2008

Suppressing Voided Documents from Customer Statements

Microsoft Dynamics GP is a feature-rich product with the ability to include and exclude a good amount of information via the user interface, but when it comes to simple practical aspects such as suppressing voided documents from a customer statement, well it kind of falls short. No worries though! This article presents detail steps on how to suppress voided documents on customer statements without involving any programming.

For this example, I will be using the RM Statement Long Form (any statement will do) and will devide the process into 2 phases. So, lets get started.

Phase I - Setup the new relationships to expose the Void Status field

Since the document void status cannot be obtained from the current tables in the relationship, it will be necessary to create a relationship to the RM Open table.

1) Print the statement you would like to modify to screen and hit CTRL + F9 on your keyboard. Close ALL opened windows once in Report Writer.

2) Click the Tables button on the toolbar and select Tables from the drop-down button menu. This will open the Tables window.

3) Locate the RM_Statements_TRX_TEMP table, click Open to open the Table Definition window.

4) Click on the Relationships button to open the Table Relationships window. Click on the New button to open the Table Relationships Definition window.

5) Click the ellipses button, next to the Secondary Table field to open the Relationships Lookup window. Locate the RM Open table, highlight and click Ok.

6) Back to the Table Relationships Definition window, click on the Secondary Table Key drop-down and select RM_OPEN_Key1

7) Match each drop-down key segment on the left with the grayed out key segment on the right. You should find and EXACT MATCH for each field (this should create a One-to-One relationship between the tables) as shown below. When finished, click Ok to close the Table Relationships Definition window.

Table Relationships Definition Window

8) Close the Table Relationships window

9) Click Ok to close and save the Table Definitions window.

10) Close the Tables window

11) In the Report Writer window, highlight the report and click Open to open the Report Definition window.

12) Click the Tables button to open the Report Table Relationships window. Highlight the RM Statements Transaction Temporary file, then click the New button.

13) In the Related Tables window, select the RM Open table added in step 5, and click Ok.

14) Close all subsequent windows to apply the changes.

Phase II - Suppress the field on the report

Now that we have created the table relationships, we will setup a calculated field to check on the Void Status field of the RM Open table to decide whether the record will be included in the body of the statement report. For this, we will use the "Suppress When Field is Empty" feature in the Report Section option, in tandem with the calculated field.

1) Click on the Reports button on the toolbar. This will open the Report Writer window.

2) Select the statement report from the Modified Reports column on the right and click the Open button to open the Report Definition window.

3) Click the Layout button to open the Report Layout window.

4) Select calculated fields from the drop-down in the Toolbar window (usually at the left of the screen)

5) Setup a Conditional calculated field called (C) Suppress Voids that returns an Integer value.

6) Setup the following 3 expressions, as shown below for the calculated field.

Calculated Field Definition Window

7) Hit Ok to save

8) Click on the Layout window again and this time select Tools from the menu. Click on Section Options

9) Click on "Suppress When Field Is Empty" checkbox beneath the Body checkbox and select the calculated field created in steps 5 and 6.

10) Close the Layout Window and click Save when prompted to. Click Ok in the Report Definition window.

Return to GP and grant security to the modified report accordingly with the release of GP you are using. This should take care of everything.


Until next post!

Mariano Gomez, MVP
IntellPartners, LLC


10/17/2013 - included steps 11 through 14 in phase I to describe the steps required to add the new table relationship to the report, per comment suggestions. Improved graphics layout and added window names.