Wise up with Wiseguy

Just a few days aback, I wrote an article referencing the acclaimed 1984 movie, Ghostbusters. In this occasion, my friend and fellow MVP, Mark Polino, makes an appearance with his new short fictional story, Wiseguy.

Wiseguy is a new twist to the classic mob tale. Prospective mobster Joey is stunned to learn that the family business is going to start filing tax reports with the IRS due to the power of Microsoft Dynamics GP. With the help of Jersey girl, Isabella, Joey must learn to collect cash using Microsoft Dynamics GP, instead of the traditional baseball bat method.

Wiseguy highlights some powerful features in Microsoft Dynamics GP including the Collections module, Accounts Receivable, EFT, and Scheduled Payments. It also highlights The Closer and The Validator. The story shows how learning Microsoft Dynamics GP can be fun and entertaining.

You can download Mark's work for free using a code from Reporting-Central - With a name like Gianmarco Salsano backing up Mark, you can understand why Wiseguy is becoming so popular.

http://www.reporting-central.com/wiseguydownload.htm

By the way, Wiseguy was one of my favorite 80's TV series, featuring Ken Wahl as agent Vincent Terranova. You can read a bit more about that series here.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

Read More

New Article on MSDynamicsWorld: When It’s Time to Upgrade an Outdated, Customized Microsoft Dynamics GP System…Who You Gonna Call?

It's been a couple months since I've written a business-driven column over at MSDynamicsWorld. This time, my new article looks into an all too common issue: upgrades from older, customized versions of Microsoft Dynamics GP systems.

By "older", I am not referring to - believe it or not - version 7.0 or 7.5, even though those are very outdated releases. I'm referring to you, still running version C/S+ 3.17, or 4.0 or even GPA. I know you have milked the product as much as you can and you feel it still does what you need it to do, but you are missing out on a unique opportunity to bring your systems up to date and take advantage of the wide array of ISV solutions that will once and for all get you off the development threadmill.

Since it's Friday, the article is meant to serve as food for thought.

When It’s Time to Upgrade an Outdated, Customized Microsoft Dynamics GP System…Who You Gonna Call?


Leave your comments and feel free to discuss with your peers.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

Read More

Microsoft SQL Server DSN Configuration

As I wrapped up fellow MVP Victoria Yudin's book just a week aback and prepared to write a review, I was reminded of the importance of properly configuring your Microsoft Dynamics GP DSN connection -- the artifact that allows the client software to read and write data to your company databases and the system database -- something the Microsoft Dynamics GP installation gracefully setup automatically nowadays for you. So, if this is the case, why is it that so many people still have issues with DSN connections and the Microsoft Dynamics GP client not "seeing" the server, I wondered.

So, I figured in this article, I would go back to basics to walk through some of the common issues and demistify the Microsoft SQL Server DSN configuration options that the setup gracefully takes care of.

1. First up, if you are on Microsoft SQL Server 2005, Microsoft SQL Server 2008, or Microsoft SQL Server 2008 R2, you should be using the Microsoft SQL Server Native Client driver. At this point, if you have any legacy ODBC drivers for systems that have been upgraded from SQL Server 7.0 or SQL Server 2000, these should not be used to connect to SQL Server 2005 or SQL Server 2008, because a) you are not taking full advantage of the Native Client driver's performance, and b) simply because the old driver is not designed to be used with the new.


Getting started


Clearly name your connection so you can identify which version of GP or server you are targetting. If you have multiple physical environments, for example, test and production, clearly name the driver to distinguish which environment you are targeting. Also, note that you can setup a driver that points to an instance of SQL Server by using the MACHINE\INSTANCE_NAME convention. Starting with version 10.0, the ODBC name must be exactly the same across all workstations where Microsoft Dynamics GP is installed.

2. Microsoft Dynamics GP only supports SQL Server authentication. As much as you complain or rant about the system not supporting Integrated Windows Authentication, you cannot set the authentication method to anything other than SQL Server authentication, in which case you will need a login ID and password only to test your connection. You can certainly avoid going through the other steps of the setup if you choose to uncheck the Connect to SQL Server to obtain default settings for the additional configuration options checkmark.



Setting up the Authentication method

Also note the checkmark option's prompt. The settings are read from SQL Server to obtain the default ODBC connection settings. This might sound redundant, but you will understand what this means next.

Some frequently asked questions as well are:

a) Why can't I use my Microsoft Dynamics GP account to authenticate my ODBC connection?

Because Microsoft Dynamics GP encrypts passwords on SQL Server. Since the password is encrypted on Microsoft SQL Server you would have to enter the sequence of characters that are a part of the encrypted password to authenticate and this is simply not feasible as well, you don't know the encrypted password to begin with. For more information see Why does Microsoft Dynamics GP encrypt passwords over at Developing for Dynamics GP.

b) Does the password I enter here get stored with the Connection?

Categorically No! The user Id and password information entered here is only used for verification of the SQL Server default settings and testing of the connection itself. They are never stored with the setup.

3. With newer versions of Microsoft Dynamics GP, there's no need to set the default database to Microsoft Dynamics GP system database, DYNAMICS. However, since I am an old timer and still have my own quirks, I do it as a standard practice. The default database is the master database when no other database is specified in the connection.

Choosing default database

What is still standard though is to disable the Use ANSI quoted identifiers and Use ANSI nulls, paddings and warnings in your connection settings. Now, keep in mind from my previous observation, that these connection settings are defaulted from Microsoft SQL Server settings at first. Why I emphasize this? Because I always get asked, Why are these checkmarks on? When in doubt, ask your Microsoft SQL Server administrator to show you the SQL Server properties for Connections.

Microsoft SQL Server Properties window - Connections tab

Also, you will want to note that at this point you can define a Mirroring server if you are running a mirrored database environment for your Microsoft Dynamics GP databases. For more information on Mirrroring, see KB article 926490 - Description of the requirements to run replication, clustering, log shipping, and database mirroring together with Microsoft Dynamics GP (access to CustomerSource or PartnerSource is required to view this article).


These are some frequently asked questions:

a) Why can't I enable ANSI quoted identifiers and ANSI nulls, warnings, and pads?

The answer lays with Microsoft Dexterity. Dexterity (through the Runtime Engine) does not support quoted identifiers for character strings or hetorogeneous transactions -- the function of ANSI nulls, warnings, and pads is to maintain consistency of transactions and queries across distributed platforms. Since Microsoft Dynamics GP is a client/server based application and the system database, DYNAMICS was designed to live on the same SQL Server with the company databases, there was really no need to maintain this compatibility. After all, no heterogeneous query would ever be issued to begin with. 

4. Some more settings that are key to keep in mind reside with this wizard page. Among them Perform translation for character data, which receives most of the attention among Microsoft Dynamics GP consultants and database adminstrators alike. This setting was designed to do, as it name suggests, translations of characters between the client code pages and the server code page.

Defining connection settings

In earlier versions of MDAC, i.e., MDAC 2.1 or later version of the SQL Server ODBC driver (version 3.70.0623 or later) or the OLEDB provider (version 7.01.0623 or later), under some circumstances you could experience translation of character data from the client code page to the server code page, even when Autotranslation is disabled for the connection. Autotranslation is not the only mechanism that can result in code page conversion. The SQL Server 7.0 ODBC driver and OLEDB provider introduced a behavior when connecting to MSDE 1.0, SQL Server 7.0, or later versions of either. All SQL statements sent as a language event are converted to Unicode on the client before being sent to the server. The end effect of this is similar to an Autotranslation of all data flowing from the client to the server through a language event, regardless of the current Autotranslation setting for the connection. This will not introduce any difficulties except when trying to store non-translated character data from a code page other than SQL Server's code page.

So why do we talk about code pages? Because a) Microsoft Dynamics GP does not support Unicode characters. If you are a database developer it means, there is no such thing as NCHAR, NVARCHAR, or NTEXT table columns defined throughout the system or company databases, that would otherwise allow for Unicode character storage. Since this has been the case since version 1 of Microsoft Dynamics GP, it's easy to see how a different client code page (as in, a different language being ran at the Windows operating system level) could cause issues if this option were to be enabled). I have ran my own tests with a Russian locale (code page 1251) and 1250 code page on SQL Server and when this option is enabled, I get jumbled data in my GP tables when submitting Russian characters to be stored on the server. That's because, while there are subtle differences between code page 1251 and code page 1250, the latter only supports Eastern European languages that are based on the latin character set.

5. Once you have completed the settings, it's now time to review the summary for all the options you have chosen. 

Reviewing connection configuration

6. Finally, you can test and you should now be good to go.

Testing connection

I hope this review of ODBC settings for the SQL Server Native driver have served as a good first step in understanding the configuration. With so many settings it's easy to see why any subtle change would cause issues across the board..

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.intellpartners.com/

Read More

Test-Driven Development with Dexterity

Often, when speaking to Visual Studio developers I am introduced to some new (or not so new) software engineering technique available via the slur of tools provided by Team Foundation Server (TFS). One of such tools is the ability to write/implement in-line test cases along with the rest of your code. This is known in Software Engineering as Test-Driven development or is sometimes referred to as self-testing code.

Dexterity also has these capabilities, believe it or not. Sufficient to say, that the Dexterity development environment and the sanScript language were conceived by Software Engineering and Compiler purists who were way ahead of their time back in the 80s when the mid market ERP was racing towards identifying a clear leader -- see Microsoft Dynamics GP Architectural Foundations Series - featuring Microsoft's Tim Brookins.

To address this issue, the SanScript language implements assertions. See A Historical Perspective on Runtime Assertion Checking in Software Engineering. An assertion is a claim about the state of computation at the time the assertion is evaluated. Assertions are always written as statements the programmer believes to be true. You can make your code self-testing by adding assertions at different stages. When the assertion fails there may be a potential problem with the script you are writing.

In Dexterity, the assert statement is primarily used to implement these claims. When an assert statement is found, the Dex compiler will evaluate the boolean condition associated with the statement. If the condition turns out to be false, the compiler will display a message related to the assertion. As such, the assert statement is implemented as follows:


assert boolean_expression {, message}

The following code illustrates the implementation of an assertion:



In this case, our assertion or primary assumption is that the selling price of the item be greater than the item cost itself. If we attempt to execute this code with the values shown on the image, the following error will be produced.




Note that an assertion will not prevent the values entered from being saved, but Dexterity will provide the option of aborting the script execution, ignoring the assertion, or debugging the current code to examine other alternatives. For the example above, the developer can choose to implement additional code that will prevent the saving of a record if indeed the selling price must be always greater than the item cost.

The assert statement should be seen as a development and testing tool. The dialog box indicating an assertion failed should not be displayed to the users of your application. By default, the runtime engine will not display the dialog box, even though an assertion failed. To allow testing with the runtime engine, add the RuntimeAsserts=TRUE setting to the defaults file, DEX.INI. This forces the runtime engine to display the dialog box for any assertions that fail.

Since assertion dialogs should not be seen by users, and some overhead is involved in evaluating them, it is strongly recommended that you don’t compile assert statements for the final version of your product. To prevent assert statements from being compiled, don’t compile your application with debug information.



These compilation options can be found in Dexterity Utilities.

Until next post!

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

Read More

Mass emailing customer documents with Microsoft Dynamics GP 2010

In a previous article, I detailed the steps required to setup the email feature in Microsoft Dynamics GP 2010 - see Configuring Email for Sales and Purchasing Documents in Microsoft Dynamics GP 2010. Now, I will show you how to take advantage of a few Dynamics GP features to mass email customer documents. That's right! En mass, bulk, or simply a bunch of them at a time!

First up, is the Print Sales Documents window, Transactions > Sales > Print Sales Documents, or if you prefer, click Sales on the Navigation Bar, then click the Print Sales Documents link under the Transactions web part.




Use this window to mass email sales documents when your ranges are predictable, for example, when you are emailing documents that are in sequence, or belong to the same date or consecutive dates, or based on the batch number or batch status.

Second up is the Sales Navigation Lists. By clicking on Sales on the Navigation Bar, then choosing Sales Order Transactions, you can display all transactions. The beauty of the Navigation List is that you can print transactions in any sequence, for any customer, and for any document types.




In addition, you can apply specific filter criteria for submitting emails to customers.

Until next post!

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

Read More

Configuring Email for Sales and Purchasing Documents in Microsoft Dynamics GP 2010

Recently, I have been receiving a number of questions on the new Sales documents email functionality in Microsoft Dynamics GP 2010 and how to get it configured and working adequately. If you know me by now, I love to translate those questions into useful articles for the Microsoft Dynamics GP community at large.

Fortunately enough, the configuration process is not that difficult. Microsoft Dynamics GP 2010 allows this feature to be configured in a top-down approach which in turn ensures that all related areas of the application are email-aware.

As a first step, there is a setup needed to be done at the company level - MSDGP > Setup > Company > E-mail Settings, or if you prefer, click on Administration on the Navigation Bar, then locate the E-mail Settings link under the Setup web-part:



The first settings you will be able to configure is whether you want the actual document embedded as part of the email body or as an attachment to the email itself. My preference is the latter as it allows for a cleaner look when the email is received by the recipient. Microsoft Dynamics GP supports 4 different attachment formats, DOCX, HTML, PDF and XPS.

Since Microsoft Word and Adobe Acrobat are widely available and have been around for a while, I strongly suggest selecting at least these 2 options. XPS has only been around as a document standard since 2009 (released in 2006) and has native support on Windows Vista and Windows 7, hence recipients running Windows XP may experience some difficulty opening attachments in this format, especially if they are unaware of the XPS viewer's download location.

NOTE: The IE-hosted XPS viewer and the XPS Document Writer are also available to Windows XP users with the download and installation of Microsoft .NET Framework 3.0.

Once you have defined the initial settings, you may then proceed to enable the email documents that can be submitted from your Microsoft Dynamics GP application. You will be able to define settings for both the Sales and the Purchasing series, as shown below:

Sales E-mail Setup

Purchasing E-mail Setup

The Sales E-mail Setup and Purchasing E-mail Setup windows can also be accessed under the respective series setup menus.

Of special importance is to setup the different labels for each document that will be enabled for submission. Also, if you are expecting the recipient to reply to your emails, select the mail boxes to which replies will be delivered using the Select Names button. My personal preference is to setup general mailboxes on your Microsoft Exchange Server or any other email hosting application. For example, your sales orders can have a general mailbox such as orders@fabrikam.com, or accounts_receivable@fabrikam.com if you are expecting replies to things like AR credit memos or debit memos from the recipients Accounts Payable team. You may do the same for purchasing. This overall approach ensures that emails are delivered to one location or an individual in charge of monitoring these replies.

Finally, you may further tailor specific settings for customer and vendor records, which will allow you to define at a granular level wheter the recipient will receive multiple attachments per email and the specific format for each document that will be attached to the email. You may also choose to enable or disable specific documents that may be submitted to the recipient.

Customer E-mail Options


Vendor E-mail Options

A feature I find particularly useful is, if you have customers or vendors that have size limits to their mailboxes, you can tailor Microsoft Dynamics GP to not allow file sizes above those limits.

I hope this review of the email capabilties of sales and purchasing documents has been useful. Please drop your comments and let me know whether you are using these features today in your business and if it has aliviated any of the past pains you've experienced by not having the functionality or in turn has created new problems.

Until next post!

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

Read More

From the Newsgroups: Upgrading 150+ company databases

This week the partner newsgroup takes on the topic of upgrades and you will be surprise to know the different approaches available to do upgrades for large amounts of company databases, especially when down time is not much of an option.

Q: Have got a situation with a client with 150+ companies (databases) on version 9. Client is extememly downtime sensitive. Is there a supported upgrade technique or method to minimized downtime?

The answer comes courtesy of Microsoft's Randal Mayer, Partner Technical Online Community moderator.

A: The supported approach is to prioritize the databases by urgency and importance of being completed. It is the high priority databases which will require being upgraded first, validated, backed up and then released to production. For the remaining databases of less importance and less urgency, run Utilities to convert them afterwards ideally after hours.

NOTE: All company databases are not required to be upgraded. In other words, once the system database DYNAMICS and a company database are upgraded, users having the new GP client can access GP to view the upgraded company data.

Dynamics GP Utilities by design does not allow an in-place upgrade of a database to run while users are in that database. In order to upgrade a database, a lock is placed on the database (duLCK table) to ensure users cannot access it from within Dynamics GP.

As you know, once the system DYNAMICS database is upgraded, no user will be able to access Dynamics GP unless using a GP install at the new version.

Not found in a manual but known to be occurring, you can also run Dynamics GP Utilities from multiple GP installs. The risk is of an interruption or resource issue at the SQL Server machine. If SQL Server machine has the resources and ideally designated strictly for SQL Server, multiple clients running GP Utilities can be converting company databases. I find this batch type approach where high priority companies are upgraded via GP Utilities at the SQL Server and lower priority companies from a client or two machine.

There are other approaches like upgrading databases amongst multiple SQL Server instances then restoring plus others. To stay within a supported approach as you request, I will stop here with the above information.

It is becoming not all that uncommon to find 100+ database GP installations. In summary, prioritize the databases during your upgrade planning.

I hope this information is useful and that it will help you when planning your next upgrade.

Until next post!

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

Read More

Testing Framework for Microsoft Dynamics GP now available for download

If you happened to have attended the past Microsoft Dynamics GP Technical Conference 2009 in Fargo, North Dakota, USA (David Musgrave and I co-presented 2 sessions at this conference) and also attended the Microsoft Dynamics GP Testing 101 session presented by Rob Bernhardt and Grant Swenson you may remember that they showcased a number of tools to assist in the testing of Microsoft Dynamics GP.

You will be glad to know that the toolset is now available for download on the MSDN code library, here.

If you are a Microsoft partner, ISV partner, or even a customer who happens to have a product developed, you will want to take these tools for a test drive.

Until next post!

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

Read More

Moving Items out of Inventory and into Fixed Assets

I love challenging customers (and partners) to use their Microsoft Dynamics GP system in very innovative ways. I like hearing things like 'this cannot be done' or 'there isn't a way to do that'. If there is a way, trust me, I will find it! One of these ways came in the form a simple question: how would you take an item that is already in inventory and move it to Fixed Assets?

Right off the start gate, I can think of 3 different ways to do this, but the solution I like the most because of its elegance and simplicity is as follows:

1. Setup an inventory site called FA and one called VIRTUAL or I/O (typically you will already have one of these setup if you have been using Inventory Control for a while).



2. Use the In-Transit Trasfer Transactions to transfer from your warehouse to the FA site using VIRTUAL as via site. Click on the Ship button to initate the 'transfer' to a Fixed Assets. In turn, this should create and in and out to your Inventory account -- so far good!



3. Open the Receiving Transaction Entry window in GP and perform an In-transit Inventory receipt type.

4. Select the In-Transit Transfer number and select the item to be transferred to a Fixed Asset.



5. Open the Receiving Item Detail Entry window then mark the Capital Item option.



6. When you click the Capital Item checkbox, click on the expansion button (blue arrow) to open the FA PO Additional Information window to add the asset information.



7. Post the receipt.

Since the Purchase Order Processing module uses a Fixed Assets clearing account to capitalize an item, you should be fine (accounting wise) once the asset is created in FA and you run your FA GL posting routine. There you go! No need to create additional vendors or customers or perform extra gimmicks.

Until next post!

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

Read More

New Article on MSDynamicsWorld: "Support Incidents vs. Consulting Engagements"

I am back at MSDynamicsWorld with a new article looking at how Microsoft Dynamics GP support incidents may (relatively quickly) end up being consulting engagements. Please take a look at this new post as it is full with useful guidelines... it may just save you some money next time when you open that new incident.

Until next post!

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

Read More

New Article on MSDynamicsWorld: MVP Frank Hamelly on "Keeping your Axe Sharp"

"If I had six hours to chop down a tree, I'd spend the first four hours sharpening the axe.' - Abraham Lincoln"

MVP Frank Hamelly quotes Abraham Lincoln in his new MSDynamicsWorld article and presents a tale of sorts to illustrate the importance of keeping your skills "axe" very sharp through training. Frank also discusses the different training options available and how employers and employees can take advantage of these to improve productivity during these difficult times.

Until next post!

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

Read More

New Article on MSDynamicsWorld: Michael Johnson on "Filtering Salesperson-specific data with SSRS"

"It didn't take long to figure out that the problem wasn't with Business Portal, but rather the way they were trying to use it and NOT using SQL Server Reporting Services (SSRS). Both tools have a place in their environment, but one is not a substitute for another"

My friend Michael Johnson, a.k.a. "The MBS Guru" has finally come out of hiding and has decided to publish a really clever solution for filtering salesperson-specific data in GP using SSRS over at MSDynamicsWorld. The good thing about Michael's solution is, you can apply it to pretty much any report that requires such type of access and filtering capabilities. They don't call this guy the MBS Guru for nothing!

Until next post!

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

Read More

New article on MSDynamicsWorld: MVP Victoria Yudin on "Taking charge of your own support"

"When I first started using Microsoft Dynamics GP almost twenty years ago... there were limited options for getting help"

MVP Victoria Yudin gives an account of her 20-year career history with Microsoft Dynamics GP and how the support landscape has changed over those years. She also gives out some important pointers on how to request help on newsgroups, forums, and even from Microsoft Dynamics GP Support. Be sure to check out her article over at MSDynamicsWorld.

Until next post!

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

Read More

New article on MSDynamicsWorld: "Integrating Microsoft Dynamics CRM with Microsoft Dynamics GP"

"No, this is not the Chris Tucker and Jackie Chan movie..."

One of my goals for this year is to become technically and functionally skilled in Microsoft Dynamics GP CRM. However, I never expected this to happen so quickly and abruptly as it did some 3 weeks ago with a "weekender-all nighter-gotta-get-it-done-by-Monday" project I worked on with my business partner. I collected some of the best practices and experiences from that project and put them together in my new MSDynamicsWorld article.

Until next post!

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

Read More